Oracle 高水位测试(HWM)

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. 
   
6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
   
7. PL/SQL Release 10.2.0.1.0 - Production
   
8. CORE 10.2.0.1.0 Production
   
9. TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
   
10. NLSRTL Version 10.2.0.1.0 - Production
    
11. 
    
12.  
    
13. 
    
14.  
    
15. 
    
16. ---1.创建测试表
    
17. 
    
18. CREATE TABLE T1 TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS;
    
19. 
    
20. --2 查询segment 头块
    
21. 
    
22. SQL> SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T1';
    
23. 
    
24. HEADER_FILE HEADER_BLOCK
    
25. ----------- ------------
    
26. 
    
27.          22 795
    
28. 
    
29. --3 DUMP segment 头块
    
30. 
    
31. 
    
32. ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;
    
33. 
    
34. --4 查看结尾为2492的trace文件 (ORAHOME\product\10.2.0\admin\orcl\udump)
    
35. 
    
36. 
    
37. SELECT SPID
    
38.   FROM V$PROCESS
    
39.  WHERE ADDR = (SELECT PADDR
    
40.                  FROM V$SESSION
    
41.                 WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));
    
42. SPID
    
43. ------------
    
44. 
    
45. 2492
    
46. 
    
47. --HWM 为 0x058008fb
    
48. 
    
49. Highwater:: 0x058008fb
    
50. 
    
51. --5 计算HWM 对应的文件编号和块
    
52. 
    
53. SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_FILEID FROM DUAL;
    
54. 
    
55. HWM_FILEID
    
56. ----------
    
57. 
    
58.         22
    
59. 
    
60. SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('058008FB','XXXXXXXX')) AS HWM_BLOCKID FROM DUAL;
    
61. 
    
62. HWM_BLOCKID
    
63. -----------
    
64. 
    
65.        2299
    
66.        
    
67. --6. 分析表，查看sgemnt空块的数及最大使用的Block ID
    
68. 
    
69. SQL> SELECT FILE_ID,MIN(BLOCK_ID),MAX(BLOCK_ID+BLOCKS) FROM DBA_EXTENTS WHERE SEGMENT_NAME='T1' GROUP BY FILE_ID;
    
70. 
    
71.    FILE_ID MIN(BLOCK_ID) MAX(BLOCK_ID+BLOCKS)
    
72. ---------- ------------- --------------------
    
73. 
    
74.         22 793 2313
    
75.         
    
76. ANALYZE TABLE T1 COMPUTE STATISTICS；
    
77. 
    
78. SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME = 'T1';
    
79. 
    
80. EMPTY_BLOCKS
    
81. ------------
    
82. 
    
83.           14
    
84. SQL> SELECT 2313 -14 FROM DUAL;--等于HWM_BLOCKID
    
85. 
    
86. 
    
87.    2313-14
    
88. ----------
    
89. 
    
90.       2299
    
91. --7.记录下delete数据前count(*)的统计信息
    
92. 
    
93. SQL> set autotrace traceonly;
    
94. SQL> select count(*) from T1;
    
95. 
    
96. 
    
97. 执行计划
    
98. ----------------------------------------------------------
    
99. 
    
100. Plan hash value: 3724264953
     
101. 
     
102. -------------------------------------------------------------------
     
105. | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |   
106. -------------------------------------------------------------------   
107. |   0 | SELECT STATEMENT   |      |     1 |   169   (2)| 00:00:03 |   
108. |   1 |  SORT AGGREGATE    |       |     1 |            |          |   
109. |   2 |   TABLE ACCESS FULL| T1   | 53176 |   169   (2)| 00:00:03 |  
110. -------------------------------------------------------------------
     
111. 
     
112. 
     
113. 
     
114. 统计信息
     
115. ----------------------------------------------------------
     
116. 
     
117.           0 recursive calls
     
118.           0 db block gets
     
119.         739 consistent gets
     
120.           0 physical reads
     
121.           0 redo size
     
122.         410 bytes sent via SQL*Net to client
     
123.         385 bytes received via SQL*Net from client
     
124.           2 SQL*Net roundtrips to/from client
     
125.           0 sorts (memory)
     
126.           0 sorts (disk)
     
127.           1 rows processed
     
128. 
     
129. --8 删除数据
     
130. 
     
131. SQL> set autotrace off;
     
132. SQL> select count(*) from T1;
     
133. 
     
134.   COUNT(*)
     
135. ----------
     
136. 
     
137.      53176
     
138.      
     
139. SQL> delete T1 where rownum <= 30000;
     
140. 
     
141. 已删除30000行。
     
142. 
     
143. SQL> COMMIT;
     
144. 
     
145. 提交完成。
     
146. 
     
147. --9 重新dump 数据头,发现Highwater仍然是原来的值。说明DELETE 并不能使HWM下降。
     
148. 
     
149. ALTER SYSTEM DUMP DATAFILE 22 BLOCK 795;
     
150. 
     
151. Highwater:: 0x058008fb
     
152. 
     
153. --10. 分析删除数据后，count(*)的统计信息，发现执行的消耗与删除前一样。739个逻辑读
     
154. 
     
155. SQL> set autotrace on;
     
156. SQL> select count(*) from T1;
     
157. 
     
158.   COUNT(*)
     
159. ----------
     
160. 
     
161.      23176
     
162. 
     
163. 
     
164. 执行计划
     
165. ----------------------------------------------------------
     
166. 
     
167. Plan hash value: 3724264953
     
168. 
     
169. -------------------------------------------------------------------
     
171. | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |   
172. -------------------------------------------------------------------   
173. |   0 | SELECT STATEMENT   |      |     1 |   169   (2)| 00:00:03 |   
174. |   1 |  SORT AGGREGATE     |      |     1 |            |          |   
175. |   2 |   TABLE ACCESS FULL| T1   | 23176 |   169   (2)| 00:00:03 |
176. -------------------------------------------------------------------
     
177. 
     
178. 
     
179. 
     
180. 统计信息
     
181. ----------------------------------------------------------
     
182. 
     
183.           0 recursive calls
     
184.           0 db block gets
     
185.         739 consistent gets
     
186.           0 physical reads
     
187.           0 redo size
     
188.         410 bytes sent via SQL*Net to client
     
189.         385 bytes received via SQL*Net from client
     
190.           2 SQL*Net roundtrips to/from client
     
191.           0 sorts (memory)
     
192.           0 sorts (disk)
     
193.           1 rows processed