oracle admin sysaux - ghdrako/doc_snipets GitHub Wiki

Oracle Database 10g Oracle introduced the SYSAUX tablespace.

Size prediction for the SYSAUX tablespace - utlsyxsz.sql

The script $ORACLE_HOME/rdbms/admin/utlsyxsz.sql can help to estimate the required size of the SYSAUX tablespace

  • You have to provide some information like snapshot interval, retention etc.
  • Interval Setting (minutes)
  • AWR Retention Setting (days)
  • Number of Instances
  • Average Number of Active Sessions
  • Number of tables in the database
  • Number of partitions
  • Number of Datafiles
  • Statistics Retention
  • DML activity (low/medium/high)

Cleanup SYSAUX

Sprawdzenie czy SYSAUX może się jeszcze rozrastać, czy już doszło do limitu

SET LINESIZE 150
COL FILE_NAME FORMAT A35
SELECT file_name, autoextensible AUTO,
       (increment_by * (SELECT VALUE
                        FROM v$parameter
                        WHERE UPPER (NAME) = 'DB_BLOCK_SIZE')) / 1024 / 1024 przyrost_mb, -- jaki jest przyrost rozmiaru przy AUTOEXTEND
       ROUND (BYTES / 1024 / 1024 / 1024) gb,                    -- aktualny rozmiar
       ROUND (maxbytes / 1024 / 1024 / 1024) maxgb,              -- maksymalny rozmiar jaki plik moze osiagnac
       ROUND ((maxbytes - BYTES) / 1024 / 1024 / 1024) wolne_gb  -- Ile jeszcze plik może urosnąć w GB
  FROM dba_data_files
 WHERE tablespace_name = 'SYSAUX';

Sprawdź co zajmuje miejsce w sysaux

SET LINES 120
COL OCCUPANT_NAME FORMAT A30
SELECT occupant_name,
       --occupant_desc, 
       space_usage_kbytes/1024 AS MB_USED
FROM v$sysaux_occupants
where SPACE_USAGE_KBYTES>0
ORDER BY MB_USED DESC
fetch first 10 rows only;


select distinct owner from
dba_segments where
tablespace_name='SYSAUX'
order by owner;

Przykladowy output:

SQL> select OCCUPANT_NAME,OCCUPANT_DESC, SPACE_USAGE_KBYTES USED_KB
2 from V$SYSAUX_OCCUPANTS where SPACE_USAGE_KBYTES>0
3 order by SPACE_USAGE_KBYTES desc
4 fetch first 10 rows only;
OCCUPANT_NAME OCCUPANT_DESC USED_KB
--------------- ------------------------------------------------------- ---------
SM/AWR Server Manageability - Automatic Workload Repository 137024
SDO Oracle Spatial 132096
XDB XDB 104448
AO Analytical Workspace Object Table 49536
AUDSYS AUDSYS schema objects 47680
SM/OPTSTAT Server Manageability - Optimizer Statistics History 39680
SM/OTHER Server Manageability - Other Components 33088
SM/ADVISOR Server Manageability - Advisor Framework 13440
LOGMNR LogMiner 11264
WM Workspace Manager 7488

TOP Occupants – the usual suspects:

  • SM/OPTSTAT- old optimizer statistics. Dane z SQL Tuning Advisor, Segment Advisor itd.
  • SM/ADVISOR – the various advisors. Historyczne dane statystyk zbieranych przez DBMS_STATS
  • SM/AWR – AWR data
  • SM/OTHER - DB Feature Usage, Alert History etc.
  • SQL_MANAGEMENT_BASE – SQL baselines
  • AUDSYS – audit data
Detailed analysis of AWR data in SYSAUX

The script $ORACLE_HOME/rdbms/admin/awrinfo.sql provides more information about the AWR components in SYSAUX

SM/AWR
SELECT retention FROM dba_hist_wr_control;                                 -- jaka biezaca retencja
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*1440); -- zmiana retencji na 8 dni

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
Czyszczenie SM/ADVISOR (Segment Advisor, SQL Advisor)
  • By default, old data will be purged automatically after 30 days
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER
 (task_name=> 'AUTO_STATS_ADVISOR_TASK',
 parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);

Do sprawdzenia co to robi ????

BEGIN
  FOR r IN (SELECT task_name FROM dba_advisor_tasks WHERE owner = 'SYS')
  LOOP
    DBMS_ADVISOR.DELETE_TASK(task_name => r.task_name);
  END LOOP;
END;
/
usunąć stare dane SM/OPTSTAT (historia statystyk)
EXEC DBMS_STATS.PURGE_STATS(SYSDATE - 7);  --  Usunąć dane starsze niż 7 dni
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); --  Zmienić czas trzymania historii - Default retention period is 31 days

WRI$ is optimizer history (OPTSTAT) that keeps old statistics info.

--How long old stats are kept
select dbms_stats.get_stats_history_retention from dual;

--Set retention of old stats to 10 days
exec dbms_stats.alter_stats_history_retention(10);

--Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

--Show available stats that have not been purged
select dbms_stats.get_stats_history_availability from dual;

--Reorg tables to reduce segment sizes and release space
alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;

-- the HIST*HISTORY tables may be partitioned. eg:
select partition_name from dba_tab_partitions where table_Name = 'WRI$_OPTSTAT_HISTGRM_HISTORY';
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move partition p_permanent;
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild;

--get DDL to rebuild any unusable indexes
select 'alter index '||segment_name||'  rebuild;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'