ORACLE _small_table_threshold与event 10949与direct path read测试

    下面的测试环境:OS:WIN7  DB：11.2.0.3

     当表所占的BLOCK数大于_small_table_threshold时，表就认为是大表，FTS大表操作结果是放到LRU末端，只CACHE一次，对小表的操作是放到MRU的末端。FTS大表时，是通过direct path read的方式读取数据。我们可以通过配置10949事件，使用进程通过db file scattered read的方式来读取数据。

      _small_table_threshold的值在数据库启动的时候自动配置成_db_block_buffer*0.02。

1. 1，查看相当参数的值 
2. SQL> @parameter_hide 
3. SQL> set echo off 
4.  
5. +------------------------------------------------------------------------+ 
6. | display hide parameter value                                       | 
7. +------------------------------------------------------------------------+ 
8.  
9. Enter Search Parameter (i.e. max|all) : db_block_buffer 
10.  
11. PARAMETER                                SESSION_VALUE        INSTANCE_VALUE       DESCRIPTION 
12. ---------------------------------------- -------------------- -------------------- ------------------------------------------------------------ 
13. _db_block_buffers                        63680                63680                Number of database blocks cached in memory: hidden parameter 
14. SQL> @parameter_hide 
15. SQL> set echo off 
16.  
17. +------------------------------------------------------------------------+ 
18. | display hide parameter value                                       | 
19. +------------------------------------------------------------------------+ 
20.  
21. Enter Search Parameter (i.e. max|all) : small 
22.  
23. PARAMETER                                SESSION_VALUE        INSTANCE_VALUE       DESCRIPTION 
24. ---------------------------------------- -------------------- -------------------- ------------------------------------------------------------ 
25. _small_table_threshold                   1273                 1273                 lower threshold level of table size for direct reads 
26. 2，创建一个表TEST1,块的个数大小small_table_threshold 
27. SQL> exec show_space(p_segname=>'TEST1',P_OWNER=>'SCOTT',P_TYPE=>'TABLE'); 
28. Unformatted Blocks .....................              62 
29. FS1 Blocks (0-25)  .....................               1 
30. FS2 Blocks (25-50) .....................               0 
31. FS3 Blocks (50-75) .....................               0 
32. FS4 Blocks (75-100).....................              52 
33. Full Blocks        .....................           1,137 
34. Total Blocks............................           1,280 
35. Total Bytes.............................      10,485,760 
36. Total MBytes............................              10 
37. Unused Blocks...........................               0 
38. Unused Bytes............................               0 
39. Last Used Ext FileId....................               5 
40. Last Used Ext BlockId...................         411,008 
41. Last Used Block.........................             128 
42.  
43. PL/SQL procedure successfully completed. 
44. 3,创建一个小表 
45. SQL> create table scott.test2 as select * from scott.test1 where rownum<75000; 
46.  
47. Table created. 
48.  
49. SQL> exec show_space(p_segname=>'TEST2',P_OWNER=>'SCOTT',P_TYPE=>'TABLE'); 
50. Unformatted Blocks .....................               0 
51. FS1 Blocks (0-25)  .....................               0 
52. FS2 Blocks (25-50) .....................               0 
53. FS3 Blocks (50-75) .....................               0 
54. FS4 Blocks (75-100).....................               0 
55. Full Blocks        .....................           1,067 
56. Total Blocks............................           1,152 
57. Total Bytes.............................       9,437,184 
58. Total MBytes............................               9 
59. Unused Blocks...........................              59 
60. Unused Bytes............................         483,328 
61. Last Used Ext FileId....................               5 
62. Last Used Ext BlockId...................         413,440 
63. Last Used Block.........................              69 
64.  
65. PL/SQL procedure successfully completed. 
66.  
67. 4，对大表进行多次访问
68. 我们可以看到物理读都是一样，并没有减小，说明BLOCK没有CACHE到BUFFER CACHE中
69. SQL> set autotrace traceonly; 
70. SQL> select count(*) from scott.test1; 
71.  
72.  
73. Execution Plan 
74. ---------------------------------------------------------- 
75. Plan hash value: 3896847026 
76.  
77. -------------------------------------------------------------------- 
78. | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | 
79. -------------------------------------------------------------------- 
80. |   0 | SELECT STATEMENT   |       |     1 |   349   (1)| 00:00:05 | 
81. |   1 |  SORT AGGREGATE    |       |     1 |            |          | 
82. |   2 |   TABLE ACCESS FULL| TEST1 | 66578 |   349   (1)| 00:00:05 | 
83. -------------------------------------------------------------------- 
84.  
85. Note 
86. ----- 
87.    - dynamic sampling used for this statement (level=2) 
88.  
89.  
90. Statistics 
91. ---------------------------------------------------------- 
92.           0  recursive calls 
93.           1  db block gets 
94.        1200  consistent gets 
95.        1190  physical reads 
96.           0  redo size 
97.         425  bytes sent via SQL*Net to client 
98.         415  bytes received via SQL*Net from client 
99.           2  SQL*Net roundtrips to/from client 
100.           0  sorts (memory) 
101.           0  sorts (disk) 
102.           1  rows processed 
103.  
104. SQL> select count(*) from scott.test1; 
105.  
106.  
107. Execution Plan 
108. ---------------------------------------------------------- 
109. Plan hash value: 3896847026 
110.  
111. -------------------------------------------------------------------- 
112. | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | 
113. -------------------------------------------------------------------- 
114. |   0 | SELECT STATEMENT   |       |     1 |   349   (1)| 00:00:05 | 
115. |   1 |  SORT AGGREGATE    |       |     1 |            |          | 
116. |   2 |   TABLE ACCESS FULL| TEST1 | 66578 |   349   (1)| 00:00:05 | 
117. -------------------------------------------------------------------- 
118.  
119. Note 
120. ----- 
121.    - dynamic sampling used for this statement (level=2) 
122.  
123.  
124. Statistics 
125. ---------------------------------------------------------- 
126.           0  recursive calls 
127.           1  db block gets 
128.        1200  consistent gets 
129.        1190  physical reads 
130.           0  redo size 
131.         425  bytes sent via SQL*Net to client 
132.         415  bytes received via SQL*Net from client 
133.           2  SQL*Net roundtrips to/from client 
134.           0  sorts (memory) 
135.           0  sorts (disk) 
136.           1  rows processed 
137. 5，下面是trace进程 
138.    查看进程是以那种方式访问数据块的 
139. SQL> oradebug setmypid 
140. Statement processed. 
141. SQL> oradebug event 10046 trace name context forever,level 12; 
142. Statement processed. 
143. SQL> select count(*) from scott.test1; 
144.  
145. SQL> oradebug tracefile_name 
146. E:\APP\LUOPING\diag\rdbms\winorcl\winorcl\trace\winorcl_ora_8624.trc 
147.  
148. PARSING IN CURSOR #262715460 len=32 dep=0 uid=0 oct=3 lid=0 tim=13695518487 hv=3636002668 ad='b88bc3dc' sqlid='gtwt4m7cbj 
149. select count(*) from scott.test1 
150. END OF STMT 
151. PARSE #262715460:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518486 
152. EXEC #262715460:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13695518602 
153. WAIT #262715460: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=75700 tim=13695518629 
154. WAIT #262715460: nam='direct path read' ela= 598 file number=5 first dba=409859 block cnt=13 obj#=75700 tim=13695519645 
155. WAIT #262715460: nam='direct path read' ela= 367 file number=5 first dba=409889 block cnt=15 obj#=75700 tim=13695520603 
156. WAIT #262715460: nam='direct path read' ela= 316 file number=5 first dba=409905 block cnt=15 obj#=75700 tim=13695521114 
157. WAIT #262715460: nam='direct path read' ela= 136 file number=5 first dba=409921 block cnt=15 obj#=75700 tim=13695521445 
158. WAIT #262715460: nam='direct path read' ela= 4731 file number=5 first dba=409937 block cnt=15 obj#=75700 tim=13695526371 
159. WAIT #262715460: nam='direct path read' ela= 1603 file number=5 first dba=409986 block cnt=126 obj#=75700 tim=13695529850 
160. WAIT #262715460: nam='direct path read' ela= 553 file number=5 first dba=410114 block cnt=126 obj#=75700 tim=13695531752 
161. WAIT #262715460: nam='direct path read' ela= 734 file number=5 first dba=410242 block cnt=126 obj#=75700 tim=13695533842 
162. WAIT #262715460: nam='direct path read' ela= 589 file number=5 first dba=410370 block cnt=126 obj#=75700 tim=13695535660 
163. WAIT #262715460: nam='direct path read' ela= 1648 file number=5 first dba=410498 block cnt=126 obj#=75700 tim=13695538106 
164. WAIT #262715460: nam='direct path read' ela= 374 file number=5 first dba=410626 block cnt=126 obj#=75700 tim=13695539715 
165. WAIT #262715460: nam='direct path read' ela= 834 file number=5 first dba=410754 block cnt=126 obj#=75700 tim=13695541868 
166. WAIT #262715460: nam='direct path read' ela= 634 file number=5 first dba=410882 block cnt=126 obj#=75700 tim=13695543782 
167. 这里可以看到是以direct path read的方式访问数据 
168.  
169. 7，刷新一下buffer_cache 
170. SQL> alter system flush buffer_cache; 
171.  
172. System altered. 
173. 8.访问小表 
174.   每一次访问小表有物理读，从2次开发物理读消失，说明BLOCK已经CACHE到BUFFER CACHE中了 
175. SQL> select count(*) from scott.test2; 
176.  
177.  
178. Execution Plan 
179. ---------------------------------------------------------- 
180. Plan hash value: 634289536 
181.  
182. -------------------------------------------------------------------- 
183. | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | 
184. -------------------------------------------------------------------- 
185. |   0 | SELECT STATEMENT   |       |     1 |   299   (1)| 00:00:04 | 
186. |   1 |  SORT AGGREGATE    |       |     1 |            |          | 
187. |   2 |   TABLE ACCESS FULL| TEST2 | 74999 |   299   (1)| 00:00:04 | 
188. -------------------------------------------------------------------- 
189.  
190.  
191. Statistics 
192. ---------------------------------------------------------- 
193.           0  recursive calls 
194.           0  db block gets 
195.        1071  consistent gets 
196.        1068  physical reads 
197.           0  redo size 
198.         425  bytes sent via SQL*Net to client 
199.         415  bytes received via SQL*Net from client 
200.           2  SQL*Net roundtrips to/from client 
201.           0  sorts (memory) 
202.           0  sorts (disk) 
203.           1  rows processed 
204.  
205. SQL> select count(*) from scott.test2; 
206.  
207.  
208. Execution Plan 
209. ---------------------------------------------------------- 
210. Plan hash value: 634289536 
211.  
212. -------------------------------------------------------------------- 
213. | Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     | 
214. -------------------------------------------------------------------- 
215. |   0 | SELECT STATEMENT   |       |     1 |   299   (1)| 00:00:04 | 
216. |   1 |  SORT AGGREGATE    |       |     1 |            |          | 
217. |   2 |   TABLE ACCESS FULL| TEST2 | 74999 |   299   (1)| 00:00:04 | 
218. -------------------------------------------------------------------- 
219.  
220.  
221. Statistics 
222. ---------------------------------------------------------- 
223.           0  recursive calls 
224.           0  db block gets 
225.        1071  consistent gets 
226.           0  physical reads 
227.           0  redo size 
228.         425  bytes sent via SQL*Net to client 
229.         415  bytes received via SQL*Net from client 
230.           2  SQL*Net roundtrips to/from client 
231.           0  sorts (memory) 
232.           0  sorts (disk) 
233.           1  rows processed 
234.            
235.  9，下面通过配置10949事件来改变块的访问方式          
236. SQL> alter session set events '10949 trace name context forever, level 1'; 
237.  
238. Session altered. 
239.  
240. SQL> select count(*) from scott.test1; 
241.  
242.  
243. select count(*) from scott.test1 
244. END OF STMT 
245. PARSE #258512224:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556637 
246. EXEC #258512224:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3896847026,tim=13852556706 
247. WAIT #258512224: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=0 tim=13852556724 
248. WAIT #258512224: nam='db file sequential read' ela= 551 file#=5 block#=409858 blocks=1 obj#=75700 tim=13852557314 
249. WAIT #258512224: nam='db file scattered read' ela= 367 file#=5 block#=409859 blocks=5 obj#=75700 tim=13852557812 
250. WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409864 blocks=8 obj#=75700 tim=13852558366 
251. WAIT #258512224: nam='db file scattered read' ela= 408 file#=5 block#=409873 blocks=7 obj#=75700 tim=13852558909 
252. WAIT #258512224: nam='db file scattered read' ela= 521 file#=5 block#=409880 blocks=8 obj#=75700 tim=13852559557 
253. WAIT #258512224: nam='db file scattered read' ela= 412 file#=5 block#=409889 blocks=7 obj#=75700 tim=13852560061 
254. WAIT #258512224: nam='db file scattered read' ela= 427 file#=5 block#=409896 blocks=8 obj#=75700 tim=13852560716 
255. WAIT #258512224: nam='db file scattered read' ela= 519 file#=5 block#=409905 blocks=7 obj#=75700 tim=13852561331 
256. WAIT #258512224: nam='db file scattered read' ela= 528 file#=5 block#=409912 blocks=8 obj#=75700 tim=13852561948 
257. WAIT #258512224: nam='db file scattered read' ela= 459 file#=5 block#=409921 blocks=7 obj#=75700 tim=13852562493 
258. WAIT #258512224: nam='db file scattered read' ela= 477 file#=5 block#=409928 blocks=8 obj#=75700 tim=13852563103 
259. WAIT #258512224: nam='db file scattered read' ela= 429 file#=5 block#=409937 blocks=7 obj#=75700 tim=13852563662 
260. WAIT #258512224: nam='db file scattered read' ela= 470 file#=5 block#=409944 blocks=8 obj#=75700 tim=13852564261 
261. WAIT #258512224: nam='db file scattered read' ela= 424 file#=5 block#=409953 blocks=7 obj#=75700 tim=13852564809 
262. WAIT #258512224: nam='db file scattered read' ela= 472 file#=5 block#=409960 blocks=8 obj#=75700 tim=13852565401 
263. WAIT #258512224: nam='db file scattered read' ela= 409 file#=5 block#=409969 blocks=7 obj#=75700 tim=13852565972 
264. WAIT #258512224: nam='db file scattered read' ela= 426 file#=5 block#=409976 blocks=8 obj#=75700 tim=13852566498 
265. WAIT #258512224: nam='db file scattered read' ela= 2543 file#=5 block#=409986 blocks=126 obj#=75700 tim=13852569237 
266. WAIT #258512224: nam='db file scattered read' ela= 2434 file#=5 block#=410114 blocks=126 obj#=75700 tim=13852572799 
267. WAIT #258512224: nam='db file scattered read' ela= 2535 file#=5 block#=410242 blocks=126 obj#=75700 tim=13852576312 
268. WAIT #258512224: nam='db file scattered read' ela= 2641 file#=5 block#=410370 blocks=126 obj#=75700 tim=13852580912 
269. WAIT #258512224: nam='db file sequential read' ela= 284 file#=5 block#=410881 blocks=1 obj#=75700 tim=13852582218 
270. WAIT #258512224: nam='db file sequential read' ela= 292 file#=5 block#=409857 blocks=1 obj#=75700 tim=13852582591 
271. WAIT #258512224: nam='db file scattered read' ela= 297 file#=5 block#=411008 blocks=2 obj#=75700 tim=13852582924 
272. WAIT #258512224: nam='db file scattered read' ela= 2563 file#=5 block#=410498 blocks=126 obj#=75700 tim=13852585689 
273. WAIT #258512224: nam='db file scattered read' ela= 2604 file#=5 block#=410626 blocks=126 obj#=75700 tim=13852589282 
274. WAIT #258512224: nam='db file scattered read' ela= 2548 file#=5 block#=410754 blocks=126 obj#=75700 tim=13852592817 
275. WAIT #258512224: nam='db file scattered read' ela= 1853 file#=5 block#=410882 blocks=78 obj#=75700 tim=13852595699 
276. WAIT #258512224: nam='db file scattered read' ela= 1267 file#=5 block#=410960 blocks=48 obj#=75700 tim=13852597583 
277. WAIT #258512224: nam='db file scattered read' ela= 1550 file#=5 block#=411072 blocks=64 obj#=75700 tim=13852599520 
278.  
279. 已经更改成db file scattered read了。 

     本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/1141799，如需转载请自行联系原作者