Oracle性能优化之工具篇.docx
- 文档编号:24242508
- 上传时间:2023-05-25
- 格式:DOCX
- 页数:17
- 大小:131.55KB
Oracle性能优化之工具篇.docx
《Oracle性能优化之工具篇.docx》由会员分享,可在线阅读,更多相关《Oracle性能优化之工具篇.docx(17页珍藏版)》请在冰豆网上搜索。
Oracle性能优化之工具篇
ORACLE性能优化之工具篇
摘要:
介绍了常见的oracle性能优化所使用的工具
关键词:
性能优化
数据库优化的讨论可以说是一个永恒的主题。
但究竟什么是调优?
我们到底要调优什么?
谁参与调优?
为什么要调优?
以及如何判断调优是否成功?
优化人员按其角色划分可分为应用设计师、应用开发人员、数据库管理员、系统管理员
分别在应用设计和开发、数据库配置、部属应用、故障解决和调优阶段发挥各自的作用,
常见的比例见下图
相对应的我们调优的阶段也分为调优设计、调优应用、调优内存、调优I/O、调优竞争、调优OS。
优化工作的源头是设计,优化设计往往会有事半功倍的效果,不过这部分也是最难优化的,因为设计部分一旦确定将很难修改。
调优应用主要是SQL的优化,很多时候就是因为一条低效的SQL导致系统很慢的,也是可挖掘的地方之一。
内存参数的设置不合理、磁盘的I/O、资源的竞争等待、操作系统的性能不好都是常见的系统性能问题。
下图描述了内存、磁盘、操作系统、网络之间关联。
最基本的原则
如果某个部分不是瓶颈,就不要尝试去优化
优化可能会过头,注意协调整个系统的性能
优化是为系统提供足够的资源并且充分的使用资源,而不是无节制的扩充资源
优化有时候也意味着合理的分配或者划分任务
归纳来说两点:
一、提高系统的响应速度和时间。
二、在合理的响应时间里保证较高的可用性
一般性能优化的基本方法
1.设立合理的性能优化目标
我们做任何事情都会有个目标,没有了目标就会茫然,不知所措,性能优化也不例外。
有了目标还是不够的,就像我们平时说的要优化系统,要优化到什么程度呢?
所以说设立目标的最重要的一点是它们必须是可量化和可达到的。
例如对关键的某个过程或SQL花费了多少时间,要求在多少时间内完成,使用了多少资源,不能超过多少,最好用表格准确详细的记录下来。
2.测量并记录当前性能
利用相关工具进行量化
3.确定当前oracle的性能瓶颈(等待什么)
从Oracle动态性能视图v$system_event、v$session_event和v$session_wait中获得等待事件,进而找出影响性能的对象和sql语句
4.确定当前的os瓶颈
5.确定瓶颈原因
6.优化所需的部分(应用程序、数据库、i/o、争用、os、存储、网络等)
7.跟踪并实施更改过程
8.测量并记录当前性能
9.重复3-7,直到满足优化目标
“工欲善其事,必先利其器”,知道了方法我们就来具体说说优化可以用到哪些工具来帮我们解决问题
一、针对操作系统的
主要是监测OS的状况
(1)WindowsNT上的监控
使用控制面板-〉管理工具-〉性能即可
(2)UNIX/LINUX上的监控
使用通用性的工具,包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
二、数据库
(1)动态性能视图
(2)utlbstat.sql/utlestat.sql
(3)statspack
为了能够顺利安装和运行Statspack你可能需要设置以下系统参数:
job_queue_processes
该参数需要大于0
SQL>altersystemsetjob_queue_processes=6scope=both;
系统已更改。
timed_statistics
收集操作系统的计时信息,这些信息可被用来显示时间等统计信息、优化数据库和SQL语句。
SQL>altersystemsettimed_statistics=true;
Systemaltered
安装Statspack需要拥有SYSDBA(connect/assysdba)权限的用户登陆。
首先登陆到数据库,最好转到$ORACLE_HOME/RDBMS/ADMIN目录,这样我们执行脚本就可以方便些。
C:
\DocumentsandSettings\Administrator>sqlplus/nolog
SQL*Plus:
Release10.2.0.1.0-Productionon星期五5月910:
23:
192008
Copyright(c)1982,2005,Oracle.Allrightsreserved.
SQL>connsys/*****assysdba
已连接。
检查数据文件路径及磁盘空间,以决定创建数据文件的位置:
SQL>selectfile_namefromdba_data_files;
FILE_NAME
--------------------------------------------------------------------------
D:
\ORACLE\PRODUCT\10.2.0\ORADATA\LINBOPQM\USERS01.DBF
D:
\ORACLE\PRODUCT\10.2.0\ORADATA\LINBOPQM\SYSAUX01.DBF
D:
\ORACLE\PRODUCT\10.2.0\ORADATA\LINBOPQM\UNDOTBS01.DBF
D:
\ORACLE\PRODUCT\10.2.0\ORADATA\LINBOPQM\SYSTEM01.DBF
D:
\ORA_DATA\LINBO_BASE.DBF
已选择5行。
SQL>
创建存储数据的表空间,根据采样的频率和周期来决定表空间的大小,最小不能少于100M。
SQL>createtablespaceperfstat
2datafile'D:
\ORACLE\PRODUCT\10.2.0\ORADATA\LINBOPQM\perfstat.dbf'
3size200M
4extentmanagementlocal;
表空间已创建。
SQL>hostdir/wsp*
驱动器D中的卷是
卷的序列号是E8E4-FA84
D:
\oracle\product\10.2.0\db_1\RDBMS\ADMIN的目录
spauto.sqlspcpkg.sqlspcreate.sqlspctab.sqlspcusr.sql
spdoc.txtspdrop.sqlspdtab.sqlspdusr.sqlsppurge.sql
sprepcon.sqlsprepins.sqlspreport.sqlsprepsql.sqlsprsqins.sql
sptrunc.sqlspuexp.parspup10.sqlspup816.sqlspup817.sql
spup90.sqlspup92.sql
22个文件925,186字节
0个目录42,538,135,552可用字节
接下来我们就可以开始安装Statspack了。
这期间会提示你输入缺省表空间和临时表空间的位置,输入我们为perfstat用户创建的表空间和你的临时表空间。
SQL>@spcreate
SpecifyPERFSTATuser'sdefaulttablespace
输入default_tablespace的值:
perfstat
Usingperfstatforthedefaulttablespace
用户已更改。
用户已更改。
SpecifyPERFSTATuser'stemporarytablespace
输入temporary_tablespace的值:
temp
...CreatingPERFSTATuser...
ChoosethePERFSTATuser'spassword.
NotspecifyingapasswordwillresultintheinstallationFAILING
SpecifyPERFSTATpassword
输入perfstat_password的值:
perfstat
....
....
PL/SQL过程已成功完成。
createuserperfstatidentifiedbyperfstat
….
CreatingPackageSTATSPACK...
程序包已创建。
没有错误。
CreatingPackageBodySTATSPACK...
程序包主体已创建。
没有错误。
NOTE:
SPCPKGcomplete.Pleasecheckspcpkg.lisforanyerrors.
在这一步,如果出现错误,那么你可以运行spdrop.sql脚本来删除这些对象。
然后重新运行spcreate.sql来创建这些对象。
运行SQL*Plus,以具有SYSDBA权限的用户登陆:
SQL>@spdrop.sql
同义词已丢弃。
off;
视图已丢掉。
同义词已丢弃。
视图已丢掉。
同义词已丢弃。
用户已丢弃
NOTE:
SPDUSRcomplete.Pleasecheckspdusr.lisforanyerrors.
SQL>
测试安装好的statspack
运行statspack.snap可以产生系统快照,运行两次,然后执行spreport.sql就可以生成一个基于两个时间点的报告。
如果一切正常,说明安装成功。
SQL>executestatspack.snap
PL/SQLproceduresuccessfullycompleted.
SQL>executestatspack.snap
PL/SQLproceduresuccessfullycompleted.
SQL>@spreport.sql
生成报表后你就可以看到数据库的各种重要的信息了,包括数据库和实例名、快照执行的时间、当前的缓存(Cache)大小、负载情况概述(每秒/每事务)、实例的执行效率、最主要的前五个等待事件、等待事件的完全列表、当前在缓冲池(pool)中的SQL语句的信息、实例活动的统计、表空间和数据文件的I/O、数据高速缓存区(Bufferpool)的统计、回滚段或者撤销段的信息、锁存器(Latch)的活动、数据字典高速缓存的统计、库高速缓存的统计、SGA的统计、数据库启动时初始化参数文件init.ora的参数值。
也可以采用图表等形式展现出来,更加直观
最后是设置定时任务,开始收集数据。
可以使用spatuo.sql来定义自动任务。
这个任务定义了收集数据的时间间隔:
一天有24个小时,1440分钟,那么:
1/24HH每小时一次
1/48MI每半小时一次
1/144MI每十分钟一次
1/288MI每五分钟一次
我们可以修改spauto.sql来更改执行间隔,如:
dbms_job.submit(:
jobno,'statspack.snap;',
trunc(sysdate+1/48,'MI'),'trunc(SYSDATE+1/48,''MI'')',TRUE,:
instno);
然后我们执行spauto,这样我们就建立了一个每30分钟执行一次的数据收集计划。
你可以查看spauto.lis来获得输出信息:
SQL>@spauto
PL/SQLproceduresuccessfullycompleted.
一个statspack的报告不能跨越一次停机
(4)Oracle企业管理器(OEM)
OracleEnterpriseManager(Oracle企业管理器,简称OEM)是通过一组Oracle程序,为管理分布式环境提供了管理服务。
OEM包括了一组DBA工具,一个repository,以及一个图形化显示的控制台。
在10g中采用B/S结构,在IE上输入http:
//loaclhost:
1158/em就可以进入了,赶紧去体验一下吧。
三、应用级
(1)autotrace
autotrace是SQLPLUS中使用的工具,提供的信息不如SQL_TRACE/tkprof那么详细,但用起来较为方便。
在10g中默认开启了该功能,10g以下的版本需要进行配置
1.准备使用
1)创建PLAN_TABLE
详见explain的使用中创建PLAN_TABLE部份
2)创建PLUSTRACE角色
用SQLPLUS以sys用户身份登录,运行$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>@?
/sqlplus/admin/plustrce.sql
3)将)PLUSTRACE角色赋给需要autotrace的用户
SQL>grantPLUSTRACEtoSCOTT
2.使用方法
通过如下语句开启/停止跟踪
SETAUTOTRACEON|OFF
|ONEXPLAIN|ONSTATISTICS|TRACEONLY|TRACEONLYEXPLAIN
SETAUTOTRACEOFF----------------不生成AUTOTRACE报告,这是缺省模式
SETAUTOTRACEONEXPLAIN------只显示执行路径
SETAUTOTRACEONSTATISTICS--只显示执行统计信息
SETAUTOTRACEON-----------------包含执行计划和统计信息
SETAUTOTRACETRACEONLY------同setautotraceon,但是不显示查询输出
SQL>setautotracetraceonly
SQL>selecttable_namefromdba_tables;
已选择1595行。
执行计划
----------------------------------------------------------
Planhashvalue:
2278670594
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1723|279K|523(4)|00:
00:
07|
|*1|HASHJOINRIGHTOUTER||1723|279K|523(4)|00:
00:
07|
|2|TABLEACCESSFULL|SEG$|4500|49500|31(0)|00:
00:
01|
|*3|HASHJOINRIGHTOUTER||1606|243K|491(4)|00:
00:
06|
|4|TABLEACCESSFULL|USER$|58|174|2(0)|00:
00:
01|
|*5|HASHJOINOUTER||1606|238K|488(4)|00:
00:
06|
|6|NESTEDLOOPSOUTER||1606|225K|349(4)|00:
00:
05|
|*7|HASHJOIN||1606|218K|349(4)|00:
00:
05|
|8|TABLEACCESSFULL|USER$|58|174|2(0)|00:
00:
01|
|*9|HASHJOIN||1606|213K|346(4)|00:
00:
05|
|*10|HASHJOIN||1606|156K|202
(2)|00:
00:
03|
|11|MERGEJOINCARTESIAN||9|639|5(20)|00:
00:
01|
|*12|HASHJOIN||1|68|1(100)|00:
00:
01|
|*13|FIXEDTABLEFULL|X$KSPPI|1|55|0(0)|00:
00:
01|
|14|FIXEDTABLEFULL|X$KSPPCV|100|1300|0(0)|00:
00:
01|
|15|BUFFERSORT||9|27|5(20)|00:
00:
01|
|16|TABLEACCESSFULL|TS$|9|27|4(0)|00:
00:
01|
|*17|TABLEACCESSFULL|TAB$|1606|46574|196
(1)|00:
00:
03|
|*18|TABLEACCESSFULL|OBJ$|51354|1805K|143(5)|00:
00:
02|
|*19|INDEXUNIQUESCAN|I_OBJ1|1|5|0(0)|00:
00:
01|
|20|TABLEACCESSFULL|OBJ$|51365|401K|138
(2)|00:
00:
02|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("T"."FILE#"="S"."FILE#"(+)AND"T"."BLOCK#"="S"."BLOCK#"(+)AND
"T"."TS#"="S"."TS#"(+))
3-access("CX"."OWNER#"="CU"."USER#"(+))
5-access("T"."DATAOBJ#"="CX"."OBJ#"(+))
7-access("O"."OWNER#"="U"."USER#")
9-access("O"."OBJ#"="T"."OBJ#")
10-access("T"."TS#"="TS"."TS#")
12-access("KSPPI"."INDX"="KSPPCV"."INDX")
13-filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
17-filter(BITAND("T"."PROPERTY",1)=0)
18-filter(BITAND("O"."FLAGS",128)=0)
19-access("T"."BOBJ#"="CO"."OBJ#"(+))
统计信息
----------------------------------------------------------
201recursivecalls
0dbblockgets
2502consistentgets
0physicalreads
0redosize
44785bytessentviaSQL*Nettoclient
1551bytesreceivedviaSQL*Netfromclient
108SQL*Netroundtripsto/fromclient
5sorts(memory)
0sorts(disk)
1595rowsprocessed
SQL>
(2)Sqltrace
SQLTRACE是Oracle提供的用于进行SQL跟踪的手段,是强有力的辅助诊断工具。
在日常的数据库问题诊断和解决中,SQLTRACE是非常常用的方法。
跟踪自己的会话很简单
Altersessionsetsql_tracetrue|false
Or
Execdbms_session.set_sql_trace(TRUE);
如果跟踪别人的会话,需要调用一个包
execdbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest目录下可以找到或通过如下脚本获得文件名称
(适用于Win环境,如果是unix需要做一定修改)
SELECTP1.VALUE||'\'||P2.VALUE||'_ora_'||p.Spid||'.ora'Filename
FROMV$process p,
V$session s,
V$parameterP1,
V$parameterP2
WHEREP1.NAME='user_dump_dest'
ANDP2.NAME='db_name'
ANDp.Addr=s.Paddr
ANDs.Audsid=Userenv('SESSIONID')
原始的跟踪文件是很难读懂的。
需要使用oracle自带的tkprof命令行工具格式化一下。
SQL>$tkprofD:
\ORACLE\PRODUCT\10.2.0\ADMIN\LINBOPQM\UDUMP\hsjf_ora_1026.trcD:
\ORACLE\PRODUCT\10.2.0\ADMIN\LINBOPQM\UDUMP\hsjf_ora_1026.txt
这个就可以方便的阅读了。
可以在hsjf_ora_1026.txt文件中看到所有的sql语句执行次数,CPU使用时间等数据。
四、辅助性的相关文件
(1)告警日志文件
报警日志文件包含一个按时间排列的信息和错误日志
SQL>showparameterbackground_dump_dest
NAMETYPEVALUE
----------------------------------------------
background_dump_deststringD:
\ORACLE\PRODUCT\10.2.0\ADMIN\LINBOPQM\BDUMP
alert.log文件包含的信息有检查点起止时间、未完成检查点、执行归档的时间、实例恢复的起止时间、死锁超时、坏块信息及系统错误等等
我们查看报警日志主要查看内部错误(ORA-600)和块损坏错误、监视数据库操作、非系统定义的初始化参数,检查后有规律的删除和整理报警日志文件,日志文件过大会影响系统
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 性能 优化 工具