课程第28次（2018-08-17星期五）

botang

1. select   dbms_stats.get_prefs(pname => 'STALE_PERCENT')
   
2. from dual;
   
3. 
   
4. select  * from  dba_tab_stat_prefs p  where p.owner='SH' and p.table_name='SALES';
   
5. 
   
6. select   dbms_stats.get_prefs(pname=>'STALE_PERCENT', ownname => 'SH',
   
7. tabname => 'SALES') from dual;
   
8. 
   
9. begin
   
10.    dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13');
    
11. end;
    
12. 
    
13. begin
    
14.   dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',pvalue => 9);
    
15. end;

复制代码

1. begin
   
2.    dbms_stats.set_table_prefs('SH','SALES','METHOD_OPT',
   
3.       'for all columns size auto for columns amount_sold size 254');
   
4. end;

复制代码

for all columns  size skewonly

--------------------------------------------------
动态采样和pending area 还是有关系的：

1. SQL> ! vim show_dynamic_stats.sql
   
2. 
   
3. SQL> @show_dynamic_stats.sql
   
4. SQL> -- Try without dynamic sampling
   
5. SQL> --
   
6. SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;
   
7. 
   
8. Explained.
   
9. 
   
10. SQL>
    
11. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
    
12. 
    
13. PLAN_TABLE_OUTPUT
    
14. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
15. Plan hash value: 349803950
    
16. 
    
17. --------------------------------------------
    
18. | Id  | Operation          | Name   | Rows  |
    
19. --------------------------------------------
    
20. |   0 | SELECT STATEMENT  |           |         1 |
    
21. |   1 |  TABLE ACCESS FULL| TABJFV |         1 |
    
22. --------------------------------------------
    
23. 
    
24. 8 rows selected.
    
25. 
    
26. SQL>
    
27. SQL>
    
28. SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
    
29. SQL> -- kick in. It will use multi column stats and produce the same estimate
    
30. SQL> -- (100 rows) as before.
    
31. SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;
    
32. 
    
33. Explained.
    
34. 
    
35. SQL>
    
36. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
    
37. 
    
38. PLAN_TABLE_OUTPUT
    
39. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
40. Plan hash value: 349803950
    
41. 
    
42. --------------------------------------------
    
43. | Id  | Operation          | Name   | Rows  |
    
44. --------------------------------------------
    
45. |   0 | SELECT STATEMENT  |           |   200 |
    
46. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
    
47. --------------------------------------------
    
48. 
    
49. 8 rows selected.
    
50. 
    
51. SQL>
    
52. SQL>
    
53. SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
    
54. SQL> -- estimate (200 rows)
    
55. SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;
    
56. 
    
57. Explained.
    
58. 
    
59. SQL>
    
60. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
    
61. 
    
62. PLAN_TABLE_OUTPUT
    
63. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
64. Plan hash value: 349803950
    
65. 
    
66. --------------------------------------------
    
67. | Id  | Operation          | Name   | Rows  |
    
68. --------------------------------------------
    
69. |   0 | SELECT STATEMENT  |           |   200 |
    
70. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
    
71. --------------------------------------------
    
72. 
    
73. 8 rows selected.
    
74. 
    
75. SQL>
    
76. SQL> show parameter optimi
    
77. 
    
78. NAME                                     TYPE         VALUE
    
79. ------------------------------------ ----------- ------------------------------
    
80. optimizer_capture_sql_plan_baselines boolean         FALSE
    
81. optimizer_dynamic_sampling             integer         0
    
82. optimizer_features_enable             string         11.2.0.1
    
83. optimizer_index_caching              integer         0
    
84. optimizer_index_cost_adj             integer         100
    
85. optimizer_mode                             string         ALL_ROWS
    
86. optimizer_secure_view_merging             boolean         TRUE
    
87. optimizer_use_invisible_indexes      boolean         FALSE
    
88. optimizer_use_pending_statistics     boolean         FALSE
    
89. optimizer_use_sql_plan_baselines     boolean         TRUE
    
90. plsql_optimize_level                     integer         2
    
91. SQL> alter session set optimizer_use_pending_statistics=true;
    
92. 
    
93. Session altered.
    
94. 
    
95. SQL> @show_dynamic_stats.sql
    
96. SQL> -- Try without dynamic sampling
    
97. SQL> --
    
98. SQL> explain plan for select * from tabjfv where c1 = 1 and c2 = 1;
    
99. 
    
100. Explained.
     
101. 
     
102. SQL>
     
103. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
     
104. 
     
105. PLAN_TABLE_OUTPUT
     
106. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     
107. Plan hash value: 349803950
     
108. 
     
109. --------------------------------------------
     
110. | Id  | Operation          | Name   | Rows  |
     
111. --------------------------------------------
     
112. |   0 | SELECT STATEMENT  |           |   100 |
     
113. |   1 |  TABLE ACCESS FULL| TABJFV |   100 |
     
114. --------------------------------------------
     
115. 
     
116. 8 rows selected.
     
117. 
     
118. SQL>
     
119. SQL>
     
120. SQL> -- Try the queries with dynamic sampling level 3. Dynamic sampling will not
     
121. SQL> -- kick in. It will use multi column stats and produce the same estimate
     
122. SQL> -- (100 rows) as before.
     
123. SQL> explain plan for select /*+ dynamic_sampling(3) */ * from tabjfv where c1 = 1 and c2 = 1;
     
124. 
     
125. Explained.
     
126. 
     
127. SQL>
     
128. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
     
129. 
     
130. PLAN_TABLE_OUTPUT
     
131. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     
132. Plan hash value: 349803950
     
133. 
     
134. --------------------------------------------
     
135. | Id  | Operation          | Name   | Rows  |
     
136. --------------------------------------------
     
137. |   0 | SELECT STATEMENT  |           |   100 |
     
138. |   1 |  TABLE ACCESS FULL| TABJFV |   100 |
     
139. --------------------------------------------
     
140. 
     
141. 8 rows selected.
     
142. 
     
143. SQL>
     
144. SQL>
     
145. SQL> -- Try with level 4. Dynamic sampling kicks in. we should see the improved
     
146. SQL> -- estimate (200 rows)
     
147. SQL> explain plan for select /*+ dynamic_sampling(4) */ * from tabjfv where c1 = 1 and c2 = 1;
     
148. 
     
149. Explained.
     
150. 
     
151. SQL>
     
152. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
     
153. 
     
154. PLAN_TABLE_OUTPUT
     
155. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     
156. Plan hash value: 349803950
     
157. 
     
158. --------------------------------------------
     
159. | Id  | Operation          | Name   | Rows  |
     
160. --------------------------------------------
     
161. |   0 | SELECT STATEMENT  |           |   200 |
     
162. |   1 |  TABLE ACCESS FULL| TABJFV |   200 |
     
163. --------------------------------------------
     
164. 
     
165. 8 rows selected.
     
166. 
     
167. SQL>
     
168. SQL>
     

复制代码