Performance Tasks, Essential

Overview

What follows are the essential items a DBA must attend to to ensure optimum performance of an Oracle database. Per Oracle, for your database to gain the optimum benefit of these actions they need to be done in this order:

1. Optimize Segments
2. Rebuild Indexes (if required)
3. Update Stats

Assumptions

The actions here assume:

• You are using Big File Tablespaces (BFTS).
• You perform all the operations during non-production hours.

Optimize Segments

Tablespaces are associated with datafiles. At the block level datafiles can become fragmented. To optimize the blocks you perform segment optimization. Per Oracle Support, the optimal way to perform this with the least impact on your database is to:

1. Enable Row Movement
2. Perform a SHRINK SPACE operation but don't change the High Water Mark (HWM).
3. Reset the HWM.

Example

 ALTER TABLE SCOTT.MyTable ENABLE ROW MOVEMENT;
 ALTER TABLE SCOTT.MyTable SHRINK SPACE COMPACT;
 ALTER TABLE SCOTT.MyTable SHRINK SPACE; (Resets the HWM)

• Resetting the HWM as shown above best ensures the associated table is locked only briefly at the end.
• ALTER TABLE SCOTT.MyTable SHRINK SPACE CASCADE; recovers space for the object and all dependent objects.
• Alternately, ALTER INDEX SCOTT.MyIndex SHRINK SPACE COMPACT;
• To COALESCE or not to COALESCE? See this.

To get the Segment Advisor Recommendations and commands go here.

Check the Progress of Alter Shrink Space Command

1. From SQL Developer get the SID of the session: Tools -> Monitor Sessions
2. Run the below SQL.

set linesize 240
set verify off

COL event     FORMAT a25
COL wait_time FORMAT 999999
COL sql_text  FORMAT a65

select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where a.SID = &v_SID
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;

Rebuild Indexes

Indexes rarely need to be rebuilt. It is usually dependent on the amount of deleted data.

Get Indexes That Need To Be Rebuilt

 -- Find Indexes having a height(blevel+1) > 4.
 SELECT owner, index_name, table_name, blevel FROM dba_indexes WHERE BLEVEL > 3;
 Owner   Index_Name  Table_Name  BLEVEL
 ------- ----------- ----------- -------
 SCOTT   EMP_IDX1    EMP         4

 -- Find Indexes having a ratio of (DEL_LF_ROWS/LF_ROWS*100) > 20.
 ANALYZE INDEX SCOTT.EMP_IDX1 VALIDATE STRUCTURE;

 SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio 
 FROM INDEX_STATS;
 NAME         HEIGHT     LF_ROWS    LF_BLKS     DEL_LF_ROWS
 ------------ ---------- ---------- ----------- -------------
 EMP_IDX1     8          938752     29575       73342

We do this also because the blevel is not always an indication of a bad index.

Rebuild It

Get Current Tablespace

 SELECT tablespace_name, index_name FROM dba_indexes WHERE table_name='EMP';
 TABLESPACE_NAME  INDEX_NAME
 ---------------  ----------
 INDEXES          EMP_IDX1

Rebuild Index

 ALTER INDEX scott.emp_idx1 REBUILD PARALLEL 4 ONLINE TABLESPACE indexes;

Check Status

 ANALYZE INDEX SCOTT.EMP_IDX1 VALIDATE STRUCTURE;

 SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio 
 FROM INDEX_STATS;
 NAME         HEIGHT     LF_ROWS    LF_BLKS     DEL_LF_ROWS
 ------------ ---------- ---------- ----------- -------------
 EMP_IDX1     4          865410     15434       0

Reset PARALLEL?
Some applications cannot handle an index using a PARALLEL value greater than 1. You can reset it as so:

 SELECT degree FROM dba_indexes WHERE index_name='EMP_IDX1';
 DEGREE
 ------
 4

 ALTER INDEX SCOTT.EMP_IDX1 PARALLEL 1;

 SELECT degree FROM dba_indexes WHERE index_name='EMP_IDX1';
 DEGREE
 ------
 1

Reference: 1373415.1