Parâmetro optimizer_capture_sql_plan_baselines

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.
Post Reply
wendercruz

Parâmetro optimizer_capture_sql_plan_baselines

Post by wendercruz »

Boa Tarde Portilho.

Apenas gostaria de ver uma questão contigo referente ao parâmetro optimizer_capture_sql_plan_baselines, analisando algumas documentações da Oracle referente a este parâmetro, achei interessando o funcionamento deste recurso e o que ele pode oferecer ao DB, porém no forúm da Oracle encontrei alguns usuários com alguns problemas referente a este recurso, um deles seria o grande aumento na tablespace SYSAUX.

Gostaria de saber se você utiliza este recurso ou já utilizou ele e se caso já utilizou se este recurso funciona da forma que a Oracle reporta, mantendo o plano sempre atualizado com base nos SQL que se é feito ao segmento, fiquei meio na dúvida de habilitar este recurso, pois imagino que se fosse bom o mesmo teria seu valor Default como TRUE e não como FALSE.

Abraços.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by portilho »

Oi.

Eu gosto dessa funcionalidade e sempre a habilito. Ela estabiliza muito as ocorrências de alterações de plano.
Eu considero o aumento da SYSAUX um problema menor, e administrativo, não de desempenho.

Sobre o valor padrão do parâmetro, a Oracle sempre segue esta linha com parâmetros que alteram o comportamento do CBO. Em uma versão é um parâmetro oculto, em uma próxima versão é um parâmetro não oculto mas desabilitado, e em uma próxima versão passa a ser habilitado por padrão.

wendercruz

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by wendercruz »

Boa Tarde Portilho,
Habilitei este parâmetro no Banco de Dados de Homologação, e realmente o mesmo captura muitos planos. O DB realmente fica melhor em termos de desempenho, porém igual tinha reportado onde tinha visto em alguns fóruns o problema de aumento expressivo da Tablespace SYSAUX.
Bem realmente tive este mesmo problema, a Tablespace SYSAUX cresceu de uma forma que esta com 32 GB atualmente, e sinceramente não sei como fazer um purge ou até mesmo fazer um reorg nela, pois ao fazer o select na dba_segments, obtive o segmento lob pertencente a tabela SQLOBJ$DATA como a que ocupa maior espaço.

Deixei a configuração bem reduzida dos parâmetros:

SQL> select parameter_name, parameter_value from dba_sql_management_config;

PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 5
PLAN_RETENTION_WEEKS 5

---------------------------------------------------------------------------
SQL> column occupant_name format a24;
SQL> column schema_name format a15;
SQL> column move_procedure format a20;
SQL> SELECT occupant_name,round( space_usage_kbytes/1024) "Space (M)",schema_name,move_procedure
FROM v$sysaux_occupants
where occupant_name='SQL_MANAGEMENT_BASE';

OCCUPANT_NAME Space (M) SCHEMA_NAME MOVE_PROCEDURE
------------------------ ---------- --------------- --------------------
SQL_MANAGEMENT_BASE 30132 SYS

--------------------------------------------------------------------------
SQL> column owner format a10;
SQL> column segment_name format a30;
SQL> column tablespace_name format a15;
SQL> select owner,segment_name,segment_type,tablespace_name,round(bytes/1024/1024,2)
from dba_segments
2 3 where round(bytes/1024/1024,2) = (select max(round(bytes/1024/1024,2))
from dba_segments
4 5 where tablespace_name='SYSAUX'
6 );

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ROUND(BYTES/1024/1024,2)
---------- ------------------------------ ------------------ --------------- ------------------------
SYS SYS_LOB0000000208C00005$$ LOBSEGMENT SYSAUX 26123

---------------------------------------------------------------------------------------------
SQL> select owner,table_name,tablespace_name
from dba_lobs
where segment_name='SYS_LOB0000000208C00005$$';

OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ---------------
SYS SQLOBJ$DATA SYSAUX

Tem alguma ideia do que poderia ser feito neste caso, ou se já passou por este problema.
Abraços.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by portilho »

Oi !
Que versão exata você está utilizando?
Veja este Bug: "Bug 9910484 SQL Plan Management Capture uses excessive space in SYSAUX".

wendercruz

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by wendercruz »

Bom Dia,
Estou com a versão 11.2.0.4. Ontem assim que publiquei a mensagem encontrei no meta link da Oracle este Bug 9910484 : UNNECESSARY UPDATES ON SQLOBJ$DATA CAUSING OBJECT AND TABLESPACE GROWTH, e também a forma que a Oracle recomenda para contornar o aumento que teve na SYSAUX (Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (Doc ID 1499542.1)). Em uma analise deste Parâmetro Portilho, consegui identificar a seguintes situações, pois habilitei em dois DB de Homologação, em um estava sendo utilizado o Sistema, então as consultas estava ocorrendo normalmente, neste não houve aumento na SYSAUX, no outro DB estava fazendo uma carga de migração, replicando basicamente 8 milhoes de registros do SQL Server para o Oracle, este sim houve um aumento expressivo na SYSAUX.

Mesmo com o bug em um DB que não tem uma carga de dados alta, o mesmo funciona bem, pois ele respeita o purge, agora em um DB com muita carga isto é um problema. De qualquer forma Obrigado mais uma vez.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by portilho »

Muito obrigado por compatilhar o resultado!
Acho que este problema de grande carga refere-se a muitos SQLs diferentes, não é? Acho que só isto explicaria tantos planos de execução armazenados. Seria esta carga executada sem Binds?
Como o crescimento da SYSAUX é um problema administrativo e não de desempenho, ainda considero este parâmetro uma boa opção para este fim.

wendercruz

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by wendercruz »

Bom Dia Portilho,
Sim o aumento foi devido a grande carga e vários SQL Diferentes, não foi utilizado variáveis Bind. Levando em consideração o benefício que este parâmetro pode trazer ao DB, não acredito que seja um parâmetro a ser desconsiderado e sim um parâmetro a ser utilizado com cautela, em uma carga grande de Dados isto será realmente um problema. Irá exigir um monitoramento maior na v$sysaux_occupants e na dba_segments com ação de purge para evitar um grande consumo de espaço na Tablespace.

Acho esta informação muito valiosa para quem deseja utilizar este parâmetro, visto que seu blog, site e fórum são uma referência.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Parâmetro optimizer_capture_sql_plan_baselines

Post by portilho »

Certamente Wender, e obrigado por compartilhar os detalhes de seu ambiente. :-)

Post Reply