oracle性能与调整学习笔记

1、performance tuning study guidelesson 2 tuning overview1. tuning stepsn tune the design.n tune the applicationn tune memoryn tune i/on tune contentionn tune the operating system.2. tuning goalsn access the least number of blocksn cache blocks in memoryn share application coden read and write data as

2、quickly as possiblen ensure that users do not wait for resourcesn perform backups and housekeeping while minimizing impactlesson 3 alert and trace files1. objectivesn describe the location and usefulness of the alert log file.n describe the location and usefulness of the background and user process

3、trace files.2. the alert log file of database contains the following information:n internal errors(ora-600)，and block corruption errors(ora-1578)n operations that affect database structures and parameters, and statement such as create database, startup, shutdown, archive log and recovern the value o

4、f all nondefault initialization parameters3. background process trace filesif an error is detected by a background process, the information is dumped into a trace file.4. user trace fileswhen resource consumption occurs during statement processing,server processes can generate trace files at the use

5、rs request. this is called user trace files.5. initialization parameter:n background_dump_dest: stores alert log file and background processes trace filesn user_dumpt_dest: stores user trace files.n sql_trace: used to enable or disable sql trace filesn max_dump_file_size: limits the size of user tra

6、ce files,specified in o/s blocks.6. 在会话期间打开允许用户级别跟踪：(session-level tracing)execute dbms_system.set_sql_trace_in_session (8,12 true);注意8是sid，12是serial#,可以通过v$session来查找，并且dbms_system包属于sys用户，普通的用户无法执行。lesson 4 utilities and dynamic performance views1. 学习目标n 通过下列手段来搜集统计信息： 性能视图和故障解决视图 utlbstat和utlesta

7、t 报告输出 oracle等待事件 oracle企业管理器提供的相应工具n 描述闩类型n 使用企业管理器(enterprise manager,em) 来为预定的环境设定事件(event)2. oracle 提供的视图、实用脚本和工具：n 动态故障解决、性能视图和数据字典：n 实用性能分析脚本：utlbstat.sql和utlestat.sqln oracle等待事件n 企业管理器事件服务n oracle分析和调整包3. v$视图和x$表的比较n v$视图是基于x$表来产生的n 从v$fixed_table中可以查看有那些v$视图和x$表n x$表不经常使用，而且名字短、晦涩难懂n v$视图内

8、容在实例启动时生成，关闭时清除4. 故障解决和性能调整常用视图介绍(t代表故障恢复，p代表性能调整)：n 数据库/实例相关的视图： v$px_process_sysstat (t/p):并行查询系统统计信息。提示：px一般都代表并行 v$process (t):当前活动的进程相关信息 v$database (t):数据库信息。如数据库id、名称、是否归档等等 v$instance (t):实例信息：如版本号、实例名称等 v$option (t): oracle服务器的安装选项 v$parameter (t/p):oracle初始化参数设置 v$backup (t):显示联机的数据文件的备份状态

9、 v$waitstat (p):显示数据块争用信息。注意，只有当timed_statistics设置为true的时候该视图的内容才被更新。 v$system_event(p)：显示某个oracle 事件(event)的等待统计信息，是所有会话的合计。这个视图有一个time_waited字段来表示等待时间，如果想让该字段显示正确，同样需要设置timed_statistics为truen 内存相关视图 v$buffer_pool_statistics (t/p):实例分配的缓冲池信息。该视图需要运行catperf.sql来创建，该脚本在window中位于rdbmsadmin下。关于数据缓冲的更多信

10、息在第6课 v$db_object_cache (t):库缓冲区(library cache)中的数据库对象信息 v$librarycache (p)：库缓冲区的性能及活动统计信息。对该视图的进一步讨论在第5课。 v$rowcache (p):数据字典活动的统计信息。详见第6课 v$sysstat (t/p):基本的实例统计信息 v$sgastat (p):显示系统全局区(sga)的内存分配情况的详细信息n 磁盘相关视图 v$datafile: (t/p)数据文件基本信息 v$filestat：(t/p)数据文件读写统计信息 v$log：(t)日志文件信息 v$log_history：(t)日

11、志历史信息 v$dbfile(t/p):数据文件信息。这个视图为了向下兼容，建议使用v$datafile. v$tempfile：临时文件信息 v$tempstat (p)：临时表空间的数据文件的读写统计信息n 争用(contention)相关视图 v$latch (p):每一种闩类型的统计信息 v$rollstat (p)：回退段的统计信息 v$waitstat (p)：查看前面的解释n 说明：数据库/实例、内存、磁盘、争用相关的视图组成了系统范畴的统计信息视图(system-wide statistics)n 会话相关视图 v$lock: 服务器当前维护的锁信息和显式锁/闩请求 v$ope

12、n_cursor:每个会话当前打开并分析的游标数 v$sort_usage:临时段的大小、创建临时段的会话等 v$sesstat:用户会话统计信息 v$session_event:每个会话中的等待事件统计信息 v$session_wait:活动会话的资源或事件争用/等待情况wait_time字段不同值的含义：0：会话的最后的等待时间=0：会话的对应事件正处于等待状态=-1：值小于1/100秒=-2：无法提供事件信息注意，wait_time需要设置timed_statistics为true v$px_sesstat:并行服务器中用户会话统计信息 v$transaction:活动的事务提示：v$s

13、ession_event和v$session_wait的英文解释就像绕口令一样难以区分，通过实际查看对应的内容就不难区分了。5. system statistics are classified by topic(e.g. class column in v$sysstat)n class 1: general instance activityn 2: redo log buffer activityn 4: lockingn 8: database buffer cache activityn 16: operating system activityn 32: parallelizati

14、onn 64: tables accessn 128: debugging purposes6. 关于v$statname和v$event_namev$statname描述了统计信息的种类及名称，其中的statistics#字段可以和v$sesstat、v$mystat的statistics#来进行联合查询。v$event_name描述了事件的种类，及每一种事件可能有的几个参数的描述。7. general session-related statisticsv$mysstat显示了当前会话的基本统计信息8. 查询会话基本信息举例：下面的语句显示使用pga(program global area

15、)内存超过30000字节的会话：select username,name,valuefrom v$statname n, v$session s, v$sesstat twhere s.sid=t.sid and n.statistic#=t.statistic#and s.type=user and s.username is not nulland =session pga memory and t.value30000;9. 关于实用脚本utlbstat和utlestat：n 这两个脚本在windows中位于rdbmsadmin目录。通过执行这两个脚本，可以生成数据库的性能报

16、告文件。脚本需要以sysdba权限来运行，并且要设置timed_statistics为true。实际上，可以这样简单地来理解这两个脚本：其根本目的是想获得某一段时间内各种资源的变化情况。因为v$视图内容不断变化，因此不容易或的某期间内的具体变化情况。而utlbstat.sql执行后，对当前的v$视图的值进行复制，从而获得了某个时间点的信息。utlestat.sql执行的时候，将v$视图的值和utlbstat产生的表的对应的值相减，获得了期间内的变化信息，据此对数据库进行分析。这两个脚本实际上是ora9i的statspack的前身。n 提示：如果想运行这两个脚本，建议找到这两个脚本，然后根据实际

17、情况修改一下连接的设置，因为连接数据库的时候用的是connect internal，可能在实际中无法连上，并且首先运行utlbstat，然后过一段时间再运行utlestat。这期间一定要有正常的数据库活动，否则无法反应真实情况。生成后要把timed_statistics改成false，否则数据库会运行很慢。 10. utlestat产生的报告包含的内容：n library cache statisticsn system statisticsn wait event statisticsn latch statisticsn rollback contention statisticsn bu

18、ffer busy wait statisticsn dictionary cache statisticsn i/o statistics per data file and tablespacen period of measurement11. latches:latches(闩，内部锁)用来保证内存结构不被修改。12. contention:当不同的进程要求访问同一资源的时候，就会发生争用(contention)。调整latches的目标就是尽量减少争用13. dba可以调整的争用：n redo allocation latchn redo copy latchn lru latch提

19、示，可以从v$latchname视图来查看latch的名称14. latch 类型n willing-to-wait（可等待） gets misses sleeps 说明，对于要求willing-to-wait闩的请求，如果当前闩不可用，请求将会等待一会然后重新请求获取闩。gets: 显示成功获willing-to-wait的闩请求数。 misses: 显示没有成功获取willing-to-wait的闩的请求数。sleeps: 显示进程等待闩的次数。n immediate immediate gets immediate misses 说明，对于immidiate类型的请求，如果闩不可用，请求

20、将立即失败15. 常见的等待事件：n free buffer waitn latch freen buffer busy waitsn db file sequential readn db file scattered readn db file parallel writen undo segment tx slotn undo segment extension提示，这些事件在v$event_name中可以找到16. 事件管理系统(event management system)的作用：n 通过创建事件来监控数据库、节点、网络中的非正常情况n 通过注册事件来自动发现问题n 通过采用修复来

21、自动解决问题n 当事件发生时通知管理员n 有五种预定义事件测试种类： 空间(space) 缺陷(fault) 资源(resource) 性能(performance) 审计(audit)17. 事件管理资料库( em repository)包括如下内容:n predefined events and events sets, and those created by the usern the fixit jobs for each eventn the registered events and their registration statusn the occurred events a

22、nd their degree of alertn the acknowleged events moved to historyn the list of administrators to be notified when events occurn information on how to notify administrators on duty when events occurn the schedule for notifying the administrator on duty when events occur18. 预定义的事件测试种类：n fault manageme

23、nt event tests: database alert(database): this test monitors when new errors have be encountered in the database alert log. database updown(database): this test monitors database status. an event occurrence is issued if the database fails. archiver hung(database): this test monitors when database ar

24、chiving has become suspended. database probe(探测器)(database): ensures that database connections can be made. data block corruption: ora-01578 sqlnet updown(listener): this test monitors listener status.n space management events tests: alert file large chunk small disk full dump full fast segment grow

25、th maximum events tablespace fulln resource manage event datafile limit lock limit process limit user limit session limitn performance management events buffer cache chain row cpu utilization disk i/o in memroy sorts library cache rollback contention19. oracle提供的调整包(oracle packs)简介：n performance man

26、agerit provides the ability to monitor database performancen topsessionsit monitors how connected sessions use database-instance resourcesn oracle trace managerit enables you to monitor performance by collecting data about events that happen in applications.n oracle trace viewerit enables you to vie

27、w the trace formatted output and make appropriate tuning n capacity plannerit enables you to plan for system resources based on the current work load.n tablespace managern sql analyze this application allows you to tune the sql application.n oracle expert it enables you to optimize the performance o

28、f your database20本章总结：本章介绍了大量的视图和工具，第一次看很容易混淆，如果看一遍不能完全掌握很正常，因为后续的很多章节都是这一课的延伸。对于各种视图，最好的理解办法就是查一查，看看到底是什么内容，utlbstat和utlestat也最好执行一遍。有条件的话，甚至打开这两个脚本，一句话一句话的来执行，会有很好的理解。lesson 5 tuning the shared pool1. objectivesn tune the library cache and the data dictionary cachen measure the shared pool hit rat

29、ion size the shared pool appropriatelyn pin objects int the shared pooln tune the shared pool reversed spacen describe the user global area(uga) and session memory considerationsn configure the large pool2. 共享池(shared pool)包含的内容：n 库缓存(library cache)：包含语句文本，解释过的代码和执行计划。library cache的主要作用是存储sql语句，共享pl

30、/sql块，避免语句重复解释。library cache通过lru（least recently used，最近最少使用）机制来维护。n 数据字典缓存(data dictionary cache)：表、列的定义及数据字典表权限等n 用户全局区(user global area,uga)，用于多线程服务器连接。3. shared_pool_size:这个初始化参数决定了共享池的大小，单位是字节。可以用下面的语句来查看改参数的设置：select value from v$parameter where name=shared_pool_size;4. 库缓存区(library cache)包含的内

31、容：n proceduresn functionsn packagesn triggersn anonymous pl/sql blocks5. library cache调整的第一目标：尽量减少语句的重新解释：n make sure that users can share statementsn prevent statements from being aged out by allocating enough spacen avoid invalidatoins that induce reparsing6. library cache调整第二目标l: 避免碎片:n reversing

32、 space for large memory requirementsn pinning frequently requred large objectsn eliminating large anonymouse pl/sql blocksn reducing uga consumption or mts contentions7. v$librarycache视图的几个术语（列）解释：（参照oracle concepts）这个视图包含library cache性能和活动的统计信息。n namespace:命名空间，也就是library cache种类。n gets: 该命名空间中要求获得

33、锁的次数。n pins: 该命名表空间中要求钉在library cache中的次数n reloads: 要求钉在library cache的请求重复执行，导致重新从硬盘装载8. 调整库缓存区的相关视图：n v$librarycachen v$sqlarean v$sqltextn v$db_object_cachen v$sgastat提示，这些视图的具体含义可以参照oracle concepts9. 调整library cache指南：reloads-to-pins ratio:reloads-to-pins率计算方法:sum(reloads)/sum(pins)，reloads和pins是

34、v$librarycache视图的列。该值应该小于1%，否则就应该增加sga大小。10. “失效”(invalidations)概念和产生失效的情形如果某对象包含在某条语句中，而该对象被修改了，那么就会产生失效，sql共享区就无效了，需要重新装载11. 调整库缓冲区大小的建议：n 为存储的对象定义必要内存的空间n 为经常执行的语句定义内存空间n 为了减少碎片和不能命中的情况，为使用大内存的对象预留内存空间n 将经常使用的对象保留在库缓存中n 将大的无名pl块改写成小的12. shared_pool_reserved_size参数为了给是用大内存的对象预留空间，是用本参数。一般建议不要超过sha

35、red_pool_size的10%。13. 查看shared_pool_reserved_size是否合适：如果request_failures列的值0而且持续增长，则证明小了，需要增加。如果request_miss=0或不增长，或者free_memory=shared_pool_reserved_size 最小 的50%，则说明大了，需要减小14. 将过程保留在库换存区举例：(1) 从v$db_object_cache中找到函数、过程或者包。（共享内存）10000）select * from v$db_object_cache where sharable_mem 10000and (typ

36、e=package or type=package body ortype=function or type=procedure) and kept=no;(2) 用dbms_shared_pool.keep来保留execute dbms_shared_pool.keep(dbms_standard); 对于匿名块或者语句，可以通过在v$sqlarea查找到address和hash_value两个字段，然后用dbms_shared_pool.keep(address,hash_value)来保留到库缓存区。15. 其它几个影响库缓存的参数n open_cursors该参数定义了用户进程的私有s

37、ql区的游标数。为了利用共享sql区，建议增加该值的大小。815和8.0.5默认是50，对于很多应用系统都不够用817默认300，一般够用了。游标应该被及时关闭。n cursor_space_for_time这时一个布尔类型的参数，默认是false。主要是用空间来换取时间。要设置该参数为true，应该保证有足够的内存v$librarycache表的reloads字段几乎为0。n session_cached_cursors这个参数对于用户反复解释/执行同样的语句的情况有利。可以通过v$sessstat视图的”session cursor cache hits”和” parse count (t

38、otal)”来查看设置是否合理。如果parse count (total)几乎为0，应该增加。这个参数默认是0。例如：select sid,value,name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and (name=session cursor cache hits or name=parse count (total)16. 数据字典缓存术语和调整n 工具：v$rowcachen gets列：对应类别的请求获取数n getmisses列：对应类别请求时导致缓存丢失数n 调整目标：getmisses之和除

39、以gets之和要小于15%select sum(getmisses)/sum(gets) from v$rowcache17. 用户全局区(user global area，uga)的调整如果服务器采用mts（多线程），那么uga就存在sga中，否则存在pga（program global area）。但是oracdle 8中引入了大池(large pool)的概念，如果配置了大池(large_pool_size)，uga将只使用共享池的一小部分内存，其余都从large pool中获得。18. 关于大池(large pool)大池通过初始化参数large_pool_size来配置，最小600k

40、b，最大根据系统而定，至少也可达到2gb。通过配置大池可以保证共享池主要用来缓存共享sql语句，避免性能下降，对于要求几百兆甚至更大内存的进程比较有利。如果大池配置不足，可能会导致ora-04031错误。大池也增加起实例启动时内存的需求。lesson 6:tuning the buffer cache1. using multiple buffer poolsoracle8 has three buffer pools:n keep: this pool is used to retain objects in memory that are likely to be reused. keep

41、ing these objects in memory reduces i/o operations.n recycle: this pool is used to eliminate blocks from memory that have little change of being reused. flusing these blocks from memory enables you to allocate the space that would be used by their cache buffers to other objects.n default: this pool

42、always exists.2. you can define each buffer pool using buffer_pool_name initialization.n the number of buffersn the number of lru latches allocated to the buffer poolfor eg:db_block_buffers=2000db_block_lru_latches=6db_buffer_keep=(buffers:500,lru_latches:2)db_buffer_recycle=(buffers:300,lru_latches

43、:1)3. defining multiple buffer poolsn db_block_buffers: defines the number of buffers for the instancen db_block_lru_latches: the number oflru latches for the entire database instance.(each pool defined takes a latch from this total)n buffer_pool_keep:n buffer_pool_recycle: used to define the buffer

44、 pool for discarding blocks4. the minimum number of buffers that must be allocated to each buffer pool is 50 per lru latch.5. enable multiple buffer pools:create index cust_idx . storage(buffer_pool keep);alter table customer storage(buffer_pool recycle);alter index cust_name_idx storage(buffer_pool

45、 keep);6. tuning goal of keep buffer poolthe goal of the keep buffer pool is to retain objects in memory, thus avoding i/o operations. the size of the keep buffer pool is computed by adding together the size of all objects dedicated to this pool.tool: analyze estimate statistics7. recycle buffer poo

46、l guildlinesn tuning goal: eliminate blocks from memory when transactions have completed.8. v$cachen v$cache is created by catparr.sqln it is intended for use with oracle parallel server(ops)n create a number of other views that are useful only for opsn maps extents in the data files to database obj

47、ectsn nedds to be rerun after new objects have been created9. determine the number of blocks in recycle pooln tune recycle pool offn run catparr.sqln use “select owner#,name,count(*) blocks from v$cache group by owner#,name”n sum the blocks for all objects ,then divide by 4.d10. v$sess_iov$sess_io p

48、rovides i/o statictises by session.eg:select io.block_gets,io.consistent_gets,io.physical_reads from v$sess_io io,v$session swhere s.audsid=userenv(sessionid)and io.sid=s.sid11. calculating the hit ratio for multiple poolsselect name,1-(physical_reads/(db_block_gets+consistent_gets) hit_ratio from v

49、$buffer_pool_statistics where db_block_gets+consistent_gets0;12. the following dictionary views have a buffer_pool column that indicates the default buffer pool for the given object:n user_,dba_segmentsn user_,all_,dba_clustersn user_,all_,dba_indexesn user_,all_,dba_tablesn user_,all_,dba_object_ta

50、blesn user_,all,dba_all_tables13. caching tables:when the server retrives blocks using a full table scan, the blocks go to the least recently used end of the lru list.the blocks will be used the next time a free block is needed(当下一次有别的需要块的时候，这些blocks就会被别的需要使用，因此不能共享)。so they are not available for ot

51、her processes. you can choose to cache whole tables at the most recently used(mru) end of the list. you can use this behavior if you do the following:n create a table using the cache clauseeg: create table test(a varchar2(10) cache;n alter a table using the cache clauseeg: alter table test cachen co

52、de the cache hint clause into a queryeg: select /*+ cache(test)*/ * from test14. lru latchesn lru latches regulate the least recently used(lru) lists used by the buffer cachen by default, the oracle server sets the number of lru latches to one-half the number of cpus, with a minimum of one.n each la

53、tch controls a minimum of 50 buffers.15. using lru latchesspace must be available in the buffer cache when new blocks are read into the buffer cache. deternning which blocks to flush from the buffer cache to make room for new blocks is regulated by a least recently used (lru) list. blocks that have

54、been not been used recently are candidates for being flushed for the cache.16. lru latch tuning goalsn ensure there are a sufficient number lru latches for the data buffer cache, so that contention between server processes is minimized.n balance the number of latches with the number of cpusn set one

55、 dbwn process for each latch.n 理解：应该保证有足够的data buffer，从而使争用最小化。lru闩的个数应该考虑cpu的个数，对于单个cpu，增加lru闩并不能改善性能。（用multiple pool除外）n 对于每一个lru闩都应该有一个对应的dbwn。这样也是为了减少争用，因为dbwn要将数据刷出data buffer，因为配置了多个lru闩，因此也应配置多个dbwn17. resolving lrulatch contention if the hit percentage for the lruis less that 99%:n increase

56、the number of lru by setting the parameter db_block_lru_latchesn the maximum number of latches is the lower of:-number of cpus x 2 x 3-number of buffers/50理解：当lru 命中率小于99%的时候应该考虑增加db_block_lru_latches的大小。该值不能超过cpu的6倍，也不能超过buffers/50。试验：假设一个单cpu的服务器，在init.ora中配置了如下的参数：db_block_lru_latches = 8buffer_pool_keep=(buffers:600,lru_latches:2)buffer_pool_recycle=(buffers:400,lru_latches:2)那么启动的时候就会出现ora-00378错误：18. diagnosing lru latch contentionthe v$latch and v$latchname views provide information regarding latches. the latch name is “cache buffers lru chai