terça-feira, 6 de dezembro de 2016

Utilizando o SQL Tuning Advisor (DBMS_SQLTUNE)

Fala pessoal...

Hoje fazendo uns acertos aqui no meu ambiente, precisei utilizar o SQL Tuning Advisor.

Ele é bem simples, mas é sempre bom lembrar que precisamos ter o "Diagnostic+Tunig" devidamente licenciado e habilitado.

Sendo assim, abaixo segue um exemplo da utilização.


-- Executar o ADDM. 

@?/rdbms/admin/addmrpt.sql

Na verdade baseado no ADDM identifiquei as necessidades iniciais tendo em vista que é um ambiente que não administro a muito tempo. Sendo assim, não é obrigatório executar esse relatório.


-- Create task
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
         begin_snap  => 34932,           -- Snap inicial
        end_snap    => 35220,           -- Snap final
        sql_id      => 'cyb0csh3g3vxa',   -- SQL Id
        scope       => DBMS_SQLTUNE.scope_comprehensive,
        time_limit  => 3600,            -- Execução em seg
        task_name   => 'cyb0csh3g3vxa_AWR2_tuning_task', 
        description => 'Task: cyb0csh3g3vxa');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

No exemplo acima utilizei uma período do meu AWR para limitar a minha task. Se não quiser utilizar os snaps, basta excluir essas duas linhas.

-- Execute task
   EXEC DBMS_SQLTUNE.execute_tuning_task(
           task_name => 'cyb0csh3g3vxa_AWR2_tuning_task');


-- Verify status
   SELECT 
task_id, 
task_name, 
status
   FROM 
dba_advisor_log
   WHERE 
task_name like '%cyb0csh3g3vxa%'
   order by 
task_id;

  TASK_ID  TASK_NAME                      STATUS
---------- ------------------------------ -----------
    154921 cyb0csh3g3vxa_AWR2_tuning_task COMPLETED

-- Verify recomendations 
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
   SELECT 
      DBMS_SQLTUNE.report_tuning_task(
      'cyb0csh3g3vxa_AWR2_tuning_task'
      ) AS recommendations 
   FROM 
      dual;

Nesse momento será apresentado uma relatório com as recomendações de melhorias.

Example:

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : cyb0csh3g3vxa_AWR2_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1200
Completion Status  : COMPLETED
Started at         : 12/06/2016 06:24:20
Completed at       : 12/06/2016 06:24:22

-------------------------------------------------------------------------------
Schema Name: PRD1_GC_APP
SQL ID     : cyb0csh3g3vxa
SQL Text   : select tbatchjobs0_.SUB_ID as SUB1_34_, tbatchjobs0_.RUN_ID as
             RUN2_34_, tbatchjobs0_.PROCESS_TYPE as PROCESS3_34_,
             tbatchjobs0_.PIECE_ID as PIECE4_34_, tbatchjobs0_.CREATE_TIME as
             CREATE5_34_, tbatchjobs0_.START_TIME as START6_34_,
             tbatchjobs0_.FINISH_TIME as FINISH7_34_, tbatchjobs0_.AGENT_ID
             as AGENT8_34_, tbatchjobs0_.THREAD_ID as THREAD9_34_,
             tbatchjobs0_.STATUS as STATUS34_, tbatchjobs0_.RESULT_INFO as
             RESULT11_34_ from T_BATCH_JOB_SUB tbatchjobs0_ where
             tbatchjobs0_.AGENT_ID=1 and tbatchjobs0_.STATUS<>107 and
             tbatchjobs0_.STATUS<>105 and tbatchjobs0_.STATUS<>223 and
             tbatchjobs0_.STATUS<>106 order by tbatchjobs0_.SUB_ID

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Predicate "TBATCHJOBS0_"."STATUS"<>107 used at line ID 2 of the execution
  plan is an inequality condition on indexed column "STATUS". This inequality
  condition prevents the optimizer from selecting indices  on table
  "PRD1_GC"."T_BATCH_JOB_SUB".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices.

2- Statistics Finding
---------------------
  Optimizer statistics for table "PRD1_GS"."T_CTS_BCP_GL_FEE" and its indices
  are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(
        ownname => 'PRD1_GS', 
        tabname => 'T_CTS_BCP_GL_FEE', 
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
        method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 48.56%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(
task_name => 'cyb0csh3g3vxa_AWR2_tuning_task', 
task_owner => 'SYS', 
replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 4019928224

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  7983 |   350K|  2063   (3)| 00:00:21 |
|   1 |  SORT ORDER BY     |                 |  7983 |   350K|  2063   (3)| 00:00:21 |
|*  2 |   TABLE ACCESS FULL| T_BATCH_JOB_SUB |  7983 |   350K|  2061   (3)| 00:00:21 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TBATCHJOBS0_"."STATUS"<>107 AND "TBATCHJOBS0_"."STATUS"<>105
              AND "TBATCHJOBS0_"."STATUS"<>106 AND "TBATCHJOBS0_"."AGENT_ID"=1 AND
              "TBATCHJOBS0_"."STATUS"<>223)
-------------------------------------------------------------------------------

É isso ai pessoal, bem simples e objetivo. 

Agora é só analisar essas sugestões e lembrar-se que o advisor fornece sugestões de melhorias e não uma receita de bolo pronta e infalível. 

Portanto, o seu resultado e sua utilização devem ser analisados e testados com muito cuidado.

Abraço
Mario

sábado, 5 de novembro de 2016

sexta-feira, 4 de novembro de 2016

Replicação de dados - GoldenGate

Olá pessoal, como  estão?

Espero que todos estejam muito bem.

Eu já publiquei uma série de artigos de GoldenGate a algum tempo atrás - Parte 1Parte 2Parte 3 e Parte 4.

Só que lá, confesso, o texto ficou um pouco grande e também muito pulverizado. 

Outro ponto é que a instalação era feita via linha de comando, enfim, chegou o momento de fazer algo mais direto.

Não vou detalhar comando a comando, acho desnecessário. 

Mas os nomes e processos são bem intuitivos, então basta dar uma olhada nos manuais aqui que você será feliz, tenho certeza.

Vamos começar.

====================================================================
-- Versões e downloads
====================================================================
- Sistema Operacional: Oracle Linux 6.5
- Bases de dados: GGPRD e GGCOPY
- Versão do Oracle: 11.2.0.4
- Versão do GG: 12.2.0.1.1
- Usuário do GG: dbagate           Senha: aaa123
- Usuário a ser replicado: SCOTT   Senha: aaa123
- Download do GG: Aqui

Nosso instalador, ficará nesse local: /home/oracle/GoldenGate

====================================================================
-- Instalação do GG (SOURCE e TARGET)
====================================================================
1) Criar o GG_HOME.

    mkdir -p /oracle/app/oracle/product/11.2.0/gghome

2) Descompactar o instalador e iniciar a instalação

    cd /home/oracle/GoldenGate 
    unzip fbo_ggs_Linux_x64_shiphome.zip

    cd /home/oracle/GoldenGate/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller

Abaixo a sequência de telas da instalação:






3) Adicionar o GG_HOME ao bash_profile

    cd ~
    vi .bash_profile
    GG_HOME=/oracle/app/oracle/product/11.2.0/gghome 

4) Agora é só testar a instalação
     
    cd $GG_HOME
    ./ggsci

   Oracle GoldenGate Command Interpreter for Oracle
   Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
   Operating system character set identified as UTF-8.
   Copyright (C) 1995, 2015, Oracle and/or its affiliates. All 
   rights reserved.

   GGSCI (rac12c1) 2> info all
   Program    Status      Group       Lag at Chkpt  Time Since Chkpt
   MANAGER    RUNNING
     
5) Caso seja necessário, alterar a porta do manager

    GGSCI (rac12c1) 3> stop manager
    edit params mgr
PORT 7889
    start manager

====================================================================
-- Preparação das bases (SOURCE e TARGET)
====================================================================

1) Pré-requisitos que devem ser configurados

   ALTER DATABASE FORCE LOGGING;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=SPFILE;
   ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;
   ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=SPFILE;
   SHUTDOWN IMMEDIATE;
   STARTUP;

   SELECT SUPPLEMENTAL_LOG_DATA_MIN,LOG_MODE FROM V$DATABASE;

Importante: as bases devem estar em modo Archive.

2) Criar user, tablespace para o GG e liberar os grant's

-- GGPRD CREATE TABLESPACE GGATE datafile '/u01/oradata/ggprd/ggate01.dbf' SIZE 1G AUTOEXTEND on NEXT 512M maxsize 8G; -- GGCOPY CREATE TABLESPACE GGATE datafile '/u01/oradata/ggcopy/ggate01.dbf' SIZE 1G AUTOEXTEND on NEXT 512M maxsize 8G; CREATE USER DBAGATE IDENTIFIED BY aaa123 DEFAULT TABLESPACE GGATE TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO DBAGATE; GRANT EXECUTE ON UTL_FILE TO DBAGATE; GRANT DBA TO dbagate; GRANT SELECT ANY DICTIONARY TO dbagate; GRANT EXECUTE ON dbms_flashback TO dbagate; 

3) Criar a estrutura interna do GG. Para isso, devemos estar no diretório do HOME_GATE

    cd $GGHOME

    @marker_setup.sql
Enter Oracle GoldenGate schema name: dbagate

    @ddl_setup.sql
Enter Oracle GoldenGate schema name: dbagate

    @role_setup.sql
Enter GoldenGate schema name: dbagate

Liberar o GRANT para o user do GG
GRANT GGS_GGSUSER_ROLE TO dbagate

    @ddl_enable.sql

4) Criar os diretórios

    ./ggsci

    GGSCI (rac12c1) 4> create subdirs

5) Preparando a tabela de checkpoint e registrando as informações do schema a ser replicado

    GGSCI (rac12c1) 5> EDIT PARAMS ./GLOBALS
    GGSCI (rac12c1) 6> GGSCHEMA DBAGATE
    GGSCI (rac12c1) 7> CHECKPOINTTABLE DBAGATE.checkpoint

    -- Precisamos estar logados com o dbagate nesse ponto
    GGSCI (rac12c1) 8> DBLOGIN USERID dbagate, PASSWORD aaa123

    GGSCI (rac12c1) 9>  ADD CHECKPOINTTABLE dbagate.checkpoint

    GGSCI (rac12c1) 10> ADD SCHEMATRANDATA scott ALLCOLS

============================================================
Somente no source
=============================================================

1) Configurar o extract. Ele é responsável pela extração dos dados que serão replicados e vai gerar os trailfiles localmente.

    GGSCI (rac12c1) 1> dblogin userid dbagate, password aaa123
    
    GGSCI (rac12c1) 2> add extract extract1, tranlog, begin now

    GGSCI (rac12c1) 3> 
    add exttrail /oracle/app/oracle/product/11.2.0/gghome/dirdat/lt, extract extract1, megabytes 50

    GGSCI (rac12c1) 4> 
    add rmttrail /oracle/app/oracle/product/11.2.0/gghome/dirdat/rt, extract extract1, megabytes 50
    
    GGSCI (rac12c1) 5> edit params extract1
    extract extract1
    userid dbagate@GGPRD, password aaa123
    EXTTRAIL /oracle/app/oracle/product/11.2.0/gghome/dirdat/lt
    TRANLOGOPTIONS EXCLUDEUSER DBAGATE
    GETTRUNCATES
    ddl include mapped objname scott.*;
    table SCOTT.*;


    GGSCI (rac12c1) 6> info extract extract1, detail

2) Configurar o datapump. Ele é responsável pela envio dos trailfiles para o servidor remoto.

    GGSCI (rac12c1) 7> ADD EXTRACT dtpump1, EXTTRAILSOURCE /oracle/app/oracle/product/11.2.0/gghome/dirdat/lt, BEGIN now

    GGSCI (rac12c1) 8> 
    ADD RMTTRAIL /oracle/app/oracle/product/11.2.0/gghome/dirdat/rt, EXTRACT dtpump1, megabytes 50

    GGSCI (rac12c1) 9> edit params dtpump1
    EXTRACT dtpump1
    RMTHOST 192.168.1.32, mgrport 7809
    RMTTRAIL /oracle/app/oracle/product/11.2.0/gghome/dirdat/rt
    PASSTHRU
    GETTRUNCATES
    TABLE SCOTT.*;


    GGSCI (rac12c1) 10> info all

============================================================
Somente no target
=============================================================
1) Adicionar o replicat

    ./ggsci
    
    GGSCI (rac12c2) 1> dblogin userid dbagate; password aaa123

    GGSCI (rac12c2) 2> add replicat replic1, exttrail /oracle/app/oracle/product/11.2.0/gghome/dirdat/rt, begin now, CHECKPOINTTABLE dbagate.checkpoint, 

    GGSCI (rac12c2) 3> edit params replic1
    REPLICAT replic1
    ASSUMETARGETDEFS
    GETTRUNCATES
    HANDLECOLLISIONS
    USERID dbagate, PASSWORD aaa123
    discardfile /oracle/app/oracle/product/11.2.0/gghome/dircrd/replic1_discard.txt, append, megabytes 50
    MAP SCOTT.*, TARGET SCOTT.*;
    DDL INCLUDE ALL
    DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 10 RETRYDELAY 30 


    GGSCI (rac12c2) 4> info all

============================================================
Iniciar os serviços do SOURCE
=============================================================

    ./ggsci

    GGSCI (rac12c1) 1> dblogin userid dbagate; password aaa123




    GGSCI (rac12c1) 2> start er *

    GGSCI (rac12c1) 3> info all

    GGSCI (rac12c1) 4> info extract extract1, detail

    GGSCI (rac12c1) 5> info extract dtpump1, detail


    GGSCI (rac12c1) 6> stats extract extract1

============================================================
Migrar o schema do source para o target - INITIAL LOAD
=============================================================

Nesse ponto iremos executar a carga inicial (INITIAL LOAD) no target. 

Essa etapa pode ser feita de várias formas. Poderíamos somente criar a estrutura de tabelas no target e fazer um INITIAL LOAD via GoldenGate mesmo. Ou fazer via EXPDP/IMPDP.

Eu prefiro sempre fazer via EXPDP/IMPDP. Então é essa forma que vamos utilizar.

1) Identificando o scn

    SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

    No nosso exemplo, o SCN sera esse: 970349

2) Executar o EXPDP com FLASHBACK_SCN no source

    expdp \"/ as sysdba\" directory=EXPDP_DIR DUMPFILE=SCOTT_%U.dmp LOGFILE=scott.log owner=scott filesize=8192m  FLASHBACK_SCN=970349



3) Realizar o IMPDP no target
    impdp \"/ as sysdba\" directory=EXPDP_DIR dumpfile=SCOTT_1.dmp logfile=impdp.log SCHEMAS='SCOTT'

============================================================
Somente no target
=============================================================
1) Iniciar o replicat. Esse processo é o responsável por ler e aplicar os trailfiles enviados pelo processo datapump do source.

    ./ggsci

    GGSCI (rac12c2) 1> dblogin userid dbagate; password aaa123

    GGSCI (rac12c2) 2> start er *

    Importante:
    Caso queira iniciar a partir do SCN (eu acho mais seguro)
    GGSCI (rac12c2) 2> start replicat replic1, ATCSN 970349

    GGSCI (rac12c2) 3> info all

    GGSCI (rac12c2) 4> info replicat replic1, detail


    GGSCI (rac12c2) 5> stats REPLICAT replic1, total, hourly, reportrate min    

Agora meus queridos, basta executar os testes na origem e ver a mágica acontecer no destino.

Lembre-se que esse é um cenário simples, porém um cenário maior acaba seguindo os mesmos passos. Consulte o manual, são opções diversas para replicação.

Espero que gostem.

Abraço
Mario

sábado, 29 de outubro de 2016

Erro ao executar um duplicate database em grid 12c (12.1.0.2.0)

Fala pessoal.

Hoje, montando um ambiente de testes aqui para um artigo de GoldenGate me deparei com um erro chatinho quando fui executar um duplicate from active database.

Estava com todas as configurações corretas, usando o script abaixo:

RUN 
{
set until scn 2462883;
set newname for datafile 1 to new;
set newname for datafile 2 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
set newname for datafile 5 to new;
set newname for datafile 6 to new;
set newname for tempfile 1 to new;
DUPLICATE TARGET DATABASE TO "ggcopy" FROM ACTIVE DATABASE;
}

Nada demais, clonando uma base de testes.

Estava tomando o erro:

sql statement: alter system set  db_unique_name =  ''GGCOPY'' comment= ''Modified by RMAN duplicate'' scope=spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/29/2016 09:12:29
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 10/29/2016 09:12:29
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'GGCOPY' comment= 'Modified by RMAN duplicate' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists


Pesquisando um pouco no MOS, como diz minha linda esposa, PAH!!! estava lá, um bug.

Note: ORA-65500 When Trying To Duplicate a Database (Doc ID 1963620.1)

Neste note, é referenciao um bug: Bug 20977794 - RMAN-3002 ORA-65500 from RMAN duplicate on RAC (Doc ID 20977794.8)

Existe um patch disponível, mas como não é o meu foco acertar essa base, eu nem apliquei.



Então, confirmando o bug, a minha base de testes estava registrada no meu grid, e por isso era dada a mensagem que o resource já existia.


Para resolver foi simples, aplicando o workaround do note, ou seja, excluir a base a ser clonada do grid e adicionar após o processo.

[ggcopy.rac12c1 dbs]$ srvctl remove database -d ggcopy
Remove the database ggcopy? (y/[n]) y

É isso, abraço.
Mario

terça-feira, 18 de outubro de 2016

Backup e restore de estatísticas

Hola que tal?

Eita, ahahahaha não ficou bom isso...

Opa, pessoal.. Como estão? 

Agora sim.

Em primeiro lugar, fique claro que esse texto é somente uma pequena introdução sobre o assunto. Alias nem entramos no assunto das estatísticas.

Aqui estamos abordando somente uma ou duas formas de fazer backup's de estatísticas.

Se você quer conhecer melhor sobre a coleta de estatísticas, aqui vão alguns links excelentes:


Só alguns exemplos dos excelentes textos e documentos obrigatórios da Oracle que nós achamos sobre o assunto. 

Este note do MOS também é ótimo: "FAQ: Automatic Statistics Collection (Doc ID 1233203.1)"

Dito isso, então deixemos de mimimi e vamos ao que interessa.

Esses dias me aconteceu um pequeno problema com estatísticas de um banco. Depois da coleta, a performance de várias tabelas ficou pior do que anteriormente. 

Com isso e com aquela falta de paciência normal dos usuários, veio a pergunta premiada. Tem como voltar essas tais estatísticas?

Claro, pequeno gafanhoto. Nós somos DBA's e DBA que se preze não faz nada sem backup (e testa o restore disso também, é claro).

Então, é isso que eu quero mostrar. Como fazer o "backup das estatísticas" de um schema e depois retornar isso em caso de necessidade. 

Coisa simples mas que muitos esquecem antes de fazer uma intervenção dessas.

Coloquei o "backup" entre aspas pois na verdade faremos um EXPORT/IMPORT das estatísticas no nosso primeiro exemplo. Já no caso do backup feito pelo próprio Oracle, que é o nosso segundo exemplo, trataremos de um restore propriamente dito.

Vamos ver primeiro as tabelas e suas estatísticas de um usuário qualquer. Vou usar o SQL abaixo (e deixar a parte da tabela comentada pois vamos usar mais tarde).

COL OWNER FOR A10;
COL TABLE_NAME FOR A15;

SELECT  
owner, 
table_name, 
avg_row_len, 
blocks, 
empty_blocks, 
num_rows, 
TO_CHAR(last_analyzed, 'DD/MM/YYYY HH24:MI:SS') as last_analyzed
FROM    
DBA_TABLES
WHERE   
OWNER = UPPER(NVL('&OWNER',OWNER));

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO    EXTCONFIG                44          1            0         11 10/09/2016 23:02:50
EXEMPLO    SERVER_STAT               0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TMP_TOP_SESSION           0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SESSION               0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SQL_TEXT              0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SQL                   0          0            0          0 10/09/2016 23:02:50
EXEMPLO    LOG_TEMPO                33          5            0          5 13/10/2016 23:00:13
EXEMPLO    ESTATISTICA              75        244            0      19539 12/10/2016 23:00:04
EXEMPLO    DISPONIBILIDADE          19         20            0         26 13/10/2016 23:00:11
EXEMPLO    BACKUP                    0          0            0          0 10/09/2016 23:02:50
EXEMPLO    MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06
EXEMPLO    INSTANCIA                 0         28            0          0 13/10/2016 23:00:13
EXEMPLO    ESQUEMA                  18         13            0       1641 10/10/2016 23:00:05
EXEMPLO    TABLESPACE               24         13            0       2550 28/09/2016 23:00:03
EXEMPLO    HIST_ARMAZENAME           0          0            0          0 10/09/2016 23:02:50
EXEMPLO    ARMAZENAMENTO             0          0            0          0 10/09/2016 23:02:50
EXEMPLO    SERVIDOR                  0         28            0          0 13/10/2016 23:00:13
EXEMPLO    CONFIG                   27          5            0          1 10/09/2016 23:02:50

Ok, bem zuadas algumas estatísticas por sinal, bem antigas.

Vamos ver como forçar um "backup" das estatísticas antes da coleta. Depois coletamos as estatísticas e vamos retornar algumas.

Primeiro, faremos da forma manual. Vou rodar tudo como SYS:

1) Criar a tabela que irá armazenar a estatística
exec dbms_stats.create_stat_table(
      ownname  => 'EXEMPLO',
      stattab  => 'STAT_EXEMPLO',
      tblspace => 'EXEMPLOTBS'
);

Onde:
ownname - Name of the schema. 
stattab      - Name of the table to create. 
tblspace   - Tablespace in which to create the stat tables. If none is specified, then they are created in the user's default tablespace. 

2) Vamos agora exportar as estatísticas do schema
exec dbms_stats.export_schema_stats(
      ownname => 'EXEMPLO',
      stattab => 'STAT_EXEMPLO',
      statown => 'EXEMPLO'
);

Onde:
ownname  - Name of the schema. 
stattab      - User stat table identifier describing where to store the statistics. 
statown    - Schema containing stattab (if different than ownname). 

-- Se quiser, pode verificar se todas as tabelas estão no backup das estatísticas. 
select distinct c1 as tabelas from EXEMPLO.STAT_EXEMPLO where type ='T';

-- Como curiosidade, poderíamos salvar apenas a estatística de uma tabela:
exec dbms_stats.export_table_stats(
      ownname => 'EXEMPLO',
      tabname => 'MENSAGEM',
      statown => 'EXEMPLO',
      stattab => 'STAT_EXEMPLO',
      cascade => true);

3) Vamos coletar as estatísticas de uma tabela qualquer
EXEC DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME=>'EXEMPLO',
TABNAME=>'MENSAGEM',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,
BLOCK_SAMPLE=>FALSE,
DEGREE=>16,
GRANULARITY=>'AUTO',
CASCADE=>TRUE,
NO_INVALIDATE=>FALSE,
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'
);

4) Conferimos as estatísticas
OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO     MENSAGEM                 86        622            0      40312 15/10/2016 00:31:00

Lembrando que a estatítica antigas estava com a data do dia 10/10.

5) Vamos restaurar a posição anterior dessa estatística então
exec dbms_stats.import_table_stats(
ownname => 'EXEMPLO',
stattab => 'STAT_EXEMPLO',
tabname => 'MENSAGEM'
);

E olha que beleza, tudo restaurado como antes.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO     MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06

Ok, mas e se eu tivesse coletado do schema todo? Sem problemas.

1) Vamos coletar do schema todo. Lembrando que já criamos o backup de nosso schema.
exec dbms_stats.gather_schema_stats(
OWNNAME=>'EXEMPLO',
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,
BLOCK_SAMPLE=>FALSE,
DEGREE=>16,
GRANULARITY=>'AUTO',
CASCADE=>TRUE,
NO_INVALIDATE=>FALSE,
OPTIONS=>'GATHER',
METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'
);

2) Conferimos as estatísticas atuais.
OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO    EXTCONFIG                44          1            0         11 15/10/2016 00:40:27
EXEMPLO    STAT_EXEMPLO             108          5            0        304 15/10/2016 00:40:27
EXEMPLO    SERVER_STAT               0          0            0          0 15/10/2016 00:40:27
EXEMPLO    TMP_TOP_SESSION           0          0            0          0 15/10/2016 00:40:28
EXEMPLO    TOP_SESSION               0          0            0          0 15/10/2016 00:40:28
EXEMPLO    TOP_SQL_TEXT              0          0            0          0 15/10/2016 00:40:28
EXEMPLO    TOP_SQL                   0          0            0          0 15/10/2016 00:40:28
EXEMPLO    LOG_TEMPO                33          5            0          5 15/10/2016 00:40:27
EXEMPLO    ESTATISTICA              75        244            0      20132 15/10/2016 00:40:27
EXEMPLO    DISPONIBILIDADE          19         20            0         26 15/10/2016 00:40:25
EXEMPLO    BACKUP                    0          0            0          0 15/10/2016 00:40:25
EXEMPLO    MENSAGEM                 86        622            0      40316 15/10/2016 00:40:27
EXEMPLO    INSTANCIA                 0         28            0          0 15/10/2016 00:40:27
EXEMPLO    ESQUEMA                  18         13            0       1766 15/10/2016 00:40:26
EXEMPLO    TABLESPACE               24         20            0       3269 15/10/2016 00:40:28
EXEMPLO    HIST_ARMAZENAME           0          0            0          0 15/10/2016 00:40:27
EXEMPLO    ARMAZENAMENTO             0          0            0          0 15/10/2016 00:40:25
EXEMPLO    SERVIDOR                  0         28            0          0 15/10/2016 00:40:27
EXEMPLO    CONFIG                   27          5            0          1 15/10/2016 00:40:25


3) Restauramos o schema todo. Antes, deletamos todas as estatísticas do schema.

-- Delete
exec DBMS_STATS.delete_schema_stats(ownname=>'EXEMPLO');

-- Import
exec DBMS_STATS.import_schema_stats(ownname=>'EXEMPLO',stattab=>'STAT_EXEMPLO',statown=>'EXEMPLO');

4) E conferimos de novo.
OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO    EXTCONFIG                44          1            0         11 10/09/2016 23:02:50
EXEMPLO    STAT_EXEMPLO
EXEMPLO    SERVER_STAT               0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TMP_TOP_SESSION           0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SESSION               0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SQL_TEXT              0          0            0          0 10/09/2016 23:02:50
EXEMPLO    TOP_SQL                   0          0            0          0 10/09/2016 23:02:50
EXEMPLO    LOG_TEMPO                33          5            0          5 13/10/2016 23:00:13
EXEMPLO    ESTATISTICA              75        244            0      19539 12/10/2016 23:00:04
EXEMPLO    DISPONIBILIDADE          19         20            0         26 13/10/2016 23:00:11
EXEMPLO    BACKUP                    0          0            0          0 10/09/2016 23:02:50
EXEMPLO    MENSAGEM                 86        496            0      37974 10/10/2016 23:00:06
EXEMPLO    INSTANCIA                 0         28            0          0 13/10/2016 23:00:13
EXEMPLO    ESQUEMA                  18         13            0       1641 10/10/2016 23:00:05
EXEMPLO    TABLESPACE               24         13            0       2550 28/09/2016 23:00:03
EXEMPLO    HIST_ARMAZENAME           0          0            0          0 10/09/2016 23:02:50
EXEMPLO    ARMAZENAMENTO             0          0            0          0 10/09/2016 23:02:50
EXEMPLO    SERVIDOR                  0         28            0          0 13/10/2016 23:00:13
EXEMPLO    CONFIG                   27          5            0          1 10/09/2016 23:02:50

A tabela STAT_EXEMPLO está sem estatística pois quando criamos o backup ela ainda não existia.

Veja que são procedimentos extremamente simples e que, confesso, geralmente eu não executa. Até o dia que eu precisei e o backup automático não estava disponível.

Certo, tudo ok até aqui e tranquilo né. Esse é o método que eu gosto de utilizar pois tenho controle total na operação.

Ahhh, só lembrando. Se por acaso você tem as estatísticas em uma área de homologação que se mostra mais eficaz que as estatísticas na produção, como eu poderia migrar essas estatísticas?

Simples meu amigo, como o nosso querido amigo exp/imp (ou expdp/impdp se preferir).

exp \'/ as sysdba\' FILE=estat.dmp LOG=exp_estat.log TABLES="exemplo.STAT_EXEMPLO"

imp \'/ as sysdba\' FILE=estat.dmp LOG=imp_estat.log TABLES="exemplo.STAT_EXEMPLO" ignore=Y

E agora é só executar os procedimentos listados acima.

Beleza, e se eu não fiz o export das estatísticas. Não tem problema, o Oracle faz um backup dessas estatísticas e retém por um tempo.

Vamos utilizar o backup automático do Oracle então para ver como é.

1) Vamos verificar a retenção da coleta (por default, 31 dias) e a data mais antiga disponível para restore.

-- Default da retenção
select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31

-- Coleta mais antiga disponível

select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------
13-SEP-16 11.18.16.343981000 PM -03:00

2) Vou coletar novamente as estatísticas do schema todo. E vou consultar a tabela DBA_TAB_STATS_HISTORY

select table_name, stats_update_time from DBA_TAB_STATS_HISTORY where table_name='MENSAGEM' order by 2;

TABLE_NAME      STATS_UPDATE_TIME
--------------- ------------------------------------
MENSAGEM        25-SEP-16 11.00.05.468877 PM -03:00
MENSAGEM        10-OCT-16 11.00.06.423578 PM -03:00
MENSAGEM        15-OCT-16 12.31.00.454985 AM -03:00
MENSAGEM        15-OCT-16 12.34.24.597019 AM -03:00
MENSAGEM        15-OCT-16 12.39.30.145758 AM -03:00
MENSAGEM        15-OCT-16 12.40.27.476614 AM -03:00
MENSAGEM        15-OCT-16 12.42.53.868798 AM -03:00
MENSAGEM        15-OCT-16 12.42.58.278735 AM -03:00

Essas acima são as coletas disponíveis para restore. E lembre-se como está a tabela nesse momento:

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO     MENSAGEM                 86        622            0      40324 15/10/2016 01:01:38

3) Vamos restaurar a estatística da tabela MENSAGEM, de "25-SEP-16 11.00.05.468877 PM -03:00".

exec DBMS_STATS.delete_table_stats(ownname=>'EXEMPLO',tabname=>'MENSAGEM');

exec dbms_stats.restore_table_stats('EXEMPLO', 'MENSAGEM','25-SEP-16 11.00.05.468877 PM -03:00');

4) E está lá, a estatística do dia 25/09.

OWNER      TABLE_NAME      AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS   NUM_ROWS LAST_ANALYZED
---------- --------------- ----------- ---------- ------------ ---------- -------------------
EXEMPLO     MENSAGEM                 86        496            0      29338 25/09/2016 23:00:05


Para verificar todas as operações realizadas na tabela, basta consultar a DBA_OPTSTAT_OPERATIONS .





col OPERATION  for a20
col TARGET     for a20
col START_TIME for a40
col END_TIME  for a40
col STATUS for a10
col JOB_NAME for a10

select 
    ID,
    OPERATION,
    TARGET,
    START_TIME,
    END_TIME,
    STATUS
from 
    DBA_OPTSTAT_OPERATIONS 
where 
    target like '%MENSAGEM%'
ORDER BY 
    START_TIME;

ID OPERATION              TARGET               START_TIME                               END_TIME                                 STATUS
---------- ---------------------- -------------------- ---------------------------------------- ---------------------------------------- ----------
2056 gather_table_stats     SISMON.MENSAGEM      15-OCT-16 12.30.58.665379 AM -03:00      15-OCT-16 12.31.02.537099 AM -03:00      COMPLETED
2057 import_table_stats     SISMON.MENSAGEM      15-OCT-16 12.34.23.087547 AM -03:00      15-OCT-16 12.34.25.265047 AM -03:00      COMPLETED
2069 delete_table_stats     SISMON.MENSAGEM      15-OCT-16 01.09.41.084232 AM -03:00      15-OCT-16 01.09.41.132820 AM -03:00      COMPLETED
2072 restore_table_stats    SISMON.MENSAGEM      15-OCT-16 01.13.00.186013 AM -03:00      15-OCT-16 01.13.00.219791 AM -03:00      COMPLETED
2073 restore_table_stats    SISMON.MENSAGEM      15-OCT-16 01.13.52.145885 AM -03:00      15-OCT-16 01.13.52.178049 AM -03:00      COMPLETED

É isso pessoal. Espero que ajude.

Abraço
Mario

Postagem em destaque

[ORACLE] How to move a datafile to a new location in ASM.

Hello everyone! I hope you're doing well! Today, I needed to move a datafile to another ASM Disk Group. The process was simple, but reme...