Re: Slow SQL performance
Date: Mon, 21 Apr 2008 13:21:15 -0500
It might help to see the table. With no indexes on the table, I don't see where statistics are going to matter. It'll full-table-scan the table. What does the 10046 trace look like in Dev compared to test? Same Disk layout, and setup?.
FYI: analyzing the table is enough to invalidate the cached plan.
On Mon, Apr 21, 2008 at 1:13 PM, Maureen English <sxmte_at_email.alaska.edu> wrote:
> 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