| First_rows Versus All_rows [message #349709] |
Mon, 22 September 2008 15:40  |
shrinika
Messages: 306
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  |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
