Re: Strange problem.

From: Chuck <skilover_nospam_at_softhome.net>
Date: Tue, 6 Dec 2005 19:24:39 +0000 (UTC)
Message-ID: <Xns972492980C329skiloversofthomenet@213.155.197.138>

hpuxrac wrote:
> I think using either the 10046 trace or the 10053 trace would get you
> the information needed to diagnose this.
>
> The 10046 trace can be run through tkprof and/or some resource
> profiler.
>
> The 10053 trace dumps optimizer information.
>

It just keeps getting better. It looks like the plan returned by explain plan is different than what's actually being executed.

According to SQL TRACE the plan being executed is

Rows Row Source Operation

-------  ---------------------------------------------------
      0  UPDATE
      0   FILTER
 247743    TABLE ACCESS FULL PS_TL_PAYABLE_TIME
      0    NESTED LOOPS
 245074     INDEX RANGE SCAN PS_TL_XREF_TBL (object id 892118)
      0     INDEX UNIQUE SCAN PS_PY_XREF_WRK (object id 892121)

Which is different than the plan reported by explain plan which is...

| Id  | Operation                    |  Name               | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                     |  1796 | 50288 |   659   (0)|
|   1 |  UPDATE                      | PS_TL_PAYABLE_TIME  |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME  |     1 |    15 |     2  (50)|
|   3 |    NESTED LOOPS              |                     |  1796 | 50288 |   659   (0)|
|   4 |     VIEW                     | VW_SQ_1             |  1796 | 23348 |            |
|   5 |      SORT UNIQUE             |                     |  1796 | 57472 |            |
|   6 |       NESTED LOOPS           |                     |  1796 | 57472 |   112   (1)|
|*  7 |        TABLE ACCESS FULL     | PS_TL_XREF_TBL      |   259K|  4817K|   110   (0)|
|*  8 |        INDEX UNIQUE SCAN     | PS_PY_XREF_WRK      |     1 |    13 |            |
|*  9 |     INDEX RANGE SCAN         | PSATL_PAYABLE_TIME  |     1 |       |            |
-----------------------------------------------------------------------------------------

How do I force Oracle to use the plan that explain plan says it's going to use? Received on Tue Dec 06 2005 - 13:24:39 CST