The LatchProf and LatchProfX scripts allow you to be more systematic with latch contention troubleshooting and tuning. No more guesswork is needed as these scripts give you exact session IDs and in this version also SQLIDs of the troublemaking applications.
You can download the new versions here:
Example output (with SQLID info) is below:
SQL> @latchprof name,sid,sqlid % % 100000
-- LatchProf 1.21 by Tanel Poder ( http://www.tanelpoder.com )
NAME SID SQLID Held Gets Held % Held ms Avg hold ms
----------------------------------- ---------- ------------- ---------- ---------- ------- ----------- -----------
cache buffers chains 133 3jbwa65aqmkvm 349 348 .35 14.169 .041
simulator lru latch 133 3jbwa65aqmkvm 51 51 .05 2.071 .041
row cache objects 133 3jbwa65aqmkvm 5 5 .01 .203 .041
cache buffers chains 24 5 5 .01 .203 .041
cache buffers chains 149 3jbwa65aqmkvm 3 3 .00 .122 .041
resmgr group change latch 33 147a57cxq3w5y 2 2 .00 .081 .041
cache buffers chains 9 5raw2bzx227wp 2 1 .00 .081 .081
In memory undo latch 149 f3y38zthh270n 2 1 .00 .081 .081
active checkpoint queue latch 5 2 1 .00 .081 .081
cache buffers chains 149 75621g9y3xmvd 2 2 .00 .081 .041
cache buffers chains 9 gvgdv2v90wfa7 2 2 .00 .081 .041
cache buffers chains 33 75621g9y3xmvd 2 2 .00 .081 .041
checkpoint queue latch 5 1 1 .00 .041 .041
ksuosstats global area 8 1 1 .00 .041 .041
cache buffers lru chain 133 3jbwa65aqmkvm 1 1 .00 .041 .041
multiblock read objects 155 75ju2gn3s8009 1 1 .00 .041 .041
resmgr group change latch 9 0w2qpuc6u2zsp 1 1 .00 .041 .041
resmgr group change latch 33 apgb2g9q2zjh1 1 1 .00 .041 .041
resmgr group change latch 133 apgb2g9q2zjh1 1 1 .00 .041 .041
space background task latch 17 1 1 .00 .041 .041
cache buffers chains 149 5raw2bzx227wp 1 1 .00 .041 .041
cache buffers chains 33 5raw2bzx227wp 1 1 .00 .041 .041
cache buffers chains 33 05s4vdwsf5802 1 1 .00 .041 .041
cache buffers chains 31 0yas01u2p9ch4 1 1 .00 .041 .041
cache buffers chains 9 41zu158rqf4kf 1 1 .00 .041 .041
In memory undo latch 33 0bzhqhhj9mpaa 1 1 .00 .041 .041
In memory undo latch 31 gvgdv2v90wfa7 1 1 .00 .041 .041
In memory undo latch 9 gvgdv2v90wfa7 1 1 .00 .041 .041
simulator lru latch 149 3jbwa65aqmkvm 1 1 .00 .041 .041
row cache objects 133 5yq51dtyc6qf2 1 1 .00 .041 .041
SQL memory manager workarea list la 133 3jbwa65aqmkvm 1 1 .00 .041 .041
enqueues 141 1 1 .00 .041 .041
row cache objects 132 1 1 .00 .041 .041
33 rows selected.
LatchProf scripts allow you to easily identify which session and SQLID (or sqlhash in 9i) cause the latch(es) to be held the most.
Let’s check what’s the most “latch-holding” SQL reported by LatchProf:
SQL> @sqlid 3jbwa65aqmkvm
HASH_VALUE CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
1432996723 0 SELECT O.ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY, ORDER_MODE, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID, C.CUSTOMER_ID,
CUST_FIRST_NAME, CUST_LAST_NAME, CREDIT_LIMIT, CUST_EMAIL, ORDER_DATE FROM ORDERS O , ORDER_ITEMS OI, CUSTOMERS C WHERE O.ORDER_ID = OI.ORDER_ID AND
O.CUSTOMER_ID = C.CUSTOMER_ID AND O.ORDER_STATUS <= 4
If you want to read more about the capabilities of LatchProf and LatchProfX, go here:
Note that the latest version (v1.21) also fixes a problem with Oracle 11.2 where the script execution plan order was wrong, causing the sampling to not happen in correct order. I added a NO_TRANSFORM_DISTINCT_AGG hint to disable a new transformation happening in 11.2 to make the scripts behave correctly…
Tanel Poder Administration, Cool stuff, Oracle, Performance, Troubleshooting
Recent Comments