Tuesday, January 17, 2012

Script to identify large pl/sql anonymous blocks

To  improve cache hit ratio, use below script to  find the anonymous blocks that can be changed into procedures/functions for subsequent pinning in the shared pool reserved area. On how to pin the objects in memory, see my article at


SQL> SELECT SUBSTR(sql_text,1,45), LENGTH(sql_text) 
  2  FROM v$sqlarea
  3  WHERE command_type = 47
  4  ORDER BY LENGTH(sql_text) DESC;

No comments:

Post a Comment