Dicas ORACLE DBA

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; SE

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 | | 1

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 cha

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 re

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

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');

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.eve

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

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

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.p

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;

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 /

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;

-- 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;

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',

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';

-- 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

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

-- 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'

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; /