[20231017]使用dbms_xplan.display_awr查询遇到的问题.txt

来源：这里教程网时间：2026-03-03 19:02:03 作者：

[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

编辑推荐：

返回列表

[20231017]使用dbms_xplan.display_awr查询遇到的问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推