quarta-feira, 29 de abril de 2009

Verificação de Espaço

Consultas básicas para verificação de espaços da sua Instância:

Database size

SELECT 'Database Size' "*****"
,round(sum(round(sum(nvl(fs.bytes/1024/1024,0)))) /
sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100, 0) "%Free"
,round(sum(round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/
1024,0)))) / sum(round(sum(nvl(fs.bytes/1024/1024,0))) +
round(df.bytes/1024/1024 - sum(nvl(fs.bytes/1024/1024,0)))) * 100,
0) "%Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0)))) "Mb Free"
,sum(round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0)))) "Mb Used"
,sum(round(sum(nvl(fs.bytes/1024/1024,0))) + round(df.bytes/1024/
1024
- sum(nvl(fs.bytes/1024/1024,0)))) "Size"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Tablespace size

COL Tablespace FORMAT a30;
SELECT df.tablespace_name "Tablespace"
,round((sum(nvl(fs.bytes,0))/ (df.bytes)) * 100) "%Free"
,round(((df.bytes - sum(nvl(fs.bytes,0)))
/ (df.bytes) ) * 100) "%Used"
,round(sum(nvl(fs.bytes/1024/1024,0))) "Mb Free"
,round(df.bytes/1024/1024
- sum(nvl(fs.bytes/1024/1024,0))) "Mb Used"
FROM dba_free_space fs, dba_data_files df
WHERE fs.file_id(+) = df.file_id
GROUP BY df.tablespace_name, df.file_id, df.bytes,
df.autoextensible
ORDER BY df.file_id;

Extent size

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
,ROUND(SUM(bytes)/1024/1024) "Mb", ROUND(SUM(bytes)/1024) "Kb"
,SUM(bytes) "Bytes", SUM(blocks) "Blocks"
FROM dba_extents
WHERE owner = &OWNER AND segment_type IN ('TABLE','INDEX')
GROUP BY segment_name, segment_type
ORDER BY segment_name, segment_type DESC;

Segment size

COL Object FORMAT a24;
COL Type FORMAT a5;
SELECT segment_name "Object", segment_type "Type"
,ROUND(bytes/1024/1024) "Mb", ROUND(bytes/1024) "Kb"
,bytes "Bytes", blocks "Blocks"
FROM dba_segments WHERE owner = &OWNER
AND segment_type IN ('TABLE','INDEX')
ORDER BY segment_name, segment_type DESC;

Index SKIP SCAN???

Index Skip Scan nada mais é que o "split" logico de um indice composto, ou seja, se sua clausula WHERE não tiver todos os campos do indice composto, ele é dividido em menores sub-indices

Exemplo:
--------
SQL> create table teste(a number,b number);

Table created.

SQL> begin
2 for i in 1..1000
3 loop
4 insert into teste values(i, 56);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create index idx_teste on teste(a,b);

Index created.

SQL> analyze table teste compute statistics;

Table analyzed.

SQL> explain plan for select /*+ index_ss(teste idx_teste) */ * from teste where b=56;

Explained.

SQL> start ?\rdbms\admin\utlxpls.sql

--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1K | 4K| 1001 | | |
| INDEX SKIP SCAN |teste_idx | 1K | 4K| 1001 | | |
--------------------------------------------------------------------------------

terça-feira, 28 de abril de 2009

Linhas Migradas

Esse é um assunto que futuramente vou abordar com mais detalhes, porem vou descrever brevemente o que são linhas migradas e algumas maneiras de identificar.

Linhas migradas afetam sistemas OLTP que por sua vez utilzam buscas indexadas ou mesmo full scan gerando uma quantidade extra de I/O ou mesmo locks de contenção. Linhas migradas são tipicamente causadas por instruções DML do tipo UPDATE e INSERT.

Abaixo os passos para identificar:

cd $ORACLE_HOME/rdbms/admin
sqlplus denilson/password
@utlchain.sql

ou criar a tabela:

create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);

Analisando todas as suas tabelas conforme exemplo abaixo:
ANALYZE TABLE TESTE LIST CHAINED ROWS INTO CHAINED_ROWS;

Verificando as tabelas com linhas migradas:

SELECT owner_name,
table_name,
count(head_rowid) row_count
FROM chained_rows
GROUP BY owner_name,table_name
/

OWNER_NAME TABLE_NAME ROW_COUNT
------------------------------ ------------------------------ ---------------
DENILSON TESTE 10

Mostra os TOP latchs

Latch é uma proteção dos buffers de memória semelhante aos locks das linhas das tabelas. Existe numerosos tipos de Latches para todos os diferentes tipos de buffer de área de memória.

Não é possível fazer tunning em Latches. Problemas com Latches são indicações de que outros problemas estão ocorrendo e pode auxiliar-nos à nos concentrar no verdadeiro problema deperformance que pode estar acontecendo.

Latches missed, spins e sleeps

O que é um Latch missed? Quando um latch é requisitado e a sessão de um buffer requisitado é fechado ou travado por outro processo, então, um latch livre é gerado. Entretanto, a segunda requisição para um latch, que o qual já foi retido por outro processo, irá esperar o requerido latch tornar-se livre para uso. O lock da linha de uma tabela simplesmente espera a liberação da linha tentando constantemente um lock na mesma. Ao contrário, um latch missed irá gerar um dos dois tipos de ação:

1) Coloca o latch em immediate mode que fará com que o processo fique requisitando um latch para tomar uma ação alternativa;

2) Coloca o latch requisitado em willing-to-wait mode e isso faz com que ele fique tentando repetidamente e spin (gira).

Quando o latch gira um determinado número de vezes, ele passa à sleep (dormir ) por um período de tempo. Spinning latches pode consumir uma grande quantidade de tempo de CPU. Quando ocorre um latch spin, ele repetidamente requisita um latch e isso pode afetar drasticamente se muitos latches spins estão ocorrendo ao mesmo tempo. Mas vamos nos aprofundar um pouco mais nesse tópico lá na frente pois precisamos de algumas outras informações.

Abaixo a query pra mostrar os TOP LATCHS:

SET LINESIZE 200

SELECT l.latch#,
l.name,
l.gets,
l.misses,
l.sleeps,
l.immediate_gets,
l.immediate_misses,
l.spin_gets
FROM v$latch l
WHERE l.misses > 0
ORDER BY l.misses DESC;

FK´s não Indexadas

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF

SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT '1'
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;


PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

Verificar HIT_RATIO - Taxa de acerto de Buffer

select
100 -
100 *
(round ((sum (decode (name, 'physical reads',
value, 0))
-
sum (decode (name, 'physical reads direct',
value, 0)) -
sum (decode (name,
'physical reads direct (lob)',
value, 0))) /
(sum (decode (name, 'session logical reads',
value, 1))
),3)) hit_ratio
from
sys.v_$sysstat
where
name in
('session logical reads',
'physical reads direct (lob)',
'physical reads',
'physical reads direct');

Calcular optimizer_index_cost_adj

O parâmetro optimizer_index_cost_adj foi criado para permitir alterar os custos relativos entre full-scan e operações índexadas.

Abaixo uma consulta para cacular o valor ideal para instance.

col c1 heading 'Media Waits para|Full Scan Read I/O' format 9999.999
col c2 heading 'Media Waits para|Index Read I/O' format 9999.999
col c3 heading 'Porcentagem de| I/O Waits|para Full Scans' format 9.99
col c4 heading 'Porcentagem de| I/O Waits|para Index Scans' format 9.99
col c5 heading 'Valor|inicial|para|optimizer|index|cost|adj' format 999

select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;
Por tratar-se de um ajuste mais fino da sua instance, deve verificar também os parametrôs optimizer_index_caching e db_file_multiblock_read_count, um tutorial mais detalhado você pode consultar http://www.dba-oracle.com/oracle_tips_cost_adj.htm

segunda-feira, 27 de abril de 2009

Instrução executando na sessão com número de vezes q quantidade de buffer gets

select executions,disk_reads,buffer_gets, SQL_TEXT from v$sqlarea where address =
&Sql_hash_value
and HASH_VALUE = &Sql_hash_value

Verificar leituras logicas e físicas da sessão

SELECT sess.SID, sess.username, sess.osuser, sess.program,
sess.machine, s.statistic#, NAME, VALUE
FROM v$sesstat s, v$statname n, v$session sess
WHERE n.statistic# = s.statistic#
AND sess.SID = s.SID
AND n.statistic# IN (9, 42)
and sess.sid =&sid

Replicação OnLine

Esse eu utilizo com criação de chave pública entre servidores usando SSH - consulte
http://dicasoracledba.blogspot.com/2009/11/criando-chaves-publicas-ssh.html

col comando format a300
set linesize 300
set serveroutput on size 100000

spool script.sql

col comando format a300
set linesize 300
set serveroutput on size 100000
set trimspool on

spool script.sql

begin
for rTS in (select tablespace_name from dba_tablespaces) loop
dbms_output.put_line('alter tablespace '|| rTS.tablespace_name ||' begin backup;');
for rDados in (select '!scp -B '|| file_name ||' oracle@10.11.246.131:'||file_name comando from dba_data_files where tablespace_name=rTS.tablespace_name ) loop
dbms_output.put_line(rDados.comando);
end loop;
dbms_output.put_line('alter tablespace '|| rTS.tablespace_name ||' end backup;');
end loop;
dbms_output.put_line('ALTER DATABASE BACKUP CONTROLFILE TO ''/tmp/control_bkp.ctl';'');
dbms_output.put_line('!scp -B /tmp/control_bkp.ctl' oracle@10.11.246.131:/tmp/control_bkp.ctl');
end;
/
spool off;
spool off;

Identificar sessão no banco pelo REQUEST_ID (Concurrents do EBS)

select oracle_process_id, sid,request_id from v$session s, v$process p, fnd_concurrent_requests r
where s.PADDR = p.addr
and r.request_id = &req_id
and r.oracle_process_id = p.SPID;

EXP-00003: no storage definition found for segment(%lu, %lu)

Bug normalmente ocorre no 9.2.0.5 até 10.1.0.4.0 (doc. id 274076.1)

Recriar a view acrescentando o UNION conforme abaixo:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * from sys.exu9tneb
/

Identificar locks entre sessões

select h.session_id Sessao_Travadora, ub.osuser Usuario_Travador ,
w.session_id Sessao_Esperando, uw.osuser Usuario_Esperando,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from dba_locks w, dba_locks h, v$session ub, v$session uw
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and h.session_id = ub.sid
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2
and w.session_id = uw.sid;

Verificar numeros de blocos usados e linhas para uma instrução DML (INSERT, DELETE, UPDATE) de uma sessão

-- V$TRANSACTION.USED_UBLK - Mostra o número de blocos em rollback
-- V$TRANSACTION.USED_REC - Registros Processados


select s.username, s.sid, rn.name, rs.extents
,rs.status, t.used_ublk, t.used_urec
,do.object_name
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
,v$locked_object lo
,dba_objects do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id
and s.sid = 524;

Identificar Objetos Lockados

COLUMN sid FORMAT 999 HEADING "Sess|ID "
COLUMN object_name FORMAT A17 HEADING "OBJ Name or|Trans ID" TRUNC
COLUMN terminal FORMAT A8 TRUNC
COLUMN osuser FORMAT A10 HEADING "OP Sys|User ID"
COLUMN username FORMAT A8

SELECT B.sid, C.serial#, C.username, C.osuser, C.terminal,
DECODE(B.id2, 0, A.object_name, 'Trans-' || TO_CHAR(B.id1)) object_name,
B.type,
DECODE(B.lmode,
0, '--Waiting--',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'Share Row Excl',
6, 'Exclusive', 'Other') "Lock Mode",
DECODE(B.request,
0, ' ',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'Share Row Excl',
6, 'Exclusive', 'Other') "Req Mode"
FROM dba_objects A, v$lock B, v$session C
WHERE (A.object_id(+) = B.id1 AND
B.sid = C.sid AND
C.username IS NOT NULL)
ORDER BY B.sid, B.id2;

Mostra a utilização dos segmentos de UNDO e as sessões que estão utilizando

select u.name, s.bytes tamanho, u.ocupado, trunc(u.ocupado * 100 /
s.bytes,2) pct_usado
from (select n.name, sum(USED_UBLK*8*1024) ocupado
from v$transaction t, v$rollname n
where t.XIDUSN = n.usn
group by n.name
)u,
dba_segments s
where s.segment_name = u.name;

select r.name, s.osuser, s.username, s.sid
from v$lock l, v$rollname r, v$session s
where l.sid=s.sid
and trunc(l.id1/65536) = r.usn
and l.type = 'TX';

VERIFICAR USUARIOS LOGADOS NO EBS

-- Executar com schema APPS, retorna os usuários do EBS logados e seus respectivos forms e modulos
SELECT
TO_CHAR(vs.logon_time, 'DD-MON-YY HH24:MI:SS') logon_time,
TRUNC(vp.spid) SPID,
vs.sid,
vs.serial#,
vs.status,
fu.description NOME_USUARIO,
fu.user_name USUARIO,
SUBSTR(DECODE(vs.module,'FNDSCSGN','MainMenu',fft.user_form_name),1,40) FORM_NAME,
SUBSTR(vs.module, 1, 10) module,
fs.effective_date date_track_date,
vs.action action_responsibility,
vs.status
,vs.program
,vs.osuser
,vs.username dbuser
,SUBSTR(vs.machine, 1, 10) server,
vs.resource_consumer_group,
TO_CHAR(fl.session_number) all_time_PUI_logons
FROM v$session vs,
v$process vp,
applsys.fnd_logins fl,
applsys.fnd_user fu,
applsys.fnd_sessions fs,
applsys.fnd_form ff,
applsys.fnd_form_tl fft
WHERE
vp.addr = vs.paddr
AND fl.spid = vs.process
AND fl.process_spid = vp.spid
AND fl.user_id = fu.user_id
AND vs.audsid = fs.session_id(+)
AND vs.module IS NOT NULL
AND ff.form_name(+) = vs.module
AND ff.form_id = fft.form_id(+)
AND ff.application_id = fft.application_id(+)
ORDER BY vs.logon_time;

CURSOR PARA MOVER TABELAS E FAZER REBUILD DOS SEUS RESPECTIVOS INDICES

set linesize 300
set serveroutput on size 100000
set feedback off

spool rebuild.sql
begin
for rTabelas in (
SELECT owner, table_name, tablespace_name
FROM DBA_TABLES
) loop
dbms_output.put_line('alter table '|| rTabelas.owner ||'.'|| rTabelas.table_name ||' move tablespace '||rTabelas.tablespace_name||';');
for rIndex in (select index_name,tablespace_name,owner
from dba_indexes
where table_name = rTabelas.table_name) loop
dbms_output.put_line('alter index '|| rIndex.owner ||'.'|| rIndex.index_name ||' rebuild tablespace '||rIndex.tablespace_name||';');
end loop;
end loop;
end;
/
spool off

Configurar Tamanho de UNDO e tempo de RETENÇÃO (UNDO_RETENTION)

-- Tamanho Undo Atual
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

-- Blocos de UNDO por Seg.
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;


--Tamanho do Bloco
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';


-- Select calcula UNDO RETENTION
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'



-- Select Calcula tamanho UNDO ideal
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'

Bloco PL/SQL para dropar objetos do schema

set serveroutput on size 99999999;
declare
vCursor number;
vComando varchar2(2000);
vError number;
vUser varchar2(200);
begin
select user into vUser from dual;
if (vUser = 'APPS' or vUser = 'SYSTEM' or vUSer = 'SYS') then
dbms_output.put_line('SCHEMA NÃO PERMITIDO');
else
dbms_output.put_line('DROPANDO OBJETOS');
vCursor := dbms_sql.open_cursor;
for rObjetos in (select *
from user_objects
where object_type in
('TABLE','FUNCTION','PROCEDURE','SYNONYM','VIEW','PACKAGE','SEQUENCE')
) loop
vComando := 'Drop '||rObjetos.object_type|| ' ' || rObjetos.object_name;
if rObjetos.object_type = 'TABLE' then
vComando := vComando || ' cascade constraint';
end if;
dbms_sql.parse(vCursor, vComando, dbms_sql.native);
vError := dbms_sql.execute(vCursor);
end loop;
dbms_sql.close_cursor(vCursor);
end if;
end;
/