基于Oracle的tkprof数据库优化

基于Oracle的tkprof数据库优化

Tkprof

Tkprof是一个用于分析Oracle跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具，如果一个系统的执行效率比较低，一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出，从而找出有问题的SQL语句。Tkprof是Oracle自带的工具不需要另外安装。
现在项目中服务器主要是Windows系统和Linux系统。下面分别演示一下两个系统下如何使用Tkprof进行分析。
Windows系统需要PLSQL工具，Linux系统还需要SecureCRT工具。
。

以His业务中的住院结算为例：

1.打开你的HIS执行程序到要测试步骤：
2.用PL/SQL登陆SYS用户：

3.用PL/SQL的 工具–〉会话 找到本机His系统的数据库连接右键，设置为允许跟踪
4.执行His程序

5.等待跟踪程序执行完毕，关闭跟踪

6.利用tkprof把日志转化成txt文件
如果是Window系统远程桌面服务器，在ORCLE安装盘查找ORA.TRC文件，进入目录找到您所跟踪生成的.TRC文件如图：

允许 CMD进入服务器DOS命令行,在DOS命令下进入.TRC文件存储路径,利用TRPROF把TRC文件转换成TXT文件
输入命令：
CD D:\oracle\diag\rdbms\his\his1\trace
TRPROF HIS1_ORA_6524.TRC HIS1_ORA_6524.TXT
然后进到D:\oracle\diag\rdbms\his\his1\trace 找到到生成的.TXT文件如图：

如果是linux系统需要借助SecureCRT工具登入Linux服务器主机方法如下
进入软件后输入服务器ip然后点击connet

输入用户名和密码

进入界面
输入linux指令 进到 oracle10g的日志文件目录 oracle\admin\his\udump
输入指令 su – oracle
进到oracle用户
输入指令 cd /
进到根目录
输入指令 cd oracle
进到oracle 目录
输入指令 cd admin
进到admin目录
输入指令 cd his
进到his目录
输入指令 ls
查看当前目录下的文件 可以看到一下目录
Adump bdump cdump dpdump hdump pfie udump
跟踪产生的文件在udump下
输入指令cd udump
进到udump目录如图

查看日志文件
输入指令 1s
查看udump下的文件
输入指令 ll –rt
按时间排序

按时间查找到 你跟踪的日志
输入指令 tkprof his1_ora_28732.trc his_ora_28732.txt
用tkprof把日志转成txt
输入指令 more his_ora_28732.txt
查看txt文件 如图 crtl+ F 翻页

7.查看txt文件会看到此次His操作的所有sql语句及对语句的分析如下面截取一段有问题的语句。
UPDATE FIN_IPB_ITEMLISTADD SET INPATIENT_NO = :B2 , ITEM_CODE = :B1 ,
ITEM_GRADECODE = NVL((SELECT M.CENTER_ITEM_GRADE FROM FIN_COM_COMPARE M
WHERE
:B1 = M.HIS_CODE AND M.PACT_CODE = ‘2’ AND ROWNUM = 1), ‘3’), ITEM_GRADENAME
= NVL(DECODE((FUN_GETCOMPAREITEMGRADE(‘2’, :B1 )), ‘1’, ‘甲类’, ‘2’, ‘乙类’,
‘4’, ‘特检特治’, ‘自费’), ‘自费’), CENTER_FEECODE = NVL((SELECT
FIN_COM_SIITEM.FEE_CODE FROM FIN_COM_SIITEM WHERE FIN_COM_SIITEM.PACT_CODE =
‘2’ AND FIN_COM_SIITEM.ITEM_CODE = (SELECT FIN_COM_COMPARE.CENTER_CODE
FROM FIN_COM_COMPARE WHERE FIN_COM_COMPARE.PACT_CODE = ‘2’ AND
FIN_COM_COMPARE.HIS_CODE = :B1 AND ROWNUM = 1) AND ROWNUM = 1), ‘91’),
FYHZTYPE = NVL((SELECT FIN_COM_SITYPEINFO.FYHZTYPE FROM FIN_COM_SITYPEINFO
WHERE FIN_COM_SITYPEINFO.CENTERVALUE = (SELECT FIN_COM_SIITEM.FEE_CODE FROM
FIN_COM_SIITEM WHERE FIN_COM_SIITEM.PACT_CODE = ‘2’ AND
FIN_COM_SIITEM.ITEM_CODE = (SELECT FIN_COM_COMPARE.CENTER_CODE FROM
FIN_COM_COMPARE WHERE FIN_COM_COMPARE.PACT_CODE = ‘2’ AND
FIN_COM_COMPARE.HIS_CODE = :B1 AND ROWNUM = 1) AND ROWNUM = 1)), ‘qtf’),
CENTER_CODE = NVL((SELECT M.CENTER_CODE FROM FIN_COM_COMPARE M WHERE :B1 =
M.HIS_CODE AND M.PACT_CODE = ‘2’ AND ROWNUM = 1), ‘3’), CENTER_NAME =
NVL((SELECT M.CENTER_NAME FROM FIN_COM_COMPARE M WHERE :B1 = M.HIS_CODE AND
M.PACT_CODE = ‘2’ AND ROWNUM = 1), ‘3’), CENTER_PRICE = NVL((SELECT
M.CENTER_PRICE FROM FIN_COM_COMPARE M WHERE :B1 = M.HIS_CODE AND
M.PACT_CODE = ‘2’ AND ROWNUM = 1), ‘3’), MO_DATE = (SELECT MO_DATE FROM
MET_IPM_ORDER WHERE MO_ORDER = :B6 ), MO_TYPECODE = (SELECT TYPE_CODE FROM
MET_IPM_ORDER WHERE MO_ORDER = :B6 ), MO_TYPENAME = (SELECT TYPE_NAME FROM
MET_IPM_ORDER WHERE MO_ORDER = :B6 ), DECMPS_STATE = (SELECT DECMPS_STATE
FROM MET_IPM_ORDER WHERE MO_ORDER = :B6 ), DATE_BGN = (SELECT DATE_BGN FROM
MET_IPM_ORDER WHERE MO_ORDER = :B6 ) WHERE RECIPE_NO = :B5 AND SEQUENCE_NO =
:B4 AND TRANS_TYPE = :B3
call count cpu elapsed disk query current rows

---

Parse 2 0.00 0.00 0 0 0 0
Execute 1992 590.19 590.48 0 32003783 6108 1992
Fetch 0 0.00 0.00 0 0 0 0

---

total 1994 590.19 590.48 0 32003783 6108 1992

CALL： 每次SQL语句的处理都分成三个部分（Parse，Execute,Fetch）
Parse： 这步将SQL语句转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。
Execute： 这步是真正的由Oracle来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作,这步就只是确定选择的记录。
Fetch： 返回查询语句中所获得的记录，这步只有select语句会被执行。
COUNT: 这个语句被parse、execute、fetch的次数。
CPU： 这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。
ELAPSED： 这个语句所有消耗在parse、execute、fetch的总的时间。
DISK： 从磁盘上的数据文件中物理读取的块的数量。一般来说更想知道的是正在从缓存中读取的数据而不是从磁盘上读取的数据。
QUERY： 在一致性读模式下，所有parse、execute、fetch所获得的buffer的数量。 一致性模式的buffer是用于给一个长时间运行的事务提供一个一致性读的快照，缓存实际上在头部存储了状态。
CURRENT： 在current模式下所获得的buffer的数量。一般在current模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。
ROWS: 所有SQL语句返回的记录数目，但是不包括子查询中返回的记录数目。对于select语句，返回记录是在fetch这步，
对于insert、update、delete操作，返回记录则是在execute这步。

问题判断：

1. query+current/rows 平均每行所需的block数，太大的话（超过20）SQL语句效率太低
2. Parse count/Execute count parse count应尽量接近1，如果太高的话，SQL会进行不必要的reparse。
3. rows Fetch/Fetch Fetch Array的大小，太小的话就没有充分利用批量Fetch的功能，
   增加了数据在客户端和服务器之间的往返次数。在PL/SQL中可以用BULK COLLECT,SQLPLUS中的arraysize(缺省是15)
4. disk/query+current 磁盘IO所占逻辑IO的比例，太大的话有可能是db_buffer_size过小(也跟SQL的具体特性有关)
5. elapsed/cpu 太大表示执行过程中花费了大量的时间等待某种资源
6. cpu　OR　elapsed 太大表示执行时间过长，或消耗了大量的CPU时间，应该考虑优化
   7.　执行计划中的Rows 表示在该处理阶段所访问的行数，要尽量减少

我们可以主要看CPU 和ELAPSED 可以看到上面的sql 效率低，执行时间过长，消耗了大量的CPU时间需要优化。