环境aix 5308+oracle10204
运行一个查看数据字典的脚本(如统计表空间使用率)发现速度超慢(一般都超过4,5分钟) 而同一配置的另个库 10秒左右能出结果,执行计划一样!
异常库系统资源topas:
Disk Busy% KBPS TPS KB-Read KB-Writ PgspOut 0 % Client 4.4
hdisk2 72.5 892.3 111.5 892.3 0.0 PageIn 0
hdisk18 68.0 860.3 107.5 852.3 8.0 PageOut 0 PAGING SPACE
hdisk14 49.5 852.3 106.5 852.3 0.0 Sios 0 Size,MB 16384
hdisk15 68.5 852.3 106.5 852.3 0.0 % Used 9.9
hdisk4 67.5 852.3 106.5 852.3 0.0 NFS (calls/sec) % Free 91.1
hdisk19 72.0 852.3 106.5 852.3 0.0 ServerV2 0
hdisk17 49.5 844.3 105.5 836.3 8.0 ClientV2 0 Press:
hdisk8 51.0 841.8 108.5 832.3 9.5 ServerV3 0 "h" for help
hdisk6 41.5 840.8 106.0 840.3 0.5 ClientV3 0 "q" to quit
hdisk9 50.5 840.3 105.0 836.3 4.0
得出这个库pv读写有点频繁然后对数据库性能进行的分析,发现很多语句执行效率较低:如
FINDING 1: 47% impact (202436 seconds)
--------------------------------------
SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 13% benefit (54858 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8y4ax7264yxm2".
RELEVANT OBJECT: SQL statement with SQL_ID 8y4ax7264yxm2 and
PLAN_HASH 342780786
select transaction_id,consumer_id,fee,operate_flag,type,nvl(rtn,'0'),
decode(priority,'2','1',priority) from ( select
a.transaction_id,'86'||a.consumer_id
consumer_id,a.fee,a.operate_flag,'EP' type,a.rtn,nvl(d.priority,'99')
priority from dbinter.ep_transaction a,dcustmsg b,dcustmsgadd
c,dbinter.EP_PRIORITY d where a.consumer_id=c.field_value and
c.id_no=b.id_no and a.optcode=d.optcode(+) and (a.operate_flag = '0'
or (a.operate_flag = '1' and a.rtn = '4')) and
c.field_code='10000195' and b.sm_code in ('','ny','wy') order by
d.priority ) where rownum < 60
RATIONALE: SQL statement with SQL_ID "8y4ax7264yxm2" was executed 644
times and had an average elapsed time of 84 seconds.
请问我怎样判断是数据库运行较慢导致这些sql语句运行速度较低,还是由于sql执行效率太低,导致数据库运行较慢!
运行一个查看数据字典的脚本(如统计表空间使用率)发现速度超慢(一般都超过4,5分钟) 而同一配置的另个库 10秒左右能出结果,执行计划一样!
异常库系统资源topas:
Disk Busy% KBPS TPS KB-Read KB-Writ PgspOut 0 % Client 4.4
hdisk2 72.5 892.3 111.5 892.3 0.0 PageIn 0
hdisk18 68.0 860.3 107.5 852.3 8.0 PageOut 0 PAGING SPACE
hdisk14 49.5 852.3 106.5 852.3 0.0 Sios 0 Size,MB 16384
hdisk15 68.5 852.3 106.5 852.3 0.0 % Used 9.9
hdisk4 67.5 852.3 106.5 852.3 0.0 NFS (calls/sec) % Free 91.1
hdisk19 72.0 852.3 106.5 852.3 0.0 ServerV2 0
hdisk17 49.5 844.3 105.5 836.3 8.0 ClientV2 0 Press:
hdisk8 51.0 841.8 108.5 832.3 9.5 ServerV3 0 "h" for help
hdisk6 41.5 840.8 106.0 840.3 0.5 ClientV3 0 "q" to quit
hdisk9 50.5 840.3 105.0 836.3 4.0
得出这个库pv读写有点频繁然后对数据库性能进行的分析,发现很多语句执行效率较低:如
FINDING 1: 47% impact (202436 seconds)
--------------------------------------
SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 13% benefit (54858 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8y4ax7264yxm2".
RELEVANT OBJECT: SQL statement with SQL_ID 8y4ax7264yxm2 and
PLAN_HASH 342780786
select transaction_id,consumer_id,fee,operate_flag,type,nvl(rtn,'0'),
decode(priority,'2','1',priority) from ( select
a.transaction_id,'86'||a.consumer_id
consumer_id,a.fee,a.operate_flag,'EP' type,a.rtn,nvl(d.priority,'99')
priority from dbinter.ep_transaction a,dcustmsg b,dcustmsgadd
c,dbinter.EP_PRIORITY d where a.consumer_id=c.field_value and
c.id_no=b.id_no and a.optcode=d.optcode(+) and (a.operate_flag = '0'
or (a.operate_flag = '1' and a.rtn = '4')) and
c.field_code='10000195' and b.sm_code in ('','ny','wy') order by
d.priority ) where rownum < 60
RATIONALE: SQL statement with SQL_ID "8y4ax7264yxm2" was executed 644
times and had an average elapsed time of 84 seconds.
请问我怎样判断是数据库运行较慢导致这些sql语句运行速度较低,还是由于sql执行效率太低,导致数据库运行较慢!
解决方案 »
- 请教高手~程序生成oracle触发器的怪异问题
- 关于linux下用OTL库C++开发ORECLE数据库程序的问题,急!
- 查询的问题sum(x.count)
- 报错oralce database 11.2.0.3.0 install on solaris11.1 sparc
- 求一句统计产量的SQL语句
- 在哪里可以下载oracle 8i 或8i的其他版本
- 从SQLPLUS语句转换成SVRMGRL语句的问题??
- 建视图
- 请问在那里可以下载到ORACLE的书籍
- 使用impdp导入时报错,ora39082报了好多个,导入数据库结束后发现一个表都没有导入进来,这个要怎么解决,请大神告知
- 请教一个UPDATE 的问题?
- decode问题
select count(*) from dba_free_space
参考别人的经验 是否是 sga等设置的太小了?