Olá pessoal,

 

Há um cliente com um banco de BI do SAP, chamado de BW (Business Warehouse), que estava com a coleta de estatística de dicionário de dados e tabelas fixas bastante desatualizado.

 

Ao questionar o time de DBAs veio a resposta: demora muito tempo para coletarmos, acabamos não fazendo.

 

De fato, fiz um teste e deixei a coleta sendo realizada, o DBMS_STATS.GATHER_DICTIONARY_STATS com DEGREE=>12 levou mais de 13 horas para ser concluído.

 

Deste período, 80% do tempo foi gasto em cima da tabela WRH$_ACTIVE_SESSION_HISTORY, que é a tabela que armazena os dados de ASH desse ambiente.

 

Bem, isso pode acontecer em bases de dados com muitos objetos, muitas sessões ativas (e em espera também), além de bases muito grandes.

 

Vamos checar a configuração do AWR:

 

SQL> @awr/config




Minute                        Minute               Days
Snapshot Interval Snapshot Retention Snapshot Retention
----------------- ------------------ ------------------
               60             259200                180
Caso precisem, a query que traz o resultado acima é essa:

 

COLUMN awr_env_interval_minutes HEADING "Minute|Snapshot Interval"

COLUMN awr_env_retention_minutes HEADING "Minute|Snapshot Retention"

COLUMN awr_env_retention_days HEADING "Days|Snapshot Retention"







SELECT

EXTRACT( day FROM snap_interval) *24*60+

EXTRACT( hour FROM snap_interval) *60+

EXTRACT( minute FROM snap_interval ) awr_env_interval_minutes,

EXTRACT( day FROM retention) *24*60+

EXTRACT( hour FROM retention) *60+

EXTRACT( minute FROM retention ) awr_env_retention_minutes,

((

EXTRACT( day FROM retention) *24*60+

EXTRACT( hour FROM retention) *60+

EXTRACT( minute FROM retention )

)/60/24)

awr_env_retention_days

from dba_hist_wr_control

where dbid = (select dbid from v$database);

 

OK, temos o intervalo de 60 minutos (default), mas temos 180 dias (6 meses) de retenção. O cliente informou que isso é necessário pois precisam comparar dados de performance de fechamentos mensais.

 

Bem, esse é um problema conhecido, para algumas tabelas podemos realizar o lock das estatísticas, isso significa que na próxima operação de DBMS_STATS.GATHER_DICTIONARY_STATS, essas tabelas não mais terão suas estatísticas coletadas.

 

Abaixo está o comando para realizar o lock das estatísticas das tabelas que impactam negativamente o tempo de coleta das estatísticas de dicionário:

 

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');

exec dbms_stats.lock_table_stats('SYS', 'WRH$_ACTIVE_SESSION_HISTORY');

 

Após isso, o tempo foi de 13 horas para 23 minutos.

 

As seguintes notas descrevem o mesmo:

 

Gathering Dictionary Statistics Runs Very Long Time (Doc ID 2364599.1)
DBMS_STATS.GATHER_DICTIONARY_STATS is Stuck on “SYS”.”WRH$_ACTIVE_SESSION_HISTORY” and is Taking a Very Long Time (Doc ID 2046826.1)

 

Espero que seja útil!

 

Um abraço,

 

Vinicius