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

http://www.bash-dba.com/2012/01/keeping-plsql-in-oracle-memory.html

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

No comments:

Post a Comment