[20231017]使用dbms_xplan.display_awr查詢遇到的問題.txt
[20231017]使用dbms_xplan.display_awr查詢遇到的問題.txt
--//記錄生產系統使用dbms_xplan.display_awr遇到的問題。
--//情況簡介：生產系統一些查詢移到備庫執行，主庫不會執行該語句。
1.環境：
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@192.168.100.237:1521/orcldg> @ ashtop sql_id,module1 1=1 &day
Total Distinct Distinct
Seconds AAS %This SQL_ID MODULE1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps
------- ------- ------- ------------- ------------------------- ------------------- ------------------- ---------- --------
34404 .4 44% | 2023-10-09 12:03:16 2023-10-10 11:47:48 1 17366
25326 .3 32% | 8vjypw6pm2wtf w3wp.exe 2023-10-09 12:03:14 2023-10-10 11:47:47 10908 20061
5584 .1 7% | oracle@lis-db (tns v1-v3) 2023-10-09 12:03:21 2023-10-10 11:47:41 1 5584
...
--//該語句非常特殊sql_id=8vjypw6pm2wtf,僅僅在備庫上執行。
2.分析遇到的問題：
SYS@192.168.100.237:1521/orcldg> @ sqlhh 8vjypw6pm2wtf 1
time unit : millisecond
BEGIN_INTERVAL_TIME INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2023-10-09 12:00:52 1 8vjypw6pm2wtf 2853834104 6342 331 10 61.6 795 32 322 10.0 0 0 0
2023-10-09 13:00:55 1 8vjypw6pm2wtf 2853834104 5398 306 10 50.2 653 35 298 8.6 0 0 0
2023-10-09 14:00:57 1 8vjypw6pm2wtf 2853834104 5317 212 7 47.3 587 22 205 9.5 0 0 0
....
2023-10-10 10:00:50 1 8vjypw6pm2wtf 4066876392 11321 222 9 60.7 796 21 214 2421617.2 0 0 0
23 rows selected.
--//出現兩種執行計劃PLAN_HASH_VALUE=2853834104,4066876392,想看看存在什麼不同之處。
--//注:實際上在備庫執行的.之所以查詢sqlhh.sql指令碼有記錄，因為我建立備庫awr報表。
--//參考連結：[20230220][20230110]生成相關備庫的awr報表=>http://blog.itpub.net/267265/viewspace-2936073/
--//這條類似語句我以前看過，開發做了小量改寫。注意看邏輯讀並不是很高，已經不存在最佳化的可能性。
--//問題主要在於備庫的磁碟效能太差(建立在虛擬機器上），並且sga太小，無法快取查詢資料，導致許多查詢訪問磁碟IO。
SYS@192.168.100.237:1521/orcldg> @ dpcawrh 8vjypw6pm2wtf '' 2853834104
no rows selected
SYS@192.168.100.237:1521/orcldg> @ dpcawrh 8vjypw6pm2wtf '' 4066876392
no rows selected
--//居然無法查詢.為什麼?
SYS@192.168.100.237:1521/orcldg> @ desc_proc sys dbms_xplan display_awr
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED
---------- ------------ ----------- ---------- --------------- --------- --------- ----------
SYS DBMS_XPLAN DISPLAY_AWR 1 TABLE OUT N
2 SQL_ID VARCHAR2 IN N
3 PLAN_HASH_VALUE NUMBER IN Y
4 DB_ID NUMBER IN Y
5 FORMAT VARCHAR2 IN Y
6 CON_ID NUMBER IN Y
7 AWR_LOCATION VARCHAR2 IN Y
7 rows selected.
--//因為這條語句在備庫執行的,dbms_xplan display_awr支援輸入db_id,預設NULL，而備庫awr記錄的db_id實際上與主庫不同.
SYS@192.168.100.237:1521/orcldg> select dbid from v$database;
DBID
----------
1585360079
--//dbid=1585360079 是真實資料庫的dbid.
SYS@192.168.100.237:1521/orcldg> select distinct dbid from dba_hist_snapshot;
DBID
----------
1585360079
18526484
2 rows selected.
--//dbid=18526484才是備庫記錄在awr歷史資料的dbid.也就是我的查詢要加入db_id=18526484才行，手工執行如下：
SYS@192.168.100.237:1521/orcldg> select * from table(dbms_xplan.display_awr('8vjypw6pm2wtf',2853834104,18526484,'all
allstats last peeked_binds cost partition note -projection -outline'));
Plan hash value: 2853834104
-----------------------------------------------------------------------------------------------------------
|Id|Operation |Name |E-Rows|E-Bytes|Cost(%CPU)|E-Time |
-----------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 32 (100)| |
| 1| SORT ORDER BY | | 18 | 42426 | 32 (10)|00:00:01|
| 2| HASH UNIQUE | | 18 | 42426 | 31 (7)|00:00:01|
| 3| VIEW | | 18 | 42426 | 30 (4)|00:00:01|
| 4| WINDOW SORT PUSHED RANK | | 18 | 6894 | 30 (4)|00:00:01|
| 5| NESTED LOOPS | | 18 | 6894 | 29 (0)|00:00:01|
| 6| NESTED LOOPS | | 22 | 6894 | 29 (0)|00:00:01|
| 7| NESTED LOOPS OUTER | | 1 | 374 | 22 (0)|00:00:01|
| 8| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 1 | 284 | 19 (0)|00:00:01|
| 9| BITMAP CONVERSION TO ROWIDS | | | | | |
|10| BITMAP OR | | | | | |
|11| BITMAP CONVERSION FROM ROWIDS | | | | | |
|12| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 116K| | 3 (0)|00:00:01|
|13| BITMAP CONVERSION FROM ROWIDS | | | | | |
|14| INDEX RANGE SCAN |IX_LIS_TEST_IDENTITY_ID| 116K| | 3 (0)|00:00:01|
|15| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_PROMPT | 1 | 90 | 3 (0)|00:00:01|
|16| INDEX RANGE SCAN |IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)|00:00:01|
|17| INDEX RANGE SCAN |PK_LIS_RESULT | 22 | | 3 (0)|00:00:01|
|18| TABLE ACCESS BY INDEX ROWID |LIS_RESULT | 22 | 198 | 7 (0)|00:00:01|
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-----------------------------------------------------------
1 - SEL$1
3 - SEL$98196233 / RE@SEL$1
4 - SEL$98196233
8 - SEL$98196233 / A@SEL$2
15 - SEL$98196233 / C@SEL$3
16 - SEL$98196233 / C@SEL$3
17 - SEL$98196233 / B@SEL$2
18 - SEL$98196233 / B@SEL$2
Peeked Binds (identified by position):
-----------------------------------
1 - :STR_MZHM (CHAR(30), CSID=852): '450121199107154539undefined'
2 - :STR_SFZH (CHAR(30), CSID=852): (null)
3 - :STR_DTTO (CHAR(30), CSID=852): '2023-06-9'
--//怎麼傳入的STR_MZHM後面會多1個undefined。日期引數2023-06-9。
SYS@192.168.100.237:1521/orcldg> select * from table(dbms_xplan.display_awr('8vjypw6pm2wtf',4066876392,18526484,'all
allstats last peeked_binds cost partition note -projection -outline'));
Plan hash value: 4066876392
-----------------------------------------------------------------------------------------------------------
|Id|Operation |Name |E-Rows|E-Bytes|Cost(%CPU)|E-Time |
-----------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 49 (100)| |
| 1| SORT ORDER BY | | 36 | 84852 | 49 (7)|00:00:01|
| 2| HASH UNIQUE | | 36 | 84852 | 48 (5)|00:00:01|
| 3| VIEW | | 36 | 84852 | 47 (3)|00:00:01|
| 4| WINDOW SORT PUSHED RANK | | 36 | 13788 | 47 (3)|00:00:01|
| 5| NESTED LOOPS | | 36 | 13788 | 46 (0)|00:00:01|
| 6| NESTED LOOPS | | 44 | 13788 | 46 (0)|00:00:01|
| 7| NESTED LOOPS OUTER | | 2 | 748 | 36 (0)|00:00:01|
| 8| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_TEST | 2 | 568 | 31 (0)|00:00:01|
| 9| BITMAP CONVERSION TO ROWIDS | | | | | |
|10| BITMAP OR | | | | | |
|11| BITMAP CONVERSION FROM ROWIDS | | | | | |
|12| INDEX RANGE SCAN |IX_LIS_TEST_IDENTITY_ID| 116K| | 4 (0)|00:00:01|
|13| BITMAP CONVERSION FROM ROWIDS | | | | | |
|14| INDEX RANGE SCAN |IX_LIS_TEST_PAT_ID | 116K| | 3 (0)|00:00:01|
|15| TABLE ACCESS BY INDEX ROWID BATCHED|LIS_PROMPT | 1 | 90 | 3 (0)|00:00:01|
|16| INDEX RANGE SCAN |IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)|00:00:01|
|17| INDEX RANGE SCAN |PK_LIS_RESULT | 22 | | 3 (0)|00:00:01|
|18| TABLE ACCESS BY INDEX ROWID |LIS_RESULT | 22 | 198 | 7 (0)|00:00:01|
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$98196233 / RE@SEL$1
4 - SEL$98196233
8 - SEL$98196233 / A@SEL$2
15 - SEL$98196233 / C@SEL$3
16 - SEL$98196233 / C@SEL$3
17 - SEL$98196233 / B@SEL$2
18 - SEL$98196233 / B@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - :STR_MZHM (CHAR(30), CSID=852): '91293170'
2 - :STR_SFZH (CHAR(30), CSID=852): '36073319951012052X'
3 - :STR_DTTO (CHAR(30), CSID=852): '2000-01-01'
--//僅僅做點陣圖或的順序不同.
3.相關類似問題:
--//如果你使用tpt的dashtop指令碼注意,如果配置收集備庫資訊,使用dashtop訪問的是dba_hist_active_sess_history檢視,
--//但是查詢條件裡面加入了 AND a.dbid = (SELECT d.dbid FROM v$database d).
--//這樣看到的主庫的awr相關資訊,並不會出現主庫與備庫合集的情況.
4.附上dpcawrh.sql指令碼:
$ cat dpcawrh.sql
-- argument1=sql_id argument2=format argument3=plan_hash_value
set verify off
select * from table(dbms_xplan.display_awr('&1',nvl('&3',null),null,'all allstats last peeked_binds cost partition note -projection -outline &2'));
@ dpcformat.sql
--//感覺自己應該建立一個新的dpcawrhd.sql指令碼。
$ cat dpcawrhd.sql
-- argument1=sql_id argument2=format argument3=plan_hash_value argument4=db_id
set verify off
select * from table(dbms_xplan.display_awr('&1',nvl('&3',null),nvl('&4',null),'all allstats last peeked_binds cost partition note -projection -outline &2'));
@ dpcformat.sql
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2990581/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220308]查詢x$ksmmem遇到的疑問.txt
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20210418]查詢v$檢視問題.txt
- [20210208]lob欄位與查詢的問題.txt
- [20240309]在windwos下使用sed遇到的問題.txt
- [20211220]關於標量子查詢問題.txt
- [20241110]使用xto10.sql指令碼遇到的問題.txtSQL指令碼
- [20211210]優化遇到的奇怪問題.txt優化
- [20221125]設定hugepages遇到的問題.txt
- [20230308]12c以上版本模糊查詢問題.txt
- [20211221]分析sql語句遇到的問題.txtSQL
- 使用git遇到的問題Git
- Java 中如何使用 SQL 查詢 TXTJavaSQL
- [20231017]使用dbms_workload_repository.add_colored_sql之2.txtSQL
- laravel使用中遇到的問題Laravel
- Go mod 使用遇到的問題Go
- c++使用遇到的問題C++
- 關於Room資料庫，拼寫模糊查詢語句遇到的問題OOM資料庫
- 同一欄位多個查詢條件時遇到的一個問題
- [20231102]除錯bash shell指令碼遇到的問題.txt除錯指令碼
- [20210107]編寫bash shell指令碼遇到的問題.txt指令碼
- 使用並查集處理集合的合併和查詢問題並查集
- [20181227]bbed的使用問題.txt
- sql 模糊查詢問題SQL
- 使用javap -v 命令遇到的問題Java
- 使用CodeMirror外掛遇到的問題
- PaddleOCR 安裝使用遇到的問題
- 使用git add 遇到的小問題Git
- Composer 使用遇到問題求助
- Laravel 中 sql 查詢 使用 group by 報錯問題。LaravelSQL
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- [20190202]使用smem查詢oracle記憶體使用.txtOracle記憶體
- leetcode題解(查詢表問題)LeetCode
- [20190314]使用strace注意的問題.txt
- [20181217]strace使用問題.txt
- 一個MySQL多表查詢的問題MySql
- Laravel5.7 查詢問題Laravel
- sphinx查詢過濾問題