Better way to create plan_table

Purpose
This example shows how to better create plan table.

Time Needed : 10min

1. @?/rdbms/admin/utlxplan.sql
2. Create user utils identified by xxxx
tablespace tools
3. grant create session,create table to utils
4. create plan table in util schema as global temporary table . (use step 1 to copy the definition). on commit preserver rows. So that two ppl with same id does not create proble,.
5. Create a public synonym for this plan table so all develoers can use this.no concurrecny issue/no blocking.

syntax is
explain plan set statement_id = ‘xxx’
[into owner.table_name]
for statement;

script to see the out put for plan is
@?/rdbms/admin/utlxpls.sql OR better in 9i

select plan_table_output
from table(dbms_xplan.display(‘plan_table’,null,’serial);

To see the plan for v$sql_plan is or create table dynamic_view as select…..v$sql_plan and use in dbms_xplan.
insert into plan_table
(statement_id,timestamp,remarks,operation,
options,object_node,object_owner,object_name,
optimizer,search_columns,id,parent_id,
position,cost,cardinality,bytes,other_tag,
partition_start,partition_stop,parition_id,other,distribution,cpu_cost,io_cost,temp_space)
select rawtohex(address)||’-‘||child_number,
sysdate,null,operation,options,
object_node,object_owner,object_name,
optimizer,search_columns,id,parent_id,
position,cost,cardinality,bytes,other_tag,
partition_start,partition_stop,partition_id,
other,distribution,cpu_cost,io_cost,temp_space
from v$sql_plan
where (address,child_number)
in
(select address,child_number from v$sql where sql_text like ‘select * from t where t1%’ and child_number =0)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: