oracle 10g_10.2.0.5如何合理高效使用dbms_stats收集統計資訊呢_part1

wisdomone1發表於2014-08-07
     前言:
   oracle sql如何生成合理的執行計劃,CBO選取sql最佳執行計劃的關鍵,即確保sql所引用的表及索引相關的統計資訊正確。那麼oracle如何收集統計資訊,它是採用dbms_stats包來實現收集統計資訊。
     dbms_stats包功能十分強大,我們依次展開測試與學習,希望對大家有所補益。

    

    測試步驟:


1,資料庫版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> 

2,建立測試表並插入資料
SQL> conn tbs_11204/system
Connected.
SQL> create table t_stat(a int,b int);

Table created.

SQL> insert into t_stat select mod(level,5),mod(level,3)+2 from dual connect by level<=200000;

200000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'t_stat',cascade=>true);

PL/SQL procedure successfully completed.


3,建立儲存表統計資訊的表

CREATE_STAT_TABLE Procedure

This procedure creates a table with name stattab in ownname's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.

Syntax

DBMS_STATS.CREATE_STAT_TABLE (
   ownname  VARCHAR2, 
   stattab  VARCHAR2,
   tblspace VARCHAR2 DEFAULT NULL);

Parameters

Table 103-8 CREATE_STAT_TABLE Procedure Parameters

Parameter Description

ownname

Name of the schema

stattab

Name of the table to create. This value should be passed as the stattab parameter to other procedures when the user does not want to modify the dictionary statistics directly.

tblspace

Tablespace in which to create the statistics tables. If none is specified, then they are created in the user's default tablespace.


Exceptions

ORA-20000: Table already exists or insufficient privileges.

ORA-20001: Tablespace does not exist.



SQL> conn tbs_11204/system
Connected.

SQL> exec dbms_stats.create_stat_table('tbs_11204','stat_t_stat');

PL/SQL procedure successfully completed.

SQL> desc stat_t_stat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30)
 C2                                                 VARCHAR2(30)
 C3                                                 VARCHAR2(30)
 C4                                                 VARCHAR2(30)
 C5                                                 VARCHAR2(30)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000)

SQL> select count(*) from stat_t_stat;

  COUNT(*)
----------
         0

4,export_table_stats匯出表的統計資訊到儲存統計資訊的表
(注:可以級聯匯出表的列及索引統計也匯出)

EXPORT_TABLE_STATS Procedure

This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index and column statistics associated with the specified table being exported as well.

Syntax

DBMS_STATS.EXPORT_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   cascade  BOOLEAN  DEFAULT TRUE,
   statown  VARCHAR2 DEFAULT NULL);

Parameters

Table 103-25 EXPORT_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.

stattab

User statistics table identifier describing where to store the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

If true, then column and index statistics for this table are also exported

statown

Schema containing stattab (if different than ownname)



SQL> exec dbms_stats.export_table_stats('tbs_11204','t_stat',null,'stat_t_stat',null,true,'tbs_11204');

PL/SQL procedure successfully completed.

SQL> select count(*) from stat_t_stat;

  COUNT(*)
----------
         3


5,從儲存統計資訊的表匯入統計回表

IMPORT_TABLE_STATS Procedure

 retrieves statistics for a particular table from the user statistics table identified by stattab and stores them in the dictionary. Cascade results in all index and column statistics associated with the specified table being imported as well.

Syntax

DBMS_STATS.IMPORT_TABLE_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2,
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade       BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
                                    get_param('NO_INVALIDATE')),
   force         BOOLEAN DEFAULT FALSE);

Parameters

Table 103-46 IMPORT_TABLE_STATS Procedure Parameters

Parameter Description

ownname

Name of the schema

tabname

Name of the table

partname

Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are imported.

stattab

User statistics table identifier describing from where to retrieve the statistics

statid

Identifier (optional) to associate with these statistics within stattab

cascade

If true, then column and index statistics for this table are also imported

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the .

force

Imports statistics even if table statistics are locked


Exceptions

ORA-20000: Object does not exist or insufficient privileges.

ORA-20001: Invalid or inconsistent values in the user statistics table. 


SQL> exec dbms_stats.import_table_stats('tbs_11204','t_stat',null,'stat_t_stat',null,true,'tbs_11204',true,true);

PL/SQL procedure successfully completed.


小結:
          1,本文主要測試了dbms_stats包的過程create_stat_table,export_table_stats,import_table_stats
         
          2,  create_stat_table用於建立儲存表統計資訊的表
          
          3,  export_table_stats用於匯出表的統計資訊到由create_stat_table建立的儲存表統計資訊的表
          
          4,  import_table_stats用於匯入create_stat_table建立的儲存表統計資訊的表到對應的表
          
          5,  export_table_stats有一些選項如:partable適用於分割槽表,cascade適用於同步級聯匯出表和表列及索引的統計資訊
               no_invalidate如果配置為true,不會馬上令依賴於表的附屬物件無效,而是讓ORACLE自行判斷何時令表附屬物件失效
               force選項用於如果表的統計正被鎖定,也可以強行匯出或匯入表的統計資訊

擴充套件閱讀:
     1,oracle dbms_stats語法


學習方法與思路:
         1,獲取oracle的官方文件

         2,定位與你學習相關的官方文件(注:馬上定位到所需的具體文件也是一種能力)

         3,快速瀏覽蒐集到的官方文件,掌握概要資訊,準備進行針對性測試

         4,整理測試文件,豐富自己的知識體系

         5,到MOS更多搜尋與dbms_stats相關的文件或相關bug,便於在生產實施中更好評估與規避風險
     

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1247518/,如需轉載,請註明出處,否則將追究法律責任。

相關文章