[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2990581/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
