其实有个另外个程序也UPDATE 该表的行! 次数也很频繁! 所以TOP事件里排行第2就是 TX-LOW LOCK CONTENTS等待事件!存储过程里的UPDATE是非循环的,只更新一行数据!
解决方案 »
- 安装asm 出错,大家帮忙
- 大数据量比对方法
- 请问我用企业管理器登陆时,选择身份时选择:sysdba和normal有什么区别吗,用户名不都是system吗?
- 无法修改与非键值保存表对应的列(怎么办?急)
- 【请问】相同值取其一,distinct与least/greatest哪个效率高?数据100w左右.
- 请教这样一个oracle的存储过程怎样写?在jsp/servlet中怎样调用
- 请问:什么是 dirty buffers
- oracle9安装问题运行 runinstall 出现cannot execute binary file ?
- 请教一个统计SQL语句
- is not a valid date and time Oracle时间类型问题报错
- oracle权限管理问题
- 请问各个大侠,如何把oracle8i中的某个数据库恢复到oracle 10g中啊,oracle 10g数据库里没有8i中的那个数据库
frommobile IN VARCHAR2,
tomobile IN VARCHAR2,
paymobile IN VARCHAR2,
content IN VARCHAR2,
COST IN FLOAT,
USER IN VARCHAR2,
PASSWORD IN VARCHAR2,
sendoutflag IN VARCHAR2,
epid IN VARCHAR2,
postfix IN VARCHAR2,
payorgid IN INTEGER,
orgid IN INTEGER,
userid IN INTEGER,
useragent IN VARCHAR2,
priority IN INT,
pricetype IN VARCHAR2,
productid IN VARCHAR2,
presendtime IN VARCHAR2,
tablename IN VARCHAR2,
item IN INT,
MESSAGE OUT INT
)
AS
msgid NUMBER;
agent_price FLOAT;
user_price FLOAT;
user_balance FLOAT;
org_balance FLOAT;
agent_balance FLOAT;
org_credit FLOAT;
user_credit FLOAT;
tempmobilestr STRING (4000);
tempmobile STRING (20);
tempagentstr STRING (4000);
tempagent STRING (20);
ipot NUMBER;
apot NUMBER;
iexit NUMBER;
sqlstr STRING (4000);
--messagestr STRING (4000);
find_num INT;
underwrite STRING (30);
-- add by sun start
zh_name STRING(50);
en_name STRING(50);
isactive INT;
lableContent String(160);
-- add by sun end
n_agent_balance EXCEPTION;
n_org_balance EXCEPTION;
n_user_balance EXCEPTION;
n_agent_pause EXCEPTION;
n_org_pause EXCEPTION;
n_user_pause EXCEPTION;
BEGIN
msgid := 0;
iexit := 0;
tempmobilestr := tomobile;
tempagentstr := useragent;
--messagestr := ''; SELECT COUNT (*)
INTO find_num
FROM n_agent_price a, n_agent b
WHERE a.agent_id = b.agent_id
AND a.price_type = pricetype
AND a.agent_id IN (SELECT agent_id
FROM n_org
WHERE org_id = orgid)
AND b.status = 0
AND b.end_status = 0
AND ROWNUM = 1; IF (find_num > 0)
THEN
SELECT a.price, b.balance
INTO agent_price, agent_balance
FROM n_agent_price a, n_agent b
WHERE a.agent_id = b.agent_id
AND a.price_type = pricetype
AND a.agent_id IN (SELECT agent_id
FROM n_org
WHERE org_id = orgid)
AND b.status = 0
AND b.end_status = 0
AND ROWNUM = 1;
ELSE
RAISE n_agent_pause;
END IF; find_num := 0; --扣费
IF ((agent_balance - agent_price * item) >= 0)
THEN
SELECT COUNT (*)
INTO find_num
FROM n_users_price a, n_users b
WHERE a.user_id = b.user_id
AND a.user_id = userid
AND a.price_type = pricetype
AND b.status = 0
AND b.end_status = 0
AND ROWNUM = 1; IF (find_num > 0)
THEN
SELECT a.price, b.balance, b.credit
INTO user_price, user_balance, user_credit
FROM n_users_price a, n_users b
WHERE a.user_id = b.user_id
AND a.user_id = userid
AND a.price_type = pricetype
AND b.status = 0
AND b.end_status = 0
AND ROWNUM = 1;
ELSE
RAISE n_user_pause;
END IF; find_num := 0; IF (payorgid > 0)
THEN
SELECT COUNT (*)
INTO find_num
FROM n_org
WHERE org_id = payorgid AND status = 0 AND end_status = 0; IF (find_num > 0)
THEN
SELECT balance, credit
INTO org_balance, org_credit
FROM n_org
WHERE org_id = payorgid AND status = 0 AND end_status = 0;
ELSE
RAISE n_org_pause;
END IF; --find_num := 0; IF ((org_balance + org_credit - user_price * item) >= 0)
THEN
UPDATE n_agent
SET balance = balance - agent_price * item
WHERE agent_id IN (SELECT agent_id
FROM n_org
WHERE org_id = orgid); UPDATE n_org
SET balance = balance - user_price * item
WHERE org_id = payorgid;
--messagestr := 'OK';
Commit;
n_agent
n_org 表的结构和索引>所以TOP事件里排行第2就是 TX-LOW LOCK CONTENTS等待事件
从statspack中看到的?可以帖出来看看不?
而是ASHRPT