Oracle-L: Third Party Application Madness

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 24 Feb 2003 11:09:01 -0800
Message-ID: <F001.005571E0.20030224110901@fatcity.com>

In need of cathartic release, I find myself sharing my pain with the folks that will understand it best. :)

The past two weeks have really been 'fun'.

Last week we attempted to install an upgraded version of a rather vital manufacturing application that is used to control our shop floor.

We backed out the upgrade and re-installed the previous version ( and requisite database restores ) because the reponse time on some views was abysmal. This 4 day adventure included one 19 hour day. Some fun, eh?

We replicate several tables from the app to another database for real time reporting needs. These snapshots are refreshed every minute, which has been working quite well for us for 10 months.

( a brief aside: The reason for replicating the data is so that we can query near real time data without bringing down database performance in production, as this is the app from hell )

The upgraded version replaces several of those tables that we replicate with views. So now instead of replicating table XYZ, I have view XYZ and must replicate 8 tables. The structure of these views made them unsuitable for fast refresh snapshots.

Here's the WHERE clause from one of the views:

SELECT *
FROM

        T1 A,  T2 X,  T3 ZZ,   T4 D,
        T5 E,  T6 J,  T7 K,    T8 PP

WHERE A.ProductID = X.ProductID (+)

AND   A.SerialNo = X.SerialNo (+)
AND   A.PRODUCTID = ZZ.ID
AND   A.ProductID = D.ProductID (+)
AND   A.SerialNo = D.SerialNo (+)
AND   D.OrderContentID = e.ID (+)
AND   A.ProductID = J.ProductID (+)
AND   A.SerialNo = J.SerialNo (+)
AND   J.WipContentRefID = K.ID (+)

The table names have been changed to keep me out of trouble.

Lovely eh?

This was supposedly tested, but I think the testing was done with 'test' data. You know, 20 rows in each table.

So we make plans to do it again this weekend ( Feb 22-23 )

I am fortunate to have on retainer a very good local DBA. We thought it might be a good idea to get as many eyeballs as possible looking at these problems. This guy used to write optimizer code for Oracle, so he got to tune the views. :)

After his initial shock at seeing the 'code' in these views, he got to work.

After removing outer joins, eliminating unncessary tables and creating a couple indexes, the views worked pretty well.

There was one still problem though. Trying to create an 'explain plan' on these views might sometimes take minutes. It seems the optimizer couldn't make up it's mind as to how best to execute the query. These views didn't just have 8 way outer joins, there are also inline views in the SELECT portion of several of them.

Here for your amusement, is the timing portion of a small set of transactions. < 2 seconds to execute, 5.5 minutes to parse the views.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 430 384.49 383.42 0 0 1 0
Execute 268 0.41 0.49 16 1782 151 54
Fetch 216 1.04 1.05 1 14919 420 108
------- ------ -------- ---------- ---------- ---------- ----------

total 914 385.94 384.96 17 16701 572 162

Misses in library cache during parse: 177

Oracle fortunately has a optimizer parameter that helps. We set optimizer_max_permutations=1000, and the pace picked up considerably. ( take *that*, SQL Server )

If that were the only problem, I would have happily moved on to the next crisis, but alas, that was not to be.

Earlier in the week I discovered that there were 1352 unindexed Foreign Key constraints in the application schema. No, that is not a typo. This is in addition to the 500 or so FK constraints that *are* indexed.

I mentioned that this might be a problem. Well, it did turn out to be so.

This morning numerous ORA-60 deadlocks were appearing. The deadlock graph looks like this:

Deadlock graph:

                       ---------Blocker(s)--------  
---------Waiter(s)---------
Resource Name          process session holds waits  process session holds 
waits
TM-00000fb4-00000000        14      33    SX   SSX       22     240    SX   
SSX
TM-00000fb4-00000000        22     240    SX   SSX       14      33    SX

SSX
session 33: DID 0001-000E-00000002 session 240: DID 0001-0016-00000002 session 240: DID 0001-0016-00000002 session 33: DID 0001-000E-00000002 Rows waited on:
Session 240: no row
Session 33: no row

SSX locks on a TM (table) indicate that there are likely unindexed FK constraints in the database.

Big surprise. I indexed the few that were causing the immediate problem, and am now planning to index the remaining 1349.

This isn't a plea for help, just commiseration. :)

Now, on to the next crisis.

Jared

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jared Still
INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Feb 24 2003 - 13:09:01 CST