Pular para o conteúdo principal

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

Comentários

Postar um comentário

Postagens mais visitadas deste blog

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

OHS 12c noprompt password when stop/start component

cd $OHS_DOMAIN/bin $ ./stopComponent.sh ohs1 Stopping System Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands Reading domain from /u01/oracle/domains/ohs_domain Please enter your password : password Connecting to Node Manager ... Successfully Connected to Node Manager. Killing server ohs1 ... Successfully killed server ohs1 Successfully disconnected from Node Manager. Exiting WebLogic Scripting Tool. Done $ OHS_HOME/oracle_common/common/bin $ wlst.sh readDomain('/u01/oracle/domains/ohs_domain') ohs_domain>cd('/SecurityConfiguration/ohs_domain') set('NodeManagerUsername','weblogic') set('NodeManagerPasswordEncrypted','password') updateDomain() closeDomain() exit() $ ./startComponent.sh ohs1  Starting system Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welco...
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