This section shows some example SQL that can be used to help find potential issues in the shared pool. The output of these statements should be spooled to a file.
Note: These statements may add to any latch contention as described in "Using V$ Views (V$SQL and V$SQLAREA)" above.
SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30 ORDER BY 2 ;This helps find commonly used literal SQL - See "Eliminating Literal SQL" above.
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses
SELECT hash_value, count(*) FROM v$sqlarea GROUP BY hash_value HAVING count(*) > 5 ;This should usually return no rows. If there are any HASH_VALUES with high counts (double figures) then you may be seeing the effects of a bug, or an unusual form of literal SQL statement. It is advisable to drill down and list out all the statements mapping to the same HASH_VALUE. Eg:
SELECT sql_text FROM v$sqlarea WHERE hash_value= <XXX>;and if these look the same get the full statements from V$SQLTEXT. It is possible for many literals to map to the same hash value. Eg: In 7.3 two statements may have the same hash value if a literal value occurs twice in the statement and there are exactly 32 characters between the occurrences.
SELECT address, hash_value, version_count , users_opening , users_executing, substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE version_count > 10 ;"Versions" of a statement occur where the SQL is character for character identical but the underlying objects or binds etc.. are different as described in "Sharable SQL" above. High version counts can occur in various Oracle8i releases due to problems with progression monitoring. This can be disabled by setting _SQLEXEC_PROGRESSION_COST to '0' as described earlier in this note.
SELECT substr(sql_text,1,40) "Stmt", count(*), sum(sharable_mem) "Mem", sum(users_opening) "Open", sum(executions) "Exec" FROM v$sql GROUP BY substr(sql_text,1,40) HAVING sum(sharable_mem) > <MEMSIZE> ;where MEMSIZE is about 10% of the shared pool size in bytes. This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.
SELECT * FROM x$ksmlru WHERE ksmlrnum>0 ;
DBA FUS >