SQL整体优化和调整.docVIP

SQL整体优化和调整学习笔记 1．下图是SQL整体处理过程,SQL的响应时间，主要和系统的db block gets/consistent gets/physical read/sort(memory)/sort(disk)相关。 关于OPTIMIZER_MODE(initSID.ora) 优化模式： Value Means CHOOSE A. 这是OPTIMIZER_MODE参数的缺省值,表示优化器会在CBO和RBO间选择.若(SQL所访问 对象的)统计信息有效,则选择CBO,否则将选RBO. B. 只要在SQL 所访问的表(一个或多个)的数据字典中,存在一个(或多个)统计,则优化器将会选择 CBO方法,并且以最佳吞吐为目标. C. 若数据字典中只含有部分统计信息,则仍然用CBO,但优化器需在对象(表、索引等)无统计的情 况下去猜测其统计数据,这会导致次优的执行计划(非最优). D. 若数据字典中未含SQL所访问表的任何统计,则优化器用RBO. ALL_ROWS Optimizer 将使用CBO(不管统计信息是否存在),并且将优化目标定为最佳吞吐(即使用最少的资 源执行完成整个语句). FIRST_ROWS_n Optimizer 将使用CBO(不管统计信息是否存在),并且将优化目标定为最快时间(best response time)返回结果的前N(N可以为1,10,100,1000)行. FIRST_ROWS Optimizer将综合使用成本和启发(heuristics)来找出最快返回结果第一行的执行计划. 注意: 在使用启发(heuristics)时,有可能会导致CBO 生成成本比不使用启发(heuristics)高得多 的执行计划.FIRST_ROWS这个参数主要是为了用于向后兼容和保持原计划的稳定性的. RULE Optimizer将使用RBO而不管统计信息是否存在. optimizer_mode 和 optimizer_goal 可以使用语句： alter session set optimizer_mode = FIRST_ROW_10来改变当前SESSION的优化模式。 另外设置 optimizer_goal 也和以上有相同效果，都设置optimizer_goal 将覆盖optimizer_mode参数设置。 也可以在语句中加HINTS来实现语句级别的优化模式改变：/*+All_ROWS(N)*/ ALL_ROWS/CHOOSE/RULE/FIRST_ROWS CBO的统计信息 可以用DBMS_STATS或ANALYZE（8I以前）对TABLE/INDEX进行完全或部分采样统计物理存储和数据统计属性。 对8I以后建议用DBMS_STATS进行分析，相比而言，DBMS_STAT可以并行进行统计，并可以对分区信息进行统计 对于以后ANALYZE 主要是进行VALIDATE校验对象结构有效性，使用LIST CHAINED ROWS来分析行链接，收集FREELIST BLOCK信息。 当SQL对象有以下特性时，整个SQL会用到CBO，即使optimizer_mode = rule : Partitioned tables and indexes Index-organized tables(IOT) Reverse key indexes Function-based indexes SAMPLE clause in a SELECT statement Parallel query and parallel DML Star transformations and star joins Extensible optimizer Query rewrite with materialized views Enterprise Manager progress meter Hash joins Bitmap indexes and bitmap join indexes Index skip scans 理解CBO CBO 在生成SQL 的计划时,会考虑对象的可访问路径和对象的统计信息,才会决定哪个执行计划是最优的.CBO 还会考虑hints(用户置于语句中的优化建议). 以下是CBO执行的步骤: A. 基于可用的访问路径和hints, Optimizer为SQL语句生成一个有效的计划集合. B. Optimizer基于SQL语句所访问对象存在数据字典中的统计(即对象的数据分布和存储特性),来评估每个计划的成本. 所谓成本(Cost)就是执行(某个SQL 的)某种计划估计需要使用到多少资源.Optimizer 评估执行所需用到的计算机资源(包括I/O,CPU和内存),根据结果来计算access paths和jo