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;

　　
            select object_owner, object_name, options, count(*)
            　　from　 v$sql_plan
            　　where　operation='INDEX'
            　　and　　object_owner!='SYS'
            　　group　by　object_owner, object_name, operation, options
            　　order　by count(*) desc;

　　
            　　(a) Create and populate a small test table
            　　create table products　　(
            　　prod_id number(3),
            　　prod_name_code varchar2(5));
            　　
            　　insert into products values(1,'aaaaa');
            　　insert into products values(2,'bbbbb');
            　　insert into products values(3,'ccccc');
            　　insert into products values(4,'ddddd');
            　　commit;
            　　
            　　(b) Create Primary Key index on that table
            　　alter table products　　add (constraint products_pk primary key (prod_id));
            　　
            　　(c) Query v$object_usage: the monitoring has not started yet
            　　column