Datatypes limits no oracle 11G

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm#i287903

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

Usuários - Criação e alteração

http://ss64.com/ora/user_c.html


*************************************************************
--  VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
*************************************************************
todos
SELECT USERNAME, created,account_status FROM DBA_USERS;

por usuário
SELECT USERNAME, created, account_status,FROM DBA_USERS WHERE lower (USERNAME)LIKE ‘USER%‘;

*************************************************************
-- CRIAÇÃO
*************************************************************

create USER nome_do_user identified by password;

lockar, deslocar
alter user nome_do_user ACCOUNT UNLOCK /LOCK;
ALTER user username password EXPIRE  -> forçar alteração de senha

OPÇÕES DE CRIAÇÃO

Syntax:

   CREATE USER username
      IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
         options;

options:
 
   DEFAULT TABLESPACE tablespace
   TEMPORARY TABLESPACE tablespace
   QUOTA int {K | M} ON tablespace
   QUOTA UNLIMITED ON tablespace
   PROFILE profile_name
   PASSWORD EXPIRE
   ACCOUNT {LOCK|UNLOCK}


*************************************************************
ATIVAÇÃO
*************************************************************

ALTER user username account lock
ALTER user username account unlock


*************************************************************
---VERIFICAR COTAS E TABLESPACES:
*************************************************************

set linesize 128
SELECT USERNAME, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE lower(USERNAME) LIKE 'USER%'
SELECT USERNAME, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE lower(USERNAME) LIKE 'scott%';

ALTERAR TABLESPACE
ALTER USER USERNAME TEMPORARY |DEFAULT  TABLESPACE tablespace_name


*************************************************************
--COTAS
*************************************************************

ALTERAR A COTA
ALTER USER nome_do_user quota tamanho_da_cota on nome_tablespace
alter user scott quota 10m on users;
alter user scott quota unlimited on example;

CHECAR COTA
select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='nome_do_user';
select tablespace_name,bytes,max_bytes from dba_ts_quotas where username='GTORRES';

TABLESPACES PADRÃO DO BANCO
SELECT property_name , property_value from database_properties WHERE property_name like'%TABLESPACE';


*************************************************************
---  AUTENTICAÇÃO
*************************************************************

connect username/password@db_alias as privilegio
conn sys/oracle@orcl as sysdba

CHECAR OS PRIVILEGIOS SYSDBA E SYSOPER checar a view V$PWFILE_USERS
select *from V$PWFILE_USERS;

CHECAR AUTENTICAÇÃO PELO SO 
select value from v$parameter where name='os_authent_prefix';


CRIANDO  um user com autenticação pelo SO -> inserir o valor padrão ops$ antes do nome criado e após o parametro identified externally
NO Unix, qualquer usuário criado dessa maneira será capaz de emitir o comando sqlplus /
create user ops$nome_user indentified externally;

no windows:
create user "OPS$DOMINIO_DA_MAQUINA\USER_DO_SO" identified externally;


*************************************************************
-- GRANTS
*************************************************************

grant privilegio to user
revoke privilegio from user
ex:
GRANT select any table to username
GRANT CREATE SESSION to username
GRANT ROLE_NAME TO USER_NAME with admin option
GRANT privilegio on schema.user object to username
GRANT privilegio on schema.user object to username with GRANT option
GRANT privilegio on schema.user object to username with ADMIN option
grant all on schema.user to ROLE_NAME

checando privilégios
select *from dba_role_privs where grantee ='JON';


DROPANDO

DROP USER SchemaOwner CASCADE;

CREATE USER MySchemaOwner IDENTIFIED BY ChangeThis
       DEFAULT TABLESPACE data  
       TEMPORARY TABLESPACE temp
       QUOTA UNLIMITED ON data;

CREATE ROLE conn;

GRANT CREATE session, CREATE table, CREATE view, 
      CREATE procedure,CREATE synonym,
      ALTER table, ALTER view, ALTER procedure,ALTER synonym,
      DROP table, DROP view, DROP procedure,DROP synonym,
      TO conn;

GRANT conn TO SchemaOwner;

TABLESPACES

criação - pag 261 - 265
sintaxes:
http://ss64.com/ora/tablespace_c.html
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm#SQLRF01403

Sintaxe criação

create TABLESPACE nome_tablespace
DATAFILE 'CAMINHO_DATAFILE\NOME_DATAFILE_1.DBF' size 1G (m|g|t) --EXEMPLO 'C:\ORADATA\GLTABS\_01.DBF'
'CAMINHO_DATAFILE\NOME_DATAFILE_2.DBF' size 1G
extent management local uniform size 5120k;


----------------------
COM FLASHBACK
----------------------

CREATE TABLESPACE APPL_DATA
DATAFILE ‘/disk3/oradata/DB01/appl_data01.dbf’
SIZE 100M
DEFAULT STORAGE COMPRESS
BLOCKSIZE 16K
LOGGING
ONLINE
FORCE LOGGING
FLASHBACK ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE ts_myapp DATAFILE
'/data/ts_myapp01.dbf' SIZE 200M,
'/data/ts_myapp02.dbf' SIZE 500M
logging
autoextend off
extent management local;


----------------------
UNDO TABLESPACE
----------------------

 -> CREATE UNDO TABLESPACE nome_tablespace_undo01  
datafile  '/data/ts_undo01.dbf' SIZE 50000M REUSE
autoextend on
RETENTION NOGUARANTEE | RETENTION GUARANTEE;

-> CREATE UNDO TABLESPACE ts_undo01 DATAFILE
'/data/ts_undo01.dbf' SIZE 50000M REUSE
autoextend on
RETENTION NOGUARANTEE;


------------------------------------------------------------
CRIANDO Bigfile and Smallfile Tablespaces

CREATE BIGFILE TABLESPACE PO_ARCHIVE
DATAFILE ‘/u02/oradata/11GR11/po_archive.dbf’ size 25G;

CREATE SMALLFILE TABLESPACE PO_DETAILS
DATAFILE ‘/u02/oradata/11GR11/po_details.dbf’ size 2G;


----------------------------------------------------------------------------------

TRABALHANDO COM OMF
 
ALTER SYSTEM SET
db_create_file_dest = ‘/u02/oradata/’ SCOPE=BOTH;
When creating a tablespace using the OMF feature, you simply omit the filename:
CREATE TABLESPACE hr_data;
exemplo:
alter system set DB_CREATE_FILE_DEST ='/home/db11g/oradata';

ou alterar nos parametros:

DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
DB_CREATE_ONLINE_LOG_DEST_3
DB_CREATE_ONLINE_LOG_DEST_4
DB_CREATE_ONLINE_LOG_DEST_5
DB_RECOVERY_FILE_DEST
********************************************************************************************

ALTERAÇÃO - Sequencia

1 -INVESTIGAR O CAMINHO - Usar as views

todas - V$TABLESPACE
views - temp -> v$tempfile  e dba_temp_files
datafiles ->v$datafile e dba_data_files , DBA_SEGMENTS
DBA_DATA_FILES
DBA_TABLESPACES
USER_TABLESPACES
DBA_TEMP_FILES
DBA_TS_QUOTAS
USER_TS_QUOTAS


Steps 

1 -  > criar a tablespace
create smallfile tablespace newts
datafile 'C:\oracle\app\oradata\orcl\newsts01.dbf'
size 100m autoextend on next 10m maxsize 200m
logging
extent management local
segment space management auto
default nocompress;


2 - -> renomear a tablespace
ALTER TABLESPACE tablespaceOLDname RENAME TO tablespaceNEWname;
ALTER TABLESPACE newts RENAME TO newts02;


3 - -> colocar  a nova tablespace offline
ALTER TABLESPACE  newts02 OFFLINE;


4 - -> renomear os datafiles no SO
host rename CAMINHO_OLD_DATAFILE\NOME_OLD_DATAFILE_.DBF NOME_NEW_DATAFILE.DBF 
host rename C:\oracle\app\oradata\orcl\newsts01.dbf newsts02.dbf;


5 - -> ALTERAR no banco o antigo datafile

ALTER DATABASE rename file 'C:\oracle\app\oradata\orcl\newsts01.dbf'
TO 'C:\oracle\app\oradata\orcl\newsts02.dbf';


6 - ->  Colocar a tablespace online
ALTER TABLESPACE  tablespaceNEWname ONLINE;
ALTER TABLESPACE  newts02 ONLINE;

7 - Checando
obs: @dba_tablespaces

ou usar a query

select substr(a.tablespace_name,1,20) "Tablespaces",
(b.BYTES/1048576) as "TotalMB",
(b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",
(c.BYTES/1048576) as "FreeMB"
from dba_tablespaces a,
(select tablespace_name,sum(bytes) as "BYTES" 
from dba_data_files 
group by tablespace_name ) b,
(select tablespace_name,sum(bytes) as "BYTES" 
from dba_free_space 
group by tablespace_name) c
where
a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
order by a.tablespace_name;


Dropando

drop tablespace newts02 including contents and datafiles;

****************************************************************************

MARCAR COMO LEITURA- GRAVAÇÃO OU SOMENTE LEITURA

para somente LEITURA -> ALTER TABLESPACE NOME_TABLESPACE read only
para ler e gravar             -> ALTER TABLESPACE NOME_TABLESPACE read write

PARA REDIMENSIONAR O DATAFILE
sintaxe - ALTER DATABASE TIPO_DO_DATAFILE  NOME_DO_DATAFILE RESIZE M|g|t

-> ALTER DATABASE DATAFILE 'C:\oracle\app\oradata\orcl\newsts02.dbf' resize 10m; 

ALTER DATABASE DATAFILE 'C:\oracle\app\oradata\orcl\newsts02.dbf' resize 10m;
autoextend on next 100m maxsize 4G ;   ---- Não esquecer de botar o limite máximo, neste exemplo é 4G


---------------------------------------------------------------------------------
PARA ADICIONAR DATAFILE

ALTER TABLESPACE NOME_TABLESPACE
add datafile 'caminho_e_nome.dbf' size M|g|t ;

exemplo ->
ALTER TABLESPACE gl_large_tabs
add datafile 'C:\oracle\app\oradata\orcl\newsts02.dbf' size 2g ;

no Linux
alter tablespace nome_tablespace add datafile 
'/dev/vx/rdsk/oracledg/db_name_data02.dbf' size 6001m



---------------------------------------------------------------------------------


GERENCIANDO ESPAÇOS
OBS: PARA AUMENTAR  TABLESPACE - SE ADICIONA DATAFILES - ADD DATAFILE

exemplo ->
ALTER TABLESPACE gl_large_tabs
add datafile 'C:\oracle\app\oradata\orcl\newsts02.dbf' size 2g ;

PARA AUMENTAR SEGMENTOS ( DENTRO DA TABLESPACE )  - SE ALOCA EXTENSÕES - allocate extent
-- >  alter table newtabs allocate extent;

E DENTRO DOS SEGMENTOS, são aumentadas as linhas

-----------------------------------------------------------------------

CONVERTENDO GERENCIAMENTO POR DICIONÁRIO PARA LOCAL (O IDEAL):


OBS: para criar uma com gerenciamento manual (não e´o ideal - pag 275)
-> create tablespace NOME_TABLESPACE SEGMENT SPACE MANAGEMENT LOCAL

1 - verificar pelas querys se há tablespace com gerenciamento local:
select tablespace_name, extent management from dba_tablespaces;
select tablespace_name, segment_space_management from dba_tablespaces;

    Checar pelo nome da tablespace: 
  -> select segment_space_management from dba_tablespaces where tablespace_name='NOME_TABLESPACE'

2 - para converter para auto:
execute dbms_space_admin.tablespace_migrate_to local('tablespace_name')

Para alterar o TRESHOLD NO EM: tablespace / edit tablespace /TRESHOLD


**********************************************************************************************

PARA MOVER SEGMENTOS:

ALTER  tipo_de segmento nome_do_segmento  MOVE TABLESPACE nome_TABLESPACE_ANTIGA
tipo_de segmento nome_do_segmento MOVE  nome_TABLESPACE_NOVA;
->
 ALTER table mantab MOVE TABLESPACE autosalter table mantab MOVE TABLESPACE autosegs;

Se tiver indices, fazer o rebuild
alter index mantabi rebuild ONLINE TABLESPACE autosegs;s;

CONFIRMAR SE ESTÃO NA TABLESPACE correta
-> select tablespace_name from DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'MANTAB%'  

-----------------------------------------------------------------------------------------------------------

Querys importantes:

Aumento de Tablespace

select SUM(bytes/1024/1024/1024) from dba_free_space where tablespace_name = 'APPS_TS_TX_DATA';

 
---> verificar tamanho de uma tablespace

select file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name = 'USERS' order by file_name;
---> verificar qual o ultimo dbf para adicionar


Informações das tablespaces - Oracle

select substr(a.tablespace_name,1,20) "Tablespaces",
(b.BYTES/1048576) as "TotalMB",
(b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",
(c.BYTES/1048576) as "FreeMB"
from dba_tablespaces a,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_data_files
group by tablespace_name ) b,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_free_space
group by tablespace_name) c
where
a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
order by a.tablespace_name;


Ver tablespaces com menos de 20% livre

COL tbs FORMAT a25
COL total(mb) FORMAT 999,990.00
COL livre(mb) FORMAT 999,990.00
COL livre(%) FORMAT 990.00
select instance_name,host_name,to_char(sysdate,'dd/mm/yy hh24:mi') from v$instance;
SELECT DISTINCT d.tablespace_name "NOME DA TABLESPACE",t.total "TOTAL(MB)", NVL(f.livre,0) "LIVRE(MB)", (NVL(f.livre,0)*100/t.total) "LIVRE(%)"
FROM dba_data_files d,
     (SELECT tablespace_name ,sum(bytes)/1024/1024 total FROM dba_data_files GROUP BY tablespace_name) t,
     (SELECT tablespace_name ,sum(bytes)/1024/1024 livre FROM dba_free_space GROUP BY  tablespace_name) f
WHERE d.tablespace_name =t.tablespace_name
AND   d.tablespace_name = f.tablespace_name(+)
AND (NVL(f.livre,0)*100/t.total) <= 20
ORDER BY 4 DESC;


Listar os datafiles das tablespaces

COL tablespace_name FORMAT a15
COL file_name FORMAT a45
COL size_mb FORMAT 999,990.00
COL autoextensible  FORMAT a5

select df.tablespace_name, df.file_name, df.bytes/1024/1024 size_mb, df.status, df.autoextensible
from dba_data_files df
where df.tablespace_name='&tablespace_name';


Adicionar espaço na tablespace
Resize: alter database datafile '/oracle/oradata/nome_datafile.dbf' resize  8192 m

Create:  alter tablespace nome_tablespace add datafile '/dev/vx/rdsk/oracledg/db_name_data02.dbf' size 6001m


Script para adição de um novo data file em dada tablespace.

SQL> alter tablespace nome_tablespace
 add datafile '/oracle/SID/sapdata2/btabi_10/btabi.data10' size 1024064K
 autoextend on
 next 1024064K
 maxsize 16777280K;


#tablespace de undo#
CREATE UNDO TABLESPACE UNDOTBS DATAFILE
'c:\oracle9i\treino9i\UNDOTBS.dbf' SIZE 350M

#Criar tablespace temp#
CREATE TEMPORARY TABLESPACE teste DATAFILE
'/proj/ebsdsgev/oradata/teste.dbf' SIZE 1024M
DEFAULT STORAGE (INITIAL 1024 K NEXT 1024 K
MAXEXTENTS unlimited PCTINCREASE 0);

#Criar tablespace TMP#
CREATE TEMPORARY TABLESPACE TEMP3
   TEMPFILE '/proj/ebsdsgev/oradata/temp03.dbf'
    SIZE 1024M AUTOEXTEND ON;

#apagar tablespace#
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;


#Ver qual tablespace esta como temp#
select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TABLESPACE%';


Obrigado ao meu amigo Bruno Pelegrini pelas dicas

Eventos de espera no ORACLE

Evento de Espera	Descrição - quando o processo.....
enqueue	está esperando pelo liberamento de algum recurso
library cache pin	quer examinar algum objeto e certificar que ninguém conseguirá alterá-lo neste tempo.
library cache load Lock	está esperando pela oportunidade de carregar um objeto ou  parte de um objeto na biblioteca de cache.
latch free	está esperando por um latch (termo usado quando um grande número de processos  estão competindo pelo acesso a algum objeto interno)
buffer busy waits	quer acesso a um bloco de dados que não está na memória,  mas que outro já requisitou uma E/S para trazê-lo.
control file sequential read	está esperando para acessar blocos do arquivo de controle.
control file parallel write	está esperando que termine sua requisição de E/S em paralelo  para os arquivos de controle.
log buffer space	está esperando por espaço livre no buffer do log.  (geralmente acontece quando as aplicações produzem redo  (imagem anterior à alteração de algum registro do banco)  mais rápido que o processo de descarregamento dos buffers de log em disco conseguem consumí-los.
log file sequential read	está esperando para trazer blocos do arquivo de log ativo para memória.
log file parallel write	está esperando para escrever blocos para um grupo de arquivos de log ativos.
log file sync	está esperando que processo responsável pela escrita de log  termine de escrever os buffers para o disco.
db file scattered read	emitiu uma requisição de E/S para ler uma série de blocos contíguos  de um arquivo de dados para a área de buffer e está esperando a operação completar.  Tipicamente nas consultas (full scan) de índices e tabelas.
db file sequential read	emitiu uma requisição de E/S para ler um bloco de um arquivo de dados  para a área de buffer e está esperando a operação completar.
db file parallel read	emitiu múltiplas requisições de E/S em paralelo para ler blocos de arquivos de dados  para a área de buffer e está esperando que todas as operações se completem.
db file parallel write	emitiu múltiplas requisições de descarregamento dos buffers para o disco está esperando  que todas as operações completem.
direct path read, direct path write	emitiu requisições assíncronas de E/S que não passam pelos buffers e está esperando  que todas as operações completem. É um evento típico de operações de classificação.

referencias

Documentação 11G

GPO 

http://www.ora-code.com/ 

http://tahiti.oracle.com/
runningoracle.com
Psoug.org
Oracle Base
http://ss64.com/ora/

Guias certificação OCA 11G 
certificacaobd.com.br
Exames
Certificação Oracle- dicas 
http://fabiodba.blogspot.com/
brunors.com

Scripts
Scripts úteis

Estudo dia a dia

http://www.datadisk.co.uk/#
Oracle Base 
Recuperações diversas (Banco)
Arup Nanda
http://www.wellingtonprado.com/ 

AlejandroVargas 

https://blogs.oracle.com/

Burleson
http://www.oraclehome.com.br/
http://eduardolegatti.blogspot.com/
http://kb.paxtecnologia.com.br 
http://blog.gaudencio.net.br 
http://www.oracle.com/technetwork
http://flaviosoares.com/
http://www.fabioprado.net/
http://felipe.fortaltec.com.br/blog/
http://allthingsoracle.com/
http://databaseguard.blogspot.com/
https://sites.google.com/site/telodba/
http://www.diaadiaoracle.com.br/wordpress/ 
Dia a dia na T.I. [Vivência de um DBA 
http://www.rodrigoalmeida.net/blog/tag/banco/
dbasolutions 
http://oraclemais.blogspot.com/
http://ebsdicas.blogspot.com/
beingoracleappsdba 
http://www.dbatutor.com/ 

ACES
http://www.gokhanatil.com/


OEBS   Querys uteis -
http://glufke.net/oracle
runningoracle.com - APPS DBA
http://www.idevelopment.info/ 
dbasolutions 
http://doganay.wordpress.com/

Unix
Toolbox

http://ss64.com 

Outros
http://valteraquino.blogspot.com/

AWRs

Funcionalidades do Advanced Workload Repository(AWR)

Fonte: paxtecnologia.com.br

O Oracle usa o AWR para detectar e analisar problemas,antes que eles possam provocar uma
interrupção no banco.
Nele contém os snapshots de todas estatísticas e cargas de trabalho importantes no banco em intervalos de 60 minutos(default), e são mantidas por 7 dias (default), e depois descartadas.

Para emitir um relatório,basta chamar o script de dentro do SQL Plus:
@?/rdbms/admin/awrrpt.sql

Para fazer comparações entre dois períodos e ver as diferenças, basta executar o seguinte script:
@?/rdbms/admin/awrddrpt.sql

Caso deseje ver o plano de execução (Explain Plan) de algumas das queries apresentadas no relatório em um determinado período, basta executar este outro relatório:
@?/rdbms/admin/awrsqrpt.sql


Para criar snapshots manualmente, basta fazer o seguinte:
 
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

O parâmetro STATISTICS_LEVEL deve estar com o valor TYPICAL ou ALL para que o relatório AWR traga as estatísticas completas. Caso esteja com o valor BASIC o snapshot poderá ser criado mas algumas estatísticas estarão faltando.

Para alterar settings dos snapshots colhidos automaticamente:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutos (= 30 Dias). Caso nenhum valor seja especificado o Oracle irá utilizar o valor corrente.
    interval  => 60);          -- Minutos. Caso nenhum valor seja especificado o Oracle irá utilizar o valor corrente.
END;
/

Aumentando a retenção para 45 dias:
exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 64800);

Para verificar valores correntes dos snapshots colhidos automaticamente:
select * from dba_hist_wr_control;

Para apagar um range de snapshots:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 15000,
    high_snap_id => 15050);
END;
/



Como interpretar um relatório AWR?
No início do relatório temos informações sobre a base de dados, se está em RAC ou não, o DB ID, a versão etc.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
prod        2627801140 inst_test           2 10.2.0.3.0  NO    db

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     25160 14-Jul-08 17:00:59        53       1.2
  End Snap:     25161 14-Jul-08 18:00:18        57       1.2
   Elapsed:               59.32 (mins)
   DB Time:              295.81 (mins)

Na próxima seção temos o tamanhos dos caches e suas respectivas variações de tamanho durante o período em que o relatório foi emitido. Todos estes parâmetros podem ser editados no pfile/spfile.

Cache Sizes
~~~~~~~~~~~                       Begin                                                End
                             ---------- ----------
               Buffer Cache:     2,000M     2,000M  Std Block Size:        32K
           Shared Pool Size:       304M       304M      Log Buffer:    30,752K

Na seção Load Profile pode-se analisar a carga da instância por segundo ou por transação. Vocë pode ainda comparar esta seção entre dois AWR Reports para ver se a carga de sua instância aumentou ou diminuiu.

Aumento de Redo Size & Block Changes:
Se houver um aumento nisto, provavelmente você está fazendo mais INSERTS, UPDATES e DELETES do que antes.

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          2,369,109.72          2,183,459.12
              Logical reads:             17,727.53             16,338.35
              Block changes:              5,579.17              5,141.97
             Physical reads:                839.34                773.57
            Physical writes:                591.31                544.98
                 User calls:                 11.66                 10.74
                     Parses:                 11.67                 10.75
                Hard parses:                  0.48                  0.45
                      Sorts:                  1.56                  1.43
                     Logons:                  0.39                  0.36
                   Executes:                 12.46                 11.48
               Transactions:                  1.09

  % Blocks changed per Read:   31.47    Recursive Call %:    92.77
 Rollback per transaction %:   93.81       Rows per Sort:  2072.81

Plano de execução

Plano de execução
Fonte:
http://www.fabioprado.net/2011/03/analisando-o-plano-de-execucao-para.html

Analisando o Plano de Execução para tunar instruções SQL

Olá Pessoal,
  

     No artigo de hoje irei comentar sobre o que é o Plano de Execução (no Oracle Database) e como utilizá-lo para nos a ajudar a tunar queries, e consequentemente, criarmos aplicações com melhor desempenho.
  

     O Plano de Execução de uma instrução SQL é uma sequência de operações que o Banco de Dados (BD) Oracle realiza para executar uma instrução. Ele é exibido em forma de uma árvore de linhas, que representam passos e que contém as seguintes informações:
  

       - Ordenação das tabelas referenciadas pela instrução;

       - Método de acesso para cada tabela mencionada na instrução;

       - Método join para as tabelas afetadas pelas operações join da instrução;

       - Operações de dados tais como filter, sort ou agregação;

       - Otimização: custo e cardinalidade de cada operação;

       - Particionamento: conjunto de partições acessadas;

       - Se a instrução utilizará execução paralela etc.

  

     O Plano de Execução pode mudar conforme o ambiente em que está sendo executado. Ele pode mudar se for executado em schemas diferentes ou ambientes de Bancos de Dados com custos (volume de dados e estatísticas, parâmetros de servidor ou sessão etc.) diferentes.

  

     Os resultados de um plano de execução permitem visualizar as decisões do Otimizador de Query do Oracle  e analisar a performance de uma query. Através dele é possível verificar se uma query acessa dados através de full table scan (°) ou index lookup (¹), e até mesmo, qual tipo de join ela efetuou: um nested loops join (²) ou um hash join (³).

(°) Full table scan: Caminho de acesso em que os dados são recuperados percorrendo todas as linhas de uma tabela. É mais eficiente para recuperar uma grande quantidade de dados da tabela. 

(¹) Index Lookup: Caminho de acesso em que os dados são recuperados através do uso de índices. É mais eficiente para recuperar um pequeno conjunto de linhas da tabela. 

(²) Nested loop join: Método de acesso de ligação (join) entre 2 tabelas ou origens de dados, utilizado quando pequenos conjuntos de dados estão sendo ligados e se a condição de ligação é um caminho eficiente para acessar a segunda tabela. 

(³) Hash join: Método de acesso de ligação (join) entre 2 tabelas ou origens de dados, utilizado para ligar grandes conjuntos de dados.
 

     O Plano de Execução, por si só, não pode diferenciar instruções SQL bem tunadas (mais otimizadas) daquelas que não apresentam boa performance. O acesso a dados por meio de índices normalmente é mais rápido que acesso full table scan, em ambientes OLTP, porém o fato do Plano de Execução utilizar um índice em algum passo da execução não necessariamente significa que a instrução será executada eficientemente. Em alguns casos, índices podem ser extremamente ineficientes. Segundo a Oracle, quando uma consulta irá retornar mais que 4% dos dados de uma ou mais tabelas ou quando uma consulta irá acessar tabelas pequenas (com poucas linhas), geralmente é mais rápido o acesso full table scan do que o acesso index lookup.

  

     Para analisar o Plano de Execução de uma instrução SQL pode-se utilizar o comando EXPLAIN PLAN, que permite exibir um plano escolhido pelo otimizador de queries do Oracle, para executar as instruções  SQL. Ao executá-lo, o otimizador escolhe um plano de execução e insere os dados descrevendo este plano em uma tabela do BD chamada PLAN_TABLE (é possível também gravar em outras tabelas). Para analisar o plano de execução é necessário escrever uma consulta para pesquisar essa tabela (SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

     Segue abaixo a Imagem 01, com um exemplo de como usar o EXPLAIN PLAN para ver os seus resultados e analisar o plano de execução de uma instrução SQL:

Imagem 01 - Exemplo de um plano de execução

                  

                 Obs.: Se no resultado (PLAN_TABLE_OUTPUT)  você não visualizar a coluna Time, conecte-se no BD através do SQL Plus e execute os comando(s) e script(s) abaixo:
       

                      drop table plan_table purge;

                      commit;

                      @$ORACLE_HOME/rdbms/admin/catplan.sql -- exemplo considerando SO Linux. Substitua $ORACLE_HOME pelo diretório correspondente ao Oracle Home do BD. 

      

     Seguem abaixo algumas regras gerais para analisar um plano de execução:

          - Em geral, a ordem de execução tem início na linha que está identada mais para a direita, seguindo a ordem do primeiro filho do nó raiz da árvore (para compreender melhor este item, consulte as referências ao final do artigo);

          -  O próximo passo a ser executado é o pai da linha encontrada no passo anterior, ou seja, a linha que está identada no nível à esquerda mais próximo;

          -  Se 2 linhas estão identadas igualmente (no mesmo nível), a linha mais acima é executada primeiro;

          -  Avalie as operações que estão sendo executadas (coluna Operation) e as estatísticas dessas operações: a quantidade de bytes (coluna Bytes) e o custo (coluna Cost) de cada passo ou simplesmente o tempo de resposta das operações (coluna Time). 

 

     Para tunar uma query, altere uma instrução SQL inúmeras vezes, analise o plano de execução de cada versão que foi alterada e opte por implementar aquela versão que consome menos recursos (colunas Bytes e Cost (%CPU)) ou que apresenta menor tempo de resposta (coluna Time). Verifique também se as operações que estão sendo executadas em cada passo do plano de execução são adequadas para a quantidade de dados a ser retornada. Se não forem adequadas, quando por exemplo no caso das estatísticas das tabelas acessadas não estarem atualizadas, é possível forçar uma operação que possa ser mais performática através do uso de hints.


CONCLUSÃO
  
   De um modo geral não é muito difícil analisar um plano de execução quando você conhece as operações  e estatísticas que podem estar contidas nele e as regras gerais para que você possa analisá-lo. 
  
   É importante entender que o Plano de Execução é uma estimativa e não o tempo real de execução da query, e que os seus passos e estatísticas podem variar se a instrução SQL for executada em ambientes diferentes (Ex.: Produção e Homologação).

   O problema maior ao analisar um plano de execução é que pode ser muito trabalhoso analisar o plano de uma instrução SQL longa e complexa  (você pode demorar muitas horas para entender o que ela irá fazer), portanto, para aumentar a sua produtividade, é importante entender os pontos principais que devem ser sempre analisados.