Home » RDBMS Server » Performance Tuning » Hints (Oracle 11g)
Hints [message #666276] Wed, 25 October 2017 06:36 Go to next message
Messages: 1
Registered: October 2017
Junior Member
HI , I have attached the explain plan for a query, i have used parallel hints and /*+ materialize ordered full(edge) */ hints
please help me how to analyse the explain plan and please advise which is best plan to continue further

i have attached the explain plans with both hints .
please help

Re: Hints [message #666282 is a reply to message #666276] Wed, 25 October 2017 07:35 Go to previous messageGo to next message
John Watson
Messages: 8880
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I see no reason to use any hints at all. Get rid of any that you have already put in, and trust the optimizer.

You just need to tune the SQL. I would start by removing what I suspect are unnecessary aggregations that are preventing view and subquery merging. For example, look at operations 1 and 2; 8 and 9; 87 and 88; I'm sure there are others. In these cases, Oracle does not think that the aggregations (or use of DISTINCT, perhaps) will remove any rows, but you are forcing Oracle to materialize the views and aggregate them anyway.

--update: it also looks as though you have some columns wrapped in functions which will suppress use of indexes, this sort of thing,
   7 - filter("ERP"."ORG_UNIT_ID"=10223 AND TRUNC(INTERNAL_FUNCTION("ERP"."ERP_PMT_DATE"))>=TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("ERP"."ERP_PMT_DATE"))<=TO_DATE(' 2017-10-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
you need to check the data types of the columns and filters, and rewrite the predicate to avoid the need for wrapping the columns in functions.

[Updated on: Wed, 25 October 2017 07:40]

Report message to a moderator

Re: Hints [message #666285 is a reply to message #666282] Wed, 25 October 2017 07:46 Go to previous messageGo to next message
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
This is going to sound harsh however it must be said.

If you cannot understand an execution plan you have no place whatsoever hinting a query.

Hints are directives.
Hints are you saying "I'm better at this than the database and all the R&D which went into it"
Hints are you saying "I understand the optimizer inside and out and am comfortable with overruling it"
Hints are you saying "I know and can demonstrate exactly why this went wrong, why all other avenues to correct this have been exhausted and ruled out"

IF you can't do all of the above, stop hinting and study the database, how it works, how it makes decisions and how you can work with it to improve your system.
Re: Hints [message #666287 is a reply to message #666285] Wed, 25 October 2017 07:53 Go to previous message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
With regards to hints, DON'T

Previous Topic: using sysdate from dual takes longer time
Next Topic: Performance Issue
Goto Forum:

Current Time: Sun Oct 01 00:32:18 CDT 2023