做的项目中有对数据库insert操作,在批量执行(100tps)的时候,insert操作会变得很慢,使用10046跟踪信息如下:
insert into OPERUSERNOTEINFO(MSISDN, OPERTIME, OPERTYPE, MODIFYTYPE,PASSWORD,
PAYTYPE, USERTYPE, USERSTATE)
values
(:1, :2, :3, :4, :5, :6, :7, :8)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 153 0.00 0.00 0 0 0 0
Execute 154 0.03 40543.05 115 199 2063 154
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 307 0.03 40543.05 115 199 2063 154Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 94 Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer deadlock 6 0.00 0.00
buffer busy waits 6 1.00 5.50
********************************************************************************从10046日志上看,Execute elapsed值很高,应该是在等待某种资源,而且update操作没有这个现象:
UPDATE BASETAB SET PAYTYPE = :1, PAYTYPESWITCHTIME = :2, PREPAIDSYSTEMID = :3
WHERE
MSISDN = :4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 154 0.00 0.00 0 0 0 0
Execute 154 0.02 0.00 1 462 464 154
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 308 0.02 0.00 1 462 464 154Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 94 Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE BASETAB (cr=3 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_BASETAB (cr=3 pr=0 pw=0 time=0 us cost=2 size=36 card=1)(object id 83214)********************************************************************************
请问下这种情况下可能是由于那些资源阻塞造成的,要检查和更改数据库的那些参数设置,对了,使用的是oracle 11G的数据库
insert into OPERUSERNOTEINFO(MSISDN, OPERTIME, OPERTYPE, MODIFYTYPE,PASSWORD,
PAYTYPE, USERTYPE, USERSTATE)
values
(:1, :2, :3, :4, :5, :6, :7, :8)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 153 0.00 0.00 0 0 0 0
Execute 154 0.03 40543.05 115 199 2063 154
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 307 0.03 40543.05 115 199 2063 154Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 94 Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer deadlock 6 0.00 0.00
buffer busy waits 6 1.00 5.50
********************************************************************************从10046日志上看,Execute elapsed值很高,应该是在等待某种资源,而且update操作没有这个现象:
UPDATE BASETAB SET PAYTYPE = :1, PAYTYPESWITCHTIME = :2, PREPAIDSYSTEMID = :3
WHERE
MSISDN = :4
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 154 0.00 0.00 0 0 0 0
Execute 154 0.02 0.00 1 462 464 154
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 308 0.02 0.00 1 462 464 154Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: ALL_ROWS
Parsing user id: 94 Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE BASETAB (cr=3 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_BASETAB (cr=3 pr=0 pw=0 time=0 us cost=2 size=36 card=1)(object id 83214)********************************************************************************
请问下这种情况下可能是由于那些资源阻塞造成的,要检查和更改数据库的那些参数设置,对了,使用的是oracle 11G的数据库
解决方案 »
- 高分求助一个存储过程问题
- 关于Oracle字符串截取的问题
- 急!超级怪问题!!oralce10g当机器打开一段时间才识别出SID
- 请问,如何通过internet同步两个oracle数据库
- oracle UTL_FILE包如何实现追加文件内容
- Oracle数据库的Date类型字段中存放的日期格式是怎样的?
- 欢迎bzszp(SongZip)和ATsuwu(苏武)两位来高人来接分!!
- 100分救命!!!
- 高分求教:CLOB 和 long 类型选择问题
- 远程连接oracle问题“The Network Adapter could not...............
- 我是从同事那里考得Oracle,所有密码什么的不知道。求救
- 请高手帮忙优化一个SQL语句
从buffer busy waits 6 1.00 5.50来看,似乎热块争用造成
批量执行(100tps)是什么意思?
除了这个OPERUSERNOTEINFO表,其它表也这样慢吗?
这个表数据量有多大,索引情况怎样?
该表原来大概有1300w数据,后来清掉了,重新测空表的时候也很慢,原来表有索引,后来为了看是否是索引问题把索引删掉了,速度有小量提升,但不明显,
还有只有这个表这么慢,其他表速度都是正常的