Re: The Cache Hint
Date: Mon, 12 Mar 2012 21:46:00 +0800
Message-ID: <CAM_ddu-vgbiJqOuJq4d8cS3o7aB4DR6_FDTZV5OvaYPXuv-awA_at_mail.gmail.com>
Jonathan,
I've tried your scripts in your post small tables in 11g https://jonathanlewis.wordpress.com/2011/03/24/small-tables/
My test shows that for the medium table(percent [2, 10] of db_cache_size),
the first tablescan will be serial direct; for long talbe(percent [10, ∞]),
every tablescan scan is serial direct.
http://sid.gd/small-tables/
What surpurise me is that the cache hint does not change the serial direct read for the medium/long table. For example, I've set the db_cache_size to 120M and t_1488 is the 10 percent table. All the 3 tablescan are all serial direct, so I wonder that the cache hint has no impact at least for the medium and long table.
- fill up the buffer cache
sid_at_CS11GR2> select /*+ cache(t)*/
2 max(small_vc)
3 from
4 t_1488 t;
MAX(SMALL_VC)
0000001488
Name
Value
physical reads
1,490
physical reads cache
2
physical reads direct
1,488
table scans (long tables)
1
table scans (direct read)
1
table scan rows gotten
1,488
table scan blocks gotten
1,488
sid_at_CS11GR2> exec dbms_lock.sleep(4);
PL/SQL procedure successfully completed.
sid_at_CS11GR2> select /*+ cache(t)*/
2 max(small_vc)
3 from
4 t_1488 t;
MAX(SMALL_VC)
0000001488
Name
Value
physical reads
1,488
physical reads direct
1,488
table scans (long tables)
1
table scans (direct read)
1
table scan rows gotten
1,488
table scan blocks gotten
1,488
sid_at_CS11GR2> exec dbms_lock.sleep(4);
PL/SQL procedure successfully completed.
sid_at_CS11GR2> select /*+ cache(t)*/
2 max(small_vc)
3 from
4 t_1488 t;
MAX(SMALL_VC)
0000001488
Name
Value
physical reads
1,488
physical reads direct
1,488
table scans (long tables)
1
table scans (direct read)
1
table scan rows gotten
1,488
table scan blocks gotten
1,488
sid_at_CS11GR2> select
2 obj, tch, count(*)
3 from x$_bh
4 where
5 obj between 87014 and 87020
6 group by
7 obj, tch
8 order by
9 count(*)
10 ;
OBJ TCH COUNT(*)
---------- ---------- ----------
87015 3 1 87016 3 1 <-- This is the segment header for t_1488, no data block cache for t_1488. 87020 1 32 87015 2 744 87019 1 14006
On Mon, Mar 12, 2012 at 8:40 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:
>
>
> Too many unknowns.
>
> One thing I can say, though, is that nothing gets loaded into the MRU end
> of the LRU, everything goes to the mid point.
> One thought about the cache hint - it's possible that it changes (again) in
> 11.2 as a side effect of serial direct path reads.
>
> In 11.2 a table over 25% of the cache size (whatever that means exactly)
> will be read using serial direct path reads (unless specified as
> parallel) - perhaps if you specify the cache hint it will be read into the
> cache instead. Conversely, if you specify nocache might this force a serial
> direct read ?
>
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> --
>
Regards
Sidney Chen
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 12 2012 - 08:46:00 CDT