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.

I’ve seen this many times in web-based applications that use paginated results.

If you’re using Oracle, the general form of such a query is :

   select * from
   (
      select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from
          (your_query_goes_here) a
      where  ROWNUM <=:MAX_ROW_TO_FETCH
   )
  where rnum  >= :MIN_ROW_TO_FETCH;

While doing this with JDBC/SQL is easy enough-if you’re using Hibernate HQL (with the appropriate Oracle Dialect), you can also take advantage of this feature. For example:

Query q = "from Customer c order by c.lastName";
q.setFirstResult(10);
q.setMaxResults(30);
List results = q.list();

For this query to work correctly, all the time, it is important to always sort by a unique column. In the example above, “lastName” will likely have duplicate values. There is no way to guarantee the order of the returned rows unless we include a unique ‘order by’ clause. If a unique column is not available, in many cases, you can just sort by ROWID. See this article here for more on rownum.The modified HQL would look like

"from Customer c
order by c.lastName, c.rowId"

Of course, you would have to map a java property “rowId” to the pseudo-column “ROWID”.
While mapping it in hibernate, be sure to mark it as update=”false” and insert=”false”, since you cannot update the ROWID column.