Home » RDBMS Server » Performance Tuning » First_rows Versus All_rows (Oracle 9.2.0.8)
First_rows Versus All_rows [message #349709] Mon, 22 September 2008 15:40 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Hello,

My database is running in FIRST_ROWS optimizer mode. I have the query performance issue in one of the
SQL query which is running from java application.

here is the query. It took 5 to 8 seconds to show the output.


 SQL> SELECT    sc.rat_id
  2                FROM cc_content cc, class_info sc
  3               WHERE cc.collection_id IN ('AGFDET')
  4                 AND sc.state IN ('ready','expired')
  5                 AND sc.expried_dt BETWEEN SYSDATE - 100
  6                                                AND SYSDATE + 50
  7                 AND sc.cntcl_id = cc.cntcl_id
  8            ORDER BY sc.expried_dt DESC
  9  /

78 rows selected.


Here is the execution plan.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=10478 Card=14 By
          tes=630)

   1    0   FILTER
   2    1     NESTED LOOPS (Cost=10478 Card=14 Bytes=630)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'class_info'
           (Cost=10478 Card=5014 Bytes=155434)

   4    3         INDEX (RANGE SCAN DESCENDING) OF CLASS_
       S_EXP_DT' (NON-UNIQUE) (Cost=177 Card=31587)

   5    2       INDEX (UNIQUE SCAN) OF 'PK_cc_content' (UNIQUE
          )

Here is the statistics info.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     836433  consistent gets
       9284  physical reads
          0  redo size
       1331  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         78  rows processed


I found the two solution to resolve this problem. One is, if we remove the ORDER BY sc.expried_dt DESC,
the problem goes away. But we can not remove the ORDER BY caluse. Another option is, add the ALL_ROWS hint.
The problem is resoved when i add ALL_ROWS hint. It took only second to show the output.

Here is the modified query.


SQL> SELECT /*+ all_rows */   sc.rat_id
  2                FROM cc_content cc, class_info sc
  3               WHERE cc.collection_id IN ('AGFDET')
  4                 AND sc.state IN ('ready','expired')
  5                 AND sc.expried_dt BETWEEN SYSDATE - 100
  6                                                AND SYSDATE + 50
  7                 AND sc.cntcl_id = cc.cntcl_id
  8            ORDER BY sc.expried_dt DESC
  9  /

78 rows selected.


Here is the execution plan


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=111 Card=14
          Bytes=630)

   1    0   SORT (ORDER BY) (Cost=111 Card=14 Bytes=630)
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'class_info'
           (Cost=17 Card=2 Bytes=62)

   4    3         NESTED LOOPS (Cost=105 Card=14 Bytes=630)
   5    4           INDEX (RANGE SCAN) OF 'PK_cc_content' (UNI
          QUE) (Cost=2 Card=7 Bytes=98)

   6    4           INDEX (RANGE SCAN) OF 'IDX_class_info_01'
           (NON-UNIQUE) (Cost=3 Card=9)


Here is the statistics info


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         85  consistent gets
          3  physical reads
          0  redo size
       1331  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         78  rows processed



Now when i use FIRST_ROWS hint, query ran for 8 seconds.
Optimizer cost is 10478. Consistent gets is 836433. Physical read is 9284.

Now when i use ALL_ROWS hint, query ran for 1 seconds.
Optimizer cost is 111. Consistent gets is 85. Physical read is 3.

Based on the exeuction plan, my understanding here is, for ALL_ROWS, it sorts at the end. But in FIRST_ROWS,
it is sorting multiple times. Is it correct? Please let me know if my understanding is wrong..
Thanks and appreicate if any one can answer.


Re: First_rows Versus All_rows [message #349715 is a reply to message #349709] Mon, 22 September 2008 18:58 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Looks to me that that in query #2 an overall more optimal
access plan was generated. Focus on the innermost access method
executed within the nested loop.

Cardinality is key. This plan in both instances expects 14 rows to be returned (filtered by the criteria).

In case #1 :

INDEX (RANGE SCAN DESCENDING) OF CLASS_
S_EXP_DT' (NON-UNIQUE) (Cost=177 Card=31587)

is being executed for each row returned from the pk_cc_content
primary key where 31587 rows are being scanned.

The explain plan is cut off at that point.

In case #2 the filtering is optimal, pk_cc_content and idx_class_info are scanned with 7 and 3 rows expected to be returned on each pass.

That significantly reduces the total rows searched to produce the result set.

I would focus on this over trying to view this as an absolute
difference between first_rows and all_rows. Try this out on a few other queries and compare access plans.

Many factors influence the CBO, and Im not up to speed on
those involving external JDBC calls, but the efficiency lies within the actual path chosen in scenario two based off the
total rows that had to be evaluated to come to the result.

Best Regards
Harry
Re: First_rows Versus All_rows [message #349736 is a reply to message #349709] Mon, 22 September 2008 21:38 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
FIRST_ROWS invokes the CBO, but just barely. It's intent is to get the very first row back ASAP. To do this, it uses heuristics, just as the RBO uses. But, unlike the RBO, it does evaluate costs, only the costs of a greatly restricted set of potential execution plans.

For example, a FIRST_ROWS query assumes the cost of a sort to be very high, and will almost never use one if an index which will provide the right ordering is available. The RBO has some strict rules as to which operation is done when, and the RBO might not find the index a FIRST_ROWS query might find.

In contrast, ALL_ROWS is what you think of when you think of the CBO.

In your case, the FIRST_ROWS plan looks a lot like what the RBO would produce. An index was found which would properly order the output, and things were built around it. There were no sorts, as your statistics show.

ALL_ROWS decided that, index schmindex, it is faster to do the sort. Your experience validates this choice.

Since you seem to need all the rows, using ALL_ROWS is the way to go.
Re: First_rows Versus All_rows [message #349922 is a reply to message #349736] Tue, 23 September 2008 08:48 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank you harrysmall3. Thank you TheSingerman. Appreciate your response. Thanks again.

[Updated on: Tue, 23 September 2008 08:48]

Report message to a moderator

Previous Topic: v$sess_io info is not changing.
Next Topic: Query performance
Goto Forum:
  


Current Time: Sat Dec 03 11:56:26 CST 2016

Total time taken to generate the page: 0.09420 seconds