Remote DBA

How to resmgr cpu quantum wait

December 25, 2016 Mudhalvan Leave a comment

Issue:
When the DB Performance issue is reported, After checking found the server load is very low, CPU/IO 90+ percent free.
But many sessiones waiting on event “resmgr:cpu quantum”. From AWR report we can see “resmgr:cpu quantum” is the TOP one consuming 100% DB time

What/Why resmgr:cpu quantum :

“resmgr:cpu quantum” means a session is waiting in a resource queue, wait until resource manager assign CPU pieces to it.

The wait event resmgr:cpu quantum is a normal wait event used by the Oracle Resource Manager to control CPU distribution. The resmgr:cpu quantum only occurs when the resource manager is enabled and the resource manage is “throttling” CPU consumption.

This is due to DEFAULT_MAINTENANCE_PLAN. From 11g onwards every weekday window has a pre-defined Resource Plan called DEFAULT_MAINTENANCE_PLAN, which will become active once the related window opens. In 10gR2, DEFAULT_MAINTENANCE_PLAN is associated with WEEKNIGHT_WINDOW and WEEKEND_WINDOW.

You can confirm this using the below SQL
SQL> select name,value from v$parameter where name in (‘resource_manager_plan’,’resource_limit’)’

SQL> show parameter resource

SQL> select window_name,resource_plan,last_start_date,duration,enabled from dba_scheduler_windows;

Solution:

Note 392037.1 – Database ‘Hangs’. Sessions Wait for ‘resmgr:cpu quantum’
Note 1339803.1 Recommended Patches for CPU Resource Manager
Set the maintenance window to other time where CPU resource is available maximum
During peak time clear the resource_manager_plan to none
alter system set resource_manager_plan=” scope=both;

You can disable resource plan for specific days by following commands

execute dbms_scheduler.set_attribute(‘MONDAY_WINDOW’,’RESOURCE_PLAN’,”);
Note: The above command will disable resource plan for Monday only

Oracle

How to DBMS_SYSTEM Package

August 3, 2016 Mudhalvan Leave a comment

DBMS_SYSTEM package contains a number of procedures that can be useful. Oracle clearly state that these procedures are not supported so use at your own risk.

The package DBMS_SYSTEM is installed by running the script
$ORACLE_HOME/rdbms/admin/dbmsutil.sql
This script is internally called by
$ORACLE_HOME/rdbms/admin/catproc.sql
By default only the SYS user can execute procedures and functions in this package.

To allow other users to execute this package, run the following commands as SYS
GRANT EXECUTE ON dbms_system TO PUBLIC;
CREATE PUBLIC SYNONYM dbms_system FOR dbms_system;

Commonly used procedures or may required for DBA

ksdwrt
SET_SQL_TRACE_IN_SESSION
SET_EV
READ_EV

ksdwrt:

Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);
Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Example:
To write a message to the trace file use
EXECUTE dbms_system.ksdwrt (1, ‘Output written to trace file’);
To write a message to the alert log use
EXECUTE dbms_system.ksdwrt (2, ‘Output written to alert log’);
To write a message to both the trace file and the alert log use
EXECUTE dbms_system.ksdwrt (3, ‘Output written to both trace file and alert log’);

SET_SQL_TRACE_IN_SESSION:

Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

This is the equivalent to enabling event 10046 level 1.

For example to enable trace in a session with SID 9, use:
EXECUTE dbms_system.set_sql_trace_in_session (9, 29, true);
To disable trace in the same session use:
EXECUTE dbms_system.set_sql_trace_in_session (9, 29, false);
The SID and serial# for the session can be obtained from V$SESSION e.g.:
DECLARE
l_sid NUMBER;
l_serial NUMBER;
BEGIN
SELECT sid, serial#
INTO l_sid, l_serial
FROM v$session
WHERE username = ‘User1’;

dbms_system.set_sql_trace_in_session (l_sid, l_serial, TRUE);
END;
/

SET_EV:

Used to set trace on for a specific event.

EXEC DBMS_SYSTEM.set_ev(sid, serial#, event, level, name);
EXEC DBMS_SYSTEM.set_ev(31, 97, 10046, 4, ”);

Where level indicates the following levels of trace.

1 – Standard SQL_TRACE functionality.
4 – As level 1 plus tracing of bind variables.
8 – As level 1 plus wait events.
12 – As level 1 plus bind variables and wait events.

READ_EV:

Used to check if a specific event is currently being traced.

EXEC DBMS_SYSTEM.read_ev(event, output);

If output = 1 the event is being traced.

Oracle