Execution Optimization and Rows per page

Cognos has  a property Execution optimization - It has 2 options one is All_rows and First_row both of these options point to modes of operation of database while generating execution plans. The default is All_rows . It tells the optimizer that the user wants to see all_rows of output on page so come up with plan that does this as fast as possible

First_row tells optimizer that the user is only interested in first 100 rows of the output on screen at a time. So come up with best plan considering this . Both these options are responsible for different execution plans.  A plan that uses index for retrieving 60,000 records is not best plan for retrieving all rows but since user sees may be only 50 records per page . This plans works best , next 50 rows are retrieved when user clicks on page next  and so on .

You can see this idea in Sql developer it fetches first 50 records and then when you scroll down it fetches the next. However sql developer always use All_rows option as default.

The concept is closely related to concept of Array size  ( read in oracle documentation).

ARRAYSIZE is the number of rows Oracle returns to a client when they ask for the next row. The client will then buffer these rows and use them before asking the database for the next set of rows.

How this affects performance

We had one report with List which returned 60,000 records .  With Execution optimization set to All_rows , It was using Full table scan. Which was taking long time as Fact had 50 million data. We changed it to First_row and it started using index as we were telling db that we are interested in few rows at a time. So fetching 100 at a time from index is much faster.

SQL which helped me to analyse this Problem

Select * From V$session

where osuser  = 'ABC'

Select rows_processed,optimizer_mode From V$sql

where sql_id  ='dfasdfasdf'

You can notice the rows_processed count going up as we scroll down in Sql_developer. It tells how many rows of output has been fetched.

In Cognos the array size is achieved by Setting Rows_per_page.  You will see by using above queries that the intial rows_processed is slightly more than 1000 in my case it is 1500, but when you click on page down each time it retrieves 1000.

When you Click on Bottom button to scroll down to last page. DB has to retrieve all records till last page. You can see the rows_processed counts increasing.