-- heavyhit.sql -- ============ -- Created by Doug Kiz (change_on_install@yahoo.com) -- -- This script will allow you to search the shared pool -- for "expensive" SQL. A list of possible criteria -- are presented, but any column in V$SQL can be used. -- -- Results are printed one statement per page in descending -- order. -- -- If the full statement is available in memory, you can -- get a full listing. Just cut and paste the "drilldown" -- script (showSQL.sql) listed in the report. -- -- You may use and distribute this script free-of-charge -- under two conditions: -- -- 1) You retain these credits -- 2) You do not charge for this script or any -- derivative work. -- -- As I have given, so you should give. -- ======================= Prompt Acceptable hit criteria ("normal" values for 10-30 GB database) prompt - BUFFER_GETS (< 1,000,000) prompt - DISK_READS (< 250,000) prompt - EXECUTIONS (1-2000+) prompt - ROWS_PROCESSED (1-2000+) prompt - OPTIMIZER_COST (1-100) (could be NULL) prompt - CPU_TIME (1 sec = 1,000,000) prompt - ELAPSED_TIME (1 sec = 1,000,000) prompt prompt (BTW: don't use commas in your input) prompt accept hit_criterion char prompt " Which hit criterion do you want to use? " accept criterion_level number prompt " What value do you want to use for a lower limit? " break on row skip page set heading off set pause on set pause "press to continue . . ." column cpu_ noprint heading "CPU Time (us)" new_value cpu_var column ela_ noprint heading "Elapsed (us)" new_value ela_var column cpu_exe_ noprint heading "CPU/exec" new_value cpu_exec_var column ela_exe_ noprint heading "Elapsed/exec" new_value ela_exec_var column shar_mem_ noprint heading "Shared" new_value shar_mem_var column pers_mem_ noprint heading "Persistent" new_value pers_mem_var column run_mem_ noprint heading "Run Time" new_value run_mem_var column sorts_ noprint heading "Sorts" new_value sorts_var column u_open_ noprint heading "Users Opening" new_value u_open_var column execs_ noprint heading "Executions" new_value execs_var column loads_ noprint heading "Loads" new_value loads_var column parse_ noprint heading "Parses" new_value parse_var column disk_ noprint heading "Disk" new_value disk_var column buffr_ noprint heading "Buffer" new_value buffr_var column rows_ noprint heading "Rows" new_value rows_var column cost_ noprint heading "Cost" new_value cost_var column user_ noprint heading "Parsing User" new_value user_var column curr_usr_ noprint heading "Current User" new_value curr_usr_var column bhr_ noprint heading "BHR" new_value bhr_var column bh_exec_ noprint heading "Buffer/exec" new_value bh_exec_var column dsk_exe_ noprint heading "Disk/exec" new_value disk_exec_var column row_exe_ noprint heading "Rows/exec" new_value row_exec_var column hash_ noprint heading "Hash Val" new_value hash_var column addr_ noprint heading "Address" new_value addr_var column loaded_ noprint heading "Loaded" new_value loaded_var column open_ noprint heading "Open Csr" new_value open_var column first_ noprint heading "First Loaded" new_value first_var column sql_ format a120 word_wrapped ttitle - col 10 'MEMORY' col 30 'RESOURCES' col 50 'OPTIMIZATION' col 74 'STATEMENT USAGE' col 95 'TIME USED (sec)' skip - col 10 '------' col 30 '---------' col 50 '------------' col 74 '---------------' col 95 '---------------' skip 2 - col 1 ' Shareable ' FORMAT 9,999,990 shar_mem_var - col 25 'Buffer' FORMAT 999,999,990 buffr_var - col 50 ' Cost' FORMAT 99,990 cost_var - col 95 ' CPU ' FORMAT 9,999,990.000 cpu_var - col 70 ' #/Users' FORMAT 9,999,990 u_open_var skip - col 1 'Persistent ' FORMAT 9,999,990 pers_mem_var - col 25 ' Disk' FORMAT 999,999,990 disk_var - col 50 'Sorts' FORMAT 99,990 sorts_var - col 95 'Elapsed ' FORMAT 9,999,990.000 ela_var - col 70 ' Parses' FORMAT 9,999,990 parse_var skip - col 1 ' Run Time ' FORMAT 9,999,990 run_mem_var - col 25 ' Rows' FORMAT 999,999,990 rows_var - col 50 ' BHR ' FORMAT 0.000 bhr_var - col 70 ' Loads' FORMAT 9,999,990 loads_var skip - col 1 ' ' - col 70 'Executions' FORMAT 9,999,990 execs_var - skip - col 1 ' ' - col 10 'Loaded: ' loaded_var - col 34 'Buffer/Execution: ' FORMAT 9,999,990 bh_exec_var - skip - col 1 ' ' - col 12 'Open: ' open_var - col 34 ' Disk/Execution: ' FORMAT 9,999,990 disk_exec_var - col 88 ' CPU/Execution: ' FORMAT 9,990.000 cpu_exec_var - skip - col 1 ' ' - col 4 'Current User: ' curr_usr_var - col 34 ' Rows/Execution: ' FORMAT 9,999,990 row_exec_var - col 88 'Elapsed/Execution: ' FORMAT 9,990.000 ela_exec_var - skip - skip - col 20 'Parsing User: ' FORMAT a30 user_var - col 70 'First Loaded: ' first_var - skip - col 65 '@showSql 'hash_var' 'addr_var skip - skip 2 select sql.SQL_TEXT sql_ , sql.cpu_time/1000000 cpu_ , sql.elapsed_time/1000000 ela_ , sql.SHARABLE_MEM shar_mem_ , sql.PERSISTENT_MEM pers_mem_ , sql.RUNTIME_MEM run_mem_ , sql.SORTS sorts_ , sql.USERS_OPENING u_open_ , sql.EXECUTIONS execs_ , sql.LOADS loads_ , sql.PARSE_CALLS parse_ , sql.DISK_READS disk_ , sql.BUFFER_GETS buffr_ , sql.ROWS_PROCESSED rows_ , sql.FIRST_LOAD_TIME first_ , NVL(sql.OPTIMIZER_COST,0) cost_ , u.NAME user_ , s.username curr_usr_ , 1 - (sql.DISK_READS/greatest(sql.BUFFER_GETS,1)) bhr_ , sql.BUFFER_GETS/greatest(sql.EXECUTIONS,1) bh_exec_ , sql.DISK_READS/greatest(sql.EXECUTIONS,1) dsk_exe_ , sql.ROWS_PROCESSED/greatest(sql.EXECUTIONS,1) row_exe_ , (sql.elapsed_time/1000000)/greatest(sql.EXECUTIONS,1) ela_exe_ , (sql.cpu_time/1000000)/greatest(sql.EXECUTIONS,1) cpu_exe_ , sql.address addr_ , sql.hash_value hash_ , decode(sql.LOADED_VERSIONS, 1, 'YES', 0, 'NO') loaded_ , decode(sql.OPEN_VERSIONS, 1, 'YES', 0, 'NO') open_ from v$sql sql , sys.user$ u , v$session s where u.user# = sql.parsing_user_id and &hit_criterion > NVL(&criterion_level, 500000) and sql.address=s.sql_address (+) and sql.hash_value=s.sql_hash_value (+) order by &hit_criterion desc / @isql_prefs