Oracle某X系統SQL最佳化(案例六)
環境說明：
DB ：Oracle 11.2.0.4.0
OS: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-oracle is 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-oracle is warnning!(10.0.0.100-oracle)[CJC 集團], 故障恢復時間:2022-11-15 07:37:00
問題分析：
收集問題時間段的 AWR 和 ASH 報告，有兩個 SQL 耗時 900 秒，和故障時間完全吻合。
耗時SQL 文字如下：
---1
900 秒
begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
---2
900.24 秒
SQL_ID:2d1p0p5k3f8fu
select p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY');
生成SQL 執行計劃
set linesize 150
set pagesize 2000
select * from TABLE(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 MEDIA
RECOVERY' 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; ---4512 select count(*) from sys.X$KSFQP; ---1703 select count(*) from sys.X$KRBMRST; ---0
表雖然都不大，但是透過MERGE JOIN CARTESIAN 連線方式，最多可以生成4512*1703=7683936 結果集。
檢視歷史執行資訊，執行開始時間沒什麼規律，單次執行耗時基本都是15 分鐘。
SET PAGESIZE 10000 SET LINE 300 COL EVENT FOR A30 select TO_CHAR(SAMPLE_TIME,'YYYY-MM-DD HH24:MI:SS') TIME,SQL_ID,EVENT,USER_ID FROM dba_hist_active_sess_history WHERE SQL_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 ......
解決方案
透過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 on select p, NULL, NULL from (select count(*) p from v$rman_status where operation = 'BLOCK MEDIA RECOVERY'); P N N ---------- - - 0 Elapsed: 00:00:00.07
檢視新執行計劃，表連線方式已經由MERGE JOIN CARTESIAN 換成HASH JOIN 。
set line 300
set pagesize 1000
set autotrace on
select 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 MEDIA
RECOVERY')
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 calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 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 this session (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"
ELSE
0
END) "AGGRCOL",
SUM(CASE "X$KSFQP"."TYPE"
WHEN 1 THEN
"X$KSFQP"."BLOCKS" *
"X$KSFQP"."BLOCK_SIZE"
ELSE
0
END) "INPCOL",
SUM(CASE "X$KSFQP"."TYPE"
WHEN 2 THEN
"X$KSFQP"."BLOCKS" *
"X$KSFQP"."BLOCK_SIZE"
ELSE
0
END) "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"
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2924173/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle某行系統SQL最佳化(案例四)OracleSQL
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- Oracle某行系統SQL優化(案例五)OracleSQL優化
- Oracle某行系統SQL優化案例(二)OracleSQL優化
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- 某保險理賠核心OB SQL最佳化案例SQL
- SQL最佳化案例-分割槽索引之無字首索引（六）SQL索引
- DM 傳統行業SQL最佳化案例行業SQL
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- SQL最佳化案例-union代替or（九）SQL
- Oracle "腦殘" CBO 最佳化案例Oracle
- SQL最佳化案例-使用with as最佳化Subquery Unnesting（七）SQL
- Oracle診斷案例-Sql_traceOracleSQL
- OB案例、金融行業核心系統跑批SQL最佳化行業SQL
- Mac系統清理最佳化工具——CleanMyMac X for macMac
- Mac垃圾清理系統最佳化工具：CleanMyMac XMac
- 一次系統延遲性最佳化案例
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- oracle一次卡頓案例(六)-latch freeOracle
- SQL最佳化案例-正確的使用索引（二）SQL索引
- SQL最佳化案例-自定義函式索引（五）SQL函式索引
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- SQL優化案例-定位系統中大量的rollback（十八）SQL優化
- SQL最佳化案例-單表分頁語句的最佳化（八）SQL
- Oracle_SQL部分_時間轉換(案例一)OracleSQL
- Oracle 11gRac 測試案例（三）系統測試Oracle
- 幫任總DBA太太最佳化條金融SQL案例（DM資料庫案例）SQL資料庫
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- Oracle 11gRac 測試案例(二)系統測試（一）Oracle
- kingbase SQL最佳化案例 ( union遞迴 改 cte遞迴 )SQL遞迴
- SQL最佳化 | MySQL問題處理案例分享三則MySql
- 資料庫最佳化案例—某市中心醫院HIS系統資料庫
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- Oracle優化案例-復現SQL ordered by Parse Calls（三十二）Oracle優化SQL
- 系統最佳化