Como estimar o crescimento da SYSAUX
A tablespace SYSAUX é uma peça chave dentro da arquitetura do Oracle Database, sendo uma tablespace auxiliar à tablespace SYSTEM (daí seu nome, SYSAUX). Ela armazena informações como dados sobre o uso de features, dados de auditoria, advisors, históricos de estatísticas e AWR. Como temos um espaço finito para a tablespace, é fundamental que saibamos como estimar o crescimento da SYSAUX quando fazemos alterações no tempo de retenção de dados do AWR ou do histórico de estatísticas. Para que possamos ter uma estimativa do aumento do uso de espaço da SYSAUX, a Oracle disponibiliza o script utlsyxsz.sql
O utlsyxsz.sql
O script é fornecido juntamente com a instalação do banco de dados, dentro do diretório $ORACLE_HOME/rdbms/admin.
Ao rodar o script podemos fornecer informações sobre o AWR como:
- Tempo de retenção dos snapshots AWR;
- Intervalo entre os snapshots;
- Número médio de sessões ativas;
Com esses dados podemos verificar o crescimento estimado do armazenamento de dados do AWR na SYSAUX.
Também é possível verificar o impacto de alterações na retenção do histórico de estatísticas, fornecendo informações como:
- Número de tabelas no banco;
- Número de partições;
- Período de retenção;
- Nível de atividade de DML no banco;
Como estimar o crescimento da SYSAUX usando o utlsyxsz.sql
No cenário abaixo, rodei o script visando medir o impacto do aumento do tempo de retenção dos dados do AWR para 14 dias, e alteração do intervalo entre os snapshots para 15 minutos. Para calcular o número médio de sessões ativas, utilizei a consulta:
SELECT ROUND(AVG(ACTIVE_SESS), 2) AS avg_active_sessions
FROM (
SELECT SAMPLE_ID, COUNT(*) AS ACTIVE_SESS
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE CON_ID = 3 -- CON_ID do PDB desejado
AND SAMPLE_TIME > SYSDATE - 1 -- Último dia
GROUP BY SAMPLE_ID
);
Para os inputs referentes ao histórico de estatísticas, mantive os valores default, uma vez que não era minha intenção alterá-los.
Rodando o script:
SQL> @?/rdbms/admin/utlsyxsz.sql This script estimates the space required for the SYSAUX tablespace. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Specify the Report File Name ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is utlsyxsz.txt. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: /home/oracle/utlsyxsz.txt Using the report name /home/oracle/utlsyxsz.txt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SYSAUX Size Estimation Report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Estimated at 19:35:15 on Mar 10, 2025 ( Monday ) in Timezone -03:00 ~~~~~~~~~~~~~~~~~~~~ Current SYSAUX usage ~~~~~~~~~~~~~~~~~~~~ | Total SYSAUX size: 6,972.7 MB | | Total size of SM/AWR 0.7 MB ( 0.0% of SYSAUX ) | Total size of SM/OPTSTAT 91.7 MB ( 1.3% of SYSAUX ) | Total size of AUDSYS 6,411.8 MB ( 92.0% of SYSAUX ) | Total size of SDO 146.8 MB ( 2.1% of SYSAUX ) | Total size of SM/OTHER 75.6 MB ( 1.1% of SYSAUX ) | Total size of XDB 60.8 MB ( 0.9% of SYSAUX ) | Total size of AO 44.6 MB ( 0.6% of SYSAUX ) | Total size of SM/ADVISOR 27.9 MB ( 0.4% of SYSAUX ) | Total size of JOB_SCHEDULER 16.7 MB ( 0.2% of SYSAUX ) | Total size of WM 6.6 MB ( 0.1% of SYSAUX ) | Total size of SMON_SCN_TIME 3.4 MB ( 0.0% of SYSAUX ) | Total size of TEXT 2.8 MB ( 0.0% of SYSAUX ) | Total size of PL/SCOPE 1.5 MB ( 0.0% of SYSAUX ) | Total size of SQL_MANAGEMENT_BASE 1.1 MB ( 0.0% of SYSAUX ) | Total size of AUTO_TASK 0.6 MB ( 0.0% of SYSAUX ) | Total size of EM_MONITORING_USER 0.2 MB ( 0.0% of SYSAUX ) | Total size of LOGSTDBY 0.1 MB ( 0.0% of SYSAUX ) | Total size of STREAMS 0.1 MB ( 0.0% of SYSAUX ) | Total size of Others 79.8 MB ( 1.1% of SYSAUX ) | ~~~~~~~~~~~~~~~~~~~~ AWR Space Estimation ~~~~~~~~~~~~~~~~~~~~ | To estimate the size of the Automatic Workload Repository (AWR) | in SYSAUX, we need the following values: | | - Interval Setting (minutes) | - Retention Setting (days) | - Number of Instances | - Average Number of Active Sessions | - Number of Datafiles | | For 'Interval Setting', | Press <return> to use the current value: 57,816,001 minutes | otherwise enter an alternative | Enter value for interval: 15 # valor de intervalo entre snapshots que desejo configurar ** Value for 'Interval Setting': 15 | | For 'Retention Setting', | Press <return> to use the current value: 8.00 days | otherwise enter an alternative | Enter value for retention: 14 # valor de retenção que desejo configurar ** Value for 'Retention Setting': 14 | | For 'Number of Instances', | Press <return> to use the current value: 1.00 | otherwise enter an alternative | Enter value for num_instances: 1 # número de instâncias da base ** Value for 'Number of Instances': 1 | | For 'Average Number of Active Sessions', | Press <return> to use the current value: 48.0 | otherwise enter an alternative | Enter value for active_sessions: 3.4 # número médio de sessões ativas calculado pela consulta anterior ** Value for 'Average Number of Active Sessions': 3.4 | *************************************************** | Estimated size of AWR: 744.3 MB | | The AWR estimate was computed using | the following values: | | Interval - 15 minutes | Retention - 14.00 days | Num Instances - 1 | Active Sessions - 3.40 | Datafiles - 9 | *************************************************** ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Optimizer Stat History Space Estimation ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | To estimate the size of the Optimizer Statistics History | we need the following values: | | - Number of Tables in the Database | - Number of Partitions in the Database | - Statistics Retention Period (days) | - DML Activity in the Database (level) | | For 'Number of Tables', | Press <return> to use the current value: 178.0 | otherwise enter an alternative <a positive integer> | Enter value for number_of_tables: ** Value for 'Number of Tables': 178 | | For 'Number of Partitions', | Press <return> to use the current value: 104.0 | otherwise enter an alternative <a positive integer> | Enter value for number_of_partitions: ** Value for 'Number of Partitions': 104 | | For 'Statistics Retention', | Press <return> to use the current value: 31.0 days | otherwise enter an alternative <a positive integer> | Enter value for stats_retention: ** Value for 'Statistics Retention': 31 | | For 'DML Activity', | Press <return> to use the current value: 2 <medium> | otherwise enter an alternative <1=low, 2=medium, 3=high> | Enter value for dml_activity: ** Value for 'DML Activity': 2 | *************************************************** | Estimated size of Stats history 41.4 MB | | The space for Optimizer Statistics history was | estimated using the following values: | | Tables - 178 | Indexes - 278 | Columns - 1,359 | Partitions - 104 | Indexes on Partitions - 360 | Columns in Partitions - 4,791 | Stats Retention in Days - 31 | Level of DML Activity - Medium | *************************************************** ~~~~~~~~~~~~~~~~~~~~~~ Estimated SYSAUX usage ~~~~~~~~~~~~~~~~~~~~~~ | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Estimated size of AWR: 744.3 MB | | The AWR estimate was computed using | the following values: | | Interval - 15 minutes | Retention - 14.00 days | Num Instances - 1 | Active Sessions - 3.40 | Datafiles - 9 | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Estimated size of Stats history 41.4 MB | | The space for Optimizer Statistics history was | estimated using the following values: | | Tables - 178 | Indexes - 278 | Columns - 1,359 | Partitions - 104 | Indexes on Partitions - 360 | Columns in Partitions - 4,791 | Stats Retention in Days - 31 | Level of DML Activity - Medium | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | For all the other components, the estimate | is equal to the current space usage of | the component. | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | | *************************************************** | Summary of SYSAUX Space Estimation | *************************************************** | Est size of AUDSYS 6,411.8 MB | Est size of SDO 146.8 MB | Est size of SM/OTHER 75.6 MB | Est size of XDB 60.8 MB | Est size of AO 44.6 MB | Est size of SM/ADVISOR 27.9 MB | Est size of JOB_SCHEDULER 16.7 MB | Est size of WM 6.6 MB | Est size of SMON_SCN_TIME 3.4 MB | Est size of TEXT 2.8 MB | Est size of PL/SCOPE 1.5 MB | Est size of SQL_MANAGEMENT_BASE 1.1 MB | Est size of AUTO_TASK 0.6 MB | Est size of EM_MONITORING_USER 0.2 MB | Est size of LOGSTDBY 0.1 MB | Est size of STREAMS 0.1 MB | Est size of Others 79.8 MB | Est size of SM/AWR 744.3 MB | Est size of SM/OPTSTAT 41.4 MB | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Total Estimated SYSAUX size: 7,666.1 MB | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | *************************************************** End of Report
Nesse caso, o uso da SYSAUX passaria de 6972MB para 7666MB, não necessitando de um aumento do espaço para a SYSAUX, uma vez que um datafile com autoextend já estava disponível. Entretanto em ambientes com mais sessões ativas, ou com mais dados de outras fontes na SYSAUX, podemos ter que tomar alguma ação para impedir um erro por falta de espaço na tablespace.
Conclusão
Antes de efetuarmos qualquer alteração no banco de dados, ainda na etapa de planejamento, precisamos estar cientes dos possíveis impactos que podemos ter no ambiente, em decorrência da mudança. No caso de alterações na captura e retenção de snapshots AWR, ou retenção do histórico de estatísticas, o script utlsyxsz.sql é uma ótima ferramenta para visualizarmos como essas mudanças podem impactar no consumo de espaço da tablespace SYSAUX.
Publicar comentário