SQL Query Running Very Slow and using RULE BASED OPTIMIZER after the snapshot standby switchover (Doc ID 2735998.1)
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 21.1 [Release 12.2 to 20.0]
Information in this document applies to any platform.
Symptoms
- One of the SQL queries is using RULE BASED OPTIMIZER after migrating to the latest 12.2 RU
- This occurs after the snapshot standby after the switchover
Changes
Updated the database home(12.2) to patch level 201020
Cause
This is due to following bug:
Bug 30572816 : RULE BASED OPTIMIZER USED ON SNAPSHOT STANDBY AFTER SWITCHOVER WHEN OPTIMIZER_MODE=FIRST_ROWS
The plan outline would have the following information:
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
RBO_OUTLINE <=====================RBO_OUTLINE shown in the outline data.
OUTLINE_LEAF(@"SEL$57815F40")
SELECT
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPT_PARAM('_replace_virtual_columns' 'false') 隐含参数
OUTLINE_LEAF(@SEL$1)
LEADING(@SEL$1 T1@SEL$1 T2@SEL$1 T3@SEL$1)
PARTIAL_JOIN(@SEL$1 T3@SEL$1)
END_OUTLINE_DATA
*/
DISTINCT T3.C5,T1.C1
FROM T1 ,T2 ,T3
WHERE T1.C1 = T2.C1
AND T1.C2 = T1.C2
AND T1.C1 = T3.C3
AND T1.C2 = T3.C1
AND T1.C3 = T3.C2
AND T1.C4 = T3.C5
order by T1.C1 asc;
How to Use Hint from a Query Using Sql Profile to Force 'Good Plan' to Another Query with Different Literal Value (Doc ID 2114139.1)
How to create hint from sql profile from a query to another query with different literal value.
Solution
1. Get the plan from the query using the 'good plan' from sql profile using the 'ADVANCED' method:
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'3fkvb3qnpgu6v',format=>'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3fkvbnpgu6v
--------------------
select /*+ gather_plan_statistics */ 'bo' from emp
Plan hash value: 179099197
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | INDEX RANGE SCAN | PK_EMP | 14 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Please note that according to the below, dbms_xplan.display_awr only works with snapshots for the local DBID, whereas dbms_xplan.display_workload_repository supports all snapshots inside AWR including remote and imported snapshots.
DBMS_XPLAN
2. Use the outline hint at the bottom on another sql with literal to force the plan:
select /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/ from 'lo' from emp;
Goal
How to display and explore the outlines data and/or optimizer hint used to enforce certain execution plans using the SQL Plan Baselines via SQL Plan Management features.
This can also help you to quickly examine whether the SPM plan is reproducible in any other instance.
Solution
Use the following query to achieve this:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_6S225D1SJJZ1Z642E4A26', FORMAT => 'OUTLINE'));
For example:
SELECT PLAN_NAME FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE='SQL_6C0845687118FC3F';
PLAN_NAME
--------------------------------------------------------------------------------
SQL_PLAN_6s225d1sjjz1z193ccf8b
SQL_PLAN_6s225d1sjjz1z642e4a26
SQL_PLAN_6s225d1sjjz1z97bbe3d0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_6S225D1SJJZ1Z193CCF8B', FORMAT => 'OUTLINE'));
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."ID"))
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM('_optimizer_use_feedback' 'false')
DB_VERSION('12.1.0.1')
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_6S225D1SJJZ1Z642E4A26', FORMAT => 'OUTLINE'));
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."ID"))
OUTLINE_LEAF(@"SEL$1")
RBO_OUTLINE
OPT_PARAM('_optimizer_use_feedback' 'false')
DB_VERSION('12.1.0.1')
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_6S225D1SJJZ1Z97BBE3D0', FORMAT => 'OUTLINE'));
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "TEST"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OPT_PARAM('_optimizer_use_feedback' 'false')
DB_VERSION('12.1.0.1')
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
You can use either FORMAT=> 'OUTLINE' or FORMAT=> 'ADVANCED'.