关于群主“CBO为什么不走索引？”的问题

aaaaaaaa2000

看了群主一片文章“CBO为什么不走索引？”
我按照群主的方式走下来还是获得不到群主的结果，我的步骤如下：
SQL>  create table maclean as select * from dba_objects;

Table created.

SQL>  update maclean set status='INVALID' where owner='TEST';

3 rows updated.

SQL> COMMIT;

Commit complete.

SQL> create index ind_maclean on maclean(status);

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');

PL/SQL procedure successfully completed.

SQL> select obj# from obj$ t where t.name='MACLEAN';

      OBJ#
----------
     74890

SQL> select count(*) from col_usage$ t where t.obj#=74890;

  COUNT(*)
----------
         1

SQL> WITH hist_data AS (
  2  SELECT endpoint_value,endpoint_actual_value,
  3         NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
  4         endpoint_number,
  5         endpoint_number,
  6         endpoint_number
  7         - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
  8             bucket_count
  9  FROM dba_tab_histograms
10  JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
11  WHERE     owner = '&owner'
12        AND table_name = '&table'
13        AND column_name = '&column'
14        AND histogram='FREQUENCY')
15  SELECT hexstr(endpoint_value) char_value,
16         bucket_count,
17         ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
18         RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
19    FROM hist_data
20  ORDER BY endpoint_value;
Enter value for owner: SYS
old  11: WHERE     owner = '&owner'
new  11: WHERE     owner = 'SYS'
Enter value for table: MACLEAN
old  12:       AND table_name = '&table'
new  12:       AND table_name = 'MACLEAN'
Enter value for column: STATUS
old  13:       AND column_name = '&column'
new  13:       AND column_name = 'STATUS'

no rows selected
SQL> declare
  2      begin
  3      for i in 1..500 loop
execute immediate ' alter system flush shared_pool';
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    execute immediate 'select count(*)  from maclean where status=''INVALID'' ' ;
    end loop;
  8      end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from col_usage$ t where t.obj#=74890;

  COUNT(*)
----------
         2

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');

PL/SQL procedure successfully completed.

SQL> WITH hist_data AS (
  2  SELECT endpoint_value,endpoint_actual_value,
  3         NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
  4         endpoint_number,
  5         endpoint_number,
  6         endpoint_number
  7         - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
  8             bucket_count
  9  FROM dba_tab_histograms
10  JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
11  WHERE     owner = '&owner'
12        AND table_name = '&table'
13        AND column_name = '&column'
14        AND histogram='FREQUENCY')
15  SELECT hexstr(endpoint_value) char_value,
16         bucket_count,
17         ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
18         RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
19    FROM hist_data
20  ORDER BY endpoint_value;
Enter value for owner: SYS
old  11: WHERE     owner = '&owner'
new  11: WHERE     owner = 'SYS'
Enter value for table: MACLEAN
old  12:       AND table_name = '&table'
new  12:       AND table_name = 'MACLEAN'
Enter value for column: STATUS
old  13:       AND column_name = '&column'
new  13:       AND column_name = 'STATUS'

CHAR_VALUE BUCKET_COUNT        PCT  PCT_OF_MAX
--------------------------------------------------------------------------------
VALIC3                5570                         100     *************************************************
SQL>
为什么还是没有收集到柱状图的信息那？

Maclean Liu(刘相兵

CHAR_VALUE BUCKET_COUNT        PCT  PCT_OF_MAX
--------------------------------------------------------------------------------
VALIC3                5570                         100     *************************************************


这就是柱状图信息啊 ， 只是并不准确

aaaaaaaa2000

要进行什么操作才能收集到正确的直方图信息那？

Maclean Liu(刘相兵

因为dbms_stats 默认自动选择采样的比例，而status=''INVALID'' ' 的数据行可能正好没有被采样到。

如：

1. SQL> update maclean set status='INVALID' where owner='MACLEAN';
   
2. 
   
3. 9 rows updated.
   
4. 
   
5. SQL> commit;
   
6. 
   
7. Commit complete.
   
8. 
   
9. SQL> create index ind_maclean on maclean(status);
   
10. 
    
11. Index created.
    
12. 
    
13. SQL>  declare
    
14.   2      begin
    
15.   3      for i in 1..500 loop
    
16.   4     execute immediate ' alter system flush shared_pool';
    
17.   5     DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    
18.   6      execute immediate 'select count(*)  from maclean where status=''INVALID'' ' ;
    
19.   7      end loop;
    
20.   8      end;
    
21.   9      /
    
22. 
    
23. PL/SQL procedure successfully completed.
    
24. 
    
25. 
    
26. SQL>  declare
    
27.   2      begin
    
28.   3      for i in 1..500 loop
    
29.   4     execute immediate ' alter system flush shared_pool';
    
30.   5     DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    
31.   6      execute immediate 'select count(*)  from maclean where status=''INVALID'' ' ;
    
32.   7      end loop;
    
33.   8      end;
    
34.   9      /
    
35. 
    
36. PL/SQL procedure successfully completed.
    
37. 
    
38. SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');
    
39. 
    
40. PL/SQL procedure successfully completed.
    
41. 
    
42. SQL> WITH hist_data AS (
    
43.   2  SELECT endpoint_value,endpoint_actual_value,
    
44.   3         NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
    
45.   4         endpoint_number,
    
46.   5         endpoint_number,
    
47.   6         endpoint_number
    
48.   7         - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
    
49.   8             bucket_count
    
50.   9  FROM dba_tab_histograms
    
51. 10  JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
    
52. 11  WHERE     owner = '&owner'
    
53. 12        AND table_name = '&table'
    
54. 13        AND column_name = '&column'
    
55. 14        AND histogram='FREQUENCY')
    
56. 15  SELECT hexstr(endpoint_value) char_value,
    
57. 16         bucket_count,
    
58. 17         ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
    
59. 18         RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
    
60. 19    FROM hist_data
    
61. 20  ORDER BY endpoint_value;
    
62. Enter value for owner: SYS
    
63. old  11: WHERE     owner = '&owner'
    
64. new  11: WHERE     owner = 'SYS'
    
65. Enter value for table: MACLEAN
    
66. old  12:       AND table_name = '&table'
    
67. new  12:       AND table_name = 'MACLEAN'
    
68. Enter value for column: STATUS
    
69. old  13:       AND column_name = '&column'
    
70. new  13:       AND column_name = 'STATUS'
    
71. 
    
72. CHAR_VALUE
    
73. --------------------------------------------------------------------------------
    
74. BUCKET_COUNT        PCT
    
75. ------------ ----------
    
76. PCT_OF_MAX
    
77. --------------------------------------------------------------------------------
    
78. VALIC3
    
79.         5546        100
    
80. *************************************************
    
81. 
    
82. 以上使用AUTO_SAMPLE自动采样大小 得到我们认为不准确的直方图
    
83. 
    
84. 
    
85. 我们手动指定100%的采样比例
    
86. 
    
87. SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'MACLEAN',estimate_percent => 100);
    
88. 
    
89. PL/SQL procedure successfully completed.
    
90. 
    
91. 
    
92. SQL> set linesize 80;
    
93. SQL> WITH hist_data AS (
    
94.   2  SELECT endpoint_value,endpoint_actual_value,
    
95.   3         NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
    
96.   4         endpoint_number,
    
97.   5         endpoint_number,
    
98.   6         endpoint_number
    
99.   7         - NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
    
100.   8             bucket_count
     
101.   9  FROM dba_tab_histograms
     
102. 10  JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
     
103. 11  WHERE     owner = '&owner'
     
104. 12        AND table_name = '&table'
     
105. 13        AND column_name = '&column'
     
106. 14        AND histogram='FREQUENCY')
     
107. 15  SELECT hexstr(endpoint_value) char_value,
     
108. 16         bucket_count,
     
109. 17         ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
     
110. 18         RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
     
111. 19    FROM hist_data
     
112. 20  ORDER BY endpoint_value;
     
113. Enter value for owner: SYS
     
114. old  11: WHERE     owner = '&owner'
     
115. new  11: WHERE     owner = 'SYS'
     
116. Enter value for table: MACLEAN
     
117. old  12:       AND table_name = '&table'
     
118. new  12:       AND table_name = 'MACLEAN'
     
119. Enter value for column: STATUS
     
120. old  13:       AND column_name = '&column'
     
121. new  13:       AND column_name = 'STATUS'
     
122. 
     
123. CHAR_VALUE
     
124. --------------------------------------------------------------------------------
     
125. BUCKET_COUNT        PCT
     
126. ------------ ----------
     
127. PCT_OF_MAX
     
128. --------------------------------------------------------------------------------
     
129. INVALI
     
130.           10        .02
     
131. 
     
132. 
     
133. VALIC3
     
134.        55539      99.98
     
135. *************************************************

复制代码

得到期望的直方图

Maclean Liu(刘相兵

注意今后如果贴代码的话，建议用代码模式"

1. " "

复制代码

"