| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 54 is less than 4 for CBO ?
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
| |
| 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-lReceived on Mon May 22 2006 - 07:27:56 CDT
 |
 |