Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 54 is less than 4 for CBO ?

RE: 54 is less than 4 for CBO ?

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Fri, 26 May 2006 12:09:02 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF3F5D5A@MSXVS04.trivadis.com>


Hi

>INHO, Oracle makes wrong assumption with all_rows hint
>as default. Most queries are never interested in all
>rows. I executed the bellow query on my production system:
>
>select round(100*count((case when fetches/executions<2 then 1
>else null end ))/count(1)) pct_first_1_rows from v$sqlarea
>where command_type=3 and executions>0

Be careful here.

  1. Some queries don't retrieve rows.
  2. Many applications do prefetching, i.e. a single fetch may retrieve dozen of rows!

To do such computations the column ROWS_PROCESSED it's better...  

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 26 2006 - 05:09:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US