| Hints [message #666276] |
Wed, 25 October 2017 06:36  |
 |
sathishsr
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
thanks
|
|
|
|
| Re: Hints [message #666282 is a reply to message #666276] |
Wed, 25 October 2017 07:35  |
John Watson
Messages: 8966
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
"ERP"."ERP_PMT_TYPE"=10)
[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 |
Roachcoach
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
