#
# Oracle Performance Tuning Chapter 13 p.309
# Looking at the V$SQLAREA table
#
SELECT SQL_TEXT FROM V$SQLAREA
    WHERE UPPER(SQL_TEXT) LIKE '%ACCOUNTS%';
# Tuning session data (Version 7)
SELECT SUM(VALUE) FROM V$SESSTAT
    WHERE NAME = 'session memory'
#
# Oracle Performance Tuning Chapter 13 p.312
# Testing INIT.ORA parameters for the effect of increasing buffer traffic
#
SELECT 250 * TRUNC(INDX / 250) +1 || 'to' ||250 * (TRUNC(INDX / 250) +1)
INTERVAL,
    SUM(COUNT) CACHE_HITS
    FROM SYS.X$KCBRRBH
    GROUP BY TRUNC(INDX / 250);
#
# Oracle Performance Tuning Chapter 13 p.313
# Testing INIT.ORA parameters for the effect of decreasing buffer traffic
#
SELECT 250 * TRUNC(INDX / 250) +1 || 'to' ||250 * (TRUNC(INDX / 250) +1)
INTERVAL,
    SUM(COUNT) CACHE_HITS
    FROM SYS.X$KCBRRBH
    WHERE INDX > 0
    GROUP BY TRUNC(INDX / 250);
#
# Oracle Performance Tuning Chapter 13 p.318
# Looking at Disk I/Os per Disk File
#
SELECT NAME, PHYSRDS, PHYSWRTS
    FROM V$DATAFILE DF, V$FILESTAT FS
    WHERE DF.FILE# = FS.FILE#;
#
# Oracle Performance Tuning Chapter 13 p.319
# Looking at Disk I/Os per Disk File
#
SELECT NAME, VALUE FROM V$SYSSTAT
    WHERE NAME IN ('sorts(memory)', sorts(disk)');
#
# Oracle Performance Tuning Chapter 13 p.320
# Looking at Disk I/Os per Disk File
#
ALTER TABLESPACE TEMP_TSPACE DEFAULT STORAGE
 (INITIAL 260K  NEXT 260K  PCTINCREASE 0);
#
ALTER TABLESPACE TEMP_TSPACE DEFAULT STORAGE
 (INITIAL 5M  NEXT 5M  PCTINCREASE 0);
#
# Oracle Performance Tuning Chapter 13 p.323
# Reducing Dynamic Extension
#    Detecting Dynamic Extension
TTITLE  'REPORT SHOWING ALL SEGMENTS WITH > 2 EXTENTS'

COLUMN TABLESPACE_NAME     FORMAT A22
COLUMN SEGMENT_NAME        FORMAT A22
COLUMN SEGMENT_TYPE        FORMAT A8

SELECT   TABLESPACE_NAME,
         SEGMENT_NAME,
         SEGMENT_TYPE,
         COUNT(*)
         FROM DBA_EXTENTS
         GROUP BY   TABLESPACE_NAME,
                    SEGMENT_NAME,
                    SEGMENT_TYPE
         HAVING COUNT(*) > 2;
#
SELECT TABLESPACE_NAME, BYTES
    FROM DBA_FREE_SPACE
    WHERE TABLESPACE_NAME = UPPER('&tspace')
    ORDER BY BYTES DESC;
#
# Oracle Performance Tuning Chapter 13 p.325
# Reducing Dynamic Extension
#    Chained Blocks
SELECT SUBSTR(ROWID, 1, 8)||SUBSTR(ROWID, 15, 4) BLCK,
    SUM (NVL(VSIZE(ACC_NO) + 1, 0)
        +(NVL(VSIZE(ACC_DESC) + 1, 0)
        +(NVL(VSIZE(ACC_TYPE) + 1, 0)
        +(NVL(VSIZE(ACC_LIMIT) + 1, 0)
        +(NVL(VSIZE(ACC_COST_ELEMENT) + 1, 0) + 5)
            FROM ACCOUNT
            GROUP BY SUBST (ROWID,1,8)||SUBSTR(ROWID,15,4)
            HAVING
    SUM (NVL(VSIZE(ACC_NO) + 1, 0)
        +(NVL(VSIZE(ACC_DESC) + 1, 0)
        +(NVL(VSIZE(ACC_TYPE) + 1, 0)
        +(NVL(VSIZE(ACC_LIMIT) + 1, 0)
        +(NVL(VSIZE(ACC_COST_ELEMENT) + 1, 0) + 5) > 4096 - 100;
# Assuming 4096 is the value of the DB_BLOCK_SIZE on your machine
#
SELECT * FROM ACCOUNT
    WHERE SUBSTR(ROWID,1,8) || SUBSTR(ROWID,15,4) = '000007230004';
# where 000007230004 is the block and file combination returned from
# your chaining query
#
#
# Oracle Performance Tuning Chapter 13 p.326
# Reducing Dynamic Extension
#    Chained Blocks
CREATE TABLE CHAINED_TEMP AS
SELECT * FROM ACCOUNT
    WHERE SUBSTR(ROWID,1, 8)||SUBSTR(ROWID,15, 4) = '000007230004';

DELETE FROM ACCOUNT
    WHERE SUBSTR(ROWID,1, 8)||SUBSTR(ROWID,15, 4) = '000007230004';

INSERT INTO ACCOUNT
SELECT * FROM CHAINED_TEMP;
#
# Oracle Performance Tuning Chapter 13 p.327
# Reducing Dynamic Extension
#    Chained Blocks
CREATE TABLE CHAINED_TEMP AS
SELECT * FROM ACCOUNT
    WHERE ROWID IN (SELECT HEAD_ROWID  FROM CHAINED ROWS
    WHERE TABLE_NAME = 'ACCOUNT');
DELETE FROM ACCOUNT
    WHERE ROWID IN (SELECT HEAD_ROWID  FROM CHAINED ROWS
    WHERE TABLE_NAME = 'ACCOUNT');
INSERT INTO ACCOUNT
SELECT * FROM CHAINED_TEMP;
#
DELETE FROM CHAINED ROWS
    WHERE TABLE_NAME = 'ACCOUNT';
#
# Oracle Performance Tuning Chapter 13 p.330
# Tuning Rollback Segments, version 6
SELECT CLASS, SUM(COUNT) CONTENTION_RATE
    FROM V$WAITSTAT
    WHERE OPERATION = 'BUFFER BUSY WAITS'
    AND CLASS IN ('UNDO SEGMENT HEADER','UNDO BLOCKS')
    GROUP BY CLASS
#
#    Tuning Rollback Segments, version 7
SELECT CLASS, COUNT FROM V$WAITSTAT
    WHERE CLASS IN ('SYSTEM UNDO HEADER','SYSTEM UNDO BLOCK')
    'UNDO HEADER', 'UNDO BLOCK');
#
# Oracle Performance Tuning Chapter 13 p.334
# Monitoring and Tuning Redo Log Files
#    Monitoring for redo log latch contention, Version 7
SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISSES
    FROM V$LATCH L, V$LATCHNAME LN
    WHERE LN.NAME IN ('REDO ALLOCATION', 'REDO COPY')
        AND L.LATCH# = LN.LATCH#;
#
# Oracle Performance Tuning Chapter 13 p.335
# Reducing Multi-threaded Server Process Contention (Version 7 only)
SELECT NETWORK, SUM(BUSY) / (SUM(BUSY) + SUM(IDLE)) "BUSY RATE"
    FROM V$DISPATCHER
    GROUP BY NETWORK;
#
# Oracle Performance Tuning Chapter 13 p.336
# Reducing Multi-threaded Server Process Contention (Version 7 only)
SELECT NETWORK, DECODE SUM(TOTALQ), 0 , "NO RESPONSES"
    SUM (WAIT)/SUM(TOTALQ) || '   100THS SECS') "AVERAGE WAIT "
    FROM V$QUEUE Q, V$DISPATCHER D
    WHERE Q.TYPE = 'DISPATCHER'
        AND Q.PADDR = D.PADDR
    GROUP BY NETWORK;
#
SELECT DECODE ( TOTALQ, 0 , 'NO REQUESTS'
    WAIT/TOTALQ ||'   100THS SECS') "AVERAGE WAIT"
    FROM V$QUEUE
    WHERE TYPE = 'COMMON';
#
# Oracle Performance Tuning Chapter 13 p.336
# Reducing Locking Problems
#    Using SQL*DBA to look at locks
SELECT A.NAME, A.OWNER#
    FROM SYS.OBJ$ A, SYS.TAB$ B, SYS.UET$ C
    WHERE C.FILE#=3
    AND 288 BETWEEN C.BLOCK# AND
        (C/BLOCK#+C.LENGTH-1)
    AND C.SEGFILE# = B.FILE#
    AND C.SEGBLOCK# = B.BLOCK#
    AND B.OBJ# = A.OBJ#;
#
SELECT * FROM ACCOUNT
    WHERE ROWID = '000000120.000d.0003;