【性能优化】之 表分析及动态采样
生活随笔
收集整理的這篇文章主要介紹了
【性能优化】之 表分析及动态采样
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
書面作業，如果有要求，請給出整個過程的SQL輸出信息，不允許單純用語言描述：
?1.演示一個表分析后執行計劃比動態采樣更準確的例子。
?2.演示在缺乏直方圖時，CBO計算出錯誤的執行計劃的例子，并給出最后正確的執行計劃。
?3.演示在分區表上，全局信息和分區信息是如何影響執行計劃的，給出演示過程。
?4.演示用extended statistics 解決列相關性的例子，給出演示過程。
?5.對一張表進行導出/導入，看它的統計信息是否發生改變，給出演示過程。
?#######################################################################################
?1.演示一個表分析后執行計劃比動態采樣更準確的例子。
答：
1.1創建表
SQL> create table t as select * from dba_objects ;
Table created.
SQL> select count(0) from t;
? COUNT(0)
----------
???? 76376
1.2 查看表的統計信息
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
? NUM_ROWS AVG_ROW_LEN???? BLOCKS LAST_ANALYZED
---------- ----------- ---------- ------------------
因為沒有做表信息的統計，這時的表行數、數據塊等信息都為空。
我們使用動態分析查看一下一條SQL 的執行計劃：
SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |? 4550 |?? 919K|?? 305?? (1)| 00:00:04 |
|*? 1 |? TABLE ACCESS FULL| T??? |? 4550 |?? 919K|?? 305?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("OBJECT_ID">30)
再使用查詢的方式，查看一下SQL 查詢的總數據量：
select count(0) from t where object_id>30;
? ??? ?COUNT(0)
?? ?--------------
1?? ?76340
可以看到，CBO 使用動態采樣估算的值，以實際值，差得是那個相當的遠。
下面使用通過DBMS_STATS 來做表的分析。
SQL> exec dbms_stats.gather_table_stats('TANG','T');
這時我們查詢表的統計信息可以看到，數據量，占用的數據塊等信息都有了。
SQL>select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
? ??? ?NUM_ROWS?? ?AVG_ROW_LEN?? ?BLOCKS?? ?LAST_ANALYZED
?? ?-------------------------------------------------
1?? ?76376?? ??? ?98?? ??? ??? ?1114?? ?2013/11/21 15:21:23
這時再使用上面的查詢，查看兩者的執行計劃的差別
這時可以看出，CBO使用表的統計信息給出的執行計劃中，數據行數76347 與實際行數 76340 可以說是約等于了。
SQL> select * from t where object_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76347 |? 7306K|?? 305?? (1)| 00:00:04 |
|*? 1 |? TABLE ACCESS FULL| T??? | 76347 |? 7306K|?? 305?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("OBJECT_ID">30)
下面再建立一個索引：
SQL> create index idx_t_id on t(object_id);
Index created.
查詢索引的統計信息：
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
? ??? ?BLEVEL?? ?LEAF_BLOCKS?? ?DISTINCT_KEYS?? ?LAST_ANALYZED
?? ?--------------------------------------------------
1?? ?1?? ?169?? ?76369?? ?2013/11/21 15:40:39
發現，這時的索引統計信息是有的。并且值還是很接近數據問題。
但是不是也是估算出來的呢，下面再做一次索引統計信息后，再查詢，
看到是一致的。說明在表已做統計分析后，建立的索引，索引會在表分析數據的基礎上，自動生成
索引統計信息
SQL> exec dbms_stats.gather_index_stats('TANG','IDX_T_ID');
PL/SQL procedure successfully completed.
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
? ??? ?BLEVEL?? ?LEAF_BLOCKS?? ?DISTINCT_KEYS?? ?LAST_ANALYZED
?? ?--------------------------------------------------
1?? ?1?? ?169?? ?76369?? ?2013/11/21 15:40:39
========================================================================================
?2.演示在缺乏直方圖時，CBO計算出錯誤的執行計劃的例子，并給出最后正確的執行計劃。
?? ?答：
?? ?直方圖為：ORACLE 對數據列上的數據分布進行統計后得出的圖示。
?? ?常常用來查看數據傾斜（值在整個數據中所占比例），了解某個值所占有的記錄數。
?? ?直方圖類型：
?? ?1.FREQUENCY 頻率直方圖：主要用于基數少，記錄多，重復率高的字段。
?? ?2.HEIGHT BALANCED 高度平衡直方圖：用于基數少，唯一值多，重復率低的字段。
?? ?2.1 建立演示環境：
?? ?SQL> drop table t1 purge;
?? ?Table dropped.
?? ?SQL> create table t1 as select * from dba_objects;
?? ?Table created.
?? ?2.2 查看一下查詢的數據量
?? ?select count(*) from t1 where object_type='TABLE';
?? ?--------
?? ?3207
?? ?2.3 做信息收集但沒有做直方圖分析 后的執行計劃
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 1');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select count(*) from t1 where object_type='TABLE';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 3724264953
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |???? 9 |?? 304?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |???? 9 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T1?? |? 1697 | 15273 |?? 304?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE')
?? ?2.3 做信息收集但即做直方圖分析 后的執行計劃
?? ??? ?SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 254');
?? ??? ?PL/SQL procedure successfully completed.
?? ??? ?SQL> select count(*) from t1 where object_type='TABLE';
?? ??? ?Execution Plan
?? ??? ?----------------------------------------------------------
?? ??? ?Plan hash value: 3724264953
?? ??? ?---------------------------------------------------------------------------
?? ??? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ??? ?---------------------------------------------------------------------------
?? ??? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |???? 9 |?? 304?? (1)| 00:00:04 |
?? ??? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |???? 9 |??????????? |????????? |
?? ??? ?|*? 2 |?? TABLE ACCESS FULL| T1?? |? 2723 | 24507 |?? 304?? (1)| 00:00:04 |
?? ??? ?---------------------------------------------------------------------------
?? ??? ?Predicate Information (identified by operation id):
?? ??? ?---------------------------------------------------
?? ??? ??? 2 - filter("OBJECT_TYPE"='TABLE')
?? ?我們來看一下前后的對比，在沒有做直方圖前，因為CBO并不知道 OBJECT_TYPE='TABLE' 的數據量所占比例，
?? ?估算的值 1697 與真實值? 3207 誤差太大的原因。
?? ?在進行了直方圖分析后，計算出的rows=2723 ，與真實值已非常接近了。
?========================================================================================
?3.演示在分區表上，全局信息和分區信息是如何影響執行計劃的，給出演示過程。
?? ?
?? ?3.1 先查詢一下數據分布情況
?? ?select? floor(object_id/10000),count(0) from t1 ?
?? ?group by? floor(object_id/10000)
?? ?order by floor(object_id/10000)
? ??? ?FLOOR(OBJECT_ID/10000)?? ?COUNT(0)
?? ?---------------------------------
?? ?0?? ?9829
?? ?1?? ?9848
?? ?2?? ?10000
?? ?3?? ?10000
?? ?4?? ?10000
?? ?5?? ?9959
?? ?6?? ?9538
?? ?7?? ?6133
?? ?8?? ?224
?? ?9?? ?838
?? ??? ?7
?? ?3.2建立一個與object_id 為分區字段的分區表：
?? ?drop table t3;
?? ?create table t3
?? ?partition by range (object_id)
?? ?(
?? ?partition p1 values less than(10000),
?? ?partition p2 values less than(20000),
?? ?partition p3 values less than(30000),
?? ?partition p4 values less than(40000),
?? ?partition p5 values less than(50000),
?? ?partition p6 values less than(60000),
?? ?partition pm values less than(maxvalue)
?? ?) as select * from dba_objects;
?? ?3.3對表進行一次統計分析
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T3');
?? ?PL/SQL procedure successfully completed
?? ?3.4 查詢表的統計信息，從統計表中看，數據為76384
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
?? ?TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ---------- ---------- ------------
?? ?T3????????????????????????????????? 76384?????? 1216 YES
?? ?3.5查詢表各分區數據量
?? ?select table_name,partition_name,num_rows,blocks,global_stats
?? ?from dba_tab_partitions where table_name='T3';
?? ??? ?TABLE_NAME?? ?PARTITION_NAME?? ?NUM_ROWS?? ?BLOCKS?? ?GLOBAL_STATS
?? ??? ?----------------------------------------------------------------
?? ?1?? ?T3?? ?P1?? ?9829?? ?145?? ?YES
?? ?2?? ?T3?? ?P2?? ?9848?? ?158?? ?YES
?? ?3?? ?T3?? ?P3?? ?10000?? ?164?? ?YES
?? ?4?? ?T3?? ?P4?? ?10000?? ?165?? ?YES
?? ?5?? ?T3?? ?P5?? ?10000?? ?165?? ?YES
?? ?6?? ?T3?? ?P6?? ?9959?? ?160?? ?YES
?? ?7?? ?T3?? ?PM?? ?16748?? ?259?? ?YES
?? ?查看一下這時的查詢的查詢計劃：
?? ?第一條我只對一個分區的數據進行查詢從上面的統計可以知道，查詢 object_id<=9000的數據只在第一個分區。
?? ?而第二條查詢 object_id<=30000 就已經是跨分區P1-P3
?? ?SQL> select count(*) from T3 where object_id<=9000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 4037133807
?? ?------------------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????? |????? |???? 1 |???? 4 |??? 41?? (0)| 00:00:01 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE???????? |????? |???? 1 |???? 4 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE SINGLE|????? |? 8848 | 35392 |??? 41?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?|*? 3 |??? TABLE ACCESS FULL??? | T3?? |? 8848 | 35392 |??? 41?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=9000)
?? ?SQL>
?? ?SQL> select count(*) from T3 where object_id<=30000;
?? ??? ?Execution Plan
?? ??? ?----------------------------------------------------------
?? ??? ?Plan hash value: 3481849819
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?| Id? | Operation???????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?|?? 0 | SELECT STATEMENT????????? |????? |???? 1 |???? 5 |?? 174?? (1)| 00:00:03 |?????? |?????? |
?? ??? ?|?? 1 |? SORT AGGREGATE?????????? |????? |???? 1 |???? 5 |??????????? |????????? |?????? |?????? |
?? ??? ?|?? 2 |?? PARTITION RANGE ITERATOR|????? | 24075 |?? 117K|?? 174?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ??? ?|*? 3 |??? TABLE ACCESS FULL????? | T3?? | 24075 |?? 117K|?? 174?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?Predicate Information (identified by operation id):
?? ??? ?---------------------------------------------------
?? ??? ??? 3 - filter("OBJECT_ID"<=30000)
?? ?3.6 我再來一次刪除數據后，再只對分區進行統計分析
?? ?SQL> delete from t3;
?? ?76384 rows deleted
?? ?SQL> commit;
?? ?Commit complete
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T3',granularity=>'partition');
?? ?PL/SQL procedure successfully completed
?? ?SQL>
?? ?3.7 查看全局統計信息及分區統計信息，可以看到全局統計信息中的數據沒有改變，還是 NUM_ROWS=76384
?? ?而分區統計信息中已變化了。數據為 0
?? ?
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
?? ?TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ---------- ---------- ------------
?? ?T3????????????????????????????????? 76384?????? 1346 YES
?? ?SQL> select table_name,partition_name,num_rows,blocks,global_stats? from dba_tab_partitions where table_name='T3';
?? ?TABLE_NAME???????????????????? PARTITION_NAME?????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ------------------------------ ---------- ---------- ------------
?? ?T3???????????????????????????? P1????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P2????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P3????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P4????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P5????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P6????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? PM????????????????????????????????????? 0??????? 302 YES
?? ?7 rows selected
?? ?SQL>
?? ?
?? ?3.8 這時我們再回來查看兩個查詢的執行計劃：
?? ??? ?3.8.1 查詢? object_id<=30000 時使用的是全局統計信息，這時查詢的數據還是 24045
?? ??? ?顯然不正確。
?? ??? ?3.8.2 查詢 object_id<=9000 時使用的是分區統計信息，這時查詢數據返回 Rows=1。
?? ?SQL> select count(*) from T3 where object_id<=30000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 3481849819
?? ?--------------------------------------------------------------------------------------------------
?? ?| Id? | Operation???????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?--------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT????????? |????? |???? 1 |???? 5 |?? 191?? (1)| 00:00:03 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE?????????? |????? |???? 1 |???? 5 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE ITERATOR|????? | 24045 |?? 117K|?? 191?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ?|*? 3 |??? TABLE ACCESS FULL????? | T3?? | 24045 |?? 117K|?? 191?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ?--------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=30000)
?? ?SQL> select count(*) from T3 where object_id<=9000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 4037133807
?? ?------------------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????? |????? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE???????? |????? |???? 1 |??? 13 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE SINGLE|????? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?|*? 3 |??? TABLE ACCESS FULL??? | T3?? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=9000)
?? ?SQL>
?? ?
?========================================================================================
?4.演示用extended statistics 解決列相關性的例子，給出演示過程。
?? ?4.1 建立測試數據
?? ?SQL> CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;
?? ?Table created.
?? ?4.2 對表進行統計分析
?? ?SQL> exec dbms_stats.gather_table_stats('TANG','T4');
?? ?PL/SQL procedure successfully completed.
?? ?
?? ?4.2查看查詢結果，以方便后面的對比
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ??? ?COUNT(*)
?? ??? ?---------------
?? ?1?? ?1066
?? ?4.3 查看查詢計劃，看到統計的行數為 1431
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 405148644
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 15 |?? 305?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 15 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T4?? |? 1431 | 21465 |?? 305?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')
?? ?4.4 再對表進行一次列相關性分析
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T4',method_opt=>'for columns (object_type,owner) size skewonly');
?? ?PL/SQL procedure successfully completed.
?? ?4.5 再查看執行計劃，看到統計行數為 1203 ，與真實值 1066 比上一次更為接近。
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 405148644
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 15 |?? 305?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 15 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T4?? |? 1203 | 18045 |?? 305?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')
?? ?SQL>
?========================================================================================
?5.對一張表進行導出/導入，看它的統計信息是否發生改變，給出演示過程。
?5.1 建立測試表：
?? ?SQL>
?? ?SQL>
?? ?SQL> drop table t5
?? ?SQL> create table t5 as select * from dba_objects;
?? ?Table created.
?? ?SQL> set autotrace off? ;
?? ?5.2 查詢數據總量
?? ?SQL> select count(0) from t5;
?? ?? COUNT(0)
?? ?----------
?? ??? ? 76385
?? ?C:\Users\Administrator>
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T5');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------?? ---------
?? ?T5?? ??? ??? ??? ?76385?????? 1114 YES
?? ?SQL>
?? ?select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('LEO7');
?? ?查詢統計分析數據
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?5.2 導出數據
?? ?C:\Users\Administrator>exp userid=tang rows=Y tables=(t5) file='exp_t5' log='exp
?? ?_t5.log'
?? ?Export: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:28:48 2013
?? ?Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
?? ?口令:
?? ?連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
?? ?tion
?? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ?已導出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
?? ?即將導出指定的表通過常規路徑...
?? ?. . 正在導出表????????????????????????????? T5導出了?????? 76385 行
?? ?成功終止導出, 沒有出現警告。
?? ?再次查詢統計分析數據，看到數據沒有變化
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?SQL>
?? ?5.3 導入數據
?? ??? ?C:\Users\Administrator>imp tang/sa ignore=Y rows=Y fromuser=tang touser=tang fil
?? ??? ?e='exp_t5.dmp' log='imp_t5.log'
?? ??? ?Import: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:32:54 2013
?? ??? ?Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
?? ??? ?連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
?? ??? ?tion
?? ??? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ??? ?經由常規路徑由 EXPORT:V11.02.00 創建的導出文件
?? ??? ?已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的導入
?? ??? ?. 正在將 TANG 的對象導入到 TANG
?? ??? ?. . 正在導入表??????????????????????????? "T5"導入了?????? 76385 行
?? ??? ?成功終止導入, 沒有出現警告。
?? ??? ?C:\Users\Administrator>
?? ?5.4 查詢一下數據總量，再查詢統計分析數據，
?? ?SQL> select count(0) from t5;
?? ?? COUNT(0)
?? ?----------
?? ??? ?152770
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?SQL>
?? ??? ?可以看到，數據總量已發生變化，但統計分析數據沒有改變。說明導入，導出數據后，
?? ??? ?表并不會自動進行統計分析，收集表的信息。如果要更加準確的表統計分析數據，還要手工進行。
?? ?（后面是進行統計后的數據）
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T5');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ?152770?????? 2286 YES
?1.演示一個表分析后執行計劃比動態采樣更準確的例子。
?2.演示在缺乏直方圖時，CBO計算出錯誤的執行計劃的例子，并給出最后正確的執行計劃。
?3.演示在分區表上，全局信息和分區信息是如何影響執行計劃的，給出演示過程。
?4.演示用extended statistics 解決列相關性的例子，給出演示過程。
?5.對一張表進行導出/導入，看它的統計信息是否發生改變，給出演示過程。
?#######################################################################################
?1.演示一個表分析后執行計劃比動態采樣更準確的例子。
答：
1.1創建表
SQL> create table t as select * from dba_objects ;
Table created.
SQL> select count(0) from t;
? COUNT(0)
----------
???? 76376
1.2 查看表的統計信息
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
? NUM_ROWS AVG_ROW_LEN???? BLOCKS LAST_ANALYZED
---------- ----------- ---------- ------------------
因為沒有做表信息的統計，這時的表行數、數據塊等信息都為空。
我們使用動態分析查看一下一條SQL 的執行計劃：
SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |? 4550 |?? 919K|?? 305?? (1)| 00:00:04 |
|*? 1 |? TABLE ACCESS FULL| T??? |? 4550 |?? 919K|?? 305?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("OBJECT_ID">30)
再使用查詢的方式，查看一下SQL 查詢的總數據量：
select count(0) from t where object_id>30;
? ??? ?COUNT(0)
?? ?--------------
1?? ?76340
可以看到，CBO 使用動態采樣估算的值，以實際值，差得是那個相當的遠。
下面使用通過DBMS_STATS 來做表的分析。
SQL> exec dbms_stats.gather_table_stats('TANG','T');
這時我們查詢表的統計信息可以看到，數據量，占用的數據塊等信息都有了。
SQL>select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';
? ??? ?NUM_ROWS?? ?AVG_ROW_LEN?? ?BLOCKS?? ?LAST_ANALYZED
?? ?-------------------------------------------------
1?? ?76376?? ??? ?98?? ??? ??? ?1114?? ?2013/11/21 15:21:23
這時再使用上面的查詢，查看兩者的執行計劃的差別
這時可以看出，CBO使用表的統計信息給出的執行計劃中，數據行數76347 與實際行數 76340 可以說是約等于了。
SQL> select * from t where object_id>30;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76347 |? 7306K|?? 305?? (1)| 00:00:04 |
|*? 1 |? TABLE ACCESS FULL| T??? | 76347 |? 7306K|?? 305?? (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("OBJECT_ID">30)
下面再建立一個索引：
SQL> create index idx_t_id on t(object_id);
Index created.
查詢索引的統計信息：
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
? ??? ?BLEVEL?? ?LEAF_BLOCKS?? ?DISTINCT_KEYS?? ?LAST_ANALYZED
?? ?--------------------------------------------------
1?? ?1?? ?169?? ?76369?? ?2013/11/21 15:40:39
發現，這時的索引統計信息是有的。并且值還是很接近數據問題。
但是不是也是估算出來的呢，下面再做一次索引統計信息后，再查詢，
看到是一致的。說明在表已做統計分析后，建立的索引，索引會在表分析數據的基礎上，自動生成
索引統計信息
SQL> exec dbms_stats.gather_index_stats('TANG','IDX_T_ID');
PL/SQL procedure successfully completed.
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';
? ??? ?BLEVEL?? ?LEAF_BLOCKS?? ?DISTINCT_KEYS?? ?LAST_ANALYZED
?? ?--------------------------------------------------
1?? ?1?? ?169?? ?76369?? ?2013/11/21 15:40:39
========================================================================================
?2.演示在缺乏直方圖時，CBO計算出錯誤的執行計劃的例子，并給出最后正確的執行計劃。
?? ?答：
?? ?直方圖為：ORACLE 對數據列上的數據分布進行統計后得出的圖示。
?? ?常常用來查看數據傾斜（值在整個數據中所占比例），了解某個值所占有的記錄數。
?? ?直方圖類型：
?? ?1.FREQUENCY 頻率直方圖：主要用于基數少，記錄多，重復率高的字段。
?? ?2.HEIGHT BALANCED 高度平衡直方圖：用于基數少，唯一值多，重復率低的字段。
?? ?2.1 建立演示環境：
?? ?SQL> drop table t1 purge;
?? ?Table dropped.
?? ?SQL> create table t1 as select * from dba_objects;
?? ?Table created.
?? ?2.2 查看一下查詢的數據量
?? ?select count(*) from t1 where object_type='TABLE';
?? ?--------
?? ?3207
?? ?2.3 做信息收集但沒有做直方圖分析 后的執行計劃
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 1');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select count(*) from t1 where object_type='TABLE';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 3724264953
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |???? 9 |?? 304?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |???? 9 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T1?? |? 1697 | 15273 |?? 304?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE')
?? ?2.3 做信息收集但即做直方圖分析 后的執行計劃
?? ??? ?SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 254');
?? ??? ?PL/SQL procedure successfully completed.
?? ??? ?SQL> select count(*) from t1 where object_type='TABLE';
?? ??? ?Execution Plan
?? ??? ?----------------------------------------------------------
?? ??? ?Plan hash value: 3724264953
?? ??? ?---------------------------------------------------------------------------
?? ??? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ??? ?---------------------------------------------------------------------------
?? ??? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |???? 9 |?? 304?? (1)| 00:00:04 |
?? ??? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |???? 9 |??????????? |????????? |
?? ??? ?|*? 2 |?? TABLE ACCESS FULL| T1?? |? 2723 | 24507 |?? 304?? (1)| 00:00:04 |
?? ??? ?---------------------------------------------------------------------------
?? ??? ?Predicate Information (identified by operation id):
?? ??? ?---------------------------------------------------
?? ??? ??? 2 - filter("OBJECT_TYPE"='TABLE')
?? ?我們來看一下前后的對比，在沒有做直方圖前，因為CBO并不知道 OBJECT_TYPE='TABLE' 的數據量所占比例，
?? ?估算的值 1697 與真實值? 3207 誤差太大的原因。
?? ?在進行了直方圖分析后，計算出的rows=2723 ，與真實值已非常接近了。
?========================================================================================
?3.演示在分區表上，全局信息和分區信息是如何影響執行計劃的，給出演示過程。
?? ?
?? ?3.1 先查詢一下數據分布情況
?? ?select? floor(object_id/10000),count(0) from t1 ?
?? ?group by? floor(object_id/10000)
?? ?order by floor(object_id/10000)
? ??? ?FLOOR(OBJECT_ID/10000)?? ?COUNT(0)
?? ?---------------------------------
?? ?0?? ?9829
?? ?1?? ?9848
?? ?2?? ?10000
?? ?3?? ?10000
?? ?4?? ?10000
?? ?5?? ?9959
?? ?6?? ?9538
?? ?7?? ?6133
?? ?8?? ?224
?? ?9?? ?838
?? ??? ?7
?? ?3.2建立一個與object_id 為分區字段的分區表：
?? ?drop table t3;
?? ?create table t3
?? ?partition by range (object_id)
?? ?(
?? ?partition p1 values less than(10000),
?? ?partition p2 values less than(20000),
?? ?partition p3 values less than(30000),
?? ?partition p4 values less than(40000),
?? ?partition p5 values less than(50000),
?? ?partition p6 values less than(60000),
?? ?partition pm values less than(maxvalue)
?? ?) as select * from dba_objects;
?? ?3.3對表進行一次統計分析
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T3');
?? ?PL/SQL procedure successfully completed
?? ?3.4 查詢表的統計信息，從統計表中看，數據為76384
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
?? ?TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ---------- ---------- ------------
?? ?T3????????????????????????????????? 76384?????? 1216 YES
?? ?3.5查詢表各分區數據量
?? ?select table_name,partition_name,num_rows,blocks,global_stats
?? ?from dba_tab_partitions where table_name='T3';
?? ??? ?TABLE_NAME?? ?PARTITION_NAME?? ?NUM_ROWS?? ?BLOCKS?? ?GLOBAL_STATS
?? ??? ?----------------------------------------------------------------
?? ?1?? ?T3?? ?P1?? ?9829?? ?145?? ?YES
?? ?2?? ?T3?? ?P2?? ?9848?? ?158?? ?YES
?? ?3?? ?T3?? ?P3?? ?10000?? ?164?? ?YES
?? ?4?? ?T3?? ?P4?? ?10000?? ?165?? ?YES
?? ?5?? ?T3?? ?P5?? ?10000?? ?165?? ?YES
?? ?6?? ?T3?? ?P6?? ?9959?? ?160?? ?YES
?? ?7?? ?T3?? ?PM?? ?16748?? ?259?? ?YES
?? ?查看一下這時的查詢的查詢計劃：
?? ?第一條我只對一個分區的數據進行查詢從上面的統計可以知道，查詢 object_id<=9000的數據只在第一個分區。
?? ?而第二條查詢 object_id<=30000 就已經是跨分區P1-P3
?? ?SQL> select count(*) from T3 where object_id<=9000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 4037133807
?? ?------------------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????? |????? |???? 1 |???? 4 |??? 41?? (0)| 00:00:01 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE???????? |????? |???? 1 |???? 4 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE SINGLE|????? |? 8848 | 35392 |??? 41?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?|*? 3 |??? TABLE ACCESS FULL??? | T3?? |? 8848 | 35392 |??? 41?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=9000)
?? ?SQL>
?? ?SQL> select count(*) from T3 where object_id<=30000;
?? ??? ?Execution Plan
?? ??? ?----------------------------------------------------------
?? ??? ?Plan hash value: 3481849819
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?| Id? | Operation???????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?|?? 0 | SELECT STATEMENT????????? |????? |???? 1 |???? 5 |?? 174?? (1)| 00:00:03 |?????? |?????? |
?? ??? ?|?? 1 |? SORT AGGREGATE?????????? |????? |???? 1 |???? 5 |??????????? |????????? |?????? |?????? |
?? ??? ?|?? 2 |?? PARTITION RANGE ITERATOR|????? | 24075 |?? 117K|?? 174?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ??? ?|*? 3 |??? TABLE ACCESS FULL????? | T3?? | 24075 |?? 117K|?? 174?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ??? ?--------------------------------------------------------------------------------------------------
?? ??? ?Predicate Information (identified by operation id):
?? ??? ?---------------------------------------------------
?? ??? ??? 3 - filter("OBJECT_ID"<=30000)
?? ?3.6 我再來一次刪除數據后，再只對分區進行統計分析
?? ?SQL> delete from t3;
?? ?76384 rows deleted
?? ?SQL> commit;
?? ?Commit complete
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T3',granularity=>'partition');
?? ?PL/SQL procedure successfully completed
?? ?SQL>
?? ?3.7 查看全局統計信息及分區統計信息，可以看到全局統計信息中的數據沒有改變，還是 NUM_ROWS=76384
?? ?而分區統計信息中已變化了。數據為 0
?? ?
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
?? ?TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ---------- ---------- ------------
?? ?T3????????????????????????????????? 76384?????? 1346 YES
?? ?SQL> select table_name,partition_name,num_rows,blocks,global_stats? from dba_tab_partitions where table_name='T3';
?? ?TABLE_NAME???????????????????? PARTITION_NAME?????????????????? NUM_ROWS???? BLOCKS GLOBAL_STATS
?? ?------------------------------ ------------------------------ ---------- ---------- ------------
?? ?T3???????????????????????????? P1????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P2????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P3????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P4????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P5????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? P6????????????????????????????????????? 0??????? 174 YES
?? ?T3???????????????????????????? PM????????????????????????????????????? 0??????? 302 YES
?? ?7 rows selected
?? ?SQL>
?? ?
?? ?3.8 這時我們再回來查看兩個查詢的執行計劃：
?? ??? ?3.8.1 查詢? object_id<=30000 時使用的是全局統計信息，這時查詢的數據還是 24045
?? ??? ?顯然不正確。
?? ??? ?3.8.2 查詢 object_id<=9000 時使用的是分區統計信息，這時查詢數據返回 Rows=1。
?? ?SQL> select count(*) from T3 where object_id<=30000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 3481849819
?? ?--------------------------------------------------------------------------------------------------
?? ?| Id? | Operation???????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?--------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT????????? |????? |???? 1 |???? 5 |?? 191?? (1)| 00:00:03 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE?????????? |????? |???? 1 |???? 5 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE ITERATOR|????? | 24045 |?? 117K|?? 191?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ?|*? 3 |??? TABLE ACCESS FULL????? | T3?? | 24045 |?? 117K|?? 191?? (1)| 00:00:03 |???? 1 |???? 4 |
?? ?--------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=30000)
?? ?SQL> select count(*) from T3 where object_id<=9000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 4037133807
?? ?------------------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? | Pstart| Pstop |
?? ?------------------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????? |????? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |?????? |?????? |
?? ?|?? 1 |? SORT AGGREGATE???????? |????? |???? 1 |??? 13 |??????????? |????????? |?????? |?????? |
?? ?|?? 2 |?? PARTITION RANGE SINGLE|????? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?|*? 3 |??? TABLE ACCESS FULL??? | T3?? |???? 1 |??? 13 |??? 49?? (0)| 00:00:01 |???? 1 |???? 1 |
?? ?------------------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 3 - filter("OBJECT_ID"<=9000)
?? ?SQL>
?? ?
?========================================================================================
?4.演示用extended statistics 解決列相關性的例子，給出演示過程。
?? ?4.1 建立測試數據
?? ?SQL> CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;
?? ?Table created.
?? ?4.2 對表進行統計分析
?? ?SQL> exec dbms_stats.gather_table_stats('TANG','T4');
?? ?PL/SQL procedure successfully completed.
?? ?
?? ?4.2查看查詢結果，以方便后面的對比
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ??? ?COUNT(*)
?? ??? ?---------------
?? ?1?? ?1066
?? ?4.3 查看查詢計劃，看到統計的行數為 1431
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 405148644
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 15 |?? 305?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 15 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T4?? |? 1431 | 21465 |?? 305?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')
?? ?4.4 再對表進行一次列相關性分析
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T4',method_opt=>'for columns (object_type,owner) size skewonly');
?? ?PL/SQL procedure successfully completed.
?? ?4.5 再查看執行計劃，看到統計行數為 1203 ，與真實值 1066 比上一次更為接近。
?? ?SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 405148644
?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 15 |?? 305?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 15 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T4?? |? 1203 | 18045 |?? 305?? (1)| 00:00:04 |
?? ?---------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')
?? ?SQL>
?========================================================================================
?5.對一張表進行導出/導入，看它的統計信息是否發生改變，給出演示過程。
?5.1 建立測試表：
?? ?SQL>
?? ?SQL>
?? ?SQL> drop table t5
?? ?SQL> create table t5 as select * from dba_objects;
?? ?Table created.
?? ?SQL> set autotrace off? ;
?? ?5.2 查詢數據總量
?? ?SQL> select count(0) from t5;
?? ?? COUNT(0)
?? ?----------
?? ??? ? 76385
?? ?C:\Users\Administrator>
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T5');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------?? ---------
?? ?T5?? ??? ??? ??? ?76385?????? 1114 YES
?? ?SQL>
?? ?select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('LEO7');
?? ?查詢統計分析數據
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?5.2 導出數據
?? ?C:\Users\Administrator>exp userid=tang rows=Y tables=(t5) file='exp_t5' log='exp
?? ?_t5.log'
?? ?Export: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:28:48 2013
?? ?Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
?? ?口令:
?? ?連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
?? ?tion
?? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ?已導出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
?? ?即將導出指定的表通過常規路徑...
?? ?. . 正在導出表????????????????????????????? T5導出了?????? 76385 行
?? ?成功終止導出, 沒有出現警告。
?? ?再次查詢統計分析數據，看到數據沒有變化
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?SQL>
?? ?5.3 導入數據
?? ??? ?C:\Users\Administrator>imp tang/sa ignore=Y rows=Y fromuser=tang touser=tang fil
?? ??? ?e='exp_t5.dmp' log='imp_t5.log'
?? ??? ?Import: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:32:54 2013
?? ??? ?Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.
?? ??? ?連接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
?? ??? ?tion
?? ??? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ??? ?經由常規路徑由 EXPORT:V11.02.00 創建的導出文件
?? ??? ?已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的導入
?? ??? ?. 正在將 TANG 的對象導入到 TANG
?? ??? ?. . 正在導入表??????????????????????????? "T5"導入了?????? 76385 行
?? ??? ?成功終止導入, 沒有出現警告。
?? ??? ?C:\Users\Administrator>
?? ?5.4 查詢一下數據總量，再查詢統計分析數據，
?? ?SQL> select count(0) from t5;
?? ?? COUNT(0)
?? ?----------
?? ??? ?152770
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ? 76385?????? 1114 YES
?? ?SQL>
?? ??? ?可以看到，數據總量已發生變化，但統計分析數據沒有改變。說明導入，導出數據后，
?? ??? ?表并不會自動進行統計分析，收集表的信息。如果要更加準確的表統計分析數據，還要手工進行。
?? ?（后面是進行統計后的數據）
?? ?SQL> execute dbms_stats.gather_table_stats('TANG','T5');
?? ?PL/SQL procedure successfully completed.
?? ?SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');
?? ?TABLE_NAME
?? ?--------------------------------------------------------------------------------
?? ?? NUM_ROWS???? BLOCKS GLOBAL_ST
?? ?---------- ---------- ---------
?? ?T5
?? ??? ?152770?????? 2286 YES
總結
以上是生活随笔為你收集整理的【性能优化】之 表分析及动态采样的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： oracle trace文件解读
- 下一篇： 【性能优化】 之 并行执行