v$sql_plan.sql

----------------------------------
-- SCRIPT  v$sql_plan.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
---
set echo off
--- ====================================================================
--- ARQUIVO...: v$sql_plan.sql
--- FINALIDADE: Mostra o plano de execucao de um SQL na Shared SQL Area
--- AUTOR.....: Oracle Metalink: Note 260942.1
--- PARAMETROS: 1 = hash_value de um SQL
--- LIMITACOES: Somente funciona em Oracle 9.2 e superior
--- ALTERACOES: 28-FEB-2006  Metalink   Last review
--- ====================================================================
---


-----------------------------------------------------------------
--- Ajusta o ambiente do SQL*Plus
-----------------------------------------------------------------
set echo off
set time off timing off pagesize 10000
set feedback 6 heading ON termout ON trimout ON trimspool ON
set pagesize 10000 linesize 1000 longchunksize 1000 long 10000000
set serveroutput ON size 1000000 format WORD_WRAPPED
set pause OFF verify OFF
column SQL_HASH format a20

-----------------------------------------------------------------
--- Recebe como argumento o HASH_VALUE do comando SQL
-----------------------------------------------------------------
define hashvalue=&1

-----------------------------------------------------------------
--- Impede merge de views complexas (performance do SELECT abaixo)
-----------------------------------------------------------------
alter session set "_complex_view_merging"=false;

-----------------------------------------------------------------
--- Mostra o texto do comando SQL
-----------------------------------------------------------------
select hash_value||decode(child_number, 0, '', '/'||child_number) SQL_HASH
     , sql_text
  from v$sql
 where child_number= 0 
   and hash_value= &hashvalue
/

-----------------------------------------------------------------
--- Mostra o plano de execucao do comando SQL
-----------------------------------------------------------------
select '| Operation                       | Name               | Starts | E-Rows | A-Rows | Buffers | Reads  | Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */ 
select '------------------------------------------------------------------------------------------------------------------------'
from dual 
union all 
select rpad('| '||substr(lpad(' ',1*(depth))||operation|| decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'|| 
          rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'|| 
          lpad(decode(starts,null,' ', 
                         decode(sign(starts-1000), -1, starts||' ', 
                         decode(sign(starts-1000000), -1, round(starts/1000)||'K',
                         decode(sign(starts-1000000000), -1, round(starts/1000000)||'M', 
                                           round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
          lpad(decode(cardinality,null,' ',
                         decode(sign(cardinality-1000), -1, cardinality||' ',
                         decode(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
                         decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M', 
                                            round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
          lpad(decode(outrows,null,' ', 
                         decode(sign(outrows-1000), -1, outrows||' ',
                         decode(sign(outrows-1000000), -1, round(outrows/1000)||'K',
                         decode(sign(outrows-1000000000), -1, round(outrows/1000000)||'M', 
                                            round(outrows/1000000000)||'G')))), 8, ' ') || '|' || 
          lpad(decode(crgets,null,' ', 
                         decode(sign(crgets-10000000), -1, crgets||' ', 
                         decode(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
                                            round(crgets/1000000000)||'G'))), 9, ' ') || '|' || 
          lpad(decode(reads,null,' ', 
                         decode(sign(reads-10000000), -1, reads||' ',
                         decode(sign(reads-1000000000), -1, round(reads/1000000)||'M',
                                            round(reads/1000000000)||'G'))), 8, ' ') || '|' || 
          lpad(decode(writes,null,' ', 
                         decode(sign(writes-10000000), -1, writes||' ', 
                         decode(sign(writes-1000000000), -1, round(writes/1000000)||'M', 
                                            round(writes/1000000000)||'G'))), 8, ' ') || '|' || 
          lpad(decode(etime,null,' ', 
                         decode(sign(etime-10000000), -1, etime||' ', 
                         decode(sign(etime-1000000000), -1, round(etime/1000000)||'M', 
                                            round(etime/1000000000)||'G'))), 8, ' ') || '|'
    as "Explain plan" 
  from 
       (select /*+ no_merge */ 
                  p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION, 
                  p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY, 
                  p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER, 
                  p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
                  p.PARTITION_STOP, p.DISTRIBUTION, pa.starts, 
                  pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets, 
                  pa.DISK_READS reads, pa.DISK_WRITES writes, 
                  pa.ELAPSED_TIME etime 
        from v$sql_plan_statistics_all pa, 
               V$sql_plan p 
        where p.hash_value = &hashvalue 
           and p.CHILD_NUMBER= 0 
           and p.hash_value = pa.hash_value(+)
           and pa.child_number(+) = 0
        order by id )
union all 
      select '------------------------------------------------------------------------------------------------------------------------'
        from dual; 

-----------------------------------------------------------------
--- Mostra os comandos SQL slave
-----------------------------------------------------------------
select /* QWEKLOIPYRTJHH7 */ 
           decode(object_node,null,'', substr(object_node,length(object_node)-3,1) || ',' || 
           substr(object_node,length(object_node)-1,2)) TQID, 
           other "SLAVE SQL" 
from v$sql_plan vp 
where other is not NULL 
    and hash_value = &hashvalue 
    and CHILD_NUMBER= 0;

-----------------------------------------------------------------
--- Restaura o merge de views complexas
-----------------------------------------------------------------
alter session set "_complex_view_merging"=true;

-----------------------------------------------------------------
--- Final do script
-----------------------------------------------------------------
----------------------------------
-- FIM DO SCRIPT  -- 
Anúncios

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 um comentário

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