Re: Oracle locking up..?

rodi_at_bbs.no (Noddy) wrote in message news:<a62a27da.0402130554.22956637_at_posting.google.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_contentprovider( v_userid IN varchar,
> v_contentproviderid IN varchar,
> v_account_number 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):=null;
> 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_contentproviderid, 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_contentproviderid
> and account_number = v_account_number;
>
> /* 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,biller_login_id,account_number,email,
> first_name,last_name,phone,address_line_1,
> address_line_2,city,zip_code,country, status,
> customer_biller_login_id, activation_date,
> request_date, confirm_date, CREATOR, MODIFIER,
> PASSPHRASE)
> values(v_userid,v_contentproviderid,v_account_number,
> v_email,v_firstname,v_lastname,v_phonenumber,
> v_address_line1,v_address_line2,v_city,v_postalcode,v_country,
> '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 -- Received on Sat Feb 14 2004 - 01:24:27 CET