Re: Oracle db very slow | where to look

Anoop wrote:
> Hi All,
>
> I couldnt find pertinent info about this through google....
>
> We had a database running on Oracle9i Release 9.2.0.8.0 - 64bit.
> Recently something went wrong and we had an external dba come in and
> look at it. He said that it is unrecoverable, & he restored it from a
> backup and left.
>
> But now our application runs very very slow when connected to this db.
> I am still a newbie at this - so can you please point me where to start
> looking to find the root cause of the performance problem. (logs /
> parameters etc..). We do not have much data - we have about 50 tables
> and each table has about 2000 rows.
>
> I am thinking that the dba turned on some tracing or something to debug
> our db problems and forgot to turn them off before giving it back to
> us.

As far as tracing goes do this:

login to your database using sqlplus
type

show parameter sql_trace

If sql_trace=TRUE you should set it to false in your init.ora or alter system set sql_trace=false scope=both; if you have an spfile.

Another easy thing to check is stats.

select count(*) from dba_tables where last_analyzed is null;

If last_analyzed is null then you are missing stats. Try

execute dbms_stats.gather_schema_stats('SCHEMA');

where SCHEMA is where your tables reside. Don't do this on the SYS schema.

Last easy thing I can think of is your block buffers. Probably wasn't changed by your outside DBA but could cause a big slowdown if its wrong. Look at the parameter DB_CACHE_SIZE. If it is tiny and you have tons of free memory then bump it up. With your small database maybe 100 meg would be fine, depending on how big your rows are.

Could be many other things, but these are some easy ones.

• Bobby