Re: Slow SQL performance
- From: "Bradd Piontek" <piontekdd@xxxxxxxxx>
- To: sxmte@xxxxxxxxxxxxxxxx
- Date: Mon, 21 Apr 2008 13:21:15 -0500
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@xxxxxxxxxxxxxxxx>
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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
