[20250304]记录19c修改AWR_CDB_SYSSTAT视图定义.txt --//19c AWR_CDB_SYSSTAT视图定义里面存在sql提示影响相关sql语句执行效率，涉及监控语句有许多条，简单直接视图定义，顺序做1个 --//记录，方便出现问题还原。 1.环境： SYS@127.0.0.1:9105/xtdb/xtdb1> @ ver2 ============================== 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.14.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. 2.原始视图定义： --//直接从toad上抽取，这样操作简单一些，而且toad缺省做了格式化处理。 CREATE OR REPLACE FORCE VIEW SYS.AWR_CDB_SYSSTAT (SNAP_ID, DBID, INSTANCE_NUMBER, STAT_ID, STAT_NAME, VALUE, CON_DBID, CON_ID) BEQUEATH DEFINER AS SELECT /*+ leading(sn s nm) use_hash(sn s) */ s.snap_id ,s.dbid ,s.instance_number ,s.stat_id ,nm.stat_name ,VALUE ,DECODE (s.con_dbid, 0, s.dbid, s.con_dbid) ,con_dbid_to_id (DECODE (s.con_dbid, 0, s.dbid, s.con_dbid)) con_id FROM WRM$_SNAPSHOT sn ,WRH$_SYSSTAT s ,WRH$_STAT_NAME nm WHERE s.stat_id = nm.stat_id AND s.dbid = nm.dbid AND s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number AND sn.status = 0; COMMENT ON TABLE SYS.AWR_CDB_SYSSTAT IS 'System Historical Statistics Information'; CREATE OR REPLACE PUBLIC SYNONYM AWR_CDB_SYSSTAT FOR SYS.AWR_CDB_SYSSTAT; GRANT SELECT ON SYS.AWR_CDB_SYSSTAT TO SELECT_CATALOG_ROLE; 3.修改定义： CREATE OR REPLACE FORCE VIEW SYS.AWR_CDB_SYSSTAT (SNAP_ID, DBID, INSTANCE_NUMBER, STAT_ID, STAT_NAME, VALUE, CON_DBID, CON_ID) BEQUEATH DEFINER AS SELECT /*+ ,leading(sn s nm) use_hash(sn s) */ s.snap_id ,s.dbid ,s.instance_number ,s.stat_id ,nm.stat_name ,VALUE ,DECODE (s.con_dbid, 0, s.dbid, s.con_dbid) ,con_dbid_to_id (DECODE (s.con_dbid, 0, s.dbid, s.con_dbid)) con_id FROM WRM$_SNAPSHOT sn ,WRH$_SYSSTAT s ,WRH$_STAT_NAME nm WHERE s.stat_id = nm.stat_id AND s.dbid = nm.dbid AND s.snap_id = sn.snap_id AND s.dbid = sn.dbid AND s.instance_number = sn.instance_number AND sn.status = 0; --//注意提示前加入逗号，相当于提示无效。这样即使修改回去也很容易. COMMENT ON TABLE SYS.AWR_CDB_SYSSTAT IS 'System Historical Statistics Information'; --//检查ok。 4.验证相关sql语句执行情况。 --//例子： VAR dbid NUMBER VAR inst_num NUMBER VAR eid NUMBER VAR bid NUMBER BEGIN SELECT dbid, USERENV('instance') INTO :dbid, :inst_num FROM v$database; SELECT MAX(snap_id) INTO :eid FROM dba_hist_snapshot WHERE dbid = :dbid AND instance_number = :inst_num; SELECT MAX(snap_id) INTO :bid FROM dba_hist_snapshot WHERE dbid = :dbid AND instance_number = :inst_num AND snap_id < :eid; END; / SELECT snap_id,instance_number,VALUE, stat_name FROM DBA_HIST_SYSSTAT WHERE stat_name IN ('physical reads direct (lob)', 'physical reads', 'physical reads direct') AND snap_id IN (:bid, :eid) AND instance_number = :inst_num AND dbid = :dbid; --//修改前执行计划: Plan hash value: 4149710619 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | | | 6 |00:00:00.02 | 3882 | | | | | 1 | NESTED LOOPS | | 1 | 1 | 89 | 10 (0)| 00:00:01 | | | 6 |00:00:00.02 | 3882 | | | | | 2 | NESTED LOOPS | | 1 | 1 | 89 | 10 (0)| 00:00:01 | | | 4072 |00:00:00.02 | 3845 | | | | |* 3 | HASH JOIN | | 1 | 1 | 43 | 8 (0)| 00:00:01 | | | 4072 |00:00:00.01 | 3724 | 3289K| 3289K| 2207K (0)| | 4 | INLIST ITERATOR | | 1 | | | | | | | 2 |00:00:00.01 | 6 | | | | |* 5 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 2 | 1 | 18 | 3 (0)| 00:00:01 | | | 2 |00:00:00.01 | 6 | | | | |* 6 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 2 | 1 | | 2 (0)| 00:00:01 | | | 2 |00:00:00.01 | 4 | 1025K| 1025K| | | 7 | INLIST ITERATOR | | 1 | | | | | | | 4072 |00:00:00.01 | 3718 | | | | | 8 | PARTITION RANGE ITERATOR | | 2 | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.01 | 3718 | | | | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSSTAT | 2 | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.01 | 3718 | | | | |* 10 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 2 | 1 | | 4 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.01 | 35 | 1025K| 1025K| | |* 11 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 4072 | 1 | | 1 (0)| 00:00:01 | | | 4072 |00:00:00.01 | 121 | 1025K| 1025K| | |* 12 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 4072 | 1 | 46 | 2 (0)| 00:00:01 | | | 6 |00:00:00.01 | 37 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//修改后执行计划: Plan hash value: 1091456696 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | | | 6 |00:00:00.06 | 7958 | | 1 | NESTED LOOPS | | 1 | 1 | 89 | 7 (0)| 00:00:01 | | | 6 |00:00:00.06 | 7958 | | 2 | NESTED LOOPS | | 1 | 1 | 89 | 7 (0)| 00:00:01 | | | 4072 |00:00:00.05 | 7921 | | 3 | NESTED LOOPS | | 1 | 1 | 43 | 5 (0)| 00:00:01 | | | 4072 |00:00:00.04 | 7800 | | 4 | INLIST ITERATOR | | 1 | | | | | | | 4072 |00:00:00.02 | 3718 | | 5 | PARTITION RANGE ITERATOR | | 2 | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.02 | 3718 | | 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| WRH$_SYSSTAT | 2 | 1 | 25 | 5 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.01 | 3718 | |* 7 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 2 | 1 | | 4 (0)| 00:00:01 |KEY(I) |KEY(I) | 4072 |00:00:00.01 | 35 | |* 8 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 4072 | 1 | 18 | 0 (0)| | | | 4072 |00:00:00.02 | 4082 | |* 9 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 4072 | 1 | | 0 (0)| | | | 4072 |00:00:00.01 | 10 | |* 10 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 4072 | 1 | | 1 (0)| 00:00:01 | | | 4072 |00:00:00.01 | 121 | |* 11 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 4072 | 1 | 46 | 2 (0)| 00:00:01 | | | 6 |00:00:00.01 | 37 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --//逻辑读反而更高，重新分析3个表后: Plan hash value: 317164640 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | | | 6 |00:00:00.01 | 57 | | | | |* 1 | HASH JOIN | | 1 | 7 | 616 | 30 (0)| 00:00:01 | | | 6 |00:00:00.01 | 57 | 2300K| 2300K| 1759K (0)| | 2 | NESTED LOOPS | | 1 | 7 | 497 | 26 (0)| 00:00:01 | | | 6 |00:00:00.01 | 51 | | | | | 3 | NESTED LOOPS | | 1 | 7 | 497 | 26 (0)| 00:00:01 | | | 6 |00:00:00.01 | 45 | | | | |* 4 | TABLE ACCESS STORAGE FULL | WRH$_STAT_NAME | 1 | 3 | 138 | 11 (0)| 00:00:01 | | | 3 |00:00:00.01 | 37 | 1025K| 1025K| | | 5 | INLIST ITERATOR | | 3 | | | | | | | 6 |00:00:00.01 | 8 | | | | | 6 | PARTITION RANGE ITERATOR | | 6 | 2 | | 3 (0)| 00:00:01 |KEY(I) |KEY(I) | 6 |00:00:00.01 | 8 | | | | |* 7 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 6 | 2 | | 3 (0)| 00:00:01 |KEY(I) |KEY(I) | 6 |00:00:00.01 | 8 | 1025K| 1025K| | | 8 | TABLE ACCESS BY LOCAL INDEX ROWID| WRH$_SYSSTAT | 6 | 2 | 50 | 5 (0)| 00:00:01 | 1 | 1 | 6 |00:00:00.01 | 6 | | | | | 9 | INLIST ITERATOR | | 1 | | | | | | | 2 |00:00:00.01 | 6 | | | | |* 10 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 2 | 2 | 34 | 4 (0)| 00:00:01 | | | 2 |00:00:00.01 | 6 | | | | |* 11 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 2 | 2 | | 2 (0)| 00:00:01 | | | 2 |00:00:00.01 | 4 | 1025K| 1025K| | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//这样问题解决.
[20250304]记录19c修改AWR_CDB_SYSSTAT视图定义.txt
来源：这里教程网
时间：2026-03-03 21:44:33
作者：
编辑推荐：
- [20250304]记录19c修改AWR_CDB_SYSSTAT视图定义.txt03-03
- [20250307]关于use_hash提示遇到的问题.txt03-03
- [20250313]建立完善ooerr小程序.txt03-03
- 同步工具导致Oracle ASM实例审计日志暴增03-03
- 一次ASM磁盘增加导致的故障分享03-03
- Oracle 多租户ORA-65023: active transaction exists in container CDB$ROOT03-03
- 数据库管理-第300期 一个不痛不痒的BUG（20250307）03-03
- 电商采集拼多多商家联系方式批量提取教程03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- AI的SQL优化能力，取决于你问问题的能力！
AI的SQL优化能力，取决于你问问题的能力！
26-03-03 - 一次ASM磁盘增加导致的故障分享
一次ASM磁盘增加导致的故障分享
26-03-03 - Oracle 多租户ORA-65023: active transaction exists in container CDB$ROOT
- 数据库管理-第300期 一个不痛不痒的BUG（20250307）
数据库管理-第300期 一个不痛不痒的BUG（20250307）
26-03-03 - 第34期 CURSOR_SHARING和FORCE_MATCHING_SIGNATURE对SQL计划稳定性的局限性
- windows下oracle打完补丁不能正常启动服务
windows下oracle打完补丁不能正常启动服务
26-03-03 - 一次数据库访问异常分析
一次数据库访问异常分析
26-03-03 - 一次Undo表空间耗尽问题
一次Undo表空间耗尽问题
26-03-03 - 19C统计信息引发的数据库慢问题
19C统计信息引发的数据库慢问题
26-03-03 - SQL优化之数据倾斜解决方案
SQL优化之数据倾斜解决方案
26-03-03
