Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 522

4 FIPS 11/28 10:43 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
4 FIPS 11/28 10:45 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
4 FIPS 11/28 10:46 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
Step 2
In the output listed in Step 1, there are connections grouped under different services. Each service further has several
modules, and each module has several actions. From the output, it's also understood that the modules and actions are
shared between the various services.
Results of the analysis identified shared modules and actions that were used by all services. This means if an
inefficient code is found, the performance gains from optimizing the code will be to all services. To understand the
overall performance of the various sections of the application and drill down to programmatic areas, it's important
to enable aggregation of statistics. This can be done either using enterprise manager or directly from the SQL plus
prompt.
In Chapter 1, breaking down the application into quadrants to help analyze the problem area was discussed.
Breaking down the application based on the connection type, such as a database service (identified by SERVICE_NAME ),
could be the high-level quadrant. Once the quadrant with the performance issue has been identified, the quadrant
can be further broken down into sub-quadrants; here the module will be the sub quadrants and will help by further
drilling down to the specific problem area. Once the problem module is identified, the next step is to drill down to a
specific action that could be causing performance issues.
The next step should be finding out services that are heavily utilized and checking against the
GV$SERVICEMETRIC_HISTORY view:
Script: MVRACPDnTap_svcstatshist.sql
SELECT SERVICE_NAME,
INSTANCE_NAME,
ELAPSEDPERCALL SERVICE_TIME,
CPUPERCALL CPU_TIME,
DBTIMEPERCALL DB_TIME,
CALLSPERSEC THROUGHPUT
FROM GV$INSTANCE GVI,
GV$SERVICEMETRIC_HISTORY GVSM
WHERE GVI.INST_ID = GVSM.INST_ID
AND GVSM.GROUP_ID = 10
AND GVSM.SERVICE_NAME NOT IN ('SYS$BACKGROUND')
ORDER BY
SERVICE_NAME,
GVI.INST_ID;
Elapsed CPU Time DB Time
Service Int mSec/Call mSec/Call mSec/Call Calls/sec
------------ ---- ------------ ---------- ---------- ---------
TP 1 114021 25245 114021 2953.89
TP 2 36969442 460485 36969442 835.58
TQC 1 37822273 4808064 37822273 1021.07
TQC 2 63958174 9283369 63958174 722.73
TQI 1 12075688 338101 12075688 437.44
TQI 2 30649094 387219 30649094 270.62
SQH 5 807323 119198 807323 162.59
SQS 4 2124545 34642 2124545 8188.80