Ron’s Weblog

While tuning some SQL queries in one of my applications, we encountered query plans that, at first glance, seemed to be plain wrong. Having recently upgraded to Oracle 10g, we found that many of our queries worked better in 9i. We tracked it down to a difference in how the new CBO (Cost Based Optimizer) works in 10g.

The OPTIMIZER_MODE initialization parameter in Oracle 10g influences whether the query is optimized to retrieve the first rows as soon as possible (first_rows) or all rows (all_rows). The default in 10g is all_rows.

For OLTP systems (most web applications I worked with have fallen under this category), “first_rows” usually makes sense. For example, displaying paginated results like the first 10 of a 1000 records.

However, if your database is used by clients other than your application(for example a reporting client), changing the default OPTIMIZER_MODE might not be a good option.

This was the case with our application where we needed both:

• A paginated RIA grid where first_rows made sense.
• An offline reporting server where all_rows made more sense.

One option was to go back to every query that needed “quick results” and add the SQL hint /* first_rows(n)*/. For example,

select /*+first_rows*/ last_name, first_name from Customer...

This seemed like a lot of work. Also, if you’re using Hibernate HQL, you cannot insert hints into the generated SQL.The other option is to execute the following statement prior to running the query(s).

ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';

However, this would also mean a lot of changes to existing code. Also, because we were using connection pooling, I’d rather execute this only once when a connection is established, and not with every query.Since we were using WebLogic Server 9.2 , we were able to take advantage of a connection pool property called “Init SQL”.(Under DataSource>Connection Pool>Advanced in the WebLogic console). This property can be set to a SQL statement that would be executed by WebLogic when the connection is initialized. We set this to :

SQL ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';

This way, regardless of the Oracle defaults, we were able to customize the OPTIMIZER_MODE without having to tweak the application code or the database.