unstable_plans.sql – this one shows statements that have multiple plans with large variance in execution time awr_plan_change.sql – history of how the statement plan changes over time awr_plan_stats.sql – aggregate statement stats (like elapsed_time, lio, etc) grouped by plan find_sql_acs.sql – A queries v$sql and shows ACS related columns mismatch3.sql – A variation of Dion Cho’s script to display data from v$sql_shared_cursor (see his post here)
---------------------------------------------------------------------------------------- -- -- File name: unstable_plans.sql -- -- Purpose: Attempts to find SQL statements with plan instability. -- -- Author: Kerry Osborne -- -- Usage: This scripts prompts for two values, both of which can be left blank. -- -- min_stddev: the minimum "normalized" standard deviation between plans -- (the default is 2) -- -- min_etime: only include statements that have an avg. etime > this value -- (the default is .1 second) -- -- See http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/ for more info. --------------------------------------------------------------------------------------- set lines 155 col execs for 999,999,999 col min_etime for 999,999.99 col max_etime for 999,999.99 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col norm_stddev for 999,999.9999 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select * from ( select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev from ( select sql_id, plan_hash_value, execs, avg_etime, stddev(avg_etime) over (partition by sql_id) stddev_etime from ( select sql_id, plan_hash_value, sum(nvl(executions_delta,0)) execs, (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 group by sql_id, plan_hash_value ) ) group by sql_id, stddev_etime ) where norm_stddev > nvl(to_number('&min_stddev'),2) and max_etime > nvl(to_number('&min_etime'),.1) order by norm_stddev /
---------------------------------------------------------------------------------------- -- -- File name: awr_plan_change.sql -- --------------------------------------------------------------------------------------- set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /
---------------------------------------------------------------------------------------- -- -- File name: awr_plan_stats.sql -- --------------------------------------------------------------------------------------- -- Note that I have modified this script slightly to include snaps with 0 executions. -- This is to account for situations with very long running statements (that generally -- cross snapshot boundaries). In these situations, the executions_delta is incremented -- in the snapshot when the statement begins. There will be 0 executions_delta in -- subsequent snapshots, but the time and lio's should still be considered. set lines 155 col execs for 999,999,999 col etime for 999,999,999.9 col avg_etime for 999,999.999 col avg_cpu_time for 999,999.999 col avg_lio for 999,999,999.9 col avg_pio for 9,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select sql_id, plan_hash_value, sum(execs) execs, -- sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(cpu_time)/sum(execs) avg_cpu_time, sum(lio)/sum(execs) avg_lio, sum(pio)/sum(execs) avg_pio from ( select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, elapsed_time_delta/1000000 etime, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, buffer_gets_delta lio, disk_reads_delta pio, cpu_time_delta/1000000 cpu_time, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number -- and executions_delta > 0 ) group by sql_id, plan_hash_value order by 5 /
---------------------------------------------------------------------------------------- -- -- File name: find_sql_acs.sql -- --------------------------------------------------------------------------------------- set verify off set pagesize 999 col username format a13 col prog format a22 col sql_text format a35 col sid format 999 col child_number format 99999 heading CHILD col ocategory format a10 col execs format 9,999,999 col execs_per_sec format 999,999.99 col etime format 9,999,999.99 col avg_etime format 99,999.99 col cpu format 9,999,999 col avg_cpu format 99,999.99 col pio format 9,999,999 col avg_pio format 99,999.99 col lio format 9,999,999 col avg_lio format 9,999,999 col ibs format a3 col iba format a3 col ish format a3 select sql_id, child_number, plan_hash_value, is_bind_sensitive ibs, is_bind_aware iba, is_shareable ish, executions execs, rows_processed , -- executions/((sysdate-to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*(24*60*60)) execs_per_sec, -- elapsed_time/1000000 etime, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, -- cpu_time/1000000 cpu, (cpu_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_cpu, -- disk_reads pio, disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio, -- buffer_gets lio, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where sql_text like nvl('&sql_text',sql_text) and sql_text not like '%from v$sql where sql_text like nvl(%' and sql_id like nvl('&sql_id',sql_id) and is_bind_aware like nvl('&is_bind_aware',is_bind_aware) order by sql_id, child_number /
---------------------------------------------------------------------------------------- -- -- File name: mismatch3.sql -- --------------------------------------------------------------------------------------- -- Modified version of Dion Cho's script - http://dioncho.wordpress.com/?s=v%24sql_shared_cursor -- -- Modified by Kerry Osborne -- I just changed the output columns (got rid of sql_text and address columns and added last_load_time) -- I also ordered the output by last_load_time. -- declare c number; col_cnt number; col_rec dbms_sql.desc_tab; col_value varchar2(4000); ret_val number; begin c := dbms_sql.open_cursor; dbms_sql.parse(c, 'select q.sql_text, q.last_load_time, s.* from v$sql_shared_cursor s, v$sql q where s.sql_id = q.sql_id and s.child_number = q.child_number and q.sql_id like ''&sql_id'' order by last_load_time', dbms_sql.native); dbms_sql.describe_columns(c, col_cnt, col_rec); for idx in 1 .. col_cnt loop dbms_sql.define_column(c, idx, col_value, 4000); end loop; ret_val := dbms_sql.execute(c); while(dbms_sql.fetch_rows(c) > 0) loop for idx in 1 .. col_cnt loop dbms_sql.column_value(c, idx, col_value); if col_rec(idx).col_name in ('SQL_ID', 'CHILD_NUMBER','LAST_LOAD_TIME') then dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value); elsif col_value = 'Y' then dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value); end if; end loop; dbms_output.put_line('--------------------------------------------------'); end loop; dbms_sql.close_cursor(c); end; /
转自《http://kerryosborne.oracle-guy.com/2009/06/oracle-11g-adaptive-cursor-sharing-acs/》