RE: Slow SQL performance

When you ran this query on the test system, did you run it with a full production load on the system? I would think that the problem is not missing histograms, but missing indexes. Histograms will help you to use indexes or partitions that are not being used. If you want to force a hard parse of the query, then you just issue an 'alter system flush shared_pool', but I don't think it is going to help.

Pat

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Maureen English Sent: Monday, April 21, 2008 1:13 PM
To: oracle-l_at_freelists.org
Subject: Slow SQL performance

All;

We are having a performance problem with our production server that is not occurring on our test server (a copy of the production server).

Both instances are 10g (10.2.0.3.0) on HP Tru64 hardware.

The known difference between the two instances is that when the test system was upgraded, I generated statistics on the table using the 'FOR ALL COLUMNS' clause in dbms_gather_table_stats. In the production system, I couldn't afford the time it took to gather statistics for all columns, so I used the 'FOR ALL INDEXED COLUMNS' clause.

The main table in our slow query happens to not have any indexes on it. I've since generated stats on that table, but it seems to have made no difference.

I'm thinking that since the query is used constantly, it is still in memory, and will stay that way until something forces it out....

Since the query is constantly used, is there a way that I can clear it from the cache? I'm looking into the DBMS_SHARED_POOL package's UNKEEP procedure, but the description of the KEEP procedure makes it sound like this will not work for tables.

Any suggestions?

• Maureen University of Alaska
  --
  http://www.freelists.org/webpage/oracle-l

[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.  

To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 21 2008 - 14:01:12 CDT