RE: 54 is less than 4 for CBO ?

Hi Laimutis,
thanks for taking time and explaining in details your idea. Since the problematic SQL statement is running in a DWH databsase, I am getting 50% as s result of your query.

As Chris already pointed out, FIRTS_ROWS hint/optimizer mode is a special case, in which CBO is behaiving almost the same way as RBO (favouring NL and index accesses). All the other hints - FIRST_ROWS(1), FIRST_ROWS(10), FIRST_ROWS(100) didn't change the execution plan, since the FTS on T_FZG_SA was also getting cheaper.

Regards. Milen

I mean, the first_rows and and all_rows are *totally* different optimization goals which result in *totally* different optimization.

The question is what assumptions Oracle makes for the first_rows hint. Today(10g) first_rows is replaced by more selective hint, first_rows_n.

Anyway, it is one thing to access e.g. 30% of rows using an index and totally another thing to access 100% of rows using the same index. Index reads may result in disk reads (oracle assumes optimizer_index_caching=1% by default) and disk reads are *very expensive* (it can easily take 10 ms) Hence the penalty of using an index to access a large percentage of rows. It makes more sence to use all kinds of merges.

P.S.
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

The result is that 91% of queries have fetches/executions<2! Most probably this 91% means that 91% of queries makes one fetch per execution!

The all rows is more for DW environment. It seems Oracle became very biased towards this DW buzz, effectively sacrifizing OLTP needs.

Anyway, my rule is: all rows goal is evil application design for any environment.

--

Bis zu 70% Ihrer Onlinekosten sparen: GMX SmartSurfer!

      Kostenlos downloaden: http://www.gmx.net/de/go/smartsurfer     

--

http://www.freelists.org/webpage/oracle-l Received on Fri May 26 2006 - 04:47:32 CDT