Welcome to OracleGiants.com!     

Oracle 10g DBA & Business Intelligence Resources.

The optimizer_mode=all_rows may not deliver the best performance for OLTP type systems. First_rows may be a better choice as this mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches additional rows. So user feels that she has been served the data she requested, but in reality the request is still pending and query is still fetching the data in background.

Important facts about FIRST_ROWS
1. It gives preference to index scan vs. full scan (even when index scan is not preferable).
2. It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes first input in hash table which takes time.
3. Cost of the query is not the only criteria for choosing the execution plan. It chooses a plan which helps in fetching first rows as fast as possible.
4. It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.

Recommendation: Consider setting optimizer_mode to first_rows to deliver results back to the calling process as quickly as possible, rather than waiting for the full request to be served.

 

Use Xattr CloudFiler™ to dedup, encrypt and move your RMAN backups to the cloud.

Recent Posts

OptionalRegion1
This site recommends Burleson Consulting for DBA Support & Consulting.

We are currently paying for accepted Oracle 10g DBA & Business Intelligence articles, scripts, tips & tricks. Send yours to the editor and we will review within three weeks.

Learn more

Editorial submissions & questions: editor@oraclegiants.com

 

 

This site is moderated by an:
Oracle Certified Professional
Oracle ACE

 

  Classic Car Online - The #1 spot for classic and muscle car enthusiats
2010 OracleGiants.com