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: Milen Kulev <makulev_at_gmx.net>
Date: Mon, 22 May 2006 14:27:56 +0200 (MEST)
Message-ID: <18439.1148300876@www046.gmx.net>


Hi Chris,
 I have tried with first_rows_10 and first_rows_1 ( first_rows_10) or the optimizer is still using the old execution plan (first FTS on T_FZG_SA table). The interesting part is that using hint FIRST_ROWS_1 the CBO doesn't even try to recalculated the cost (effectively ignoring the hint)

explain plan for
SELECT /*+ first_rows_1 */ t_fzg_sa.fahrgestellnr_7, t_fzg_sa.vertriebsschluessel,

       t_fzg_sa.historien_zaehler, t_fzg_sa.fahrzeugart, 
       t_fzg_sa.fzg_produktionsdatum, t_fzg_sa.sa_bestelltyp, 
       t_fzg_sa.letzte_aenderung, t_fzg_sa.aenderungsart 
FROM DWH.t_fzg_sa, VDWH_LOAD.tt_vdwh_fgnr WHERE t_fzg_sa.fahrgestellnr_7 = tt_vdwh_fgnr.fahrgestellnr_7 ; select * from table
(dbms_xplan.display);

| Id | Operation | Name | Rows | Bytes | Cost |
Pstart| Pstop
|


| 0 | SELECT STATEMENT | | 916K| 39M| 32100 |
 |       |

| 1 | NESTED LOOPS | | 916K| 39M| 32100 |
| |
| 2 | PARTITION RANGE ALL| | | | |
1 | 14 |
| 3 | TABLE ACCESS FULL | T_FZG_SA | 303M| 10G| 32100 |
1 | 14 |
|* 4 | INDEX UNIQUE SCAN | SYS_C00146578 | 1 | 8 | |
|

|

Predicate Information (identified by operation id):


   4 - access("T_FZG_SA"."FAHRGESTELLNR_7"="TT_VDWH_FGNR"."FAHRGESTELLNR_7")

But if change optimized mode at session level (via "alter session set optimizer_mode=FIRST_ROWS_1";) then CBO is recalculating the cardinality (ORIG and COMPUTED) and costs. CBO is still choosing the bad execution plan due to the facts that FTS on T_FZG_SA now has Cost=3 ( it was 31200!!!)

In the 10053 event file I see the following lines (with optimizer_mode=FIRST_ROWS_1)
First K Rows: K/N ratio = 0,000001091131285, qbc=11020ea80 First K Rows: Setup end

Then the Cardinality of the table T_FZG_SA (303367558) is recalculated with this factor (K/N ratio) and I am getting:

SINGLE TABLE ACCESS PATH (First K Rows)
  TABLE: T_FZG_SA ORIG CDN: 3311 ROUNDED CDN: 3311 CMPTD CDN: 3311  Access path: tsc Resc: 2 Resp: 2
.....

instead of

SINGLE TABLE ACCESS PATH
  TABLE: T_FZG_SA ORIG CDN: 303367558 ROUNDED CDN: 303367558 CMPTD CDN: 303367558
  Access path: tsc Resc: 32100 Resp: 32100

And now the next bacth of questions (conserning "alter session set optimizer_mode=FIRST_ROWS_1" ):

1)Where the digit
First K Rows: K/N ratio = 0,000001091131285, qbc=11020ea80 is coming from ?

2) Why the FTS cost on T_FZG_SA is only 2 ? How this value is calculated

3) Why CBO is not reacting to my hint (as I see there is no type error) in  explain plan for
SELECT /*+ first_rows_1 */ t_fzg_sa.fahrgestellnr_7 ....

Otherwise I must admit that first_rows_1 optimized has (obviously) very litte with
 first_rows_1 , first_rows_10 etc .

My only option at the moment is to use first_rows hint instead of RULE hint (although there is no difference in the logic of the optimizer in this case) . The table T_FZG_SA is partioned so optimizer switches anyway to CBO.

Regards. Milen

> --- Ursprüngliche Nachricht ---
> Von: "Christian Antognini" <Christian.Antognini_at_trivadis.com>
> An: <makulev_at_gmx.net>
> Kopie: <oracle-l_at_freelists.org>
> Betreff: RE: 54 is less than 4 for CBO  ?
> Datum: Mon, 22 May 2006 12:58:34 +0200
> 
> Milen
> 
> >But how is is possible to ignore the fact that cost = 54 (index acces) is
> >less than  cost = 4 ?
> >Or there are some peculiarities hard-coded in the logic of CBO that are
> >roughly saying : "If you have FIRST_ROWS as hint or optimizer_mode then
> use
> >the index at  (almost) any cost".
> 
> The old first row optimizer (that you activated with the hint FIRST_ROWS)
> works in this way. Therefore I usually say it isn't a true cost-based
> optimizer. Is more or less a hybrid between rule-based and cost-based.
> 
> If you want a better first row optimizer you should use the new one (which
> is available as of 9.0). It can be activated with FIRST_ROWS_n, where n is
> the number of rows for which the query optimizer will compute the costs.
> With it you will get full table scans as well if they make sense from a
> costing point of view...
> 
> 
> HTH
> Chris
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 

-- 
"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...
Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail
--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 22 2006 - 07:27:56 CDT

Original text of this message

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