DIAG_TUNE_SCRIPTS.sql

----------------------------------
-- SCRIPT  verifica_novo.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------

To use these scripts to assist with the diagnosis of performance problems, 
please follow these steps:

    1.  Open an editor from SQL.

    2.  Copy this entire note from after BEGIN> to before END> and paste the 
        contents into your editor.  

    3.  Save the file as <directory path>/objects.sql 
        (or directory path \objects.sql for DOS or WINDOWS)
 
    4.  Open a session of SQL*Plus.

    5.  While at the SQL> prompt, type the following:

        SQL>spool c:\objects_perf  <or your desired path and tar number>
        This will create and record all of the actions taken by your 
        SQL*Plus session.  The log file will have an extension of .lst.
 
    6.  At the SQL prompt, type @<directory path>/objects.sql 
        (or @<directory patch?\objects.sql if in DOS or WINDOWS).

    7.  After you are completely through recording to this log file, at 
        the SQL prompt,  type SQL>spool off.

    8.  Review the file and if necessary send the .lst file to Oracle Support.


The scripts are as follows:

BEGIN>

      ttitle 'DATA DICTIONARY CACHE RATIO REPORT' skip

      column "Data Dict. Gets"   format 999,999,999  
      column "Data Dict. cache misses" format 999,999,999  
      column dictcache format 999.99 heading 'Dictionary Cache Ratio %'  
 
      select   sum(gets) "Data Dict. Gets",  
               sum(getmisses) "Data Dict. cache misses",  
               sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache  
      from     v$rowcache;


      ttitle 'COMPUTE SUM OF VALUE kbval ON REPORT' skip

      column name     format a20 heading "SGA Segment"
      column value    format 99,999,990  heading "Size|(Bytes)"
      column kbval    format 99,990.9 heading "Size|(Kb)"
      break on report
      select  name,
              value,
              round(value/1024,1) kbval
      from    v$sga;


          col f1 format a55 heading 'Redo Log File Name'
          col f2 format 9999 heading 'Group'
          col f3 format a10 heading 'Status'

          ttitle  'Redo Log File Names Report' skip
          break on f2 skip

          select  GROUP# f2,
                  STATUS f3,
                  MEMBER f1
          from    v$logfile
          order by GROUP#,MEMBER;


      ttitle 'REDO CONTENTION REPORT' skip
      column value format 999,999,999  
      select substr(name,1,30) Name,  
             value  
      from   v$sysstat 
      where  name = 'redo log space requests';


      ttitle 'GET WAIT RATIO ROLLBACK REPORT'
      column "Ratio" format 999.99999999 
      column name format A15
      column "PERCENT" 
      select  name, 
              waits, 
              gets, 
              100-(waits/gets) "Ratio",
              (waits/gets)*100 "PERCENT"
      from    v$rollstat a, v$rollname b  
      where   a.usn = b.usn;  


      ttitle 'ROLLBACK GENERAL INFORMATION' skip 
      select   rssize,
               optsize,
               hwmsize,
               shrinks,
               wraps,  
               extends,
               aveactive  
      from   v$rollstat  
      order  by rownum;


      ttitle 'Sorts Disk/Memory' skip
 
      select  substr(name,1,30) "Statistic Name",
               value
      from    v$sysstat
      where   name in ('sorts (memory)','sorts (disk)');




      ttitle 'High water marks report' skip
      col event format a37 heading 'Event'  
      col total_waits format 99999999 heading 'Total|Waits'  
      col time_waited format 9999999999 heading 'Time Wait|In Hndrds'  
      col total_timeouts format 999999 heading 'Timeout'  
      col average_wait heading 'Average|Time' format 999999.999  
 
      select *  
      from   v$system_event;


      column pct heading "Hit Ratio (%)" format 999.9

      ttitle  'Buffer Cache Checks - Goal, above 95%' skip

      select  ((1- (sum(decode(a.name,'physical reads',value,0)))/
               (sum(decode(a.name,'db block gets',value,0)) +
                sum(decode(a.name,'consistent gets',value,0)))) * 100) "PERCENT"
      from    v$sysstat a;



      column sum_pins format 999,999,999  
      column sum_reloads format 999,999,999  
      column hit_ratio format 999.99999
      ttitle 'PINS and Library Cache' skip
       
      select 'PINS - # of times an item in the library cache was executed - '||
              sum(pins) sum_pins, 
              'RELOADS - # of library cache misses on execution steps - '||
              sum(reloads) sum_reloads,  
              'Pin hit ratio should be close to 1.0  - '||
              ROUND((sum(reloads)/sum(pins)),6) hit_ratio
      from   v$librarycache;  


      ttitle 'FREE MEMORY IN SHARED POOL REPORT' skip
      select name, 
              (bytes/1024/1024) "Free Memory in MB" 
      from   v$sgastat
      where  name = 'free memory';




      column name format A25
      ttitle 'OBJECTS IN CACHE REPORT' skip
      select name,
             sharable_mem
      from   v$db_object_cache 
      where sharable_mem > 10000
      and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');



      ttitle 'RELOADS-library cache misses' skip

      column libcache format 99.99 heading 'Library Cache Miss Ratio (%)'

      select  sum(pins) "Executions",
              sum(reloads) "Reloads While Executing",
              sum(reloads)/sum(pins) *100 libcache
      from    v$librarycache;


      column name heading "Latch Type" format a25
      column pct_miss heading "Misses/Gets (%)" format 999.99999
      column pct_immed heading "Immediate Misses/Gets (%)" format 999.99999

      ttitle  'Latch Contention Analysis Report' skip

      select  n.name,
               misses*100/(gets+1) pct_miss,
               immediate_misses*100/(immediate_gets+1) pct_immed
      from    v$latchname n,v$latch l
      where   n.latch# = l.latch#
      and     n.name in('%cache bugffer%','%protect%');


      ttitle 'Buffer Contention Specific - PARAMETERS PRINTED IF > 0' skip - 


      select  class,count
      from    v$waitstat
      where   class in ('data blocks','segment header',
                        'undo header','undo block');



      ttitle 'Packages in dba_object_size NOT owned by SYS or SYSTEM' skip
 
      column total_bytes format 9999999 heading 'Total|Bytes'
      column "OBJECT" format A25
      column type format A15

      select    owner || '.' || name OBJECT,
                 type, 
                 to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",
                 loads, 
                 executions execs,
                 kept
      from      v$db_object_cache
      where     type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
      and       owner not in ('SYS','SYSTEM')
      and       kept = 'NO'
      and       sharable_mem > 100000
      order by owner, name;

END>


References
----------

Related notes for MRP Performance Troubleshooting Self Study:
  
    *  Oracle Application Performance Tuning and Troubleshooting Guide
       Note 100956.1
    *  Database Objects Note 100960.1
    *  RDBMS  Note 100964.1
    *  Network Note 101007.1
    *  Platform Note 100991.1
    *  MRP Application  Note 101015.1


----------------------------------
-- FIM DO SCRIPT  -- 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em ORACLE 11gR2, PLSQL SCRIPTS e marcado , , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s