Re: The Cache Hint
- From: Sidney Chen <huanshengchen@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Mar 2012 21:46:00 +0800
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@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@CS11GR2> exec dbms_lock.sleep(4);
PL/SQL procedure successfully completed.
sid@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@CS11GR2> exec dbms_lock.sleep(4);
PL/SQL procedure successfully completed.
sid@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@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@xxxxxxxxxxxxxxxxxx
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
