Índice do fórum Treinamentos Avançados Treinamento Oracle Performance Diagnostics & Tuning Coleta de estatisticas

Coleta de estatisticas

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

Mensagem Sex Ago 09, 2013 8:03 pm

Mensagens: 0
Percebi que fazendo uma coleta de estatistica com o analyze(opcao antiga) e com a dbms_stats.gather encontrei divergencias ao consultar a dba_tables(principalmente na coluna de blocos vazios) primeiro executei o dbms(auto) e tinha 2000mil blocos vazios, depois fiz o analyze(compute) e os blocos vazios cairam para 50 apenas... sei que existem diferenças tambem em relacao aos histogramas, saberiam mais informaçoes e quais diferenças entre os dois metodos ?

Obrigado

Mensagem Qui Ago 15, 2013 12:56 pm

Mensagens: 0
Estudando um pouco mais e fazendo alguns testes, de fato o analyze se mostrou mais eficaz para validação dos blocos vazios, sei la... mas qto mais a gente estuda mais duvidas e mais burro a gnt se sente... agora to confuso quanto ao METHOD_OPT percebi que ele é um parametro diretamente ligado aos histogramas ** já sei perfeitamente como funcionam histogramas etc **

...mas quais melhores formas de utilizar o METHOD_OPT ? e quais diferenças dessas formas(FOR ALL HIDDEN COLUMNS / FOR ALL INDEXED COLUMNS / FOR ALL COLUMNS SIZE AUTO / SKEWONLY etc, etc...) :?:

..não encontrei boas explicações quanto aos status FREQUENCY/HEIGHT BALANCED :shock:

Mensagem Seg Ago 19, 2013 5:41 pm
portilho Site Admin

Mensagens: 482
O ANALYZE não foi descontinuado, e tem sua utilidade.
Dois exemplos de sua utilidade, além de coletar estatísticas, são:
- Validar a integridade do objeto.
- Identificar facilmente Migrated e Chained Rows.

Mas as estatísticas coletadas por ele são limitadas aos seguintes itens:
- Number of rows (NUM_ROWS)
- Number of data blocks below the high water mark—the number of data blocks that have been formatted to receive data regardless whether they currently contain data or are empty (BLOCKS)
- Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS)
- Average available free space in each data block in bytes (AVG_SPACE)
- Number of chained rows (CHAIN_COUNT)
- Average row length, including the row overhead, in bytes (AVG_ROW_LEN)

O que eu acho que aconteceu no seu caso é que o GATHER_TABLE_STATS, já que foi utilizada a opção AUTO, não escolheu um percentual adequado da tabela, enquanto o ANAYLZE analisou ela toda (COMPUTE).

ANALYZE: http://docs.oracle.com/cd/E11882_01/ser ... SQLRF01105

Mensagem Seg Ago 19, 2013 5:52 pm
portilho Site Admin

Mensagens: 482
Como eu gosto de dizer, se tivesse uma manira melhor, não seria dada escolha a você. :-)

Você precisará de Histogramas dependendo da quantidade de dados raros (skewed data) que você tem, e qual sua raridade.

Por exemplo, em uma tabela com todos os brasileiros, na coluna de CPFs, todos dados são igualmente raros.
Na coluna de Estado, temos dados mais raros que outros (Acre e São Paulo, por exemplo).
Se os Histogramas, na coluna de Estado, se você coletasse apenas 1% dos dados (tavlez pela opção GATHER_AUTO), seria muito provável que só fossem coletados dados de pessoas de SP, RJ e MG. E SELECTs com WHERE nesse coluna iriam tender a utilizar Full Table Scan, quando para os habitantes do Acre, seria melhor tavlez um Fst Index Scan.

Eu gosto da explicação do Performance Tuning Guide mesmo, tem as duas figurinhas que deixa bem claro.
http://docs.oracle.com/cd/E11882_01/ser ... PFGRF94766

Mensagem Sex Ago 21, 2015 1:06 pm

Mensagens: 0
Afinal de contas, qual é job que roda coleta de estatísticas automaticamente? No Oracle 10g esse job era o GATHER_STATS_JOB. Já no 11g ele não existe mais, notei que foram criados três jobs que pelo nome são candidatos para substitutos do GATHER_STATS_JOB:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 21 09:25:56 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner, job_name, enabled from dba_scheduler_jobs order by 2;

OWNER JOB_NAME ENABL
------------------------------ ------------------------------ -----
SYS BSLN_MAINTAIN_STATS_JOB TRUE
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE
SYS FGR$AUTOPURGE_JOB FALSE
SYS FILE_WATCHER FALSE
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE
ORACLE_OCM MGMT_CONFIG_JOB TRUE
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE

SYS ORA$AUTOTASK_CLEAN TRUE
SYS PURGE_LOG TRUE
EXFSYS RLM$EVTCLEANUP TRUE
EXFSYS RLM$SCHDNEGACTION TRUE

OWNER JOB_NAME ENABL
------------------------------ ------------------------------ -----
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE
SYS XMLDB_NFS_CLEANUP_JOB FALSE

O conteúdo deles é um tanto curioso:


DBMS_METADATA.GET_DDL('PROCOBJ','MGMT_STATS_CONFIG_JOB','ORACLE_OCM')
--------------------------------------------------------------------------------


BEGIN
dbms_scheduler.create_job('"MGMT_STATS_CONFIG_JOB"',
job_type=>'STORED_PROCEDURE', job_action=>
'ORACLE_OCM.MGMT_CONFIG.collect_stats'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('15-AUG-2009 12.24.04.694342000 AM -07:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=monthly;interval=1;bymonthday=1;byhour=01;byminute=01;bysecond=01'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
'OCM Statistics collection job.'
);
dbms_scheduler.enable('"MGMT_STATS_CONFIG_JOB"');
COMMIT;
END;



SQL> /

OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------------------
MGMT_CONFIG_JOB ORACLE_OCM JOB
MGMT_STATS_CONFIG_JOB ORACLE_OCM JOB
MGMT_CONFIG ORACLE_OCM PACKAGE
MGMT_CONFIG_UTL ORACLE_OCM PACKAGE
MGMT_DB_LL_METRICS ORACLE_OCM PACKAGE
MGMT_CONFIG_UTL ORACLE_OCM PACKAGE BODY
MGMT_CONFIG ORACLE_OCM PACKAGE BODY
MGMT_DB_LL_METRICS ORACLE_OCM PACKAGE BODY



SQL> select dbms_metadata.get_ddl('PACKAGE','MGMT_CONFIG','ORACLE_OCM') "DDL" from dual;

DDL
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE "ORACLE_OCM"."MGMT_CONFIG" AS
/*
Submit a job to collect the configuration.
Basically, a job with what->printCollectConfigMetrics(<collection directory>
*/
procedure submit_job;

/*
Runs the configuration collection job now.
*/
procedure run_now;

/*
Stop the job.
*/
procedure stop_job;

/*
Print the job details.
*/
procedure print_job_details;

/*
Config collection job
*/
procedure collect_config;


/*
Statistics collection job
*/
procedure collect_stats;

END MGMT_CONFIG;
CREATE OR REPLACE PACKAGE BODY "ORACLE_OCM"."MGMT_CONFIG" AS

JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_CONFIG_JOB';
STATS_JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_STATS_CONFIG_JOB';

/*
Checks to see if the job already exists
*/
FUNCTION job_exists (job_name_in VARCHAR) RETURN BOOLEAN IS
l_job_cnt NUMBER;
BEGIN
select count(*) into l_job_cnt from
dba_scheduler_jobs WHERE job_name = job_name_in and owner ='ORACLE_OCM';
if l_job_cnt = 0
THEN
return FALSE;
ELSE
return TRUE;
END IF;
END job_exists;

/*
Submit a job to collect the configuration.
Basically, a job with what->collect_config
*/
procedure submit_job IS
BEGIN
IF not job_exists(JOB_NAME) THEN
sys.dbms_scheduler.create_job(
job_name => JOB_NAME,
job_type => 'STORED_PROCEDURE',
job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_config',
schedule_name=> 'SYS.MAINTENANCE_WINDOW_GROUP',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Configuration collection job.');
COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20000,'Cannot resubmit. A job '''|| JOB_NAME
|| '''already exists.');
END IF;
IF not job_exists(STATS_JOB_NAME) THEN
sys.dbms_scheduler.create_job(
job_name => STATS_JOB_NAME,
job_type => 'STORED_PROCEDURE',
job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_stats',
start_date=> SYSTIMESTAMP,
repeat_interval => 'freq=monthly;interval=1;bymonthday=1;byhour=01;bymin
ute=01;bysecond=01',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'OCM Statistics collection job.');
COMMIT;
ELSE
RAISE_APPLICATION_ERROR(-20001,'Cannot resubmit. A job '''|| STATS_JOB_NAM
E
|| '''already exists.');
END IF;
END submit_job;

/*
Submit a job to collect the configuration.
Basically, a job with what->collect_config_metrics(<collection directory>
*/
procedure submit_job_for_inst(inst_id IN BINARY_INTEGER, p_inst_num IN BINARY_IN
TEGER,
p_job_name IN VARCHAR2,
p_job_action IN VARCHAR2, p_job_action2 in VARCHAR
2) IS
l_job NUMBER;
l_par v$instance.PARALLEL%TYPE;
l_instNum v$instance.INSTANCE_NUMBER%TYPE;
BEGIN
BEGIN
IF not job_exists(p_job_name || '_' || inst_id) THEN
sys.dbms_scheduler.create_job(
job_name => p_job_name || '_' || inst_id,
job_type => 'PLSQL_BLOCK',
job_action => p_job_action ,
start_date => NULL,
repeat_interval => NULL,
enabled => FALSE,
auto_drop => TRUE,
comments => 'OCM collection job run for an instance.');
BEGIN
-- Use the instance_id attribute.
-- This may throw exception if not implemented in the version of
-- the database. We would be ignoring the exception it that case.
DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id'
,inst_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);
-- Run the job synchronously
-- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Don't raise an exception otherwise it fills the alert/trace
DBMS_OUTPUT.put_line('Do not raise an exception');
-- RAISE_APPLICATION_ERROR(-20000,'SQLERRM: ' || SQLERRM || ' SQLCODE: '|
| SQLCODE);
END;

-- create 2nd job if specified
IF p_job_action2 is NOT NULL THEN
select PARALLEL into l_par from v$instance;
IF l_par = 'YES' THEN
select instance_number into l_instNum from v$instance;
IF l_instNum <> p_inst_num THEN
BEGIN
IF not job_exists(p_job_name || '_2_' || inst_id) THEN
sys.dbms_scheduler.create_job(
job_name => p_job_name || '_2_' || inst_id,
job_type => 'PLSQL_BLOCK',
job_action => p_job_action2 ,
start_date => NULL,
repeat_interval => NULL,
enabled => FALSE,
auto_drop => TRUE,
comments => 'OCM 2nd job run for RAC instance.');
BEGIN
-- Use the instance_id attribute.
-- This may throw exception if not implemented in the version of
-- the database. We would be ignoring the exception it that case.
DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_2_' || inst_id,'instan
ce_id',inst_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
DBMS_SCHEDULER.ENABLE (p_job_name || '_2_' || inst_id);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Don't raise an exception otherwise it fills the alert/trace
DBMS_OUTPUT.put_line('Do not raise an exception');
END;
END IF;
END IF;
END IF;
END submit_job_for_inst ;

/*
Runs the configuration collection job now.
*/
procedure run_now IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(JOB_NAME);
DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);
COMMIT;
END run_now;

/*
Print the job details.
*/
procedure print_job_details IS
BEGIN
dbms_output.put_line('Configuration collection job name: ' || JOB_NAME);

dbms_output.put_line('Statistics collection job name: ' || STATS_JOB_NAM
E);
dbms_output.put_line('Job Schedule Name: SYS.MAINTENANCE_WINDOW_GROUP');

END print_job_details;

/*
Stop the job.
*/
procedure stop_job IS
BEGIN
DBMS_SCHEDULER.DROP_JOB (JOB_NAME);
DBMS_SCHEDULER.DROP_JOB (STATS_JOB_NAME);
COMMIT;
END stop_job;

/*
Config collection job
*/
procedure collect_config IS
CURSOR l_res_cur IS select inst_id,instance_number from gv$instance;
BEGIN
FOR inst_id_row in l_res_cur LOOP
submit_job_for_inst(inst_id_row.inst_id, inst_id_row.instance_number, JOB_NAME
,
'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.COLLECT_CONFIG_METRICS(''O
RACLE_OCM_CONFIG_DIR''); END;',
'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.WRITE_DB_CCR_FILE(''ORACLE
_OCM_CONFIG_DIR2'', TRUE); END;');
END LOOP;
END collect_config;

/*
Statistics collection job
*/
procedure collect_stats IS
CURSOR l_res_cur IS select inst_id, instance_number from gv$instance;
BEGIN
FOR inst_id_row in l_res_cur LOOP
submit_job_for_inst(inst_id_row.inst_id, inst_id_row.instance_number, STATS_JO
B_NAME,
'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.collect_stats_metrics(''ORA
CLE_OCM_CONFIG_DIR''); END;',
NULL);
END LOOP;
END collect_stats;

END MGMT_CONFIG;
/

Ele não demonstra ser um job de coleta de estatísticas em si, mas sim de informações dos jobs.
Pelo o que entendi o OCM (Oracle Configuration Manager) é responsável por enviar informações da sua base para a Oracle e muita gente desabilita isso por N motivos.

Segundo o Burleson esse jobs não fazem esse papel de coleta e ainda um deles ainda precisa da diagnostics package license.

Ele ainda diz que não temos um job que faça essa coleta, precisamos cria-lo manualmente...
Mas algumas tabelas criadas possuem estatisticas coletadas recente, mesmo sem ter feito nenhuma ação, simplesmente usei o dbca e crie a base no default. A dúvida que fica é, quem coletou essas estatísticas? Como foram configuradas? Como desabilito/habilito esse job para criar a minha própria rotina?

http://www.dba-oracle.com/t_gather_stats_job.htm

Mensagem Sáb Ago 22, 2015 9:54 am
portilho Site Admin

Mensagens: 482
Esta é a forma atual (acho que >= 11gR1) de visualizar as tarefas automáticas de manutenção:

SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;


Esta é a forma de desabilitar a coleta de estatísticas automática:

SQL> BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE ( 
    CLIENT_NAME  => 'AUTO OPTIMIZER STATS COLLECTION'
    ,OPERATION    => NULL
    ,WINDOW_NAME  => NULL
);
END;
/


Esta é a forma de habilitá-la novamente:
SQL> BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE ( 
    CLIENT_NAME  => 'AUTO OPTIMIZER STATS COLLECTION'
    ,OPERATION    => NULL
    ,WINDOW_NAME  => NULL
);
END;
/

Mensagem Sáb Ago 22, 2015 10:37 am

Mensagens: 0
Agora faz sentido!

Mas é possível ver o conteúdo das tasks? Achei várias views com DBA_AUTOTASK_*, mas não encontrei nenhuma delas com algum campo como o WHAT ou JOB_ACTION.

Mensagem Ter Ago 25, 2015 10:22 am
portilho Site Admin

Mensagens: 482
Também não encontrei onde fica o que exatamente é feito pela coleta automática.

As melhores referências que achei a respeito estão abaixo, e elas não tem informações precisas a respeito das opções de coleta:
https://oracle-base.com/articles/11g/au ... ment-11gr1
http://docs.oracle.com/cd/B28359_01/ser ... /tasks.htm
http://docs.oracle.com/cd/B28359_01/app ... askadm.htm

Mas eu geralmente não me importo com a coleta automática. Eu a desabilito e configuro a minha via crontab / Task Scheduler.

Mensagem Ter Ago 25, 2015 10:35 am

Mensagens: 0
Sim, eu também não gosto dessa forma, gosto de montar a coleta do meu jeito.
Obrigado pelas referências!

Mensagem Ter Ago 25, 2015 10:36 am
portilho Site Admin

Mensagens: 482
Ok! :-)

Próximo

Voltar para Treinamento Oracle Performance Diagnostics & Tuning

cron