您的位置:首页 > 汽车 > 新车 > 廊坊开发区疫情_网页设计作业成品代码js_搜全网的浏览器_北京网站seo设计

廊坊开发区疫情_网页设计作业成品代码js_搜全网的浏览器_北京网站seo设计

2025/8/24 14:14:38 来源:https://blog.csdn.net/jnrjian/article/details/142867107  浏览:    关键词:廊坊开发区疫情_网页设计作业成品代码js_搜全网的浏览器_北京网站seo设计
廊坊开发区疫情_网页设计作业成品代码js_搜全网的浏览器_北京网站seo设计

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'.

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com