Scripts Práticos – Oracle: Consulta Shared Pool, Rebuild de Índices e Ajuste de Tamanhos de INITIAL e NEXT

Scripts Práticos – Oracle

Salve Galera…

Ontem eu estava lendo o blog do Flavio Isidoro sobre indices e ele postou um script de rebuild que eu já utilizo aqui.. ai pensei que poderia também compartilhar os meus com o pessoal..

Então ta ai.. Estou postando 1 script de consulta SHARED_POOL das intâncias, porém só funciona para versões abaixo do 10g, já que no 10G esta “memória” é “auto-gerenciável” de acordo com sua SGA.. mas vamos la..

Ahh… estes scripts são todos para serem utilizados no SQL*PLUS..

Obs.: Lilian.. ta vendo.. eu falei que estava utilizando na maioria das vezes o SQL*PLUS e não ferramentas gráficas.. huahauha..!!!

Script 1 – Consulta Shared Pool

COL TAMANHO_SHARED_POOL           FORMAT 999,990.00  HEADING "TAMANHO_SHARED_POOL(MB)"
COL PERCENTAGEM_USADO_SHARED_POOL FORMAT 990.00      HEADING "%USADO"
COL USADO_SHARED_POOL             FORMAT 999,990.00  HEADING "USADO_SHARED_POOL(MB)"
COL LIVRE_SHARED_POOL             FORMAT 999,990.00  HEADING "LIVRE_SHARED_POOL(MB)"  

 set heading off
set feedback off

SET PAGESIZE 100 LINES 130 HEADING on FEED OFF NULL '' break on report ON contents SKIP 1

SELECT
MAX(B.VALUE)/(1024*1024) TAMANHO_SHARED_POOL,
SUM(A.BYTES)/(1024*1024) USADO_SHARED_POOL,
(MAX(B.VALUE)/(1024*1024)) - (SUM(A.BYTES)/(1024*1024)) LIVRE_SHARED_POOL,
((SUM(A.BYTES)/(1024*1024))/(MAX(B.VALUE)/(1024*1024)))*100 PERCENTAGEM_USADO_SHARED_POOL
FROM V$SGASTAT A, V$PARAMETER B
WHERE A.POOL= 'shared pool'
AND A.NAME NOT IN ('free memory')
AND B.NAME='shared_pool_size'
/

 set heading on
set feedback on
PROMPT

No scripts acima, eu já pré-formatei algumas colunas, para melhorar a visualização dentro do Sql*Plus.. assim evitando que haja quebra de linhas quando eu rodar o script…

Script 2 – Rebuild de Indice para outra Tablespace

Obs2.: Houve um assunto no forum falando sobre a criação de tablespaces para cada “situação”, uma para dados, outra para indices, outra para tmp.. etc.. Bom.. eu sou adepto desta prática, criar uma tablespace para armazenar cada grupo de dados.. Pode ser por performance, mas eu prefiro por estética e melhor pratica de controle, pois assim consigo mensurar melhor a ultilização de uma determinada tablespace…

set heading off
set feedback off
set time off
set timing off
set lines 100
set pages 3000
spool C:\SCRIPTS\temp\Rebuild_indexes_tablespaces.sql
SELECT 'alter index '|| a.owner ||'.'||a.index_name || ' rebuild tablespace TBS'||UPPER(TRIM(d.NAME))||'IND;' FROM ALL_INDEXES a, v$database d
WHERE a.OWNER = '&OWNER'
AND TABLESPACE_NAME = 'TBS'||UPPER(TRIM(d.NAME))||'DAT'
/
spool off
set heading on
set feedback on
set time on
set timing on
@C:\SCRIPTS\temp\Rebuild_indexes_tablespaces.sql

Neste script acima eu fiz para ter praticidade, eu costumo determinar o nome das tablespaces colocando TBS+INSTANCIA+GRUPO, ou seja.. TBSBANCODAT (tablespace de dados puros), TBSBANCOIND (Tablespace de Indices) e TBSBANCOTMP (tablespace Temporary).. por este motivo existe a concatenação com a view V$database..
Quando este script for executado, vc vai precisar apenas passar o OWNER que vc quer fazer o rebuild, não precisará editar o script para cada OWNER que vc for fazer..

Script 3 – Rebuild de Indice para ajustar tamanho de INITIAL e NEXT.

set heading off
set feedback off
set time off
set timing off
set lines 100
set pages 3000
spool C:\SCRIPTS\temp\Rebuild_indexes_storage_initial.sql
SELECT 'ALTER INDEX ' || a.OWNER || '.' || INDEX_NAME || ' REBUILD STORAGE (INITIAL 128K NEXT 128K);' FROM ALL_INDEXES
WHERE OWNER = '&OWNER'
/
spool off
set heading on
set feedback on
set time on
set timing on
@C:\SCRIPTS\temp\Rebuild_indexes_storage_initial.sql

Bom, já peguei varios casos onde o indice é criado pelo desenvolvedor e por muitas vezes sem parametros de intial e next corretos.. indices com initial de 80MB e next de 48k.. então para ajustar isto, eu decidi adotar 128k para o initial e next de todos os indices.. Sei que existem indices que precisam ser reavaliados, mas 99% dos que utilizo eu padronizei com estes tamanhos por ser o mais adequado a situação deles… tenho indices também com 1mb de initial e 1mb de next.. mas depende de cada caso.. que deve ser muito bem analisado…

Script 4 – Analyze Tables…

set heading off
set feedback off
set time off
set timing off
set lines 100
set pages 3000
spool C:\SCRIPTS\temp\Analyze_Tables.sql
SELECT 'ANALYZE TABLE '|| OWNER ||'.'||TABLE_NAME || ' COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;' FROM ALL_TABLES
WHERE NUM_ROWS IS NOT NULL
AND OWNER = '&OWNER'
/
spool off
set heading on
set feedback on
set time on
set timing on
@C:\SCRIPTS\temp\Analyze_Tables.sql

Este é um script de Analyze.. simples.. onde também vai solicitar qual o Owner que vc gostaria de fazer o Analyze..

MAIS 2 SCRIPTS QUE TENHO A CERTEZA QUE SERÃO DE BOM PROVEITO…

Script 5 – Move Tables para outra Tablespace…

set heading off
set feedback off
set time off
set timing off
set lines 100
set pages 3000
spool C:\SCRIPTS\temp\Move_Tables_tablespaces.sql
select 'ALTER TABLE '||owner||'."'||table_name||'" move tablespace ' || '&TABLESPACEDESTINO'|| ';' from dba_tables
WHERE owner = '&OWNER'
AND TABLESPACE_NAME = '&TABLESPACEORIGEM'
/
spool off
set heading on
set feedback on
set time on
set timing on
@C:\SCRIPTS\temp\Move_Tables_tablespaces.sql

O script de Move Table ao ser executado irá solicitar a Tablespace de Destino das tabelas, o Owner da tabela e a Tablespace de Origem das tabelas..

Script 6 – Move LOB para outra Tablespace…

set heading off
set feedback off
set time off
set timing off
set lines 100
set pages 3000
spool C:\SCRIPTS\temp\Move_Lob_tablespaces.sql


SELECT 'ALTER TABLE '|| OWNER ||'.'|| TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ') store as (TABLESPACE &TABLESPACEDESTINO);'
from dba_tab_columns
WHERE owner='&OWNER'
and data_type like '%LOB'
/
spool off
set heading on
set feedback on
set time on
set timing on
@C:\SCRIPTS\temp\Move_Lob_tablespaces.sql

O script 6 é um complemento do Script 5, mas somente quando existem campos do tipo BLOB e CLOB, pois por muitas vezes temos types CLOB e BLOB que são referenciados a uma determinada Tablespace e ao mover as Tabelas, também temos que mover estes types..

Estes scripts podem ser adaptados conforme a necessidade de cada um..

Bom pessoal, é isto, conforme eu for precisando de mais scripts eu vou disponibilizando aqui para vocês… Espero que sejam uteis para vocês assim como estão sendo para mim…

Abraços….