环境说明：
DB：Oracle 11.2.0.4.0OS:Redhat 7.9
问题：
PGA告警。
告警信息：
[ID:U_33XXX33][CJC集团系统（AAAA）:CJC集团系统,10.0.10.12][数据库][3级][AAAA],10.0.0.100-oracle,2022.11.15 07:21:30 PGA per Session On CCCJCXXX-DB-vip-oracleis warnning!(10.0.0.100-oracle)[CJC集团],故障发生时间:2022-11-15 07:21:57
恢复信息：
[ID:U_33XXX33,故障恢复][CJC集团系统（AAAA）:CJC集团系统,10.0.10.12][数据库][3级][AAAA],10.0.0.100-oracle,2022.11.15 07:21:30 PGA per Session On CCCJCXXX-DB-vip-oracleis warnning!(10.0.0.100-oracle)[CJC集团],故障恢复时间:2022-11-15 07:37:00
问题分析：
收集问题时间段的AWR和ASH报告，有两个SQL耗时900秒，和故障时间完全吻合。
耗时SQL文本如下：
--900秒begindbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
--900.24秒--SQL_ID:2d1p0p5k3f8fuselect p, NULL, NULL from (select count(*)p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY');
生成SQL执行计划
set linesize 150set pagesize 2000select * fromTABLE(dbms_xplan.display_cursor('2d1p0p5k3f8fu'));
Plan hash value: 139239320------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 4 (100)| || 1 | VIEW | | 1 | 13 | 4 (100)| 00:00:01 || 2 | SORT AGGREGATE | | 1 | 153 | | ||* 3 | FILTER | | | | | ||* 4 | HASH JOIN OUTER | | 1 | 153 | 4 (100)| 00:00:01 || 5 | MERGE JOIN CARTESIAN | | 1 | 96 | 4 (100)| 00:00:01 || 6 | MERGE JOIN CARTESIAN | | 1 | 70 | 2 (100)| 00:00:01 || 7 | FIXED TABLE FULL | X$KCCRSR | 1 | 44 | | || 8 | BUFFER SORT | | 1 | 26 | 2 (100)| 00:00:01 || 9 | VIEW | | 1 | 26 | 2 (100)| 00:00:01 || 10 | HASH GROUP BY | | 1 | 91 | 2 (100)| 00:00:01 ||* 11 | HASH JOIN OUTER | | 1 | 91 | 1 (100)| 00:00:01 || 12 | FIXED TABLE FULL | X$KCCRSR | 1 | 26 | | || 13 | VIEW | | 1 | 65 | 1 (100)| 00:00:01 || 14 | HASH GROUP BY | | 1 | 65 | 1 (100)| 00:00:01 || 15 | FIXED TABLE FULL| X$KSFQP | 1 | 65 | | || 16 | BUFFER SORT | | 1 | 26 | 2 (100)| 00:00:01 || 17 | VIEW | | 1 | 26 | 2 (100)| 00:00:01 || 18 | HASH UNIQUE | | 1 | 62 | 2 (100)| 00:00:01 ||* 19 | HASH JOIN OUTER | | 1 | 62 | 1 (100)| 00:00:01 || 20 | FIXED TABLE FULL | X$KCCRSR | 1 | 26 | | || 21 | VIEW | | 1 | 36 | 1 (100)| 00:00:01 || 22 | WINDOW SORT | | 1 | 49 | 1 (100)| 00:00:01 ||* 23 | FIXED TABLE FULL | X$KSFQP | 1 | 49 | | ||* 24 | FIXED TABLE FULL | X$KRBMRST | 1 | 57 | | |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter((NVL(UPPER("OPER_KRBMRST"),UPPER("R2"."RSROP"))='BLOCK MEDIARECOVERY' AND "HH"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND"HH"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST") AND"ODEV"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND"ODEV"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST")))4 - access("R2"."RSRRID"="ID_KRBMRST" AND "R2"."RSRTST"="STAMP_KRBMRST")11 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND"R"."RSRTST"="RS"."RMAN_STATUS_STAMP")19 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID" AND"R"."RSRTST"="RS"."RMAN_STATUS_STAMP")23 - filter("TYPE"=2)24 - filter(("STATUS_KRBMRST"=1 OR "STATUS_KRBMRST"=9 OR "STATUS_KRBMRST"=17 OR"STATUS_KRBMRST"=25))54 rows selected.
检查表数据量
select count(*) from sys.X$KCCRSR; ---4512select count(*) from sys.X$KSFQP; ---1703select count(*) from sys.X$KRBMRST; ---0
表虽然都不大，但是通过MERGE JOIN CARTESIAN连接方式，最多可以生成4512*1703=7683936结果集。
查看历史执行信息，执行开始时间没什么规律，单次执行耗时基本都是15分钟。
SET PAGESIZE 10000SET LINE 300COL EVENT FOR A30select TO_CHAR(SAMPLE_TIME,'YYYY-MM-DDHH24:MI:SS') TIME,SQL_ID,EVENT,USER_ID FROM dba_hist_active_sess_history WHERESQL_ID='2d1p0p5k3f8fu' ORDER BY 1 desc;
TIME SQL_ID EVENT USER_ID-------------------------------------- -------------------------- ------------------------------ ----------2022-11-15 07:34:05 2d1p0p5k3f8fu direct path write temp 0......2022-11-15 07:19:14 2d1p0p5k3f8fu direct path write temp 0......2022-11-14 02:33:41 2d1p0p5k3f8fu 0......2022-11-14 02:18:49 2d1p0p5k3f8fu direct path write temp 0......2022-11-12 21:33:16 2d1p0p5k3f8fu 0......2022-11-12 21:18:25 2d1p0p5k3f8fu 0......2022-11-11 16:32:50 2d1p0p5k3f8fu direct path write temp 0......2022-11-11 16:17:59 2d1p0p5k3f8fu direct path write temp 0......2022-11-10 11:32:23 2d1p0p5k3f8fu 0......2022-11-10 11:17:32 2d1p0p5k3f8fu 0......2022-11-09 06:31:47 2d1p0p5k3f8fu direct path write temp 0......2022-11-09 06:16:56 2d1p0p5k3f8fu 0......2022-11-08 01:31:31 2d1p0p5k3f8fu 0......2022-11-08 01:16:30 2d1p0p5k3f8fu direct path write temp 0............2022-10-06 12:13:07 2d1p0p5k3f8fu direct path write temp 0......2022-10-06 11:58:15 2d1p0p5k3f8fu direct path write temp 03433 rows selected.
解决方案：
通过SQL文本和执行计划可以定位到，此问题和Bug 14078947吻合
1.禁用_optimizer_mjc_enabled参数
alter system set "_optimizer_mjc_enabled" = false;
2.删除并锁定X$KCCRSR统计信息
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');exec dbms_stats.UNLOCK_TABLE_STATS('SYS','X$KCCRSR');
检查效果,耗时由15分钟下降到0.07秒。
set timing onselect p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY');P N N---------- - -0Elapsed: 00:00:00.07
查看新执行计划，表连接方式已经由MERGE JOIN CARTESIAN换成HASH JOIN。
set line 300set pagesize 1000set autotrace onselect p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY');
Execution Plan----------------------------------------------------------Plan hash value: 2827575901---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 4 (100)| 00:00:01 || 1 | VIEW | | 1 | 13 | 4 (100)| 00:00:01 || 2 | SORT AGGREGATE | | 1 | 153 | | ||* 3 | HASH JOIN | | 1 | 153 | 4 (100)| 00:00:01 ||* 4 | HASH JOIN | | 1 | 127 | 2 (100)| 00:00:01 || 5 | VIEW | | 100 | 2600 | 2 (100)| 00:00:01 || 6 | HASH GROUP BY | | 100 | 9100 | 2 (100)| 00:00:01 ||* 7 | HASH JOIN OUTER | | 100 | 9100 | 1 (100)| 00:00:01 || 8 | FIXED TABLE FULL | X$KCCRSR | 100 | 2600 | 0 (0)| 00:00:01 || 9 | VIEW | | 1 | 65 | 1 (100)| 00:00:01 || 10 | HASH GROUP BY | | 1 | 65 | 1 (100)| 00:00:01 || 11 | FIXED TABLE FULL| X$KSFQP | 1 | 65 | | ||* 12 | FILTER | | | | | ||* 13 | HASH JOIN OUTER | | 100 | 10100 | 0 (0)| 00:00:01 || 14 | FIXED TABLE FULL | X$KCCRSR | 100 | 4400 | 0 (0)| 00:00:01 ||* 15 | FIXED TABLE FULL | X$KRBMRST | 1 | 57 | | || 16 | VIEW | | 100 | 2600 | 2 (100)| 00:00:01 || 17 | HASH UNIQUE | | 100 | 6200 | 2 (100)| 00:00:01 ||* 18 | HASH JOIN OUTER | | 100 | 6200 | 1 (100)| 00:00:01 || 19 | FIXED TABLE FULL | X$KCCRSR | 100 | 2600 | 0 (0)| 00:00:01 || 20 | VIEW | | 1 | 36 | 1 (100)| 00:00:01 || 21 | WINDOW SORT | | 1 | 49 | 1 (100)| 00:00:01 ||* 22 | FIXED TABLE FULL | X$KSFQP | 1 | 49 | | |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - access("ODEV"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND"ODEV"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST"))4 - access("HH"."RECID"=NVL("ID_KRBMRST","R2"."RSRRID") AND"HH"."STAMP"=NVL("STAMP_KRBMRST","R2"."RSRTST"))7 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID"(+) AND"R"."RSRTST"="RS"."RMAN_STATUS_STAMP"(+))12 - filter(NVL(UPPER("OPER_KRBMRST"),UPPER("R2"."RSROP"))='BLOCK MEDIARECOVERY')13 - access("R2"."RSRRID"="ID_KRBMRST"(+) AND"R2"."RSRTST"="STAMP_KRBMRST"(+))15 - filter("STATUS_KRBMRST"(+)=1 OR "STATUS_KRBMRST"(+)=9 OR"STATUS_KRBMRST"(+)=17 OR "STATUS_KRBMRST"(+)=25)18 - access("R"."RSRRID"="RS"."RMAN_STATUS_RECID"(+) AND"R"."RSRTST"="RS"."RMAN_STATUS_STAMP"(+))22 - filter("TYPE"=2)Statistics----------------------------------------------------------0 recursive calls0 db block gets0 consistent gets0 physical reads0 redo size650 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed
通过10053看下SQL转换情况
alter session set tracefile_identifier='10053';alter session set events='10053 trace name context forever,level 1';select count(*) from v$rman_status;alter session set events '10053 trace name context off';select value from v$diag_info where name='Default Trace File';
查看SQL转换
原始SQL
******************************************----- Current SQL Statement for thissession (sql_id=bv3y9sfd6dx38) -----select p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY')*******************************************
转换后的SQL
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."P" "P", NULL "NULL", NULL "NULL"FROM (SELECT COUNT(*) "P"FROM SYS."X$KCCRSR" "R2",SYS."X$KRBMRST" "X$KRBMRST",(SELECT "R"."RSRRID" "RECID","R"."RSRTST" "STAMP",SUM("RS"."AGGRCOL") 1048576 "MBYTES",SUM("RS"."INPCOL") "INPBYTES",SUM("RS"."OUTCOL") "OUTBYTES"FROM SYS."X$KCCRSR" "R",(SELECT "X$KSFQP"."RMAN_STATUS_RECID" "RMAN_STATUS_RECID","X$KSFQP"."RMAN_STATUS_STAMP" "RMAN_STATUS_STAMP",SUM(CASE "X$KSFQP"."TYPE"WHEN 3 THEN"X$KSFQP"."BLOCKS" *"X$KSFQP"."BLOCK_SIZE"ELSE0END) "AGGRCOL",SUM(CASE "X$KSFQP"."TYPE"WHEN 1 THEN"X$KSFQP"."BLOCKS" *"X$KSFQP"."BLOCK_SIZE"ELSE0END) "INPCOL",SUM(CASE "X$KSFQP"."TYPE"WHEN 2 THEN"X$KSFQP"."BLOCKS" *"X$KSFQP"."BLOCK_SIZE"ELSE0END) "OUTCOL"FROM SYS."X$KSFQP" "X$KSFQP"GROUP BY "X$KSFQP"."RMAN_STATUS_RECID","X$KSFQP"."RMAN_STATUS_STAMP") "RS"WHERE "R"."RSRRID" = "RS"."RMAN_STATUS_RECID"(+)AND "R"."RSRTST" = "RS"."RMAN_STATUS_STAMP"(+)GROUP BY "R"."RSRRID", "R"."RSRTST") "HH",(SELECT DISTINCT "R"."RSRRID" "RECID","R"."RSRTST" "STAMP","RS"."DEVICE_TYPE" "DEVICE_TYPE"FROM SYS."X$KCCRSR" "R",(SELECT "X$KSFQP"."RMAN_STATUS_RECID" "RMAN_STATUS_RECID","X$KSFQP"."RMAN_STATUS_STAMP" "RMAN_STATUS_STAMP",DECODE(COUNT(DISTINCT "X$KSFQP"."DEVTYPE")OVER(PARTITION BY"X$KSFQP"."RMAN_STATUS_RECID","X$KSFQP"."RMAN_STATUS_STAMP"),1,FIRST_VALUE("X$KSFQP"."DEVTYPE")OVER(PARTITION BY"X$KSFQP"."RMAN_STATUS_RECID","X$KSFQP"."RMAN_STATUS_STAMP"),0,NULL,'*') "DEVICE_TYPE"FROM SYS."X$KSFQP" "X$KSFQP"WHERE "X$KSFQP"."TYPE" = 2) "RS"WHERE "R"."RSRRID" = "RS"."RMAN_STATUS_RECID"(+)AND "R"."RSRTST" = "RS"."RMAN_STATUS_STAMP"(+)) "ODEV"WHERE NVL(UPPER("X$KRBMRST"."OPER_KRBMRST"), UPPER("R2"."RSROP")) ='BLOCK MEDIA RECOVERY'AND "HH"."RECID" = NVL("X$KRBMRST"."ID_KRBMRST", "R2"."RSRRID")AND "HH"."STAMP" =NVL("X$KRBMRST"."STAMP_KRBMRST", "R2"."RSRTST")AND "ODEV"."RECID" = NVL("X$KRBMRST"."ID_KRBMRST", "R2"."RSRRID")AND "ODEV"."STAMP" =NVL("X$KRBMRST"."STAMP_KRBMRST", "R2"."RSRTST")AND "R2"."RSRRID" = "X$KRBMRST"."ID_KRBMRST"(+)AND "R2"."RSRTST" = "X$KRBMRST"."STAMP_KRBMRST"(+)AND ("X$KRBMRST"."STATUS_KRBMRST"(+) = 1 OR"X$KRBMRST"."STATUS_KRBMRST"(+) = 9 OR"X$KRBMRST"."STATUS_KRBMRST"(+) = 17 OR"X$KRBMRST"."STATUS_KRBMRST"(+) = 25)) "from$_subquery$_001"
相关BUG描述：
Bug 14078947 - Select from V$RMAN_STATUS very slow when database is open (Doc ID 14078947.8)
文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
