DBA FUS‎ > ‎

Useful SQL for looking at Shared Pool problems

posted Jul 26, 2011, 8:09 AM by Sachchida Ojha
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.
  • Finding literal SQL
  •           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.
  • Finding the Library Cache hit ratio
    If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses
  • Checking hash chain lengths:
  •         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.
  • Checking for high version counts:
  •         SELECT address, hash_value,
                    version_count ,
                    users_opening ,
                    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.
  • Finding statement/s which use lots of shared pool memory:
  •         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.
  • Allocations causing shared pool memory to be 'aged' out
  •         SELECT * 
              FROM x$ksmlru
            WHERE ksmlrnum>0