ORA-01722 until shared pool is flushed

From: <JayMiller_at_TDWaterhouse.com>
Date: Wed, 29 Mar 2006 18:36:44 -0500
Message-ID: <03CB0D78C4AF01429A5C04F1EFD2988512A939CE@usnjc01wmx002.tdwaterhouse.com>

We have an odd issue that has gotten worse as of yesterday. We have an 8.1.7.2 database that is very important but cannot be upgraded or patched (it's being retired in about a year and would require about 6 months of testing for all the applications that rely on it).

At irregular intervals (2-5x/month) one of the stored procedures will start returning an ORA-01722. If the shared pool is flushed the problem goes away.

Since we had a quick workaround of flushing the shared pool and the database is being retired anyway I wasn't too worried but as of yesterday flushing the shared pool no longer worked and I also had to kill the sessions connecting as that user (we have a middle tier with connection pooling using websphere).

The error occurs at random times of day (always during business hours), but became especially prevalent during a period when we accidently had statistics on some data dictionary objects. This obviously caused general performance problems but also the 01722 error started happening daily instead of every few weeks.

No trace files or errors in the alert log. A level 12 trace while the error was occurring turned up the following:

PARSING IN CURSOR #265 len=218 dep=1 uid=8 oct=3 lid=8 tim=2689309642 hv=1964772
655 ad='d9ff5f14'

SELECT CAR.CUSTOMER_ID,
CAR.ACCOUNT_ID,
CAR.CUST_ROLE
FROM CUSTOMER_ROLE CR,CUST_ALT_ROLE CAR WHERE CR.ACCOUNT_ID = :b1
AND CR.CUST_ROLE = :b2
AND CAR.ACCOUNT_ID = CR.ACCOUNT_ID
AND CAR.CUSTOMER_ID = CR.CUSTOMER_ID END OF STMT
EXEC #265:c=5,e=4,p=0,cr=51,cu=0,mis=1,r=0,dep=1,og=4,tim=2689309642 ERROR #265:err=1722 tim=2689309642
EXEC #34:c=45,e=46,p=0,cr=500,cu=71,mis=0,r=0,dep=0,og=4,tim=2689309642

ERROR #34:err=20020 tim=2689309642
WAIT #255: nam='latch free' ela= 0 p1=-453714724 p2=106 p3=0
WAIT #255: nam='latch free' ela= 0 p1=-453714724 p2=106 p3=1

The code associated with this is as follows (part of a much larger package):
BEGIN

             SELECT car.customer_id, car.account_id, car.cust_role
               INTO lv_cr_customer_id, lv_cr_account_id, lv_cr_cust_role
               FROM customer_role cr, cust_alt_role car
              WHERE cr.account_id = lv_account_id
                AND cr.cust_role = i_cust_role
                AND car.account_id = cr.account_id
                AND car.customer_id = cr.customer_id;
         EXCEPTION
               WHEN no_data_found THEN
               NULL;
         END;

The variables have the datatypes assigned correctly and anyway it starts working after the shared pool is flushed so it doesn't seem like a programming bug.

The only thing that seems related is that we occasionally (but much more rarely) get an ORA-01722 when we access a 9.2.0.4 database through a database link. At that point I was able to duplicate the error by running an anonymous pl/sql block but the same SQL run from the SQL*Plus prompt ran correctly without errors. Unfortunately I didn't save that SQL and don't recall it offhand (we haven't had the error in over 6 months). Those errors only started after the remote database was upgraded to 9i (we had a similar problem from an 8.1.6 database that was connecting to the 9.2.0.4 database until it was upgraded to 9i).

System details:

Database with error:
Solaris 2.8
Oracle 8.1.7.2

Remote database that caused similar error (not called from current error but maybe it's significant?):
Solaris 2.9
Oracle 9.2.0.4

Ideas?

Thanks,
Jay Miller

This message is confidential and sent by TD Waterhouse solely for use by the intended recipient. If you are not the intended recipient, you are hereby notified that any use, distribution or copying of this communication is strictly prohibited. This should not be deemed as an offer or solicitation, to buy or sell any product. Any 3rd party information contained herein was prepared by sources deemed reliable, but is not guaranteed. TD Waterhouse does not accept electronic instructions that would require an original signature. Information received by or sent from TD Waterhouse is stored, subject to review, and may be produced to regulatory authorities or others with a legal right to such.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 29 2006 - 17:36:44 CST