Re: issues explaining performance issues to clients
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 23 Jul 2009 11:13:58 -0400
There are pages in the application where users can build sql statements from
a series of drop down boxes. So there are large combinations of where
clauses and joins that they can have. It is really not practical to index
every possible situation that a user can create. There are too many
permutations.
This is an operations team with no say over the data model and no say over
the application at all. The only real power we have is to open tickets to
tell them when they don't use bind variables. When there are poor performing
queries they come to us to fix them.
On Wed, Jul 22, 2009 at 3:51 PM, <joel.wittenmyer@xxxxxxxxxxxxxxxxx> wrote:
> Quoting Dba DBA <oracledbaquestions@xxxxxxxxx>:
>
> 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.
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
