导致归档居多的SQL语句

ry715

老师，我有一个库，最近归档特别多，我就用toad看了一下，其中有一条语句特别频繁的出现，不知道您能帮忙看一下，可能是什么原因造成的吗，或者给个排查的思路，谢谢老师啦。

1. delete from "SYS"."WRH$_SQLSTAT" where "SNAP_ID" = '11392' and "DBID" = '750028045' and "INSTANCE_NUMBER" = '2' and "SQL_ID" = '7gzxf61vj6wq4' and "PLAN_HASH_VALUE" = '3742463261' and "OPTIMIZER_COST" = '2' and "OPTIMIZER_MODE" = 'ALL_ROWS' and "OPTIMIZER_ENV_HASH_VALUE" = '3539190100' and "SHARABLE_MEM" = '354358' and "LOADED_VERSIONS" = '17' and "VERSION_COUNT" = '19' and "MODULE" IS NULL and "ACTION" IS NULL and "SQL_PROFILE" IS NULL and "FORCE_MATCHING_SIGNATURE" = '1434349337146538306' and "PARSING_SCHEMA_ID" = '0' and "PARSING_SCHEMA_NAME" = 'SYS' and "FETCHES_TOTAL" = '1266' and "FETCHES_DELTA" = '5' and "END_OF_FETCH_COUNT_TOTAL" = '1266' and "END_OF_FETCH_COUNT_DELTA" = '5' and "SORTS_TOTAL" = '0' and "SORTS_DELTA" = '0' and "EXECUTIONS_TOTAL" = '1266' and "EXECUTIONS_DELTA" = '5' and "PX_SERVERS_EXECS_TOTAL" = '0' and "PX_SERVERS_EXECS_DELTA" = '0' and "LOADS_TOTAL" = '42' and "LOADS_DELTA" = '0' and "INVALIDATIONS_TOTAL" = '0' and "INVALIDATIONS_DELTA" = '0' and "PARSE_CALLS_TOTAL" = '259' and "PARSE_CALLS_DELTA" = '1' and "DISK_READS_TOTAL" = '0' and "DISK_READS_DELTA" = '0' and "BUFFER_GETS_TOTAL" = '3890' and "BUFFER_GETS_DELTA" = '15' and "ROWS_PROCESSED_TOTAL" = '1266' and "ROWS_PROCESSED_DELTA" = '5' and "CPU_TIME_TOTAL" = '223000' and "CPU_TIME_DELTA" = '0' and "ELAPSED_TIME_TOTAL" = '254610' and "ELAPSED_TIME_DELTA" = '467' and "IOWAIT_TOTAL" = '18' and "IOWAIT_DELTA" = '0' and "CLWAIT_TOTAL" = '7269' and "CLWAIT_DELTA" = '0' and "APWAIT_TOTAL" = '0' and "APWAIT_DELTA" = '0' and "CCWAIT_TOTAL" = '3029' and "CCWAIT_DELTA" = '0' and "DIRECT_WRITES_TOTAL" = '0' and "DIRECT_WRITES_DELTA" = '0' and "PLSEXEC_TIME_TOTAL" = '0' and "PLSEXEC_TIME_DELTA" = '0' and "JAVEXEC_TIME_TOTAL" = '0' and "JAVEXEC_TIME_DELTA" = '0' and "BIND_DATA" = HEXTORAW('beda0b20040056678b2900040ff001200354164144444d3a3735303032383034355f325f3131333931c0021602c105c0021602c105c002160180') and "FLAG" IS NULL and "PARSING_USER_ID" = '0' and "IO_OFFLOAD_ELIG_BYTES_TOTAL" = '0' and "IO_OFFLOAD_ELIG_BYTES_DELTA" = '0' and "IO_INTERCONNECT_BYTES_TOTAL" = '0' and "IO_INTERCONNECT_BYTES_DELTA" = '0' and "PHYSICAL_READ_REQUESTS_TOTAL" = '0' and "PHYSICAL_READ_REQUESTS_DELTA" = '0' and "PHYSICAL_READ_BYTES_TOTAL" = '0' and "PHYSICAL_READ_BYTES_DELTA" = '0' and "PHYSICAL_WRITE_REQUESTS_TOTAL" = '0' and "PHYSICAL_WRITE_REQUESTS_DELTA" = '0' and "PHYSICAL_WRITE_BYTES_TOTAL" = '0' and "PHYSICAL_WRITE_BYTES_DELTA" = '0' and "OPTIMIZED_PHYSICAL_READS_TOTAL" = '0' and "OPTIMIZED_PHYSICAL_READS_DELTA" = '0' and "CELL_UNCOMPRESSED_BYTES_TOTAL" = '0' and "CELL_UNCOMPRESSED_BYTES_DELTA" = '0' and "IO_OFFLOAD_RETURN_BYTES_TOTAL" = '0' and "IO_OFFLOAD_RETURN_BYTES_DELTA" = '0' and ROWID = 'AAAVS9AACAAFMqZAAG';
   

复制代码

ry715

而且看了一下awr的报告很多类似的删除语句，比如

1. delete from WRH$_SQLSTAT tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and (tab.snap_id <= b.end_snap_id))

复制代码

，

1. delete from WRH$_ROWCACHE_SUMMARY tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and (tab.snap_id <= b.end_snap_id))

复制代码

真心不明白为什么会这样，不懂是什么原因触发的这些操作。

botang

ry715 发表于 2017-8-31 17:56
而且看了一下awr的报告很多类似的删除语句，比如，
真心不明白为什么会这样，不懂是什么原因触发的这些操 ...

这些语句都不是Top SQL，换句话说：都不是你要关心的。
它们都是 AWR 的自身维护语句。

WR_$
中间取值为R H M I
都是AWR的基表。

ry715

现在就是会生成特别多的归档日志，一天差不多有40多G，就去看了一下归档日志的内容，就是很多delete的语句，然后又跑个AWR的报告，那有什么方法可以知道产生如此之多归档的原因吗，有啥思路不，老师。

问了软件厂家，说最近啥都没干。

botang

ry715 发表于 2017-9-1 15:51
现在就是会生成特别多的归档日志，一天差不多有40多G，就去看了一下归档日志的内容，就是很多delete的语句 ...

一天差不多有40多G，就去看了一下归档日志的内容

怎么看的？

这个时候要看TOP SQL。

xiaoyu

归档特别多用以下语句查下，几点的归档切换量特别大，然后针对性的去抓AWR，然后贴出来看：

1. SELECT  A.THREAD#,  SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),1,5)  DAY,
   
2.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
   
3.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
   
4.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
   
5.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
   
6.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
   
7.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
   
8.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
   
9.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
   
10.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
    
11.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
    
12.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
    
13.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
    
14.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
    
15.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
    
16.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
    
17.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
    
18.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
    
19.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
    
20.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
    
21.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
    
22.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
    
23.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
    
24.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
    
25.        SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
    
26.        COUNT(*) TOTAL
    
27. FROM GV$LOG_HISTORY  A  
    
28. WHERE FIRST_TIME>=TO_CHAR(SYSDATE-30)
    
29.   GROUP BY A.THREAD#,      
    
30. SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),1,5)
    
31. ORDER BY A.THREAD#,SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),1,5) DESC;

复制代码

ry715

我又仔细查了一下，sysaux和system的表空间使用都在99.9%了，所以我觉得应该是这个原因造成的。

太郁闷了，出问题的时候，第一件事情就是查表空间，回看了聊天记录，里面显示就已经满了

唉，这年纪大了，眼睛也不好使了。

ry715

xiaoyu 发表于 2017-9-5 17:11
归档特别多用以下语句查下，几点的归档切换量特别大，然后针对性的去抓AWR，然后贴出来看：

谢谢，我目前分析应该是sysaux和system表空间满造成的，一直无法生成快照，然后就在不停的在自己清理。

botang

ry715 发表于 2017-9-7 10:58
谢谢，我目前分析应该是sysaux和system表空间满造成的，一直无法生成快照，然后就在不停的在自己清理。

课堂上，我提醒你的没错吧

ry715

botang 发表于 2017-9-7 15:54
课堂上，我提醒你的没错吧

我确实第一时间就去查空间了，可惜把已使用看成了未使用，一看，空间还有90%多，其实当时也没考虑到说咋可能空间剩余这么多。