RE: Third Party Application Madness
Title: RE: Third Party Application Madness That's the situation I'm in now, but the language is Hungarian. Matt Adams - GE Appliances - [EMAIL PROTECTED] We have enough youth. How about a fountain of intelligence? -Original Message- From: Odland, Brad [mailto:[EMAIL PROTECTED]] Sent: Monday, February 24, 2003 4:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Third Party Application Madness And the table and column names hopefully are in a language you know... I ran into a app that the table names and columns were in German. Not a problem if you know German... Brad -Original Message- Sent: Monday, February 24, 2003 2:13 PM To: Multiple recipients of list ORACLE-L Congratulations! We, the Annointed Members present you, Jared Still, with the Sadistic Vendor Setup Purple Heart award. Wear it with pride. At least you *have* PKs and FKs... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, February 24, 2003 1:09 PM To: Multiple recipients of list ORACLE-L 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'. [snip] Now, on to the next crisis. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: RE: Third Party Application Madness
Jerry, Depends on the third party application/vendor. Dick Goulet Reply Separator Author: "Whittle Jerome Contr NCI" <[EMAIL PROTECTED]> Date: 2/24/2003 2:02 PM Dick, Is that aspirin first then the Jim Beam or Jim Beam first and aspirin the next morning? Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] > > Jared, > > Been there, done that, I'll put a large bottle of aspirin & Jim Beam in the > mail for you. They help me!! > > Dick Goulet > RE: RE: Third Party Application Madness Dick, Is that aspirin first then the Jim Beam or Jim Beam first and aspirin the next morning? Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] Jared, Been there, done that, I'll put a large bottle of aspirin & Jim Beam in the mail for you. They help me!! Dick Goulet -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: Third Party Application Madness
Excellent advice! I'll give it a 'shot' later today. Jared On Monday 24 February 2003 13:35, [EMAIL PROTECTED] wrote: > Jared, > > Been there, done that, I'll put a large bottle of aspirin & Jim Beam in > the mail for you. They help me!! > > Dick Goulet > > Reply Separator > Author: "Jesse; Rich" <[EMAIL PROTECTED]> > Date: 2/24/2003 12:12 PM > > Congratulations! We, the Annointed Members present you, Jared Still, with > the Sadistic Vendor Setup Purple Heart award. Wear it with pride. > > At least you *have* PKs and FKs... > > > Rich > > Rich JesseSystem/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > > -Original Message- > Sent: Monday, February 24, 2003 1:09 PM > To: Multiple recipients of list ORACLE-L > > > > 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'. > > [snip] > > Now, on to the next crisis. > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Third Party Application Madness
Re PK's and FK's: Yes, I was glad to see that at first, but not so sure now, as it seems that this database has been 'normalized' to a point that even I, a dyed in the wool normalization bigot, find extreme. The 'normalization' is in quotes because I haven't had time see if the schema is actually normalized, or just broken up into a lot more tables. The abundance of outer joins used tends to point to the latter. Jared On Monday 24 February 2003 12:08, Jesse, Rich wrote: > Congratulations! We, the Annointed Members present you, Jared Still, with > the Sadistic Vendor Setup Purple Heart award. Wear it with pride. > > At least you *have* PKs and FKs... > > > Rich > > Rich JesseSystem/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > > -Original Message- > From: Jared Still [mailto:[EMAIL PROTECTED] > Sent: Monday, February 24, 2003 1:09 PM > To: Multiple recipients of list ORACLE-L > Subject: Third Party Application Madness > > > > 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'. > > [snip] > > Now, on to the next crisis. > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: RE: Third Party Application Madness
Title: RE: RE: Third Party Application Madness Dick, Is that aspirin first then the Jim Beam or Jim Beam first and aspirin the next morning? Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] Jared, Been there, done that, I'll put a large bottle of aspirin & Jim Beam in the mail for you. They help me!! Dick Goulet
RE: Third Party Application Madness
Title: RE: Third Party Application Madness Love those COTS - emphasis on "customized" and not on "off the shelf" unless you mean with a bulldozer. -Original Message- From: Odland, Brad [mailto:[EMAIL PROTECTED]] Sent: Monday, February 24, 2003 4:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Third Party Application Madness And the table and column names hopefully are in a language you know... I ran into a app that the table names and columns were in German. Not a problem if you know German... Brad -Original Message- Sent: Monday, February 24, 2003 2:13 PM To: Multiple recipients of list ORACLE-L Congratulations! We, the Annointed Members present you, Jared Still, with the Sadistic Vendor Setup Purple Heart award. Wear it with pride. At least you *have* PKs and FKs... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, February 24, 2003 1:09 PM To: Multiple recipients of list ORACLE-L 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'. [snip] Now, on to the next crisis. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:RE: Third Party Application Madness
Jared, Been there, done that, I'll put a large bottle of aspirin & Jim Beam in the mail for you. They help me!! Dick Goulet Reply Separator Author: "Jesse; Rich" <[EMAIL PROTECTED]> Date: 2/24/2003 12:12 PM Congratulations! We, the Annointed Members present you, Jared Still, with the Sadistic Vendor Setup Purple Heart award. Wear it with pride. At least you *have* PKs and FKs... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, February 24, 2003 1:09 PM To: Multiple recipients of list ORACLE-L 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'. [snip] Now, on to the next crisis. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Third Party Application Madness
And the table and column names hopefully are in a language you know... I ran into a app that the table names and columns were in German. Not a problem if you know German... Brad -Original Message- Sent: Monday, February 24, 2003 2:13 PM To: Multiple recipients of list ORACLE-L Congratulations! We, the Annointed Members present you, Jared Still, with the Sadistic Vendor Setup Purple Heart award. Wear it with pride. At least you *have* PKs and FKs... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, February 24, 2003 1:09 PM To: Multiple recipients of list ORACLE-L 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'. [snip] Now, on to the next crisis. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Third Party Application Madness
Congratulations! We, the Annointed Members present you, Jared Still, with the Sadistic Vendor Setup Purple Heart award. Wear it with pride. At least you *have* PKs and FKs... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, February 24, 2003 1:09 PM To: Multiple recipients of list ORACLE-L 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'. [snip] Now, on to the next crisis. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Third Party Application Madness
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 cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 430384.49 383.42 0 0 1 0 Execute268 0.41 0.49 16 1782151 54 Fetch 216 1.04 1.05 1 14919420 108 --- -- -- -- -- -- -- total 914385.94 384.96 17 16701572 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-0fb4-14 33SX SSX 22 240SX SSX TM-0fb4-22 240SX SSX 14 33SX SSX session 33: DID 0001-000E-0002 session 240: DID 0001-0016-0002 session 240: DID 0001-0016-0002 session 33: DID 0001-000E-0002 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 -- Ple
