Oracle 10g Cost Based Optimizer

Hello Friends, here i like to give some brief on Cost based optimizer in Oracle, i am not covering Rule based optimizer as it is not much in use as well not recommended. plz share ur feedback or if u like to contribute more on this topic,

Oracle 10g Cost Based Optimizer

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. To figure out the optimal execution path for a statement, the optimizers consider the following:

  • The syntax you’ve specified for the statement
  • Any conditions that the data must satisfy (the WHERE clauses)
  • The database tables your statement will need to access
  • All possible indexes that can be used in retrieving data from the table
  • The Oracle RDBMS version
  • The current optimizer mode
  • SQL statement hints
  • All available object statistics (generated via the ANALYZE command)
  • The physical table location (distributed SQL)
  • INIT.ORA settings (parallel query, async I/O, etc.)

Understanding the Cost-Based Optimizer

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. Plz note The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

  • OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
  • A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command

The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.

If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up. When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:



If you analyze a table by mistake, you can delete the statistics. For example:


Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Inner workings of the cost-based optimizer

The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer’s functionality can be (loosely) broken into the following steps:

1.       Parse the SQL (check syntax, object privileges, etc.).

2.       Generate a list of all potential execution plans.

3.       Calculate (estimate) the cost of each execution plan using all available object statistics.

4.       Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality —A UNIQUE index’s selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

Non-UNIQUE index equality —For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

Range evaluation —For index range execution plans, selectivity is evaluated. This evaluation is based on a column’s most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

Range evaluation over bind variables—For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

System resource usage —By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important —The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

Oracle Technical – Improve Import/ Export performance?



Ø       Set the BUFFER parameter to a high value (e.g. 2M)



Ø       Set the RECORDLENGTH parameter to a high value (e.g. 64K)

Ø       Stop unnecessary applications to free-up resources for your job.

Ø       If you run multiple export sessions, ensure they write to different physical disks.

Ø       DO NOT exports to an NFS mount filesystem. It will take forever.



Ø       Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

Ø       Place the file to be imported on a separate physical disk from the oracle data files

Ø       Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

Ø       Set the LOG_BUFFER to a big value and restart oracle.

Ø       Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG)

Ø       Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

Ø       Use COMMIT=N in the import parameter file if you can afford it

Ø       Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements

Ø       Remember to run the indexfile previously created

 Thanks – Shivmohan Purohit 






Trace a Concurrent Request And Generate TKPROF File

Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program 

  • Responsibility: System Administrator
  • Navigate: Concurrent > Program > Define
  • Query Concurrent Program
  • Select the Enable Trace Checkbox 

Turn On Tracing

  • Responsibility: System Administrator
  • Navigate: Profiles > System
  • Query Profile Option Concurrent: Allow Debugging
  • Set profile to Yes

 Run Concurrent Program With Tracing Turned On

  • Logon to the Responsibility that runs the Concurrent Program 
  •  In the Submit Request Screen click on Debug Options (B)
  • Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ‘Request id: ‘||request_id ,  ‘Trace id: ‘||oracle_Process_id,  ‘Trace Flag: ‘||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ‘||prog.user_concurrent_program_name,  ‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ‘||decode(phase_code,’R’,’Running’)  ||’-‘||decode(status_code,’R’,’Normal’),  ‘SID Serial: ‘||ses.sid||’,’|| ses.serial#,  ‘Module : ‘||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and’user_dump_dest’  and’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id; 





3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10” long running queries



$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 





Thanks – Shivmohan Purohit 


Discoverer SQL: Long Running Discoverer Report

Discoverer SQL: Long Running Discoverer Report

Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.

SELECT q.qs_doc_name Worksheet,

 q.qs_doc_details Sheet ,

 q.qs_num_rows Lines,


 q.qs_act_elap_time Seconds,

 FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes, 

 q.qs_created_date Executed,

 q.QS_DOC_OWNER Owner,

 u.user_name APPS_User,



WHEN q.qs_state = 1 THEN ‘RUNNING’

WHEN q.qs_state = 2 THEN ‘COMPLETED’


  FROM discovr_apps.eul5_qpp_stats q,

 fnd_user u

 WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1

AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id

–AND u.user_name LIKE ‘D%’

–AND q.qs_state =1

  –AND FLOOR(q.qs_act_elap_time/60) >= 30 

  ORDER BY q.qs_act_elap_time DESC;

Thanks – Shivmohan Purohit