Hands-On Oracle Performance Tuning-CD#01

Hands-On 01 (Why tuning and what is Granule unit)

Steps required when doing performance tuning

Granule definition

Hands-On 02 (Memory Space Allocation on the Fly)

Oracle Memory Structures

Using the V$SGA dictionary view

Fixed Size

Variable Size

Oracle Database Files

Controlfiles

Datafiles

Redo Buffers

Useful parameters and views to resize memory

SHARED_POOL_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

DB_BLOCK_BUFFERS

BLOCK_SIZE.

DB_CACHE_SIZE

SHARED_POOL_SIZE

LARGE_POOL_SIZE

DB_CACHE_SIZE

SHO PARAMETER

ALTER SYSTEM SET

BUFFER POOL

KEEP

RECYCLE

DEFAULT

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

HOST ERASE

DB_8K_CACHE_SIZE

Views

V$SGA

V$BUFFER_POOL

Commands:

SHO PARAMETER

ALTER SYSTEM SET

CREATE TABLESPACE

DROP TABLESPACE

Hands-On 03 (Sizing the Shared Pool)

Resizing the Shared Pool Memory allocation

Keeping an object in the Shared Pool memory

Displaying the Library Cache Hit Ratio

Displaying the objects from the Shared Pool memory

Removing an object from the Shared Pool memory

Calculating the Dictionary Cache Hit Ratio

The Major components of the Shared Pool memory

The Library Cache

The Dictionary Cache

The User Global Area

Using the SHARED_POOL_RESERVED_SIZE parameter

Commands:

SHOW PARAMETER

SET SERVEROUTPUT ON

EXECUTE SYS.DBMS_SHARED_POOL.SIZES(150);

EXECUTE SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD');

EXECUTE SYS.DBMS_SHARED_POOL.UNKEEP('SYS.STANDARD');

Hands-On 04 (Sizing the Buffer Cache)

Resizing the BUFFER CACHE memory allocation

Setting the Dynamic Buffer Cache Advisory parameter

ON
OFF
READY

The Least Recently Used (LRU) list

The Dirty list

The DB Writer processes (DBWn)

Check the SGA memory maximum allocation size

Using the V$PARAMETER view

Checking the Buffer Cache size

Checking the Shared Pool size

Checking the Redo Log buffer size

Checking the JAVA Pool size

Decreasing the Shared Pool memory size

Increasing the Buffer Cache memory size

Commands:

SHOW PARAMETER

ALTER SYSTEM SET db_cache_advice=ON;

ALTER SYSTEM SET db_cache_size=80M

ALTER SYSTEM SET shared_pool_size=60M

ALTER SYSTEM SET db_cache_size=80M

Hands-On 05 (Measuring the Buffer Cache Hit Ratio)

Measuring the Buffer Cache Hit Ratio

Creating a cache table

Calculating the Hit Ratio for Multiple Pools

Displaying the Hit Ratio for the KEEP buffer pool

Caching the Oracle objects in the Buffer Pools

Diagnosing the FREELIST contentions

Adding a FREELIST to a table

Using the DEFAULT pool

Using the KEEP pool

Using the RECYCLE pool

Using the V$SYSSTAT view

Using the V$BUFFER_POOL view

Using the V$BUFFER_POOL_STATISTICS dictionary view

Using the DBA_SEGMENTS view

Using the V$SESSION_WAIT view

Dropping a table

Commands:

ALTER SYSTEM SET db_cache_size=60m

ALTER SYSTEM SET db_keep_cache_size=16m

CREATE TABLE STORAGE (BUFFER_POOL KEEP)

SELECT /*+ CACHE (iself.dept) */

ALTER TABLE STORAGE (FREELISTS 2)

Hands-On 06 (Monitor and size the Redo Log buffer)

Monitoring the Redo Log Buffer memory size

Re-sizing the Redo Log Buffer memory size

Checking the Redo allocation entries ratio

Checking waiting sessions

Checking for an Online Full Redo Log file

Using the V$SESSION_WAIT view

Hands-On 07 (Database I/O problem)

Monitoring the Database I/O problem

Monitoring the Checkpoint process activities

Tuning the Checkpoint process activities

Using the V$FILESTAT view

Using the V$SYSSTAT view

Using the V$SYSTEM_EVENT view

Using the DBA_DATA_FILES view

Setting the UNDO_MANAGEMENT parameter

Monitoring the SQL statement sorting

Distributing tablespaces on different disks

Using the Redundant Array of Inexpensive Disks (RAID)

Commands:

ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile

Hands-On 08 (Optimize Sort Operations)

Monitoring a sort statement operation

Optimizing a sort statement operation

Using the V$SYSSTAT view

SORTS (DISK)

SORTS (MEMORY)

Calculating the sort Ratio

SORT_AREA_SIZE

PGA_AGGREGATE_TARGET

Increasing the SORT_AREA_SIZE parameter

Hands-On 09 (Diagnosing Contention for Latches)

Diagnosing contention for latches

Viewing the Shared Pool memory size

Viewing the Library Cache Hit Ratio

Viewing the Redo Allocation Latch ratio

Viewing the Redo Copy Latch wait ratio

Types of latch requests

Willing to wait request

Immediate Request

Using the V$LATCH dictionary view

Using the V$LATCHHOLDER view

Using the V$LATCH view

Using the V$LATCHNAME view

Calculating the Shared Pool Latch Hit Ratio

Calculating the Redo Allocation Latch ratio

Calculating the Copy Wait Ratio

Hands-On 10 (Tuning rollback or undo segments)

Tuning Rollback or UNDO Segments

Setting the UNDO_SEGMENT parameter

Setting the UNDO _RETENTION parameter

Setting the UNDO_TABLESPACE parameter

Calculate an estimate of UNDO spaces for

   The UNDO retention requirement

Tuning the manual UNDO segments

Listing block contentions

Calculating the UNDO segment Hit Ratio

Using the V$UNDOSTAT view

Using the V$ROLLNAME view

Using the V$ROLLSTAT view

Using the V$WAITSTAT view

Using the V$SYSSTAT view

Checking the DB BLOCK GETS parameter

Checking the CONSISTENT GETS parameter

Checking the SHRINKS column

Checking the AVESHRINKS column

Hands-On 11 (Monitoring and Detecting Lock Contention)

Monitoring and detecting a lock contention

Locking a table in the exclusive mode

Locking a table in the shared mode

Displaying locks contention

Killing a session

Using the V$LOCK view

Using the DBA_OBJECTS view

Using the V$LOCKED_OBJECT view

Using the V$SESSION view

The SID column

The SERIAL# column

Types of locks

TX-Transaction Enqueue

TM-DML Enqueue

UL- User Supplied

Commands:

LOCK TABLE IN EXCLUSIVE MODE

LOCK TABLE IN SHARE MODE

ALTER SYSTEM KILL SESSION

Hands-On 12 (Optimizing a SQL statement)

Using the EXPLAIN PLAN statement

Creating the PLAN_TABLE table

Submitting a SQL statement using the EXPLAIN PLAN statement

Using the SET STATEMENT_ID clause

Recalling the EXECUTION plan from the PLAN_TABLE table

Understanding of the following operations:

TABLE ACCESS FULL

TABLE ACCESS BY INDEX

INDEX UNIQUE SCAN

NESTED LOOPS

MERGE JOIN

FILTER

SORT AGGREGATE

Commands:

START %ORACLE_HOME%\rdbms\admin\utlxplan

EXPLAIN PLAN SET STATEMENT_ID=

Hands-On 13 (Diagnostic and Tuning Tools)

Installing and Configuring the STATSPACK

Running the SPCREATE script

Creating user PERFSTAT

Connecting to the PERFSTAT user

Cleaning all the STATSPACK table

Producing a performance report

Taking SNAPSHOTS

Setting the TIMED_STATISTICS parameter

Understanding of the X$, and V$ tables

Using the V$FIXED_TABLE

Understanding the PERFSTAT scripts:

SPCREATE.SQL

SPTRUNC.SQL

SPREPORT.SQL

Reading the output PERFSTAT file sp_1_6

Commands:

SHO PARAMETER TIMED_STATISTICS

ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

START %ORACLE_HOME%\rdbms\admin\spcreate

START %ORACLE_HOME%\rdbms\admin\sptrunc

START %ORACLE_HOME%\rdbms\admin\spreport

            EXECUTE statspack.snap;