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.
