Re: Full 2 million rows Table Scan but cero disk reads
Date: Tue, 21 Sep 2010 17:56:55 -0700
Message-ID: <4C995457.6080705_at_ardentperf.com>
Hm, looks normal to me, not like a special optimization. If it does have a name, then I don't know the name. I'm not a cbo expert by any means... but here's my swing at it (hopefully someone will correct any mistakes that I make here):
You're asking for the impacts made during all the visits from a
particular user. Oracle does this:
[1] first finds the user (full scan of the small user table),
[2] then it enumerates the visits (with an index range scan into the
visit table),
[3] then creates a hash table in memory from those values. [4] Finally it full scans the (large) impacts table [5] and uses the in-memory hash to build complete result set records.
At step 1, oracle found 1 user.
At step 2, oracle looked in the index and found that this user never
visited.
Now oracle simply stops because the final result will be empty (these
are all inner joins).
This is a good example where estimate was very different from actual, because estimates are based on "averages" but you have a non-average query. On "average" the CBO seems to think that each user has 3528 visits. You executed this query with a user who had 0 visits. Using "averages" the CBO expects something roughly around 8686 logical I/O operations. This execution only required only 30: reading the user table is 27 and the index scan is 3. Not surprisingly, this table and index are entirely cached - so there was no physical I/O at all.
Possibly I'm mis-reading things... hopefully others will correct me if that's the case.
-Jeremy
LS Cheng wrote:
> Hi all
>
> I have a database running in 11.1.0.7 in Linux, while I was doing some
> query testing I found a query as following with the execution plan and
> execution statistics:
>
> select a.*
> from impacts a
> where (start_visit, id_visit) IN (SELECT start_visit, id_visit
> FROM VISITS WHERE ID_USER =
> (SELECT
> COD_ID_USER FROM VISIT_USERS WHERE IDENT= :V1 ))
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 1 0.00 0.00 0 27
> 0 0
> Fetch 1 0.00 0.00 0 3
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 0.00 0.00 0 30
> 0 0
>
> Misses in library cache during parse: 1
> Optimizer mode: ALL_ROWS
> Parsing user id: 39
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 HASH JOIN (cr=30 pr=0 pw=0 time=0 us cost=8686 size=1783188
> card=15642)
> 0 TABLE ACCESS BY INDEX ROWID VISITS (cr=30 pr=0 pw=0 time=0
> us cost=724 size=63504 card=3528)
> 0 INDEX RANGE SCAN LSC$I1 (cr=30 pr=0 pw=0 time=0 us cost=11
> size=0 card=3528)(object id 1131243)
> 1 TABLE ACCESS FULL VISIT_USERS (cr=27 pr=0 pw=0 time=0 us
> cost=7 size=15 card=1)
> 0 TABLE ACCESS FULL IMPACTS (cr=0 pr=0 pw=0 time=0 us
> cost=7920 size=237716448 card=2476213)
>
>
> This is pretty good because looks like the hash join is between cero
> rows join result (join between VISITS and VISIT_USERS) and a 2 million
> rows table (IMPACTS)and that yields 30 buffer gets. So it actually
> says TABLE ACCESS FULL IMPACTS but actually didnt read any rows.
>
> I guess this is a CBO optimization, does anyone know what is this
> optimization called... :-?
>
>
> Thanks
>
> --
> LSC
>
-- http://www.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 21 2010 - 19:56:55 CDT