Chinese

前提：
1，oracle 11.2.0.1.0的专属服务器

2，主要内存参数为
workarea_size_policy：AUTO
memory_max_target：1176M
memory_target：1176M
sga_max_size：712M
sga_target：0
pga_aggregate_target：0
db_cache_size：0
db_keep_cache_size：0
db_recycle_cache_size：0
java_pool_size：0
large_pool_size：0
olap_page_pool_size：0
shared_pool_reserved_size：10066329
shared_pool_size：0
streams_pool_size：0

3，修改keeppool的内存参数
alter system set db_keep_cache_size = 64m scope=both;

4，修改之后db_keep_cache_size：64m

5，测试步骤
create table z_all_objects as
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b;

------------------48m
SELECT B.BYTES/1024/1024,B.BLOCKS,B.*
FROM DBA_SEGMENTS B
WHERE B.SEGMENT_NAME LIKE '%Z_ALL_OBJECTS';

alter table Z_ALL_OBJECTS storage(buffer_pool keep);

alter system flush buffer_cache;

select count(OBJECT_NAME)
from Z_ALL_OBJECTS;

select count(1)
from v$bh b
where b.OBJD = (SELECT b.DATA_OBJECT_ID FROM DBA_OBJECTS B WHERE B.OBJECT_NAME LIKE '%Z_ALL_OBJECTS')
and b.STATUS <> 'free';


SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> select count(OBJECT_NAME) from Z_ALL_OBJECTS;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name
c:\app\administrator\diag\rdbms\orclauto\orclauto\trace\orclauto_ora_5080.trc



*** 2012-11-01 15:34:46.490
WAIT #2: nam='SQL*Net message from client' ela= 9625317 driver id=1413697536 #bytes=1 p3=0 obj#=73439 tim=540397263432
CLOSE #2:c=0,e=6,dep=0,type=3,tim=540397263587
=====================
PARSING IN CURSOR #2 len=413 dep=1 uid=0 oct=3 lid=0 tim=540397265425 hv=4104804724 ad='361a2908' sqlid='12jjrsbuanqbn'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("Z_ALL_OBJECTS") FULL("Z_ALL_OBJECTS") NO_PARALLEL_INDEX("Z_ALL_OBJECTS") */ 1 AS C1, 1 AS C2 FROM "Z_ALL_OBJECTS" SAMPLE BLOCK (1.037549 , 1) SEED (1) "Z_ALL_OBJECTS") SAMPLESUB
END OF STMT
PARSE #2:c=0,e=1126,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=4261417846,tim=540397265423
EXEC #2:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=4261417846,tim=540397265636
FETCH #2:c=0,e=1567,p=0,cr=66,cu=0,mis=0,r=1,dep=1,og=1,plh=4261417846,tim=540397267240
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=66 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=4025 pid=1 pos=1 obj=73441 op='TABLE ACCESS SAMPLE Z_ALL_OBJECTS (cr=66 pr=0 pw=0 time=2012 us cost=19 size=61752 card=5146)'
CLOSE #2:c=0,e=9,dep=1,type=0,tim=540397267399
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=3 lid=0 tim=540397267712 hv=3753611487 ad='3621019c' sqlid='2d2xqxmgvr46z'
select count(OBJECT_NAME) from Z_ALL_OBJECTS
END OF STMT
PARSE #1:c=0,e=4068,p=0,cr=67,cu=0,mis=1,r=0,dep=0,og=1,plh=758519258,tim=540397267711
EXEC #1:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=758519258,tim=540397267882
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=73439 tim=540397267930
WAIT #1: nam='direct path read' ela= 9730 file number=1 first dba=84361 block cnt=7 obj#=73441 tim=540397278063
WAIT #1: nam='direct path read' ela= 25029 file number=1 first dba=84472 block cnt=8 obj#=73441 tim=540397303797
WAIT #1: nam='direct path read' ela= 10184 file number=1 first dba=86272 block cnt=128 obj#=73441 tim=540397316945
WAIT #1: nam='direct path read' ela= 7974 file number=1 first dba=86400 block cnt=128 obj#=73441 tim=540397327185


6，解决方案
PS：根据老刘以前的帖子，发现确实是'direct path read。导致了大表没有将block缓存起来。
详见：http://t.askmaclean.com/viewthread.php?tid=1280&highlight=

查看本次实现实现的参数
SELECT x.ksppinm NAME, y.ksppstvl VALUE,y.ksppstvl*8192/1024/1024 valuem, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx
AND x.ksppinm LIKE '%_small_table_threshold%';
发现结果是1114个block，大小为8.703125M。

将session的参数修改为999999之后，解决了这个问题。
SQL> alter session set "_small_table_threshold"=999999;

Session altered.

SQL>
SQL> select count(OBJECT_NAME) from Z_ALL_OBJECTS;


*** 2012-11-01 15:40:10.282
WAIT #2: nam='SQL*Net message from client' ela= 6389799 driver id=1413697536 #bytes=1 p3=0 obj#=73441 tim=540721057439
CLOSE #2:c=0,e=11,dep=0,type=1,tim=540721057599
=====================
PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=3 lid=0 tim=540721057726 hv=3753611487 ad='3621019c' sqlid='2d2xqxmgvr46z'
select count(OBJECT_NAME) from Z_ALL_OBJECTS
END OF STMT
PARSE #1:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=758519258,tim=540721057725
EXEC #1:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=758519258,tim=540721057899
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=73441 tim=540721057948
WAIT #1: nam='db file scattered read' ela= 6129 file#=1 block#=84361 blocks=7 obj#=73441 tim=540721064239
WAIT #1: nam='db file scattered read' ela= 8707 file#=1 block#=84472 blocks=8 obj#=73441 tim=540721073318
WAIT #1: nam='db file scattered read' ela= 9590 file#=1 block#=86144 blocks=8 obj#=73441 tim=540721083209
WAIT #1: nam='db file scattered read' ela= 818 file#=1 block#=86152 blocks=8 obj#=73441 tim=540721089823



7，深入测试研究
将SQL> alter session set "_small_table_threshold"=1114;

同时将测试表的数据量减小
create table z_all_objects as
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b union all
select * from all_objects b;


SELECT B.BYTES/1024/1024,B.BLOCKS,B.*
FROM DBA_SEGMENTS B
WHERE B.SEGMENT_NAME LIKE '%Z_ALL_OBJECTS';

SQL> select count(OBJECT_NAME) from Z_ALL_OBJECTS;
这时候，发现，虽然表的大小大于了"_small_table_threshold，但是在做count的时候，没有direct read


******请问此时为什么"_small_table_threshold参数没有起到做用呢？******



*** 2012-11-01 15:47:22.598
WAIT #0: nam='SQL*Net message from client' ela= 63031600 driver id=1413697536 #bytes=1 p3=0 obj#=73441 tim=541153368784
=====================
PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=3 lid=0 tim=541153368978 hv=3753611487 ad='3621019c' sqlid='2d2xqxmgvr46z'
select count(OBJECT_NAME) from Z_ALL_OBJECTS
END OF STMT
PARSE #2:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=758519258,tim=541153368977
EXEC #2:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=758519258,tim=541153369156
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=73441 tim=541153369205
WAIT #2: nam='db file sequential read' ela= 14425 file#=1 block#=84360 blocks=1 obj#=73442 tim=541153383707
WAIT #2: nam='db file scattered read' ela= 501 file#=1 block#=84361 blocks=7 obj#=73442 tim=541153384458
WAIT #2: nam='db file scattered read' ela= 8470 file#=1 block#=84472 blocks=8 obj#=73442 tim=541153393166
WAIT #2: nam='db file scattered read' ela= 9630 file#=1 block#=86144 blocks=8 obj#=73442 tim=541153403060