BUFFER_POOL KEEP问题

liuxbgiant

SYS@orcl>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

parameter set as :
SYS@orcl>sho parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 80M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 160M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 128M
db_recycle_cache_size                big integer 0

SYS@orcl>create table dna.t2 storage(buffer_pool keep) as select level id ,rpad('*',4000,'*') data from dual connect by
level<=15000;

表已创建。

SYS@orcl>select count(*) from dna.t2;

  COUNT(*)
----------
     15000

SYS@orcl>set autotrace traceonly
SYS@orcl>select count(*) from dna.t2;


执行计划
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  4116   (1)| 00:00:50 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 16126 |  4116   (1)| 00:00:50 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15004  consistent gets
      15000  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SYS@orcl>select count(*) from dna.t2;


执行计划
----------------------------------------------------------
Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  4116   (1)| 00:00:50 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 16126 |  4116   (1)| 00:00:50 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      15004  consistent gets
      15000  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
为什么会出现大量的physical reads现象，难道此存在于The KEEP buffer pool中的表，应该不会出现此现象？

本人通过测试


select * from dba_segments where buffer_pool='KEEP';


也发现表确实在The KEEP buffer pool中

lingzhiluoye

学习了

sunnyihui

ML v5，学习了 。。。。。。。。。。。。。

kim_meng

ML威武。学习了。

Maclean Liu(刘相兵

ODM TEST:

1. SQL>
   
2. SQL> select * from v$version;
   
3. 
   
4. BANNER
   
5. --------------------------------------------------------------------------------
   
6. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
7. PL/SQL Release 11.2.0.3.0 - Production
   
8. CORE    11.2.0.3.0      Production
   
9. TNS for Linux: Version 11.2.0.3.0 - Production
   
10. NLSRTL Version 11.2.0.3.0 - Production
    
11. 
    
12. 
    
13. 
    
14. SQL> show parameter db_keep
    
15. 
    
16. NAME                                 TYPE                             VALUE
    
17. ------------------------------------ -------------------------------- ------------------------------
    
18. db_keep_cache_size                   big integer                      128M
    
19. 
    
20. 
    
21. SQL> create table maclean_tan2 storage(buffer_pool keep) as select level id ,rpad('*',4000,'*') data from dual connect by
    
22.   2  level<=15000;
    
23. 
    
24. Table created.
    
25. 
    
26. 
    
27. 
    
28. SQL>  select count(*) from maclean_tan2;
    
29. 
    
30.   COUNT(*)
    
31. ----------
    
32.      15000
    
33. 
    
34. 
    
35. Execution Plan
    
36. ----------------------------------------------------------
    
37. Plan hash value: 1229461046
    
38. 
    
39. ---------------------------------------------------------------------------
    
40. | Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
    
41. ---------------------------------------------------------------------------
    
42. |   0 | SELECT STATEMENT   |              |     1 |  4069   (1)| 00:00:49 |
    
43. |   1 |  SORT AGGREGATE    |              |     1 |            |          |
    
44. |   2 |   TABLE ACCESS FULL| MACLEAN_TAN2 | 15476 |  4069   (1)| 00:00:49 |
    
45. ---------------------------------------------------------------------------
    
46. 
    
47. Note
    
48. -----
    
49.    - dynamic sampling used for this statement (level=2)
    
50. 
    
51. 
    
52. Statistics
    
53. ----------------------------------------------------------
    
54.           4  recursive calls
    
55.           0  db block gets
    
56.       15081  consistent gets
    
57.       15000  physical reads
    
58.           0  redo size
    
59.         527  bytes sent via SQL*Net to client
    
60.         523  bytes received via SQL*Net from client
    
61.           2  SQL*Net roundtrips to/from client
    
62.           0  sorts (memory)
    
63.           0  sorts (disk)
    
64.           1  rows processed
    
65.                   
    
66.                   
    
67. SQL> select count(*) from maclean_tan2;
    
68. 
    
69.   COUNT(*)
    
70. ----------
    
71.      15000
    
72. 
    
73. 
    
74. Execution Plan
    
75. ----------------------------------------------------------
    
76. Plan hash value: 1229461046
    
77. 
    
78. ---------------------------------------------------------------------------
    
79. | Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
    
80. ---------------------------------------------------------------------------
    
81. |   0 | SELECT STATEMENT   |              |     1 |  4069   (1)| 00:00:49 |
    
82. |   1 |  SORT AGGREGATE    |              |     1 |            |          |
    
83. |   2 |   TABLE ACCESS FULL| MACLEAN_TAN2 | 15476 |  4069   (1)| 00:00:49 |
    
84. ---------------------------------------------------------------------------
    
85. 
    
86. Note
    
87. -----
    
88.    - dynamic sampling used for this statement (level=2)
    
89. 
    
90. 
    
91. Statistics
    
92. ----------------------------------------------------------
    
93.           0  recursive calls
    
94.           0  db block gets
    
95.       15004  consistent gets
    
96.       15000  physical reads
    
97.           0  redo size
    
98.         527  bytes sent via SQL*Net to client
    
99.         523  bytes received via SQL*Net from client
    
100.           2  SQL*Net roundtrips to/from client
     
101.           0  sorts (memory)
     
102.           0  sorts (disk)
     
103.           1  rows processed
     
104. 
     
105. 
     
106. SQL> alter session set events '10046 trace name context forever,level 8';
     
107. 
     
108. Session altered.
     
109. 
     
110. SQL> select count(*) from maclean_tan2;
     
111. 
     
112.   COUNT(*)
     
113. ----------
     
114.      15000
     
115.          
     
116. SQL> oradebug setmypid;
     
117. Statement processed.
     
118. SQL> oradebug tracefile_name
     
119. /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_29876.trc         
     
120. 
     
121. 
     
122. 
     
123. PARSING IN CURSOR #140118795641360 len=33 dep=0 uid=0 oct=3 lid=0 tim=1340511245212199 hv=486583032 ad='76883110' sqlid='drryzcwfh1ars'
     
124. select count(*) from maclean_tan2
     
125. END OF STMT
     
126. PARSE #140118795641360:c=6000,e=35195,p=0,cr=77,cu=0,mis=1,r=0,dep=0,og=1,plh=1229461046,tim=1340511245212192
     
127. EXEC #140118795641360:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1229461046,tim=1340511245212328
     
128. WAIT #140118795641360: nam='SQL*Net message to client' ela= 13 driver id=1650815232 #bytes=1 p3=0 obj#=79780 tim=1340511245212395
     
129. WAIT #140118795641360: nam='asynch descriptor resize' ela= 11 outstanding #aio=0 current aio limit=235 new aio limit=265 obj#=79780 tim=1340511245214369
     
130. WAIT #140118795641360: nam='direct path read' ela= 140 file number=1 first dba=200555 block cnt=1 obj#=79780 tim=1340511245276928
     
131. WAIT #140118795641360: nam='direct path read' ela= 124 file number=1 first dba=200683 block cnt=1 obj#=79780 tim=1340511245294008
     
132. WAIT #140118795641360: nam='direct path read' ela= 126 file number=1 first dba=201707 block cnt=1 obj#=79780 tim=1340511245425743
     
133. WAIT #140118795641360: nam='direct path read' ela= 170 file number=1 first dba=201835 block cnt=1 obj#=79780 tim=1340511245454308
     
134. WAIT #140118795641360: nam='direct path read' ela= 126 file number=1 first dba=201963 block cnt=1 obj#=79780 tim=1340511245472445
     
135. WAIT #140118795641360: nam='direct path read' ela= 113 file number=1 first dba=202091 block cnt=1 obj#=79780 tim=1340511245488926
     
136. WAIT #140118795641360: nam='direct path read' ela= 116 file number=1 first dba=202219 block cnt=1 obj#=79780 tim=1340511245505475
     
137. WAIT #140118795641360: nam='direct path read' ela= 116 file number=1 first dba=202475 block cnt=1 obj#=79780 tim=1340511245539057
     
138. WAIT #140118795641360: nam='direct path read' ela= 157 file number=1 first dba=202603 block cnt=1 obj#=79780 tim=1340511245556950
     
139. WAIT #140118795641360: nam='direct path read' ela= 31 file number=1 first dba=202987 block cnt=1 obj#=79780 tim=1340511245608673
     
140. WAIT #140118795641360: nam='direct path read' ela= 131 file number=1 first dba=203115 block cnt=1 obj#=79780 tim=1340511245624922
     
141. WAIT #140118795641360: nam='direct path read' ela= 113 file number=1 first dba=203755 block cnt=1 obj#=79780 tim=1340511245706298
     
142. WAIT #140118795641360: nam='direct path read' ela= 28 file number=1 first dba=203883 block cnt=1 obj#=79780 tim=1340511245722656
     
143. WAIT #140118795641360: nam='direct path read' ela= 13 file number=1 first dba=204011 block cnt=1 obj#=79780 tim=1340511245738218
     
144. WAIT #140118795641360: nam='direct path read' ela= 31 file number=1 first dba=204523 block cnt=1 obj#=79780 tim=1340511245801733
     
145. 
     
146. 
     
147. direct path read  而非 db file scattered read
     
148. 
     
149. 11g new feature 对于大表 的FULL SCAN 可以直接采用 direct path read   读入PGA 而不经过 buffer cache
     
150. 
     
151. 
     
152. 
     
153. ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
     
154. 
     
155. 
     
156. 10949 event 可以禁止 11g 的这种特性；
     
157. 
     
158. [oracle@vrh1 ~]$ oerr ora 10949
     
159. 10949, 00000, "Disable autotune direct path read for full table scan"
     
160. // *Cause:
     
161. // *Action:  Disable autotune direct path read for serial full table scan.
     
162. 
     
163. 
     
164. 
     
165. 
     
166. _small_table_threshold 设置为较大值  避免 optimizer 将这个表视为大表 buffer 被flush
     
167. 
     
168. 
     
169. SQL>
     
170. SQL> alter session set "_small_table_threshold"=999999;
     
171. 
     
172. Session altered.
     
173. 
     
174. SQL> ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
     
175. 
     
176. Session altered.
     
177. 
     
178. SQL> select count(*) from maclean_tan2;
     
179. 
     
180.   COUNT(*)
     
181. ----------
     
182.      15000
     
183. 
     
184. SQL> set autotrace on;
     
185. SQL> select count(*) from maclean_tan2;
     
186. 
     
187.   COUNT(*)
     
188. ----------
     
189.      15000
     
190. 
     
191. 
     
192. Execution Plan
     
193. ----------------------------------------------------------
     
194. Plan hash value: 1229461046
     
195. 
     
196. ---------------------------------------------------------------------------
     
197. | Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
     
198. ---------------------------------------------------------------------------
     
199. |   0 | SELECT STATEMENT   |              |     1 |  4069   (1)| 00:00:49 |
     
200. |   1 |  SORT AGGREGATE    |              |     1 |            |          |
     
201. |   2 |   TABLE ACCESS FULL| MACLEAN_TAN2 | 15476 |  4069   (1)| 00:00:49 |
     
202. ---------------------------------------------------------------------------
     
203. 
     
204. Note
     
205. -----
     
206.    - dynamic sampling used for this statement (level=2)
     
207. 
     
208. 
     
209. Statistics
     
210. ----------------------------------------------------------
     
211.           0  recursive calls
     
212.           0  db block gets
     
213.       15011  consistent gets
     
214.           0  physical reads
     
215.           0  redo size
     
216.         527  bytes sent via SQL*Net to client
     
217.         523  bytes received via SQL*Net from client
     
218.           2  SQL*Net roundtrips to/from client
     
219.           0  sorts (memory)
     
220.           0  sorts (disk)
     
221.           1  rows processed
     
222. 
     
223. 
     
224. SQL> select count(*) from maclean_tan2;
     
225. 
     
226.   COUNT(*)
     
227. ----------
     
228.      15000
     
229. 
     
230. 
     
231. Execution Plan
     
232. ----------------------------------------------------------
     
233. Plan hash value: 1229461046
     
234. 
     
235. ---------------------------------------------------------------------------
     
236. | Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
     
237. ---------------------------------------------------------------------------
     
238. |   0 | SELECT STATEMENT   |              |     1 |  4069   (1)| 00:00:49 |
     
239. |   1 |  SORT AGGREGATE    |              |     1 |            |          |
     
240. |   2 |   TABLE ACCESS FULL| MACLEAN_TAN2 | 15476 |  4069   (1)| 00:00:49 |
     
241. ---------------------------------------------------------------------------
     
242. 
     
243. Note
     
244. -----
     
245.    - dynamic sampling used for this statement (level=2)
     
246. 
     
247. 
     
248. Statistics
     
249. ----------------------------------------------------------
     
250.           0  recursive calls
     
251.           0  db block gets
     
252.       15011  consistent gets
     
253.           0  physical reads
     
254.           0  redo size
     
255.         527  bytes sent via SQL*Net to client
     
256.         523  bytes received via SQL*Net from client
     
257.           2  SQL*Net roundtrips to/from client
     
258.           0  sorts (memory)
     
259.           0  sorts (disk)
     
260.           1  rows processed
     
261. 
     
262. 
     
263. http://www.oracledatabase12g.com/archives/script-list-buffer-cache-details.html
     
264. 
     
265. 
     
266. 参考以上网址的脚本
     
267. 
     
268. set pages 999
     
269. set lines 92
     
270. 
     
271. column c0 heading "Owner"                                    format a12
     
272. column c1 heading "Object|Name"                              format a30
     
273. column c2 heading "Object|Type"                              format a8
     
274. column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
     
275. column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
     
276. column c5 heading "Buffer|Pool"                              format a7
     
277. column c6 heading "Block|Size"                               format 99,999
     
278. 
     
279. select
     
280.    buffer_map.owner                                          c0,
     
281.    object_name                                       c1,
     
282.    case when object_type = 'TABLE PARTITION' then 'TAB PART'
     
283.         when object_type = 'INDEX PARTITION' then 'IDX PART'
     
284.         else object_type end c2,
     
285.    sum(num_blocks)                                     c3,
     
286.    (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
     
287.    buffer_pool                                       c5,
     
288.    sum(bytes)/sum(blocks)                            c6
     
289. from
     
290.    buffer_map,
     
291.    dba_segments s
     
292. where
     
293.    s.segment_name = buffer_map.object_name
     
294. and
     
295.    s.owner = buffer_map.owner
     
296. and
     
297.    s.segment_type = buffer_map.object_type
     
298. and
     
299.    nvl(s.partition_name,'-') = nvl(buffer_map.subobject_name,'-')
     
300. group by
     
301.    buffer_map.owner,
     
302.    object_name,
     
303.    object_type,
     
304.    buffer_pool
     
305. having
     
306.    sum(num_blocks) > 10
     
307. order by
     
308.    sum(num_blocks) desc
     
309. ;
     
310. 
     
311.                                                        Number of Percentage
     
312.                                                        Blocks in  of object
     
313.              Object                         Object        Buffer  blocks in Buffer    Block
     
314. Owner        Name                           Type           Cache     Buffer Pool       Size
     
315. ------------ ------------------------------ -------- ----------- ---------- ------- -------
     
316. SYS          MACLEAN_TAN2                   TABLE         15,001         98 KEEP      8,192
     
317. SYS          C_TOID_VERSION#                CLUSTER        1,765         57 DEFAULT   8,192
     
318. SYS          C_OBJ#                         CLUSTER        1,428         93 DEFAULT   8,192
     
319. SYS          OBJ$                           TABLE            931         91 DEFAULT   8,192
     
320. SYS          I_OBJ2                         INDEX            760         99 DEFAULT   8,192
     
321. SYS          C_FILE#_BLOCK#                 CLUSTER          198         77 DEFAULT   8,192
     
322. SYS          I_FILE#_BLOCK#                 INDEX             40        100 DEFAULT   8,192
     
323. SYS          I_OBJ1                         INDEX             37         14 DEFAULT   8,192
     
324. SYS          INDPART$                       TABLE             16        100 DEFAULT   8,192
     
325. SYS          I_HH_OBJ#_INTCOL#              INDEX             15         12 DEFAULT   8,192
     
326. SYS          HIST_HEAD$                     TABLE             15          4 DEFAULT   8,192
     
327. SYS          AQ$_SYS$SERVICE_METRICS_TAB_S  TABLE             14         88 DEFAULT   8,192
     
328. SYS          C_TS#                          CLUSTER           13         81 DEFAULT   8,192
     
329. SYS          I_DEPENDENCY1                  INDEX             13          2 DEFAULT   8,192
     
330. SYS          I_ACCESS1                      INDEX             12          2 DEFAULT   8,192
     
331. 
     
332. 15 rows selected.
     
333. 
     
334. 
     
335. 
     
336. 
     
337. 
     
338. 
     
339. 
     
340. 可以看到 MACLEAN_TAN2 表在 keep buffer pool中的详细信息， 15,001 blocks=  117MB
     
341.                   
     
342.                   

复制代码