溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
emp表有如下數據。
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在執行查詢的時候，是先比較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其實知道，你的表中存放數據的一些特征，上面語句顯示的只是鳳毛麟角。通過這些特征，Oracle優化器就能知道如何去查詢，使得執行的效率最高。
以上這些信息，我們稱之為對象的統計信息。那么如何收集統計信息呢？
一、 analyze 命令
使用analyze命令可以收集統計信息，如：
收集或刪除對象的統計信息
驗證對象的結構
確定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);
我們先查看一下統計信息是否存在
查看表的統計信息
SQL> select table_name, num_rows, blocks, empty_blocks
from user_tables
where table_name in ('T1', 'T2', 'T3', 'T4', 'T5','T6');查看字段統計信息
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');查看索引統計信息
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.
表沒有任何統計數據，但是索引已經有統計信息，可見在建立表的時候會默認收集統計信息。
先將索引的統計信息刪除
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;
驗證索引上是否還存在統計信息
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
執行統計信息命令，并查看統計信息有無變化
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這個包來收集統計信息。這個包的功能非常多。可以收集數據庫級別、schema級別及表級別的統計信息。還可以對統計信息刪除、鎖定、導出、導入等。我們以最常用的表級別統計為例說明DBMS_STATS該如何使用。
收集的統計信存儲在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);參數說明如下：
示例：
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.
刪除統計信息
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);鎖定統計信息
DBMS_STATS.LOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
鎖定以后就不能再執行統計信息
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
導出、導入統計信息
要導出統計信息首先要建立一個統計表
語法：
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統計信息導出
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. 導入統計信息
語法：
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.
如果是分區表，新的分區來不及收集統計系統，可以使用其它的分區統計信息來生成新分區的統計信息
DBMS_STATS.COPY_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, srcpartname VARCHAR2, dstpartname VARCHAR2, scale_factor VARCHAR2 DEFAULT 1, force BOOLEAN DEFAULT FALSE);
如果表還沒有統計信息，那么在執行sql語句時，Oracle會動態的采樣表中的一部分數據，生成統計信息。
SQL> show parameter optimizer_dynamic_sampling ; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ optimizer_dynamic_sampling integer 2
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
