Re: Unexplained Plan Change
- From: Michael Wehrle <michaelw436@xxxxxxxxx>
- To: moabrivers@xxxxxxxxx
- Date: Tue, 22 Jun 2010 16:14:36 -0400
You might want to check the view V$SQL_SHARED_CURSOR for possible evidence
of why the plan was changed. I know from experience, and from reading
various Oracle books that this doesn't always provide an answer:
V$SQL_SHARED_CURSOR
V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared
with existing child cursors. Each column identifies a specific reason why
the cursor cannot be shared.
On Mon, Jun 21, 2010 at 12:53 PM, LB <moabrivers@xxxxxxxxx> wrote:
> I've got a query that ran this morning that for some reason decided to jump
> ship off its normal plan and use a different plan. This is a very
> straightforward single table query. When the query runs normally it uses
> the correct index and returns results very quickly. This table and its 2
> indexes (one PK and one non-unique) have their respective statistics
> gathered nightly at 1AM. At 8AM this morning, Oracle decided that using the
> PK index was the best choice and so it gobbled up 22 seconds doing single
> block I/O. Now I understand that gathering statistics can cause a SQL plan
> to change but that plan change should remain effect all things being equal.
> If it jump shipped because of a statistical gathering change then it should
> stay the course on the new plan. It doesn't. Explaining the query from
> several different clients all result in the correct explain plan that uses
> the non-unique index and not the primary key index. I am unable to
> reproduce any situation where the PK should be used for this query so I am a
> bit confused as to why Oracle decided it was the best choice at that moment
> in time. I also understand that bind peeking was an issue in 10.2.0.3
> resolved in 10.2.0.4. Even explaining the plan with just the bind variables
> results in the correct plan again being chosen. The only recorded wait time
> was for db file sequential reads (8 seconds on the index datafile, 14
> seconds on the table datafile).
>
> The optimizer settings haven't changed and its impossible for the driving
> application to change its optimizer settings at the session level. The
> query uses bind variables (we are 10.2.0.4 64-bit on Linux) and I've
> reconstructed the query's variables using v$sql_bind_capture. As I'm not
> the code architect behind the generation of this query, I cannot change the
> query itself.
>
>
> Here's an example of the query:
>
> The PK index is on COL_A_I_PK and COL_F_PK
> The non-unique index is on COL_A_I_PK, COL_B_I, COL_C_I, COL_D_I, COL_E_I
>
> You will notice the bulk of predicate filter is repetitive with OR
> statements on columns B, C, D, and E.
>
> SELECT COUNT(*)
> FROM
> (
> SELECT COL_A_I_PK,
> COL_B_I,
> COL_C_I,
> COL_D_I,
> COL_E_I,
> COL_F_PK,
> other_non_indexed_columns....
> FROM T
> WHERE COL_A_I_PK = :1
> AND other_non_indexed_column = :"SYS_B_0"
> AND ((COL_B_I = :2
> AND COL_C_I = :3
> AND COL_D_I = :4
> AND COL_E_I=:5)
> OR (COL_B_I = :6
> AND COL_C_I = :7
> AND COL_D_I = :8
> AND COL_E_I=:9)
> OR (COL_B_I = :10
> AND COL_C_I = :11
> AND COL_D_I = :12
> AND COL_E_I=:13)
> OR (COL_B_I = :14
> AND COL_C_I = :15
> AND COL_D_I = :16
> AND COL_E_I=:17)
> OR (COL_B_I = :18
> AND COL_C_I = :19
> AND COL_D_I = :20
> AND COL_E_I=:21)
> OR (COL_B_I = :22
> AND COL_C_I = :23
> AND COL_D_I = :24
> AND COL_E_I=:25)
> OR (COL_B_I = :26
> AND COL_C_I = :27
> AND COL_D_I = :28
> AND COL_E_I=:29)
> OR (COL_B_I = :30
> AND COL_C_I = :31
> AND COL_D_I = :32
> AND COL_E_I=:33)
> OR (COL_B_I = :34
> AND COL_C_I = :35
> AND COL_D_I = :36
> AND COL_E_I=:37)
> OR (COL_B_I = :38
> AND COL_C_I = :39
> AND COL_D_I = :40
> AND COL_E_I=:41)
> OR (COL_B_I = :42
> AND COL_C_I = :43
> AND COL_D_I = :44
> AND COL_E_I=:45) )
> )
>
> Any thoughts on other places I might look to find out why it changed.
> Running a 10053 event trace seems a little too late at this point because
> the correct plan is again being chosen. This is the first time this query
> ran today.
>
>
> Thanks.
>
Other related posts:
