Oracle locking up..?

[Noddy]

We have an application that updates a row in a table. Sometimes Oracle
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.

We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).

What can cause this situation in Oracle;

* Table lock... ? How does Oracle lock tables when updating.
* ..

Do Oralce log whether or not a table has been locked and for how long
?

Thanx. Noddy

---------------
procedure add_user_conten tprovider( v_userid IN varchar,
v_contentprovid erid IN varchar,
v_account_numbe r IN varchar, v_email IN
varchar, v_firstname IN varchar, v_lastname IN
archar,
v_address_line1 IN varchar,
v_address_line2 IN varchar, v_city IN varchar,
v_postalcode IN varchar, v_country IN varchar,
v_phonenumber IN varchar, v_modifier
IN varchar, v_updatestatus OUT varchar)
is
t_status varchar(10):=nu ll;
t_login varchar(255) := null;
begin
/* is the user deleted */
deleted_user(v_ userid,t_status );
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

provider_exists (v_contentprovi derid, t_status);
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

select login_id into t_login
from customer_biller
where biller_login_id = v_contentprovid erid
and account_number = v_account_numbe r;

/* if t_login is not null the agreement exists */
if t_login is not null then
v_updatestatus := '23005';
return;
end if;

/* if not data found the agreement doesn't exist */
exception when no_data_found then
v_updatestatus := null;

/* everything is ok, insert the agreement */
insert into customer_biller
(login_id,bille r_login_id,acco unt_number,emai l,
first_name,last _name,phone,add ress_line_1,
address_line_2, city,zip_code,c ountry, status,
customer_biller _login_id, activation_date ,
request_date, confirm_date, CREATOR, MODIFIER,
PASSPHRASE)
values(v_userid ,v_contentprovi derid,v_account _number,
v_email,v_first name,v_lastname ,v_phonenumber,
v_address_line1 ,v_address_line 2,v_city,v_post alcode,v_countr y,
'PENDING', v_userid, DATE '1900-01-01',
DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier,
'PASSPHRASE');

end;

end;

[craig]

Oracle Locking up is somewhat vague. I assume you mean someone is issuing
sql and it takes forever to return.

You or your DBA should check the data dictionary for locks in tables like
DBA_LOCKS, DBA_BLOCKERS, DBA_WAITERS, DBA_DML_LOCKS. If you search the web
you could probably find some scripts to run in cron to use this information
to identify problems. Also you can use statspack or oracle OEM if you have
it installed to see the problems. But that is a whole new area of
discussion.

Also, it looks like your Code has hard coded literal values in it, which can
slow execution. Oracle likes bind variables to speed parsing. I think
these may give a problem, not sure about the exception though
(v_updatestatus := '23005')(v_upda testatus := null)

"Noddy" <ro**@bbs.no> wrote in message
news:a6******** *************** ***@posting.goo gle.com...

We have an application that updates a row in a table. Sometimes Oracle
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.

We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).

What can cause this situation in Oracle;

* Table lock... ? How does Oracle lock tables when updating.
* ..

Do Oralce log whether or not a table has been locked and for how long
?

Thanx. Noddy

---------------
procedure add_user_conten tprovider( v_userid IN varchar,
v_contentprovid erid IN varchar,
v_account_numbe r IN varchar, v_email IN
varchar, v_firstname IN varchar, v_lastname IN
archar,
v_address_line1 IN varchar,
v_address_line2 IN varchar, v_city IN varchar,
v_postalcode IN varchar, v_country IN varchar,
v_phonenumber IN varchar, v_modifier
IN varchar, v_updatestatus OUT varchar)
is
t_status varchar(10):=nu ll;
t_login varchar(255) := null;
begin
/* is the user deleted */
deleted_user(v_ userid,t_status );
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

provider_exists (v_contentprovi derid, t_status);
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

select login_id into t_login
from customer_biller
where biller_login_id = v_contentprovid erid
and account_number = v_account_numbe r;

/* if t_login is not null the agreement exists */
if t_login is not null then
v_updatestatus := '23005';
return;
end if;

/* if not data found the agreement doesn't exist */
exception when no_data_found then
v_updatestatus := null;

/* everything is ok, insert the agreement */
insert into customer_biller
(login_id,bille r_login_id,acco unt_number,emai l,
first_name,last _name,phone,add ress_line_1,
address_line_2, city,zip_code,c ountry, status,
customer_biller _login_id, activation_date ,
request_date, confirm_date, CREATOR, MODIFIER,
PASSPHRASE)
values(v_userid ,v_contentprovi derid,v_account _number,
v_email,v_first name,v_lastname ,v_phonenumber,
v_address_line1 ,v_address_line 2,v_city,v_post alcode,v_countr y,
'PENDING', v_userid, DATE '1900-01-01',
DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier,
'PASSPHRASE');

end;

end;

[Mark D Powell]

ro**@bbs.no (Noddy) wrote in message news:<a6******* *************** ****@posting.go ogle.com>...

We have an application that updates a row in a table. Sometimes Oracle
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.

We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).

What can cause this situation in Oracle;

* Table lock... ? How does Oracle lock tables when updating.
* ..

Do Oralce log whether or not a table has been locked and for how long
?

Thanx. Noddy

---------------
procedure add_user_conten tprovider( v_userid IN varchar,
v_contentprovid erid IN varchar,
v_account_numbe r IN varchar, v_email IN
varchar, v_firstname IN varchar, v_lastname IN
archar,
v_address_line1 IN varchar,
v_address_line2 IN varchar, v_city IN varchar,
v_postalcode IN varchar, v_country IN varchar,
v_phonenumber IN varchar, v_modifier
IN varchar, v_updatestatus OUT varchar)
is
t_status varchar(10):=nu ll;
t_login varchar(255) := null;
begin
/* is the user deleted */
deleted_user(v_ userid,t_status );
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

provider_exists (v_contentprovi derid, t_status);
if t_status is not null then
v_updatestatus := t_status;
return;
end if;

select login_id into t_login
from customer_biller
where biller_login_id = v_contentprovid erid
and account_number = v_account_numbe r;

/* if t_login is not null the agreement exists */
if t_login is not null then
v_updatestatus := '23005';
return;
end if;

/* if not data found the agreement doesn't exist */
exception when no_data_found then
v_updatestatus := null;

/* everything is ok, insert the agreement */
insert into customer_biller
(login_id,bille r_login_id,acco unt_number,emai l,
first_name,last _name,phone,add ress_line_1,
address_line_2, city,zip_code,c ountry, status,
customer_biller _login_id, activation_date ,
request_date, confirm_date, CREATOR, MODIFIER,
PASSPHRASE)
values(v_userid ,v_contentprovi derid,v_account _number,
v_email,v_first name,v_lastname ,v_phonenumber,
v_address_line1 ,v_address_line 2,v_city,v_post alcode,v_countr y,
'PENDING', v_userid, DATE '1900-01-01',
DATE '1900-01-01', DATE '1900-01-01', v_modifier, v_modifier,
'PASSPHRASE');

end;

end;

I suggest you run an SQL trace on the task and look to see how the one
select is being done and what Oracle says about the insert
performance. The problem could be in the deleted_user or
provider_exists procedures.

For the select I would expect that the account_number is the PK. But
if it is not and both it and biller_login_id are separately indexed
then Oracle could be choosing the wrong index. A trace would identify
this.

Good luck -- Mark D Powell --

[Joel Garry]

ro**@bbs.no (Noddy) wrote in message news:<a6******* *************** ****@posting.go ogle.com>...

We have an application that updates a row in a table. Sometimes Oracle
does not respond for over 10 seconds. This is after an insert. It
seems that there is a lock somewhere. This is a multi user system.
Records can be added 3+ pr. second.

We have a procedure stored that does the actuall insert. Documented
below. As you can see the insert is pretty straight forward. (look at
the last section in the procedure).

What can cause this situation in Oracle;

* Table lock... ? How does Oracle lock tables when updating.

There is a document in your doc set or you can find at
tahiti.oracle.c om called the Applications Developers Guide
Fundamentals. Free registration required at otn.oracle.com.

http://download-west.oracle.com/docs...08sql.htm#2477

This is a decremented group, see
http://members.cox.net/oracleunix/readme-cdos.htm

jg
--
@home.com is bogus.
http://www.firstgov.gov/fgsearch/res...p/everhart.pdf