oracledba.help

Performance Tasks, Essential

<- SpecialTopics

TOC

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.

Because of the time the actions require to run, at first you might need to perform them a little at a time. Especially if you have a large database and\or inferior hardware.

For instance, optimizing the segments of your tables. At first you may only be able to do a few during your nightly maintenance period. Eventually you may find you can do all of them at one time.

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;
  • 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.

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

Rebuilding an index may help performance in specific cases where the blevel and leaf objects are OK.

Update Stats

Statistics on tables, indexes and partitions are used by the Cost Based Optimizer (CBO) in determining the most optimal way to execute SQL. My page on essential stats info is here.


Reference: 1373415.1