oracle長條圖，長條圖

oracle長條圖，長條圖

長條圖 當某列資料分布不均衡，為了讓CBO能產生最佳的執行計畫，我們可能需要對錶收集長條圖，長條圖最大的桶數(Bucket)是254。收集長條圖是一個很耗時的過程，如無必要，千萬別去收集長條圖。Oracle的長條圖有兩種：一種是頻率長條圖(FREQUENCY HISTOGRAM)，當列中Distinct_keys 較少(小於254)，如果不手工指定長條圖桶數(BUCKET)，Oracle就會自動的建立頻率長條圖，並且桶數(BUCKET)等於Distinct_Keys。一種是高度平衡長條圖(HEIGHT BALANCED)，當列中Distinct_keys大於254，如果不手工指定長條圖桶數(BUCKET)，Oracle就會自動的建立高度平衡長條圖。長條圖用在什麼情況下？  列的值分布非常不均衡的時候，並且where條件中經常用到這個列。長條圖都准嗎？  不一定。如果一個欄位distinct值的個數非常多，基本接近主鍵的distinct值的個數，就沒必要做長條圖，長條圖也不一定100%準確。  相關的@指令碼在文章的最後面提供。SQL> drop table a;表已刪除。SQL> create table a as select * from dba_objects where rownum<=10000;表已建立。SQL> @anatab                        --常規的表分析輸入 ownname 的值:  ggs輸入 tabname 的值:  a輸入 estimate_percent 的值:  100輸入 skewonly_repeat_auto 的值:  auto輸入 degree 的值:  4PL/SQL 過程已成功完成。經過時間:  00: 00: 00.26SQL> @getcolstat                    --欄位的長條圖輸入 owner 的值:  ggs輸入 table_name 的值:  aCOLUMN_NAME        NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS LAST_ANALYZED   ---------------- ---------- ----------- ----------- --------------------- --------------  SECONDARY             10000           1         .01 NONE                1 28-7月 -14      GENERATED             10000           2         .02 NONE                1 28-7月 -14      TEMPORARY             10000           2         .02 NONE                1 28-7月 -14      STATUS                10000           1         .01 NONE                1 28-7月 -14      TIMESTAMP             10000         350         3.5 NONE                1 28-7月 -14      LAST_DDL_TIME         10000         385        3.85 NONE                1 28-7月 -14      CREATED               10000         303        3.03 NONE                1 28-7月 -14      OBJECT_TYPE           10000          34         .34 NONE                1 28-7月 -14      DATA_OBJECT_ID        10000        1836       18.36 NONE                1 28-7月 -14      OBJECT_ID             10000       10000         100 NONE                1 28-7月 -14      SUBOBJECT_NAME        10000          27         .27 NONE                1 28-7月 -14      OBJECT_NAME           10000        7725       77.25 NONE                1 28-7月 -14      OWNER                 10000           9         .09 NONE                1 28-7月 -14      已選擇13行。SQL>SQL> select object_type,count(*) from a group by object_type;OBJECT_TYPE           COUNT(*)------------------- ----------INDEX                      946JOB CLASS                    2CONTEXT                      2TYPE BODY                   82PROCEDURE                   50RESOURCE PLAN                3RULE                         1SCHEDULE                     1TABLE PARTITION             52WINDOW                       2WINDOW GROUP                 1TABLE                      841TYPE                      1088VIEW                      2953LIBRARY                    113FUNCTION                    68TRIGGER                      5PROGRAM                      3CLUSTER                     10SYNONYM                   2458PACKAGE BODY               470QUEUE                       21CONSUMER GROUP               5EVALUATION CONTEXT           8RULE SET                    11DIRECTORY                    2UNDEFINED                    6OPERATOR                    15SEQUENCE                   102LOB                        128PACKAGE                    485JOB                          6INDEX PARTITION             59LOB PARTITION                1已選擇34行。SQL> explain plan for select count(*) from a where object_type='INDEX';已解釋。SQL> @getplan'general,outline,starts'Enter value for plan type:generalPLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2223038180---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |     7 |    25   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          ||*  2 |   TABLE ACCESS FULL| A    |   294 |  2058 |    25   (0)| 00:00:01 | --跟實際不一致，上面查出來的是946---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_TYPE"='INDEX')SQL> select 10000/34 from dual;    --說明rows中的294是 估算值=總行數/欄位distinct值的個數  10000/34----------294.117647已選擇 1 行。SQL>SQL> @anatab_col輸入 owner 的值:  ggs輸入 table_name 的值:  a輸入 columns 的值:  object_type     --做object_type欄位的長條圖PL/SQL 過程已成功完成。SQL> explain plan for select count(*) from a where object_type='INDEX';已解釋。SQL> @getplan'general,outline,starts'Enter value for plan type:generalPLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2223038180---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |     7 |    25   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          ||*  2 |   TABLE ACCESS FULL| A    |   946 |  6622 |    25   (0)| 00:00:01 |   --這裡返回的是真實的行數,做object_type欄位的長條圖後，執行計畫非常准。---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_TYPE"='INDEX')SQL>SQL>SQL>SQL>SQL> @getcolstat輸入 owner 的值:  ggs輸入 table_name 的值:  aCOLUMN_NAME         NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED---------------- ---------- ----------- ----------- --------- ----------- --------------      SECONDARY             10000           1         .01 NONE                1 28-7月 -14          GENERATED             10000           2         .02 NONE                1 28-7月 -14          TEMPORARY             10000           2         .02 NONE                1 28-7月 -14          STATUS                10000           1         .01 NONE                1 28-7月 -14          TIMESTAMP             10000         350         3.5 NONE                1 28-7月 -14          LAST_DDL_TIME         10000         385        3.85 NONE                1 28-7月 -14          CREATED               10000         303        3.03 NONE                1 28-7月 -14          OBJECT_TYPE           10000          34         .34 FREQUENCY          34 28-7月 -14   --剛好等於distinct值DATA_OBJECT_ID        10000        1836       18.36 NONE                1 28-7月 -14          OBJECT_ID             10000       10000         100 NONE                1 28-7月 -14          SUBOBJECT_NAME        10000          27         .27 NONE                1 28-7月 -14          OBJECT_NAME           10000        7725       77.25 NONE                1 28-7月 -14          OWNER                 10000           9         .09 NONE                1 28-7月 -14          已選擇13行。SQL> select count(distinct object_name) from a;    --總共才10000行，可以看出object_name的選擇性是比較高的COUNT(DISTINCTOBJECT_NAME)--------------------------                      7725已選擇 1 行。SQL> @anatab_col輸入 owner 的值:  ggs輸入 table_name 的值:  a輸入 columns 的值:   object_namePL/SQL 過程已成功完成。SQL> @getcolstat輸入 owner 的值:  ggs輸入 table_name 的值:  aCOLUMN_NAME         NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED  ---------------- ---------- ----------- ----------- --------------- ----------- --------------  SECONDARY             10000           1         .01 NONE                      1 28-7月 -14      GENERATED             10000           2         .02 NONE                      1 28-7月 -14      TEMPORARY             10000           2         .02 NONE                      1 28-7月 -14      STATUS                10000           1         .01 NONE                      1 28-7月 -14      TIMESTAMP             10000         350         3.5 NONE                      1 28-7月 -14      LAST_DDL_TIME         10000         385        3.85 NONE                      1 28-7月 -14      CREATED               10000         303        3.03 NONE                      1 28-7月 -14      OBJECT_TYPE           10000          34         .34 FREQUENCY                34 28-7月 -14      DATA_OBJECT_ID        10000        1836       18.36 NONE                      1 28-7月 -14      OBJECT_ID             10000       10000         100 NONE                      1 28-7月 -14      SUBOBJECT_NAME        10000          27         .27 NONE                      1 28-7月 -14      OBJECT_NAME           10000        7725       77.25 HEIGHT BALANCED          75 28-7月 -14      OWNER                 10000           9         .09 NONE                      1 28-7月 -14      已選擇13行。SQL> select count(*) from a where object_name like '%A%';  COUNT(*)----------      6404已選擇 1 行。SQL> explain plan for select count(*) from a where object_name like '%A%';已解釋。SQL> @getplan'general,outline,starts'Enter value for plan type:generalPLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------Plan hash value: 2223038180---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |    19 |    25   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          ||*  2 |   TABLE ACCESS FULL| A    |   500 |  9500 |    25   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_NAME" LIKE '%A%')     --LIKE '%A%'對於cbo而言太複雜了，沒有真正跑的話，cbo根本不知道真正返回多少行。SQL> 已選擇13行。SQL> col OBJECT_NAME for a30SQL> select OBJECT_NAME,count(*) from a group by OBJECT_NAME having count(*)>3 order by count(*) desc;OBJECT_NAME                      COUNT(*)------------------------------ ----------DBMS_REPCAT_AUTH                        5已選擇 1 行。SQL> explain plan for select count(*) from a where OBJECT_NAME='DBMS_REPCAT_AUTH';已解釋。SQL> @getplan'general,outline,starts'Enter value for plan type:generalPLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------Plan hash value: 2223038180---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |    19 |    25   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          ||*  2 |   TABLE ACCESS FULL| A    |     1 |    19 |    25   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_NAME"='DBMS_REPCAT_AUTH')   --這個不複雜了吧，rows=1，一樣不準，長條圖也不可能保證100%準確的所以說，並不是所有欄位都適合做長條圖。distinct值非常多的，根本不適合做長條圖，預設的桶數也裝不下。只有欄位值傾斜非常嚴重，distinct值少，而且用到的sql中where條件包含了這個欄位。如果sql中都沒有用到這個欄位，那也沒必要做長條圖，因為做長條圖是非常cpu效能的。@指令碼--anatab.sqlset timing onBEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => '&ownname',                                tabname          => '&tabname' ,                                estimate_percent => &estimate_percent,                                method_opt       => 'for all columns size &skewonly_repeat_auto',                                no_invalidate    => FALSE,                                degree           => °ree,                                cascade          => TRUE);END;/set timing off--anatab_col.sqlBEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => '&owner',                                tabname          => '&table_name',                                estimate_percent => 100,                                method_opt       => 'for columns &columns ',  --such as:col1,col2,col3...                                no_invalidate    => FALSE,                                degree           => 4,                                granularity      => 'ALL',                                cascade          => TRUE);END;/--getcolstat.sqlcol COLUMN_NAME for a30select a.column_name,       b.num_rows,       a.num_distinct Cardinality,       round(a.num_distinct / b.num_rows * 100, 2) selectivity,       a.histogram,       a.num_buckets,       a.last_analyzed  from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner   and a.table_name = b.table_name   and a.owner = upper('&owner')   and a.table_name = upper('&table_name');  --getplan.sqlset feedback offpro 'general,outline,starts'proacc type prompt 'Enter value for plan type:' default 'general'select * from table(dbms_xplan.display) where '&&type'='general';select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';set feedback onundef type

oracle select語句最佳化問題？5分

1)
WHERE zone_id=:zoneId AND
stat_time>=TO_DATE(:start,'YYYY-MM-DD') AND
stat_time<TO_DATE(:endDate,'YYYY-MM-DD')+1

2) 建立適當索引

如果單SQL,，沒有其他的可以最佳化的了，TO_CHAR(stat_time,'YYYY-MM-DD'),這個調用了3次，可以考慮用預存程序？
 
oracle 11g 與 12c 有什不同?

在OOW 2012上Tom kyte介紹了Oracle新一代重量級資料庫產品12c 的12個新特性， 目前Open World 2012的主要PDF都可以下載了，傳送門在此：Search Content Catalog for Oracle OpenWorld 2012 sessions。
Tom的《12 Things About The Latest Generation of Database Technology》。

這裡我們來領略下Tom眼中的12個特性增強：

#1 Even better PL/SQL from SQL， 直接在SQL中嵌入PL/SQL對象並運行，猜測可能最佳化了SQL engine 和 PL/SQL engine 2種的代碼引擎之間的互動，以獲得比之前傳統的SQL調用函數更少的環境切換。

#2 Improved Defaults 增強了DEFAULT, default目前可以直接指代sequence了，同時增強了default充當identity的能力

Default to a sequence
Default when null inserted
Identity Type
Metadata-only Defaults for NULL columns

#3 Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types
Varchar2, NVarchar2 and Raw這些資料類型的大小上限可以擴充到32k，和其在PL/SQL中的變數類型一樣了，當然過長可能導致像LOB那樣OUT OF LINE存放

#4 Easy Top-N and pagination queries ，更易用的Top-N和頁碼查詢
提供了類似MySQL中limit的文法，Row Limiting Clause

FETCH FIRST 5 ROWS ONLY; ==》僅fetch前5行
FETCH NEXT 0.01 PERCENT ROWS ONLY; 僅fetch 0.0.1%的行數

#5 Row Pattern Matching 增強行模式比對
提供了新的模式比對子句 match_recognize，可以通過match_recognize 定義正則文法

#6 Partitioning Improvements 分區特性增強 , 包括非同步維護全域索引的drop和truncate分區操作， 以及Interval + Reference分區方式
Asynchronous Global Index Maintenance for DROP and TRUNCATE
partition
Cascade Functionality for TRUCATE and EXCHANGE partition
Multiple partition operations in a single DDL
Online move of a partition (without DBMS_REDEFINITION)
Interval + Reference partitioning

#7 Adaptive Execution Plans 自適應執行計畫， 該特性可以談得上神奇，最終的執行計畫將基於執行中獲得的行，列傾斜造成的問題將被克服

#8 Enhanced Statistics 統計資訊增強， 加入了第11級的動態採樣， 對於並行查......餘下全文>>