#
#Oracle Performance Tuning Chapter 12 p.272
# Interpreting the Trace File
#
/* SAMPLE PL/SQL SCRIPT TO DEMONSTRATE HOW A FULL TABLE SCAN IS SHOWN
IN THE TKPROF OUTPUT, EVEN THOUGH ONE IS NOT ACTUALLY PERFORMED */

DECLARE CURSOR GET_ACCT IS
     SELECT ACC_NAME FROM ACCOUNTS
         WHERE ACC_ID = 1;
         ACC_NAME_STORE CHAR(30); 
BEGIN
        OPEN GET_ACCT;
        FETCH GET_ACCT INTO ACC_NAME_STORE;
        CLOSE GET_ACCT;
END;
#
#Oracle Performance Tuning Chapter 12 p.273
# Explaining the Optimizer's Plan
    SELECT  LPAD(' ', 2*LEVEL) || OPERATION || '' || OPTIONS || ' ' ||
            OBJECT_NAME EXPLAIN_PLAN
        FROM  PLAN_TABLE 
        CONNECT BY  PRIOR ID = PARENT_ID AND
                    STATEMENT_ID ='EMP FILE SELECT'
        START WITH ID = 1 
        WHERE STATEMENT_ID  = 'EMP FILE SELECT' 
#
#Oracle Performance Tuning Chapter 12 pp.291-292
# Identifying Database Extents
#
    SELECT SUBSTR(S.SEGMENT_NAME,1,20) OBJECT_NAME, 
        SUBSTR(S.SEGMENT_TYPE,1,5) TYPE, 
        SUBSTR(S.TABLESPACE_NAME,1,10) T_SPACE,   
        NVL(NVL(T.INITIAL_EXTENT, I.INITIAL_EXTENT),R.INITIAL_EXTENT)/ 4096
            FST_EXT,
        NVL(NVL(T.NEXT_EXTENT,I.NEXT_EXTENT),R.NEXT_EXTENT) / 4096 NXT_EXT,
            S.EXTENTS - 1  TOT_EXT,
            S.BLOCKS  TOT_BLKS
        FROM    
            SYS.DBA_ROLLBACK_SEGS R,
            SYS.DBA_INDEXES I,
            SYS.DBA_TABLES T,
            SYS.DBA_SEGMENTS S
        WHERE
        S.SEGMENT_NAME     LIKE  UPPER('&S_NAME')  || '%'
    AND S.TABLESPACE_NAME  LIKE  UPPER('&T_SPACE') || '%'
    AND S.EXTENTS             >  1
    AND S.OWNER            =     T.OWNER (+)
    AND S.SEGMENT_NAME     =     T.TABLE_NAME (+)
    AND S.TABLESPACE_NAME  =     T.TABLESPACE_NAME (+)
    AND S.OWNER            =     I.OWNER (+)
    AND S.SEGMENT_NAME     =     I.INDEX_NAME (+)
    AND S.TABLESPACE_NAME  =     I.TABLESPACE_NAME (+)
    AND S.OWNER            =     R.OWNER (+)
    AND S.SEGMENT_NAME     =     R.SEGMENT_NAME (+)
    AND S.TABLESPACE_NAME  =     R.TABLESPACE_NAME (+)
    ORDER BY    
        S.SEGMENT_NAME,   S.SEGMENT_TYPE
#
#Oracle Performance Tuning Chapter 12 p.292
# Performing Database Table Sizing
# Looking for tablespace space shortages
    SELECT  SUBSTR(D.TABLESPACE_NAME,1,15)                    TSPACE,
            D.FILE_ID                                         FILE_ID
            D.BYTES / 1024 / 1024                             TOT_MB,
            D.BYTES / 4096                                    ORA_BLKS,
            SUM(E.BLOCKS)                                     TOT_USED,
            ROUND(SUM(E.BLOCKS) / (D.BYTES / 4096), 4) * 100  PCT_USED,
    FROM   SYS.DBA_EXTENTS   E, 
           SYS.DBA_DATA_FILES D
    WHERE  D.FILE_ID = E.FILE_ID (+)
    GROUP  BY D.TABLESPACE_NAME, D.BYTES
#
# Oracle Performance Tuning Chapter 12 p.293
# Performing Database Table Sizing
# Looking for tablespace fragmentation
    SELECT    SUBSTR(TS.NAME,1,10)                            TSPACE,
              TF.BLOCKS                                       BLOCKS,
              SUM(F.LENGTH)                                   FREE,
              COUNT(*)                                        PIECES,
              MAX(F.LENGTH)                                   BIGGEST,
              MIN(F.LENGTH)                                   SMALLEST,
              ROUND(AVG(F.LENGTH))                            AVERAGE, 
              SUM(DECODE(SIGN(F.LENGTH-5), -1, F.LENGTH, 0))  DEAD
    FROM      SYS.FET$           F, 
              SYS.FILE$          TF, 
              SYS.TS$            TS
    WHERE     TS.TS#  =  F.TS#
    AND       TS.TS#  =  TF.TS# 
    GROUP BY  TS.NAME, TF.BLOCKS;
#
# Oracle Performance Tuning Chapter 12 p.294
# Performing Database Table Sizing
# Looking at space use by individual tables
    SELECT   BLOCKS                                   ALLOCATED_BLKS,
             COUNT(DISTINCT SUBSTR(T.ROWID,1,8) 
                         || SUBSTR(T.ROWID,15,4))     USED, 
            (COUNT(DISTINCT SUBSTR(T.ROWID,1,8) 
                         || SUBSTR(T.ROWID,15,4)) 
             / BLOCKS) * 100                          PCT_USED
    FROM     SYS.DBA_SEGMENTS E,
             &TAB_NAME T
    WHERE    E.SEGMENT_NAME = UPPER ('&TAB_NAME')
    AND      E.SEGMENT_TYPE = 'TABLE' 
    GROUP BY E.BLOCKS;
#
# Oracle Performance Tuning Chapter 12 p.294
# Looking at the average number of records per block
    SELECT   SUBSTR(T.ROWID,1,8)  || '-' ||SUBSTR(T.ROWID,15,4)  BLOCK,
             COUNT(*)                                            ROW_CNT,
    FROM     &TAB_NAME T
    WHERE    ROWNUM  <  2000
    GROUP BY SUBSTR(T.ROWID,1,8) || '-' || SUBSTR(T.ROWID,15,4);
#
# Oracle Performance Tuning Chapter 12 p.296
# Computing the Hit Ratio
#
# Actual "STATISTIC#" values depend upon your platform and ORACLE release.
    SELECT  A.VALUE + B.VALUE                               LOG_READS,
            C.VALUE                                         PHY_READS,
            ROUND(100 * (A.VALUE + B.VALUE - C.VALUE) 
               /(A.VALUE + B.VALUE))                        RATIO,
            D.VALUE                                         PHY_WRITES    
    FROM    SYS.V_$SYSSTAT A,
            SYS.V_$SYSSTAT B,
            SYS.V_$SYSSTAT C,
            SYS.V_$SYSSTAT D
    WHERE   A.STATISTIC#  = 28            /* db block gets   */
            B.STATISTIC#  = 29            /* consistent gets */
            C.STATISTIC#  = 30            /* physical gets   */
            D.STATISTIC#  = 31            /* physical writes */
#
# Oracle Performance Tuning Chapter 12 p.297
# Looking at the Dictionary Cache
# This script applies only to Version 6.
SELECT  SUBSTR(PARAMETER,1,20)                                PARAMETER,
        GETS                                                  GETS,
        GETMISSES                                             MISSES,
        DECODE(GETS,  0, 100, ROUND( 100 * GETMISSES / GETS)) RATIO, 
        COUNT                                                 COUNT,
        USAGE                                                 USAGE,
        DECODE(COUNT, 0, 100, ROUND(100 * USAGE / COUNT))     CAPACITY, 
FROM    SYS.V_$ROWCACHE;