row_cache_lock and library_cache_lock

We "successfully" locked up a production Oracle 7.3.2.3 database last week by performing a series of Analyze Table commands while placing a SQL Parsing load on the database from a Forms 4.5 application. Basically the deadlock occurs because all Forms sessions are waiting on Library Cache (per event column of v$session_wait) while the Analyze command was pended on row_cache.

The application generates a lot of 1 time SQL which needs to be parsed and many tables also start out empty and require frequent Analyze commands as they grow so the optimizer know when to switch from full table scans to index reads. Apparently the Analyze command invalidates cursors, forcing them to be re-parsed.

Now we are trying to recreate the problem on demand so we can verify Oracle Support's claim that upgrading to 7.3.3.6 will fix it. My basic monitoring tool is the command:

   Select event, count(*) from v$session_wait group by event;

The lockup does not happen all the time. Frequently, I'll see a up to a dozen sessions pending on Library Cache and the Analyze command on Row Cache; most of the time the analyze command frees itself after a few seconds, then everyone grabs library cache and parses their SQL.

Has anyone seen anything like this before? Any thoughts on what init.ora parameters to tune? We tried dropping row_cache_cursors from the default of 10 to 2 to try and make the problem happen more often, but it wouldn't lock up after that.

Any help or suggestions appreciated.
--

Bill Schott
E-mail: bschott_at_megsinet.net
ICQ#: 3758635 Received on Sun Jun 07 1998 - 15:12:47 CDT