Home » RDBMS Server » Performance Tuning » Optimizer question/ Date handling (Oracle 9.2.0.8.0)
Optimizer question/ Date handling [message #489546] Mon, 17 January 2011 07:39 Go to next message
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 #489549 is a reply to message #489546] Mon, 17 January 2011 08:24 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would have assumed you'd get the same plans as well. Care to post the explain plans so we can see the differences for ourselves?

Report message to a moderator

Re: Optimizer question/ Date handling [message #489551 is a reply to message #489546] Mon, 17 January 2011 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DATE '...' is a constant, SYSDATE is a non-deterministic function; each time you call the first query, it is the same analysis; each time you call the second query, the analysis should be different depending on current date/time; I don't see any reason for Oracle to treat them in the same way.

Regards
Michel

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 messageGo to next message
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 #489554 is a reply to message #489553] Mon, 17 January 2011 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A SQL query is implemented to be shared.
So the plan should take into account that the value of SYSDATE will change between 2 executions.
If Oracle knows what SYSDATE is and has a special handling of it, this one integrates the fact
that the value will change at each and every execution of the query.

Regards
Michel

Report message to a moderator

Re: Optimizer question/ Date handling [message #489556 is a reply to message #489554] Mon, 17 January 2011 09:02 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I see.

Then I guess my question is now...is there any way around that anyone knows of?

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 messageGo to next message
cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: Optimizer question/ Date handling [message #489561 is a reply to message #489559] Mon, 17 January 2011 09:23 Go to previous messageGo to next message
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.

Report message to a moderator

Re: Optimizer question/ Date handling [message #490583 is a reply to message #489561] Tue, 25 January 2011 17:40 Go to previous messageGo to next message
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

Report message to a moderator

Re: Optimizer question/ Date handling [message #490617 is a reply to message #490583] Wed, 26 January 2011 02:57 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Cheers mate, I tried the top one already, but I will try that hint - if I get a chance to get back to it!

Report message to a moderator

Previous Topic: Locker and waiter problem
Next Topic: Create Index vs Rebuild Index
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 18:01:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.