Arquivo mensais:novembro 2008

hit da instancia

Publicado em por
Responder

Através de um select simples podemos calcular todos os “Hit Ratios” mais importantes de uma instance Oracle. Para simplificar o “Hit Ratio” é o percentual de acerto em uma certa área da SGA. Por exemplo, 95,3% é o “Hit Ratios” do Database buffer cache, isso significa que 95,3% dos blocos de dados Oracle procurados estão sendo encontrado em memória.

O select :

SELECT ‘Library Cache’ Buffer , ROUND(100-SUM(reloads)/SUM(pins) * 100,2) hit
FROM v$librarycache
UNION ALL
SELECT ‘Dictionary Cache’, 100 – ROUND((SUM(getmisses)/SUM(gets))*100,2) hit
FROM v$rowcache
UNION ALL
SELECT ‘Buffer Cache’, ROUND((1 – (phy.value / (cur.value + con.value)))*100,2) hit
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.NAME = ‘db block gets’
AND con.NAME = ‘consistent gets’
AND phy.NAME = ‘physical reads’
UNION ALL
SELECT ‘Redo Log Buffer’, ROUND((100-(r.value*100)/e.value),2) hit
FROM v$sysstat r, v$sysstat e
WHERE r.NAME = ‘redo buffer allocation retries’
AND e.NAME = ‘redo entries’
UNION ALL
SELECT ‘Sort Area’, ROUND(((100*m.value)/(d.value + m.value)),2) hit
FROM v$statname a, v$sysstat d, v$sysstat m
WHERE a.statistic# = d.statistic#
AND d.NAME = ‘sorts (disk)’
AND m.NAME = ‘sorts (memory)’

O Resultado gerado no SQL*Plus:

fonte: http://oraclemais.blogspot.com/

recriar indices

Publicado em por
Responder

Eu ja tinha feito algumas linhas de codigo para o REBUILD do indice mas pesquisando encontrei um blog do Guilherme e achei interessante postar aqui a dica dele.

Essa package criada por mim possibilita a anlise de ínidices para saber se eles precisam sofrer o rebuild. Caso seja necessário, tem um procedimento que automatiza essa tarefa também.

CREATE
PACKAGE manutencao_indice
IS
PROCEDURE cria_tabela;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2);

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER);

PROCEDURE analisa_todos;

END; — Package spec
/

CREATE
PACKAGE BODY manutencao_indice

IS

PROCEDURE cria_tabela
IS
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE ‘||USER||’.DBA$INDEX_STATS
(DONO VARCHAR2(30),
NOME_INDICE VARCHAR2(30),
PCT_DEL NUMBER(5,2),
QUANDO DATE)’;
END;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
sql_stmt VARCHAR2(200);
CURSOR cur_stats
IS
SELECT (b.del_lf_rows/(DECODE(b.lf_rows,0,1)))*100 pct_delete
FROM index_stats b
WHERE b.name = indice;

BEGIN
EXECUTE IMMEDIATE ‘ANALYZE INDEX ‘||dono||’.’||indice||’ VALIDATE STRUCTURE’;
FOR rec_stats IN cur_stats LOOP
sql_stmt :=’INSERT INTO ‘||USER||’.dba$index_stats VALUES (:1, :2, :3, :4)’;
EXECUTE IMMEDIATE sql_stmt USING dono, indice, rec_stats.pct_delete, SYSDATE;
END LOOP;
COMMIT;
END;

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER)
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes
WHERE owner = dono;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE analisa_todos
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE rebuild_index(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE ‘ALTER INDEX ‘||dono||’.’||indice||’ REBUILD ONLINE’;
END;

END;
/

Fonte: http://oraclemais.blogspot.com/

bloqueio de linha do ORACLE

Publicado em por
Responder

SELECT os_user_name “OS User”,
process “OS Pid”,
oracle_username “Oracle User”,
l.sid “SID”,
DECODE(type,
‘MR’, ‘Media Recovery’,
‘RT’, ‘Redo Thread’,
‘UN’, ‘User Name’,
‘TX’, ‘Transaction’,
‘TM’, ‘DML’,
‘UL’, ‘PL/SQL User Lock’,
‘DX’, ‘Distributed Xaction’,
‘CF’, ‘Control File’,
‘IS’, ‘Instance State’,
‘FS’, ‘File Set’,
‘IR’, ‘Instance Recovery’,
‘ST’, ‘Disk Space Transaction’,
‘TS’, ‘Temp Segment’,
‘IV’, ‘Library Cache Invalidation’,
‘LS’, ‘Log Start or Switch’,
‘RW’, ‘Row Wait’,
‘SQ’, ‘Sequence Number’,
‘TE’, ‘Extend Table’,
‘TT’, ‘Temp Table’, type) “Lock Type”,
DECODE(lmode,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, lmode) “Lock Held”,
DECODE(request,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, request) “Lock Requested”,
DECODE(block,
0, ‘Not Blocking’,
1, ‘Blocking’,
2, ‘Global’, block) “Status”,
owner “Owner”,
object_name “Object name”
FROM v$locked_object lo, dba_objects do, v$lock l
WHERE lo.object_id = do.object_id
AND l.sid = lo.session_id

retirar ACENTOS

Publicado em por
Responder

Usando uma função simples disponível no banco de dados Oracle podemos retirar os acentos de uma coluna:

translate(txt,’âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç’,’AAAAAAAAEEEEIIOOOOOOUUUUCC’)

Exemplo:
system@> select txt from teste2;

TXT
—————————————-
âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç

system@> select translate(txt,’âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç’,’AAAAAAAAEEEEIIOOOOOOUUUUCC’) txt
2 from teste2;

TXT
—————————————-
AAAAAAAAEEEEIIOOOOOOUUUUCC

Ultimos comandos SQL executados

Publicado em por
1

Para visualizar os comandos executados no oracle, basta consultar:

SELECT /*+ INDEX (c) INDEX (q) USE_NL (q) */
q.piece, q.sql_text
FROM v$open_cursor c, v$sqltext_with_newlines q
WHERE c.sid = &SID
AND c.”ADDRESS” = q.”ADDRESS”
AND c.”HASH_VALUE” = q.”HASH_VALUE”
ORDER BY q.”ADDRESS”, q.”HASH_VALUE”, q.piece

Escrever no ALERT.LOG do oracle.

Publicado em por
Responder

Procedimento KSDWRT da dbms_system

Esse procedimento permite colocar uma mensagem em uma arquivo de destino que pode ser tanto o alert log ou um arquivo de trace.

Syntax
DBMS_SYSTEM.KSDWRT (
dest IN BINARY_INTEGER,
tst IN VARCHAR2);

Parâmetros
dest:
Onde vai escrever:
1 – Escreve no arquivo de trace.
2 – Escreve no alertlog.
3 – Escreve em ambos.

tst:
Mensagem que será escrita.

Exemplo

Escrever uma mensagem no alert log e no arquivo de trace

SQL> exec dbms_system.ksdwrt (3, ‘ ’);
SQL> exec dbms_system.ksdwrt (3, ‘— Comeco —’);
SQL> exec dbms_system.ksdddt;
SQL> exec dbms_system.ksdwrt (3, ‘ Teste da dbms_system.KSDWRT’);
SQL> exec dbms_system.ksdwrt (3, ‘— Fim —’);
SQL> exec dbms_system.ksdwrt (3, ‘ ’);

Saída no arquivo de trace:

*** 2006-08-10 08:01:15.000

— Comeco —
*** 2006-08-10 08:01:15.000
Teste da dbms_system.KSDWRT
— Fim —

Saída no alertlog:

Fri Aug 10 08:01:15.000

— Comeco —
Teste da dbms_system.KSDWRT
— Fim —

Quantidade de IO por device

Publicado em por
Responder

Este script que funciona para linux e windows calcula o IO dispendido por device.

SELECT nome.short_name “Device”,
sum(phyrds) “Leituras”,
sum(phywrts) “Escritas”,
sum((phywrts+phyrds)) “Total”,
s.segundos “Tempo(s)”,
sum(round((phywrts+phyrds)/(s.segundos),2)) “IO/s”
FROM
(SELECT /*+ RULE */
file# file_id,
phywrts,
phyrds,
readtim/10 readtim,
writetim/10 writetim,
0 contents
FROM v$filestat f
UNION ALL
SELECT /*+ RULE */
file# file_id,
phywrts,
phyrds,
readtim readtim,
writetim writetim,
1 contents
FROM v$tempstat) io,
(SELECT /*+ RULE */
ts#,
d.file# file_id,
d.name file_name,
SUBSTR(d.name, 1, INSTR(d.name, ”, 2)+INSTR(d.name,’/’,2)-1) short_name
FROM v$datafile d
UNION ALL
SELECT /*+ RULE */
ts#,
d.file# + p.value file_id,
d.name file_name,
SUBSTR(d.name, 1, INSTR(d.name, ”, 2)+INSTR(d.name,’/’,2)-1) short_name
FROM v$tempfile d, v$parameter p
WHERE d.blocks >0
AND p.name = ‘db_files’) nome,
(SELECT round(((sysdate-startup_time)*86400)) segundos
from v$instance) s
WHERE io.file_id = nome.file_id
GROUP BY nome.short_name, s.segundos

Gerar DDL de todas tablespaces

Publicado em por
Responder

set pagesize 0
set feedback off
set linesize 1000
spool cre_tbs.sql
select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N’,null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name;
spool off
set pagesize 20
set feedback on
set linesize 150