<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


        

    
	

    The important thing in life is to have a great aim , and the determination 
    

    

    


    
	
		

    常用鏈接
    

		

    統計
    
	
		
		
		
    IT技術鏈接
    
		
				
			
	
		
    保險相關
    
		
				
			
	
		
    友情鏈接
    
		
				
			
	
		
    基金知識
    
		
				
			
	
		
    生活相關
    
		
				
			
	

		

    最新評論
	
    

		
	

    

    
	
					
	
    
		
    
			Oracle統計信息的收集、管理與清除
		
    
		以下測試環境為Oracle 10g 10.2.0.4版本，測試對Oracle的統計信息的收集與管理。

    首先依據dba_objects創建一張測試表：
    

    SQL> create table eygle as select * from dba_objects;
     
    Table created
    對該表進行統計信息收集，這里未指定method_opt，則Oracle將采用 FOR ALL COLUMNS SIZE AUTO 選項，這一選項在Oracle 9i中不收集列的柱狀圖信息，在Oracle 10g中則會根據數據庫的選項選擇是否收集柱狀圖，缺省的會為列收集基本信息。
    在Oracle 10g中，缺省的初始化參數 _column_tracking_level 設置為1，Oracle會動態收集列的使用情況，如果某些傾斜列被頻繁使用，則Oracle會在Auto模式下，自動為該列收集列級柱狀圖。

    看以下測試，執行缺省的表統計信息收集：
     
    

    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE');
     
    PL/SQL procedure successfully completed
     
    此時數據庫為表上的所有字段收集了缺省的統計信息，每個列兩個Bucket：
    

    SQL> col column_name for a30
    SQL> col owner for a10
    SQL> col table_name for a10
    SQL> col ENDPOINT_ACTUAL_VALUE for a10
    SQL> select * from dba_tab_histograms where table_name='EYGLE' and owner='EYGLE' order by 4;
     
    OWNER      TABLE_NAME COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- ------------------------------ --------------- -------------- ----------
    EYGLE      EYGLE      OWNER                                        0 3.492486153566 
    EYGLE      EYGLE      OBJECT_NAME                                  0 3.388431933833 
    EYGLE      EYGLE      SECONDARY                                    0 4.049991549657 
    EYGLE      EYGLE      GENERATED                                    0 4.049991549657 
    EYGLE      EYGLE      TEMPORARY                                    0 4.049991549657 
    EYGLE      EYGLE      STATUS                                       0 4.478619304731 
    EYGLE      EYGLE      TIMESTAMP                                    0 2.555831764971 
    EYGLE      EYGLE      LAST_DDL_TIME                                0 2455466.759085 
    EYGLE      EYGLE      CREATED                                      0 2455466.759085 
    EYGLE      EYGLE      OBJECT_TYPE                                  0 3.494321128346 
    EYGLE      EYGLE      DATA_OBJECT_ID                               0              2 
    EYGLE      EYGLE      OBJECT_ID                                    0              2 
    EYGLE      EYGLE      SUBOBJECT_NAME                               0 4.163573043437 
    EYGLE      EYGLE      OWNER                                        1 4.532981758140 
    EYGLE      EYGLE      GENERATED                                    1 4.621144204096 
    EYGLE      EYGLE      TEMPORARY                                    1 4.621144204096 
    EYGLE      EYGLE      STATUS                                       1 4.478619304731 
    EYGLE      EYGLE      TIMESTAMP                                    1 2.605922956775 
    EYGLE      EYGLE      LAST_DDL_TIME                                1 2455492.879583 
    EYGLE      EYGLE      SECONDARY                                    1 4.049991549657 
    EYGLE      EYGLE      OBJECT_TYPE                                  1 4.532166435311 
    EYGLE      EYGLE      DATA_OBJECT_ID                               1          12918 
    EYGLE      EYGLE      OBJECT_ID                                    1          12918 
    EYGLE      EYGLE      SUBOBJECT_NAME                               1 4.533986999644 
    EYGLE      EYGLE      OBJECT_NAME                                  1 4.956504674196 
    EYGLE      EYGLE      CREATED                                      1 2455492.879583 
     
    26 rows selected
     
    SQL> select * from dba_tab_histograms where table_name='EYGLE' and owner='EYGLE' order by 3;
     
    OWNER      TABLE_NAME COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- ------------------------------ --------------- -------------- ----------
    EYGLE      EYGLE      CREATED                                      0 2455466.759085 
    EYGLE      EYGLE      CREATED                                      1 2455492.879583 
    EYGLE      EYGLE      DATA_OBJECT_ID                               0              2 
    EYGLE      EYGLE      DATA_OBJECT_ID                               1          12918 
    EYGLE      EYGLE      GENERATED                                    0 4.049991549657 
    EYGLE      EYGLE      GENERATED                                    1 4.621144204096 
    EYGLE      EYGLE      LAST_DDL_TIME                                0 2455466.759085 
    EYGLE      EYGLE      LAST_DDL_TIME                                1 2455492.879583 
    EYGLE      EYGLE      OBJECT_ID                                    1          12918 
    EYGLE      EYGLE      OBJECT_ID                                    0              2 
    EYGLE      EYGLE      OBJECT_NAME                                  0 3.388431933833 
    EYGLE      EYGLE      OBJECT_NAME                                  1 4.956504674196 
    EYGLE      EYGLE      OBJECT_TYPE                                  1 4.532166435311 
    EYGLE      EYGLE      OBJECT_TYPE                                  0 3.494321128346 
    EYGLE      EYGLE      OWNER                                        0 3.492486153566 
    EYGLE      EYGLE      OWNER                                        1 4.532981758140 
    EYGLE      EYGLE      SECONDARY                                    1 4.049991549657 
    EYGLE      EYGLE      SECONDARY                                    0 4.049991549657 
    EYGLE      EYGLE      STATUS                                       0 4.478619304731 
    EYGLE      EYGLE      STATUS                                       1 4.478619304731 
     
    OWNER      TABLE_NAME COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- ------------------------------ --------------- -------------- ----------
    EYGLE      EYGLE      SUBOBJECT_NAME                               1 4.533986999644 
    EYGLE      EYGLE      SUBOBJECT_NAME                               0 4.163573043437 
    EYGLE      EYGLE      TEMPORARY                                    0 4.049991549657 
    EYGLE      EYGLE      TEMPORARY                                    1 4.621144204096 
    EYGLE      EYGLE      TIMESTAMP                                    0 2.555831764971 
    EYGLE      EYGLE      TIMESTAMP                                    1 2.605922956775 
     
    26 rows selected
     
    同時，列的低值、高值等信息會被收集記錄在dba_tab_col_statistics中：
    

    SQL> select table_name,column_name,num_distinct,low_value,high_value,DENSITY from dba_tab_col_statistics
      2  where table_name='EYGLE';
     
    TABLE_NAME COLUMN_NAME          NUM_DISTINCT LOW_VALUE          HIGH_VALUE                    DENSITY
    ---------- -------------------- ------------ ------------------ -------------------------- ----------
    EYGLE      OWNER                          11 43434943           574D535953                 0.09090909
    EYGLE      OBJECT_NAME                  9096 4142425F464A31     5F75746C245F6C6E635F696E64 0.00010993
    EYGLE      SUBOBJECT_NAME                 79 5030               575248245F5741495453545F35 0.01265822
    EYGLE      OBJECT_ID                   11676 C103               C3021E13                   8.56457690
    EYGLE      DATA_OBJECT_ID               2869 C103               C3021E13                   0.00034855
    EYGLE      OBJECT_TYPE                    35 434C5553544552     57494E444F572047524F5550   0.02857142
    EYGLE      CREATED                       494 786E091A130E06     786E0A16160725             0.00202429
    EYGLE      LAST_DDL_TIME                 502 786E091A130E06     786E0A16160725             0.00199203
    EYGLE      TIMESTAMP                     515 313939372D30342D31 323031302D31302D32323A3231 0.00194174
    EYGLE      STATUS                          1 56414C4944         56414C4944                          1
    EYGLE      TEMPORARY                       2 4E                 59                                0.5
    EYGLE      GENERATED                       2 4E                 59                                0.5
    EYGLE      SECONDARY                       1 4E                 4E                                  1

    這些基本信息在執行計劃生成時會被參考，不能通過for all columns size 1的收集方式刪除：
    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE',method_opt => 'for all columns size 1');
     
    PL/SQL procedure successfully completed

    SQL> select * from dba_tab_histograms where table_name='EYGLE' and owner='EYGLE' order by 3;
     
    OWNER      TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- -------------------- --------------- -------------- ----------
    EYGLE      EYGLE      CREATED                            0 2455466.759085 
    EYGLE      EYGLE      CREATED                            1 2455492.879583 
    EYGLE      EYGLE      DATA_OBJECT_ID                     0              2 
    EYGLE      EYGLE      DATA_OBJECT_ID                     1          12918 
    EYGLE      EYGLE      GENERATED                          0 4.049991549657 
    EYGLE      EYGLE      GENERATED                          1 4.621144204096 
    EYGLE      EYGLE      LAST_DDL_TIME                      0 2455466.759085 
    EYGLE      EYGLE      LAST_DDL_TIME                      1 2455492.879583 
    EYGLE      EYGLE      OBJECT_ID                          1          12918 
    EYGLE      EYGLE      OBJECT_ID                          0              2 
    EYGLE      EYGLE      OBJECT_NAME                        0 3.388431933833 
    EYGLE      EYGLE      OBJECT_NAME                        1 4.956504674196 
    EYGLE      EYGLE      OBJECT_TYPE                        1 4.532166435311 
    EYGLE      EYGLE      OBJECT_TYPE                        0 3.494321128346 
    EYGLE      EYGLE      OWNER                              0 3.492486153566 
    EYGLE      EYGLE      OWNER                              1 4.532981758140 
    EYGLE      EYGLE      SECONDARY                          1 4.049991549657 
    EYGLE      EYGLE      SECONDARY                          0 4.049991549657 
    EYGLE      EYGLE      STATUS                             0 4.478619304731 
    EYGLE      EYGLE      STATUS                             1 4.478619304731 
     
    OWNER      TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- -------------------- --------------- -------------- ----------
    EYGLE      EYGLE      SUBOBJECT_NAME                     1 4.533986999644 
    EYGLE      EYGLE      SUBOBJECT_NAME                     0 4.163573043437 
    EYGLE      EYGLE      TEMPORARY                          0 4.049991549657 
    EYGLE      EYGLE      TEMPORARY                          1 4.621144204096 
    EYGLE      EYGLE      TIMESTAMP                          0 2.555831764971 
    EYGLE      EYGLE      TIMESTAMP                          1 2.605922956775 

    類似如下刪除單列統計信息的語句也對基本統計信息無效：
    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE',method_opt=>'for columns owner size 1');
     
    PL/SQL procedure successfully completed

    但是使用delete_column_stats可以徹底刪除列的柱狀圖信息：
    

    SQL> exec dbms_stats.delete_column_stats(user,'EYGLE','OWNER');
     
    PL/SQL procedure successfully completed

    SQL> select * from dba_tab_histograms where table_name='EYGLE' and owner='EYGLE' order by 3;
     
    OWNER      TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- -------------------- --------------- -------------- ----------
    EYGLE      EYGLE      CREATED                            1 2455492.879583 
    EYGLE      EYGLE      CREATED                            0 2455466.759085 
    EYGLE      EYGLE      DATA_OBJECT_ID                     0              2 
    EYGLE      EYGLE      DATA_OBJECT_ID                     1          12918 
    EYGLE      EYGLE      GENERATED                          0 4.049991549657 
    EYGLE      EYGLE      GENERATED                          1 4.621144204096 
    EYGLE      EYGLE      LAST_DDL_TIME                      0 2455466.759085 
    EYGLE      EYGLE      LAST_DDL_TIME                      1 2455492.879583 
    EYGLE      EYGLE      OBJECT_ID                          0              2 
    EYGLE      EYGLE      OBJECT_ID                          1          12918 
    EYGLE      EYGLE      OBJECT_NAME                        0 3.388431933833 
    EYGLE      EYGLE      OBJECT_NAME                        1 4.956504674196 
    EYGLE      EYGLE      OBJECT_TYPE                        1 4.532166435311 
    EYGLE      EYGLE      OBJECT_TYPE                        0 3.494321128346 
    EYGLE      EYGLE      SECONDARY                          1 4.049991549657 
    EYGLE      EYGLE      SECONDARY                          0 4.049991549657 
    EYGLE      EYGLE      STATUS                             0 4.478619304731 
    EYGLE      EYGLE      STATUS                             1 4.478619304731 
    EYGLE      EYGLE      SUBOBJECT_NAME                     0 4.163573043437 
    EYGLE      EYGLE      SUBOBJECT_NAME                     1 4.533986999644 
     
    OWNER      TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- -------------------- --------------- -------------- ----------
    EYGLE      EYGLE      TEMPORARY                          0 4.049991549657 
    EYGLE      EYGLE      TEMPORARY                          1 4.621144204096 
    EYGLE      EYGLE      TIMESTAMP                          0 2.555831764971 
    EYGLE      EYGLE      TIMESTAMP                          1 2.605922956775 
     
    24 rows selected

    但是通常Oracle不建議刪除列上的基本統計信息，因為這些信息在進行執行計劃選擇時可能極為有用，比如判斷某些謂詞的取值是否越界等。

    看以下操作，先完整收集13列26行統計信息：

    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE');
     
    PL/SQL procedure successfully completed
     
    然后在表上執行基于OWNER的查詢： 
    

    SQL> select count(*) from eygle where owner='SYS';
     
      COUNT(*)
    ----------
          6729
     
    SQL> select count(*) from eygle where owner='EYGLE';
     
      COUNT(*)
    ----------
           240
     
    SQL> select count(*) from eygle where owner='SYSTEM';
     
      COUNT(*)
    ----------
           449

    再來進行缺省條件的統計信息收集： 
    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE');
     
    PL/SQL procedure successfully completed
     
    此時你可能會注意到，OWNER列的柱狀圖已經被收集：
    

    SQL> select * from dba_tab_histograms where table_name='EYGLE' and owner='EYGLE' order by 3;
     
    OWNER      TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
    ---------- ---------- -------------------- --------------- -------------- ----------
    EYGLE      EYGLE      CREATED                            1 2455492.879583 
    EYGLE      EYGLE      CREATED                            0 2455466.759085 
    EYGLE      EYGLE      DATA_OBJECT_ID                     1          12918 
    EYGLE      EYGLE      DATA_OBJECT_ID                     0              2 
    EYGLE      EYGLE      GENERATED                          1 4.621144204096 
    EYGLE      EYGLE      GENERATED                          0 4.049991549657 
    EYGLE      EYGLE      LAST_DDL_TIME                      0 2455466.759085 
    EYGLE      EYGLE      LAST_DDL_TIME                      1 2455492.879583 
    EYGLE      EYGLE      OBJECT_ID                          1          12918 
    EYGLE      EYGLE      OBJECT_ID                          0              2 
    EYGLE      EYGLE      OBJECT_NAME                        1 4.956504674196 
    EYGLE      EYGLE      OBJECT_NAME                        0 3.388431933833 
    EYGLE      EYGLE      OBJECT_TYPE                        1 4.532166435311 
    EYGLE      EYGLE      OBJECT_TYPE                        0 3.494321128346 
    EYGLE      EYGLE      OWNER                            312 4.119221354213 
    EYGLE      EYGLE      OWNER                           2842 4.171130061672 
    EYGLE      EYGLE      OWNER                          11679 4.532981758140 
    EYGLE      EYGLE      OWNER                           9571 4.327723496506 
    EYGLE      EYGLE      OWNER                          11361 4.327723757311 
    EYGLE      EYGLE      OWNER                          11364 4.378425024777 
    EYGLE      EYGLE      OWNER                          10912 4.327723735598 
    EYGLE      EYGLE      OWNER                              9 3.492486153566 
    EYGLE      EYGLE      OWNER                             55 3.544214255849 
    EYGLE      EYGLE      OWNER                            295 3.600792664974 
    EYGLE      EYGLE      OWNER                            303 4.118597800700 
    EYGLE      EYGLE      SECONDARY                          0 4.049991549657 
    EYGLE      EYGLE      SECONDARY                          1 4.049991549657 
    EYGLE      EYGLE      STATUS                             0 4.478619304731 
    EYGLE      EYGLE      STATUS                             1 4.478619304731 
    EYGLE      EYGLE      SUBOBJECT_NAME                     0 4.163573043437 
    EYGLE      EYGLE      SUBOBJECT_NAME                     1 4.533986999644 
    EYGLE      EYGLE      TEMPORARY                          1 4.621144204096 
    EYGLE      EYGLE      TEMPORARY                          0 4.049991549657 
    EYGLE      EYGLE      TIMESTAMP                          1 2.605922956775 
    EYGLE      EYGLE      TIMESTAMP                          0 2.555831764971 
     
    35 rows selected
     
    這種現象就來源于 _column_tracking_level 的特性監控，如果不希望發生這樣的信息收集，則可以調整這個隱含的參數。

    此時再使用delete_column_stats就可以刪除這些字段的統計信息：
    

    SQL> exec dbms_stats.delete_column_stats(user,'EYGLE','OWNER');
     
    PL/SQL procedure successfully completed
    也可以針對這個列使用如下命令清除這個字段的柱狀圖但是保留基本統計信息：
    

    SQL> exec dbms_stats.gather_table_stats(user,'EYGLE',method_opt=>'for columns owner size 1');
     
    PL/SQL procedure successfully completed
    通常推薦使用'for columns owner size 1' 而不是delete_column_stats去清除列的統計信息，完全刪除列的基本統計信息在某些Bug的作用下，可能會導致優化器計算的異常。

    在統計信息收集時，必須注意到這些選項和后臺動作，否則就可能出現和面對很多莫名其妙的問題。

    
		
    
			posted on 2014-05-16 18:21 鴻雁 閱讀(197) 評論(0)  編輯  收藏  所屬分類: 數據庫 
		
    
	
    
	
	


	


    
	    
    




    








    

				

    




    







    
        
	



主站蜘蛛池模板：
亚洲最大的黄色网|
99热在线日韩精品免费|
深夜国产福利99亚洲视频|
三年片免费高清版|
亚洲不卡中文字幕|
亚洲午夜日韩高清一区
|
国产精品成人免费福利|
亚洲AV无码国产剧情|
久久香蕉国产线看观看亚洲片|
国外成人免费高清激情视频|
免费无码又爽又刺激高潮软件|
91丁香亚洲综合社区|
亚洲精品无码永久中文字幕|
无码人妻精品一二三区免费|
国产成人精品一区二区三区免费|
亚洲AV无码一区二区三区性色|
亚洲av永久无码精品漫画|
国产精品免费播放|
99久久人妻精品免费二区|
曰批全过程免费视频观看免费软件|
亚洲第一页在线视频|
激情综合色五月丁香六月亚洲|
免费a级毛片无码a∨蜜芽试看|
永久免费A∨片在线观看|
疯狂做受xxxx高潮视频免费|
亚洲AV无码国产精品色|
亚洲va国产va天堂va久久|
亚洲不卡无码av中文字幕|
成年大片免费视频|
5g影院5g天天爽永久免费影院|
一级毛片在播放免费|
亚洲精品无AMM毛片|
亚洲日本香蕉视频|
婷婷亚洲久悠悠色悠在线播放|
免费一级特黄特色大片在线
|
91精品手机国产免费|
成人免费无码H在线观看不卡|
亚洲av无码偷拍在线观看|
亚洲伊人久久大香线焦|
亚洲久本草在线中文字幕|
亚洲精品午夜国产VA久久成人
|