Iniciando na versão 11.1, quando se coleta as estatísticas, agora existe a opção de publicar imediatamente a nova estatística ao concluir a coleta(opção default) ou manter as novas estatísticas como pendentes, desta forma as novas estatísticas podem ser avaliadas (satisfatórias ou não) antes de serem publicadas para todo o database.
Para verificar se as estatisticas são publicadas imediatamente apos uma coleta utilizamos o pacote DBMS_STATS verificando o retorno do parâmetro PUBLISH
SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
------------------------------
TRUE
A query vai retornar TRUE ou FALSE, sendo que TRUE indica que as novas estatísticas são automaticamente publicadas quando coletadas enquanto FALSE indica que são mantidas como pendentes.
Para alterar o valor do parâmetro utilize o seguinte comando, passando TRUE ou FALSE conforme necessidade:
exec dbms_stats.set_global_prefs('PUBLISH','<TRUE | FALSE>');
As estatísticas pendentes também podem ser empregadas a nível de SCHEMA ou TABELA.
select dbms_stats.get_prefs('PUBLISH','<SCHEMA>') publish from dual;
select dbms_stats.get_prefs('PUBLISH','<SCHEMA>','<TABLE_NAME>') publish from dual;
exec dbms_stats.set_schema_prefs('<SCHEMA >','PUBLISH','<TRUE | FALSE>');
exec dbms_stats.set_table_prefs('<SCHEMA>','<TABLE_NAME>','PUBLISH','<TRUE | FALSE>');
As estatísticas pendentes são armazenadas no dicionario de dados do Oracle e podem ser visualizadas através das visões *_TAB_PENDING_STATS e *_IND_PENDING_STATS [DBA_ | ALL_ | USER_]
Para se utilizar uma estatística pendente (para analisar ganhos em performance, por exemplo) devemos ajustar o parâmetro OPTIMIZER_USE_PENDING_STATISTICS para TRUE (valor default FALSE) a nível de sessão.
Identificado melhoria ou piora na nova estatística podemos torná-la publica ou eliminá-la conforme abaixo:
--Publica todas as estatisticas pendentes exec dbms_stats.publish_pending_stats(null, null);
--Publica todas as estatisticas pendendes do objeto
exec dbms_stats.publish_pending_stats('<SCHEMA>','<TABLE_NAME>');
--Deleta as estatisticas pendentes do objeto
exec dbms_stats.delete_pending_stats('<SCHEMA>','<TABLE_NAME>');
Exemplo prático:
SQL> create table exemplo
2 (x number);
Table created.
SQL> insert into exemplo select 1 from dual connect by level <=10000;
10000 rows created.
SQL> insert into exemplo values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> create index exemplo_idx on exemplo(x);
Index created.
SQL> execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANDERSON',TABNAME =>'EXEMPLO',estimate_percent => dbms_stats.auto_sample_size ,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => 1 ,granularity => 'ALL', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> select x, count(1) from exemplo group by x order by 2;
X COUNT(1)
---------- ----------
2 1
1 10000
SQL> explain plan for
2 select * from exemplo where x=2;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3328780064
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 15003 | 8 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| EXEMPLO_IDX | 5001 | 15003 | 8 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
1 - filter("X"=2)
13 rows selected.
SQL> col PUBLISH for a20
SQL> select dbms_stats.get_prefs('PUBLISH','ANDERSON','EXEMPLO') publish from dual;
PUBLISH
--------------------
TRUE
SQL> exec dbms_stats.set_table_prefs('ANDERSON','EXEMPLO','PUBLISH','FALSE');
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_prefs('PUBLISH','ANDERSON','EXEMPLO') publish from dual;
PUBLISH
--------------------
FALSE
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select last_analyzed from dba_tables where table_name='EXEMPLO';
LAST_ANALYZED
-------------------
19/03/2015 10:33:14
SQL> select last_analyzed from dba_tab_pending_stats where table_name='EXEMPLO';
no rows selected
SQL> execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANDERSON',TABNAME =>'EXEMPLO',estimate_percent => dbms_stats.auto_sample_size ,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 1 ,granularity => 'ALL', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> select last_analyzed from dba_tables where table_name='EXEMPLO';
LAST_ANALYZED
-------------------
19/03/2015 10:33:14
SQL> select last_analyzed from dba_tab_pending_stats where table_name='EXEMPLO';
LAST_ANALYZED
-------------------
19/03/2015 10:51:06
SQL> explain plan for
2 select * from exemplo where x=2;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3328780064
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 15003 | 8 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| EXEMPLO_IDX | 5001 | 15003 | 8 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("X"=2)
13 rows selected.
SQL> alter session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
Session altered.
SQL> explain plan for
2 select * from exemplo where x=2;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4245883405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EXEMPLO_IDX | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("X"=2)
13 rows selected.
SQL> select last_analyzed from dba_tables where table_name='EXEMPLO';
LAST_ANALYZED
-------------------
19/03/2015 10:33:14
SQL> exec dbms_stats.publish_pending_stats('ANDERSON','EXEMPLO');
PL/SQL procedure successfully completed.
SQL> select last_analyzed from dba_tables where table_name='EXEMPLO';
LAST_ANALYZED
-------------------
19/03/2015 10:51:06
SQL> conn anderson
Enter password:
Connected.
SQL> show parameter OPTIMIZER_USE_PENDING_STATISTICS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
SQL> explain plan for
2 select * from exemplo where x=2;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4245883405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EXEMPLO_IDX | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("X"=2)
13 rows selected.
Autor: Anderson Graf
Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI
Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata
Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br
