D2-00 11g 统计-直方图(90 页)+-

OracleRDBMSTuningCBO表统计数据与直方图,赵元杰zyj56812012年11月,2/78,内容提要,SQL语句处理步骤回顾CBO模式统计数据搜集表统计有关概念与术语直方图统计数据搜集列的相关性问题优化器的决策问题,3/78,SQL语句处理阶段,Close,Open,Fetch,Bind,Parse,Execute,4/78,SQLexecution,分析PARSER,OptimizerMode?,数据字典Dictionary,Cost-BasedOptimizer,Rule-BasedOptimizer,RowsourceGenerator,结果,统计数据,CBO,RBO,查询计划,用户,SQL处理流程,5/78,SQL语句处理阶段:Parse,分析阶段(Parsephase):搜索共享池是否存储一样SQL语句检查语法(Checkssyntax)检查语义与权限(semantics-搜集表的10%统计数据-EXECDBMS_STATS.GATHER_TABLE_STATS(JJCDZBXS,TBDA_SP_TP,estimate_percent=10);-,SCOTT模式下表的统计：DBMS_STATS.GATHER_TABLE_STATS：,19/270,2.删除表的统计数据,EXECdbms_stats.delete_table_stats(ownname=SCOTT,tabname=EMP);-EXECdbms_stats.delete_table_stats(ownname=SCOTT,tabname=EMP,cascade_columns=false,cascade_indexes=false);-DBMS_STATS.DELETE_SCHEMA_STATSexamplesSQLEXECdbms_stats.delete_schema_stats(JJCDZBXS);PL/SQLproceduresuccessfullycompleted.,DELETE_TABLE_STATS删除陈旧数据：如果统计数据过于陈旧，删除也是必要的：,20/270,3.搜集索引的统计数据,BeginSYS.DBMS_STATS.GATHER_INDEX_STATS(OwnName=SCOTT,IndName=DEPT_PK);END;/-SELECTBlevel,distinct_keys,last_analyzedFROMDBA_INDEXESWHEREindex_name=DEPT_PK;-BeginSYS.DBMS_STATS.DELETE_INDEX_STATS(OwnName=SCOTT,IndName=DEPT_PK);END;/,GATHER_INDEX_STATS：如果DBMS_STATS.GATHER_TABLES_STATS时设置cascade=true。则Oracle同时搜集索引数据。如果索引没有搜集统计数据，则用：,21/270,4.搜集Schema统计数据,EXECdbms_stats.gather_schema_stats(ownname=-相关也统计,对模式下所有表和索引进行分析：在SQL*PLUS下运行：,22/78,内容提要,SQL语句处理步骤回顾CBO模式统计数据搜集表统计有关概念与术语直方图统计数据搜集列的相关性问题优化器的决策问题,23/78,几个概念术语-可选择性,可选择性(Selectivity)谓词可选择性dept=:b1，则可选择性为：1/NDV=1/3=0.333谓词可选择性(dept=:b1orcode=:b2)=(1/3)+(1/3)(1/3)*(1/3)=0.555(可选择性高些)谓词可选择性dept=:b1andcode=:b2=(1/3)*(1/3)=0.111,24/78,几个概念术语-基数,基数(CARDINALITY)：基数(cardinality)是一个数据源，表示能返回的数据行数；CARD表示Oracle认为此操作将返回多少行数(10g-11g用rows)；Cardinality=Selectivity*Numberofrows,25/78,几个概念术语-基数insertintot1selectl1,casewhenl1100,method_opt=forallcolumnssize1);End;/,27/78,几个概念术语-基数：无直方图,非均匀分布下的无直方图情况非均匀分布,selectcolor,count(*)fromt1groupbycolororderby2;COLORCOUNT(*)-blue9red90white900selectcount(*)fromt1wherecolor=blue;SQLselect*fromtable(dbms_xplan.display_cursor);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|3(100)|1|SORTAGGREGATE|1|6|*2|TABLEACCESSFULL|T1|333|1998|3(0)|00:01|-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blue),28/78,几个概念术语-直方图,直方图(Histogram)：直方图：在Oracle系统里是列上的统计信息，主要用于倾斜度很高的列上；统计信息包括:表上的统计信息;索引上的统计信息;列上的统计信息,29/78,几个概念术语-直方图种类,直方图种类：Frequencyorequi-width(频率直方图或等宽直方图)；Heightbalancedorequi-depth(高度平衡或等比深度)；,30/78,几个概念术语-直方图种类,直方图种类：频率直方图(一般搜集频率直方图)查询dba_tab_col_statistics的histogram列比如男女年龄结构为“金字塔”形状,31/78,DBMS_STATS使用-基数：有直方图,有直方图情况用gather_table_stats搜集直方图,EXECdbms_stats.gather_table_stats(cbo2,t1,estimate_percent=100,method_opt=forallcolumnssize10);PL/SQLproceduresuccessfullycompleted.Selectcount(*)fromt1wherecolor=blue;SQLselect*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|6|3(0)|00:00:01|1|SORTAGGREGATE|1|6|*2|TABLEACCESSFULL|T1|9|54|3(0)|00:00:01-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blue)14rowsselected.,32/78,DBMS_STATS使用-直方图,直方图：非均匀分布下需要统计直方图；一般情况不建议统计直方图-因为消耗CPU；SKEWONLY：非均匀分布；sizeskewonly只收集非均匀分布的直方图，系统自动决定桶数；,33/78,Oracle优化器与列直方图,优化器的选择与直方图中数据有关：直方图会影响优化器的执行计划当数据出现倾斜性是会带来性能的改善非均匀的数据分布可得到最佳的直接计划，下面例子就是不均匀,Numberofbuckets=50,34/78,DBMS_STATS使用-直方图与桶,桶(Bucket)：user_tab_histograms数据字典的NUMBER_BUCTETS列代表直方图的桶数；,35/78,DBMS_STATS使用-直方图与估计,直方图统计百分比问题：直方图统计数据搜集百分比,begindbms_stats.gather_table_stats(cbo2,t1,estimate_percent=1,method_opt=forallcolumnssize10);end;/1selectcolumn_name,endpoint_numberfromdba_tab_histograms2*wheretable_name=T1andcolumn_name=COLORSQL/ColumnNameENDPOINT_NUMBER-COLOR0COLOR12rowsselected,采用1%百分比，所以只找到2种,本次搜集会覆盖原来的统计数据,36/78,DBMS_STATS使用-直方图与估计,直方图统计百分比问题：直方图统计数据搜集百分比,SQLexplainplanforselectcount(*)fromt1wherecolor=blue;Explained.SQLselect*fromtable(dbms_xplan.display);Planhashvalue:3724264953-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|6|3(0)|00:00:01|1|SORTAGGREGATE|1|6|*2|TABLEACCESSFULL|T1|333|1998|3(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blue),37/78,Oracle优化器与列直方图,优化器是否采用索引=搜集直方图情况而定：直方图是OracleCBO优化器使用的一种统计数据，比如有一个字段a的取值范围是110000，整个表有100万条记录，如果要查询a10andaforallcolumnssizeskewonlymethod_opt=forallcolumnssizeauto,begindbms_stats.gather_schema_stats(ownname=SCOTT,estimate_percent=dbms_stats.auto_sample_size,method_opt=forallcolumnssizeskewonly,degree=7);end;/,42/78,DBMS_STATS使用-创建直方图,gather_table_stats的method_opt：forallcolumns:统计所有列的histograms.forallindexedcolumns:统计所有indexed列的histograms.forallhiddencolumns:统计你看不到列的histogramsforcolumnsSIZE|REPEAT|AUTO|SKEWONLY:N的取值范围1,254;REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY-sizeskewonly只收集非均匀分布的直方图，系统自动决定桶数(bucket),43/78,DBMS_STATS使用-创建直方图,DBMS_STATS可搜集直方图统计数据：创建直方图只要需要，可自动搜集；可用DBMS_STATS包改变默认值；可用DBMS_STATS手工创建直方图；下面例子显示要在PROD_LIST_PRICE列上创建50桶直方图:,EXECUTEdbms_stats.gather_table_stats(sh,products,method_opt=forcolumnssize50prod_list_price);,44/78,DBMS_STATS使用-查看直方图,SELECTcolumn_name,num_distinct,num_buckets,histogramFROMUSER_TAB_COL_STATISTICSWHEREhistogramNONE;,SELECTcolumn_name,num_distinct,num_buckets,histogramFROMUSER_TAB_COL_STATISTICSWHEREcolumn_name=PROD_LIST_PRICE;,1,2,45/78,DBMS_STATS使用-直方图提示,DBMS_STATS的METHOD_OPTS选项是FORALLCOLUMNSSIZEAUTO,会在需要时默认创建直方图经常在WHERE查询倾斜的列时.在列上存在很高的倾斜数据分布时下面情况不使用直方图，除非存在特别好的性能直方图需要额外的存储空间.与其他统计数据一样，直方图也是静态的.当分布数据列的值变化频繁时要重计算直方图.对于绑定变量存在质疑,46/78,DBMS_STATS包-直方图与优化器,优化器在下面情况不是有直方图:相关列使用绑定变量列的数据是均匀分布的查询语句的子句WHERE不使用直方图相关的列列是唯一的约束，并且只在等价谓词中使用结论：除非访问的索引字段倾斜性很严重，类似上面的例子，需要使用直方图的，其他情况，都可以使用绑定变量。如果需要使用直方图，建议还是别用绑定变量了。,47/78,DBMS_STATS包-统计数据与数据字典,相关的数据字典：user_tab_statistics-统计之后用来显示表的统计信息user_tables-查看schames下所有表的统计信息user_tab_col_statistics-统计之后用来显示列的统计信息user_tab_histograms-统计之后用来显示直方图的统计信息user_ind_statistics-统计之后用来显示索引的统计信息user_indexes,48/78,DBMS_STATS包-直方图元数据,ALL_TAB_HISTOGRAMSENDPOINT_NUMBER-直方图柱状数ENDPOINT_VALUE规格化柱状终点值ENDPOINT_ACTUAL_VALUE未做规格化柱状(实际)终点值,ManagingStatisticsCollection:Example,dbms_stats.gather_table_stats(sh-schema,customers-table,null-partition,20-samplesize(%),false-blocksample?,forallcolumns-columnspec,4-degreeofparallelism,default-granularity,true);-cascadetoindexes,dbms_stats.set_param(CASCADE,DBMS_STATS.AUTO_CASCADE);dbms_stats.set_param(ESTIMATE_PERCENT,5);dbms_stats.set_param(DEGREE,NULL);,50/78,StatisticsGathering:ManualApproaches,Dynamicsampling:,BEGINDBMS_STATS.DELETE_TABLE_STATS(OE,ORDERS);DBMS_STATS.LOCK_TABLE_STATS(OE,ORDERS);END;,BEGINDBMS_STATS.GATHER_TABLE_STATS(OE,ORDERS);DBMS_STATS.LOCK_TABLE_STATS(OE,ORDERS);END;,Forobjectsmodifiedinbatchoperations:GatherstatisticsaspartofthebatchoperationFornewobjects:Gatherstatisticsimmediatelyafterobjectcreation,Manualstatisticscollection:,LockingStatistics,PreventsautomaticgatheringIsusedprimarilyforvolatiletablesLockwithoutstatisticsimpliesdynamicsampling.Lockwithstatisticsisforrepresentativevalues.,EXECUTEDBMS_STATS.LOCK_TABLE_STATS(ownername,tablename);,EXECUTEDBMS_STATS.LOCK_SCHEMA_STATS(ownername);,SELECTstattype_lockedFROMdba_tab_statistics;,52/78,VerifyingTableStatistics,SELECTlast_analyzedanalyzed,sample_size,monitoring,table_nameFROMdba_tablesWHEREtable_name=EMPLOYEES;ANALYZEDSAMPLE_SIZEMONTABLE_NAME-09-FEB-042000YESEMPLOYEES,53/78,VerifyingColumnStatistics,SELECTcolumn_name,num_distinct,histogram,num_buckets,density,last_analyzedanalyzedFROMdba_tab_col_statisticsWHEREtable_name=SALESORDERBYcolumn_name;,COLUMN_NAMENUM_DISTINCTHISTOGRAMNUM_BUCKETSDENSITYANALYZED-AMOUNT_SOLD3586NONE1.00027886209-FEB-04CHANNEL_ID4NONE1.2509-FEB-04CUST_ID7059NONE1.00014166309-FEB-04PROD_ID72FREQUENCY725.4416E-0709-FEB-04PROMO_ID4NONE1.2509-FEB-04QUANTITY_SOLD1NONE1109-FEB-04TIME_ID1460NONE1.00068493209-FEB-047rowsselected.,54/78,DBMS_STATS包-直方图数据查看,创建基于频率的直方图最后查询结果返回行数：36769,createtablegenderasselectMgenderfromdba_objects;-insertintogenderselectFfromdba_objectswhererownumuser,tabname=GENDER,ESTIMATE_PERCENT=100,method_opt=FORALLCOLUMNSSIZE10);-selectnum_rowsfromall_tableswhereowner=userandtable_name=GENDER,55/78,DBMS_STATS包-直方图数据查看,查询数据：,SELECTcolumn_name,ENDPOINT_NUMBER,endpoint_value,ENDPOINT_ACTUAL_VALUE,SUBSTR(SYSTEM.hexstr(endpoint_value),1,30)FROMall_tab_histogramsaWHEREtable_name=GENDERANDowner=USER;*,ENDPOINT_NUMBERENDPOINT_VALUEENDPOINT_ACTUAL_VALUEActual_value93.64112E+35F%f367694.00458E+35M1#,56/78,DBMS_STATS包-直方图执行计划,setechoonsetautotracetraceonlyexplainSQLselect*fromgenderwheregender=F;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=19Card=9Bytes=18)10TABLEACCESS(FULL)OFGENDER(TABLE)(Cost=19Card=9Bytes=18)SQLselect*fromgenderwheregender=M;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=19Card=36760Bytes=73520)10TABLEACCESS(FULL)OFGENDER(TABLE)(Cost=19Card=36760Bytes=73520),57/78,DBMS_STATS包-10g直方图,10G的method_opt默认为“sizeauto”表示在Oracle数据库里直方图是需要的10G“sizeauto”在许多列创建直方图直方图搜集时间是关键,58/78,内容提要,SQL语句处理步骤回顾CBO模式统计数据搜集表统计有关概念与术语直方图统计数据搜集列的相关性问题优化器的决策问题,59/78,相关性问题,相关性例子-假设有3种图形三角形(triangles)均为蓝色(Blue)园形(circles)均为红色(Red)正方形()均为黑色(black)则Predicatesshape=CIRCLEandcolor=RED是正确的相关性但是优化器假定是没有相关性的,60/78,相关性问题例子,创建样例表T1：表存储3种颜色：,droptablet1;createtablet1(color_idnumber,colorvarchar2(10),shapevarchar2(10);insertintot1selectl1,casewhenl1100,method_opt=forallcolumnssize1);,61/78,相关性问题例子,相关性与基数(CorrelationCOLORSHAPECOUNT(*)-bluetriangle9redcircle90blackrectangle900explainplanforselectcount(*)fromt1wherecolor=blueandshape=triangle;select*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|16|3(0)|0:00:01|1|SORTAGGREGATE|1|16|*2|TABLEACCESSFULL|T1|111|1776|3(0)|0:00:01-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blueANDSHAPE=triangle)(cbo2,t1,estimate_percent=100,method_opt=forallcolumnssize1);,估计的直方图没有用上-颜色与形状没有关系,62/78,相关性问题例子,为什么没有用上相关性？-优化器假定两个谓词没有联系：第1列的谓词可选择性为color=blueis1/3.第1列的谓词可选择性为shape=triangleis1/3.两个谓词的可选择性为sel(p1)*sel(p2)=(1/3)*(1/3)=1/9理论基数估计变为：999*(1/9)=111,63/78,相关性与直方图,具有直方图情形：先搜集直方图统计数据：,altersessionsetoptimizer_dynamic_sampling=0;execdbms_stats.gather_table_stats(cbo2,t1,estimate_percent=100,method_opt=forallcolumnssize5);explainplanforselectcount(*)fromt1wherecolor=blueandshape=triangle;select*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time-|0|SELECTSTATEMENT|1|16|3(0)|0:00:01|1|SORTAGGREGATE|1|16|*2|TABLEACCESSFULL|T1|1|16|3(0)|00:00:01-PredicateInformation(identifiedbyoperationid):-2-filter(SHAPE=triangleANDCOLOR=blue),有了直方图，基数更为可信,64/78,相关性问题!,相关性问题？11g为止，相关性问题还存在，没有好的解决方法；列的统计需要手工进行调整；在10g版本,optimizer_dynamic_sampling级别为4可缓解这个问题发生；在11g版本，提供扩展搜集统计来解决相关性问题。参考：,65/78,相关性问题!,扩展统计数据的搜集：Dbms_stats包提供新函数来搜集扩展数据；下面代码对(color,shape)进行扩展搜集数据,从而捕获color和shape列的相关性问题：,SELECTdbms_stats.create_extended_stats(ownname=user,tabname=T1,extension=(color,shape)ASc1_c2_correlationFROMdual;C1_C2_CORRELATION-SYS_STUAOJW6_2K$IUXLR#$DK235BV,66/78,相关性问题!,扩展统计数据搜集的数据字典：DBA_TAB_COLS表有虚拟列：下面代码搜集虚拟列的直方图数据:,selectowner,table_name,column_name,hidden_column,virtual_columnfromdba_tab_colswheretable_name=T1andowner=CBO2orderbycolumn_id;OWNERTABLECOLUMN_NAMEHIDVIR-.CBO2T1SHAPENONOCBO2T1SYS_STUAOJW6_2K$IUXLR#$DK235BVYESYES,begindbms_stats.gather_Table_stats(user,T1,estimate_percent=100,method_opt=forallcolumnssize254);end;/,67/78,相关性问题-更好的统计数据,更好统计数据搜集带来的好处-准确：全表扫描返回基数也准确：,SQLexplainplanforselectcount(*)fromt1wherecolor=blackandshape=rectangle;Explained.SQLselect*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|16|4(0)|00:00:01|1|SORTAGGREGATE|1|16|*2|TABLEACCESSFULL|T1|900|14400|4(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blackANDSHAPE=rectangle),68/78,相关性问题-更好的统计数据,更好统计数据搜集带来的好处-准确(续)：全表扫描返回基数也准确：,explainplanforselectcount(*)fromt1wherecolor=blueandshape=triangle;select*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|16|4(0)|00:00:01|1|SORTAGGREGATE|1|16|*2|TABLEACCESSFULL|T1|9|144|4(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-filter(SHAPE=triangleANDCOLOR=blue),69/78,相关性问题-更好的统计数据,更好统计数据搜集带来的好处-准确(续)：组合的查询返回基数也准确：,SQLexplainplanforselectcount(*)fromt1wherecolor=blueandshape=rectangle;Explained.SQLselect*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|16|4(0)|00:00:01|1|SORTAGGREGATE|1|16|*2|TABLEACCESSFULL|T1|5|80|4(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-filter(COLOR=blueANDSHAPE=rectangle),70/78,内容提要,SQL语句处理步骤回顾CBO模式统计数据搜集表统计有关概念与术语直方图统计数据搜集列的相关性问题优化器的决策问题,71/78,优化器决策问题-1.绑定变量,绑定变量窥测(BindPeeking)问题：BindPeeking:第一次硬解析,如果收集了直方图,并且使用了绑定变量或者设置了Cursor_Sharing这个变量,这时BindPeeking就会启动_optim_peek_user_binds参数控制执行计划行为，默认为TRUECursor_Sharing参数：Exact书写完全一致Similar非绑定变量自动转为绑定变量还会有peeking,where条件中没有柱状图就会peeking否则认为SQL不安全Force不理会柱状图直接共享,72/78,优化器决策问题-1.绑定变量,绑定变量窥测例子：SQL语句使用绑定变量,variablev_colorvarchar2(12)exec:v_color:=blue;selectcount(*)fromt1wherecolor=:v_color;select*fromtable(dbms_xplan.display_cursor);SQL_ID209g46tpf1gnq,childnumber0-selectcount(*)fromt1wherecolor=:v_colorPlanhashvalue:2432955788-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|1|SORTAGGREGATE|1|*2|INDEXRANGESCAN|T1_I1|9|-PredicateInformation-2-access(COLOR=:V_COLOR),exec:v_color:=black;selectcount(*)fromt1wherecolor=:v_color;select*fromtable(dbms_xplan.display_cursor);SQL_ID209g46tpf1gnq,childnumber0-selectcount(*)fromt1wherecolor=:v_colorPlanhashvalue:2432955788-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|1|SORTAGGREGATE|1|*2|INDEXRANGESCAN|T1_I1|9|-PredicateInformation-2-access(COLOR=:V_COLOR),73/78,优化器决策问题-1.绑定变量,调整共享光标：两个SQL语句具有相同的执行计划,variablev_colorvarchar2(10)exec:v_color:=blue;PL/SQLproceduresuccessfullycompleted.SQLselectdistinctshapefromt1wherecolor=:v_color;SHAPE-triangleselect*fromtable(dbms_xplan.display_cursor);SQL_IDd5d8tfnunpcca,childnumber0-selectdistinctshapefromt1wherecolor=:v_colorPlanhashvalue:3899492605-|Id|Operation|Name|Rows|-|0|SELECTSTATEMENT|1|HASHUNIQUE|3|2|TABLEACCESSBYINDEXROWID|T1|9|*3|INDEXRANGESCAN|T1_COLOR|9|-PredicateInformation(identifiedbyoperationid):-3-access(COLOR=:V_COLOR),variablev_colorvarchar2(10)exec:v_color:=black;PL/SQLproceduresuccessfullycompleted.SQLselectdistin