issues explaining performance issues to clients
Date: Wed, 22 Jul 2009 14:42:08 -0400
Message-ID: <f30139790907221142w4723e1d1x1aed797fc219f74d_at_mail.gmail.com>
How do you handle this? This is a large government project environment.
Users can basically do whatever they want here. They have the ability to make any query they want. We have one table that is about 2 GBs that has 30 indexes on it to support this.
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. In our test environment where there is little activity this returns in 6 seconds. Through a 10046 trace my physical waits are negligible (the data is probably cached in the SAN, since the LIOs and PIOs are the same) and the waits on db_file_scattered_reads total to less than 1 second.
In production. The exact same query, same plan and basically the same LIOs and PIOs takes about a minute. Of that 55 seconds of it is physical reads. We have twice as many db_file_scattered_read waits and the wait time is about 55 seconds. This does not surprise me since due to alot of bad queries like this overall workload is higher and this leads to more waits and slower response time. This is fairly generic.
A few questions.
1. Some people here see this as a "problem" that requires root cause
analysis to fix. They want to make production "fast". From a DBA perspective
the root cause is "fix the queries and use an existing index. Stop letting
the users do whatever they want". Or the more expensive route, we need to
consider another reporting system, etc... and a major architecture
change(they won't like the second one at all).
2. They want me to engage other people to find the root cause. The SAN is
run by a totally different team that works with many teams. Does anyone know
what tools they should be using to look into this and what I should say to
them exactly, to get more detailed metrics beyond oracle's perspective.
I do not know how the underlying subsystem is set up. For all I know we could have other applications in the same RAID groups, etc...
I have tried to work with SAN guys before. Unless you get a consultant from EMC or HP or something like that, I find that they just click buttons. I do not know if that is the case here. So how would you engage the SAN engineers?
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 22 2009 - 13:42:08 CDT