« Things That Have Happened Recently | Main | New Database »
April 6, 2009
Coming Back to the Blog
How did it get to be April already?? As a once-faithful blogger, I thought it was about time to get back into blogging form. It's an easy habit to acquire, and an easy habit to "unacquire".
Well, a lot has been happening here at Western Oregon Univerisity with regard to our Oracle databases. We have spent a good deal of time attempting to get the CBORD residential and food service application upgrade running on Oracle. To say it has been frustrating would be a gross understatement. The Windows server where the application resides upgraded just fine. We created a special database just for CBORD - they require either version 10.2.0.2 or 10.2.0.3 - NOT 10.2.0.4... Well, of course nobody has one of those just sitting around, so we created one. The CBORD queries, however, don't like version 10.2.0.3 - each of their queries takes an unbelievable amount of time to complete. Their SQL queries have a lot of joins and are written in a way with which we are unfamiliar here.
Here's an example of a query that returns 8 records, 13 columns per record. Running time: 122 seconds.
SELECT ft.FacilityType_SK as FacilityType_Key , ft.Name , ft.Icon , ft.Listing_Order , ft.Active , ft.IDStartPosition ,
ft.IDLength , ft.IDPrefix , ft.IDIncludeInChildren , ft.Is_Space_Level as IsSpaceLevel ,
(CASE WHEN EXISTS (Select 1 FROM HMSp_FacilityTree ftr JOIN av_hms_db_Assignments a
ON(ftr.Facility_SK_FK = a.Facility_SK) WHERE ftr.EndDate is Null and ftr.FacilityType_SK_FK = ft.FacilityType_SK )
THEN 1 ELSE 0 END) As OnContract , (CASE WHEN EXISTS (Select 1 FROM HMSp_FacilityTree ftr
JOIN av_hms_db_ContractEl_Facility a ON(ftr.Facility_SK_FK = a.Facility_SK_FK_SpaceLink)
WHERE ftr.EndDate is Null and ftr.FacilityType_SK_FK = ft.FacilityType_SK ) THEN 1 ELSE 0 END) As IsLinked ,
ft."ROWID" FROM hmsp_FacilityType ft
ORDER By ft.Listing_Order
We do a lot better with PL/SQL, PHP, that sort of thing. Anyway, after much tweaking on our part, and their part, we were forced to have them roll back the upgrade so that we would at least have our old version to use.
They suggest that it's our Solaris 10 operating system on the db's server that is the source of the slowness. We, of course, have a hard time accepting this since we have literally hundreds of applications which run just fine on our production databases. Also, we feel that Solaris is a much more efficient OS than Windows. Plus, how would their application even know what OS it was calling to? The application is only requesting data from the db. So, back and forth we go.
I have done some investigation into the Solaris OS parameters that Oracle uses to run efficiently. Dave and I worked on a different way of setting those parameters (OK, so Dave did it all and I just watched) in Solaris 10. This seemed to make the db run the CBORD queries using much less CPU time. Unfortunately, it still took the same length of time to finish the query.
We are continuing an investigation into the Optimizer parameters, and maybe there is a magic button out there that will make these queries run in an acceptable amount of time. Also, Michael is going to set up some db's on different OSes and see what differences he might discover.
Well, that's enough for today. It feels good to blog again!
Posted by rossm at April 6, 2009 3:56 PM
Comments
Post a comment
Thanks for signing in, . Now you can comment. (sign out)
(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)