[20170603]12c Top Frequency histogram.txt

来源:http://www.cnblogs.com/lfree/archive/2017/06/05/6947417.html
-Advertisement-
Play Games

恢復內容開始 [20170603]12c Top Frequency histogram.txt--//個人對直方圖瞭解很少,以前2種直方圖類型對於目前的許多應用來講已經足夠,或者講遇到的問題很少.--//抽一點點時間,簡單探究12c Top Frequency histogram.--//以前的頻 ...


---恢復內容開始---

[20170603]12c Top Frequency histogram.txt

--//個人對直方圖瞭解很少,以前2種直方圖類型對於目前的許多應用來講已經足夠,或者講遇到的問題很少.
--//抽一點點時間,簡單探究12c Top Frequency histogram.

--//以前的頻率直方圖Frequency histogram,受限bucket(桶的大小),如果有255個不同值,oracle分析後不會建立頻率直方圖,而是建立高
--//度直方圖.這樣的情況會導致一些流行值的統計在顯示執行計劃時差距很大.而12c引入了Top Frequency histogram,註意這裡的top,
--//我的理解就是流行值(popular),也就是這樣建立的直方圖僅僅包括popular,其他non-popular不考慮,這樣在sql語句的查詢這些
--//popular時,顯示的統計信息相對準確,從而有利於oracle選擇正確的執行計劃.

--//以下是我的學習筆記,也許會存在許多錯誤,僅僅做一個記錄.我也看了許多別人的blog.^_^.而且我目前的環境只有12.0.0.1(版本太
--//低).

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

--//如果要建立Top Frequency histogram必須要滿足幾個條件:
--//鏈接 raajeshwaran.blogspot.co.id/2016/06/top-frequency-histogram-in-12c.html

The database creates a Top frequency histogram, when the following criteria are met.

NDV is greater than n, where n is the requested number of buckets (default 254)
The percentage of rows occupied by Top-frequent values is greater than or equal to the threshold p where p is (1-(1/n)*100).
The estimate_percent parameter in dbms_stats gathering procedure should be auto_sample_size (set to default)

--//翻譯過來NDV(也就是欄位的不同值)大於N(指bucket的數量).
--//流行值(popular)在Top-frequent中合計數量/總計數量之比要大於(1-(1/n)*100).如果建立10個桶,這樣流行值的總計必須在90%以上

2.首先驗證(1-(1/n)*100)比值是否正確:
SCOTT@test01p> create table t as select * from dba_objects;
Table created.

select column_name,num_distinct,density,histogram,SAMPLE_SIZE
  from user_tab_col_statistics
  where table_name ='T'
  and column_name ='OWNER';

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32     .03125 NONE                  91695

--//12c ctas 建立統計信息,但是不會建立直方圖.density 1/32=.03125.
SCOTT@test01p> select count(*) from t;
  COUNT(*)
----------
     91695

--//隨手寫的sql語句:

with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3  from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b;

ROWNUM OWNER                N1         N2         N3         X1         X2
------ ----------------- ----- ---------- ---------- ---------- ----------
     1 SYS               41942      91695      41942     .45741          0
     2 PUBLIC            37142      91695      79084     .86247         .5
     3 APEX_040200        3405      91695      82489      .8996     .66667
     4 ORDSYS             3157      91695      85646     .93403        .75
     5 MDSYS              1819      91695      87465     .95387         .8
     6 XDB                 985      91695      88450     .96461     .83333
     7 SYSTEM              641      91695      89091      .9716     .85714
     8 CTXSYS              405      91695      89496     .97602       .875
     9 WMSYS               387      91695      89883     .98024     .88889
    10 DVSYS               352      91695      90235     .98408         .9
    11 SH                  309      91695      90544     .98745     .90909
    12 ORDDATA             292      91695      90836     .99063     .91667
    13 LBACSYS             209      91695      91045     .99291     .92308
    14 OE                  142      91695      91187     .99446     .92857
    15 SCOTT                96      91695      91283     .99551     .93333
    16 GSMADMIN_INTERNAL    77      91695      91360     .99635      .9375
    17 IX                   58      91695      91418     .99698     .94118
    18 DBSNMP               55      91695      91473     .99758     .94444
    19 PM                   44      91695      91517     .99806     .94737
    20 HR                   35      91695      91552     .99844        .95
    21 OLAPSYS              25      91695      91577     .99871     .95238
    22 OJVMSYS              23      91695      91600     .99896     .95455
    23 DVF                  19      91695      91619     .99917     .95652
    24 FLOWS_FILES          13      91695      91632     .99931     .95833
    25 AUDSYS               12      91695      91644     .99944        .96
    26 ORDPLUGINS           10      91695      91664     .99966     .96154
    27 OUTLN                10      91695      91664     .99966     .96296
    28 BI                    8      91695      91688     .99992     .96429
    29 ORACLE_OCM            8      91695      91688     .99992     .96552
    30 SI_INFORMTN_SCHEM     8      91695      91688     .99992     .96667
    31 APPQOSSYS             5      91695      91693     .99998     .96774
    32 TEST                  2      91695      91695          1     .96875

--//如果加入條件where round(n3/n2,5) >round(1-1/rownum,5),全部輸出.也就是這樣如果桶小於32,大於1.建立的都是Top Frequency.

3.繼續測試:
D:\temp>cat a1.sql
cat a1.sql
exec  dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',method_opt=>'for columns owner size &1');
select column_name,num_distinct,density,histogram,SAMPLE_SIZE from user_tab_col_statistics where table_name ='T' and column_name ='OWNER';

SCOTT@test01p> @ a1.sql 2
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32     .03125 HYBRID                 5500

SCOTT@test01p> @ a1.sql 3
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 5.4529E-06 TOP-FREQUENCY         91695

SCOTT@test01p> @ a1.sql 4
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 5.4529E-06 TOP-FREQUENCY         91695

SCOTT@test01p> @ a1.sql 31
PL/SQL procedure successfully completed.
COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 5.4529E-06 TOP-FREQUENCY         91695

SCOTT@test01p> @ a1.sql 32
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 5.4529E-06 FREQUENCY             91695

--//除了bucket=2,32建立的直方圖HYBRID,FREQUENCY外,建立的都是TOP-FREQUENCY.
--//以10個bucket為例.解方程式(90235-x)/(91695-x)=0.9 ,得到x=77095.也就是要減少77095.

--//delete t where owner='SYS' and rownum<=41000;
--//delete t where owner='PUBLIC' and rownum<=36095;

SCOTT@test01p> delete t where owner='SYS' and rownum<=41000;
41000 rows deleted.

SCOTT@test01p> delete t where owner='PUBLIC' and rownum<=36095;
36095 rows deleted.

SCOTT@test01p> commit ;
Commit complete.

with a as (select distinct owner,count(*) over(partition by owner) n1 ,count(*) over () n2 from t order by 2 desc ),
b as (select owner,n1,n2,sum(n1) over (order by n1 desc) n3  from a order by n1 desc)
select rownum,owner,n1,n2,n3,round(n3/n2,5) x1,round(1-1/rownum,5) x2 from b where rownum<=11;

ROWNUM OWNER         N1         N2         N3         X1         X2
------ ----------- ---- ---------- ---------- ---------- ----------
     1 APEX_040200 3405      14600       3405     .23322          0
     2 ORDSYS      3157      14600       6562     .44945         .5
     3 MDSYS       1819      14600       8381     .57404     .66667
     4 PUBLIC      1047      14600       9428     .64575        .75
     5 XDB          985      14600      10413     .71322         .8
     6 SYS          942      14600      11355     .77774     .83333
     7 SYSTEM       641      14600      11996     .82164     .85714
     8 CTXSYS       405      14600      12401     .84938       .875
     9 WMSYS        387      14600      12788     .87589     .88889
    10 DVSYS        352      14600      13140         .9         .9
    11 SH           309      14600      13449     .92116     .90909
11 rows selected.
--//backet=10,前面10個值占90%.

SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.
COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 .000034247 TOP-FREQUENCY         14600

--//再減少1條記錄.
SCOTT@test01p> delete t where owner='SYS' and rownum<=1;
1 row deleted.

SCOTT@test01p> commit ;
Commit complete.

ROWNUM OWNER         N1         N2         N3         X1         X2
------ ----------- ---- ---------- ---------- ---------- ----------
     1 APEX_040200 3405      14599       3405     .23324          0
     2 ORDSYS      3157      14599       6562     .44948         .5
     3 MDSYS       1819      14599       8381     .57408     .66667
     4 PUBLIC      1047      14599       9428      .6458        .75
     5 XDB          985      14599      10413     .71327         .8
     6 SYS          941      14599      11354     .77772     .83333
     7 SYSTEM       641      14599      11995     .82163     .85714
     8 CTXSYS       405      14599      12400     .84937       .875
     9 WMSYS        387      14599      12787     .87588     .88889
    10 DVSYS        352      14599      13139     .89999         .9
    11 SH           309      14599      13448     .92116     .90909
11 rows selected.
--//現在前10占.89999.

SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32    .018378 HYBRID                14599

--//可以發現建立的直方圖不是TOP-FREQUENCY,而是HYBRID(混合型直方圖).

4.轉化成TOP-FREQUENCY.
SCOTT@test01p> insert into t  select * from dba_objects where owner='SYS' and rownum=1;
1 row created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> @ a1 10
PL/SQL procedure successfully completed.

COLUMN_NAME          NUM_DISTINCT    DENSITY HISTOGRAM       SAMPLE_SIZE
-------------------- ------------ ---------- --------------- -----------
OWNER                          32 .000034247 TOP-FREQUENCY         14600

--//DENSITY=1/SAMPLE_SIZE/2, 1/14600/2=.00003424657534246575正好符合.

5.現在看看執行計劃:
SCOTT@test01p> select count(*) from t where owner='DVSYS';
  COUNT(*)
----------
       352

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2at34f0zaqhzj, child number 0
-------------------------------------
select count(*) from t where owner='DVSYS'
Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   428 (100)|          |      1 |00:00:00.01 |    1544 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     8 |            |          |      1 |00:00:00.01 |    1544 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    352 |  2816 |   428   (0)| 00:00:01 |    352 |00:00:00.01 |    1544 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"='DVSYS')

--//可以發現e_rows=A-Rows.可以發現非常準確.
--//查看直方圖信息.

SCOTT@test01p> select endpoint_number,endpoint_actual_value from user_tab_histograms where table_name ='T' and column_name ='OWNER' order by 1;
ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
--------------- --------------------
           3405 APEX_040200
           3810 CTXSYS
           4162 DVSYS
           5981 MDSYS
           9138 ORDSYS
          10185 PUBLIC
          11127 SYS
          11768 SYSTEM
          12155 WMSYS
          13140 XDB
10 rows selected.

--//4162-3810=352,可以發現正好符合.也就是popular值統計很正確.看看非popular值.

SCOTT@test01p> select count(*) from t where owner='DVSYS1';
  COUNT(*)
----------
         0

Plan hash value: 2966233522        
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   428 (100)|          |      1 |00:00:00.01 |    1544 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     8 |            |          |      1 |00:00:00.01 |    1544 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     66 |   528 |   428   (0)| 00:00:01 |      0 |00:00:00.01 |    1544 |
---------------------------------------------------------------------------------------------------------------------

SCOTT@test01p> select count(*) from t where owner='SCOTT';
  COUNT(*)
----------
        96

Plan hash value: 2966233522
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   428 (100)|          |      1 |00:00:00.01 |    1544 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     8 |            |          |      1 |00:00:00.01 |    1544 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     66 |   528 |   428   (0)| 00:00:01 |     96 |00:00:00.01 |    1544 |
---------------------------------------------------------------------------------------------------------------------

--//可以發現估計E-Rows如何計算的呢?

6.做10053跟蹤:
D:\tools\sqllaji>cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'&&1');

SCOTT@test01p> @ 10053x f31kz63ksu1tc 0
PL/SQL procedure successfully completed.


***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#1):
    NewDensity:0.004545, OldDensity:0.000034 BktCnt:13140.000000, PopBktCnt:13140.000000, PopValCnt:10, NDV:32
  Column (#1): OWNER(VARCHAR2)
    AvgLen: 8 NDV: 32 Nulls: 0 Density: 0.000000
    Histogram: Top-Freq  #Bkts: 13140  UncompBkts: 13140  EndPtVals: 10  ActualVal: yes
  Table: T  Alias: T
    Card: Original: 14600.000000  Rounded: 66  Computed: 66.36  Non Adjusted: 66.36
  Access Path: TableScan
    Cost:  428.36  Resp: 428.36  Degree: 0
      Cost_io: 428.00  Cost_cpu: 14122025
      Resp_io: 428.00  Resp_cpu: 14122025
  Best:: AccessPath: TableScan
         Cost: 428.36  Degree: 1  Resp: 428.36  Card: 66.36  Bytes: 0


    check parallelism for statement[<unnamed>]
kkfdtParallel: parallel is possible (no statement type restrictions)
    kkfdPaForcePrm: dop:1 ()
     use dictionary DOP(1) on table
kkfdPaPrm:- The table : 106380
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdiPaPrm: dop:1 serial(?)
***************************************

--//使用 NewDensity:0.004545.
 BktCnt:13140.000000, PopBktCnt:13140.000000 => 對應就是前10個流行值的總和.

--//非流行值的數量: 14600-13140=1460
--//非流行值的桶數量: 32-10=22
--//非流行值的數量/非流行值的桶數量 1460/22=66.36363636363636363636,四捨五入66,正好符合執行計劃的推斷.
--//NewDensity的計算 =1460/14600/22=.00454545454545454545,非常接近.



---恢復內容結束---


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • omi cli "omi cli" "omi cli命令" "omi框架" 用戶指南 "文件目錄" "npm 腳本" "npm start" "npm run dist" "代碼分割" "相容 IE8" "插入 CSS" "插入組件局部 CSS" "局部CSS使用圖片" "插入 Less" "插入組 ...
  • 前端感覺寫的比較少,也是為了練手,下午沒事用來寫了這個三級聯動,也是第一次寫這東西。 據我瞭解,城市信息可以選擇存在資料庫或者直接寫在前端,為了省事,我直接寫在前端,下麵是我的代碼: <!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional/ ...
  • 關於網路安全加密的介紹可以看之前文章: "1. 網路安全——數據的加密與簽名,RSA介紹" "2. Base64編碼、MD5、SHA1 SHA512、HMAC(SHA1 SHA512)" "3. When I see you again(DES、AES、RSA、Base64、MD5加密原理介紹,代碼 ...
  • Android系統在運行每一個程式應用的時候,都會創建一個Application對象,用於存儲與整個應用相關的公共變數。一個Android應用只會生成一個Application對象,在不同的Activity中獲取的Application對象是一樣的,所以Application對象是一個單例(Sing ...
  • 轉載請註明:http://www.cnblogs.com/igoslly/p/6947225.html 下一章是關於ListFragment的內容,首先先介紹ListView的相關配置,理解ListFragment也相較容易。 在fznpcy專欄:http://blog.csdn.net/fznpc ...
  • 目錄 零、主要參考網頁 一、概述 二、分區類型以及創建方式 三、分區表的管理 三、分區表的管理 四、獲取分區表信息 四、獲取分區表信息 五、分區的局限與分表 零、主要參考網頁 http://www.2cto.com/database/201503/380348.html【mysql分表和表分區詳解】 ...
  • 目錄 零、參考網頁 一、概述 二、語法 三、外鍵 四、索引 零、參考網頁 http://zhidao.baidu.com/link?url=ik8ZtrHL2qfZMgQStSFEcP2ORechkwBzbxBQjMQ15SoCV11-rRv5buPIPLZBZu570-YWRoAFsqANBiII ...
  • 卸載MariaDB CentOS7預設安裝MariaDB而不是MySQL,而且yum伺服器上也移除了MySQL相關的軟體包。因為MariaDB和MySQL可能會衝突,故先卸載MariaDB。 1、安裝新版mysql之前,我們需要將系統自帶的mariadb-lib卸載 2、到mysql的官網下載最新版 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...