Verificando histogramas

Query para verificar informações sobre os histogramas gerados pelas estatísticas:

SELECT rownum num,column_name,type,len,precision,scale,histogram,buckets, nullok

         FROM (

               SELECT c.column_name,

                      c.data_type as type,

                      c.data_length as len,

                      c.data_precision as precision,

                      c.data_scale as scale,

                      c.histogram,

                      h.buckets,

                      c.nullable as nullok

               FROM dba_tab_columns c,

                    (SELECT owner,table_name,column_name,count(*) as buckets

                     FROM dba_histograms

                     WHERE owner=&&OWN and

                           table_name=&&TAB

                     GROUP BY owner,table_name,column_name ) h

               WHERE c.owner=&&OWN and

                     c.table_name=&&TAB and

                     c.table_name = h.table_name(+) and

                     c.column_name = h.column_name(+) and

                     c.owner = h.owner(+)

               ORDER BY c.column_id);

 

 

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

Utilizando o pacote dbms_redefinition para particionamento de tabelas no Oracle 10g

O objetivo desse tutorial é realizar particionamento de tabelas on-line. Podemos usar o pacote para outras ações, como reorganização de tabelas.

Para esse procedimento vamos dividir as tarefas, conforme abaixo:

1. Verificar se a tabela é candidata ao particionamento
2. Criação das novas tablespaces
3. Criação da tabela interina
4. Start do Redefinition
5. Sync do Redefinition
6. Finish do Redefinition

1) Verificar se a tabela é candidata ao particionamento

Iremos utilizar a procedure CAN_REDEF_TABLE do pacote DBMS_REDEFINITION
Essa procedure determina se é possível fazer o redimensionamento online. Esse é o primeiro passo para o processo de particionamento (redefinition). Se a tabela não puder ser particionada retornará um erro

exec dbms_redefinition.can_redef_table('SCOTT', 'BILLING', DBMS_REDEFINITION.CONS_USE_PK);

2) Criação das novas tablespaces

Realizar esse passo de acordo com as suas novas partições

3) Criação da tabela interina

CREATE TABLE SCOTT.BILLING

PARTITION BY HASH (CODE1, CODE2, CODE3)

PARTITIONS 20

STORE IN (DATA_PAR_HASH1,DATA_PAR_HASH2,DATA_PAR_HASH3,DATA_PAR_HASH4,DATA_PAR_HASH5,DATA_PAR_HASH6,DATA_PAR_HASH7,DATA_PAR_HASH8,DATA_PAR_HASH9,DATA_PAR_HASH10,DATA_PAR_HASH11,DATA_PAR_HASH12,DATA_PAR_HASH13,DATA_PAR_HASH14,DATA_PAR_HASH15,DATA_PAR_HASH16,DATA_PAR_HASH17,DATA_PAR_HASH18,DATA_PAR_HASH19,DATA_PAR_HASH20)

AS SELECT * FROM SCOTT.BILLING where 1=2;

4) Start do Redefinition

Iremos utilizar a procedure START_REDEF_TABLE do pacote DBMS_REDEFINITION
Essa procedure inicia o processo de particionamento on-line.

begin

dbms_redefinition.START_REDEF_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, COL_MAPPING => NULL

, OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_PK

, ORDERBY_COLS => NULL

, PART_NAME => NULL

);

end;

/

5) Cópia dos dependentes

Iremos utilizar a procedure COPY_TABLE_DEPENDENTS do pacote DBMS_REDEFINITION
Essa procedure copia as dependências da tabelas de origem para a tabela interina

DECLARE

num_errors PLS_INTEGER;

BEGIN

dbms_redefinition.COPY_TABLE_DEPENDENTS

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, COPY_INDEXES => DBMS_REDEFINITION.CONS_ORIG_PARAMS

, COPY_TRIGGERS => TRUE

, COPY_CONSTRAINTS => TRUE

, COPY_PRIVILEGES => TRUE

, IGNORE_ERRORS => TRUE

, NUM_ERRORS => num_errors

, COPY_STATISTICS => TRUE

);

END;

/

Verificar os erros:

SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS where base_table_name = 'BILLING'

select OBJECT_TYPE, OBJECT_NAME, BASE_TABLE_NAME, INTERIM_OBJECT_NAME from dba_redefinition_objects where BASE_TABLE_NAME = 'BILLING' order by 1,2,3;

6) Sync do Redefinition

Essa procedura mantém a tabela provisória sincronizado com a tabela original. Este passo é útil para minimizar a quantidade de sincronização antes de concluir a redefinição online.

BEGIN

dbms_redefinition.SYNC_INTERIM_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, PART_NAME => NULL

);

END;

/

7) Finish do Redefinition

Esse procedure complete o processo de particionamento. Após concluir esta etapa, a tabela original é redefinido com os atributos e os dados da tabela provisória. A tabela original está bloqueado brevemente durante este procedimento

BEGIN

dbms_redefinition.FINISH_REDEF_TABLE

(

UNAME => 'SCOTT'

, ORIG_TABLE => 'BILLING'

, INT_TABLE => 'BILLING_PAR'

, PART_NAME => NULL

);

END;

/

9) Abortar o processo (se necessário)

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE

(

uname => 'BILLOWN'

, orig_table => 'BILLING'

, int_table => 'BILLING_PAR'

, part_name => NULL

);

END;

/

VIRTUAL INDEX

Antes de criar um index, podemos testar se ele resolverá seu problema através da criação de um INDEX VIRTUAL.

SQL> create table emp
2 (id number,
3 job varchar2(50))
4 /

Tabela criada.

SQL> declare
2 contador integer;
3 begin
4 contador := 1;
5 while contador <= 1000 loop
6 insert into emp values (contador,'JOB');
7 contador := contador + 1;
8 end loop;
9 commit;
10 end;
11 /

Procedimento PL/SQL concluído com sucesso.

SQL> alter table emp add status varchar2(20);

Tabela alterada.

SQL> update emp set status = 'ok' where id between 1 and 500;

500 linhas atualizadas.

SQL> update emp set status = 'nok' where id between 501 and 1000;

500 linhas atualizadas.

SQL> commit;

Commit concluído.

SQL> set autotrace traceonly explain

SQL> select * from emp where status = 'ok';

Plano de Execução
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 26000 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 500 | 26000 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("STATUS"='ok')

Note
-----
- dynamic sampling used for this statement

SQL> create index idx_emp_status on emp(status) nosegment;

Índice criado.

SQL> select * from emp where status = 'ok';

Plano de Execução
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 26000 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 500 | 26000 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("STATUS"='ok')

Note
-----
- dynamic sampling used for this statement

SQL> alter session set "_use_nosegment_indexes" = true;

Sessão alterada.

SQL> select * from emp where status = 'ok';

Plano de Execução
----------------------------------------------------------
Plan hash value: 229917500

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 26000 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 500 | 26000 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_STATUS | 4 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

2 - access("STATUS"='ok')

Note
-----
- dynamic sampling used for this statement

Utilizando o pacote dbms_sqltune para otimização de queries

O pacote DBMS_SQLTUNE identifica e ajuda a resolver problemas de desempenho do banco de dados

Abaixo Passo a Passo para tunning de uma query

1) Pegar o SQL_ID da query que pretende otimizar

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT coluna1, coluna2 FROM tabela%';

2) Criar a Task

SQL_ID --> SQL_ID (captura no select acima)
SCOPE --> DBMS_SQLTUNE.SCOPE_COMPREHENSIVE
TIME_LIMIT --> Tempo de execução da query

DECLARE
VAL VARCHAR2(4000);
BEGIN
VAL := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => 'gxtg1pzkt5fm5', SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, TIME_LIMIT => 60, TASK_NAME => 'Tuning 1', DESCRIPTION => ''Tuning de SQL 1');
END;
/

Caso não tenha o SQL_ID

set serveroutput on
DECLARE
l_sql CLOB;
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql := 'select * from teste whete coluna_teste > sysdate -30';
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
user_name => 'SCOTT',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'Tuning 1',
description => 'Tuning de SQL 1');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

3) Executar a task para obter as informações
Obs.: nesse passo que vai demorar, dependendo do tempo da query

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('Tuning 1');

4) Recomendações

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('Tuning 1') RECOMMENTATION FROM DUAL;

5) Excluir o Task

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('Tuning 1');

6) Verificar outros advisor

select task_id,task_name,status from user_advisor_log;

7) Interromper a task

begin
DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'Tuning 1');
end;
/

8) Verifica task interrompida

begin
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'Tuning 1');
end;
/

9)Cria o script com o conjunto de advisor

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('Tuning 1') RECOMMENTATION FROM DUAL;

Oracle Physical Standby - monitorações

Mostra o último timestamp e número de sequência que foi recebido e aplicado para o standby:

select 'Ultimo aplicado : ' INFO, to_char(max(next_time),'DD-MON-YY:HH24:MI:SS') Time,thread#,max(sequence#)
from v$archived_log
where applied='YES'
group by thread# union
select 'Ultimo recebido : ' INFO, to_char(max(next_time),'DD-MON-YY:HH24:MI:SS') Time,thread#,max(sequence#)
from v$archived_log
group by thread#;

Performance do dataguard:

select
    NAME Name,
    VALUE Value,
    UNIT Unit
    from v$dataguard_stats
    union
    select null,null,' ' from dual
    union
    select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
   from v$dataguard_stats;


Status dor processos de captura (RFS) e apply (MRP)

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Informações de GAP:


select * from v$archive_gap;
     

SRVCTL - alterar start_options de um database

Usando um RAC como Physical Standby: alterando o start_options do database para o standby subir como MOUNT:

[oracle@server ~]$ srvctl modify database -d SID1 -s MOUNT

Verificar configurações do database:

[oracle@server ~]$ srvctl config database -d sid1 -a

svr0001 SID1a /u00/app/oracle/product/10.2.0/
svr0002 SID1b /u00/app/oracle/product/10.2.0/
svr0003 SID1c /u00/app/oracle/product/10.2.0/
DB_NAME: SID1
ORACLE_HOME: /u00/app/oracle/product/10.2.0/
SPFILE: null
DOMAIN: null
DB_ROLE: null

START_OPTIONS: MOUNT

POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED