Pular para o conteúdo principal

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'

Comentários

  1. A partir do 9 já tem gerenciamento automático, UNDO_RETENTION é somente uma dica, pois o oracle irá dimensionar undo sozinho, salvo vc deixe de usar o gerenciamento automático.
    Execute as cargas máximas em seu ambiente que o oracle irá dimensionar baseado em estatísticas o undo e use advisors.
    Rodar estas consultas em qualquer horário não servirá de nada.

    ResponderExcluir

Postar um comentário

Postagens mais visitadas deste blog

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

Webcenter Content - Jython to Checkin Content with Attachments (CHECKIN_NEW_WITH_RENDITIONS)

In the last few days, i had to load a content that, by its requirements, needs to attach files to the content. My first alternative would be Batch Loading, however for the bacth loder file it was not possible because it does not actually support the rendition service. With the next option, i tried to use the IdcCommand, but still had many problems, such as errors below: sample hda file: [oracle @ hostname bin] $ cat idctst.txt @Properties LocalData IdcService = EDIT_RENDITIONS dID = 37020 renditionKeys = addRendition0 addRendition0.name = test543 addRendition0.action = edit addRendition0.file = test.txt addRendition0.file: path = / u01 / oracle / domains / content_domain / ucm / cs / vault / ~ temp / test.txt @end << EOD >> Executing: [oracle @ hostname bin] $ ./IdcCommand -f idctst.txt -u sysadmin -l log.log -c server Error: Java HotSpot (TM) 64-Bit Server VM warning: Using the ParNew young collector with the Serial old collector is deprecated and w