Code: [Copy to clipboard]
set echo off
Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN
0drop 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
0drop 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 0select 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 0select 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
0select p.owner, p.name,
sum(s.executions) totexec
from sqltemp s,
(0select 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)
0delete from plan_table
where statement_id in
( 0select addr from sqltemp ).
0drop table sqltemp. |