#
# ORACLE Performance Tuning Chapter 15 p. 361
# Resizing Temporary Tables
        ALTER USER OPS$SCOTT
            DEFAULT TABLESPACE USER_TSPACE
            TEMPORARY TABLESPACE TEMP_TSPACE;
# ORACLE Performance Tuning Chapter 15 pp. 361-362
# Resizing Temporary Tables
# Change the DEFAULT STORAGE parameter on the temporary tablespace:
        ALTER TABLESPACE TEMP_TSPACE
            DEFAULT STORAGE (INITIAL      10M
                            NEXT          10M
                            PCTINCREASE   0);
# ORACLE Performance Tuning Chapter 15 p. 362
# Resizing Temporary Tables
# Change the DEFAULT STORAGE parameter back to its daily setting:
         ALTER TABLESPACE TEMP_TSPACE
             DEFAULT STORAGE (INITIAL      100K
                             NEXT          100K
                             PCTINCREASE   0);
#
# ORACLE Performance Tuning Chapter 15 p. 363
# Choosing Rollback Segments
        CREATE ROLLBACK SEGMENT LARGE_ROLB1
        TABLESPACE ROLLBACK_TSPACE
        STORAGE (INITIAL 5M NEXT 5M);
#
        CREATE ROLLBACK SEGMENT LARGE_ROLB2
        TABLESPACE ROLLBACK_TSPACE
        STORAGE (INITIAL 5M NEXT 5M);
#
# ORACLE Performance Tuning Chapter 15 p. 366
# Detecting and Avoiding Chained Rows
    SELECT  SUBSTR(ST.NAME,1,40),SY.VALUE, SY.STATISTIC#
        FROM  V$STATNAME ST, V$SYSSTAT SY
        WHERE  ST.STATISTIC#  =  SY.STATISTIC#
        AND  ST.NAME  =  'table fetch continued row';
#
# ORACLE Performance Tuning Chapter 15 p. 367
# Detecting and Avoiding Chained Rows
    SELECT  SUBSTR(ROWID, 1, 8)  BLK,  
        SUM  (  NVL( VSIZE( COL1 ) + 1, 0) 
            +   NVL( VSIZE( COL2 ) + 1, 0) 
            +   NVL( VSIZE( COL3 ) + 1, 0) 
            +   NVL( VSIZE( COL4 ) + 1, 0)  + 5)
        FROM  BAD_TABLE
        GROUP 
        BY SUBSTR(ROWID, 1, 8)
        HAVING	
        SUM (  NVL( VSIZE( COL1 ) + 1, 0)  
           +   NVL( VSIZE( COL2 ) + 1, 0) 
           +   NVL( VSIZE( COL3 ) + 1, 0) 
           +   NVL( VSIZE( COL4 ) + 1, 0)  + 5) > 'BLOCK_SIZE' - 100
#
# ORACLE Performance Tuning Chapter 15 p. 370
# Creating Indexes After Inserts
CREATE  INDEX  TRANS_IDX1 ON TRANSACTION
        CATEGORY, TRANS_DATE) 
        STORAGE (INITIAL 200K  NEXT 100K);
#
# ORACLE Performance Tuning Chapter 15 p. 370
# Using PL/SQL to Speed up Updates
  UPDATE  ACCOUNT_TOTALS  A 
      SET  CURRENT_EXP = CURRENT_EXP + ( SELECT  DAILY_EXP
                                         FROM    TRANSACTION T
                                         WHERE   T.ACC_NO = A.ACC_NO )
    WHERE  EXISTS ( SELECT 'X'
                     FROM   TRANSACTION T
                     WHERE  T.ACC_NO = A.ACC_NO  );
#
# ORACLE Performance Tuning Chapter 15 pp. 370-371
# Using PL/SQL to Speed up Updates
    DECLARE
        CURSOR READ_TRAN  IS
        SELECT ACC_NO, DAILY_EXP
        FROM TRANSACTION;
        .
        .
        .
        ACC_NO_STORE  NUMBER (6);
        DAILY_EXP_STORE   NUMBER (9,2);
    BEGIN
        OPEN READ_TRAN;
        LOOP;
            FETCH READ_TRAN INTO  ACC_NO_STORE, DAILY_EXP_STORE; 
            EXIT WHEN READ_TRAN%NOTFOUND;
            .
            .
            .
            UPDATE ACCOUNT_TOTALS A 
            SET  CURRENT_EXP  =  CURRENT_EXP +  DAILY_EXP_STORE
            WHERE  ACC_NO  =  ACC_NO_STORE ;
        END LOOP;
     END;
#
# ORACLE Performance Tuning Chapter 15 p. 372
# Minimizing the Number of Updates
        UPDATE ACCOUNT  A
        SET  ( ACC_DESC,
               CATEGORY ) = ( SELECT  ACC_DESC,
                                      CATEGORY
                              FROM  INPUT_TABLE  I
                              WHERE  A.ACC_NO  =  I.ACC_NO )
                WHERE EXISTS  ( SELECT  'X'
                                FROM INPUT_TABLE  I
                                WHERE  A.ACC_NO = I.ACC_NO );
# For best performance, use the above statement rather than the ones below: 
        UPDATE ACCOUNT 
        SET  ACCOUNT_DESC  = ( SELECT ACC_DESC  
                               FROM INPUT_TABLE I
                               WHERE  A.ACC_NO = I.ACC_NO  )
               WHERE EXISTS  ( SELECT  'X'  
                               FROM INPUT_TABLE I
                               WHERE A.ACC_NO = I.ACC_NO  ) ;
        UPDATE ACCOUNT 
        SET  CATEGORY  =    (  SELECT CATEGORY  
                               FROM INPUT_TABLE I
                               WHERE A.ACC_NO = I.ACC_NO  )
               WHERE EXISTS ( SELECT 'X'  
                              FROM INPUT_TABLE I
                              WHERE A.ACC_NO = I.ACC_NO  ) ;
        UPDATE ACCOUNT 
        SET  ACCOUNT_DESC = ( SELECT ACC_DESC  
                              FROM INPUT_TABLE I
                              WHERE A.ACC_NO = I.ACC_NO  ),
                 CATEGORY = ( SELECT CATEGORY  
                              FROM INPUT_TABLE I
                              WHERE A.ACC_NO = I.ACC_NO  )
               WHERE EXISTS ( SELECT 'X'  
                              FROM INPUT_TABLE I
                              WHERE A.ACC_NO = I.ACC_NO  ) ;
#
# ORACLE Performance Tuning Chapter 15 pp. 373-374
# Tuning Audit Jobs
    SELECT   ACCOUNT_NAME, FIN_YEAR, TOTAL_EXPEND 
    FROM     ACCT_FIN_YR
    MINUS    
    SELECT   ACCOUNT_NAME,FIN_YEAR,SUM(AMOUNT) 
    FROM     POSTING
    WHERE    TYPE = 'EXPEND'
    GROUP BY ACCOUNT_NAME,FIN_YEAR;
#
# ORACLE Performance Tuning Chapter 15 p. 374
# Tuning Audit Jobs
SELECT   LOCATION_ID, PROJECT_NAME 
    FROM     PROJECT
    WHERE    LOCATION_ID IN ( SELECT LOCATION_ID 
                              FROM   PROJECT
                              MINUS
                              SELECT LOCATION_ID 
                              FROM   LOCATION);