Re: moving 10g execution plan to 11g using SPM

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Tue, 4 Mar 2014 14:05:00 +0100
Message-ID: <CAJu8R6gKZPqrdf6c2xARZO9Rzrnd_a81QVZB5n4tYKeBM07_7Q_at_mail.gmail.com>

I don't think that the object id matters here as shown below via the following example:

I have a query for which I have an enabled and accepted SPM baseline

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)

---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL_ID 731b98a8u0knf, child number 1

select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

| 0 | SELECT STATEMENT | | | | 124 (100)|

         |


|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |

         |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 25000 | 1318K| 124 (2)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 25000 | | 13 (8)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("FLAG"=:N)

Note

SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement

The object id of the I1 index is

SQL> select object_name, object_id from user_objects where object_name = 'I1';

OBJECT_NAME OBJECT_ID

-------------------- ----------
I1                       209090

Now I am going to drop/re-create that index and see what happens to my SPM baseline

SQL> drop index I1;

Index dropped.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)

---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

---------------------------------------------------------------------------------------------------------

SQL_ID 731b98a8u0knf, child number 1

select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

| 0 | SELECT STATEMENT | | | | 241 (100)| |
| 1 | SORT AGGREGATE | | 1 | 54 | | |
|* 2 | TABLE ACCESS FULL| T1 | 25000 | 1318K| 241 (3)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter("FLAG"=:N)

SPM baseline is not anymore reproducible and hence not used. That's predictable.

Let's now re-create the index so that it will have a new object_id

SQL> CREATE INDEX i1 ON t1 (flag);

Index created.

SQL> select object_name, object_id from user_objects where object_name = 'I1';

OBJECT_NAME OBJECT_ID

-------------------- ----------
I1                       209091

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)

---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL_ID 731b98a8u0knf, child number 1

select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3625400295

| 0 | SELECT STATEMENT | | | | 124 (100)|

         |


|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |

         |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 25000 | 1318K| 124 (2)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 25000 | | 13 (8)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("FLAG"=:N)

Note

SQL plan baseline SQL_PLAN_13w748wknkcwd8576eb1f used for this statement

The object id seems not to play a role in the reproducibility of a SPM plan baseline. However the object name does matter.

SQL> alter index i1 rename to i2;

Index altered.

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)

---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL_ID 731b98a8u0knf, child number 1

select count(*), max(col2) from t1 where flag = :n

Plan hash value: 718843153

| 0 | SELECT STATEMENT | | | | 124 (100)|

         |


|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |

         |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 25000 | 1318K| 124 (2)|
00:00:01 |
|* 3 | INDEX RANGE SCAN | I2 | 25000 | | 13 (8)| 00:00:01 |

Predicate Information (identified by operation id):

3 - access("FLAG"=:N)

If you want to know why your SPM baseline has not been re-used then post

(a) the SPM baseline plan
(b) the CBO plan that has been produced for your query

I have few related posts here

http://hourim.wordpress.com/category/sql-plan-managment/

Best regards
Mohamed Houri

Mohamed Houri

2014-03-04 13:39 GMT+01:00 Carlos Sierra <carlos.sierra.usa_at_gmail.com>:

> Different object ids should matter, since the hints that make the baseline
> do not refer to objects by id.
>
> In any case, i suggest to focus on the root cause of the regression.
>
> Cheers,
>
> Carlos Sierra
>
> blog: carlos-sierra.net
> twitter: _at_csierra_usa
>
> Life's Good!
>
> On Mar 4, 2014, at 6:11 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
> > Hi Carlos
> >
> > I just realized that I might have different object mapping because I
> moved 10g database to 11g using TTS and all the object_id have changed. Can
> that be the reason?
> >
> > I will look into SQLT and get the the xtract output
> >
> > Thanks
> >
> >
> >
> > On Tue, Mar 4, 2014 at 1:09 PM, Carlos Sierra <
> carlos.sierra.usa_at_gmail.com> wrote:
> > Ls,
> >
> > SPM is not very verbose when it comes to not reproducing a plan. Where I
> can help you with is identifying the reason of the regressions you are
> observing. What I would need is between 1 and 3 of such regressions, and
> have SQLT XTRACT (MOS 215187.1) executed for each of those SQL_IDs in both
> the 10.2.0.5 and the 11.2.0.4 environments.
> >
> > Cheers,
> >
> > Carlos Sierra
> >
> > blog: carlos-sierra.net
> > twitter: _at_csierra_usa
> >
> > Life's Good!
> >
> > On Mar 4, 2014, at 6:00 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
> >
> > > Hi all
> > >
> > > I am currently upgrading a few databases from 10.2.0.5 to 11.2.0.4 and
> running SQL regression tests using SQL Performance Analyzer, I have
> detected a few queries that is not performing well in 11g so I transported
> those queries execution plan and imported as sql plan baselines in 11g. The
> strange thing is that these base lines are not used, after running SPM
> tracing I see that it cannot reproduce the execution plan (SPM: failed to
> reproduce the plan using the following info: is shown in the trace files)
> but I cannot see the reasoning. The possibilities I can think of is schema
> differences between 10g and 11g, object mapping is different or outline
> errors but these dont seem apply or at least not observed in SPM trace and
> 10053 trace.
> > >
> > > Does anyone know what other potential reasons can cause such behaviour?
> > >
> > >
> > > Thanks
> > >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 04 2014 - 14:05:00 CET

This message: [ Message body ]
Next message: Carlos Sierra: "Re: moving 10g execution plan to 11g using SPM"
Previous message: Carlos Sierra: "Re: moving 10g execution plan to 11g using SPM"
In reply to: Carlos Sierra: "Re: moving 10g execution plan to 11g using SPM"
Next in thread: Ls Cheng: "Re: moving 10g execution plan to 11g using SPM"
Reply: Ls Cheng: "Re: moving 10g execution plan to 11g using SPM"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message