Thursday, September 29, 2011

Setting up Oracle Optimizer

Oracle optimizer determines the best execution plan for the SQL statements which is vital for overall performance of the database. The optimizer is dependent heavily on the availability of the statistics that you gather on the database.

The database can be a warehouse or an OLTP. Based on the type of database, you can set-up your optimizer setting using 'optimizer_mode' parameter

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

My database is setup as ALL_ROWS which is default.  ALL_ROWS, has the goal of maximizing throughput meaning it will process all the rows in the table at once and is recommended for warehouse environments. For OLTP, recommended value is  FIRST_ROWS_n, that is for good response time.

Change you optimizer goal with the below statement

SQL> alter session set optimizer_mode=first_rows_1;

FIRST_ROWS_n value favors index scans whereas the all_rows is more biased toward full scans

No comments:

Post a Comment