Oracle 12.2 BUG：INTERNAL MERGE STATEMENT SLOW USING GV$ TABLES OPT_PARAM

今天有朋友在墨天轮上，提出一个有意思的问题，一个 SQL 的 VERSION_COUNT 过高。我截取一段报告：

注意，排在前列的几个SQL，都是系统的内部SQL，也就是说，是数据库自身SQL问题导致的，后面的一个是 emagent_SQL_rac_database 的模块发出的。第一个SQL 有 4256 个版本，后者有 2737 个版本。

我们的第一判断就是 BUG。但是这个 SQL 在报告中没有展示出来，如何进一步分析呢？

根据 SQLID 3kqrku32p6sfn 我们可以很容易 Google 到完整的SQL语句：

MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ 
INTO OPTSTAT_USER_PREFS$ D USING ( 
SELECT * FROM (SELECT O.OBJ#,  SYSTIMESTAMP CHGTIME,  ROUND(MAX(S.DELTA_READ_IO_BYTES/S.DELTA_TIME),  3) SCANRATE 
FROM GV$ACTIVE_SESSION_HISTORY S,  GV$SQL_PLAN P,  OBJ$ O,  USER$ U 
WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQL_ID 
AND S.SQL_PLAN_HASH_VALUE = P.PLAN_HASH_VALUE 
AND S.SQL_PLAN_LINE_ID = P.ID AND P.OBJECT_NAME = O.NAME 
AND (:B4 IS NULL OR O.OBJ# = :B4 ) 
AND O.OWNER# = U.USER# AND P.OBJECT_OWNER = U.NAME 
AND O.TYPE# = 2 AND S.CON_ID = SYS_CONTEXT('userenv',  'con_id') 
AND P.CON_ID = SYS_CONTEXT('userenv',  'con_id') 
AND S.SQL_PLAN_OPERATION = 'TABLE ACCESS' 
AND P.OPERATION = 'TABLE ACCESS' AND S.DELTA_TIME > 0 
AND S.DELTA_READ_IO_BYTES > 0 AND FROM_TZ(S.SAMPLE_TIME,  :B3 ) > :B2 
AND (:B1 = 'ON' OR (:B1 = 'HADOOP_ONLY' AND EXISTS 
( SELECT NULL FROM SYS.EXTERNAL_TAB$ ET WHERE ET.OBJ# = O.OBJ# 
AND TYPE$ IN ('ORACLE_HIVE',  'ORACLE_HDFS',  'ORACLE_BIGDATA'))))
 GROUP BY O.OBJ#) C WHERE NOT EXISTS 
(SELECT NULL FROM OPTSTAT_USER_PREFS$ PR WHERE PNAME = 'SCAN_RATE' 
AND C.OBJ# = PR.OBJ# AND 
(PR.SPARE1 IS NULL OR C.SCANRATE <= TO_NUMBER(PR.VALCHAR))) ) 
SRC ON (D.OBJ#=SRC.OBJ# AND D.PNAME = 'SCAN_RATE') 
WHEN MATCHED THEN UPDATE SET D.VALCHAR= TO_CHAR(SRC.SCANRATE),  D.CHGTIME=SRC.CHGTIME,  D.SPARE1=1 
WHEN NOT MATCHED THEN INSERT (OBJ#,  PNAME,  VALCHAR,  CHGTIME,  SPARE1) VALUES(SRC.OBJ#,  'SCAN_RATE',  TO_CHAR(SRC.SCANRATE),  SRC.CHGTIME,  1)
复制

有了完整的 SQL 语句，可以很容易在 MOS 上找到对应的 BUG 。我引用一下关键信息。

Statement ‘MERGE /*+ OPT_PARAM(’_parallel_syspls_obey_force’ ‘false’) */ INTO OPTSTAT_USER_PREFS’ Taking High CPU (Doc ID 2533703.1)

APPLIES TO:
Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
Observed that one process is using 100% CPU (one core):

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26958 oracle 20 0 24.418g 72308 68040 R 100.0 0.1 55586:21 ora_ppa7_strn1 
复制

Found that this process is following sql:

MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME, ROUND(MAX(S.DELTA_READ_IO_BYTES
/S.DELTA_TIME), 3) SCANRATE FROM GV$ACTIVE_SESSION_HISTORY S, GV$SQL_PLAN P, OBJ$ O, USER$ U WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQL_ID AND S.SQL_PLAN_HASH_VALUE = P.PLAN_
HASH_VALUE AND S.SQL_PLAN_LINE_ID = P.ID AND P.OBJECT_NAME = O.NAME AND (:B4 IS NULL OR O.OBJ# = :B4 ) AND O.OWNER# = U.USER# AND P.OBJECT_OWNER = U.NAME AND O.TYPE# = 2 AND S.CON_
ID = SYS_CONTEXT('userenv', 'con_id') AND P.CON_ID = SYS_CONTEXT('userenv', 'con_id') AND S.SQL_PLAN_OPERATION = 'TABLE ACCESS' AND P.OPERATION = 'TABLE ACCESS' AND S.DELTA_TIME >
0 AND S.DELTA_READ_IO_BYTES > 0 AND FROM_TZ(S.SAMPLE_TIME, :B3 ) > :B2 AND (:B1 = 'ON' OR (:B1 = 'HADOOP_ONLY' AND EXISTS ( SELECT NULL FROM SYS.EXTERNAL_TAB$ ET WHERE ET.OBJ# = O.
OBJ# AND TYPE$ IN ('ORACLE_HIVE', 'ORACLE_HDFS', 'ORACLE_BIGDATA')))) GROUP BY O.OBJ#) C WHERE NOT EXISTS (SELECT NULL FROM OPTSTAT_USER_PREFS$ PR WHERE PNAME = 'SCAN_RATE' AND C.O
BJ# = PR.OBJ# AND (PR.SPARE1 IS NULL OR C.SCANRATE <= TO_NUMBER(PR.VALCHAR))) ) SRC ON (D.OBJ#=SRC.OBJ# AND D.PNAME = 'SCAN_RATE') WHEN MATCHED THEN UPDATE SET D.VALCHAR= TO_CHAR(S
RC.SCANRATE), D.CHGTIME=SRC.CHGTIME, D.SPARE1=1 WHEN NOT MATCHED THEN INSERT (OBJ#, PNAME, VALCHAR, CHGTIME, SPARE1) VALUES(SRC.OBJ#, 'SCAN_RATE', TO_CHAR(SRC.SCANRATE), SRC.CHGTIM
E, 1) 
复制

CHANGES
none

CAUSE
This is due to an internal bug:

Bug 29296074 - OPT: INTERNAL MERGE STATEMENT FOR SCAN RATE IS SLOW USING GV$ TABLES

SOLUTION

1. Bug is fixed 20.1

OR

2. Apply Patch 29296074

OR

3. Use following workaround:

Please find the SQL_ID of interest like this:

select sql_id
from v$sql
where sql_text like '%INTO OPTSTAT_USER_PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME%';
复制

Use following note:

Document 1931944.1 How to Create a SQL Patch to add Hints to Application SQL Statements

Add any of these hints to this SQL:

/*+ opt_param( '_complex_view_merging', 'false') */
/*+ opt_param( '_PUSH_JOIN_PREDICATE', 'false') */
/*+ opt_param( '_PUSH_JOIN_UNION_VIEW', 'false') */
/*+ RULE */
复制

Syntax would be something like the following:

connect / as sysdba
variable x varchar2(100);
exec :x:=dbms_sqldiag_internal.i_create_patch(sql_id=>'XXXXXXXXXXXX',
hint_text=>'opt_param( "_complex_view_merging", "false") opt_param("_PUSH_JOIN_PREDICATE", "false") opt_param( "_PUSH_JOIN_UNION_VIEW", "false")' , creator=>'SYS', name=> 'SQL_Patch_for-merge');
复制

Also try gathering following stats:

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
exec DBMS_STATS.GATHER_DICTIONARY_STATS ;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

复制

以上供参考。