Optimizer question/ Date handling [message #489546] |
Mon, 17 January 2011 07:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
All,
I hope this isn't going to be a contender for vaguest question/crap post of the year however, here goes.
Note that this is light on specific detail since for me at least, its a generic question about the optimizer. I've not seen this behaviour before and I'm properly confused, it's at odds with what I'd expect.
The optimizer changes plans if I flip part of a where clause to something that I'd have thought would result in identical plans.
i.e.
WHERE
...
AND O.CREATED >= date'2011-01-10'
AND O.CREATED <= date'2011-01-16'
...
Creates one plan and
WHERE
...
AND O.CREATED >= trunc(sysdate-7,'iw')
AND O.CREATED <= trunc(sysdate,'iw')-1
...
Creates a materially different one, although not in the table access to the table aliased 'O' above but to different tables in the wider plan, its very weird.
My main question is based on the fact that I'd thought that these two variations would be treated as one in the same. What am I missing? Are there complexities around TRUNC or something I'm unaware of that might change a plan?
I'd expected Oracle to convert the second block of code to dates then build a plan from there but it would seem it is not.
Any suggestions/reading points would be great, as I say - this one has me scratching my head.
[Updated on: Mon, 17 January 2011 07:42] Report message to a moderator
|
|
|
|
|
Re: Optimizer question/ Date handling [message #489553 is a reply to message #489549] |
Mon, 17 January 2011 08:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Plan with 'sysdate' clause:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2757 | 1696K| | 995K (1)|
| 1 | SORT GROUP BY | | 2757 | 1696K| 3688K| 995K (1)|
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN OUTER | | 2757 | 1696K| | 994K (1)|
| 4 | NESTED LOOPS OUTER | | 2757 | 1550K| | 994K (1)|
| 5 | NESTED LOOPS OUTER | | 2009 | 1077K| | 972K (1)|
| 6 | NESTED LOOPS | | 2009 | 988K| | 966K (1)|
| 7 | NESTED LOOPS | | 2009 | 927K| | 962K (1)|
| 8 | NESTED LOOPS | | 2035 | 858K| | 958K (1)|
|* 9 | HASH JOIN | | 2035 | 777K| | 952K (1)|
|* 10 | TABLE ACCESS FULL | xxxRETAILBUSINESS | 6665 | 208K| | 309 (1)|
|* 11 | TABLE ACCESS BY INDEX ROWID | xxxPORTFOLIOPRODUCT | 1 | 59 | | 4 (25)|
| 12 | NESTED LOOPS | | 8285 | 2904K| | 952K (1)|
| 13 | NESTED LOOPS OUTER | | 22193 | 6501K| | 885K (1)|
| 14 | NESTED LOOPS OUTER | | 22193 | 5721K| | 818K (1)|
|* 15 | HASH JOIN | | 22193 | 4421K| 69M| 752K (1)|
|* 16 | HASH JOIN | | 487K| 64M| 52M| 437K (1)|
|* 17 | HASH JOIN | | 487K| 46M| 20M| 398K (1)|
|* 18 | INDEX RANGE SCAN | IDX_xxxORDER_CREATED | 482K| 15M| | 8051 (1)|
|* 19 | TABLE ACCESS BY INDEX ROWID| xxxORDERLINE | 1667K| 108M| | 383K (1)|
|* 20 | INDEX FULL SCAN | FK_RETAILPROOFOFPUR_ORDERLINE | 29M| | | 218K (1)|
| 21 | TABLE ACCESS FULL | xxxRETAILPROOFOFPURCHASE | 5744K| 202M| | 22786 (1)|
|* 22 | TABLE ACCESS FULL | xxxVISITREQUIREMENT | 6594K| 415M| | 286K (1)|
| 23 | TABLE ACCESS BY INDEX ROWID | xxxADDRESS | 1 | 60 | | 4 (25)|
|* 24 | INDEX RANGE SCAN | PK_xxxADDRESS | 1 | | | 3 (34)|
| 25 | TABLE ACCESS BY INDEX ROWID | xxxTELEPHONE | 1 | 36 | | 4 (25)|
|* 26 | INDEX RANGE SCAN | PK_xxxTELEPHONE | 1 | | | 3 (34)|
|* 27 | INDEX RANGE SCAN | PK_xxxPORTFOLIOPRODUCT | 1 | | | 3 (34)|
| 28 | TABLE ACCESS BY INDEX ROWID | xxxCUSTOMERROLE | 1 | 41 | | 4 (25)|
|* 29 | INDEX RANGE SCAN | FK_CUSTOMER_PORTFOLIO | 1 | | | 3 (34)|
| 30 | TABLE ACCESS BY INDEX ROWID | xxxPARTYROLE | 1 | 41 | | 3 (34)|
|* 31 | INDEX RANGE SCAN | PK_xxxPARTYROLE | 1 | | | 2 (50)|
| 32 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 31 | | 3 (34)|
|* 33 | INDEX RANGE SCAN | XPKPERSON | 1 | | | 2 (50)|
| 34 | TABLE ACCESS BY INDEX ROWID | xxxSUBSCRIPTION | 1 | 45 | | 4 (25)|
|* 35 | INDEX RANGE SCAN | PK_xxxSUBSCRIPTION | 1 | | | 3 (34)|
| 36 | TABLE ACCESS BY INDEX ROWID | xxxSUBSCRIPTIONENTITLEMENT | 1 | 27 | | 12 (9)|
|* 37 | INDEX RANGE SCAN | FK_SUBSCR_SUBSCRENTITLEMENT | 3 | | | 4 (25)|
| 38 | TABLE ACCESS FULL | xxxENTITLEMENT | 1198 | 64692 | | 8 (13)|
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRUNC(SYSDATE@!-7,'fmiw')<=TRUNC(SYSDATE@!,'fmiw')-1)
3 - access("ENT"."ENTITLEMENTID"="RDMENT"."ID"(+))
9 - access("RB"."ID"="PP"."RETAILERID")
10 - filter("RB"."ASAGROUPNUMBER"='21846' OR "RB"."ASAGROUPNUMBER"='50000' OR "RB"."ASAGROUPNUMBER"='55555')
11 - filter("PP"."RETAILERID" IS NOT NULL AND "PP"."STATUS"<>'CN' AND "PP"."STATUS"<>'BL')
15 - access("OL"."FULFILMENTITEMID"="VR"."FULFILMENTITEMID")
16 - access("OL"."RETAILPROOFOFPURCHASEID"="POP"."ID")
17 - access("O"."ID"="OL"."ORDERID")
18 - access("O"."CREATED">=TRUNC(SYSDATE@!-7,'fmiw') AND "O"."CREATED"<=TRUNC(SYSDATE@!,'fmiw')-1)
19 - filter("OL"."FULFILMENTITEMID" IS NOT NULL AND "OL"."ACTION"='AD' AND "OL"."PORTFOLIOPRODUCTID" IS NOT NULL)
20 - filter("OL"."RETAILPROOFOFPURCHASEID" IS NOT NULL)
22 - filter("VR"."JOBTYPE"='IN')
24 - access("OL"."FULFILMENTADDRESSID"="ADR"."ID"(+))
26 - access("VR"."TELEPHONEID"="TEL"."ID"(+))
27 - access("PP"."ID"="OL"."PORTFOLIOPRODUCTID")
29 - access("PP"."PORTFOLIOID"="CROL"."PORTFOLIOID")
31 - access("CROL"."PARTYROLEID"="PROL"."ID")
33 - access("PROL"."PARTYID"="PER"."PARTYID")
35 - access("PP"."SUBSCRIPTIONID"="SUB"."ID"(+))
37 - access("SUB"."ID"="ENT"."SUBSCRIPTIONID"(+))
Plan with hard dates
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 630 | 84 (3)|
| 1 | SORT GROUP BY | | 1 | 630 | 84 (3)|
| 2 | NESTED LOOPS OUTER | | 1 | 630 | 83 (2)|
| 3 | NESTED LOOPS OUTER | | 1 | 594 | 80 (2)|
| 4 | NESTED LOOPS OUTER | | 1 | 540 | 79 (2)|
| 5 | NESTED LOOPS | | 1 | 513 | 68 (2)|
| 6 | NESTED LOOPS | | 1 | 482 | 66 (2)|
| 7 | NESTED LOOPS | | 1 | 441 | 64 (2)|
| 8 | NESTED LOOPS | | 1 | 400 | 61 (2)|
| 9 | NESTED LOOPS OUTER | | 1 | 363 | 59 (2)|
| 10 | NESTED LOOPS | | 1 | 318 | 56 (2)|
| 11 | NESTED LOOPS | | 1 | 286 | 55 (2)|
| 12 | NESTED LOOPS OUTER | | 1 | 227 | 52 (2)|
| 13 | NESTED LOOPS | | 1 | 167 | 49 (3)|
| 14 | NESTED LOOPS | | 4 | 404 | 33 (4)|
|* 15 | INDEX RANGE SCAN | IDX_xxxORDER_CREATED | 4 | 132 | 5 (20)|
|* 16 | TABLE ACCESS BY INDEX ROWID| xxxORDERLINE | 1 | 68 | 8 (13)|
|* 17 | INDEX RANGE SCAN | FK_xxxORDER_ORDERLINE | 11 | | 4 (25)|
|* 18 | TABLE ACCESS BY INDEX ROWID | xxxVISITREQUIREMENT | 1 | 66 | 5 (20)|
|* 19 | INDEX RANGE SCAN | FK_xxxFLFLMNTITM_xxxVSTRQMT | 1 | | 4 (25)|
| 20 | TABLE ACCESS BY INDEX ROWID | xxxADDRESS | 1 | 60 | 4 (25)|
|* 21 | INDEX UNIQUE SCAN | PK_xxxADDRESS | 1 | | 3 (34)|
|* 22 | TABLE ACCESS BY INDEX ROWID | xxxPORTFOLIOPRODUCT | 1 | 59 | 4 (25)|
|* 23 | INDEX UNIQUE SCAN | PK_xxxPORTFOLIOPRODUCT | 1 | | 3 (34)|
|* 24 | TABLE ACCESS BY INDEX ROWID | xxxRETAILBUSINESS | 1 | 32 | 2 (50)|
|* 25 | INDEX UNIQUE SCAN | PK_xxxRETAILBUSINESS | 1 | | |
| 26 | TABLE ACCESS BY INDEX ROWID | xxxSUBSCRIPTION | 1 | 45 | 4 (25)|
|* 27 | INDEX UNIQUE SCAN | PK_xxxSUBSCRIPTION | 1 | | 3 (34)|
| 28 | TABLE ACCESS BY INDEX ROWID | xxxRETAILPROOFOFPURCHASE | 1 | 37 | 3 (34)|
|* 29 | INDEX UNIQUE SCAN | PK_xxxRETAILPROOFOFPURCHASE | 1 | | 2 (50)|
| 30 | TABLE ACCESS BY INDEX ROWID | xxxCUSTOMERROLE | 1 | 41 | 4 (25)|
|* 31 | INDEX RANGE SCAN | FK_CUSTOMER_PORTFOLIO | 1 | | 3 (34)|
| 32 | TABLE ACCESS BY INDEX ROWID | xxxPARTYROLE | 1 | 41 | 3 (34)|
|* 33 | INDEX UNIQUE SCAN | PK_xxxPARTYROLE | 1 | | 2 (50)|
| 34 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 31 | 3 (34)|
|* 35 | INDEX UNIQUE SCAN | XPKPERSON | 1 | | 2 (50)|
| 36 | TABLE ACCESS BY INDEX ROWID | xxxSUBSCRIPTIONENTITLEMENT | 1 | 27 | 12 (9)|
|* 37 | INDEX RANGE SCAN | FK_SUBSCR_SUBSCRENTITLEMENT | 3 | | 4 (25)|
| 38 | TABLE ACCESS BY INDEX ROWID | xxxENTITLEMENT | 1 | 54 | 2 (50)|
|* 39 | INDEX UNIQUE SCAN | PK_xxxENTITLEMENT | 1 | | |
| 40 | TABLE ACCESS BY INDEX ROWID | xxxTELEPHONE | 1 | 36 | 4 (25)|
|* 41 | INDEX UNIQUE SCAN | PK_xxxTELEPHONE | 1 | | 3 (34)|
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
15 - access("O"."CREATED">=TO_DATE(' 2011-01-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"O"."CREATED"<=TO_DATE(' 2011-01-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
16 - filter("OL"."RETAILPROOFOFPURCHASEID" IS NOT NULL AND "OL"."FULFILMENTITEMID" IS NOT NULL AND
"OL"."ACTION"='AD' AND "OL"."PORTFOLIOPRODUCTID" IS NOT NULL)
17 - access("O"."ID"="OL"."ORDERID")
18 - filter("VR"."JOBTYPE"='IN')
19 - access("OL"."FULFILMENTITEMID"="VR"."FULFILMENTITEMID")
21 - access("OL"."FULFILMENTADDRESSID"="ADR"."ID"(+))
22 - filter("PP"."RETAILERID" IS NOT NULL AND "PP"."STATUS"<>'CN' AND "PP"."STATUS"<>'BL')
23 - access("PP"."ID"="OL"."PORTFOLIOPRODUCTID")
24 - filter("RB"."ASAGROUPNUMBER"='21846' OR "RB"."ASAGROUPNUMBER"='50000' OR "RB"."ASAGROUPNUMBER"='55555')
25 - access("RB"."ID"="PP"."RETAILERID")
27 - access("PP"."SUBSCRIPTIONID"="SUB"."ID"(+))
29 - access("OL"."RETAILPROOFOFPURCHASEID"="POP"."ID")
31 - access("PP"."PORTFOLIOID"="CROL"."PORTFOLIOID")
33 - access("CROL"."PARTYROLEID"="PROL"."ID")
35 - access("PROL"."PARTYID"="PER"."PARTYID")
37 - access("SUB"."ID"="ENT"."SUBSCRIPTIONID"(+))
39 - access("ENT"."ENTITLEMENTID"="RDMENT"."ID"(+))
41 - access("VR"."TELEPHONEID"="TEL"."ID"(+))
@Michel: I agree in their determination, i.e. what Oracle does on resolving them however I'd (conjecture here) expect Oracle to resolve the sysdate then generate a plan, not generate a plan without knowing what the where clause limitations are. How else could is make a decision on if indexes are appropriate to use?
That being said, if I'm wrong in this - any way around it? I cant be hardcoding date ranges into this query, hence the sysdate approach.
What's really throwing me is its affecting the plan outside the table aliased as "o"...that just loses me completely.
Edit: spelling fail
[Updated on: Mon, 17 January 2011 08:41] Report message to a moderator
|
|
|
|
|
Re: Optimizer question/ Date handling [message #489559 is a reply to message #489556] |
Mon, 17 January 2011 09:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Move the sysdate calculations into a with clause? Doubt it'll help but you never know.
As for why the plan has gone the way it has gone:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
|* 15 | INDEX RANGE SCAN | IDX_xxxORDER_CREATED | 4 | 132 | 5 (20)|
.....
|* 18 | INDEX RANGE SCAN | IDX_xxxORDER_CREATED | 482K| 15M| | 8051 (1)|
Those are index lookups due to the date, 1st hardcoded, 2nd sysdate, see the difference in the number of rows it thinks it's going to find?
|
|
|
Re: Optimizer question/ Date handling [message #489561 is a reply to message #489559] |
Mon, 17 January 2011 09:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
It didn't help, already tried it, binds yield the same (albeit expected) result >:o)
Yup - I just didn't expect it to evaluate that way.
Currently bending the plan via hints, which I kinda dislike but I cant think of another (practical) way.
|
|
|
Re: Optimizer question/ Date handling [message #490583 is a reply to message #489561] |
Tue, 25 January 2011 17:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
It seems with SYSDATE range that it doesn't know how few rows will be returned. It probably doesn't compare the two values and realise that they are just 7 days apart.
To give it a better chance, you could try:
AND O.CREATED >= trunc(sysdate,'iw')-7
AND O.CREATED <= trunc(sysdate,'iw')-1
Or try
SELECT /*+ CARDINALITY(O 10) */
Ross Leishman
|
|
|
|