sql优化经典讲解.ppt

SQL优化基础,索引,索引的类型 B*树索引 反向码索引 降序索引 位图索引 基于函数的索引,B*树索引,基于二叉树的,由分支块和叶子块组成.包括每个被索引列的值和行所对应的ROWIDCreate index indexname on tabename(columnname),反向索引,B*索引的一个分支反转索引码中每列的字节便于并发Create index indexname on tablename(column)reverse,ORACLE的内部表示,SQL select dump(1,16)from dual 2 union all select dump(2,16)from dual 3 union all select dump(3,16)from dual 4/DUMP(1,16)-Typ=2 Len=2:c1,2Typ=2 Len=2:c1,3Typ=2 Len=2:c1,4已用时间:00:00:00.00,反向的情况,SQL select dump(reverse(1),16)from dual 2 union all select dump(reverse(2),16)from dual 3 union all select dump(reverse(3),16)from dual;DUMP(REVERSE(1),1-Typ=2 Len=2:2,c1Typ=2 Len=2:3,c1Typ=2 Len=2:4,c1已用时间:00:00:00.00SQL,降序索引,索引中的储存方式由升序变成降序SQL select*from test t where id between 1 and 100 order by id desc,name asc;已选择100行。已用时间:00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT(ORDER BY)2 1 TABLE ACCESS(BY INDEX ROWID)OF TEST 3 2 INDEX(RANGE SCAN)OF PK_ID(UNIQUE),SQL create index i_desc on test(id desc,name asc);索引已创建。已用时间:00:00:00.01Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(BY INDEX ROWID)OF TEST 2 1 INDEX(RANGE SCAN)OF PK_ID(UNIQUE),位图索引,用于低cardinality列(即列的唯一值除以行数为一个很小的值,接近零)如”性别”不适用于并行只能在CBO下Create bitmap index indexname on tablename(column),SQL select*from testbitmap where sex=男;已选择5000行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(FULL)OF TESTBITMAP,位图索引的特点,比B*tree索引更节省空间建立速度快不能走RULE可存储NULL值,SQL create index bit_inx_sex on testbitmap(sex);索引已创建。SQL select*from testbitmap where sex=男;已选择5000行。Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(BY INDEX ROWID)OF TESTBITMAP 2 1 INDEX(RANGE SCAN)OF BIT_INX_SEX(NON-UNIQUE),函数索引,必须拥有QUERY_REWRITE权限必须使用基于成本的优化器必须设置以下两个参数:query_rewrite_enabled=true query_rewrite_integrity=trustedCreate index indexname on tabename(fun(column),SQL create index l_fun on test(upper(name);分析表略去SQL select*from test where upper(name)=1TEST;ID NAME-1 1test已用时间:00:00:00.04Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=2 Card=1 Bytes=11)1 0 TABLE ACCESS(BY INDEX ROWID)OF TEST(Cost=2 Card=1 Bytes=11)2 1 INDEX(RANGE SCAN)OF L_FUN(NON-UNIQUE)(Cost=1 Card=1),各种索引使用场合,B*tree 多用于oltp系统.cardinlity高的情况下反向索引 OPS环境下降序索引 带排序操作,位图索引 适用于低cardinality列.适合集中读取,不适合插入和修改,原因在于任何需要更新同一个位图索引条目的修改都将锁定整个位图，严重抑制了并发性,未用到索引的原因,类型不匹配对列使用了函数，而索引只是基于列的。使用索引实际会降低速度。很长时间没有分析表了，表的增长较快，这样CBO会作出错误的判断。结果集返回的比例过大Query_rewrite_enabled未设为true而导致函数索引不可用,索引的管理,DROP过多的索引监控索引 alter index monitoring usage;注:9205以下有BUG.重建索引 alter index IDX rebuild online迁移索引 alter index rebuild tablespace,常见的一些问题,使用NULL条件查询 方法UPDATE为N/A,然后查:如 id=N/ALike%a%:使用firstrows提示,Like%DURE如:where object_type like%DURE create index inxreserve on user_test(reverse(object_type)反向索引创建后改写为:where reverse(object_type)like ERUD%,理解SQL工具,执行计划,ORACLE_HOME/rdbms/admin/utlxplan.sqlCreate public synonym plan_table for sys.plan_table;,查看执行计划的几种方式,1.explain plan forselect*.select*from table(dbms_xplan.display);2.Explain plan set statement_id=id into plan_table for sql statement3.sqlplus:set autotrace on;4.sql_trace and tkprof,Trace文件解释,Count CpuElapsedDiskQueryCurrentRows,阅读执行计划,首先读取缩进最深的语句如果两上语句的缩进程度相同,则上面的先,Oracle优化器,Oracle优化器的类型,Cost Based Optimizer(简称CBO)基于统计信息的优化Rule Based Optimizer(简称RBO)基于数据字典的优化，在以后的版本中将不支持,RBO,根据数据字典查询有无可用的索引，如果有则使用，否则不使用不同的访问方法有预定好的优先级，选择优先级高的执行方法,RBO中访问数据,Rowid-Cluster Join(SR)-Hash Cluster Key(SR)-UK或PK(SR)-Cluster Join-Hash Cluster Key-Index Cluster Key-Composite Index-Single Column Index-Bound Range Index Scan-Unbound Range Index Scan-Sort Merge Join-Max or Min on Indexed Column-Order on Indexed Column-Full Table Scan,RBO的特点,总是使用索引总是使用驱动表只有在不可避免的避免的情况才使用全表扫描任何索引都可以(但并非很好),例子,SQL select count(*)from user_test;COUNT(*)-30904已用时间:00:00:00.01SQL create index id_owner on user_test(owner);索引已创建。已用时间:00:00:00.09,SQL select*from user_test where owner=SYS;已选择13931行。已用时间:00:00:00.07Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(BY INDEX ROWID)OF USER_TEST 2 1 INDEX(RANGE SCAN)OF ID_OWNER(NON-UNIQUE)Statistics-0 recursive calls 0 db block gets 2060 consistent gets 29 physical reads 0 redo size 995554 bytes sent via SQL*Net to client 10711 bytes received via SQL*Net from client 930 SQL*Net roundtrips to/from client 0 sorts(memory)0 sorts(disk),SQL drop index id_owner;索引已丢弃。已用时间:00:00:00.00SQL select*from user_test where owner=SYS 2/已选择13931行。已用时间:00:00:00.07Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS(FULL)OF USER_TEST,CBO,需要收集统计信息表有多少行，占用多少数据块列有多少个Null值、不同值列的最大值和最小值，及值的分布情况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。,CBO的新功能,Partition Table 和 Partition IndexIndex Organized TableReserve Key IndexFunction IndexBitmap Index 和 Bitmap Join IndexIndex Skip ScanParallel Query 和 Parallel DMLHash Join基于实体化视图的Query Rewrite,使用CBO,新的应用应当基于CBO开发用Analyze dbms_stats收集信息在语句中用hint指定rule choose first_rows all_rows在session中用alter session指定optimizer_mode参数在参数文件中指定optimizer_mode参数,CBO几个概念,Selective结果记录的比例Histograms列值的分布情况Cardinality结果记录数Cost执行成本,CBO:Selective,指结果记录的比例用于决定使用索引访问还是使用全表扫描例子：表T的COL1字段上有10个不同值，当执行select*from T where col1=?时，这个查询的selective=1/10*100=10%,CBO:Histograms,用于描述列值的分布情况在分布不平均的例上，需要用Histograms来记录列值的分布情况例子:在表T的COL1字段上，有100个值，它分的分布如下 10 20 5 5 40 10 10,CBO:Cardinality,CBO对查询将返回的记录数的一个估计，在最简单的情况下：Cardinality=Table rows*Selective,CBO:Cost,CBO根据所收集的统计信息或猜测信息给某一个SQL语句算出的执行成本具体计算方法很复杂没有统计信息或过时的统计信息，会导至CBO产生和使用错误的执行方法CBO还不是十分完善，可以使用Hint影响CBO选择执行方法,全表扫描和索引扫描,索引扫描不一定是最快的(如小表)CBO中经常会选择全表扫描(如小表)当Selective 15%时，选择索引扫描比较有利，否则使用全表扫描更好,CBO中的访问方法,Full Table ScansRowid ScansIndex ScansCluster ScansHash ScansSample Table Scans,CBO中Index访问,Index Unique ScanIndex Range Scan DescendingIndex Skip ScanFull ScanFast Full Index ScanIndex JoinBitmap Join,CBO中的JOIN方法,NEST LOOPMERGE JOINHASH JOIN,几个参数对CBO的影响,OPTIMIZER_INDEX_CACHING 这个初始化参数代表一个百分比,取值范围在0到99之间.缺省值是0，代表当CBO使用索引访问数据时，在内存中发现数据的比率是0%，这意味着通过索引访问数据将需要产生物理读取，代价昂贵。如果使用缺省设置，Oracle评估成本的时候，很多时候就会错误的选择全表扫描。,OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间.该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等于全表扫描。,收集统计信息,定时收集在数据大量变更后收集使用Analyze命信收集或dbms_stats包收集在optimizer_mode=choose时，收集信息后会采用CBO进行优化，在dblink应用较多时，需要注意。,一个例子,SQL SELECT*FROM USER_TEST WHERE OBJECT_TYPE=PROCEDURE;已选择24160行。已用时间:00:00:46.00Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=59 Card=9157 Bytes=8 51601)1 0 TABLE ACCESS(BY INDEX ROWID)OF USER_TEST(Cost=59 Card=9157 Bytes=851601)2 1 INDEX(SKIP SCAN)OF IDX(NON-UNIQUE)(Cost=29 Card=91 57),Statistics-0 recursive calls 0 db block gets 42204 consistent gets 8659 physical reads 120 redo size 8034786 bytes sent via SQL*Net to client 91550 bytes received via SQL*Net from client 8279 SQL*Net roundtrips to/from client 0 sorts(memory)0 sorts(disk)124160 rows processed,对表和索引作分析,SQL exec dbms_stats.gather_table_stats(TEST,USER_TEST);PL/SQL 过程已成功完成。已用时间:00:01:18.05SQL EXEC dbms_stats.gather_index_stats(TEST,IDX);PL/SQL 过程已成功完成。已用时间:00:00:46.07,SQL SELECT*FROM USER_TEST WHERE OBJECT_TYPE=PROCEDURE;已选择3008行。已用时间:00:00:00.06Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE(Cost=31 Card=75553 Bytes=6950876)1 0 TABLE ACCESS(BY INDEX ROWID)OF USER_TEST(Cost=31 Card=75553 Bytes=6950876)2 1 INDEX(RANGE SCAN)OF IDX(NON-UNIQUE)(Cost=1 Card=75 553),注意事项,建议使用DBMS_STATS 包来代替ANALYZE 语句收集统计信息。DBMS_STATS 包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用其他方式优化收集操作。收集和基于成本优化器无关的信息必须用ANALYZE 而不是DBMS_STATS，比如.使用VALIDATE 或LIST CHAINED ROWS 子句。收集freelist 块的信息。,DBMS_STATS,Gather_database_stats收集数据库下所有对象的统计信息Gather_schema_stats收集某一用户下所有对象的统计信息Gather_table_stats收集某一个表或分区的统计信息Gather_index_stats收集某一个索引或分区的统计信息,gather_database_stats,estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_schema_stats,ownname=用户名estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_table_stats,ownname=用户名tabname=表名partname=分区名estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_index_stats,ownname=用户名tabname=表名partname=分区名estimate_percent=比例比例越大，收集越耗时degree=并行度默认为表的并行度,查看表的统计信息,USER|ALL|DBA _TABLESnum_rows/blocks/avg_row_len/last_analyzed,查看索引的统计信息,USER|ALL|DBA _INDEXESNUM_ROWS/DISTINCT/LEAF_BLOCKS/CF/ALFBKEYCF=一个索引叶块对应的数据块的数量，越小表示一索引越有效ALFBKEY=每一个值占据的叶块的数量,查看列的统计信息,USER|ALL|DBA _tab_col_statisticsnum_distinct/num_nulls/num_buckets/densitynum_buckets=histograms中组的数量Density=1/num_distinct,查看histograms的信息,user|all|dba_histogramsuser|all|dba_part_histogramsuser|all|dba_subpart_histogramsuser|all|dba_tab_col_statistics不适合在分布均匀的列或在where中用bind variable来查询的列在收集时需要在method_opt中指定size值,CBO使用实列,使用Function Index选择一个例值分布不平均的表学会收集统计信息使用dbms_job来定时收集统计信息查看统计信息,Optimizer Hints,为什么会有Hints,因为CBO的功能还不十分强大尽量让优化器选择，除非你很有自信例子：在使用dblink的环境中在使用bind variable时,Hint:优化器的选择,All_rowsFirst_rows(n)n=1,10,100,1000ChooseRule,Hint:选择访问方法,Full(表名)Index(表名 索引)Index_asc(表名 索引)Index_desc(表名 索引)Index_combine(表名 bid1 bid2)Index_join(表名 ind1 ind2)Index_ffs(表名 索引)No_index(表名 索引),Hint:控制SQL转换,Use_concat使用union all来替换or条件No_expand不使用union all来替换or条件Rewrite/norewrite启用和禁用Query Rewrite技术Merge(视图)/no_merge(视图)是否合并视图,Hint:控制Join类型,Use_nl(表名)Use_merge(表1 表2)Use_hash(表1 表2)Hash_aj/nl_aj/merge_ajHash_sj/nl_sj/merge_sj,Hint:控制并行,Parallel(表名 并行度)Noparallel(表名),Hint:其他,AppendCache,创建索引,索引能提高速度的关键就是索引所占的空间要比表小得多注意索引的大小，有一些表可以建成索引组织表索引的列不要太多，要选择一些selective比较低的列建B-tree索引，选择selective高的列建bitmap索引(在更新比较多的表不不要建bitmap索引)将selective较低的列放在前面在更新不多的表上建索引时，可以考虑用compress选择，以节约索引的空间,创建表,普通表索引组织表大部分字段是Primary Key的表分区表一般记录数在50万以上的可以考虑创建分区表,其他问题,在表空间使用时，最好用Local表空间，否则应当设置pctincrease为0以减少表空间的碎片不同数据量级的表应当有不同的initial和next或Uniform size设置许多表有maxextents的限值，需要注意unable to allocate extends的错误信息若使用CBO，应在晚上定时收集statistics,SQL调整过程,确定具有高影响力的SQL,V$SQLAREA executions disk_reads rows_processed buffer_gets sort,定位使用频繁的SQL,Statspack前10,抽取和解释语句,查看执行计划,调整SQL,添加索引添加提示重写SQLPL/SQL,总结,认真思考理解CBO的部分原理查出目前数据库中各个索引与表的大小的比值，考虑索引存在的必须性，或索引列的选择是否正确了解和合理使用Optimizer Hint使用CBO，在CBO下调整优化SQL用STATPACK找出一些reads比较大的SQL并进行调整,