Tom,

EXACTLY what I am looking for!

When I first got your "Effective Oracle by Design" we were not running 9i on our main database so I sort of skipped over the 9i specific stuff. While reviewing your book again over the weekend I saw this very thing you reference.
Thank you for the confimation of this technique by answering my question!

Thanks!

Robert.

Unfortunately I'm at a site where I dont have any create privileges and am limited to:

SELECT ANY DICTIONARY
CREATE SESSION

so I couldn't construct the view. As a quick try I created an SQL script SQLPLAN_LIST.sql:

column "Operation" format A40
column "Object Name" format A30
column "PStart" format A6
column "PStop" format A6
column "I" format A3
column ACCESS_PREDICATES format A80
column FILTER_PREDICATES format A80
set lines 9999
set pages 99
set trimspool on
set verify off
set heading on

select  /*+ NO_MERGE */ rownum  || decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
        substr(lpad(' ',2*(depth-1)) || operation,1,20) || 
        decode(options,NULL,'',' (' || options || ')') "Operation",
        substr(object_name,1,30) "Object Name",
        cardinality "# Rows",
        bytes,
        cost,
        partition_start "PStart",
        partition_stop "PStop"
  from (select * from v$sql_plan where hash_value = &&1) a
  start with id = 0
  connect by prior id = parent_id
      and prior nvl(hash_value, 0 ) = nvl(hash_value, 0 )
  order by id, position
/
set heading off
select 'Access Predicates .....' from dual;
select * from (
select  /*+ NO_MERGE */ rownum  || decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
    decode(access_predicates,NULL,'###',access_predicates) "ACCESS_PREDICATES"
  from (select * from v$sql_plan where hash_value = &&1) a
  start with id = 0
  connect by prior id = parent_id
      and prior nvl(hash_value, 0 ) = nvl(hash_value, 0 )
  order by id, position
) where access_predicates <> '###'
/
set heading off
select 'Filter predicates .....' from dual;
select * from (
select  /*+ NO_MERGE */ rownum  || decode(access_predicates,NULL,decode(filter_predicates,NULL,'','*'),'*') "I",
    decode(filter_predicates,NULL,'###',filter_predicates) "FILTER_PREDICATES"
  from (select * from v$sql_plan where hash_value = &&1) a
  start with id = 0
  connect by prior id = parent_id
      and prior nvl(hash_value, 0 ) = nvl(hash_value, 0 )
  order by id, position
) where filter_predicates <> '###'
/


and then in use : 

SQL> @sqlplan_list 2942443644

1   SELECT STATEMENT                                                                                      60
2   SORT (UNIQUE)                                                                    6       1038         60
3     UNION-ALL
4       NESTED LOOPS                                                                 1        179         22
5         NESTED LOOPS                                                               1        163         21
6*          TABLE ACCESS (FULL)              DBT_C_I_REPLY_Q                         1        133         20
7           TABLE ACCESS (BY INDEX ROWID)    DBT_C_I_CONT_MSG                        1         30          1
8*            INDEX (UNIQUE SCAN)            DBO_C_I_CONT_MSG_01                     1
9*        TABLE ACCESS (BY INDEX ROWID)      DBT_C_I_CTL_RQST                        1         16          1
10*         INDEX (UNIQUE SCAN)              DBO_C_I_CTL_RQST_01                     1

Access Predicates .....

8*  "CONT"."BRCH_CODE"=:B0 AND "CONT"."SEQ_ID"="REPLY"."SEQ_ID"
10* "RQST"."BRCH_CODE"=:B0 AND "RQST"."RQST_ID"="CONT"."RQST_ID"

Filter predicates .....

6*  "REPLY"."BRCH_CODE"=:B0 AND ("REPLY"."STAT_CODE"=:B1 OR "REPLY"."STAT_CODE"=:B2)
9*  NVL("RQST"."STAT_CODE",'XX')<>'01' AND NVL("RQST"."STAT_CODE",'XX')<>'07'

The above had some lines deleted to make it more readable.

Provided here just in case it comes in handy for someone else in a similar position

with dynamic_plan_table
as
( select
rawtohex(address) || '_' || child_number statement_id,
sysdate timestamp, operation, options, object_node,
object_owner, object_name, 0 object_instance,
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, access_predicates,
filter_predicates
from v$sql_plan)
select plan_table_output
from TABLE( dbms_xplan.display
( 'dynamic_plan_table',
(select rawtohex(address)||'_'||child_number x
from v$sql
where sql_text='select * from t t1 where object_id > 32000' ),
'serial' ) );


This gives me an error saying

ERROR at line 14:
ORA-32035: unreferenced query name defined in WITH clause

Thanks, Could you please tell me how to write the query then if I dont have permission to create view?

Thanks

Hi Tom,

Greetigns!. Is there a way we could use dbms_xplan without creating this view, we are constrained by the oracle 9i environment.

The intent is to validate the plan on production to the development environment are the same / nearly the same.

Please enlighten us.

Thanks & Regards,
Bala

"The text in italics above, is a query that gets the STATEMENT_ID"

This is no text in italics above. Please don't waste my time

Hi Tom,

Above response gave rise to a doubt.Is there a difference bwteen plan generated using trace file(I mean in tkprof) and exection plan found in v$sql_plan ?

Thanks,
Girish

We had been assigned a task of Performance Statistics gathering in Production DBs.
For the same we followed the steps mentioned below

create temp table to hold the sql stats and plan namely sql_stats_temp,
and another table temp_tab with data FROM v$sql_plan,sql_stats_temp and joined it using rawtohex(address) & child_number of v$sql_plan = rawtohex(address) of sql_stats_temp .
alter sql_stats_temp,to add clob column for storing sql plan
and then wrote a script will fetch the sql stats and plan and load into sql_stats_temp table.
select plan_table_output
from TABLE( dbms_xplan.display( 'TEMP22_TABLE',rec1.sql_id,'serial')
)
).

But We get the No more data to read from socket error.

This is because of the size allocated to access_predicates,filter_predicates which is 4000.


--drop temp table
drop table sql_stats_temp;
drop table TEMP22_TABLE;

-- create temp table to hold the sql stats and plan
create table sql_stats_temp
as
SELECT *
FROM ( select v.Hash_value,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.ROWS_PROCESSED,
v.disk_reads,
v.buffer_gets,
v.executions,
v.first_load_time,
v.last_load_time,
v.address,
v.child_number
from v$sql v
where upper(v.sql_text) NOT like '%SYS%'
and upper(v.sql_text) NOT like'%DBMS_%'
and v.sql_text NOT like'%$%'
and upper(v.sql_text) NOT like'%SESSION%'
and upper(v.sql_text) NOT like'%TEMP22%'
and upper(v.sql_text) NOT like'%SQL_STATS_TEMP%'
and upper(v.sql_text) NOT like'%JAVA%'

-- where rawtohex(v.address)||'_'||child_number = t.statement_ID
order by elapsed_time DESC
)
WHERE rownum <= 1000;


CREATE table TEMP22_TABLE (STATEMENT_ID, TIMESTAMP, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_INSTANCE, 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, ACCESS_PREDICATES, FILTER_PREDICATES)
AS
SELECT rawtohex(vp.address)
|| '_'
|| vp.child_number statement_id,
sysdate TIMESTAMP,
vp.operation,
vp.OPTIONS,
vp.object_node,
vp.object_owner,
vp.object_name,
0 object_instance,
vp.optimizer,
vp.search_columns,
vp.id,
vp.parent_id,
vp.position,
vp.cost,
vp.cardinality,
vp.bytes,
vp.other_tag,
vp.partition_start,
vp.partition_stop,
vp.partition_id,
vp.other,
vp.distribution,
vp.cpu_cost,
vp.io_cost,
vp.temp_space,
vp.access_predicates,
vp.filter_predicates
FROM v$sql_plan vp,
sql_stats_temp st
WHERE rawtohex(vp.address)
|| '_'
|| vp.child_number = rawtohex(st.address)
|| '_'
|| st.child_number;


--alter table to add clob column for storing sql plan
ALTER TABLE sql_stats_temp
ADD (sqlplan CLOB)

/

select * from V$sql where address='000000039B1CB0A0'


-- The below script will fetch the sql stats and plan and load into sql_stats_temp table.
Declare
v_plan CLOB;
Begin
FOR rec1 IN (select rawtohex(address)||'_'||child_number sql_id from sql_stats_temp) LOOP

v_plan:= NULL;

-- The below loop will get the plan of the sql statement and update the sqlplan clob column
FOR rec2 IN (

select plan_table_output
from TABLE( dbms_xplan.display( 'TEMP22_TABLE',rec1.sql_id,'serial')
)
) LOOP

v_plan:= v_plan||' '||rec2.plan_table_output;


END LOOP;
update sql_stats_temp
set sqlplan = v_plan
where rawtohex(address)||'_'||child_number = rec1.sql_id;
COMMIT;
END LOOP;
END;
/
--select * from sql_stats_temp