Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 284

5 3kvu10uhykg7y 17437284 3266017235 34687603948 22257664
8436p6pb4j6pz 1.84E+08 1039185500 7039642954 7233536
7g94kt7nkq1yt 1086 444176496 6070377657 43016192
2ub6q6w9aa9pf 4466 37937685 1578875870 38723584
38kt82gam41ak 30676410 51508343 409237475 663552
ast9xrxar0yx6 0 12114417 234604478 19947520
92ccq3p60gwz2 0 36387561 185204879 1056768
0h6tt5nw7kyad 987 1598312 129586820 32505856
In the output above, the statement with SQL_ID 3kvu10uhykg7y has the highest cluster wait times ( CLWT column).
The query also experienced high concurrency waits ( CONWT column), and the average wait time ( AWT column)
was also high.
Step 2
With the help of the SQL_ID from Step 1, the actual sql text was determined.
SQL> SELECT SQL_ID, SQL_TEXT FROM GV$SQLSTATS WHERE SQL_ID='3kvu10uhykg7y';
SQL_ID
----------------
SQL_TEXT
-----------------------------------------------------------------------------------------------
3kvu10uhykg7y
INSERT INTO RMQ_MESSAGE(MESSAGE_ID,RA_ID,RMQ_ID,REQUEST_ID,MESSAGE_TYPE,MESSAGE_TARGET_TYPE,
MESSAGE_VERSION,MESSAGE_PRIORITY,ACK_PROCESSING,REPLYTO_COMMAND_ID,MESSAGE_DATETIME)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)
The statement is a DML operation, applying the understanding of the architecture discussed in Chapter 2. This
could be a scenario where there is high block movement between instance 4 and instance 5. Step 1 also shows that the
operation had a significantly high number of bytes transferred across the interconnect.
Step 3
In a RAC environment, with high insert operations where the operation includes a monotonously increasing sequence
number based on primary keys, it is a good practice to isolate the transactions to one or few instances in the cluster.
The query was found only on instances 4 and 5. Drilling down further into the operation of this query and the
current session information, the query is executed from an application connected to the database with a service name
'TAPS. Checking the status of the service using the srvctl utility, it was determined that the service was running on
instances 4 and 5 only.
SQL> !srvctl status service -db SSKYDB_
Service FIPS is running on instance(s) SSKYDB_1,SSKYDB_2
Service SSKY is running on instance(s) SSKYDB_1,SSKYDB_2
Service GRUD is running on instance(s) SSKYDB_1,SSKYDB_2
Service TICKS is running on instance(s) SSKYDB_1,SSKYDB_2,SSKYDB_3,SSKYDB_4,SSKYDB_5
Service TAPS is running on instance(s) SSKYDB_4,SSKYDB_5
Service SRPT is running on instance(s) SSKYDB_5