Hi Spark
Kind of a key point would be to identify the offending queries. If one could
track the offending process, either through sql_trace or some application
supplied logging mechanism, it might be possible to identify what was slow
and consider strategies to speed it up.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Spark" <markfle_at_yahoo.com> wrote in message
news:3ee9da97$0$18497$ed9e5944_at_reading.news.pipex.net...
> Oracle9i Release 9.2.0.3.0 on Windows 2000 server sp3.
>
> Hi all
>
> We have recently upgraded a major database to 9i and we are having a few
> performance issues on lookups and returning queries. We have run the
> following Analyze script in the hope that it would speed up the system;
>
> begin dbms_stats.gather_schema_stats(OWNNAME => 'SCHEMANAME',CASCADE =>
> TRUE); END;
>
> This has had the effect of further slowing down the relevant queries.
>
> On the test machine we have found that if we delete the stats (Analyze
> Table_name delete stats) for the offending table and rebuild the indexes,
> performance comes back to the point where users agree it should be.
>
> I don't see this as a lasting fix and was wondering if anyone could
> postulate a reason for this behaviour. I have had a scan through metalink
> and tahiti.oracle, but can find nothing that relates to this problem. Is
> there a default for number of rows analyzed in any given table?
>
> The table itself contains greater than 3300000 rows.
>
> Many Thanks
>
>
Received on Fri Jun 13 2003 - 14:39:11 CDT
