Skip to Main Content
  • Questions
  • are first_rows, all_rows based on choose optimizer mode?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 06, 2002 - 3:44 pm UTC

Last updated: November 21, 2012 - 9:49 am UTC

Version: 8.1.7.2

Viewed 10K+ times! This question is

You Asked

Hi Tom

I have never been clear with optimizer_goal first_rows and all_rows, I suspect they use CBO (or they are CBO?) but if that was the case by default what optimizer are we using? first_rows, all_rows or choose?

Basically is I dont understand the difference between rows (first and all) optimizer and cost based optimizer or they are the samething?

and Tom said...

first_rows and all_rows are CBO only modes, this is true.

In choose mode, the optimizer will be closer to an all_rows optimization.

First_rows attempts to optimize the query to get the very first row back to the client as fast as possible. This is good for an interactive client server environment where the client runs a query and shows the user the first 10 rows or so and waits for them to page down to get more.

All_rows attempts to optimize the query to get the very last row as fast as possible. This makes sense in a stored procedure for example where the client does not regain control until the stored procedure completes. You don't care if you have to wait to get the first row if the last row gets back to you twice as fast. In a client server/interactive application you may well care about that.

Rating

  (35 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 06, 2002 - 4:14 pm UTC

so by default if we set optimizer_mode=CHOOSE we are using ALL_ROWS?

Tom Kyte
April 06, 2002 - 4:22 pm UTC

generally -- IF statistics exist (else you are using RBO) -- plsql does all rows, other environments might do other modes.

optmizer mode types

Lakshmi Narasimhan R, April 07, 2002 - 8:11 am UTC

Hi tom

at any version of oracle before, there exist
a optmizer mode or goal in the name of COST. as far as i knew only RULE and CHOOSE but i heard few people saying optmizer mode = COST. Is it true.

Tom Kyte
April 07, 2002 - 11:31 am UTC

they are "wrong".

This init.ora parameter is documented in the Oracle Reference guide and can take values:

rule, choose, first_rows, all_rows

and in 9i, forst_rows_<N> where n in ( 1, 10 , 100, 1000 )

refer them to the docs.

Many people refer to "all_rows" or "first_rows" as "cost mode"

And a worthy of note...

Connor McDonald, April 08, 2002 - 4:01 am UTC

Just noting for readers that under 9i, FIRST_ROWS_1 is not synonymous with FIRST_ROWS

Cheers
Connor

Mike, April 29, 2002 - 11:35 am UTC

Oracle will use CBO and optimizer_mode set to FIRST_ROWS or ALL_ROWS in init.ora, If there is NO FOUND stats?

Tom Kyte
April 29, 2002 - 12:53 pm UTC

If the optimize mode is set to first or all rows, the CBO will be used for ALL queries, even if there are no statistics.

Please confirm CBO when there are no stats

Andrew, April 29, 2002 - 5:06 pm UTC

Tom

I understood that if statistics where absent, then RBO kicked in even if CBO enabled. Is your previous statement accurate? Is this a difference in behaviour between 8.1.6 and 8.1.7?

In </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2020768125166 <code>
you state that deleting the statistics invokes RBO.

Tom Kyte
April 29, 2002 - 9:11 pm UTC

I am 100% on this one.  Consider:

ops$tkyte@ORA815.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA815.US.ORACLE.COM> 
ops$tkyte@ORA815.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA815.US.ORACLE.COM> alter session set optimizer_goal=choose;

Session altered.

ops$tkyte@ORA815.US.ORACLE.COM> select * from t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'



ops$tkyte@ORA815.US.ORACLE.COM> alter session set optimizer_goal=first_rows;

Session altered.

ops$tkyte@ORA815.US.ORACLE.COM> select * from t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=82 Bytes=1066)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1066)



ops$tkyte@ORA815.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA815.US.ORACLE.COM> 


You can see from the autotrace that the CBO is used (whenever you see COST, that means CBO was involved)...

I said in that referenced note that you could delete the stats and (this was assumed) given that the optimizer_goal was left to its default of CHOOSE -- rbo will be used.  (they did say "Also, we are currently using the default CHOOSE.", meaning if you delete stats, RBO is used.  Have stats, CBO is used)

I see nothing in there to contradict what is said here.

If your optimizer goal is set to something OTHER THEN CHOOSE OR RULE -- the CBO will be used regardless.

If the optimizer goal is set to RULE, the RBO will be used for all queries EXCEPT those that access objects that require the CBO (partitioned tables, tables with non-default degrees of parallelism, IOTs, etc)

If the optimizer goal is set to CHOOSE, then CBO will be used for all queries that touch AT LEAST one table that was analyzed (or access an object that requires the CBO as above).  RBO will be used otherwise.

 

SQL Optimization Techniques

Shajan Joseph, April 30, 2002 - 10:42 am UTC

Useful and Informative

Thanks Tom

Is setting optimizer_goal not supported anymore in 10g?

Su Baba, November 19, 2003 - 12:59 pm UTC

We found in our environment after we migrated our application to Oracle 10g, one of the the PL/SQL pacakage returns the following error:

alter session set optimizer_goal = first_rows
*
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete


Is this not supported anymore in 10g? How do we resolve this problem?

thanks



Tom Kyte
November 21, 2003 - 11:43 am UTC

ops$tkyte@ORA10G> alter session set optimizer_goal = first_rows
   2  /
alter session set optimizer_goal = first_rows
                                 *
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete


ops$tkyte@ORA10G> c/goal/mode
  1* alter session set optimizer_mode = first_rows
ops$tkyte@ORA10G> /

Session altered.
 

A reader, November 26, 2003 - 3:05 am UTC


Helpful

Paresh N., December 01, 2003 - 9:22 am UTC

it was quiet helpful still require more descriptive

Very Useful

Riyaz K, December 11, 2003 - 12:56 pm UTC

It was very useful and informative

what's the cbo doing here?

Anthony, May 16, 2004 - 9:50 pm UTC

Tom,
Doing some testing on 10g and noticed something "odd".
I figured it might not be a big deal so i'm posting it here.

10g on an hp laptop running redhat AS 3.0:

explain plan for
select z.ename,z.obj
from (
select e.ename,
( select empno||hiredate||sal from emp d where e.empno=d.empno ) obj
from emp e
) z;


select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3251399651

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 16 | 14 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 14 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("D"."EMPNO"=:B1)

which is cool, but...

create type x as object (a number, b date, c number);
/

explain plan for
select z.ename,z.obj.a,z.obj.b,z.obj.c
from (
select e.ename,
( select x(empno,hiredate,sal) from emp d where e.empno=d.empno ) obj
from emp e
) z;

select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3251399651

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 16 | 14 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 16 | 14 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 16 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 140 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("D"."EMPNO"=:B1)
2 - filter("D"."EMPNO"=:B1)
3 - filter("D"."EMPNO"=:B1)

I realize the cbo may sometimes take "unorthodox" paths,
(such as a cartesian product) to get an answer, but I'm
kinda baffled as to why it chose to pick a path that
results in 316 reads.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
316 consistent gets
0 physical reads
0 redo size
935 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

Where did 4 emp tables come from?
The relational algebra states that a select ( or project )
on a relation results in a relation, so does the object
type in the sql cause an implicit cast and the cbo treats
the object type as a table itself, along with the table
in the scalar subquery and the outer table? Even if it did,
that would be 3 tables, not 4. Any ideas?

thanks,
ant

Tom Kyte
May 17, 2004 - 7:26 am UTC

looks to be an issue with explain plan, not with the query plan itself, tkprof the source of truth says:

select z.ename,z.obj
from (
select e.ename,
( select empno||hiredate||sal from emp d where e.empno=d.empno ) obj
from emp e
) z

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 46 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 46 0 14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 60

Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL EMP (cr=42 pr=0 pw=0 time=309 us)
14 TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=57 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************

select z.ename,z.obj.a,z.obj.b,z.obj.c
from (
select e.ename,
( select x(empno,hiredate,sal) from emp d where e.empno=d.empno ) obj
from emp e
) z

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 46 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 46 0 14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 60

Rows Row Source Operation
------- ---------------------------------------------------
14 TABLE ACCESS FULL OBJ#(51588) (cr=4 pr=0 pw=0 time=58 us)



it does the same thing (same work, 46 consistent gets in my case)... explain plan for whatever reason is confused by the object type.


for what it is worth, so is 9ir2

tkprof is correct, but number of blocks is high

anthony, May 17, 2004 - 11:52 pm UTC

tom,
ran tkprof on a 10046 trace and the reads were over 300.
I then checked the table and it had a lot of rows which were
deleted. So, the 14 row emp table was using 52 8k blocks.
I recreated it and the results were similar to yours.

One strange thing is, after recreating the table and analyzing it, USER_TABLES thinks the table is made up of 11 blocks. The tablespace uses assm and a 4mb uniform extent size (with 8k blocks and minextents=1) for 512 total blocks.
Where do the 11 blocks in user_tables come from though?
There can't be 11 8k blocks in use for the 14 row emp table.

I checked dbms_space.space_usage and that showed only one block in use, but dbms_space.unused_space showed 501 unused blocks. Is there an error in USER_TABLES and unused_space or are there really 11 blocks in use?



Tom Kyte
May 18, 2004 - 11:29 am UTC

ASSM does not do things contigously, the table is really 11 blocks below the true HWM, might not have 11 formatted blocks though. It is the very nature of ASSM.




optimizer_mode=all_rows affects "select * from dict" in 9i

Dan, October 18, 2004 - 5:14 pm UTC

Hi Tom,

why can this happen when we moved to 9i (9.2.0.4)? If we force to use hint like this

select /*+ choose */ * from dict;
or,
alter session set optimizer_mode=choose;

then it runs fast. The dictionary tables don't have stats.

Thanks.

Tom Kyte
October 19, 2004 - 8:46 am UTC

optimizer_mode = all_rows in all releases forced the use of the CBO.

The CBO without stats is like a morning without coffee. A really bad idea (for me anyway).


using CHOOSE let the optimizer fall back to the RBO -- which is what you use when you have no statistics.

Dan, October 19, 2004 - 2:14 pm UTC

It's a data warehouse database and all_rows provides pretty good performance for general queries so far. The dictionary tables under sys are the only set of tables without stats. Is there any other problems we may see with the all_rows set at the instance level?

Thank you very much!

Tom Kyte
October 19, 2004 - 2:45 pm UTC

If you use the CBO -- all_rows is pretty much the way CHOOSE works.

So, I would leave it at choose and make sure all of the tables I wanted to use the CBO on (your data warehouse tables) had statistics.

Done.

optimizer_mode=all_rows, but first_rows is used ???

Georgi Mechev, November 04, 2004 - 11:16 am UTC

Hi Tom,

I set optimizer_mode to all_rows and I have 'fresh' statistics (9.2.0.4 EE). But for one of our sql statements(huge select with more than 10 joints and many tables involved ) I can see (by tracing and also by OEM) that optimizer uses first_rows. Why? The statement runs for hours. If I use hint /*+all_rows*/ it runs for 1 sec. But why all_rows mode is not used by default - it is set in init.ora?
Many thanks in advance!


Tom Kyte
November 05, 2004 - 1:00 pm UTC

prove it to me.

show me the steps in sqlplus -- cut and paste

show the setting of optimizer_mode, run the query and show me the first_rows output in tkprof.



prove

Georgi Mechev, November 08, 2004 - 4:56 am UTC

Hi Tom,

As you requested - 
============
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS


Then I enable a tracing
SQL> execute sys.dbms_system.set_sql_trace_in_session(10,7,true);

PL/SQL procedure successfully completed.

The execution hangs - I kill the session and format the trace file with tkprof
tkprof <file name> out.log

The outout is too big to post it here - this is just one piece from it.
There are few lines with 
Optimizer goal: CHOOSE, one with 
Optimizer goal: RULE and at the end


Optimizer goal: FIRST_ROWS
Parsing user id: 23

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0   NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0    NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0     NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0      NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0       NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0        NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0         NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0          NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0           NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0            NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0             NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0              TABLE ACCESS BY INDEX ROWID OBJ#(9498) (cr=0 r=0 w=0 time=0 us)
  46618               INDEX FULL SCAN OBJ#(9503) (cr=263 r=263 w=0 time=441579 us)(object id 9503)
      0              TABLE ACCESS BY INDEX ROWID OBJ#(8065) (cr=0 r=0 w=0 time=0 us)
      0               INDEX RANGE SCAN OBJ#(8068) (cr=0 r=0 w=0 time=0 us)(object id 8068)
      0             TABLE ACCESS BY INDEX ROWID OBJ#(11248) (cr=0 r=0 w=0 time=0 us)
      0              INDEX UNIQUE SCAN OBJ#(11251) (cr=0 r=0 w=0 time=0 us)(object id 11251)
      0            TABLE ACCESS BY INDEX ROWID OBJ#(11541) (cr=0 r=0 w=0 time=0 us)
      0             INDEX RANGE SCAN OBJ#(11543) (cr=0 r=0 w=0 time=0 us)(object id 11543)
      0           TABLE ACCESS BY INDEX ROWID OBJ#(9381) (cr=0 r=0 w=0 time=0 us)
      0            INDEX UNIQUE SCAN OBJ#(9384) (cr=0 r=0 w=0 time=0 us)(object id 9384)
      0          TABLE ACCESS BY INDEX ROWID OBJ#(7962) (cr=0 r=0 w=0 time=0 us)
      0           INDEX RANGE SCAN OBJ#(7965) (cr=0 r=0 w=0 time=0 us)(object id 7965)
      0         VIEW PUSHED PREDICATE  (cr=0 r=0 w=0 time=0 us)
      0          NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0           NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0            TABLE ACCESS BY INDEX ROWID OBJ#(34898) (cr=0 r=0 w=0 time=0 us)
      0             INDEX UNIQUE SCAN OBJ#(34903) (cr=0 r=0 w=0 time=0 us)(object id 34903)
      0            TABLE ACCESS BY INDEX ROWID OBJ#(35104) (cr=0 r=0 w=0 time=0 us)
      0             INDEX UNIQUE SCAN OBJ#(35106) (cr=0 r=0 w=0 time=0 us)(object id 35106)
      0           TABLE ACCESS BY INDEX ROWID OBJ#(35117) (cr=0 r=0 w=0 time=0 us)
      0            INDEX UNIQUE SCAN OBJ#(35119) (cr=0 r=0 w=0 time=0 us)(object id 35119)
      0        TABLE ACCESS BY INDEX ROWID OBJ#(11581) (cr=0 r=0 w=0 time=0 us)
      0         INDEX RANGE SCAN OBJ#(11584) (cr=0 r=0 w=0 time=0 us)(object id 11584)
      0       VIEW PUSHED PREDICATE  (cr=0 r=0 w=0 time=0 us)
      0        NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0         NESTED LOOPS  (cr=0 r=0 w=0 time=0 us)
      0          NESTED LOOPS  (cr=0 r=0 w=0 time=0 us)
      0           TABLE ACCESS BY INDEX ROWID OBJ#(7837) (cr=0 r=0 w=0 time=0 us)
      0            INDEX UNIQUE SCAN OBJ#(7839) (cr=0 r=0 w=0 time=0 us)(object id 7839)
      0           TABLE ACCESS BY INDEX ROWID OBJ#(10669) (cr=0 r=0 w=0 time=0 us)
      0            INDEX UNIQUE SCAN OBJ#(10672) (cr=0 r=0 w=0 time=0 us)(object id 10672)
      0          TABLE ACCESS BY INDEX ROWID OBJ#(8862) (cr=0 r=0 w=0 time=0 us)
      0           INDEX UNIQUE SCAN OBJ#(8865) (cr=0 r=0 w=0 time=0 us)(object id 8865)
      0         TABLE ACCESS BY INDEX ROWID OBJ#(8076) (cr=0 r=0 w=0 time=0 us)
      0          INDEX RANGE SCAN OBJ#(8079) (cr=0 r=0 w=0 time=0 us)(object id 8079)
      0      VIEW PUSHED PREDICATE  (cr=0 r=0 w=0 time=0 us)
      0       NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
      0        NESTED LOOPS  (cr=0 r=0 w=0 time=0 us)
      0         NESTED LOOPS  (cr=0 r=0 w=0 time=0 us)
      0          TABLE ACCESS BY INDEX ROWID OBJ#(7837) (cr=0 r=0 w=0 time=0 us)
      0           INDEX UNIQUE SCAN OBJ#(7839) (cr=0 r=0 w=0 time=0 us)(object id 7839)
      0          TABLE ACCESS BY INDEX ROWID OBJ#(10669) (cr=0 r=0 w=0 time=0 us)
      0           INDEX UNIQUE SCAN OBJ#(10672) (cr=0 r=0 w=0 time=0 us)(object id 10672)
      0         TABLE ACCESS BY INDEX ROWID OBJ#(8862) (cr=0 r=0 w=0 time=0 us)
      0          INDEX UNIQUE SCAN OBJ#(8865) (cr=0 r=0 w=0 time=0 us)(object id 8865)
      0        TABLE ACCESS BY INDEX ROWID OBJ#(8076) (cr=0 r=0 w=0 time=0 us)
      0         INDEX RANGE SCAN OBJ#(8079) (cr=0 r=0 w=0 time=0 us)(object id 8079)
      0     TABLE ACCESS BY INDEX ROWID OBJ#(7392) (cr=0 r=0 w=0 time=0 us)
      0      INDEX RANGE SCAN OBJ#(7395) (cr=0 r=0 w=0 time=0 us)(object id 7395)
      0    TABLE ACCESS BY INDEX ROWID OBJ#(9188) (cr=0 r=0 w=0 time=0 us)
      0     INDEX RANGE SCAN OBJ#(9193) (cr=0 r=0 w=0 time=0 us)(object id 9193)
      0   TABLE ACCESS BY INDEX ROWID OBJ#(9188) (cr=0 r=0 w=0 time=0 us)
      0    INDEX RANGE SCAN OBJ#(9193) (cr=0 r=0 w=0 time=0 us)(object id 9193)

I have at the end of the sql statement order by clause.
If I comment order by clause, the statement runs for 2-3 msec. With order by clause, but also with hint /*+all_rows*/ it runs also for 2-3 msec. But without hint, it hangs - even with optimizer_mode=all_rows in init.ora
Probably this info is not enough  - I understand that, but maybe it is not good idea to post here 4000 lines trace file or 800 lines formated with tkprof file. If you need some exact parts of the trace file, I will poost them.

Once again many thanks in advance.






 

Tom Kyte
November 08, 2004 - 4:34 pm UTC

No, I wanted to see the query and all -- the entire thing.

Show me a session with optimizer_goal = all_rows

and the query that runs with first_rows optimization all by itself.


because -- that other session, it could have easily issued "alter session", or it could have easily hinted the query.


So, you haven't shown that that is not happening.

Georgi Mechev, November 10, 2004 - 9:51 am UTC

Hi Tom

I found the reason - the select statements uses views which select other views,which select other
views and so on - a bit complicated architecture with few 'view layers'. One of the views in the lower layers uses hint 'first_rows' in select clause and this changes
the optimizer mode for some of the other view also. After removing the hint everything run OK. So, it seems that this was the reason.
Thanks for the time spent and best regards.


It's all about first_rows, all_rows and choose

shailesh saraff, May 20, 2005 - 8:28 am UTC

Hi Tom,

I am puzzled with optimizer first_rows, all_rows and choose, this thread has helped me to understand few things, but I have some queries.

Recently we have migrated our database from Oracle 8i to 10g and found following. Same query, same data stucture and data, but query executions plans are different on Oracle 8i and on 10g. On 8i we are using OPTIMIZER_MODE = CHOOSE and on 10g OPTIMIZER_MODE = all_rows which is default optimizer of 10g. Resultset on both the database versions are same, but since execution plans are different, default sorting is not happening on 10g. So when we see our application we observe difference in sorting. Below I have given query and indexes on respective tables, plan on different versions.

Indexes: XIE1MASTERTABLE - (VORGANGSID, LEISTUNG) and XPKKATALOGTABLE - CODE

SELECT MASTERTABLE.EINRICHTUNG, MASTERTABLE.ITBRowID, ZAEHLER, LEISTUNG, ARTIKEL, BEZEICHNUNG, VERABREICHTDATUM, VERABREICHZEIT, ANZAHL, MENGENEINHEIT, DOSIS, DOSIERUNGSEINHEIT,
VERABREICHUNGSART, CHARGENNREINGZWANG, HOSTTRANSAKTIONSID, IstZuUebertragen, MASTERTABLE.PREIS, VERBRAUCHSORT
FROM MASTERTABLE, KATALOGTABLE
WHERE MASTERTABLE.ARTIKEL = CODE (+) AND VORGANGSID = 3769
AND MASTERTABLE.EINRICHTUNG = 'Ein1' ;

On Oracle 8i:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=23 Bytes=2139)
1 0 NESTED LOOPS (OUTER) (Cost=28 Card=23 Bytes=2139)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (Cost=5 Card=23 Bytes=1035)
3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (NON-UNIQUE) (Cost=3 Card=23)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'KATALOGTABLE' (Cost=1 Card=2309 Bytes=110832)
5 4 INDEX (UNIQUE SCAN) OF 'XPKKATALOGTABLE' (UNIQUE)

----------------------------------------------------------------------

On Oracle 10g:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=22 Bytes=2046)
1 0 HASH JOIN (OUTER) (Cost=16 Card=22 Bytes=2046)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (TABLE) (Cost=5 Card=22 Bytes=990)
3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (INDEX) (Cost=3 Card=22)
4 1 TABLE ACCESS (FULL) OF 'KATALOGTABLE' (TABLE) (Cost=10 Card=2309 Bytes=110832)


But when I set 'ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS' on 10g, query changed plan as shown below.

SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=22 Bytes=2046)
NESTED LOOPS (OUTER) (Cost=29 Card=22 Bytes=2046)
TABLE ACCESS (BY INDEX ROWID) OF TBLVERBRAUCH (TABLE) (Cost=7 Card=22 Bytes=990)
INDEX (RANGE SCAN) OF XPKVERBRAUCH (INDEX (UNIQUE)) (Cost=3 Card=22)
TABLE ACCESS (BY INDEX ROWID) OF KATARTIKEL (TABLE) (Cost=1 Card=1 Bytes=48)
INDEX (UNIQUE SCAN) OF XPKKATARTIKEL (INDEX (UNIQUE)) (Cost=0 Card=1)

and result was sorted as it is on Oracle 8i.
Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g? Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER).

We can't go and change queries to sort explictely in thousand places in an application, so please guide us and share your experiences.

Thanks & Regards,

Shailesh


Tom Kyte
May 20, 2005 - 10:26 am UTC

there is NO SUCH THING as a default sort, never has been. never will be.

the only way you can expect to get ordered data is to ORDER BY


sorry -- in 8i, your data can come back unsorted as well.


YOU HAVE TO FIX THE BUG IN THE CODE, the 8i code is broken as well.


this never worked right if you expected the data to be in any sort of sort order, NEVER.

shailesh saraff, May 24, 2005 - 1:33 am UTC

Hi Tom,

Ok fine. Let's ignore sorting, but why same data structure has different explain plan on 8i and 10g? and to maintain same query plans of 8i on 10g what we need to do? Changing queries all over is certainly not the practical option.

Thanks & Regards,

Shailesh

Tom Kyte
May 24, 2005 - 7:48 am UTC

because the same query could have a different plan today on 8i as well -- 7.0 even.

Group by NOSORT has been an option for a long long long long time.


You do not need, want, desire -- the same plans.


In this case, I'm sorry -- but it is rather plain -- you have a bug in your developed code. Your bug is that you are expecting for some reason that rows will come from a query in some predicable order WITHOUT AN ORDER BY. You never had any reason to expect that. If it worked (and I bet if you go back to 8i, you'll find at least one case where it isn't working as you expected only no one said anything), it was purely by accident.

There is nothing that can be done by you other than to fix the bug in the developed code. You have built a house of cards on sand -- it is not a very stable foundation. During this upgrade process, you must fix this. A simple gather statistics on your 8i system can and ultimately will produce the same behavior.

This is not about a version change, this is about a bug in your queries -- without an ORDER BY -- there is not any order to the rows, none that you can expect.

Query

shailesh saraff, June 02, 2005 - 8:20 am UTC

Hello Tom,

Thanks for all your help. I have few queries, kindly take sometime to answer them.

On Oracle 10g we set optimizer mode=first_rows and run following query, it takes 209 seconds, plan shows that query used index on column <MovementsDate> where as if we set optimizer mode=all_rows then same query execute in milli seconds and uses index on column <FallId>. This is what is expected.

Many of our OLTP application queries may affect with performance when optimizer mode=first_rows. What shall I do to avoid this. If I remove ORDER BY clause then it works properly.

All details are given below.

SELECT ParentOE
FROM TblMovement
WHERE ParentOE IS NOT NULL AND Fallid = :1
AND MovementsDate <= :2
ORDER BY MovementsDate Desc

First Rows:
-------------

call count cpu elapsed disk query current rows
------- ------ ------ ------- ----- ----- ----- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 8.48 209.62 48494 1002335 0 2
------- ---- ------- --------- -------- -------- ---- -----
total 3 8.48 209.63 48494 1002335 0 2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 52

Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID TBLMOVEMENT (cr=1002335 pr=48494 pw=0 time=106281 us)
1720211 INDEX RANGE SCAN DESCENDING XIE2TBLMOVEMENT (cr=6419 pr=6415 pw=0 time=37844652 us)(object id 48934)


SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 13165
TABLE ACCESS BY INDEX ROWID SYSADM.TBLMOVEMENT 1 14 13165
INDEX RANGE SCAN DESCENDING SYSADM.XIE2TBLMOVEMENT 22 K 87

-----------------------------------------------------------
ALL_ROWS:
----------

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 6
SORT ORDER BY 1 14 6
TABLE ACCESS BY INDEX ROWID SYSADM.TBLMOVEMENT 1 14 5
INDEX RANGE SCAN SYSADM.XIE1TBLMOVEMENT 2 3


Since we have migrated from 8i to 10g, currently issuing following statement solves purpose and query returns result in milli seconds and uses required index.

alter session set OPTIMIZER_FEATURES_ENABLE='8.1.7'

But is this recommanaded? will this have any effects? we may have to check. By seeting 8.1.7 we will miss enahancement in optimizer for 10g. We also checked v$parameter when we set this value optimizeer_mode is set to CHOOSE which was our 8i optimizer.

Could you please help us.

Thanks & Regards,

Shailesh



Tom Kyte
June 02, 2005 - 4:45 pm UTC

it is trying to use the index to avoid the sort, but that index isn't very good for avoiding that sort in this case.

10g statistics

A reader, September 09, 2006 - 11:21 am UTC

Hi Tom,
in 10G when optimizer_mode ALL_ROWS
and there are no statistics for all tables

you said it will also use CBO

does that mean it will gather statistics on the fly ?
is there any specail settings needed to enable the on the fly gathering statistics ?

thanks

Tom Kyte
September 09, 2006 - 12:28 pm UTC

yes, it uses dynamic sampling when you hard parse.. Nothing need be done in 10g to enable this, it is enabled by default.



first_rows

A reader, April 12, 2007 - 6:09 am UTC

Tom this is a snippet from oracle documentation regarding first_rows hint.

The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

in bold it clearly says that oracle will fetch the first n rows (for e.g.) for a query.

my question is
when i do this
select /*+first_rows(10)*/ * from emp where deptno=10;

then why i am getting rows returned is more than 10.

hope my query is clear

ALL_ROWS <> FIRST_ROWS Plan

Stan, April 04, 2008 - 11:24 am UTC

Hi Tom,

Consider:
Scenerio 1) Optimizer set to ALL_ROWS... Run SELECT Query
Scenerio 2) Optimizer set to FIRST_ROWS... Run same SELECT Query

All other things being equal (stats, etc.) and assuming buffers are empty at the start of each query, by the time the LAST row is returned by Oracle the amount of work (CPU, Elapsed, Reads, etc.) done by Oracle for scenerio 2 should always be greater than or equal to secenerio 1? If not please explain.

Thanks in advance.
Tom Kyte
April 04, 2008 - 12:36 pm UTC

In theory, scenario 2 should consume at least as much if not more (greater than or equal to) resources than scenario 1 to return all rows.


In theory, in practice there are undoubtedly cases where that is not true (else the optimizer would be 100% perfect and we'd never talk about it)

RE: first_rows (from April 12, 2007 - 6am US/Eastern)

Duke Ganote, April 04, 2008 - 2:27 pm UTC

Regarding the question above
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3310002340673#216183400346866471
"why i am getting rows returned is more than 10" for this query:
select /*+first_rows(10)*/ * from emp where deptno=10; "

Because "the plan that returns the first n rows most efficiently" will still return all rows requested by the query.

ALL_ROWS <> FIRST_ROWS Plan - Another Question

Stan, April 04, 2008 - 3:07 pm UTC

Excellent, thanks.

Since my assumption was "in theory" correct, in the case where there is a DELETE, UPDATE, or INSERT which is based on result set from a select like:

delete from a where col in (select col from tab1, tab2, etc.)

It seems in those statments you would ALWAYS want ALL_ROWS regardless since you don't "move on" until your work is done. Will the optimizer setting of ALL_ROWS or FIRST_ROWS effect this plan?

Thanks again in advance.
Tom Kyte
April 04, 2008 - 10:16 pm UTC

it can - yes.

optimizer_mode in 9iR2

Rajeshwaran, Jeyabal, June 15, 2010 - 12:58 pm UTC

Tom:

<quote>
The ALTER SESSION... SET OPTIMIZER_MODE statement does not affect SQL that is run from within PL/SQL.
</quote>
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#12158

test@PWTK52> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

test@PWTK52> show parameter optimizer_mode;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
optimizer_mode                       string      CHOOSE

test@PWTK52> alter session set optimizer_mode=all_rows;

Session altered.

test@PWTK52> alter session set timed_statistics=true;

Session altered.

test@PWTK52> alter session set events '10046 trace name context forever, level 12';

Session altered.

test@PWTK52> begin
  2     for r in (select manager_id from dept )
  3     loop
  4             dbms_output.put_line (r.manager_id);
  5     end loop;
  6  end;
  7  /
200
201
114
203
121
103
204
145
100
108
205

















PL/SQL procedure successfully completed.


Here is my Tkprof output.
********************************************************************************

begin
 for r in (select manager_id from dept )
 loop
  dbms_output.put_line (r.manager_id);
 end loop;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 107  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

SELECT MANAGER_ID 
FROM
 DEPT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       28      0.00       0.00          1         29          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       30      0.00       0.00          1         29          0          27

Misses in library cache during parse: 1
Optimizer mode: 
ALL_ROWS
Parsing user id: 107     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  TABLE ACCESS FULL DEPT 


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
********************************************************************************


As per documentation why the ALTER SESSION... OPTIMIZER_MODE parameter value affects SQL that is run from PL/SQL ? Am i doing things wrong here Tom? please correct me, if i am wrong.
Tom Kyte
June 22, 2010 - 10:25 am UTC

that was changed (in 9i) - plsql does reflect the optimizer mode, the documentation was incorrect.

that statement does not appear in current documentation.

First rows hint

Manu Shaalya, August 13, 2010 - 3:09 am UTC

Hello,

Is there a default value for the integer that has to be specified during the first_rows hint?
What is it?

Thanks in advance.
Tom Kyte
August 19, 2010 - 12:30 am UTC

first_rows is different than first_rows(n)

you should use first_rows(n) and supply some value.

first_rows is sort of like a "rule based cost based hint", whereas first_rows(n) is a true "cost based optimization"

see
http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/

first_rows_n doubt

Biswaranjan, June 05, 2012 - 3:19 am UTC

Hi Tom,

I was trying to understand first_rows_1 ,first_row and all_rows .
I did something like below and have some doubt. I know this might be 
a silly doubt but i dont know about the concept.
Table a contains index column 'col1' and orhters are normal column.
I just create the table using "create table a select level,level+1,level+10 from dual connect
by level<79999993;"

I have gatherd the statistics for the table "a".



SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 COL1                                               NUMBER
 COL2                                               NUMBER
 COL3                                               NUMBER

SQL> select count(*) from a;

  COUNT(*)
----------
  79999992

Elapsed: 00:00:26.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3918351354

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 57738   (5)| 00:11:33 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| A    |    80M| 57738   (5)| 00:11:33 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     252859  consistent gets
     252836  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> show parameter optimizer_mode;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_1
SQL> select * from a where col2=2 or col2=74567345;   --i even tried using /*+ first_rows(1) */

      COL1       COL2       COL3
---------- ---------- ----------
         1          2         11
  74567344   74567345   74567354

Elapsed: 00:00:19.62

Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    34 | 29696   (7)| 00:05:57 |
|*  1 |  TABLE ACCESS FULL| A    |     2 |    34 | 29696   (7)| 00:05:57 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=2 OR "COL2"=74567345)


Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
     252866  consistent gets
     211112  physical reads
          0  redo size
        575  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

My doubt is even I have set the optimizer_mode to first_rows_1 ,why the below output comes
at a time.
      COL1       COL2       COL3
---------- ---------- ----------
         1          2         11
  74567344   74567345   74567354
(I mean to say the the first row should be displayed when in full scan it gets it(col2=2) and then
the second row will be displayed when it gets the second record(col2=74567345) ).

I have read somewhere first_rows favours index scan ,is that the issue??

I also read the link http://hoopercharles.wordpress.com/2011/03/10/what-is-the-difference-between-the-first_rows-hint-and-rownum-in-the-where-clause/
and understood how the cardinality changes accordingly the first_rows(n) but my doubt is why in my above example
both rows comes at a time (instead the first found row should displayed first and after sometime the second row should be displayed 
).


Can you please guide me where I am missing (i know i understood and implemented worngly but dont know where) and
please give me an example how I will make use of this first_rows_1 things.

thanks as always,
Biswaranjan

Tom Kyte
June 05, 2012 - 10:18 am UTC

the first_row(1) hint is not a "limit" or a filter.

It simply says "optimize this query as if the program fetching from the database will retrieve just the first row"


Your program would then retrieve only one row (if that is what it wanted to do). the entire answer is ALWAYS going to be available if the program wants to retrieve it.


So, when and why would you use this hint? You would use it when you are building an interactive application and that application needs to get the first N rows out from the database as fast as possible for the end user.

Suppose your application needs to get 25 rows from a query and display them as soon as possible and the query is:

select * from big_table order by indexed_not_null_column;


Now, the fastest way to get the first 25 records from this query would be by using the index.

Conversely - the slowest way to get ALL of the records from this query would be by using the index.

By default, we optimize queries using all rows optimization - which means BY DEFAULT we might full scan, sort order by and then return data. That would take a long time to get the first 25 records - but the rest of the rows would be really fast.

If we tell the optimizer "first_rows(25)" - optimize to get the first 25 as fast as possible - we will use an index full scan, table access by index rowid. That'll get the first 25 records instantly - but it would take a really long time to get all of the rows out via the index.


So, for an interactive application -first_rows(n) can make a lot of sense. for a batch program or a stored procedure, all_rows makes sense.


for example, big_table in the following is a 100,000 row copy of all objects with a new primary key ID:

big_table%ORA11GR2> set autotrace traceonly explain
big_table%ORA11GR2> 
big_table%ORA11GR2> select *
  2    from big_table1 t1, big_table t2
  3   where t1.id = t2.id
  4     and t1.owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3103639499

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            | 47562 |  9196K|       |  1315   (1)| 00:00:16 |
|*  1 |  HASH JOIN         |            | 47562 |  9196K|  5160K|  1315   (1)| 00:00:16 |
|*  2 |   TABLE ACCESS FULL| BIG_TABLE1 | 47562 |  4598K|       |   135   (2)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| BIG_TABLE  |   100K|  9667K|       |   403   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."ID")
   2 - filter("T1"."OWNER"='SYS')

big_table%ORA11GR2> 
big_table%ORA11GR2> select /*+ first_rows(25) */ *
  2    from big_table1 t1, big_table t2
  3   where t1.id = t2.id
  4     and t1.owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 4055765821

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |    25 |  4950 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |               |       |       |            |          |
|   2 |   NESTED LOOPS               |               |    25 |  4950 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | BIG_TABLE     |    53 |  5247 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | BIG_TABLE1_PK |     1 |       |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE1    |     1 |    99 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."ID"="T2"."ID")
   5 - filter("T1"."OWNER"='SYS')

big_table%ORA11GR2> 
big_table%ORA11GR2> set autotrace off



Note how it would like to be a full scan/full scan and a hash join, that is the fastest way to get ALL of the rows

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894

shows that...

but, if we say "get the first 25 rows as fast as possible" - we get indexes and nested loops.

But if we ran that second query to completion - it would take a really long time - just like the link shows.

forgot to mention version

Biswaranjan, June 05, 2012 - 3:27 am UTC

oracle version is 10.2.0.1.0

continuation to my last post.

Biswaranjan, June 05, 2012 - 2:23 pm UTC

Hi Tom,

I just did as you said for testing(instead of all_objects i used below tables).

create table t1(a number primary key,b number,c number);
insert into t1 select level,level+1,level+2 from dual connect by level<1000001;
commit;

create table t2(a number primary key,b number,c number);
insert into t2 select level,level+1,level+2 from dual connect by level<1000001;
commit;

set autotrace traceonly

SQL> select *
  2        from  t1,  t2
  3       where t1.a = t2.a;

1000000 rows selected.

Elapsed: 00:00:15.99

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   488K|    36M|       |  3696   (2)| 00:00:45 |
|*  1 |  HASH JOIN         |      |   488K|    36M|    10M|  3696   (2)| 00:00:45 |
|   2 |   TABLE ACCESS FULL| T1   |   225K|  8598K|       |   617   (2)| 00:00:08 |
|   3 |   TABLE ACCESS FULL| T2   |   488K|    18M|       |  1332   (2)| 00:00:16 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."A"="T2"."A")


Statistics
----------------------------------------------------------
       1545  recursive calls
          1  db block gets
      42190  consistent gets
      14923  physical reads
        176  redo size
   39546156  bytes sent via SQL*Net to client
     733707  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
    1000000  rows processed



SQL> select /*+ first_rows(10) */ *
  2        from  t1,  t2
  3       where t1.a = t2.a;

1000000 rows selected.

Elapsed: 00:00:30.97

Execution Plan
----------------------------------------------------------
Plan hash value: 607555540

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   780 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    10 |   780 |     7   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | T2          |    10 |   390 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |    39 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C005632 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."A"="T2"."A")


Statistics
----------------------------------------------------------
       1492  recursive calls
          0  db block gets
    5138987  consistent gets
      10595  physical reads
          0  redo size
   39546156  bytes sent via SQL*Net to client
     733707  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
    1000000  rows processed
######################################################################

Now I understood why the optimizer choose different access path and nested ,hash etc for all_rows and first_rows_n.
(even to qickly display first 10 rows it can choose some different access path and logic, but for whole records to be displayed 
it will take much time if we use fist_rows hint).

you have cleard all of my  doubt regarding it.

But now I have one doubt which i already asked in my recent post (may be i have not represented it clearly).
Suppose I created a table like below.
create table one_by_one(a number,b number);
insert into  one_by_one select level,level+1 from dual connect by level<10000000;
commit;
NOte: I have not made either a or b column as primary key and not even created any indexes.

SQL> select * from one_by_one where rowid=(select max(rowid) from one_by_one);

         A          B
---------- ----------
   5233291    5233291

Elapsed: 00:00:02.84

For the recent data of one_by_one table I thought "5233291" is in the last table block.

SQL> select * from one_by_one where a=1 or a=5233291;

         A          B
---------- ----------
         1          1
   5233291    5233291

Elapsed: 00:00:04.36

Execution Plan
----------------------------------------------------------
Plan hash value: 1494747822

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 19108 |   485K|  5213   (2)| 00:01:03 |
|*  1 |  TABLE ACCESS FULL| ONE_BY_ONE | 19108 |   485K|  5213   (2)| 00:01:03 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=1 OR "A"=5233291)


Statistics
----------------------------------------------------------
        983  recursive calls
          0  db block gets
      23373  consistent gets
      23222  physical reads
          0  redo size
        506  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          2  rows processed
For the above query , My doubt is why in display after 4.36 seconds we get both those record(I mean to say
though first record with column "a" value "1" is in the first block so it should come as soon as we press 
enter after the query and when through table full scan it will read the last block it will then display the second record 
whose a column's value is "5233291")
Instead of this below output
         A          B
---------- ----------
         1          1
   5233291    5233291 

it should come like 
         A          B
---------- ----------
         1          1    --this should come immediate we press enter
   5233291    5233291     -----this record should come after sometime ,probably 4seconds later .


Is it the oracle keeps the output and display together one it done with full scan.

Sorry I asked it here though this page is for hint thing.

waiting for your nice explanation!!!!

oops ,forgot to say thank you to clearing my doubt and giving extra knowledge about first_rows.

thanks as always,
Biswaranjan.

Tom Kyte
June 05, 2012 - 5:34 pm UTC

because you are using sqlplus and sqlplus displays things in pages.


"oracle" the database didn't do this, sqlplus the client application that does ad-hoc queries for you in a very very simplistic fashion did this for you.


Sis it truly return the 1st row

Doug, June 05, 2012 - 6:49 pm UTC

Hi Tom,

In this example immediately above, since SQL*Plus pages results is there anywhere you can tell if the we can tell from SQL trace/tkprof output or something that the FIRST_ROWS goal was used?
Regards
Doug
Tom Kyte
June 05, 2012 - 6:55 pm UTC

look at the plan and see if the cardinalities were adjusted. That is how first_rows(n) works - it sets estimated cardinalities down.

continuation to my last post

Biswaranjan, June 06, 2012 - 1:51 am UTC

thanks you very much respected Tom.

Great Your two lines cleared every confusion from me .
:) .
Simply you rock.
regards,
Your fan.

A reader, June 06, 2012 - 11:15 pm UTC

Hi Tom,

have below question related to last poster demo

(1) "because you are using sqlplus and sqlplus displays things in pages" - is there is co-relation between array size and pages?

(2) Also why the autotrace statistics show "sort(memory)" for netsed loop and full table scan


Thanks

Tom Kyte
June 07, 2012 - 6:42 am UTC

(1)

SQL> set pagesize N


(2) see all of the recursive sql? It contributes to the autotrace statistics too. Probably from hard parsing the query.

Use of first rows

Jay, June 14, 2012 - 4:28 am UTC

Hi Tom,
Other than for cases wheere we have pagination, will first_rows improve performance?

Tom Kyte
June 14, 2012 - 5:55 am UTC

anytime it is more important to you to get the first row as soon as possible rather than the last row as fast as possible.


FIRST_ROW vs FIRST_ROWS(20)

Rajeshwaran, Jeyabal, November 21, 2012 - 8:38 am UTC

Tom,

I have a query fired from Web application search screen. When i say /*+ FIRST_ROWS */ i get results in 2 secs with plan_1. But when i say /*+ FIRST_ROWS(20) */ query runs for 10 mins. with plan_2.

Plan_1 - is full of Index range Scan and Nested loops.
Plan_2 - is full of Full Table scan and Hash Joins.

so why FIRST_ROWS(20) is slow? you want me to go FIRST_ROWS rather?

Plan_1
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                               |    20 |   162K|       |  1907K  (1)| 06:21:30 |       |       |
|   1 |  FAST DUAL                                  |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   2 |  FAST DUAL                                  |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   3 |  FAST DUAL                                  |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   4 |  FAST DUAL                                  |                               |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|*  5 |  COUNT STOPKEY                              |                               |       |       |       |            |          |       |       |
|   6 |   VIEW                                      |                               | 28630 |   227M|       |  1907K  (1)| 06:21:30 |       |       |
|*  7 |    SORT ORDER BY STOPKEY                    |                               | 28630 |     9M|    11M|  1907K  (1)| 06:21:30 |       |       |
|   8 |     NESTED LOOPS                            |                               | 28630 |     9M|       |  1905K  (1)| 06:21:04 |       |       |
|   9 |      NESTED LOOPS OUTER                     |                               | 28622 |  7155K|       |  1848K  (1)| 06:09:37 |       |       |
|  10 |       NESTED LOOPS OUTER                    |                               | 28622 |  6624K|       |  1782K  (1)| 05:56:25 |       |       |
|  11 |        NESTED LOOPS                         |                               | 28622 |  5394K|       |  1741K  (1)| 05:48:22 |       |       |
|  12 |         NESTED LOOPS                        |                               | 29867 |  5337K|       |  1711K  (1)| 05:42:24 |       |       |
|  13 |          NESTED LOOPS                       |                               | 46685 |  6701K|       |  1618K  (1)| 05:23:43 |       |       |
|  14 |           TABLE ACCESS BY GLOBAL INDEX ROWID| RV_PROJ_CONTENT               |   486K|    32M|       | 27045   (1)| 00:05:25 | ROWID | ROWID |
|* 15 |            INDEX RANGE SCAN                 | IDX_RV_PROJ_CONTENT_08        |   486K|       |       |  1516   (1)| 00:00:19 |       |       |
|* 16 |           TABLE ACCESS BY GLOBAL INDEX ROWID| RV_CHART_ENC                  |     1 |    77 |       |     4   (0)| 00:00:01 | ROWID | ROWID |
|* 17 |            INDEX RANGE SCAN                 | IDX_RV_CHART_ENC_01           |    10 |       |       |     2   (0)| 00:00:01 |       |       |
|* 18 |          TABLE ACCESS BY GLOBAL INDEX ROWID | RV_CHART                      |     1 |    36 |       |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 19 |           INDEX UNIQUE SCAN                 | PK_RV_CHART                   |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 20 |         TABLE ACCESS BY INDEX ROWID         | RV_HP                         |     1 |    10 |       |     1   (0)| 00:00:01 |       |       |
|* 21 |          INDEX UNIQUE SCAN                  | PK_RV_HP                      |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|  22 |        TABLE ACCESS BY GLOBAL INDEX ROWID   | RV_PROJ_CONTENT_EXTENT        |     1 |    44 |       |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 23 |         INDEX UNIQUE SCAN                   | IDX_RV_PROJ_CONTENT_EXTENT_01 |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  24 |       TABLE ACCESS BY GLOBAL INDEX ROWID    | RV_CHART_ENC_PROV             |     1 |    19 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 25 |        INDEX RANGE SCAN                     | IDX_RV_CHART_ENC_PROV_1       |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|  26 |      TABLE ACCESS BY GLOBAL INDEX ROWID     | RV_PROJ_CONTENT_PROVIDER      |     1 |   107 |       |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 27 |       INDEX UNIQUE SCAN                     | PK_RV_PROJ_CONTENT_PROVIDER   |     1 |       |       |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------------------


Plan_2
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                          |    20 |   162K|       |   264K  (1)| 00:52:55 |       |       |
|   1 |  FAST DUAL                                  |                          |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   2 |  FAST DUAL                                  |                          |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   3 |  FAST DUAL                                  |                          |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|   4 |  FAST DUAL                                  |                          |     1 |       |       |     2   (0)| 00:00:01 |       |       |
|*  5 |  COUNT STOPKEY                              |                          |       |       |       |            |          |       |       |
|   6 |   VIEW                                      |                          | 16907 |   134M|       |   264K  (1)| 00:52:55 |       |       |
|*  7 |    SORT ORDER BY STOPKEY                    |                          | 16907 |  5993K|  6456K|   264K  (1)| 00:52:55 |       |       |
|   8 |     NESTED LOOPS OUTER                      |                          | 16907 |  5993K|       |   263K  (1)| 00:52:39 |       |       |
|*  9 |      HASH JOIN                              |                          | 16907 |  5679K|  4112K|   224K  (1)| 00:44:52 |       |       |
|* 10 |       HASH JOIN OUTER                       |                          | 16903 |  3912K|  3384K|   205K  (1)| 00:41:10 |       |       |
|* 11 |        HASH JOIN                            |                          | 16903 |  3185K|  4368K|   186K  (1)| 00:37:15 |       |       |
|* 12 |         HASH JOIN                           |                          | 26421 |  4050K|    24M|   153K  (1)| 00:30:45 |       |       |
|* 13 |          HASH JOIN                          |                          |   275K|    21M|       | 27051   (1)| 00:05:25 |       |       |
|* 14 |           TABLE ACCESS FULL                 | RV_HP                    |    12 |   120 |       |     3   (0)| 00:00:01 |       |       |
|* 15 |           TABLE ACCESS BY GLOBAL INDEX ROWID| RV_PROJ_CONTENT          |   311K|    20M|       | 27045   (1)| 00:05:25 | ROWID | ROWID |
|* 16 |            INDEX RANGE SCAN                 | IDX_RV_PROJ_CONTENT_08   |   486K|       |       |  1516   (1)| 00:00:19 |       |       |
|  17 |          PARTITION LIST ALL                 |                          |  2433K|   178M|       |   115K  (2)| 00:23:03 |     1 |  1377 |
|* 18 |           TABLE ACCESS FULL                 | RV_CHART_ENC             |  2433K|   178M|       |   115K  (2)| 00:23:03 |     1 |  1377 |
|  19 |         PARTITION LIST ALL                  |                          |  3480K|   119M|       | 24307   (1)| 00:04:52 |     1 |  1030 |
|* 20 |          TABLE ACCESS FULL                  | RV_CHART                 |  3480K|   119M|       | 24307   (1)| 00:04:52 |     1 |  1030 |
|  21 |        PARTITION LIST ALL                   |                          |  2566K|   107M|       | 12568   (2)| 00:02:31 |     1 |  1377 |
|  22 |         TABLE ACCESS FULL                   | RV_PROJ_CONTENT_EXTENT   |  2566K|   107M|       | 12568   (2)| 00:02:31 |     1 |  1377 |
|  23 |       PARTITION LIST ALL                    |                          |  1121K|   114M|       | 12028   (1)| 00:02:25 |     1 |  1244 |
|  24 |        TABLE ACCESS FULL                    | RV_PROJ_CONTENT_PROVIDER |  1121K|   114M|       | 12028   (1)| 00:02:25 |     1 |  1244 |
|  25 |      TABLE ACCESS BY GLOBAL INDEX ROWID     | RV_CHART_ENC_PROV        |     1 |    19 |       |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 26 |       INDEX RANGE SCAN                      | IDX_RV_CHART_ENC_PROV_1  |     1 |       |       |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------

Tom Kyte
November 21, 2012 - 9:49 am UTC

what YOU can do (I'm not asking for this information, I'm telling you to do this and analyze the results) is look at the estimated cardinality versus the actual and see where they different by a large amount, that is where the optimizer "made a mistake"

then ask yourself - why did it make that mistake, am I missing some statistics, do I have non-representative statistics, do I need more statistics and so on - like this:

http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html


use this approach:

https://blogs.oracle.com/optimizer/entry/how_do_i_know_if

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library