Oracle Performance Tuning-15


Performance Tuning

Optimizer mode. It will collect the statistics of the object.

Optimizer will 1.transform the query – parsed 2.estimate 3. Generate a plan.

  1. Rule based optimizer (RBO) – deprecated from 10g onwards.
  2. Cost based optimizer (CBO)


RBO – Rule. 15 objects can be gathered.

CBO – Choose (or) All_rows, First_rows & first_rows_n. More than 256 objects can be gathered.

The cost based optimizer will always select the low cost. It may be an index scan or table scan, which is lower.


Choose / All_rows – It will gather the stats for all the rows of the table.

First_rows – Only first row of the table

First_rows_n – The will gather the number of given rows. It mostly used for a larger tables.


Optimizer statistics are a collection of data that describe more details about the database objects.

For an object, the following will be taken.

Cost – Number of resources, like CPU,RAM etc

Cardinal – No. of rows

Bytes- Size of the rows

DBblockgets- Reading data from DBC (Select)

Consistentgets- Reading data from DBC (DML)

Physical reads – Reading data from data file

Physical writes – Writing data to data file

Logical reads- DBblockgets + Consistentgets


Find out the current optimizer mode

Sql> show parameter optimizer_mode;

Sql> alter system set optimizer_mode =all_rows/first_rows/rule;


Sql> show parameter timed_statistics; – Default will be false

Sql> alter system set timed_statistics =true; – It will gather stats auto.

STATISTICS_LEVEL is set to TYPICAL or ALL, then true & BASIC, then false

How to gather stats

DB: level:

sql> exec dbms_stats.gather_database_stats;

Schema level – Mostly in real time.

sql> exec dbms_stats.gather_schema_stats (‘PRAPHU’, cascade => true); – cascade will gather indexes also.

Table: level:

sql> exec dbms_stats.gather_table_stats (‘PRAPHU’, ‘EMP’,cascade => true);


sql> Analyze table emp compute statistcs; — Old version


Last stats analyzed for a table:

sql> select table_name,last_analyzed from dba_tables where owner= ‘PRABU’;

We mostly gather stats in non-business hours on daily basis. Stats are stored in system tablespace.


Query Tuning / SQL / Application Tuning:

Sql> Select * from emp where eno =101;

Methods to find the query problem:

  1. Explain plan – Query level – Real time used
  2. TK proof – Query level
  3. Auto trace – Query level
  4. AWR – Server level
  5. DMVs – Server level
  6. OEM – Need to write

Possible checks and fix

Code level:

  1. Check the execution plan for the query by using anyone above methods
  2. Check the index has been fulfilled
  3. Check the stats are updated
  4. Check the index fragmentation
  5. Check the row chaining, Row migration & HWM

Server level:

  1. Run the v$ view and DMV to find the problem like blocking, resource usages
  2. Run the AWR, ADDM & ASH report and analysis the issue


Explain Plan:

It will display the execution of query, without actual run.

Sql> @?/rdbms/admin/utlxplan.sql; – It will create a plan table.

Sql> explain plan for select * from emp where eno=101; – It will save the data in plan_table

Sql> @?/rdbms/admin/utlxpls.sql;


Sql> select * from table (dbms_xplan.display);


Sql> select * from plan_table;


TK Prof

It’s a utility. It used to convert the trace to human readable file.

It will give the actual query execution details.

Keyword: TKprof

Sql> show parameter user_dump_dest;

Sql> alter session set sql_trace=true;

Alter system set – Permeant.

Alter session set – Temporary, only for a session.



A DBA may enable tracing for another user’s session by using the following statement:



Sql> select * from emp where eno=101; – Note the time and check the udump location.

Sql> alter session set sql_trace=flase; – Otherwise it will create a dump file for each query for a session.

$ TKprof /opt/oracle/diag/rdbms/prabu/trace/oracle123.trc /opt/output.txt – It will change the trace file to meaning full one.

$ vi /opt/output.txt

Analyze and apply the result to the following formula.

Formula: [1- Physical reads (PR)/(dbblockgets+consistentgets]*100

If the result is >90% good. < 90% bad query.

Auto Trace

It will trace all the queries.


autotrace on – Enables all options.

autotrace on explain – Displays returned rows and the explain plan.

autotrace on statistics – Displays returned rows and statistics.

autotrace trace explain – Displays the execution plan for a select statement without actually executing it. “set autotrace trace explain”

autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.


Sql> set timing on;

01.40.00 sql> set autotrace on;

01.41.00 sql> analyze table emp compute statistics; – Optional

sql> select *from emp where eno =101;

Three output will display 1. Query output+ Statistics + table access full or index scan

We need to create an index for eno, the index was created still the output was slow.


Explain plan Tkprof Auto Trace
execution of query, without actual run execution of query, actual run Trace all query


Check the fragmentation:

When to rebuild: —


For that we need to validate the index structure.

Sql> analyze index ind_eno validate structure;

Sql> select [(del_lf_rows_len)/(lf_rows_len)]*100 from index_stats where name=’IND_ENO’;

< 20 % – Good & > 20% need to rebuild.


Sql> select blevel from dba_indexes where index_name =’IND_ENO’;

< 3 % – Good & > 3% need to rebuild.

Sql> alter index IND_ENO rebuild;


Sql> alter index ind_eno rebuild online;

Note: Currently if the index is used by a user, we must do an online rebuild. In case, if we have not checked, it will take more time to rebuild.

10G features:

ASSM- Automatic segment space management.

It’s a query tuning based feature. We can do a shrink online.

Sql> alter table emp enable row movement;

Sql> alter table emp shrink space;

We can shrink the table by using above two methods.  Internally, it will move the data block by block and there will be slowness.

Sql> alter table emp shrink space compact;  – Compact only work, when the table is not used by anyone.

Sql> alter table emp shrink space cascade;  – It will shrink both  table and indexes.

We can use advisory to use the better option.

Sql> select * from dba_advisor_logs;

Sql> alter table emp disable row movement;

Best practice: Use alter shrink.


Check the Row chaining:


Sql> @?/rdbms/admin/utlchain.sql; – It will create a chained_rows table

Sql> analyze table emp list chained rows;

Sql> select count(*) from chained_rows;

0 – Good & >0 – Row chaining and migration problem.


Sql> analyze table emp compute statistics;

Sql> select blocks,empty_blocks,chained_cnt from dba_tables where table_name=’EMP’;

Blocks – 0 good & >0 Below HWM problem

Empty_Blocks – 0 good & >0 Above HWM problem

Chained_cnt – 0 good & >0 Problem in RC & RM.

spool chain.lst;

set pages 9999;

column c1 heading “Owner”   format a9;

column c2 heading “Table”   format a12;

column c3 heading “PCTFREE” format 99;

column c4 heading “PCTUSED” format 99;

column c5 heading “avg row” format 99,999;

column c6 heading “Rows”    format 999,999,999;

column c7 heading “Chains”  format 999,999,999;

column c8 heading “Pct”     format .99;

set heading off;

select ‘Tables with migrated/chained rows and no RAW columns.’ from dual;

set heading on;


owner              c1,

table_name         c2,

pct_free           c3,

pct_used           c4,

avg_row_len        c5,

num_rows           c6,

chain_cnt          c7,

chain_cnt/num_rows c8

from dba_tables


owner not in (‘SYS’,’SYSTEM’)


table_name not in

(select table_name from dba_tab_columns


data_type in (‘RAW’,’LONG RAW’,’CLOB’,’BLOB’,’NCLOB’)



chain_cnt > 0

order by chain_cnt desc



Sql> alter table emp deallocate unused; – Not needed, Since it will remove only the above HWM.

So, following is the methods of fix.

  1. Move tables between tablespaces, then rebuild index. (The move will make an index to unusable)
  2. Move table within tablespace, then rebuild
  3. Use Query to recreate a table ‘create table as select * from’


Sql> alter table emp move tablespace tools;

Sql> alter index ind_eno rebuild;


Sql> alter index ind_eno rebuild tablespace tools; – The index will move and rebuild.

  1. Mostly used in real time

Check we have good free space in the tablespace.

Sql> alter table emp move;

Sql> alter index ind_eno rebuild;


Sql> alter index ind_eno rebuild tablespace tools;


Note: We need to do this in maintenance window.  From 10g onwards we can do this online.

Sql> create table emp1 as select * from emp;

Sql> drop table emp;

Sql> rename table emp1 to emp;

Note: The dependencies will not move, we need to manually create them.


Rebuild Index Coalesce Shrink
Exclusive lock Online Lock
Recreates, 2*space shuffles, to get empty block Shrink empty blocks
Variation size Same index size Variation size


Oracle Advisory for Rebuild, Shrink & Coalesce


variable id number;



name varchar2(100);

descr varchar2(500);

obj_id number;



descr:=’Segment Advisor Example’;


dbms_advisor.create_task (

advisor_name     => ‘Segment Advisor’,

task_id          => :id,

task_name        => name,

task_desc        => descr);


dbms_advisor.create_object (

task_name        => name,

object_type      => ‘TABLE’,

attr1            => ‘HR’,

attr2            => ‘EMPLOYEES’,

attr3            => NULL,

attr4            => NULL,

attr5            => NULL,

object_id        => obj_id);



task_name        => name,

parameter        => ‘recommend_all’,

value            => ‘TRUE’);





I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

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

6 + = 8