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