p385.sql

oracal的入门知识

SELECT   sql_text
    FROM v$sqltext t, v$sql_plan p
   WHERE t.hash_value = p.hash_value
     AND p.operation = 'TABLE ACCESS'
     AND p.options = 'FULL'
ORDER BY p.hash_value, t.piece;

SELECT   sql_text
    FROM v$sqltext t, v$sql_plan p
   WHERE t.hash_value = p.hash_value
     AND p.operation = 'INDEX'
     AND p.options = 'FULL SCAN'
ORDER BY p.hash_value, t.piece;  

SET linesize 120
COL operation    format a55
COL cost         format 99999
COL kbytes       format 999999
COL object       format a25
SELECT   hash_value, child_number,
            LPAD (' ', 2 * DEPTH)
         || operation
         || ' '
         || options
         || DECODE (ID,
                    0, SUBSTR (optimizer, 1, 6) || ' Cost=' || TO_CHAR (COST)
                   ) operation,
         object_name OBJECT, COST, ROUND (BYTES / 1024) kbytes
    FROM v$sql_plan
   WHERE hash_value IN (
                    SELECT a.sql_hash_value
                      FROM v$session a, v$session_wait b
                     WHERE a.SID = b.SID
                           AND b.event = '&waitevent')
ORDER BY hash_value, child_number, ID;