RE: issues explaining performance issues to clients

So the requirement is "no matter what we query or how we query it, it must be as fast as we say it should be, and our adhoc requirements are more important than prod"? It doesn't make sense. How can that be done for adhoc wide open access? I think 55 secs is quite reasonable performance for people doing who knows what. But, if it was me and I had that type of requirement. I would copy the table somewhere once nightly, bitmap index all the columns and let them have at it.  

What does "where clauses that we do not currently have an index to support" mean?

If you have a table with columns a,b,c,d,e and currently have an index on a,b,c and someone uses a,b,c,d in a where clause do you then add an index on a,b,c,d?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
Sent: Wednesday, July 22, 2009 2:42 PM
To: oracle-l_at_freelists.org
Subject: issues explaining performance issues to clients  

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.    

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