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

You may have issues with incorrect statistics. "restored it from a backup" is a particular possible set of procedures, but sloppy usage may include other things such as importing from an export. Do you know exactly what he did to restore? Can you check to be sure all tables and indices have a reasonable time of statistics gathering? (If you have OEM, you can simply look under the schema for indices and tables and there should be recent dates.) There are a number of ways to gather statistics, what procedures does your site use? (One way is exec dbms_stats.gather_schema_stats(ownname=> 'XYZ', cascade=> TRUE, DEGREE=
> x ); where XYZ is your schema name and x is how many processors you have, or one less).

You can also use OEM to tell you where your system is slow from Oracle's viewpoint, in case he did something really screwy like put all your redo logs on a RAID-5 device, or made your init.ora into defaults.  Also check your init.ora for bizarro trace file settings with "forever" in their name. Statspack may be helpful too - there might be web sites to help with those, like oraperf.com.

Look at your alert.log for any error messages (probably something like $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert$ORACLE_SID.log), and check how often your logs are switching. It also shows which init.ora parameters are non-default.

Also use your OS tools to see if anything is wrong there. It does help to post what exact OS and hardware you are using. If you had something get corrupt and now things are slow that might simply mean a disk or controller is dying (the most common thing I've personally seen over the past few years with these symptoms and no DBA idiocy).

Search metalink.oracle.com for notes about performance on your configuration. (Searching for performance tuning methodology in the knowledge base gives some interesting results, though there are perhaps better methodologies). See the performance guide at tahiti.oracle.com for some other things to do. If you have the time and inclination, see Cary Milsap's book (though you probably should do that before you have problems).

jg

--
@home.com is bogus.
http://www.theindychannel.com/news/10473879/detail.html