Re: issues explaining performance issues to clients

> Well the users created yet another combination of where clauses that we do
> not currently have an index to support(this happens alot). It does 200,000
> LIOs and 200,000 PIOs.

Just addressing this fragment. Though when you say they have the ability to create any query they want, I don't know if that means they create it directly in production, or they create it and migrate it through a QA process. If the former, you can delete this email now because what I'm talking about is a process to control change, rather than an approach to addressing chaos and ignorance on the part of your users and management :)

At my current company, when I arrived the DBA group had no visibility to the change process. The thought was that if the development group wasn't changing a table or adding an index, it didn't affect the database and the DBAs didn't need to know about it, and the DBA group had no say in the Data Model (I've changed that also). I explained to the front line managers where this theory went wrong. I then told them how I could help the development team if they would do a couple simple things for us. I wrote a series of scripts that would generate a detailed explain plan for any query run by a developer or QA analyst simply by typing in two words and pasting the query. I also included in those scripts the ability for QA analysts to generate Statspack Snapshots (still on 9i. different battle...), to run before and after a test, again by typing in two words. The first two development cases I looked at allowed me to make two simple changes that brought their processing time down from 7 hours to 12 minutes, and from 6 days+ (killed after 6 days) to 6 hours. Operations saved two YEARS worth of CPU cycles in the next year just with those changes alone. That made believers of them.

     The payoff is that now developers must submit explain plans to the dba group and get signoff when a query is changed or added. Only one developer fights it, the rest love it, and management has mandated the process since they saw the payoff. The DBA group checks it before it goes to QA and again in QA. They also get statspack reports delivered to them for each QA run. The DBA group is now a required sign-off on the Change Request form.

--
http://www.freelists.org/webpage/oracle-l