oracle性能调优汇总.doc

.性能调优 -方法：一、检查日志是否有错误二、检查参数是否设置正确三、检查系统IO、CPU、内存等利用率，查看哪些占用较高四、检查哪些SQL语句大量占用IO或CPU权衡性能与安全的方面：一、多个控制文件二、一个日志文件组中有多个日志文件三、频繁的执行检查点四、备份数据文件五、频繁的归档六、块检查七、同时执行操作与统计性能调优工具：查看ALTER.LOG中的信息：1、用/ORA- 能查找文件中的错误信息2、要想让ALTER.LOG文件中产生检查点开始与结束信息，得修改参数LOG_CHECKPOINTS_TO_ALERT，将之修改为TRUE。默认为FALSE。SQLALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE SCOPE=BOTH;在用户UDUMP目录下的文件中产生监控用户执行信息：方法1、监控自己的操作。在自己的session中设置参数，这样就能在用户udump目录下生成执行的SQL语句信息SQLALTER SESSION SET SQL_TRACE=TRUE;在pl/sql或者sqlplus中，打开一个sql_window。（1）先运行：alter session set sql_trace=true;（2）再运行你那个返回结果不正确的SQL（3）再运行：alter session set sql_trace=false;（4）马上登陆到机器上，到$ORACLE_BASE/admin/sid/udump目录下。（5）找到刚生成的.trc文件(假设文件名是 xxx.trc)，执行命令转储跟踪文件：tkprof xxx.trc aa.txt。查看aa.txt文件。这个文件里面有执行计划。看看执行计划每一步返回的结果集记录数是不是正确。方法2、SYS用户监控别的用户执行情况 方法3、对所有SESSION进行监控SQLALTER SESSION SET SQL_TRACE=TURE;安装STATSPACK一、创建表空间，最少100M二、安装 在安装时默认创建了一个用户PERFSTAT，所以密码也可以输入这个选择要将快照存放于哪个表空间使用STATSPACK手动执行生成一次快照，间隔一般为10几分钟为益，中间不要有停机。产生报表，产生的报表存放于当前目录下。基于全局视图1、v$sysstat 此表中记录了数据库启动以来各项指标2、v$sgastat 此表中记录了SGA及子部件中的相关信息3、v$system_event 此表中记录了系统中的事件信息，与v$event_name表关联基于session视图其他动态性能视图以X$开头的表为v$的基表。V$fixed_table表中存储了所有动态性能表信息动态性能视图：参数与事件有关：Statistics_level 有三个值alltypical（默认）basic。可以通过alter sessionalter syste set来修改Timed_statistics 值为true/false, 可以通过alter sessionalter syste set来修改，最好打开，跟statistics_level有关，如果statistics_level设置为all或typical,则此值为true。如果statistics_level设置为basic，则为false。Timed_os_statistics 跟statistics_level有关,如果statistics_level设置为all,则此值为5秒。如果statistics_level设置为basic或typical，则为0秒Db_cache_advice 值为on/off/ready, 可以通过alter syste set来修改。跟statistics_level有关，如果statistics_level设置为all或typical,则此值为on。如果statistics_level设置为basic，则为off。数据库配置与IO降低IO的办法：一、将不同的数据文件放在不同的磁盘上（如表或索引，因为索引与表是同时读取的，容易引起竞争）二、将数据文件与REDO文件分开存放，REDO文件尽量放在IO快的硬盘上。三、减少与数据库无关的磁盘IO重要视图：v$filestat 显示了对数据文件逻辑读写、单块或多块IO事件表空间管理：System表空间不能存放用户数据，应该用loaclly managed管理表空间表与索引要分开存放在不同的磁盘上创建独立的undo表空间，不用数据库本身的undo表空间。创建独立的临时表空间，不用数据库本身的临时表空间大的对像应该单独创建表空间用于存放为了加大全表扫描IO吞吐量，可以设置db_file_multiblock_read_count参数。让他一次可以读取多个块，默认为一次读取一块（一块默认为8K）。这个参数与操作系统有关，例：如果此值设置为8，则一次可以读取64K，如果操作系统一次读取128K，则此是可行的，如果操作系统一次不能读取64K，则不可行。一般操作系统一次IO可以达1M，如果此参数设置过大，数据库会自动降低此参数值。此参数可以在SESSION或SYSTEM设置。初始化参数db_file_multiblock_read_count 影响Oracle在执行全表扫描时一次读取的block的数量.db_file_multiblock_read_count的设置要受OS最大IO能力影响，也就是说，如果 你系统的硬件IO能力有限，即使设置再大的db_file_multiblock_read_count也是没有用 的。理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系: Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制，目前Oracle所支持的最大db_file_multiblock_read_count 值为128.重要视图：V$session_longops 查询一些长时间全表扫描的执行信息（大于6秒钟）监控数据库中的全表扫描：Short tables 小于4块Long tables 大于4块查询执行比较长的全表扫描：可以通过如下语句向v$session_longops中添加记录，以便监控：检查点分全检查点与间接检查点全检查点CKPT更新控制文件与数据文件头，DBWN就将脏数据一次性写入数据文件。全检查点又分:Complete一次性将数据写入数据文件。用命令alter system checkpoint;Tablespace只将相关的表空间的数据写入数据文件间接检查点CKPT更新控制文件与数据文件头，分次将数据慢慢的写入数据文件（默认）为了提高IO，尽量将数据放在REDO文件中，少发生检查点少写数据文件。但放置太多如果数据库重启后恢复时间会很长，可以通过fast_start_mttr_target设置恢复时间，如600秒。数据库就会自动调整其他参数以满足恢复时间为10分钟。归档文件应该放在其他磁盘上，REDO组中的文件应该分开放在不同的磁盘上，为了提高归档写速度，可以设置多个归档进程。内存调优Library cache 保存SQL命令（主要调试对象）Data dictionary cache 保存数据字典（相对固定, 主要调试对象）通过show parameter sga能查看到sga中各部件的大小，通过v$sysstat能查看到各部件中的详细组成及大小。一条SQL存入LIBRARY CACHE时，他会自动判断这条SQL与以前解析过的SQL是否一样，一样就调用以前解析过的SQL去执行并返回结果（软解析），如果不一样，他就会去将这条SQL进行解析，检查这条SQL的权限，语法等，生成让数据库能懂的执行代码，并返回执行结果（硬解析）。判断是否一样的条件：一、新输入的SQL与LIBCTIONARY CACHE中的SQL是否一样二、将新输入的SQL生成HASH值，与这前的SQL的HASH值进行比较以判断是否一样三、如果HASH值一样，这时数据库再从头一一比较SQL语句中的各字母，包括空格、大小写、注释，如下三条就不同：数据库认为常量不一样也不是同一SQL，如：可以用CURSOR_SHARING参数设置匹配条件：EXACT精确(默认)SIMILAR 类似FORCE 强制四、如果有两个用户，而两个用户下有一个相同的表，这时即时SQL语句相同，但所有者不一样，这样这两个SQL也不一样。五、当SQL中绑定了变量，不管输入的值是否一样，这SQL是一样的。所以强烈要求使用绑定变量。实例一修改参数：一、查看数据库中已经硬解析了多少次二、修改参数三、修改后就使如下两条SQL只用软解析而不用硬解析：实例二绑定变量一、用存储过程插入数据使用绑定变量不使用绑定变量二、执行存储过程并查看执行时间，以对比使用绑定变量与未使用绑定变量的执行时间：可以看出使用绑定变量的时间远小于未使用绑定变量的时间调试library cache注意事项：一是要使用绑定变量，二是增加Library cache,当然得看free memory有多大（此值应该不能太低）。select * from v$sgastat where name=free memory;三是将一些常用的SQL常驻library cache中四是要在library cache中留出一执行大事务的空间五是避免使用PL/SQL语句读取大的块，也就是将一个大的SQL语句分成多个小的SQL语句六是不要将large pool放在library cache中重要视图：v$librarycacheNAMESPACE library cache中的各组件名GETS 在library cache中寻找某条SQL的次数GETHITS 在library cache中找到某条SQL的次数GETHITRATIO 在library cache中的查找命中率（尽量的高）PINS 在library cache中执行某条SQL的次数PINHITS 在library cache中成功执行某条SQL的次数PINHITRATIO 在library cache中成功执行某条SQL的命中率（尽量的高）RELOADS 在library cache中去执行时发现以前的SQL已经被清除了，得重新去载入并解析（此值应该小于1，如果大于1可能是shard pool cache过小，应该增大。当然得看free memory）INVALIDATIONS 因为表进行了修改，这时就要重新解析（此值尽量的接近0）提高SQL命中率的方法：一、绑定变量二、少用动态SQL语句三、尽量使用同一个用户登录V$sql_shared_cursor 查看SQL为何不能与已经存在LIBRARY CACHE中的SQL共享检查LIBRARY CACHE是否过小：查询正在运行的SQL:查看运行的SQL的完整信息：总结：如果发现命中率低、free memory低、reloads高等，就表示library cache过低，应该调大shared_pool_size，因为library cache不能单独调大。查看LIBRARY CACHE大小建议：此视图能估算出SHARED_POOL大小的不同系统的性能变化。查看SQL执行计划：V$sql_plan表中的hash_value字段与v$sql表中的plan_hash_value字段进行关联。保留池（共享池中的一部分）SHARED_POOL_RESERVED_SIZE: 当载入一个大的对象时（5K），虽然在library cache的碎片中有能放下5K的空间，但没有能连续的能放下5K的空间，这时就只能调用保留池（相当于预留的一个空间）以提高性能，不然就会清除一些过时的SQL对象，再重新载入解析执行。当调用的完后又会将空间还给保留池以供下一次使用。保留池大约为共享池的510%，最大不能超过50%。可以通过修改SHARED_POOL_RESERVED_SIZE大小来实现修改保留池大小。可以通过V$SHARED_POOL_RESERVED视图查看保留池是否满足大小。如果REQUEST_FAILURES大于0或逐渐增大，可能就是保留池太小，应该增大保留池和共享池大小。如果REQUEST_FAILURES等于0或不逐渐增大，并且保留池中的空闲空间常大于50%可能就是保留池太大，如果太大应该减小保留池。注：如果是自动管理SGA，通过命令查看如下：SQL show parameter shared_pool;NAME YPE VALUE- - -shared_pool_reserved_size big integer 31876710shared_pool_size big integer 0要想查看真实的shared_pool_size得从v$sga_dynamic_components表中查看。修改shared_pool_size大小：一、先要估算出library cache现在占用大小或二、将某些SQL固定在内存中一、先确定哪些SQL需要固定在内存中KEPT=NO 表示该SQL不在内存中二、执行包注：如果要固定最好在数据库一启动时就固定，因为内存中还没有存在的包等，有空余空间。sys.dbms_shared_pool.keep(address|,|hashvalue,C); 固定SQLsys.dbms_shared_pool.unkeep(address|,|hashvalue,C); 取消固定SQL清除内存中的对像，但固定的不会被清除。查找大的数据块：如果查到可以将该SQL改写成小的数据块以提高执行速度。常用视图：调整Data Dictionary Cache:Gets: 读取次数Getmisses:读取失败次数（该参数越小越好或尽量不增加，但如果数据库刚启动不久则该参数可能较大，如285，因为刚启动很多数据本就不存在）查询执行情况:如果不够就可以调整SHARED_POOL_SIZE大小。alter system set shared_pool_size=50M scope=spfile;调整Large Pool大对像使用（I/O服务进程，如DBWR_IO_SLAVES,备份与恢复，共享模式，并行查询），用的时候在SGA中分配，用完就清除。调整参数LARGE_POOL_SIZELARGE_POOL通常用于共享服务器(MTS)，并行计算或者RMAN备份恢复中。设置其大小的参数为：LARGE_POOL_SIZE 如果不设置MTS，LARGE_POOL通常在RMAN 、OPQ 会使用到，LARGE_POOL_SIZE设置在10M - 50M 应该差不多了。如果RMAN备份恢复中，分配了多通道，可以考虑参考给出的公式。LARGE_POOL_SIZE = number_of_allocated_channels * (16MB+( 4 * size_of_tape_buffer)。 假如设置 MTS,则由于 UGA 放到large_pool_size 的缘故，这个时候依据 session最大数量和 sort_ares_size 等参数设置，必须增大large_pool_size 的设置，可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点，不是必须使用MTS，我们都不主张使用MTS，尤其同时在线用户数小于500的情况下。调整BUFFER CACHE原理：Server进程读磁盘数据到内存LRU链表对列中去，DBWn进程将内存CHECKPOINT QUEUE对列中的脏数据写入磁盘。LRU链表控制访问次数的排列，访问次数多的排在前面，次数少的或没有被访问的放在后面直到清除。CHECKPOINT QUEUE脏数据排序，等待写入磁盘。Buffer cache中块的四种状态：Pinned:有多个块，但只有一个块能写，其他块等待Clean:buffer cache中的内容与磁盘上的一致，也没有被使用Free/unused:从来没有使用过的快Dirty:没有被使用，但内容与磁盘上的不一致，调用DBWn进程写到磁盘上，后才能淘汰脏数据块（dirty buffer）：buffer cache中的内存数据块的内容被修改，从而导致与数据文件中的数据块的内容不一致。空闲数据块（free buffer）：buffer cache中的内存数据块为空。干净数据块（clean buffer）：buffer的内容与数据文件中的一致。钉住的数据块（pin buffer）：当前正在更新的内存数据块。DB_BLOCK_CHECKSUM=TRUN 块校验，防止数据损坏。全增加12%的负载。Buffer cache下有几个子cache（default、keep、recycle 、nk buffer cache）。他们的大小各不相关，由以下三个参数分别指定调整各cache大小由DB_CACHE_ADVICE监控并给出建议：一、修改参数使用监控Alter system set db_cache_advice=on/off/ready。如果由off变为on时，建议由off到ready再到on。以免会发生无故错误。二、查看视图V$db_cache_advice以得到建议cache值：以16、32等cache大小来标示各段读取次数。注：ORACLE物理读并不一定是指读磁盘，因为他是将读交给操作系统，由操作系统去读。操作系统接到读命令后有可能在操作系统中的缓存中读，如果缓存中没有要的结果时才向磁盘读。所以可以增大操作系统的缓存来达到减少磁盘读，但不建议这样做。最好减少数据库的读次数。执行一条SQL的顺序：SERVER原理一、首先在buffer cache中查找，如果查找到将其标记为最近更新，如果没有找到就从数据文件读。二、在读数据文件之前，在LRU中找一个空的位置，同时将改变的脏数据挂到checkpoint queue对列上。三、如果checkpoint queue对列达到预设的长度值，SERVER进程就会提醒DBWn进程将脏数据写入磁盘。四、将数据块读入到LRU中间何时执行DBWn:一、如果checkpoint queue对列达到预设的长度值，SERVER进程就会提醒DBWn进程将脏数据写入磁盘。二、数据库热备份时三、删除对像时四、关闭数据库时调整buffer cache步骤：三个重要指标（影响性能的指标）：Free buffer inspected:指在LRU中查找到空闲块所经历的查找块数Free buffer waits:在LRU上找不到空闲块时通知DBWn将数据写入磁盘后所用的等待（造成的原因：一、DBWn进程一直在执行但I/O写入慢，解决方法是将数据文件均匀的分布在各磁盘上，不行就换更快的磁盘。二、I/O不慢，但经常等待一些被别人占用的资源，解决方法是将数据文件均匀的分布在各磁盘上，不行就换更快的磁盘。三、本身BUFFER CACHE太小，脏数据产生太快太多，DBWn进程一直都处理不过来。解决方法是加大buffer cache。四、DBWn进程太少，处理不过来，解决方法是增加DBWn进程数或减小buffer cache大小）。Buffer busy waits:在LRU上找到了所要的块，但该块正被另一个进程所使用，这时就等待（造成争用的原因：一、(unselective indexes)争用同一个表或索引中的同一块，可能是SQL语句使用了LIKE等函数，查询不精确，造成多条SQL查询时使用到了同一条数据，解决方法是少用模糊查询，以免使用到其他语句要用的数据。二、(right-hand-indexes)访问不同块，但这个些块的索引值几乎相同，如1234、1235。并放在同一个块中。这样就很可能造成争用，解决方法为修改倒序索引类型，让其值变为4321、5321。三、UNDO HEADER争用，如果不是自动UNDO表空间管理有可能造成争用，解决方法是自动管理UNDO表空间或扩大UNDO表空间。四、UNDO BLOCK争用，如果不是自动管理UNDO表空间就增大块大小）查询当前使用情况：扩展：如BUFFER BUSY WAITS为3466个争用，可以用下面的命令查看是在使用哪些块等从而找到是哪些SQL或索引在争用，从而去优化这些SQL或索引查询命中率：Session logical reads:请求总次数Physical reads:物理读次数如果命中率很低，在调大buffer cache之前要做如下步骤：一、以上三种重要指标已经达到要求二、SQL已经被调优三、避免出现将数据写入交换区上四、确保上一次增加的buffer cache有效五、命中率依然很低调整大小：一、修改参数使其监控二、查看建议值三、调整动态调整调整Multiple buffer pools内存中的块分三种：热块（常用的块，不会被清除掉）、温和块（相对来说使用比较少）冷块（极少使用）调优办法：将热块放在default pool中，将温和块放在keep pool中，将冷块放在recycle pool中。各池大小调整：Alter system set db_cache_size=128M;Alter system set db_keep_cache_size=256M;Alter system set db_recycle_cache_size=64M;注：他们三个池大小不受对方影响，如db_cache_size不够时而db_keep_cache_size可能还有很多空闲空间。他不会去db_keep_cache_size中拿空间来用。如果将各种块放在各池中？在创建时指定放在哪个池中：不指定就默认放在default pool中修改到其他池中去：应该将哪些对象放在哪些池中？将常用的小表放在keep pool中，如果是大对象不常用放在recycle pool中。对象在池中占用多大？重要视图：v$BHKEEP POOL调整目标：尽量让keep pool中的对象不要清除掉，让keep pool中的对象总块尽量等于keep pool块大小。RECYCLE POOL调整目标：用完就清除掉。查询三个池的命中率：查询三个池的块数什么是大表？就是指有一定限制的块个数，11G中限制为20个块的表为大表，此值可设定。CACHE TABLES对一些大表进行全表扫描时，会将块放在对列最后，很快就会被淘汰出局，但对于一些小表进行全表扫描时也会这样，但有些小表又常常要使用，不希望被淘汰出局，这时就可以将要全表扫描的小表固定起来不让淘汰出局（指一定时间不会被淘汰，如果长时间不访问也会被淘汰出局），使读入的块放在对列前面。原则：不要在CACHE中放太多的表，以免使得内存不够 尽量将表固定在keep pool中在创建表时固定到内存中在最后加cache语句：创建表后在修改：注：要取消内存中的表就在加nocache:SQLALTER TABLE employee NOCACHE;在 hint中创建表到内存中：FREE LIST：如果有多个进程对表进行操作，那么可能对FREE LIST进行争用并提示“buffer busy waits”。这时就要以创建多个FREE LIST，原则是对一个表有多少个进程进行操作就创建多少个FREE LIST。单CPU创建多个FREE LIST效果不大。推荐使用自动管理方式ASSM空间自动管理（BITMAPS方式）。查看FREE LIST争用情况：查看v$system_event表中的buffer busy waits事件与v$waitstat表中的segment header事件就能确定是否有free list争用情况。此SQL能查询是在争用哪个表注：创建ASSM管理方式在创建表空间时指定：这样上面这个表就是使用了ASSM自动管理方式管理。指定多个DBWn:如果buffer cache中的脏数据过多，而又不能及时写入数据文件，这时可能造成等待。为了解决这问题可以指定多个DBWn进程进行写操作。什么时候要增加DBWn进程？查看FREE BUFFER WAITS事件，如果较多就可能是DBWn进程太少写不过来。SQLALTER SYSTEM SET DB_WRITER_PROCESSES=5; 多CPU和系统支持异步IO操作比较有用。注：操作系统大多支持异步IO功能异步IO:就是进行发出一个读指令，过一会在去取要的结果就可以了。（推荐）同步IO：就是进程发出一个读指令，直到结果返回。为了实现异步IO，所以在安装数据库时必须安装以下两个文件：打开异步IO：如果系统不支持异步IO,这时就不能用指定多个DBWn进程的方式来提高读写速度，只能用以下方式实现：一个DBWn进程与多个IO SLAVES进程模拟异步IO。奖dbwr_io_slaves设置为多个时就启动了模拟IO。动态调整SGAV$brffer_pool 此视图能查看到SGA调整之前与调整之后的各值调整SGA各池大小：调整REDO LOG BUFFER：当多个用户都执行DML或DDL操作时，由于LGWR进程只有一个，可能造成不能及时的将修改记录写入REDO LOG日志文件，这时就有可能造成用户进程等待，所有有必要调整REDO LOG BUFFER大小。当LGWR进程将修改记录信息写入完1、2、3时再重头写1时，这时如果ARC0进程写放太慢，这时也要等待ARC0将信息写重做日志后LGWR才能写入REDO LOG,这时就可以增加REDO LOG日志组个数或日志组大小或增加ARC0进程数（LOG_ARCHIVE_MAX_PROCESSES）。查看切换等待： LGWR何时执行？用户COMMIT时、三秒钟、REDO LOG BUFFER三分之一时、1M时（所以不用配置太大，以免浪费，一般就12M或几百K）。REDO LOG BUFFER大小一般为512K或128*CPU数.SQLALTER SYSTEM SET LOG_BUFFER=512K;注：查看CPU个数：SQLSHOW PARAMETER CPU;如何查看是否REDO LOG BUFFER不足：方法一、V$sysstat：Redo entries:向REDO LOG BUFFER中放了多少条REDO信息。Redo buffer allocation retries:在REDO LOG BUFFER不足的情况下，尝试了多少次去寻找空余空间。Redo buffer allocation retries/ Redo entries=% 应该小于1%方法二、发现不能向REDO LOG BUFFER中放信息时就会在v$session_wait中出现log buffer space event事件。所以应该不出现log buffer space event事件为最好。调整：方法一、增加REDO LOG BUFFER池大小方法二、如果LGWR等待： A、增加日志组个数或日志组大小B、增加ARCn进程个数方法三、减少产成REDO信息 A、如批量导入可以不用产成REDO信息B、在创建表或索引时指定NOLOGING不产生REDO信息调整排序操作如下会用到排序：Sort-based(sort、groub-by、order by)Hash-joinBitmap operatorsWork area:工作区，在PGA内存中单独划分了一个区域用于排序。工作区分三种情况：1、optimal 全部输入排序的数据都在内存工作区中完成（理想情况）2、one-pass 输入排序的数据在内存中放不下在磁盘中放一部分，做一次磁盘扫描。3、multi-pass输入排序的数据在内存中放不下在磁盘中放大部分，做多次磁盘扫描（最差）。PGA中各个用户工作区大小是默认为自动管理，管理员只关心总的工作区大小就可以，不用关心各个用户连接上来的工作区大小，但如果确实用户连接过多，要使用的工作区过大，也可能超过管理员设置的总工作区大小。在PGA中，用户连接上来一个就会在PGA中分配一块内存（工作区），断开后就会清除该工作区。SQLALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO|MANUAL; 推荐自动管理方式。SQLALTER SYSTEM SET PGA_AGGREGATE_TARGET=1024M; 设置总工作区大小。总工作区设置多大？建议：可以先设置一个初始值，运行一段时间后可以去查看相应的动态视图以得到推荐值。建议：将内存的20%留给数据库以外的应用，在将80%物理内存分配给实例，再根据系统情况分配相应的大小给PGA；如是OLTP系统，分配推荐为：PGA_AGGREGATE_TARGET=(TOTAL_NAME*80%)*20%;如果是OLAP或DSS系统，分配推荐为：PGA_AGGREGATE_TARGET=(TOTAL_NAME*80%)*50%;判断内存是否够用？一、查看参数Over allocation count； 指超过你设置的总工作区大小的次数，如果超过次数很多，就表明你设置的总工作区太小。Bytes processed 指运行数据库以来PGA处理的总字节数。(相当于放内存中的量)Extra bytes read/written 指当没有工作在optimal模式下时，他在磁盘上（临时表空间）读或写的字节数。（相当于放磁盘上的量）二、查看参数百分比Cache hit percentage；累积值，如果是100%表示工作在OPTIMAL中，公式内存中处理大小/（内存中处理大小+磁盘上处理大小）如：有四个表要进行排序，三个1M和一个100M，如果一个1M在ONE-PASS中，则100/(103+1）*10099.03%公式：Bytes processed / (Bytes processed+ Extra bytes read/written) =Cache hit percentageV$SQL_WORKAREA_HISTOGRAM工作排序历史记录表：分成33个组，每组给出了推荐值V$SQL_SORKAREA_ACTIVE：记录了当前正在排序的工作事件V$sql_workarea记录了排序的历史记录（垒计值）查看SQL每个工作区的语句：查看当前SQL的活动情况：查看SQL的执行计划：调整大小PGA_AGGREGATE_TARGET：一、设置STATISTICS_LEVEL为TYPICAL二、查看V$PGA_TARGET_ADVICE视图如果设置过小，ESTD_OVERALLOC_COUNT非0，即处于灰色区，那么即时设置为375M，ORACLE也会突破这个值，所以根本不用考虑灰色区。以上推荐为自动管理方式，如果遇到某个用户要执行大量的批量操作时，可能会临时改为手动管理方式，以某用户登录后执行：SQLALTER SESSION SET WORKAREA_SIZE_POLICY= MANUAL; 手动管理方式。SQLALTER SESSION SET PGA_AGGREGATE_TARGET=1024M; 设置总工作区大小。注：如果是专用模式，排序区放在PGA中，如果是共享模式，排序区放在SGA中。如果是手动管理方式可能要设置如下两个参数：SORT_AREA_SIZE 排序区大小SORT_AREA_RETAINED_SIZE 排序结果区大小例：如果有两个排序操作，一个排序结束后会将结果放在SORT_AREA_RETAINED_SIZE中在去执行第二个排序操作。第一个排序结果等待慢慢被用户提取。建议：推荐自动管理 尽量少排序 排序尽量在内存中完成 如果在内存中不能完成，就要规划好临时表空间ROWS 表示排序了多少行DISK 表示在磁盘中排序了多少次MEMORY表示在内存中排序了多少次磁盘排序与内存排序比值应该小于5%如果大于5%，则在自动管理方式下得增大PGA_AGGREGATE_TARGET，如果是手动管理则增大SORT_AREA_SIZE。查看临时表空间的占用情况：资源管理器能限制SESSION对资源CPU、磁盘IO、读取时间、超时时间等进行限制。资源管理器只对组进行控制：三个对象如下资源控制组相同限制的为一组资源规划限制CPU等资源使用情况资源集合包括资源控制组与资源规划的指令资源管理器由DBMS_RESOURCE_MANAGER包管理。例：OTHER_GROUP组必须存在。创建组：或可以创建8个组创建子组：等级：共有8级，L1-L8。L1最高。可限制的资源：CPU占用率允许多少个用户连接（超出的就放在对列中等待，直到前面的执行完成）一个用户可以并发多少个进程（只对一个限制）自动组切换（就是如果一个限制用户所执行情况超出了该组的设置，他会自动将他清除到其他组，通常另一个组等级比该组低）SQL最大执行时间（如果超过该执行时间将可能不会被执行或报错）UNDO POOL大小（限制UNDO表空间大小）空闲时间分配方法：SYS_GROUP与OTHER_GROUPS组为默认组，不能删除。资源管理步骤：一、分配权限（如果是SYS用户就不用授权直接可以使用） 授权给OE用户并且他不能再授权给其他用户二、创建资源管理 1、创建等待区（存放即将创建的组、计划等） 2、创建、修改、删除组创建组选项： 3、创建资源计划创建计划选项4、创建指令创建指导性计划选项 5、验证指令 6、提交到DATA DICTIONARY目录中 7、分配用户到组将MOIRA用户分配到OLTP组中并且不能再分配权限或默认分配给某用户 8、加载计划等于后跟计划名称9、查看计划扩展知识：DBA手动将某SESSION切换到某个组手动切换组:将某用户切换到指定组中：相关视图SQL调优优化器两种优化模式：RULE-BASED 模板规则，根据这些模板规则去检查SQL以得出用哪个规则最优化。（过时）COST-BASED 信息统计，根据SQL所要统计的条数、索引、CPU、IO等情况选择用哪条路径最优化。（推荐）优化器的分类为批处理与交互式两种。修改优化器行为：方法一、用参数修改优化器行为，可以在三个等级上执行。ALL_ROWS返回所有行的最小成本FIRST_ROWS_N返回前1、10、100或1000行的最小成本STATEMENT最高，INSTANCE最低。在统计数据前尽量更新数据字典中的统计信息以免得到最精确的统计值方法二、使用HINT提示以指导优化器如何处理告诉优化器用指定的索引进行查询方法三、固定执行计划，就是应用程序中的SQL在一个数据库或环境中调试好的执行计划拿到另一个数据库（高版本）或环境中去后可能执行计划发生改变，他会根据新的环境或数据库生成新的执行计划，这样就可能得重新调试SQL以达到优化的目的，所有有必要将以前调试好的计划固定住让其在新环境或数据库中也使用以前的执行计划进行优化查询。（方法就是将以前的执行计划导出来并导入到新的环境中去，此法已经过时），另一方法是使用参数让新的数据库使用某个版本的执行计划去实现版本的兼容。注：STORED OUTLINES过时，SQL PLAN MANAGEMENT推荐使用。调优工具一、关注STATSPACK中的以下值EXECUTIONS 执行次数最多的排行PARSE CALLS 解析次数排行GETS 读取buffer cacth次数排行READS 从磁盘读块到内存中的次数排行二、执行计划查看SQL执行计划时就在前面加EXPLAIN PLAN FOR。执行后就会将结果存储到PLAN_TABLE表中，这表第一次使用时要手动创建。创建执行计划后查看表中的执行计划，查看完执行计划后请清空该表为下次使用SQLdelete from play_table;1、可以直接查看表PLAN_TABLE2、可以用脚本查看utlxpls.sql（执行串行SQL时查看用）注：查看执行计划原则：如果是同一缩进先执行上面的再执行下面的，不在同一缩进先执行里面的再执行外面的。从上图可以看出先执行2再执行1。也可以用脚本查看Utlxplp.sql(执行并行SQL时查看用)3、用包dbms_xplan（推荐）三、用TKPROF工具查看TRACE文件1、查看TRACE文件要先打开2、执行要查看的SQL语句3、关闭为4、最后用TKPROF工具打开TRACE文件并输出成报表注：TRACE文件放在USER_DUMP_DEST目录下，生成的文件也放在此目录下用法：Tkprof tracefile outputfile explain= table= print= insert= sys= sort= 参数说明：tracefile：你要分析的trace文件outputfile：格式化后的文件explain=user/passwordconnectstringtable=schema.tablename这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中。注意，该table必须是数据库中不存在的，如果存在会报错。print=n：只列出最初N个sql执行语句insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中sys=no：过滤掉由sys执行的语句record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去waits=yes|no：是否统计任何等待事件aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes四、SQL*PLUS AUTOTRACE工具1、安装2、使用管理统计信息管理统计的目的就是为优化器提供最佳路径，使优化器以准确的显示结果。主要用DBMS_STATS包进行管理。针对表的：有多少行、多少块、平均长度索引：多少个叶子结点、有几级、列：一列中有多少个NULL值、一列中有多少个不同的值、数据分布情况、系统：CPU、IO收集信息所用的包：收集过程收集HR用户下的T表信息：查看收集信息：收集完成后优化器就会根据收集到的信息选择最佳路径。注：收集的信息放于数据字典中什么时候收集？1、如果一个表是递增式的增涨数据，如表中本有一百万条数据，那么一天可能增涨几十上百条数据，这样的就可以一周或一个月手动收集一次信息2、如果批量修改了大量数据信息这时就应该立即收集信息3、如果是分区表中某一个分区发生了改变，这时就只需要收集改变的分区信息表的收集信息：1、一个表中有多少记录2、一个表中有多少块与空余块3、每块的空余空间4、一个块中放不下