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:
- LatchProf (reads V$ views)
- LatchProfX (reads X$ tables, but gives better info, run as SYS)
Example output (with SQLID info) is below:
SQL> @latchprof name,sid,sqlid % % 100000 -- LatchProf 1.21 by Tanel Poder ( ) 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 [/sql] If you want to read more about the capabilities of LatchProf and LatchProfX, go here:
- </2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/>
- </2008/07/23/advanced-oracle-troubleshooting-guide-part-8-even-more-detailed-latch-troubleshooting-using-latchprofx/>
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…
