Re: Slow SQL performance

Maureen,
 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:

> 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
>
>
>

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