emp表有如下數(shù)據(jù)。
成都創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站設計、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設與策劃設計,民權(quán)網(wǎng)站建設哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設十余年,網(wǎng)設計領域的專業(yè)建站公司;建站業(yè)務涵蓋:民權(quán)等地區(qū)。民權(quán)做網(wǎng)站價格咨詢:18980820575
SQL> select ename,deptno from emp; ENAME DEPTNO ------------------------------ ---------- SMITH 20 ALLEN 30 WARD 30 JONES 20 MARTIN 30 BLAKE 30 CLARK 10 SCOTT 20 KING 10 TURNER 30 ADAMS 20 JAMES 30 FORD 20 MILLER 10 14 rows selected.
假設我們有如下簡單的查詢
select ename,deptno from emp where ename='RICH' and deptno=10;
那么Oracle在執(zhí)行查詢的時候，是先比較ename字段呢？還是先比較deptno字段呢？
顯然先比較deptno再比較ename字段的效率明顯低于先比較ename，再比較deptno。 那Oracle究竟如何去判斷呢？
我們先查詢一張表
SQL> COL COLUMN_NAME FOR A30 SQL> SELECT column_name, num_distinct, density FROM dba_tab_columns WHERE owner = 'SCOTT' AND table_name = 'EMP'; COLUMN_NAME NUM_DISTINCT DENSITY ------------------------------ ------------ ---------- EMPNO 14 .071428571 ENAME 14 .071428571 JOB 5 .2 MGR 6 .166666667 HIREDATE 13 .076923077 SAL 12 .083333333 COMM 4 .25 DEPTNO 3 .333333333 8 rows selected.
Oracle其實知道，你的表中存放數(shù)據(jù)的一些特征，上面語句顯示的只是鳳毛麟角。通過這些特征，Oracle優(yōu)化器就能知道如何去查詢，使得執(zhí)行的效率最高。
以上這些信息，我們稱之為對象的統(tǒng)計信息。那么如何收集統(tǒng)計信息呢？
一、 analyze 命令
使用analyze命令可以收集統(tǒng)計信息，如：
收集或刪除對象的統(tǒng)計信息
驗證對象的結(jié)構(gòu)
確定table 或cluster的migrated 和chained rows。
示例：
SQL> create user anal identified by anal ; User created. SQL> grant resource,connect to anal; Grant succeeded. SQL> grant select any dictionary to anal; Grant succeeded. SQL> conn anal/anal Connected. SQL> create table t1 as select * from dba_objects; SQL> create table t2 as select * from dba_objects; SQL> create table t3 as select * from dba_objects; SQL> create table t4 as select * from dba_objects; SQL> create table t5 as select * from dba_objects; SQL> create table t6 as select * from dba_objects; SQL> create unique index pk_t1_idx on t1(object_id); SQL> create unique index pk_t2_idx on t2(object_id); SQL> create unique index pk_t3_idx on t3(object_id); SQL> create unique index pk_t4_idx on t4(object_id); SQL> create unique index pk_t5_idx on t5(object_id); SQL> create unique index pk_t6_idx on t6(object_id);
我們先查看一下統(tǒng)計信息是否存在
查看表的統(tǒng)計信息
重慶分公司
重慶分公司
SQL> select table_name, num_rows, blocks, empty_blocks
from user_tables
where table_name in ('T1', 'T2', 'T3', 'T4', 'T5','T6');查看字段統(tǒng)計信息
select table_name,
column_name,
num_distinct,
low_value,
high_value,
density
from user_tab_columns
where table_name in ('T1', 'T2', 'T3', 'T4','T5','T6');查看索引統(tǒng)計信息
SQL> col table_name for a30 SQL> col index_name for a30 SELECT table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor, num_rows FROM user_indexes TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS ------------------------------ ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ---------- T6 PK_T6_IDX 1 155 74564 1 1 1174 74564 T5 PK_T5_IDX 1 155 74563 1 1 1174 74563 T4 PK_T4_IDX 1 155 74562 1 1 1174 74562 T3 PK_T3_IDX 1 155 74561 1 1 1174 74561 T2 PK_T2_IDX 1 155 74560 1 1 1174 74560 T1 PK_T1_IDX 1 155 74559 1 1 1174 74559 6 rows selected.
表沒有任何統(tǒng)計數(shù)據(jù)，但是索引已經(jīng)有統(tǒng)計信息，可見在建立表的時候會默認收集統(tǒng)計信息。
先將索引的統(tǒng)計信息刪除
SQL> analyze table t1 delete statistics; analyze table t2 delete statistics; analyze table t3 delete statistics; analyze table t4 delete statistics; analyze table t5 delete statistics; analyze table t6 delete statistics;
驗證索引上是否還存在統(tǒng)計信息
SELECT table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key avg_leaf_blocks, avg_data_blocks_per_key avg_data_blocks, clustering_factor, num_rows FROM user_indexes
執(zhí)行統(tǒng)計信息命令，并查看統(tǒng)計信息有無變化
analyze table t1 compute statistics for table;
--針對表收集信息，查看user_tables
analyze table t2 compute statistics for all columns;
--針對表字段收集信息，查看user_tab_columns
analyze table t3 compute statistics for all indexed columns;
--收集索引字段信息
analyze table t4 compute statistics;
--收集表，表字段，索引信息
analyze table t5 compute statistics for all indexes;
--收集索引信息
analyze table t6 compute statistics for table for all indexes for all columns;
--收集表，表字段，索引信息
二、DBMS_STATS包
Oracle推薦使用DBMS_STATS這個包來收集統(tǒng)計信息。這個包的功能非常多?？梢允占瘮?shù)據(jù)庫級別、schema級別及表級別的統(tǒng)計信息。還可以對統(tǒng)計信息刪除、鎖定、導出、導入等。我們以最常用的表級別統(tǒng)計為例說明DBMS_STATS該如何使用。
收集的統(tǒng)計信存儲在dba_tab_statistics、dba_ind_statistics和dba_tab_col_statistics表中。
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE);參數(shù)說明如下：
示例：
SQL> col table_name for a30 SQL> SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM user_tab_statistics; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN ------------------------------ ---------- ---------- ------------ ----------- T1 74559 1088 0 98 T2 T3 T4 T5 T6 6 rows selected.
刪除統(tǒng)計信息
DBMS_STATS.DELETE_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);鎖定統(tǒng)計信息
DBMS_STATS.LOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
鎖定以后就不能再執(zhí)行統(tǒng)計信息
SQL> exec dbms_stats.lock_table_stats(user,'T1'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true); BEGIN dbms_stats.gather_table_stats(user,'t1',cascade=>true); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 23829 ORA-06512: at "SYS.DBMS_STATS", line 23880 ORA-06512: at line 1
導出、導入統(tǒng)計信息
要導出統(tǒng)計信息首先要建立一個統(tǒng)計表
語法：
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
SQL> exec DBMS_STATS.CREATE_STAT_TABLE (user,'STAT_TMP','SYSAUX'); PL/SQL procedure successfully completed.
2. 將表t1統(tǒng)計信息導出
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, stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
SQL> EXEC DBMS_STATS.EXPORT_TABLE_STATS (ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP'); PL/SQL procedure successfully completed.
3. 導入統(tǒng)計信息
語法：
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,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);SQL> exec dbms_stats.UNlock_table_stats(user,'T1'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.delete_table_stats(user,'T1'); PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.IMPORT_TABLE_STATS (ownname=>USER,tabname=>'T1',stattab=>'STAT_TMP'); PL/SQL procedure successfully completed. SQL> SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM user_tab_statistics; 2 3 4 5 6 TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN ------------------------------ ---------- ---------- ------------ ----------- T1 74559 1088 0 98 T2 T3 T4 T5 T6 STAT_TMP 7 rows selected.
如果是分區(qū)表，新的分區(qū)來不及收集統(tǒng)計系統(tǒng)，可以使用其它的分區(qū)統(tǒng)計信息來生成新分區(qū)的統(tǒng)計信息
DBMS_STATS.COPY_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, force BOOLEAN DEFAULT FALSE);
如果表還沒有統(tǒng)計信息，那么在執(zhí)行sql語句時，Oracle會動態(tài)的采樣表中的一部分數(shù)據(jù)，生成統(tǒng)計信息。
SQL> show parameter optimizer_dynamic_sampling ; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ optimizer_dynamic_sampling integer 2
