OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » stored outline (9.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

stored outline [message #487183]

Thu, 23 December 2010 10:03

oragaurav
Messages: 3
Registered: December 2010

Junior Member

Hi experts,

i have an issues with stored outlines.i am on oracle 9.2.0.1

i created and outline as
CREATE OR REPLACE OUTLINE JOB1 FOR CATEGORY JOB_TBL2 ON
SELECT EMPLID FROM PS_JOB WHERE ACTION LIKE :1
then
ALTER SESSION SET use_stored_outlines=JOB_TBL2;
after that if i run the query SELECT EMPLID FROM PS_JOB WHERE ACTION LIKE :1 then my stored outline is used
but if i run as SELECT EMPLID FROM PS_JOB WHERE ACTION LIKE 'HIR' it doesnot use stored outline.

cursor sharing is similar.

Please help.

Report message to a moderator

Re: stored outline [message #487185 is a reply to message #487183]

Thu, 23 December 2010 10:10

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I would say that is the expected behaviour: you have a non-equality predicate, so with cursor_sharing=similar you wouldn't want the query re-written to match the outline.

Report message to a moderator

Re: stored outline [message #487195 is a reply to message #487183]

Thu, 23 December 2010 11:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, "LIKE <cste without %_>" is internally replace by "= <cste>" during the first steps of syntax analysis.

Regards
Michel

Report message to a moderator

Re: stored outline [message #487207 is a reply to message #487185]

Thu, 23 December 2010 21:13

oragaurav
Messages: 3
Registered: December 2010

Junior Member

Thanks Johnson,

i even tried with cursor_sharing exact\force but my problem is not resolved.

Please help.

Report message to a moderator

Re: stored outline [message #487208 is a reply to message #487207]

Thu, 23 December 2010 21:21

oragaurav
Messages: 3
Registered: December 2010

Junior Member

experts,

My main problem is my table ps_job is having different values to pass that generate dynamically.so when i change the value my access path is changed as i notice in explain plan and my query start taking 5 minutes to run from 50 seconds so,

i want to preserve my query access path also i am in 9.2.0.1 windows r2.

Report message to a moderator

Re: stored outline [message #487210 is a reply to message #487208]

Thu, 23 December 2010 22:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: stored outline [message #487230 is a reply to message #487208]

Fri, 24 December 2010 02:04

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I think you may be mistaking the purpose of the cursor_sharing parameter: it is not intended to force the same execution plan for different statements, it is meant to reduce the workload of repeated parsing. If you want to drive the optimizer to a particular plan, use hints.

Report message to a moderator

Previous Topic:	Optimizing select from view
Next Topic:	Clustering Factor in Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 07:22:15 CDT 2024