Sunday , October 22 2017



  1. Performance tuning is of 2 types
    1. Proactive tuning – it is least preferred because of practical problems
    2. Reactive tuning – most preferred way which means react to the problem instead of preventing it to occur
  2. Any performance problem should be resolved in following phases
  1. When a performance problem is reported, first we need to check if problem is only for one user or for multiple users
  2. If it is only for only one user, then it could be because of network problem so check tnsping to database
  3. If tnsping value is too high then intimate network admin about this. If not move to next phase


  1. In this phase we need to find whether any new applications added or are there any changes in the code
  2. If any additions or changes, ask application team to revert them and then check the performance. If working fine, then problem is with those changes
  3. If no additions/changes happened or if performance problem exists after reverting, then proceed for next phase


  1. By running some reports like ADDM or ASH, we can know what queries are giving problem and can send them to application team (or team which is responsible for writing sql queries) for tuning
  2. Sometimes DBA help may be required, so DBA should have expertise knowledge on SQL
  3. In real time, most(90%) of tuning problems will get resolved in this phase. If not solved, proceed to next phase


  1. In this phase, first we need to check what is the last analyzed date for the tables involved in the query.

# To find out last analyzed date of a table

SQL> select last_analyzed from dba_tables where table_name=’EMP’;
  1. If we see last_analyzed date as old date, then it means that table statistics didn’t gathered from long time. It may be the reason for performance problem.
  2. Optimizer will generate the best execution plan based on these statistics. But if statistics are old, optimizer will go for worst plan which affects performance.In such cases, we need to analyze manually using below commands

# To analyze a table

SQL> analyze table emp compute statistics;
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’);
  1. If table contains huge no.of rows analyze will take time as it collects info for each and every row. In such cases, we can estimate statistics which means collecting statistics for some percentage of rows. This can be done using below command

      # To analyze a table using estimate option

SQL> analyze table emp estimate statistics;
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’,’’,40);

# To analyze a Index (Oracle also recommends to analyze indexes also)

SQL> analyze index pk_emp compute statistics;
SQL> exec dbms_stats.gather_index_stats(‘SCOTT’,’PK_EMP’);

# To analyze a schema

SQL> exec dbms_stats.gather_schema_stats(‘SCOTT’);

# To analyze full database (this will not consider base tables)

SQL> exec dbms_stats.gather_database_stats;

# To analyze base tables and dictionary views

SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_fixed_objects_stats;
Note: In 10g, oracle automatically collects statistics for tables which are modified greater than 10% every night 10PM of server time. But due to practical complications, experts recommended to disable that automated job and create a new one manually
Note: Always statistics gathering job should run in non-peak hours of server time as it takes max cpu power and memory for processing
  1. Tables are divided into 3 categories
    1. Static tables   monthly analyze
    2. Semi-dynamic tables   weekly analyze
    3. Dynamic tables   daily analyze
  2. Optimizer works on two modes
    1. Rule based optimization (RBO) [deprecated from 10g]
    2. Cost based optimization (CBO)
  3. OPTIMIZER_MODE parameter will actually decide which optimization mode should be used for query execution
  4. In 9i, optimizer_mode will have default value as CHOOSE i.e optimizer will choose whether to use RBO or CBO. In 10g/11g, the default value is ALL_ROWS which means it will prepare the execution plan in such a way that it will select all rows from the table
  5. We can define optimizer_mode=first_n_rows (n is integer) in order to select n rows from the table. This is useful when you are displaying data in pages
  6. If performance problems remain same even after analyzing, then we need to see if query is using indexes or not by generating explain plan


  1. It is a plan which shows the flow of execution for any sql statement
  2. To generate explain plan we require plan_table in SYS schema. If not there, we can create using $ORACLE_HOME/rdbms/admin/utlxplan.sql script
  3. After creating plan_table, use below command to generate explain plan
SQL> grant select,insert on plan_table to scott;
SQL> conn scott/tiger
SQL> explain plan for select * from emp;
  1. To view the content of explain plan, run $ORACLE_HOME/rdbms/admin/utlxpls.sql script
  2. Optimizer may deviate from best execution plan sometimes depends on resources (CPU or memory) availability
  3. If index is not there on a table, create an index on a column which is after where condition in the query
  4. Also choose any one of following types of index to be created
    1. B-Tree index – must be used for high cardinality (no.of distinct values) columns
    2. Bitmap index – for low cardinality columns
    3. Function based index – for columns with defined functions
    4. Reverse key index – for selecting latest data always
  5. If still facing performance problem, check if we are using right type of table
  6. Following are types of tables available
    1. General table – which used regularly
    2. Cluster table – a table which shares common columns with other tables


In the above diagram X is the common column shared by both A & B. Problem in using cluster table is any modifications to X cannot be done easily
    1. Index organized table (IOT) – it avoids creating indexes separately as data itself will be stored in index form. The performance of IOT is fast but DML and DDL operations are very costly
    2. Partition table – a normal table can be split logically into partitions so that we can make queries to search only in 1 partition which improves search time. The following are the types of partitions available
      1. Range
      2. List
      3. Hash
We can also have composite partition of following types
  1. Range – range
  2. Range – list
  3. Range – hash
  4. List – list
  5. List – hash
  6. List – range (from 11g)


View More:



Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *