Oracle 9i Statspack 高性能调整
Oracle 9i 和 10g 高性能调优
Oracle 9i 和10g 高性能调优介绍让我们从安装所需要的检查的事物开始来讨论oracle的优化。
调优环境调优的环境是什么？是一个你的调优努力能起作用的一种环境。
调整oralce数据库的所必需的东西⏹好多软件工具⏹训练有素的人⏹分段的测试环境⏹生产环境的一个副本○真实的和期望的生产环境。
在成长快速或者需求改变时，这些环境是经常不同的。
○经可能的使得数据库的大小/内容一致。
在不能完全满足这样的要求时，至少开发和测试数据库应该同生产数据库成比例○统计表是一样的吗？统计表可以拷贝，或者同生产数据库使用同样的时间间隔执行可用工具调整和监控数据库的优秀软件很多。
OEM有很多非常有用的小组件。
Spotlight擅长于对繁忙系统的视频和信息进行实时监控。
两者对调整数据库物理和SQL代码的性能分析都是非常有用的。
也有许多其他的工具可用。
在调整过程中最重要的工具是developer和administrators。
那是为什么你读这本书的原因。
最好的软件工具也意味着是最昂贵的，当然这也不是说较为便宜的工具是无用的了。
通常，越贵的软件为你所做的事情越多。
然而，有时候自动为你做了某些工作，你不理解其内部机制。
你的工具集未必比经过良好训练，经验丰富的数据库管理员和开发人员作得更好。
训练有素的人好的训练有其恰当的地方。
作为系统管理员或者开发者，数据库管理员趋向于有root权限。
每一种训练都有其优缺点。
开发者除了编码SQL，创建数据模块之外，所了解的知识越来越多。
系统管理员对unix之类的操作系统知识具有广泛的了解，关注的是oracle数据库的物理存储的调优。
开发者关注的是数据库模型和创建更为高效的SQL代码方面的优化。
幸的是，事情不总是如此。
有时候开发者趋向于把调优SQL代码和数据模型负担看着是数据库管理的负责范围。
这样就会导致混乱。
分段的环境你需要尽可能多的实验环境。
作为DBA，你不能期望在一个在线的生产数据库上进行调优工作。
oracle数据库性能调整与优化
Oracle9i数据库性能调整与优化V1.0在数据库成熟应用的时代，数据库的性能优化已经演变为一项相当严密的系统工程。
作为企业IT基础设施的核心部件之一，数据库并不是孤立的系统，它与网络、操作系统、存储等硬件系统紧密相连，这种与其他IT部件的多重连接特性决定了数据库性能优化是一门综合技术。
数据库性能优化的实现路径和IT系统管理架构越来越密不可分。
Oracle9i数据库在内部特性方面有着非常大增强，其中一个最令Oracle DBA兴奋莫过于可以动态设置全部Oracle SGA控制参数。
与8i不同是，原来都将初始化参数放到一个文本文件中，并且在数据库启动时候读取，Oracle9i却可以通过ALTER DATABASE 和ALTER SYSTEM命令复位全部Oracle参数。
在9i前，如果想对Oracle数据库处理模式作一些改变话，Oracle管理员必须关闭数据库并且重新设置INIT.ORA文件中参数，然后重新启动数据库。
对于白天使用OLTP 模式运作，晚上切换到数据仓库模式Oracle数据库来说，这种重新设置是经常做。
对于需要停止和重新启动Oracle数据库来修改参数来说，Oracle9i在这方面有明显加强，它令实现数据库连续可用目标变得更加简单。
定义：SGA：（System Global Area）是Oracle Instance的基本组成部分，在实例启动时分配。
是一组包含一个Oracle实例的数据和控制信息的共享内存结构。
主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。
它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
理论上SGA可占OS系统物理内存的1/2——1/3。
SGA几个很重要的特性：1、SGA的构成——数据和控制信息，我们下面会详细介绍；2、SGA是共享的，即当有多个用户同时登录了这个实例，SGA中的信息可以被它们同时访问（当涉及到互斥的问题时，由latch和enquence控制）；3、一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区。
Oracle 9i 数据库性能调优技术-les02
Maintenance of the Alert Log File
• •
The alert log file consists of a chronological log of messages and errors. Check the alert log file regularly to:
2-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Using Oracle Expert
2-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Statspack
• • • • •
Installation of Statspack using the spcreate.sql script Collection of statistics execute statspack.snap Automatic collection of statistics using the spauto.sql script Produce a report using the spreport.sql script To collect timing information, set TIMED_STATISTICS = True
2-7
Copyright © ll rights reserved.
Oracle Enterprise Manager Console
2-9
Oracle9i性能调整
create or replace function GET_SQL(V_SQLADDRESS in RAW, V_SQLHASHVALUE in NUMBER) return varchar2 is Result varchar2(2000); CURSOR C1 IS SELECT A.SQL_TEXT FROM V$SQLAREA A WHERE A.ADDRESS=V_SQLADDRESS AND A.HASH_VALUE=V_SQLHASHVALUE; begin FOR REC IN C1 LOOP RESULT:=RESULT||REC.SQL_TEXT; END LOOP; return(Result); end GET_SQL;
索引与性能
基于函数的索引 Create index ind_name on animal (upper(animal_name)); CBO, query_rewrite_enable=true query_rewrite_integrity=trusted 分析相应的表，列，所有索引；
Bad sql的定位
索引与性能
Index的原理（通常指B树） 根据键值来确定rowid，根据rowid来定位记录 Index的平衡性 DML对index的影响，而键值不会被删除。 Index的效率 可选择性的问题 重组的必要性 效率以及DML的影响 索引组织表（IOT）原理 既是表又是索引。 适用场合：静态的所有列皆为pk的大表
如何跟踪一个session
10046 event(level n)+tkprof n=4:enable sqltrace+variable bind; n=8:enable sqltrace+wait event; n=12: n=4+n=8
Oracle9i性能调整
索引与性能
IOT创建语句 create table t1 (x int primary key,y int,x int) organization index; 减小IO数量，但是bug较多。 位图index 适合选择性不强的情况，专为DSS系统设计。锁定块 ，不适合OLPT系统。 加速创建INDEX nologging－注意备份 并行创建
系统资源的调整－IO
IO系统的监控 iostat -xn 以看出系统在单位时间内的读写次数以及吞吐能力 Dbfile的io控制 CREATE TABLE sum_read_writes AS SELECT SUM(phyrds) phys_reads, sum(phywrts) phys_wrts FROM V$FILESTAT; TTITLE ' DATAFILE DISK I/O REPORT' COLUMN name FORMAT a35 COLUMN phyrds FORMAT 999,999,999 COLUMN phywrts FORMAT 999,999,999 COLUMN read_pct FORMAT 999.99 COLUMN write_pct FORMAT 999.99 SELECT name, phyrds, phyrds * 100 / srw.phys_reads read_pct, phywrts, phywrts * 100 / srw.phys_wrts write_pct FROM sum_read_writes srw, v$datafile df, v$filestat fs WHERE df.file# = fs.file# ORDER BY phyrds desc; DROP TABLE sum_read_writes; clear columns TTITLE off
Oracle 9i 整体性能优化概述草稿之一 调整争用
2.2.2是否存在latch争用
select event,total_waits,time_waited
from v$system_event
where event = ‘latch free’;
如：
EVENT TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
? library cache：需要优化library cache。
? cache buffers LRU chain：管理database cache buffers上的LRU List上的块，自由缓冲区列表。此争用有两种可能：
- 导致严重的全部扫描的SQL语句或执行计划。SQL需要优化。
- 脏缓冲区写盘database writer未能跟上I/O请求。优化磁盘I/O。
比如如果应用程序有许多频繁插入行的用户，在试图访问该表的free list就可能会经历等待。
2.3.2是否存在free list争用
select event,total_waits,time_waited from v$system_event
where event = ‘buffer busy waits’;
而lock，在遇到问题的时候，可作为维护参考，平时不进行太多的维护（或从应用上考虑优化）。（除了定期去$ORACLE_HOME/admin/$ORACLE_SID/udump查看死锁情况外）
2.2诊断latch竞争
2.2.1概念
Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。
Oracle中性能调整
Oracle中性能调整一、性能调整的目标1、加快响应时间。
存取尽量少的块，缓存频繁使用的块。
2、提高吞吐量和连接量。
3、减少等待和瓶颈、减少paging和swapping。
以下将影响性能：使用中间件封装、频繁的登录退出、Cursor的管理(cursor_sharing),表的join二、性能调整工具Oracle Enterprise Manager相关工具： Lock monitorPerformace OverviewTop sessions*查看用户锁Performance manager ->锁数->用户类型锁，等待锁->内存->sga概览->共享池%可用,sga命中百分比->IO平均成本(看哪个文件IO多)->加载->会话数(活动)->数据库例程->内存排序百分比->等待事件->用户事件Statspack$oracle_home/rdbms/admin/spcreate.sql$Sqlplus perfstat/口令Sql>Exec statspack.snap 手工获取快照Sql>@spauto 自动获取快照Sql>@spreport输入snap id号，即可得出报表。
Utlbstat.sql/utlestat.sql (conn as sysdba)三、性能调整步骤每一个数据库必须存储一个参考基线(baseline)，以后的report与baseline比较可以看出什么地方性能下降了。
调整时一次应用一个假设，达到目标时，产生一个新的baseline。
通常步骤：1、定义问题2、收集统计数据（操作系统和Oracle的统计）3、检查并画出性能差的部分4、实施假定的数值或参数5、统计检查问题是否已经解决，重复3-5。
实际执行步骤：1、检查alert日志和用户trace文件；2、检查参数文件（查不正确或欠妥的参数）3、检查内存、I/O、CPU使用，标志不正常的进程、语句4、分析有效的CPU/总CPU使用5、确定哪个资源是瓶颈6、平衡安全性和性能的关系注意：Incomplete checkpoints四、参数Log_checkpoint_to_alertSessions ：会话数mts_sessionsprocesses ：进程数Sql_traceTimed_statistics、statistics_level= TYPICALExecute dbms_system.set_sql_trace_in_session(8,12,true)Db_cache_adviceLog_checkpoint_timeout：脏数据块保留的最长时间Log_checkpoint_interval：用于恢复的日志块的最大数目Fast_start_mttr_target：平均例程恢复时间Pga_aggregate_target：专用服务器进程PGA五、执行语句Sql>select username,name,value from v$statname,v$session s,v$sesstat t Where s.sid=t.sid and n.statistic#=t. statistic#and s.type=’USER’ and ername is not nulland =’Session pga memory’and t.value>3000;⏹占用3000以上PGA字节的会话Sql>select phyrds,phywrts, from v$datafile d,v$filestat fwhere d.file#=f.file# order by Performance Manager→ I/O⏹看哪个数据文件较忙（有无全表扫描？建立索引？分开存放？）Select name,value from v$sysstat where name like ‘%table scan%’;Select sid,serial#,opname,(sofar/totalwork)*100 as percent_complete from v$session_longopswhere (sofar/totalwork)*100!=100看长查询Db_file_multiblock_read_count*db_block_sizeAlter table employees allocate extent (datafile ‘xxx.dbf’ size 10m);optimizer_mode = CHOOSE⏹回滚段的当前活动事务:Sql>SELECT ername,t.xidusn,t.ubafil,t.ubablkfrom v$session s,v$transaction t WHERE s.taddr=t.addr; -- oracle7 Sql> SELECT ername,t.xidusn,t.ubafil,t.ubablk,ed_ublkFROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr; --oracle9i Sql>select segment_name,max_extents,PCT_INCREASEfrom dba_rollback_segs;Sql>SELECT ,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status FROM v$rollname n,v$rollstat s WHERE n=n;六、事件：V$session_event, v$session_wait, v$system_eventBuffer busy waitsSQL>select event,total_waits from v$system_eventWhere event in (‘free buffer waits’,’buffer busy waits’)SQL> select name,value from v$sysstatwhere name=‘free buffer inspected’SQL>select * from v$waitstatwhere class in ('data block','undo header','undo block'‘segment header’)可用增加索引，自动段管理，增加freelists或用自动段管理segment header>0。
Oracle 9i 数据库性能调优技术-les13
•
Pros
– Are less likely to extend dynamically – Deliver small performance benefit – Enable you to read the entire extent map with a single I/O operation
SQL> EXECUTE dbms_stats.gather_table_stats > ('HR','EMPLOYEES'); PL/SQL procedure successfully completed. SQL> SELECT num_rows, blocks, empty_blocks as empty, 2 avg_space, chain_cnt, avg_row_len 3 FROM dba_tables 4 WHERE owner = 'HR' 5 AND table_name = 'EMPLOYEES'; NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN -------- ------ ----- --------- --------- ----------13214 615 35 1753 0 184
13-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Block Size
Minimize block visits by: • Using a larger block size • Packing rows tightly • Preventing row migration
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Lesson 3 安装和配置STATSPACK1．安装STATSPACK步骤8.1.7之前的statspack安装1）决条件：✓创建专用表空间，如perfstat✓作为SYS运行catdbsyn.sql✓作为SYS运行dbmspool.sql✓对perfstat表空间分配至少180M磁盘空间✓运行statscre.sql2）8.1.7之后STATSPACK安装✓创建专用表空间，如perfstat✓运行spcreate.sql✓如果运行过程中出错，重建的时候应该先运行spdrop.sql删除内容，检查出错原因，纠正后再进行。
3）测试安装是否正常运行。
调用spreport.sql来测试。
对于8.1.7前调用statsrep.sql (需要完善具体方法)WZH 9iOCP>exec statspack.snap;WZH 9iOCP>exec statspack.snap;WZH 9iOCP>@?\rdbms\admin\spreport.sql注意选择正确的spreport.sql的路径。
4）规划自动STATSPACK数据搜集。
运行?\rdbms\admin\spauto.sql可以设置自动搜集statspack 数据。
这个脚本创建了一个作业。
为了运行这个作业，要保证job_queue_processes参数大于0，而且要使用statspack所属用户来执行。
例如下面的脚本设置每1小时进行一次statspack：variable jobno number;variable instno number;beginselect instance_number into :instno from v$instance;dbms_job.submit(:jobno, 'statspack.snap;',trunc(sysdate+1/24,'HH'), 'trunc(SYSDA TE+1/24,''HH'')', TRUE);commit;end;/2．STA TSPACK的配置和维护1）查看STATSPACK快照：WZH 9iOCP>select snap_id,dbid,to_char(snap_time,'yyyy/mm/dd hh24:mi:ss') snaptime,snap_level from stats$snapshot;2）STA TSPACK搜集级别✓LEVEL 0：一般性能统计✓LEVEL 5：增加SQL语句，将SQL语句放入stats$sql_summary中✓LEVEL 10：增加子锁存统计。
除了0和5的所有内容之外，还会把附加的子锁存信息放入stats$latch_children表中。
这种级别的统计应该在接收Oracle技术支持的情况下进行。
✓修改方法：execute statspack.snap(i_snap_level=>0,i_modify_parameter=>‟true‟);✓动态修改搜集级别可以不加i_modify_parameter，例如：exec statspack.snap(I_snap_level=>10)3）STA TSPACK搜集范围快照域值只应用于在stats$sql_summary表中获取的SQL语句，用来控制stats$sql_summary 表的快速增长。
✓executions_th：SQL语句执行的数量（默认100）✓disk_reads_th：sql语句执行的磁盘读入数量（默认1000）✓parse_calls_th：sql语句执行的解析调用数量（默认1000）✓buffer_gets_th：sql语句执行缓冲区获取的数量（默认10000）新的Oracle9i SQL域值：✓p_def_num_sql：sql语句数量的阈值✓p_def_executions_th：sql语句执行程序数量的阈值✓p_def_parse_calls_th：sql语句分析调用数量的阈值✓p_def_disk_reads_th：磁盘读操作数量的阈值✓p_def_sharable_mem_th：sql可共享内存数量的阈值✓p_def_version_count_th：sql子句游标通过statspack..modify_statspack_parameter函数可以改变阈值的默认值。
阈值存放在stats$statspack_parameter中。
改变阈值举例：sql>exec statspack.modify_statspack_parameter(i_buffer_gets_th=>20000);4）3．将STA TSPACK用于ORACLE8.0到8.15STA TSPACK随ORACLE8.1.6正是引入的，如果需要用于之前版本，需要做以下修正：✓statscbps.sql✓statsrep80.sql上面这些sql在D:\STUDY\ORACLE\statscode目录下4．删除旧有的STATSPACK快照如果快照过旧，可能无法使用，可以使用sppurge.sql来删除指定的快照，也可以手动删除，例如：SQL>delete from snats$snapshot where snap_id<10000;上面者条语句会自动删除stats$sql_summary中的数据，因为statspack可以使用ON CASCADE DELETE 来实现外键参考一致。
SQL>delete from stats$sql_summary where snap_time <sysdate-180;上面这条语句删除180天前的快照。
Lesson 4 STATSPACK表中的数据1．statspack的局限性statspack并不能获得全部信息，下面几个部分用stataspack的标准并不能获得，不过可以从本书提供的扩展脚本中来获得。
✧服务器统计✧磁盘统计✧对象统计2．stats$sql_statistics这个表用来提供一个块照中所有被检查的SQL的一个总计数据。
SQL> desc stats$sql_statistics名称空? 类型----------------------------------------- -------- ---------SNAP_ID NOT NULL NUMBER(6)DBID NOT NULL NUMBERINSTANCE_NUMBER NOT NULL NUMBERTOTAL_SQL NOT NULL NUMBERTOTAL_SQL_MEM NOT NULL NUMBERSINGLE_USE_SQL NOT NULL NUMBERSINGLE_USE_SQL_MEM NOT NULL NUMBER3．stats$sqltext：该表存储存在stats$sqltext中的SQL源代码。
4．stats$latch_misses_summary该表记录Oracle数据中的锁存失败。
当我们观察锁存时，必须记住有两种类型锁存：1是愿意等待型锁存，2是立即型锁存。
愿意等待型会反复试图获得锁存，因为锁存无法获得而导致的休眠次数需要引起我们注意。
锁存获取失败次数也要严格注意，因为这意味着资源短缺。
5．stats$sgastat_summary （只适用于Oracle8i）该表包括了一些Oracle数据库系统范围内的重要值，在9i中已经作废6．stats$sql_summary这是statspack工具中最重要的表之一。
该表提供了所有sql语句的内容以及各个语句所使用资源的详尽描述。
7．stats$rollstat：记录Oracle会滚段活动信息。
这个表的数据对于会滚段尺寸和初始化参数调整有用，但是完成调整之后旧很少再用到了。
8．stats$latch：记录锁信息。
在Oracle内部中，锁存被用来串行化事务处理。
9．stats$latch_children只有在进行级别10搜集的时候才会产生stats$latch_children表10．Stats$librarycache11．Stats$waitstat如果会议数据库正在面临资源短缺，stats$waitstat表中描述的系统等待统计就会非常有用。
12．Stats$enqueuestat：队列等待信息13．Stats$sysstat与v$sysstat结构一致，具有200多个独立统计名称，只有少数与oracle性能有关14．Stats$buffer_pool（8i之前）该表用来保存与各个数据缓冲池中缓冲区数量有关的基本信息。
15．Stats$buffer_pool_statistics16．Stats$filestatxs重要的调整表之一。
记录了各个数据文件的I/O情况。
17．Stats$system_event系统事件表18．Stats$session_event会话事件表19．Stats$bg_event_summary所有的数据库实例的后台进程等待事件20．Stats$rowcache_summary搜集IDLM中的活动是该表的目的。
该表会记录OPS(RAC)环境中各个实例的块使用情况。
21．Stats$sgaxs该表用于对单独的rac环境中使用的多个SGA进行交叉引用Lesson 5 扩展STATSPACK搜集服务器统计信息1．vmstat 工具vmstat 工具是最常见的UNIX监视工具，它在大多数的UNIX版本中都有（在IRIX中叫做osview）。
Vmstat 可以用来查看cpu、内存方面的信息。
下面是在aix下使用vmstat的一个输出：% vmstat 10 5kthr memory page faults cpu----- ----------- ------------------------ ------------ -----------r b avm fre re pi po fr sr cy in sy cs us sy id wa2 1 68028 637 0 0 0 0 1 0 130 1992 85 13 18 69 12 0 68372 259 0 0 0 0 0 0 120 21803 146 40 60 0 02 1 68095 505 0 0 0 4 21 0 135 21639 147 40 60 0 02 0 68126 474 0 0 0 0 0 0 118 22586 142 39 61 0 02 0 68779 171 0 0 2 40 188 0 125 21911 143 36 64 0 01）在vmstat中可以得到什么？✓r（运行队列）运行队列展示了正在执行和等待CPU资源的任务数量。