Re: moving 10g execution plan to 11g using SPM
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Carlos Sierra <carlos.sierra.usa@xxxxxxxxx>
- Date: Tue, 4 Mar 2014 14:05:00 +0100
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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 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@xxxxxxxxx>:
> 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: @csierra_usa
>
> Life's Good!
>
> On Mar 4, 2014, at 6:11 AM, Ls Cheng <exriscer@xxxxxxxxx> 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@xxxxxxxxx> 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: @csierra_usa
> >
> > Life's Good!
> >
> > On Mar 4, 2014, at 6:00 AM, Ls Cheng <exriscer@xxxxxxxxx> 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
> > >
> >
> >
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
