Severe Performance Degredation

[craig322]

Yesterday we did a shutdown of our Oracle database and HP-UX server because one of the client programs was hanging.

The database and server were both shut down cleanly without errors.

When the HP-UX/database were started, the performance drastically nose dived for all queries.

Here are a few examples

I can log in via sqlplus instantly, when I log in via toad or oem, it takes upwards of 2 minutes just to connect.

If I try to do anything simple, like look at anything (derived from system table queries) using these tools, it processes for sometimes over an hour.

Other processes are also slow.

Simple SQL statements from sqlplus just appear to hang.

As far as I know nothing was changed (I am the dba). This is a production database that has been running for years.

HELP - TIA

 

[prosper]

Hi, craig322.

Check Database Performance need to check in different sides.

1. Is your database on the remote sides ? Because network is too slow, the time for login database become long, use ping command in unix / DOS / windows to detect the speed.

2. In unix, is there any big job?
use command top in unix to detect which job need more CPU and memory. ( if the job is database job, you can detect which sql is running by the process id in Console Manager => Instance => Session, you may tune the sql)

3. You can check whether any error in alert log.

4. What is the size of your database and How many CPU and memory ? For oracle 9i and OLTP database, phyiscal memory is better 4G or more.

5. You can check memory configuration is enough for Database, In console manager => Instance => Configure => Memory, there is a graph for the usage and suggestion of SGA, PGA setting, etc. ( Because more database session, more large jobs require more memory )

6. If your database set time_statistics on and set optimizer is rule base, you can detect which sql require more memory by top SQL in performance manager.

7. It may be OS error, you can ask your unix adminstrator to check whether error in unix, the log should be in file syslog in directory /var/adm/

 

[sem]

Check processes owned by Oracle. It's possible that previous shutdown wasn't so clean and some ghosts exist.

Regards, Dima

http://seminihin.narod.ru

 

[hoinz]

Hardware problems?
I saw something like this when an 8-cpu machine came up using only 4 of them.