[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| |
———————————————————————————————————————————————————————————————
–//这样问题解决.
来源链接：https://www.cnblogs.com/lfree/p/18771124
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容