Database Reference
In-Depth Information
Apart from making updates to the GV$SERVICEMETRIC view, MMON also updates the operating system
statistics and uses this information to determine the load characteristics on the various nodes in the cluster. The
following query output from GV$OSSTAT provides the operating system statistics:
SQL> SELECT * FROM GV$OSSTAT WHERE INST_ID=1;
INST_ID STAT_NAME VALUE OSSTAT_ID
---------- ------------------------------ ---------- ----------
1 NUM_CPUS 2 0
1 IDLE_TIME 134867 1
1 BUSY_TIME 82151 2
1 USER_TIME 66198 3
1 SYS_TIME 15953 4
1 NICE_TIME 0 6
1 RSRC_MGR_CPU_WAIT_TIME 0 14
1 LOAD 6.65917969 15
1 PHYSICAL_MEMORY_BYTES 433270784 1008
GV$OSSTAT
GV$OSSTAT contains O/S statistics updated by the MMON process and is used to determine the load on the nodes/
servers. The value updated by the MMON process is then processed and sent to the participating clients by the
MMON Lite (MMNL) process. The values are in hundredths of a second, as a processor has been busy executing code,
and is averaged over all processors.
Tracing the Load Metric Capture
Load balancing can be verified by enabling tracing at the database level using event 10735 at Level 3. Tracing can be
enabled using the following statement:
SQL> ALTER SYSTEM SET EVENTS '10735 TRACE NAME CONTEXT FOREVER, LEVEL 3';
When the trace is enabled, the MMNL process writes additional load average information into its trace file stored
in the user dump destination directory of the instance. The user dump destination directory can be determined after
connecting to the database and verifying the parameter USER_DUMP_DEST.
The MMNL trace file contains the activities involving the cluster and or services that are being monitored. This
provides the insight into how the client machines using this metric will react. The output following is an extract of
the trace for the database service RAPTEST. The output indicates the various load matrixes and the call function that
helps determine the local server threshold:
[oracle@prddb1 trace]$ cat PRDDB_1_mmnl_23263.trc | grep RAPTEST
kswsgfntpt : Metrics for svc : RAPTEST
kswsgfntpt : svc / fgval / clbdelta / scount : RAPTEST / 0.0000/ 100.0000 0
kswsgthr: calling kelr_get_local_threshold() svc:RAPTEST
kswsgfntpt : Metrics for svc : RAPTEST
kswsgfntpt : svc / fgval / clbdelta / scount : RAPTEST / 4.1667/ 5050.0000 2
kswsgthr: calling kelr_get_local_threshold() svc:RAPTEST
kswsgfntpt : Metrics for svc : RAPTEST
kswsgfntpt : svc / fgval / clbdelta / scount : RAPTEST / 14.5833/ 2541.6113 7
kswsgthr: calling kelr_get_local_threshold() svc:RAPTEST
Search WWH ::
Custom Search