workarea_size_policy和sort_area_size以及PGA使用情况直方图

botang

1. SQL> set timing on
   
2. SQL> select   * from t_big  order by  text   ;
   
3. 
   
4. 623430 rows selected.
   
5. 
   
6. <font color="Red" size="5">Elapsed: 00:00:05.75</font>
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. Plan hash value: 1510685833
    
11. 
    
12. --------------------------------------------------------------------------------
    
13. ----
    
14. 
    
15. | Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
    
16.    |
    
17. 
    
18. --------------------------------------------------------------------------------
    
19. ----
    
20. 
    
21. |   0 | SELECT STATEMENT   |           |   551K|  1082M|           |   239K  (1)| 00:47:
    
22. 57 |
    
23. 
    
24. |   1 |  SORT ORDER BY           |           |   551K|  1082M|  1437M|   239K  (1)| 00:47:
    
25. 57 |
    
26. 
    
27. |   2 |   TABLE ACCESS FULL| T_BIG |   551K|  1082M|           |  2829   (1)| 00:00:
    
28. 34 |
    
29. 
    
30. --------------------------------------------------------------------------------
    
31. ----
    
32. 
    
33. 
    
34. Note
    
35. -----
    
36.    - dynamic sampling used for this statement (level=2)
    
37. 
    
38. 
    
39. Statistics
    
40. ----------------------------------------------------------
    
41.           0  recursive calls
    
42.           0  db block gets
    
43.       10429  consistent gets
    
44.           0  physical reads
    
45.           0  redo size
    
46.    48792205  bytes sent via SQL*Net to client
    
47.      457690  bytes received via SQL*Net from client
    
48.       41563  SQL*Net roundtrips to/from client
    
49.           1  sorts (memory)
    
50.           0  sorts (disk)
    
51.      623430  rows processed
    
52. 
    
53. SQL> alter session set workarea_size_policy=manual;
    
54. 
    
55. Session altered.
    
56. 
    
57. Elapsed: 00:00:00.00
    
58. SQL> alter session set sort_area_size=2097152000;
    
59. 
    
60. Session altered.
    
61. 
    
62. Elapsed: 00:00:00.00
    
63. SQL> select   * from t_big  order by  text   ;
    
64. 
    
65. 623430 rows selected.
    
66. 
    
67. <font color="Red" size="5">Elapsed: 00:00:15.89</font>
    
68. 
    
69. Execution Plan
    
70. ----------------------------------------------------------
    
71. Plan hash value: 1510685833
    
72. 
    
73. ----------------------------------------------------------------------------
    
74. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time           |
    
75. ----------------------------------------------------------------------------
    
76. |   0 | SELECT STATEMENT   |           |   551K|  1082M|  2845   (1)| 00:00:35 |
    
77. |   1 |  SORT ORDER BY           |           |   551K|  1082M|  2845   (1)| 00:00:35 |
    
78. |   2 |   TABLE ACCESS FULL| T_BIG |   551K|  1082M|  2829   (1)| 00:00:34 |
    
79. ----------------------------------------------------------------------------
    
80. 
    
81. Note
    
82. -----
    
83.    - dynamic sampling used for this statement (level=2)
    
84. 
    
85. 
    
86. Statistics
    
87. ----------------------------------------------------------
    
88.           5  recursive calls
    
89.           0  db block gets
    
90.       10497  consistent gets
    
91.           0  physical reads
    
92.           0  redo size
    
93.    48792205  bytes sent via SQL*Net to client
    
94.      457690  bytes received via SQL*Net from client
    
95.       41563  SQL*Net roundtrips to/from client
    
96.           1  sorts (memory)
    
97.           0  sorts (disk)
    
98.      623430  rows processed
    
99. 
    
100. SQL> alter session set sort_area_size=2097152;
     
101. 
     
102. Session altered.
     
103. 
     
104. Elapsed: 00:00:00.00
     
105. SQL> select   * from t_big  order by  text   ;
     
106. 
     
107. 623430 rows selected.
     
108. 
     
109. Elapsed: 00:00:17.03
     
110. 
     
111. Execution Plan
     
112. ----------------------------------------------------------
     
113. Plan hash value: 1510685833
     
114. 
     
115. --------------------------------------------------------------------------------
     
116. ----
     
117. 
     
118. | Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
     
119.    |
     
120. 
     
121. --------------------------------------------------------------------------------
     
122. ----
     
123. 
     
124. |   0 | SELECT STATEMENT   |           |   551K|  1082M|           |   322K  (1)| 01:04:
     
125. 36 |
     
126. 
     
127. |   1 |  SORT ORDER BY           |           |   551K|  1082M|  1437M|   322K  (1)| 01:04:
     
128. 36 |
     
129. 
     
130. |   2 |   TABLE ACCESS FULL| T_BIG |   551K|  1082M|           |  2829   (1)| 00:00:
     
131. 34 |
     
132. 
     
133. --------------------------------------------------------------------------------
     
134. ----
     
135. 
     
136. 
     
137. Note
     
138. -----
     
139.    - dynamic sampling used for this statement (level=2)
     
140. 
     
141. 
     
142. Statistics
     
143. ----------------------------------------------------------
     
144.          74  recursive calls
     
145.         277  db block gets
     
146.       10497  consistent gets
     
147.        8741  physical reads
     
148.           0  redo size
     
149.    48672662  bytes sent via SQL*Net to client
     
150.      457690  bytes received via SQL*Net from client
     
151.       41563  SQL*Net roundtrips to/from client
     
152.           0  sorts (memory)
     
153.           1  sorts (disk)
     
154.      623430  rows processed
     
155. 
     
156. SQL> show parameter wo
     
157. SQL> set autot off
     
158. SQL> show parameter work
     
159. 
     
160. NAME                                     TYPE         VALUE
     
161. ------------------------------------ ----------- ------------------------------
     
162. fileio_network_adapters              string
     
163. listener_networks                     string
     
164. workarea_size_policy                     string         MANUAL
     
165. SQL> show parameter sor
     
166. 
     
167. NAME                                     TYPE         VALUE
     
168. ------------------------------------ ----------- ------------------------------
     
169. cursor_sharing                             string         EXACT
     
170. cursor_space_for_time                     boolean         FALSE
     
171. nls_sort                             string
     
172. open_cursors                             integer         300
     
173. session_cached_cursors                     integer         50
     
174. sort_area_retained_size              integer         0
     
175. sort_area_size                             integer         2097152
     
176. SQL>
     

复制代码