Recently i came accross a problem where one delete statement as below was taking almost more than 10 hours :
DELETE /*+ dynamic_sampling(4) */ FROM sys.wri_optstat_histgrm_history WHERE savtime < :1 AND ROWNUM <= NVL (:2, ROWNUM)
It is actually purging the old statistics from sysaux table and pointing towoards a bug
For an workaround we use below, before applying any patch:
SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
Table altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging parallel 8;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild nologging parallel 8;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
DELETE /*+ dynamic_sampling(4) */ FROM sys.wri_optstat_histgrm_history WHERE savtime < :1 AND ROWNUM <= NVL (:2, ROWNUM)
It is actually purging the old statistics from sysaux table and pointing towoards a bug
For an workaround we use below, before applying any patch:
Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8]
SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
Table altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging parallel 8;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild nologging parallel 8;
Index altered.
SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
No comments:
Post a Comment