国产99久久精品_欧美日本韩国一区二区_激情小说综合网_欧美一级二级视频_午夜av电影_日本久久精品视频

最新文章專(zhuān)題視頻專(zhuān)題問(wèn)答1問(wèn)答10問(wèn)答100問(wèn)答1000問(wèn)答2000關(guān)鍵字專(zhuān)題1關(guān)鍵字專(zhuān)題50關(guān)鍵字專(zhuān)題500關(guān)鍵字專(zhuān)題1500TAG最新視頻文章推薦1 推薦3 推薦5 推薦7 推薦9 推薦11 推薦13 推薦15 推薦17 推薦19 推薦21 推薦23 推薦25 推薦27 推薦29 推薦31 推薦33 推薦35 推薦37視頻文章20視頻文章30視頻文章40視頻文章50視頻文章60 視頻文章70視頻文章80視頻文章90視頻文章100視頻文章120視頻文章140 視頻2關(guān)鍵字專(zhuān)題關(guān)鍵字專(zhuān)題tag2tag3文章專(zhuān)題文章專(zhuān)題2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章專(zhuān)題3
問(wèn)答文章1 問(wèn)答文章501 問(wèn)答文章1001 問(wèn)答文章1501 問(wèn)答文章2001 問(wèn)答文章2501 問(wèn)答文章3001 問(wèn)答文章3501 問(wèn)答文章4001 問(wèn)答文章4501 問(wèn)答文章5001 問(wèn)答文章5501 問(wèn)答文章6001 問(wèn)答文章6501 問(wèn)答文章7001 問(wèn)答文章7501 問(wèn)答文章8001 問(wèn)答文章8501 問(wèn)答文章9001 問(wèn)答文章9501
當(dāng)前位置： 首頁(yè) - 科技 - 知識(shí)百科 - 正文

示例演示直方圖的重要性

來(lái)源：懂視網(wǎng) 責(zé)編：小采 時(shí)間：2020-11-09 07:33:27
文檔

示例演示直方圖的重要性

示例演示直方圖的重要性:1. 示例說(shuō)明直方圖的作用。 初始化數(shù)據(jù) dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_obje
推薦度：
導(dǎo)讀示例演示直方圖的重要性:1. 示例說(shuō)明直方圖的作用。 初始化數(shù)據(jù) dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_obje

1. 示例說(shuō)明直方圖的作用。 初始化數(shù)據(jù) dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_objects ; Table created. dexter@STARTREK select count(*

1. 示例說(shuō)明直方圖的作用。

初始化數(shù)據(jù)

dexter@STARTREK> select count(*) fromall_objects ;

COUNT(*)

----------

72642

dexter@STARTREK> create table tuning4_tabnologging as select * from all_objects ;

Table created.

dexter@STARTREK> select count(*) fromall_objects ;

COUNT(*)

----------

72643

dexter@STARTREK> create indexidx_tuning4_tab_owner on tuning4_tab (owner) ;

Index created.

dexter@STARTREK> @gather_tab

Enter value for tbname: tuning4_tab

PL/SQL procedure successfully completed.

在這里碰到了一個(gè)小問(wèn)題，因?yàn)閿?shù)據(jù)的傾斜比較嚴(yán)重，而且oracle數(shù)據(jù)庫(kù)在執(zhí)行g(shù)ather_table_stats的時(shí)候沒(méi)有收集owner列的統(tǒng)計(jì)信息，這里優(yōu)化器選擇了錯(cuò)誤的執(zhí)行計(jì)劃。下面記錄了完整的處理過(guò)程。

出現(xiàn)錯(cuò)誤的執(zhí)行計(jì)劃

dexter@STARTREK> select* from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 989038285

------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

3 - access("OWNER"='PUBLIC' OR"OWNER"='SYS')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

9581 consistent gets

0 physical reads

0 redo size

6805858 bytes sent via SQL*Net to client

43970 bytes received via SQL*Net from client

3952 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

59253 rows processed

下面的輸出可以看到，其實(shí)使用全表掃描的效率要高于indexrange scan。

dexter@STARTREK> select /*+full(tuning4_tab)*/ *from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1641193091

---------------------------------------------------------------------------------

| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 4687 | 443K| 290 (1)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 4687 | 443K| 290 (1)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4927 consistent gets

0 physical reads

0 redo size

3035580 bytes sent via SQL*Net toclient

43970 bytes received via SQL*Netfrom client

3952 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

59253 rows processed

dexter@STARTREK>

trace 一下

dexter@STARTREK> alter session settracefile_identifier=histogram ;

Session altered.

dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';

Session altered.

dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 989038285

------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 -access("OWNER"='PUBLIC' OR "OWNER"='SYS')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

9581 consistent gets

0 physical reads

0 redo size

6805858 bytes sent via SQL*Net toclient

43970 bytes received via SQL*Netfrom client

3952 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

59253 rows processed

dexter@STARTREK> alter session set events '10053trace name context off ';

Session altered.

dexter@STARTREK>

從10053中看到

Access path analysis for TUNING4_TAB

***************************************

SINGLE TABLE ACCESS PATH

SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]

Column(#1): OWNER(

AvgLen: 6NDV: 31 Nulls: 0 Density: 0.032258

Table:TUNING4_TAB Alias: TUNING4_TAB

Card:Original: 72643.000000 Rounded: 4687 Computed: 4686.65 Non Adjusted: 4686.65

Rounded:

4687

實(shí)際：

59253

明顯是由于統(tǒng)計(jì)信息不準(zhǔn)確造成的。我們看一下它的直方圖信息。

其實(shí)從執(zhí)行計(jì)劃

| 0 | SELECTSTATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |

也可以看到它的統(tǒng)計(jì)信息不準(zhǔn)確。

確定問(wèn)題根源

dexter@STARTREK> select* from user_tab_histograms where table_name='TUNING4_TAB' andcolumn_name='OWNER' ;

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

------------------------------------------------------------ --------------- --------------------------------------------

TUNING4_TAB OWNER 0 3.3913E+35

TUNING4_TAB OWNER 1 4.5831E+35

dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;

TABLE_NAME COLUMN_NAME HISTOGRAM

------------------------------------------------------------ ---------------

TUNING4_TAB OWNER NONE

TUNING4_TAB OBJECT_NAME NONE

TUNING4_TAB SUBOBJECT_NAME NONE

TUNING4_TAB OBJECT_ID NONE

TUNING4_TAB DATA_OBJECT_ID NONE

TUNING4_TAB OBJECT_TYPE NONE

TUNING4_TAB CREATED NONE

TUNING4_TAB LAST_DDL_TIME NONE

TUNING4_TAB TIMESTAMP NONE

TUNING4_TAB STATUS NONE

TUNING4_TAB TEMPORARY NONE

TUNING4_TAB GENERATED NONE

TUNING4_TAB SECONDARY NONE

TUNING4_TAB NAMESPACE NONE

TUNING4_TAB EDITION_NAME NONE

15 rows selected.

沒(méi)有直方圖信息。

我們來(lái)直接查看表中數(shù)據(jù)的分布情況

dexter@STARTREK> select owner,count(*) fromtuning4_Tab group by owner order by 2 ;

OWNER COUNT(*)

------------------------------ ----------

OWBSYS 2

APPQOSSYS 5

SCOTT 6

SI_INFORMTN_SCHEMA 8

OUTLN 8

ORACLE_OCM 8

BI 8

ORDPLUGINS 10

PM 10

FLOWS_FILES 11

OWBSYS_AUDIT 12

DEXTER 25

APEX 33

HR 35

IX 48

DBSNMP 57

OE 112

ORDDATA 239

SH 299

EXFSYS 308

WMSYS 312

CTXSYS 384

SYSTEM 516

XDB 517

OLAPSYS 717

MDSYS 1545

APEX_030200 2251

ORDSYS 2512

SYSMAN 3392

PUBLIC 28027

SYS 31226

31 rows selected.

傾斜很?chē)?yán)重，并且因?yàn)間ather_table_stats的時(shí)候默認(rèn)沒(méi)有收集直方圖信息，導(dǎo)致優(yōu)化器沒(méi)有選擇正確的執(zhí)行計(jì)劃，我們來(lái)收集一下它的直方圖。

默認(rèn)為FOR ALL COLUMNS SIZEAUTO沒(méi)有收集直方圖。

dexter@STARTREK> selectdbms_stats.get_param('METHOD_OPT') from dual ;

DBMS_STATS.GET_PARAM('METHOD_OPT')

------------------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

收集列的直方圖信息

dexter@STARTREK> execdbms_stats.gather_table_stats(user,'tuning4_tab',cascade=>true,method_opt=>'FORALL columns size skewonly') ;

PL/SQL procedure successfully completed.

dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;

TABLE_NAME COLUMN_NAME HISTOGRAM

------------------------------------------------------------ ---------------

TUNING4_TAB OWNER FREQUENCY

TUNING4_TAB OBJECT_NAME HEIGHT BALANCED

TUNING4_TAB SUBOBJECT_NAME FREQUENCY

TUNING4_TAB OBJECT_ID NONE

TUNING4_TAB DATA_OBJECT_ID HEIGHT BALANCED

TUNING4_TAB OBJECT_TYPE FREQUENCY

TUNING4_TAB CREATED HEIGHT BALANCED

TUNING4_TAB LAST_DDL_TIME HEIGHT BALANCED

TUNING4_TAB TIMESTAMP HEIGHT BALANCED

TUNING4_TAB STATUS FREQUENCY

TUNING4_TAB TEMPORARY FREQUENCY

TUNING4_TAB GENERATED FREQUENCY

TUNING4_TAB SECONDARY FREQUENCY

TUNING4_TAB NAMESPACE FREQUENCY

TUNING4_TAB EDITION_NAME NONE

15 rows selected.

owner為頻率直方圖，比較正確。

dexter@STARTREK> select * from user_tab_histograms wheretable_name='TUNING4_TAB' and column_name='OWNER' ;

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

------------------------------------------------------------ --------------- -------------------------------------

TUNING4_TAB OWNER 1 3.3913E+35

TUNING4_TAB OWNER 179 3.3913E+35

TUNING4_TAB OWNER 206 3.4959E+35

TUNING4_TAB OWNER 212 3.5442E+35

TUNING4_TAB OWNER 215 3.5448E+35

TUNING4_TAB OWNER 244 3.6006E+35

TUNING4_TAB OWNER 246 3.7551E+35

TUNING4_TAB OWNER 249 3.8082E+35

TUNING4_TAB OWNER 370 4.0119E+35

TUNING4_TAB OWNER 383 4.1159E+35

TUNING4_TAB OWNER 422 4.1174E+35

TUNING4_TAB OWNER 423 4.1186E+35

TUNING4_TAB OWNER 436 4.1186E+35

TUNING4_TAB OWNER 636 4.1186E+35

TUNING4_TAB OWNER 2824 4.1711E+35

TUNING4_TAB OWNER 2855 4.3242E+35

TUNING4_TAB OWNER 5199 4.3277E+35

TUNING4_TAB OWNER 5455 4.3277E+35

TUNING4_TAB OWNER 5500 4.3277E+35

TUNING4_TAB OWNER 5525 4.5330E+35

TUNING4_TAB OWNER 5567 4.5831E+35

21 rows selected.

再次測(cè)試

已經(jīng)收集好了直方圖，我們?cè)賮?lái)看一下執(zhí)行計(jì)劃以及10053事件。

set autotrace traceonly

alter session set tracefile_identifier=histogram ;

alter session set events '10053 trace name contextforever ,level 12';

select * from tuning4_Tab where owner='SYS' orowner='PUBLIC' ;

alter session set events '10053 trace name contextoff ';

已經(jīng)選擇了正確、最優(yōu)的執(zhí)行計(jì)劃。

dexter@STARTREK> set autotrace traceonly

dexter@STARTREK> alter session settracefile_identifier=histogram ;

Session altered.

dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';

Session altered.

dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1641193091

---------------------------------------------------------------------------------

| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 59137 | 5601K| 291 (1)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 59137 | 5601K| 291 (1)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4927 consistent gets

0 physical reads

0 redo size

3035580 bytes sent via SQL*Net toclient

43970 bytes received via SQL*Netfrom client

3952 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

59253 rows processed

dexter@STARTREK> alter session set events '10053trace name context off ';

Session altered.

dexter@STARTREK> exit

SINGLE TABLE ACCESS PATH

SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]

Column(#1):

NewDensity:0.000090, OldDensity:0.000007 BktCnt:5567, PopBktCnt:5565,PopValCnt:19, NDV:31

Column(#1): OWNER(

AvgLen: 6NDV: 31 Nulls: 0 Density: 0.000090

Histogram: Freq #Bkts: 21 UncompBkts: 5567 EndPtVals: 21

Table:TUNING4_TAB Alias: TUNING4_TAB

Card:Original: 72643.000000 Rounded: 59137 Computed: 59137.43 Non Adjusted: 59137.43

Rounded: 59137

比較正確了。也選擇了全表掃描作為最佳的accesspath。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

文檔

示例演示直方圖的重要性

示例演示直方圖的重要性:1. 示例說(shuō)明直方圖的作用。 初始化數(shù)據(jù) dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_obje
推薦度：
標(biāo)簽： 作用 直方圖 演示
  • 熱門(mén)焦點(diǎn)

最新推薦

猜你喜歡

熱門(mén)推薦

專(zhuān)題
Top
主站蜘蛛池模板： 亚洲精品毛片久久久久久久 | 国产精品视频一区二区三区 | 国产日韩免费 | 日本特级淫片免费看 | va欧美国产在线视频 | 欧美极品欧美精品欧美视频 | 视频在线一区 | 拍真实国产伦偷精品 | 波多野吉衣在线观看 | 自拍 欧美 在线 综合 另类 | 国产99久久亚洲综合精品 | 日韩欧美视频一区 | 久久综合爱 | 久久精品国产欧美成人 | 国产a国产片| 视频一区二区三区在线 | 亚洲国产一区二区三区精品 | 亚洲一区二区欧美 | 日韩成人在线电影 | 国产区精品视频 | 国产原创一区 | 国产国拍亚洲精品永久不卡 | 国内精品伊人久久久久妇 | 麻豆91精品91久久久 | 国产日韩欧美亚洲综合 | 日韩综合在线 | 免费观看h | 国产69久久精品成人看小说 | 一级欧美日韩 | 99久久精品国产一区二区三区 | 亚洲国产欧美视频 | 99re伊人| 亚洲香蕉久久综合网 | 免费看一级毛片 | 极品美女一区二区三区 | 91香蕉国产亚洲一区二区三区 | 国产麻豆久久 | 欧美性受一区二区三区 | 欧洲一区二区三区在线观看 | 国内精品久久久久 | 免费国产叼嘿视频大全网站 |