Pular para o conteúdo principal

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

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Webcenter Content - Cache Configuration for JdbcFileStore configuration

The configuration values below, control the temporarily cached files keep in FileCache table. FsCacheThreshold Specifies the maximum cache size, in megabytes (default=100) Once the threshold is met, Content Server starts deleting files that are older than the minimum age, as specified by the FsMinimumFileCacheAge parameter FsMaximumFileCacheAge The age at which files are deleted, expressed in days. The default is 365 days FsMinimumFileCacheAge The minimum age at which cached files can be deleted This parameter is used in conjunction with the FsCacheThreshold parameter to determine when to delete cached files FsCleanUpCacheDuringIndexing This parameter activates vault cache cleaning, while FsCacheThreshold, FsMaximumFileCacheAge and FsMinimumFileCacheAge are controling its behaviour.  Expect, when this is enabled on a system that currently has a large number of files in the FileCache table, the first cycle will take a long time to complete. How to Confi...
Postar um comentário
Leia mais

Assign Backup Policy for Boot and Block Volumes - OCI

To get Availability Domain oci iam availability-domain list  #!/bin/bash OCI_AD="hQsi:SA-SAOPAULO-1-AD-1" OCI_COMPARTMENT="ocid1.compartment.oc1..aaaaaaaavgyjpoffz5qliieawamjaksfb6juxejurtduclqjkvv66ookcxja" #Get Silver policy ocid funct_get_policy(){ OCI_POL_OCID=$(oci bv volume-backup-policy list | jq -r '.data[] | select(."display-name"|contains("silver"))' | jq -r '.id') } funct_boot_vol (){ #Create a file with all boot volume ocid oci bv boot-volume list --availability-domain ${OCI_AD} -c  ${OCI_COMPARTMENT} | jq -r '.data[].id' > boot-vol.txt while IFS="" read -r p || [ -n "$p" ] do   printf 'Seeting backup policy for %s\n' "$p"   oci bv volume-backup-policy-assignment create  --asset-id  $p  --policy-id ${OCI_POL_OCID} done < boot-vol.txt } funct_block_vol (){ #Create a file with all block volume ocid oci bv volume list  -c ${OCI_COMPARTMENT} | jq -r '.data[].id' ...
Postar um comentário
Leia mais

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' ...
2 comentários
Leia mais