iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training

Table of contents

PERFORMANCE -- Table of contents

Hands-On Oracle Performance Tuning

 

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; Database Size; 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; V$SGA; V$BUFFER_POOL

 

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

 

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

 

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

 

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)

 

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

 

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

 

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

 

Good Luck!

 

Google
 
Web web site