[20231017]使用dbms_xplan.display_awr查詢遇到的問題.txt

lfree發表於2023-10-23

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章