Home » RDBMS Server » Performance Tuning » Predicate Informationn in PLAN (Oracle 10.2.0.3)
Predicate Informationn in PLAN [message #425033] Wed, 07 October 2009 04:26 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
When used with Rule hint a SQL is performing faster compared to
the same sql without hint.

When observed I found the below predicates in SHOW PLAN for the
sql with no hint ( In the plan with Rule I did not find the below predicate)

Does this predicate below may lead to performance issue?

Thanks



Predicate Information (identified by operation id):
---------------------------------------------------

13 - filter("D"."LOCATION_ID" IS NOT NULL)

Re: Predicate Informationn in PLAN [message #425037 is a reply to message #425033] Wed, 07 October 2009 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Predicate Informationn in PLAN [message #425060 is a reply to message #425033] Wed, 07 October 2009 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the RBO performs better than the CBO, then it generally suggests that your statistics are out of date.

The predicate information is not present in the RBO plan because the RBO does not bother with minor details like values of columns when composing it's execution plan.
Re: Predicate Informationn in PLAN [message #425071 is a reply to message #425060] Wed, 07 October 2009 06:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes the stats are collected 2 days back.
Thanks you very much for the advice!
Re: Predicate Informationn in PLAN [message #425123 is a reply to message #425071] Wed, 07 October 2009 11:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post the plans of the two versions?

You're not doing something silly like running the query normally, and then running the RBO version, and noticing only the performance improvement caused by the blocks all being in memory for the second query, are you?
Previous Topic: Slow performance for Some queries
Next Topic: Hints within a query that reads from view
Goto Forum:
  


Current Time: Thu Nov 14 05:08:13 CST 2024