Improve Database performance by analyzing tables

A little bit background why we need to analyze tables, indexes, clusters:

         Sometimes we have changes for the tables, for example if we have regular ETL process that constantly modifies the database structure or table contents. The statistics that Oracle collects from the last table analyzation might be out of sync with the current data dictionary. And this information is often used by the optimizer when querying. Therefore querys base against the old statistics would run slow and the database performance could be decreased because of the changed statistic not being collected accordingly.

       What statistics does Oracle collect by the analyzation?

         Oracle will collect statistic on the number of rows, the number of empty data blocks, the number of blocks below the high water mark, the average data block free space, the average row length, and the number of chained rows in a table when the Oracle ANALYZE TABLE command is performed.Oracle ANALYZE TABLE can be used to collect statistics on a specific table.

          When using Oracle ANALYZE TABLE all domain indexes marked LOADING or FAILED will be skipped.

          Oracle will also calculate PCT_ACESS_DIRECT statistics for index-organized tables when using Oracle ANALYZE TABLE

         Note:Before analyzing a table with the Oracle ANALYZE TABLE command you must create function based indexes on the table.

        Two options for analyzing tables: Computes Statistics VS Estimate Statistics

Computes Statistics Estimate Statistics
Method Full Table Scan Sampling
Accuracy High Depends on the sample
Cost High Low

        Computer Statistics Analyzation uses the full table scan again the entire Oracle table. Upon completion of the command, the data dictionary would be updated with the highly accurate statistics by the cost-based optimizer.However, Estimate Statistics Analyzaiton would take samples from the table and the samples are stored in the data dictionary.

       Note: You need to weight the time and database resource against the accuracy of the statistics before considering which method should take at your current point of view. For small to medium tables, use analyze table table_name compute statistics. For larger tables take a sample (for example, analyze table table_name estimate statistics sample 20 percent).

         Utilize TOAD for table analyzations

          Login Toad with your database connection. Select the table you want to analyze and right click on Analyze Table.

  

    Once you get to the analyzation page, select the tables you want to do analyzation on and click on the green arrow to start analyzing the table.By default the method would be estimate statistics as shown in the picture below.

The success information is displayed after the analyzation.

You could also change the mode to be compute statistics by switching to the Options tab and modify the Analyze Functions as shown below:

              Utilize SQLPLUS for table analyzations

             Login to SQLPLUS with your database connection as below:

            A better solution : Utilize DBMS_STATS to collect statistics

Here is a code snippet for how we are gonna invoke the dbms_stats package in 10g.Just note that Oracle Analyze table commands are now considered to be old fashioned. And the dbms_stats package are used more and more frequently in packages because it provides high quality infomation about tables and indexes.

BEGIN

FMIS_SYNCH.SYNCH_REF_TABLES_WITH_FMIS;

DBMS_STATS.GATHER_SCHEMA_STATS

(

ownname=>'yourowner',

options => 'GATHER AUTO',

estimate_percent => DBMS_STATS.auto_sample_size,

method_opt=>'for all columns size repeat',

cascade => true,

degree=>12

);


END;

For a more detailed information on the features that Oracle 11g Database has offered. Please take a look at the guru’s blog as below:

http://www.dba-oracle.com/art_builder_dbms_stats.htm

Advertisements
Posted in ETL

One thought on “Improve Database performance by analyzing tables

  1. Good post. I be taught something tougher on completely different blogs everyday. It should at all times be stimulating to learn content from other writers and observe a little bit something from their store. I’d want to use some with the content on my weblog whether you don’t mind. Natually I’ll give you a hyperlink on your net blog. Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s