iSelfSchooling - Case Studies (Performance Tuning)

The Oracle Performance Tuning Case Study consists of the 13 most common ways to detect an Oracle performance problem. In your organization, you are expected at least know how to: Change memory allocation on the fly; size the Shared Pool; size the Buffer Cache; Measure the Buffer Cache Hit Ratio; Monitor and re-size the Redo Log buffer; Detect Database I/O problems; Optimize Sort Operations; Diagnose Contentions for Latches; Tune rollback or undo segments; Monitor and Detect Lock Contentions; Optimize a SQL statement; and Use Diagnostic and Tuning Tools.

Hands-On Oracle Performance Tuning-CD#01

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

As a DBA you, are also responsible for detecting performance problems of your organization’s database. You need to know how to start investigating a performance problem and then solve it. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Steps required when doing performance tuning
Granule definition

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

As a DBA, you are responsible for monitoring and changing memory space allocation of your organization’s database in case of a performance problem. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for monitoring and changing the memory space allocation for the Shared Pool in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for monitoring and changing the memory space allocation for the Buffer Cache in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for monitoring and calculating the Buffer Cache Hit Ratio in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible to monitor and resize the Redo Log buffer in the SGA memory in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for monitoring and detecting I/O problems of your organization’s database in case of performance problems. Your job’s responsibilities dictate that you should at least be aware of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible to optimize sort operations of your organization’s database in case of performance problems. Your job’s responsibilities dictate that you should be aware of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for diagnosing any latch contentions in the Shared Pool area in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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 segment) - Manuscript

As a DBA, you are responsible for tuning rollback or undo segments in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for monitoring and detecting a lock contention in case of a performance problem. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

As a DBA, you are responsible for optimizing a SQL statement using the EXPLAIN PLAN statement in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

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) - Manuscript

Another one of your responsibilities as a DBA is to use and read the STATSPACK tool in a case of a performance problem. Your job’s responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

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;