| Help - Stored Outline [message #305715] |
Tue, 11 March 2008 12:32  |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
