Re: Nested Loops cost

You don't state the version of Oracle, but I would guess it is between 8.1.6 and 8.1.7.2

I __THINK__ you may be seeing a side-effect of a bug in Oracle's treatment of root blocks, but you are almost certainly seeing the
results of block pinning.

Check the v$sesstat for the statistic
"block is pinned" - I think you will find that by adding that to the consistent gets the two sets of figures will make more
sense.

It is a little-known feature of Oracle that GETs aren't the only form of logical I/O; every version of Oracle from 6.0 onwards (I believe) has used pinning for the duration of calls to reduce the cost of making multiple visits to a same buffered block. "Block is pinned" accesses are equivalent to, but
less contentious than, "consistent gets".

Each new version of Oracle finds new strategies when it makes sense to use pinning to reduce contention - but sometimes there are little bugs, and, of course, some of the peripheral tools (such as performance tuning tools) take some time to catch up.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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



sweidanz_at_yahoo.com wrote in message
<66Hz8.13$rT2.1303_at_nsw.nnrp.telstra.net>...


>Can anyone explain the difference in consistent gets on the following two

>queries.

>1.

>

>SELECT  car.cd_company_system,

>        car.no_combine_024,

>        car.no_account,

>        le.nm_preferred

>FROM   CAR_table car,

>        LE_TABLE le

>WHERE  car.tp_cust_acct_role = 'P'

>AND    (car.dt_end IS NULL OR car.dt_end > SYSDATE)

>AND    car.cd_company_system = 'RPT1'

>AND    car.cd_company_system = le.cd_company_system

>AND    car.no_legal_entity = le.no_legal_entity

>

>

>Execution Plan

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

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=2 Bytes=80)

>   1    0   NESTED LOOPS (Cost=103 Card=2 Bytes=80)

>   2    1     TABLE ACCESS (FULL) OF 'CAR_TABLE' (Cost=101 Card=2

>Bytes=38)

>   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'LE_TABLE' (Cost=1 Card=307

>Bytes=6447)

>   4    3       INDEX (UNIQUE SCAN) OF 'PVP036' (UNIQUE)

>

>Statistics

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

>          0  recursive calls

>          8  db block gets

>       1051  consistent gets

>        649  physical reads

>          0  redo size

>       6358  bytes sent via SQL*Net to client

>        836  bytes received via SQL*Net from client

>         10  SQL*Net roundtrips to/from client

>          0  sorts (memory)

>          0  sorts (disk)

>        122  rows processed

>

>2.

>

>SELECT /*+ INDEX (car xvp0242) */

>       car.cd_company_system,

>       car.no_combine_024,

>       car.no_account,

>      le.nm_preferred

>FROM   CAR_TABLE car,

>       LE_TABLE le

>WHERE  car.tp_cust_acct_role = 'P'

>AND    (car.dt_end IS NULL OR car.dt_end > SYSDATE)

>AND    car.cd_company_system = 'RPT1'

>AND    car.cd_company_system = le.cd_company_system

>AND    car.no_legal_entity = le.no_legal_entity

>

>Execution Plan

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

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=354 Card=2 Bytes=80)

>   1    0   NESTED LOOPS (Cost=354 Card=2 Bytes=80)

>   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CAR_TABLE' (Cost=352

>Card=2 Bytes=38)

>   3    2       INDEX (FULL SCAN) OF 'XVP0242' (NON-UNIQUE) (Cost=347

>Card=2)

>   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'LE_TABLE' (Cost=1 Card=307

>Bytes=6447)

>   5    4       INDEX (UNIQUE SCAN) OF 'PVP036' (UNIQUE)

>

>Statistics

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

>          0  recursive calls

>          0  db block gets

>        846  consistent gets

>          0  physical reads

>          0  redo size

>       6358  bytes sent via SQL*Net to client

>        836  bytes received via SQL*Net from client

>         10  SQL*Net roundtrips to/from client

>          0  sorts (memory)

>          0  sorts (disk)

>        122  rows processed

>

>xvp0242 index is a nonunique composite index on (no_legal_entity,

>cd_company_system)

>pvp036 index is a unique composite index on (no_legal_entity,

>cd_company_system)

>

>I can not understand why by forcing to use an index xvp0242, it does less

>consistent gets than with doing full scan on the table. My undestanding is

>INDEX full scan, will read each index, and as the index is not enough to

>check for the rest of the conditions, it needs to get the data block for

>that index, then check that the record meets the conditions.If it does,

>then join to the second table to get the match. Which eventually means

>reading all the index blocks, reading all data blocks for the first table.

>While in doing a full scan on the first table, it does read the data

>blocks only, check that record meets the conditions, then join to the

>second table to get the match record. By doing so, it means full scan

>should read less data blocks (as it did not have to read the index

>blocks). But it does not seem that Oracle is doing what I initially

>understand.

>Can anyone helps in clarifying this behaviour.

>

>Thanks,

>ZS