Re: About CBO cost in oracle10g

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 24 May 2004 21:49:30 +0000 (UTC)
Message-ID: <c8tqla$idf$1@sparta.btinternet.com>

I've had a quick look at this.
The numbers are different on my system, but the principle is the same - the cost is not the

cost of outer + (card of outer * cost of inner) for the nested loop FTS.

For the moment I'm just going to guess that this is a tweak that's been put in specifically for nested loop FTS - allowing for (a) the segment header block not being read on every pass, and (b) an arbitrary caching fudge factor.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"eygle" <eygle_at_itpub.net> wrote in message
news:f9bc34d.0405231831.74df0e61_at_posting.google.com...


> Thanks Jonathan.

>

> It's standard emp/dept tables of 10g demo.

> And I have analyzed all of them.

>

> The following is my step:

>

> SYS AS SYSDBA on 24-MAY-04 >connect scott/tiger

> Connected.

> SCOTT  on 24-MAY-04 >exec dbms_stats.gather_schema_stats('SCOTT')

>

> PL/SQL procedure successfully completed.

>

> SCOTT  on 24-MAY-04 >select table_name,num_rows from user_tables;

>

> TABLE_NAME                       NUM_ROWS

> ------------------------------ ----------

> DEPT                                    4

> EMP                                    14

> BONUS                                   0

> SALGRADE                                5

>

> SCOTT  on 24-MAY-04 >connect / as sysdba

> Connected.

> SYS AS SYSDBA on 24-MAY-04 >shutdown immediate

> Database closed.

> Database dismounted.

> ORACLE instance shut down.

> SYS AS SYSDBA on 24-MAY-04 >startup

> ORACLE instance started.

>

> Total System Global Area  314572800 bytes

> Fixed Size                  1301704 bytes

> Variable Size             274473784 bytes

> Database Buffers           37748736 bytes

> Redo Buffers                1048576 bytes

> Database mounted.

> Database opened.

> SYS AS SYSDBA on 24-MAY-04 >connect scott/tiger

> Connected.

> SCOTT  on 24-MAY-04 >alter session set events '10053 trace name

> context forever,level 1';

>

> Session altered.

Received on Mon May 24 2004 - 16:49:30 CDT