My Oracle Support Banner

SPM Baseline Plan Cannot Be Reproduced When Implicit Data Type Conversion Happened (Doc ID 2456227.1)

Last updated on NOVEMBER 04, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

10053 trace abstract:

SPM: statement found in SMB
... ...
SPM: finding a match for the generated plan, planId = 3402166110
... ...
SPM: fixed planId's of plan baseline are: 3123576401
... ...
SPM: plan reproducibility round 1 (plan outline + session OFE)
... ...
SPM: failed to reproduce the plan using the following info:
... ...
SPM: generated non-matching plan:
... ...
SPM: plan reproducibility round 2 (hinted OFE only)
... ...
SPM: failed to reproduce the plan using the following info:
... ...
SPM: generated non-matching plan:
... ...
SPM: couldn't reproduce any enabled+accepted plan so using the cost-based plan, planId = 3402166110

Baseline Plan:

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 1 | 737 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 737 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_TAB1_01 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Actual used SQL Plan:

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |        |       | 31459 (100)|          |
|   1 |  SORT ORDER BY             |             |      1 |   737 | 31459   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL| TAB1        |      1 |   737 | 31458   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------

  

Environment for creating baseline plan:

  Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=873): '100'
   2 - (VARCHAR2(30), CSID=873): '1001209'
   3 - (VARCHAR2(30), CSID=873): '08180573223'

  
Environment for using baseline plan:

  Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 100                   *<<<<<<<-------
   2 - (NUMBER): 1000002               *<<<<<<<-------
   3 - (VARCHAR2(30), CSID=873): '08180B30661'
"CORP_CD" VARCHAR2(3),
"DEPT_CD" VARCHAR2(7),

  

SELECT
... ...
FROM
"USER1" . "TAB1" "TAB1"
WHERE
TO_NUMBER ( "TAB1" . "CORP_CD" ) = : B1 *<<<<<<<-------
AND TO_NUMBER ( "TAB1" . "DEPT_CD" ) = : B2 *<<<<<<<-------
AND "TAB1" . "DEMP_NO" = : B3
... ...
;

  

Changes

 Transported SQL plan baselines from one database to another database.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.