Tuning Contention - Oracle Database 11gR2 Performance Tuning - page 458

4. Connect as SYSDBA and execute the WORKLOAD_NOBIND procedure, executing the

query in step 2 before and after the procedure execution to monitor latch contention:
CONNECT / AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT
EVENT, TOTAL_WAITS,
TIME_WAITED, AVERAGE_WAIT, TOTAL_TIMEOUTS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;

exec sh.CHAPTER4.WORKLOAD_NOBIND;

SELECT
EVENT, TOTAL_WAITS,
TIME_WAITED, AVERAGE_WAIT, TOTAL_TIMEOUTS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;

5. Execute the WORKLOAD_BIND procedure as in step 2 and compare the results

obtained with the ones in step 4:
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT
EVENT, TOTAL_WAITS,
TIME_WAITED, AVERAGE_WAIT, TOTAL_TIMEOUTS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;

exec sh.CHAPTER4.WORKLOAD_BIND;

SELECT
EVENT, TOTAL_WAITS,
TIME_WAITED, AVERAGE_WAIT, TOTAL_TIMEOUTS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'latch:%' ORDER BY EVENT;

6. Drop the package CHAPTER4 :

DROP PACKAGE sh.CHAPTER4;