SYSAUX İçerisinden Advisor İstatistikleri Nasıl Temizlenir?

Selamlar, bir Oracle 19c ortamımızda sysaux tablespace’inin 65GB’a ulaştığını farkettik.

Sysaux tablespace’i içerisindeki objeleri kontrol ettiğimizde nerdeyse tamamı “SM/ADVISOR” olarak gözüküyor.

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 65,503.4 MB ( 100% of 65,536.0 MB MAX with AUTOEXTEND ON )
| 
| Schema SYS occupies 65,266.9 MB ( 99.6% )
| Schema MDSYS occupies 89.0 MB ( 0.1% )
| Schema AUDSYS occupies 76.3 MB ( 0.1% )
| Schema XDB occupies 60.6 MB ( 0.1% )
| Schema WMSYS occupies 6.6 MB ( 0.0% )
| Schema CTXSYS occupies 2.8 MB ( 0.0% )
| Schema GSMADMIN_INT occupies 0.9 MB ( 0.0% )
| Schema SYSTEM occupies 0.3 MB ( 0.0% )
| Schema DBSNMP occupies 0.2 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ---------------- 
| SM/ADVISOR SYS 64,437.1 MB
| SM/OPTSTAT SYS 278.4 MB
| SM/OTHER SYS 126.4 MB
| SDO MDSYS 89.0 MB

Tablespace boyutu bir hayli büyüdüğü için bu istatistikleri tek tek delete etmek çok ciddi anlamda redo ve undo kullanımına yol açacak. Bu nedenle truncate yöntemini tercih ediyorum.

Öncelikle WRI$_ADV_OBJECTS tablosundaki AUTO_STATS_ADVISOR_TASK’a ait olan satır sayısını alalım. Bizde yaklaşık 280milyon satır vardı.

SELECT COUNT(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

Geçici bir tablo create edip içerisine AUTO_STATS_ADVISOR_TASK’a ait olmayan satırları alalım.

CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

Kaç satır geldiğini kontrol edelim. Bizde yaklaşık 19bin satır vardı.

SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW;

WRI$_ADV_OBJECTS tablosunu truncate edelim. Delete komutu ile yapmadığımız için redo veya undo kullanmayacak.

TRUNCATE TABLE WRI$_ADV_OBJECTS;

Ana tablomuzu tekrar dolduralım.

INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" ) SELECT "ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,
"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" , "SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4" FROM WRI$_ADV_OBJECTS_NEW;

Tabloyu reorginize ediyorum ve index’leri rebuild edip işlemi tamamlıyorum.

SQL> alter session set container=<PDB_NAME>;
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER TABLE WRI$_ADV_OBJECTS MOVE');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD');
SQL> exec dbms_pdb.exec_as_oracle_script('ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD');

Eğer ilgili job’ı drop etmek isterseniz aşağıdaki komutu;

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

Drop ettikten sonra tekrar create etmek isterseniz de aşağıdaki komutu kullanabilirsiniz;

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

Umarım faydalı olmuştur. Oracle veritabanı hakkında verdiğimiz destek hizmetimizden yararlanmak için siz de Forenda ile iletişime geçebilirsiniz.

Kaynaklar;

SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)