Code: [Copy to clipboard]

　　set echo off

　　Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN

　　drop table plan_table;

　　create table PLAN_TABLE (

　　statement_id　　　　 varchar2(30),

　　timestamp　　　　　　date,

　　remarks　　　　　　　varchar2(80),

　　operation　　　　　　varchar2(30),

　　options　　　　　　　 varchar2(255),

　　object_node　　　　　varchar2(128),

　　object_owner　　　　 varchar2(30),

　　object_name　　　　　varchar2(30),

　　object_instance　　　　numeric,

　　object_type　　　　 varchar2(30),

　　optimizer　　　　　 varchar2(255),

　　search_columns　　　　 number,

　　id　　　　　　　　　　　　numeric,

　　parent_id　　　　　　　　numeric,

　　position　　　　　　　　numeric,

　　cost　　　　　　　　numeric,

　　cardinality　　　　　　　　numeric,

　　bytes　　　　　　　　numeric,

　　other_tag　　　　　　varchar2(255),

　　partition_start　　 varchar2(255),

　　partition_stop　　　varchar2(255),

　　partition_id　　　　numeric,

　　other　　　　　　　　long,

　　distribution　　　　varchar2(30),

　　cpu_cost　　　　　　　　numeric,

　　io_cost　　　　　　　　numeric,

　　temp_space　　　　　　　　numeric,

　　access_predicates　 varchar2(4000),

　　filter_predicates　 varchar2(4000));

　　

　　Rem Drop and recreate SQLTEMP for 

    taking a snapshot of the SQLAREA

　　drop table sqltemp;

　　create table sqltemp　 (

　　ADDR　　　　　　　　 VARCHAR2 (16),

　　SQL_TEXT　　　　　　　　 VARCHAR2 (2000),

　　DISK_READS　　　　　　　　NUMBER,

　　EXECUTIONS　　　　　　　　NUMBER,

　　PARSE_CALLS　　　　 NUMBER);

　　

　　set echo on

　　Rem Create procedure to populate 

    the plan_table by executing

　　Rem explain plan...for 'sqltext' dynamically

　　create or replace procedure do_explain (

　　addr IN varchar2, sqltext IN varchar2)

　　as dummy varchar2 (1100);

　　mycursor integer;

　　ret integer;

　　my_sqlerrm varchar2 (85);

　　begin dummy:='EXPLAIN PLAN 

    SET STATEMENT_ID=' ;

　　dummy:=dummy||''''||addr||''''

    ||' FOR '||sqltext;

　　mycursor := dbms_sql.open_cursor;

　　dbms_sql.parse(mycursor,dummy,dbms_sql.v7);

　　ret := dbms_sql.execute(mycursor);

　　dbms_sql.close_cursor(mycursor);

　　commit;

　　exception -- Insert errors into 

    PLAN_TABLE...

　　when others then my_sqlerrm :=

    substr(sqlerrm,1,80);

　　insert into plan_table(statement_id,

    remarks) values (addr,my_sqlerrm);

　　-- close cursor if exception 

    raised on EXPLAIN PLAN

　　dbms_sql.close_cursor(mycursor);

　　end;

　　/

　　

　　Rem Start EXPLAINing all S/I/U/D 

    statements in the shared pool

　　declare

　　-- exclude statements with 

    v$sqlarea.parsing_schema_id = 0 (SYS)

　　cursor c1 is select address, sql_text, 

    DISK_READS, EXECUTIONS, PARSE_CALLS

　　from v$sqlarea

　　where command_type in (2,3,6,7)

　　and parsing_schema_id != 0;

　　cursor c2 is select addr, 

    sql_text from sqltemp;

　　addr2　　　　　varchar(16);

　　sqltext　　　　v$sqlarea.sql_text%type;

　　dreads　　　　 v$sqlarea.disk_reads%type;

　　execs　　　　　v$sqlarea.executions%type;

　　pcalls　　　　 v$sqlarea.parse_calls%type;

　　begin open c1;

　　fetch c1 into addr2,sqltext,

    dreads,execs,pcalls;

　　while (c1%found) loop

　　insert into sqltemp values

    (addr2,sqltext,dreads,execs,pcalls);

　　commit;

　　fetch c1 into addr2,

    sqltext,dreads,execs,pcalls;

　　end　　loop;

　　close　c1;

　　open　c2;

　　fetch c2 into addr2, sqltext;

　　while (c2%found) loop

　　do_explain(addr2,sqltext);

　　fetch c2 into addr2, sqltext;

　　end　 loop;

　　close c2;

　　end;

　　/

　　

　　Rem Generate a report of index 

    usage based on the number of times

　　Rem a SQL statement using 

    that index was executed

　　select p.owner, p.name, 

    sum(s.executions) totexec

　　from sqltemp s,

　　(select distinct statement_id stid, 

    object_owner owner, object_name name

　　from plan_table where operation = 'INDEX') p

　　where s.addr = p.stid

　　group by p.owner, p.name

　　order by 2 desc;

　　

　　Rem Perform cleanup on exit (optional)

　　delete　from plan_table

　　where　statement_id in

　　( select　addr　from sqltemp );

　　drop table sqltemp;