FIRST_ROWS optimizer question 1

[theshowmecanuck]

I am trying to get some information on the FIRST_ROWS optimizer option in Oracle 9i.

I know that this option tells the optimizer to return the first row of the result set as fast as possible.

However, I know that in many product's cost based optimizers, that when the first rows type of option is used, that while the first row returns more quickly, the overall speed to return all the rows of the result set is generally slower. That is, slower than using the "normal" cost based optimization (in this case the "CHOOSE" option). And this is especially so if the result set is returning many thousands of rows (even up to a few hundred thousand in extreme cases).

Does anyone know if this is true for Oracle's FIRST_ROWS optimizer option?

BTW, the majority of the work on our server is pure OLTP with on-line CSM applications. So in general, the FIRST_ROWS option makes sense for us. But we do have a few batch type jobs operating on the instance as well.

Thanks,

BillR

 

[jimbopalmer]

As a general rule, First_rows works well in forms, All_rows works better in reports.

In Forms populating the first page means you can begin work, even if you do not have all rows yet. In Reports you cannot begin to use it till it all prints, no one cares that page one was faster.

I tried to remain child-like, all I acheived was childish.

 

[ChrisHunt]

"while the first row returns more quickly, the overall speed to return all the rows of the result set is generally slower"

Erm... That's rather the point isn't it? By issuing the first_rows hint you're saying "Get me the first row back quickly, I don't care (much) how long the whole set takes". If it didn't affect the overall time, or made it faster, they'd have the hint switched on all the time!

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[sem]

In fact you may change optimizer goal on database, session and even statement level, so you may just alter session before running batch job.

Regards, Dima

 

[theshowmecanuck]

This is for Chris...

We have a DBA who thinks otherwise and I wanted to hear some other opinions. I am also looking through documentation.

 

[dbtoo2001]

We have an application which uses FIRST_ROWS as its optimizer mode setting. We, sad to admit..., also run reports against the same instance. We have a specific userid which runs all of the reports (through Business Objects) which we created a trigger to set that users session optimizer goal to ALL_ROWS. Report execution reduced to seconds instead of minutes. It was not effective for all reports (75% of the time is was)! So we took off the trigger and put FIRST_ROWS in as hints to each report. (I ask the same question... Why didn't you just fix the 25% that were still slow? - sad isn't it?)

 

[jimbopalmer]

Yes a All_rows hint solves about 75% of all Tuning, In business Objects you can also put hints on user objects and on individual reports. they do not conflict

I tried to remain child-like, all I acheived was childish.

 

[sem]

If your "Report execution reduced to seconds instead of minutes", your statistics is probably not good enough. And of cours "more specific settings win", i.e. statement hint prevails over session and session - over instance.

Regards, Dima