Re: issues explaining performance issues to clients
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 23 Jul 2009 11:19:15 -0400
not practical. There are alot of tables where this happens. I have another
mail where I state that users have web pages where they can choose columns
from a drop down list. It is not practical to properly index tables for that
many permutations. We have tried. We often have 30-50 indexes/table to
support this, but we can't support everything.
there are a number of tables in the 2 GB- 10 GB range that end up with full
table scans. From tests perspective this returns in 6-20 seconds in test
(since no else is on the system), but it is "slow" in production where alot
more people are on the system and they want to know why. They basically want
it measured. I'll have to get with the SAN engineers to somehow measure IO
usage in production and the capacity or something. I am not sure how to do
this.
On Wed, Jul 22, 2009 at 5:52 PM, Rich <richa03@xxxxxxxxx> wrote:
> The table is only 2GB?
> Cache the table in RAM - no more PIOs.
> Also drop the indexes and do full tablescans every time - that will save
> time on inserts and also make every query require about the same amount of
> time.
> If it's still too slow, get a faster CPU and/or CPU to RAM backplane (front
> side bus).
>
>
> On Wed, Jul 22, 2009 at 11:42 AM, Dba DBA <oracledbaquestions@xxxxxxxxx>wrote:
>
>> 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?
>>
>
>
Other related posts:
