Oracle Sql tuning...

Tuning SQL Statements

• Tuning SQL statements involve the following steps.
•  Ensuring properly constructed SQL statements .
•  Making use of the  right indexes.
•  Use of appropriate optimizer Hints.
•  Prudent choice of SQL Clauses
•  Avoiding hard parse, etc.,

Introduction to the Oracle Optimize

• Optimizer is a software component in Oracle, which tells Oracle, the best way to execute a SQL statement.
• Oracle from versions 9i onwards, makes use of Cost Based Optimizer (CBO) as against the Rule Based Optimizer (RBO) of earlier versions.
• CBO depends upon a set of statistic tables.
• These tables contain information on various aspects of data distribution in the table.
• This includes details like what percentage of the rows have the same value for a given column, null values distribution,  block related distribution, etc.,
• These details are updated by Oracle periodically.
• We can also update these details by using Oracle supplied packages

Updating Statistics for the Optimizer

• Oracle automatically updates the statistics at periodic intervals as specified  by the DBA. 
• You can also update the statistics with the following Oracle supplied packages.

EXEC dbms_stats.gather_schema_stats(’oratrng’,cascade=>TRUE);

EXEC  dbms_stats.gather_table_stats(‘oratrng’,’EMP’,cascade=>TRUE);

The statistical information gathered by the optimizer includes the following:-

Table Statistics

• Number of rows
• Number of blocks
• Average row length

Column Statistics

• Number of distinct values (NDV) in column
• Number of nulls in column
• Data distribution (histogram)

Index Statistics

• Number of leaf blocks
• Levels
• Clustering factor

System Statistics

• I/O performance and utilization
• CPU performance and utilization

Tables Storing the Statistics

The following are some of the tables that store the gathered statistics.

• USER_TABLES
• USER_OBJECT_TABLES
• USER_TAB_STATISTICS
• USER_TAB_COL_STATISTICS
• USER_TAB_HISTOGRAMS
• USER_INDEXES
• USER_IND_STATISTICS
• USER_CLUSTERS
• USER_TAB_PARTITIONS
• USER_TAB_SUBPARTITIONS
• USER_IND_PARTITIONS
• USER_IND_SUBPARTITIONS
• USER_PART_COL_STATISTICS
• USER_PART_HISTOGRAMS
• USER_SUBPART_COL_STATISTICS
• USER_SUBPART_HISTOGRAMS

Querying  the Statistics

The following sample queries illustrate how to find the statistics from some of these tables.

The Optimizer is the one which will make use of this statistics.

select table_name,column_name,num_distinct 

    from user_tab_col_statistics   

    where table_name = ‘EMP’;

 select table_name, NUM_ROWS 

    from user_tables 

    where table_name in (‘EMP’, ‘DEPT’);