Home » RDBMS Server » Performance Tuning » Help - Stored Outline (Oracle 9i R2 on RHEL)
Help - Stored Outline [message #305715] Tue, 11 March 2008 12:32 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hi,

I am using Oracle 9i R2 on RHEL

I am trying to create stored Outline for (statements in) a Procedure (which in turn calls some other procedures)

In order to do this first I tried to create Stored Outline for a SQL Statement - Select Query, which worked
Then I tried similar thing with Procedure. But Alas ! It did not work.

Following is the process I followed for the same.

Can anybody please advice me where I am wrong?

1)Stored Outline for Select Query

Checked Execution plan for a query on application table
select * from process_log where dtprocess='03-mar-2008';
...It uses Index path

created test table from application table without Primary key and Index
create table process_log2 as select * from process_log;
...Full Table Scan

Created Store outline - Default Category
alter session set create_stored_outlines=true;
alter session set events '10046 trace name context forever, level 12';
select * from process_log2 where dtprocess='03-mar-2008';
alter session set events '10046 trace name context off';
alter session set create_stored_outlines=false;

Created Index Primary Key and Index on Test table
alter table process_log2 add (
constraint xpkprocess_log2 primary key (nprocessid, dtprocess, strsubprocessid)
using index tablespace hsaindx);

Gathered Stats on the Test table with cascade=True
exec dbms_stats.gather_table_stats('HSASYS','process_log2',CASCADE=>TRUE);

Confirmed that the new index is being picked up -- since use_stored_outlines=true haven't set yet
alter session set events '10046 trace name context forever, level 12';
select * from process_log2 where dtprocess='03-mar-2008';
alter session set events '10046 trace name context off';


Again checked the Execution Plan to confirm if stored outline is being used
alter session set use_stored_outlines=true;
alter session set events '10046 trace name context forever, level 12';
select * from process_log2 where dtprocess='03-mar-2008';
alter session set events '10046 trace name context off';
alter session set use_stored_outlines=true;
... TKprof showed Full Table Scan, thus Stored Outline was used.
Explain Plan and ROW Source both showed same Plan

So this Test worked
Now I tried replicating same for a Stored Procedure

Created Another Test table and a stored procedure using the Application Table

Create table process_log3 as select * from process_log;

Create or replace procedure cpl2 as
x number;
begin
select count(1) into x from process_log3 where dtprocess='03-mar-2008';
for i in (select * from process_log3 where dtprocess='03-mar-2008')
loop
null;
end loop;
end;
/

Created Store outline - Default Category
alter session set create_stored_outlines=true;
alter session set events '10046 trace name context forever, level 12';
exec cpl2;
alter session set events '10046 trace name context off';
alter session set create_stored_outlines=false;


Created Index Primary Key and Index on Test table
alter table process_log3 add (
constraint xpkprocess_log3 primary key (nprocessid, dtprocess, strsubprocessid)
using index tablespace hsaindx);

Gathered Stats on the Test table with cascade=True
exec dbms_stats.gather_table_stats('HSASYS','process_log3',CASCADE=>TRUE);

Checked the Execution Plan to confirm if stored outline is being used
alter session set use_stored_outlines=true;
alter session set events '10046 trace name context forever, level 12';
exec cpl2;
alter session set events '10046 trace name context off';
alter session set use_stored_outlines=false;

....Here Tkprof showed that it was using Index Path meaning Stored Outline wasn't used though user_stored_outline.USED shows 'USED'

Moreover this time ROW SOURCE didn't appear in the Trace file only Explian Plan appeared.
I used following command options with tkprof sys=no explain=sql_session_user/password

Can anybody please advice me where I am wrong?

Thanks and Regards,
OraKaran
Re: Help - Stored Outline [message #306066 is a reply to message #305715] Wed, 12 March 2008 13:15 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello,

Anybody please reply.

I am just trying to create Stored Outline for a Stored Procedure.

Thanks and Regards,
OraKaran
Re: Help - Stored Outline [message #306089 is a reply to message #305715] Wed, 12 March 2008 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>It did not work.
While the statement is likely true, it is NOT helpful for anyone to give you advice.

My car does not work.
Please tell me how to make it go.

It would benefit everyone if you read & followed the posting guidelines as found in the STICKY post at the top of this forum.
Re: Help - Stored Outline [message #306126 is a reply to message #305715] Wed, 12 March 2008 23:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
See if this helps:

http://orafaq.com/maillist/oracle-l/2002/12/24/1708.htm
Re: Help - Stored Outline [message #306556 is a reply to message #305715] Fri, 14 March 2008 12:25 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Many Thanks Barbara,

I will check/ update privilges, try it back

Thanks and Regards,
OraKaran
Previous Topic: Waits During Inserts .
Next Topic: Doubt regarding ORA-30036: unable to extend segment by 8 in undo tablespace
Goto Forum:
  


Current Time: Fri Dec 02 14:20:18 CST 2016

Total time taken to generate the page: 0.23426 seconds