使用EXPLAN和STOREDOUTLINE.ppt

查找和修补有错误的查询时在很大程度上要借助于合适的工具。不同的场合有不同的工具，我们下面就来研究这些工具。1、Oracle的SQLTRACE实用程序这个实用程序在一个跟踪文件中记录了数据库的所有活动，我们需要使用tkprof实用程序转换成可读取的格式。,使用SQLtrace的一个例子,1、下面的三个参数很重要,允许在系统中执行跟踪,2、对一个会话启用跟踪，这样这个会话就会被跟踪,selectspid,s.sid,s.serial#,p.username,p.programfromv$processp,v$sessionswherep.addr=s.paddrands.sid=(selectsidfromv$mystatwhererownum=1)/当前会话的sid和serial#,如果这个参数是false，那么每一个会话都会产生一个trace文件，然后所有的操作都会被trace，这样将会产生大量的trace文件，建议不要使用这个选项。,如何定位产生的trace文件呢？1、在当前会话中，运行下面的语句，得出当前的spidselectspid,s.sid,s.serial#,p.username,p.programfromv$processp,v$sessionswherep.addr=s.paddrands.sid=(selectsidfromv$mystatwhererownum=1)/2、看一下文件的生成日期3、在会话中执行一个特殊一些的命令，例如SelectMytestfromdual;然后使用grep等在文件堆中进行筛选。,TKPROF程序将SQLTRACE所生成的trace文件转换成可阅读的格式。,这个实用工具的使用方法,1、tracefile：包含了SQL_TRACE统计信息的TRACE文件名2、Output_file：输出文件名字3、print=number：包含在输出结果中的语句数量，如果没有列出来，那么默认所有语句都列出来4、explain=username/passwd：在trace文件里对用户的sql语句运行explainplan，这个选项可以创建自己的plan_table，这样用户就需要创建表和创建表所需要的空间的特权，在tkprof运行结束以后会删除这个表，确保使用运行查询的用户，从而确保由正确的用户进行解释5、insert=filename：这个选项生成脚本来创建表并为每个所跟踪的SQL语句存储TRACE文件统计6、record=filename：这个选项将生成一个保存用户所有SQL语句的文件7、sys=ye/no：这个选项可以在输出结果中不显示递归SQL语句。递归SQL就是一些为了完成用户操作所进行的一些对数据字典的访问和操作。例如为了执行插入操作，首先需要一个递归操作：分配一个extent给表所在的段。,8、sort=parameter有大量的排序选项可用：FCHCPU（获取的CPU时间）、FCHDSK（获取的磁盘读取）、FCHCU和FCHQRY（获取的内存读取）、FCHROW（取出的行数）、EXEDSK（执行期间的磁盘读取数）、EXECU和EXEQRY（执行期间的内存读取数）、EXEROW（执行时处理的行数）、EXECPU（执行的CPU时间）、PRSCPU（分析CPU）、PRSCNT（分析时间）9、waits=yes/no任何等待事件的记录概要10、aggregate=yes/no是否组合相同SQL文本的多个用户11、table=schema.table在将执行计划写入到输出文件之前临时存放的位置,一个完整的小例子,按照CPU时间分析了两个最消耗CPU时间的SQL语句。包括执行计划。,忽略掉所有的递归语句,生成一个脚本，里面包括createtable语句和insert语句。将跟踪记录插入到这个表中。但是这个脚本没有执行，我们可以另外打开一个窗口，然后运行这个脚本。,将整个会话中的SQL语句都抓出来。,显示4次磁盘读取（物理读取）共5491次（query+current），内存读取量是5491-4=5487这个查询中，磁盘读取不是大问题，因为几乎所有的读取都发生在内存中。,通过执行这个脚本，在这个用户下面建立了plan_table。,TRACE输出部分1、SQL语句2、统计3、信息4、EXPLAINPLAN（执行计划）,1、SQL语句这个语句和执行的SQL语句完全相同。这个是输出的第一部分。,2、统计部分该部分包含了对这个SQL语句以及为了满足该语句而生成的所有递归SQL语句的统计。该部分有8个列组成。,第一列就是对数据库的调用类型分析（parse）：硬分析或者软分析执行（execute）：实际执行执行计划取出（fetch）：获取结果数据后面7列是对上面三个选项的统计汇总。,1、count：这个类型的调用次数2、cpu：这条语句所有这种类型调用的总CPU时间，如果没有设置初始化参数timed_statistics设为true，那么cpu和elapsed都为03、Elapsed：这个调用的总消耗时间4、disk：为了满足这次调用而从磁盘检索的数据块数目5、Query：进行这个类型调用时从内存检索的数据缓冲区数目主要指的是select6、current：进行这个类型调用时从内存检索的数据缓冲区数目主要指的update、insert、delete7、rows：这条语句处理的总行数，select语句所处理的行数都体现在fetch统计中，insert、update、delete都出现在execute行中,信息部分包含了有关分析和执行调用中丢失的库缓存的数量信息。如果这个丢失率很高，说明共享池的大小出现了问题，应当对库缓存的命中率和重载率进行检查。优化器模式。分析这条语句使用的用户名字（执行这条语句的用户名）。,不同的执行步骤中所涉及的行的数目。,执行计划这是最有用的部分第一列是执行计划中每一行语句所处理的行数，在这里可以看到语句的执行情况。如果这里面处理的行数较多，那么就需要引起注意，看是否使用了全表扫描或者走了较差的索引。,查看TKPROF输出时可发现的问题分析数字太大应该增大sharedpool磁盘读取量太高没有使用索引或根本没有索引Query或current太高（内存）索引位于低基数的列上。删除或者限制索引的使用，使用直方图、使用位图索引等可以解决这类问题。表的连接顺序出现问题也会出现这个问题分析所需要的时间太多可能是opencursors的数量有问题EXPLANPLAN里某一行语句要处理的行数相对其他行语句而言太多索引可能有问题、全表扫描在分析期间库缓存的Misses值大于1这表明需要重载这条语句，可以增加sharedpool或者共享SQL语句,深入探讨TKPROF输出通过比较TKPROF输出和实际对象的物理特征，我们可以了解到oracle的工作原理。,已分配的块和已使用的块,没有任何信息。,分析以后就有数据了。,总共是1404个读取，其中磁盘就发生了281个读取。,注意：这个trace文件中，将两个相同的语句进行合并显示。,整个表都在内存中，多个5个数据块。,注意：全表扫描是Oracle控制从内存中首先清除的内容之一（运行全表扫描以后他们将归为近期最少使用项LRU），这是因为他们效率非常低，通常还占用了大量的内存。,DBMS_MONITOR(10g新特性)在具有连接池或共享服务器的多层环境中，一个会话可能跨越多个进程，甚至跨越多个实例。DBMS_MONITOR是在oracle10g中引入的内置的程序包，通过该程序包可以跟踪从客户机到中间层、再到后端数据库的任何用户会话，从而可以较为容易的标识创建大量工作量的特定用户。要使用这个程序包需要具有DBA权限。,数据库服务器,中间应用服务器,客户机,端到端的应用程序跟踪可以基于如下：（到底让我跟踪谁啊？）会话：基于会话ID和序列号客户端标识符：允许跨越多个会话设置跟踪，基于登录ID指定终端用户。使用DBMS_SESSION.SET_IDENTIFIER过程设置该值实例：基于实例名指定给定的实例服务名：指定一组相关的应用程序，使用DBMS_SERVICE.CREATE_SERVICE过程设置该值模块名：开发人员在其应用程序代码中使用DBMS_APPLICATION_INFO.SET_MODULE过程设置该值操作名：开发人员在其应用程序中使用DBMS_APPLICATION_INFO.SET_ACTION过程设置该值后面的三个跟踪选项是关联的，不可以在没有指定上面两个的情况下，指定最后一个。但是可以指定第一个、不指定后面两个。也可以指定前面两个，但是不指定最后面一个。,1、基于会话ID和序列号设置跟踪,在一个会话中执行左面的语句启用跟踪。,在另外一个会话中，执行语句，执行的语句被跟踪。,跟踪当前会话。,基于客户端标识符设置跟踪1、验证客户端标识符,发现没有客户端标识符。,一个会话连接上来以后，设置自己的客户端ID，然后开始执行自己的SQL语句。,再次查询就可以查找到这个会话。,前面演示了根据一个会话的client_id来进行跟踪。,根据服务名、模块名、操作名来进行跟踪为了使用操作名，必须有对应的服务名、模块名为了使用模块名，必须有对应的服务名,我们首先来看一下服务名的概念。,数据库默认有一个服务名，这个服务名和数据库的名字经常一样。,服务名字注册到listener中去。,SQLPLUSSYSTEM/SYSORCL,一个数据库,两个服务名：两个应用，分别连接到两个服务上,应用服务器1,应用服务器2,一个数据库另个服务名。,两个服务都注册到listener中。,不同的应用服务器通过不同的服务名连接到了数据库中。,凡是通过orcl1这个服务连接上来的所有的会话都被跟踪。,一共两个会话，那么就产生了两个跟踪文件。,设置模块名和操作名,这一点和设置client_id非常的相似。,对于模块名和操作名，需要应用程序专门设置。,通过这个视图可以看见有哪些trace目前处于打开状态。,使用trcsess将多个文件保存到一个文件中（10g的新特性）使用这个新特性，可以有选择地从多个跟踪文件中提取跟踪数据，并且将这些跟踪数据基于会话ID或者模块名等标准保存到一个跟踪文件中。这个命令在连接池、共享服务器配置中特别有用。使用这个命令，可以获得属于某个用户会话的统一的跟踪信息。根据如下的一些标准创建统一跟踪文件1、会话ID2、客户端ID3、服务名4、模块名5、操作名,以空格分开，如果没有指定，那么默认使用当前目录下面的所有文件，文件名中可以使用通配符*。,根据某个标准，在指定的trc文件中进行搜寻，将符合标准条件的所有的数据提取出来，放置到一个文件中。,当前目录下面所有的以orcl1服务名连接到数据库的跟踪信息，都被提取出来，放置到service_id.txt中。以某个标准从trc文件中提取数据。,数据提取出来以后，使用tkprof进行格式化输出。,开发人员可以使用EXPLAINPLAN命令来查看orale优化器用来执行SQL语句的查询执行计划。这个命令对提高SQL语句的性能很有帮助，因为他不用真正执行SQL语句。他只是列出了所要使用的计划，并把这个计划插入到了一个表中。在使用这个命令之前，需要执行utlxplan.sql文件，创建相应的表（PLAN_TABLE）EXPLAINPLAN和TRACE的区别1、一个是真正的执行、一个不需要真正的执行2、如果一个SQL语句执行时间特别的长，就需要使用explainplan,TRACE要跟踪一个运行4小时的查询，也就需要花费4个小时的跟踪设置ora参数文件创建plan_table表运行查询执行语句，填充plan_table运行tkprof除输出explainplan以外，还显示磁盘和内存读取数,EXPLAINPLAN对一个运行4小时的查询执行EXPLAINPLAN，并得出结果，所需时间不到1分钟创建plan_table表解释查询填充plan_table查询plan_table输出显示EXPLAINPLAN,如何使用EXPLAIN1、运行utlxplan.sql脚本，创建plan_table,说明这个用户已经执行过这个脚本，不需要在执行，如果这个用户下面没有这个表，那么这个用户需要执行这个脚本。,2、使用explainfor执行语句,这个可以没有，有这个的目的就是如果有多个开发人员在使用这个表，互相之间在访问的时候，不会发生混淆。,Oracle已经提供了脚本供我们直接使用。,后者对并行查询有详细的解释。,selectlpad(,2*(level-1)|operation|options|object_name|decode(id,0,Cost=|position)QueryPlanfromplan_tablestartwithid=0andstatement_id=Mysqlconnectbypriorid=parent_idandstatement_id=Mysql/,EXPLAINPLAN从上至下读取和从下至上读取具体使用哪种方式读取取决于从PLAN_TABLE表中检索信息的查询编写方式。因此两个方式都对。selectLPad(,2*(Level-1)|Level|.|Nvl(Position,0)|Operation|Options|Object_Name|Object_Type|Decode(id,0,Statement_Id|Cost=|Position)|cost|Object_NodeQueryPlanfromplan_tablestartwithid=0Andstatement_id=Mysqlconnectbypriorid=parent_idandstatement_id=Mysql/,启用AUTOTRACE,.,SETAUTOTRACEON的作用如下：1、执行SELECT语句2、自动显示执行计划和一些统计信息,打开autotrace：执行结果、执行计划、统计信息不显示查询的输出结果仅显示执行计划仅显示统计信息,在分区模式下面使用的EXPLAINPLAN建立一个分区表createtabledept1(deptnonumber(2),dept_namevarchar2(30)partitionbyrange(deptno)(partitiond1valueslessthan(10),partitiond2valueslessthan(20),partitiond3valueslessthan(maxvalue)/insertintodept1values(1,DEPT1);insertintodept1values(7,DEPT7);insertintodept1values(10,DEPT10);insertintodept1values(15,DEPT15);insertintodept1values(22,DEPT22);createindexdept_indexondept1(deptno)local(partitiond1,partitiond2,partitiond3)/,强制对两个分区进行全表扫描。,实际对全表所有分区进行了扫描。,selectoperation,options,id,object_name,partition_startstart_p,partition_stopstopfromplan_table/,无论是索引还是表，走的都是一个分区。这个语句的访问实现了分区的优势。我们通过这个start和stop可以非常清楚的知道分区的意义是否实现。这一个点很重要。,在不使用trace的情况下查找大的硬盘或内存的读取量。这就需要使用v$sqlarea,selectdisk_reads,sql_textfromv$sqlareawheredisk_reads1000orderbydisk_reads;,查找磁盘读取量大于1000的SQL语句。,查找内存读取率大于1000的SQL语句。,selectbuffer_gets,sql_textfromv$sqlareawherebuffer_gets10000orderbybuffer_gets/,我们根据自己的系统的情况进行分析，确认一个边界值。然后寻找所有超过这个边界值语句。按照降序进行排序输出。对于有问题的SQL语句进行分析的优化。,selectdisk_reads,sql_textfromv$sqlareawheredisk_reads10000orderbydisk_readsdesc;DISK_READSSQL_TEXT12987selectorder#,columns,typesfromorderswheresubstr(orderid,1,2)=:111131selectcustid,cityfromcustomerwherecity=CHICAGO,这个输出结果表明有两个有问题的查询引起了大量磁盘读取。第一个就是使用SUBSTR函数使ORDERID函数上的索引受到限制；第二个显示了CITY上缺少索引。,selectbuffer_gets,sql_textfromv$sqlareawherebuffer_gets200000orderbybuffer_getsdesc;BUFFER_GETSSQL_TEXT300219selectorder#,cust_no,fromorderswheredivision=1,输出结果表明有一个查询造成了过量的内存读取(300219个数据块读入内存中)。在DIVISION上的索引有一个低的基数1，因此在此表中只有一个分区。此处发生的情况时读取整个索引，然后读取整个表。而且为了提高性能，必须对这个语句进行限制(如果不会添加额外的分区，则应该永久删除这个语句)。,使用dbms_xplan在oracle9i以后，oracle提供了查看explainplan较为容易的方法。使用dbms_xplan注意的事项1、该程序包自动查询plan_table中的最后一个执行计划2、该程序包使用table（）函数和另外一个管道函数3、文本截取操作可能存在问题,另外一种explainplan输出方法：构建树结构这是一种更加逻辑化的执行计划表示方法父子关系：先执行子、再执行父，一种先后的顺序关系,selectLPAD(,2*(LEVEL-1)|operationOPERATION,optionsOPTIONS,DECODE(TO_CHAR(id),0,COST=|NVL(TO_CHAR(position),n/a),object_name)OBJECTNAME,id|-|NVL(parent_id,0)|-|NVL(position,0)ORDER,SUBSTR(optimizer,1,6)OPTfromplan_tablestartwithid=0andstatement_id=Msqlconnectbypriorid=parent_idandstatement_id=Msql/,ORDER这个行的ID这个行的父ID父ID相同的子ID的顺序,画出一棵树1、首先找出父步骤是0的子步骤，然后将其画到树中2、，然后再找父步骤是1的子步骤，将其画到树中，依次类推将树画出来,OPT优化器的当前模式,TABLEACCESSBYINDEXROWID,INDEXUNIQUESCAN,解释执行计划：执行顺序、每一步做了什么1、执行顺序由这些步骤的父子关系确定2、先执行子步骤、再执行父步骤3、如果一个父步骤有很多子步骤，子步骤就按照步骤位置（执行计划中order列的第三列）执行。4、执行树从左到右，从下到上,记住：父步骤要使用子步骤的结果,selecta.cust_last_name,a.cust_first_name,a.cust_mid_init,b.order_desc,b.order_create_dtfromorder_hdrb,customerawherecust_phone=:host1andb.cust_no=a.cust_noandb.order_status=OPEN;,OPERATIONOPTIONSOBJECTNAMEORDEROPTSELECTSTATEMENTCOST=n/a0-0-0ALL_RONESTEDLOOPS1-0-1TABLEACCESSBYROWIDORDER_HDR2-1-1INDEXRANGESCANIX_ORDER_STATUS3-2-1TABLEACCESSBYROWIDCUSTOMER4-1-2INDEXUNIQUESCANPK_CUSTOMER5-4-1,这个语句有5个步骤1、首先执行步骤3，这是一个范围扫描2、步骤3向步骤2返回了0、1或许多的ROWID，根据返回的每个ROWID，步骤2会访问order_hdr表，获得需要的数据，步骤2会把数据返回给步骤13、对于步骤2所检索到的每一行数据，步骤1会把CUST_NO传递给步骤54、步骤5使用客户号来执行一次唯一扫描并获得ROWID，这个ROWID从步骤5返回给步骤4，如果没有找到任何数据，步骤4会告诉步骤1，放弃这行数据，如果找到一个ROWID，步骤4通过ROWID访问这个表，并检索数据。得到数据以后，如果电话号正确，将数据返回给步骤15、将步骤2的结果和步骤4的结果行进行合并，然后返回给用户，如果电话号码不正确，步骤4将不返回任何数据，步骤1将丢弃该行。,根据解析树进行性能的检查,这显然是一个连接查询。驱动表使用的是ORDER_HDR，我们希望驱动表是小表。右边的数据可能会比左边的数据要少，因此希望改进连接顺序。,selecta.cust_last_name,a.cust_first_name,a.cust_mid_init,b.order_desc,b.order_create_dtfromorder_hdrb,customerawherecust_phone=:host1andb.cust_no=a.cust_noandb.order_status=OPEN;,select/*+ORDERED*/a.cust_last_name,a.cust_first_name,a.cust_mid_init,b.order_desc,b.order_create_dtfromcustomera,order_hdrbwherecust_phone=:host1andb.cust_no=a.cust_noandb.order_status=OPEN;,OPERATIONOPTIONSOBJECTNAMEORDEROPTSELECTSTATEMENTCOST=n/a0-0-0ALL_RONESTEDLOOPS1-0-1TABLEACCESSBYROWIDCUSTOMER2-1-1INDEXRANGESCANIX_CUST_PHONE3-2-1TABLEACCESSBYROWIDORDER_HDR4-1-2AND-EQUAL5-4-1INDEXRANGESCANIX_ORDER_CUST6-5-1INDEXRANGESCANIX_ORDER_STATUS7-5-2,OPERATIONOPTIONSOBJECTNAMEORDEROPTSELECTSTATEMENTCOST=n/a0-0-0ALL_RONESTEDLOOPS1-0-1TABLEACCESSBYROWIDCUSTOMER2-1-1INDEXRANGESCANIX_CUST_PHONE3-2-1TABLEACCESSBYROWIDORDER_HDR4-1-2AND-EQUAL5-4-1INDEXRANGESCANIX_ORDER_CUST6-5-1INDEXRANGESCANIX_ORDER_STATUS7-5-2,NESTEDLOOP（1）,TABLEACCESSBYROWIDCUSTOMER（2）,INDEXRANGESCANIX_CUST_PHONE（3）,TABLEACCESSBYROWIDORDER_HDR（4）,AND-EQUAL（5）,INDEXRANGESCANIX_ORDER_CUST（6）,INDEXRANGESCANIX_ORDER_STATUS（7）,检查部分调整的查询的性能我们对前面进行的查询执行计划进行分析,NESTEDLOOP（1）,TABLEACCESSBYROWIDCUSTOMER（2）,INDEXRANGESCANIX_CUST_PHONE（3）,TABLEACCESSBYROWIDORDER_HDR（4）,AND-EQUAL（5）,INDEXRANGESCANIX_ORDER_CUST（6）,INDEXRANGESCANIX_ORDER_STATUS（7）,使用这个返回的数据作为驱动表还是比较合适的，因为有可能只返回一行数据，是一个小表。,是否合适？因为根据订单状态返回太多的行，因此使用索引不是一个好的选择。,进一步调整select/*+ORDERED*/a.cust_last_name,a.cust_first_name,a.cust_mid_init,b.order_desc,b.order_create_dtfromcustomera,order_hdrbwherecust_phone=:host1andb.cust_no=a.cust_noandb.order_status|=OPEN;限制索引的使用,OPERATIONOPTIONSOBJECTNAMEORDEROPTSELECTSTATEMENTCOST=n/a0-0-0RULENESTEDLOOPS1-0-1TABLEACCESSBYROWIDCUSTOMER2-1-1INDEXUNIQUESCANPK_CUSTOMER3-2-1TABLEACCESSBYROWIDORDER_HDR4-1-2INDEXRANGESCANIX_ORDER_STATUS5-4-1,NESTEDLOOPS（1）,TABLEACCESSBYROWIDCUSTOMER（2）,TABLEACCESSBYROWIDORDER_HDR（4）,INDEXUNIQUESCANPK_CUSTOMER（3）,INDEXRANGESCANIX_ORDER_STATUS（5）,建立一个组合索引（订单号和订单状态）,在开发产品中利用TRACE/EXPLAIN发现有问题的查询上面的方法最大的问题就是：不能跟踪表单（OracleForms）和报告因为上面的方法需要将代码复制出来进行执行，然后得到执行计划等。对于Oracle的表单系统，有自己的一套得到执行计划的方法，这个属于oracleforms的范畴（有具体的菜单可以使用）。当然，DBMS_MONITOR的功能非常强大，可以跟踪表单和报告。,在explainplan中可以给此次跟踪起一个名字,执行explainplan时的时间,在这步操作所执行的内部操作名称，第一行是下面的四个值之一：DELETE、INSERT、UPDATE、SELECT,和OPERATION对应的详细信息,表或索引的名字,当前优化器的模式每一步的数字,当前步骤下一步的执行ID,拥有相同的父ID的子ID的顺序,CBO估算出来的成本，只是一个权重值,CBO估算出来的这个操作的访问行数CBO估算出来的这个操作访问的字节,BYTES，这个值很重要，如果使用索引，但是访问的数据量特别大，可能使用全表扫描更好。字节数还可以帮助我们判断哪个表应该在查询时先被访问（驱动表）。注意COST和BYTES都是估算出来的。,Oracle支持的一些有用的程序包可以使用DBMS_SYSTEM程序包来得到其他用户会话的信息，然后通过这些信息来跟踪这些会话。,SCOTT用户的操作被跟踪,SYS用户执行,停止跟踪,开发测试过程中经常使用的一种方式：execDBMS_SESSION.SET_SQL_TRACE(TRUE)；跟踪当前会话，我们可以在开发调试过程中，在PLSQL中嵌入这么一行，就可以跟踪后面的PLSQL的执行计划输出。,未记录入档的TRACE操作的初始化参数selectksppinmParametername,ksppstvlValue,ksppstdfDefaultfromx$ksppix,x$ksppcvywherex.indx=y.indxandksppinmlike/_%trace%escape/,使用10046跟踪（这个功能扩展了SQLTRACE）10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.10046事件可以设置以下四个级别:1-启用标准的SQL_TRACE功能,等价于sql_trace4-Level1加上绑定值(bindvalues)8-Level1+等待事件跟踪12-Level1+Level4+Level8类似sql_trace，10046事件可以在全局设置，也可以在session级设置。对于4、8、12得到的信息，在TKPROF的时候会被忽略掉，因此需要直接查看trc文件。,使用存储纲要前面讲了很多，主要目的就是为调整性能。协助我们判断ORACLE如何运行SQL语句。存储纲要：首先对一个SQL语句的执行计划进行存储，然后在查询运行的任意时间内使用先前决定好的执行计划。注意：Oracle并没有存储执行计划，而是存储了一些提示（这些提示可以让oracle以精确的方式进行查询）。,设置storedoutline首先要设置一些权限，然后才能存储和使用纲要。,数据库使用基于成本的优化器，一些具体的会话参数,selectname,valuefromv$parameterwherenamein(query_rewrite_enabled,star_transformation_enabled,optimizer_features_enable,cursor_sharing)/,设置参数use_stored_outlines为整个数据库使用存储框架,可以使用altersystemsetuse_stored_outlines=true还可以在会话层启用优化器计划稳定性altersessionsetuse_stored_outlines=true命令,创建框架包,创建和修改存储框架,有两个查询，第一个查询没有hints，第二个查询有hints。根据数据库的特点，我们希望数据库能够使用第二个执行计划。1、修改源代码，在所有的查询中使用hits2、使用存储纲要我们讲到的就是第二个方法,创建两个存储纲要1、我们先给没有加提示的查询创建存储框架,2、再给加了提示的查询创建存储框架,交换存储框架,两个存储纲要的名字发生了转换。这样做了一个欺骗的动作。以后在使用纲要的时候，如果使用的是selectcount(*)fromemp;那么数据库就使用new_emp纲要（转换前使用old_emp）。SQL语句和纲要进行匹配的时候，使用的是类似于sharedpool中的方法。因此我们以后不需要加上提示，就可以使用new_emp这个带提示的纲要了。,测试存储纲要,果然使用的是带提示的存储纲要。从以上可以看到没有加提示的查询使用了加了提示的查询(从而实现了在不需要修改源代码的情况下更改查询的执行计划,进行优化.当然这里是为了演示,本来不加提示时的效率更高),另起一个会话，还是会使用存储的存储纲要。因为我们设置了use_stored_outlines为true时采用的是system，这样整个系统里面所有的用户在执行这个语句的时候，都会使用存储纲要。,新的oracle（8i以上）版本中，在匹配SQL的时候，不再是精确匹配，这样就方便了我们的书写。但是，建议如下：任何时候,只要有可能,所有的SQL都应该存储在PL/SQL过程函数或软件包中,同时使用绑定变量以确保SQL可以同时使用,存储纲要的两个特点1、我们可以在不改变源代码的情况下，修改执行计划（通过增加提示）2、保存存储纲要，稳定执行计划，提高SQL语句的解析速度优化器计划稳定性背后的思想如果表中记录数经常变动,你可能不希望使用优化器计划稳定性,因为,你希望CBO基于当前的CBO统计资料为每一个SQL语句重新生成执行计划如果表相对稳定,那么使用存储纲要将会很大程度提高SQL的性能,因为这样做将绕过SQL解析过程(即使Oracle初始化参数和CBO统计资料变更时)如果你选择为所有的SQL实施存储框架,这种情况下只有新的查询才会使用CBO统计资料,因此,你可以使用这个办法降低表和索引进行重新分析的次数,系统里面有两个存储纲要。都是都是我们前面显式的添加的。,注意：存储纲要放置到了outln模式下面的表中。每个用户可以有自己的存储纲要，但是没有什么必要这么去做。,总结创建存储纲要的方法1、前面已经讲了一种方法：使用create命令显式的创建纲要。2、还可以同设置参数来启用存储纲要的存储,只有在这个会话中运行的SQL语句的执行计划会被存储在存储纲要中。,如果想所有的SQL语句的执行计划都被存储，需要使用如下的命令。altersystemsetcreate_stored_outlines=true;,删除已存储纲要。,如果我们想从RBO迁移到CBO，程序在RBO下面保持良好的性能，为了维持RBO下面的性能良好的执行计划，我们可以将执行计划存储成存储纲要。,创建（会话级别或者系统级别）,使用纲要（会话级别或者系统级别）,1、建立公共的存储纲要2、建立专用的存储纲要（从公共存储纲要复制过来）3、update专用的存储纲要（直接修改提示）4、使用专用纲要进行测试5、测试成功以后，将专用纲要同步到公共纲要,OracleOutline是用来保持SQL执行计划（executionplan）的一个工具。我们可以通过outline工具防止SQL执行计划数据库环境变更（如统计信息，部分参数等）而引起变化。Outline的主要使用在以下情况：1.为避免在升级后某些sql出现严重性能下降而且在短时间内不能优化的情况，我们可以使用outline的功能将原生产库中的sql执行计划实施在新的数据库上。2.为避免SQL的执行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能降低。3.避免大规模分布实施的应用出现数据库版本、配置等区别引起的优化器产生不同的执行计划。4.某些Bug引起优化器生成较差的执行计划。在bug修复前我们可以使用outline来强制SQL的执行计划的正确。Outline的机制是将所需要的执行计划的hint保存在outline的表中。当执行SQL时，Oracle会与outline中的SQL比较，如果该SQL有保存的outline，则通过保存的hint生成执行计划。,Outline的使用需要注意以下事项。1Outln用户是一个非常重要的系统用户，其重要性跟sys，system一样。在任何情况下都不建议用户删除outln，否则会引起数据库错误。2优化器通过Outline生成执行计划前提是outline内所有hint都有效的。如：索引没有创建的前提下，索引的hint是失效的，导致该SQL的outline计划不会被使用。3literialsql的共享程度不高，Outline针对绑定变量的sql较好。针对literialsql的情况，需要每条sql都生成outline。4创建outline需要有createanyoutline的权限。5要注意从CBO的角度来看，数据库表和索引的统计信息是随着数据量的变化而不断改变的。固定的执行计划在某些时段并不一定是最优的执行计划。所以outline的使用是要根据具体情况来决定的。,Outline维护停止db使用outline功能：altersystemsetuse_stored_outlines=false;disable/enable具体outline：alteroutlineol_namedisable;alteroutlineol_nameenable;删除outlinecategory：9i,10g:execdbms_outln.drop_by_cat(category_name);8i:execoutln_pkg.drop_by_cat(category_name);outline相关视图dba_outlines检查outline是否存在selectname,category,ownerfromdba_outlines;dba_outline_hints该视图列出outline的hints内容,Outline使用举例本文举例说明如何使用outline，并且将outline的内容从8i迁移到10g的数据库上使用。操作步骤以scott用户为例说明。8i，10g中在scott用户下创建测试表以说明outline的使用.LoginasscottCreatetablet_test（col1varchar2(2)）;1确定8i生产库的db，listener处于关闭的状态。2启动8i生产库instance.38i库使用system用户登陆，赋createanyoutline权限给sql执行用户。Grantcreateanyoutlinetoscott;,48i库使用scott用户登陆。Createoutlinet_ol1forcategoryspecialonselect*fromt_testwherecol1=00;(注意每个outline都需要使用唯一的名字，不能重复)Specialoutline所属的类（category）Select*fromt_testwherecol1=00;需要保存outline的sql510g，8i库Unlock并修改outlin用户口令。注意，outln用户的口令可以修改但是outln用户不能删除。Alteruseroutlnidentifiedbyoutlnaccountunlock;6在8i库使用outln用户，导出outline数据。Expoutln/outlntables=ol$ol$hintsfile=ol.dmplog=ol_exp.log将export的数据拷贝到10g库所在机器7在10g库使用outln用户导入outline数据impoutln/outlnfile=ol.dmpignore=ylog=ol_imp.log,8在10g库使用sys用户更新ouline的signatureconnectsys/managerexecdbms_outln.update_signatures;启用storedoutlinealtersystemsetuse_stored_outlines=special;指定outlinecategory9检测outline是否被使用connectscott/tigercreateindexI_testont_test(col1);创建索引，以改变执行计划explainplanforselect*fromt_testwherecol1=00;?/rdbms/admin/utlxplp,