Oracle slow.

[Atul Thakur]

My HPUX 11.0 system has enough CPU idle time, but clients are complaining that a simple read of oracle from a table and write to a table is taking a very long time to complete. I suspect memory and memory kernel params. Any assistance would be appreciated.

Thanks.

Atul Thakur

[Gulam Mohiuddin]

Please assign proper SGA size,SORT_ARE_SIZE and Rollback Segments to your Database.

[Alan Wyskowski]

Run the "dmesg" and check the syslog /var/adm/syslog/syslog.log to make sure you don't have any disks off-line etc.

Otherwise, you may want to rebuild the index to that table if there is one.

[Tim Medford]

There are several things to check before assuming the problem is in the OS. Have the tables and indexes been analyzed recently? Have you run explain plans on the SQL they are running? You may try tracing one of their sessions and the tkprof'ing the output to see where the time is being spent.

If this all looks good, then I would check Oracle statistics to see if the SGA is too small.

Next I would check on the OS side is the disk configuration. It is the most likely cause of sluggish performance. Are you using RAID? Are your tablespaces spread across separate LUNs or physical disks? You can use glance or sar -d for a quick look at disk performance.

If all this looks good, then check further into the OS, kernel parameters.. paging... etc..

Regards,
Tim

[Volker Borowski]

Hi,

general in thos questions it would be good to have some more information:

- size of the db
- size of database buffer cache (db_blockbuffers, db_block_size)
- SQL-Satement causing trouble (if available)
- physical memeory in box

General aproach:
- If this is a plain DB server, decrease UX-kernel-params for filesystem cache, to prevent swaps of the db-buffer. Be sure, that the DB-Buffer fits into phys mem all the time.
- Is the situation from start, or has anything changed -> This might even be application side, like dataloads, new module is live now or so.-> Hunt the changes.
- Entire system or specific action:
Entire System -> go for buffers, locks, io-times in general
Specific action -> which statement ? Can it be re-written or indexsupported ?

Hope this helps a bit
Volker

[Volker Borowski]

Hi,

general in thos questions it would be good to have some more information:

- size of the db
- size of database buffer cache (db_blockbuffers, db_block_size)
- SQL-Satement causing trouble (if available)
- physical memeory in box

General aproach:
- If this is a plain DB server, decrease UX-kernel-params for filesystem cache, to prevent swaps of the db-buffer. Be sure, that the DB-Buffer fits into phys mem all the time.
- Is the situation from start, or has anything changed -> This might even be application side, like dataloads, new module is live now or so.-> Hunt the changes.
- Entire system or specific action:
Entire System -> go for buffers, locks, io-times in general
Specific action -> which statement ? Can it be re-written or indexsupported ?

Hope this helps a bit
Volker

[Michael Lee_4]

pay more attention on database, especially the db design and application. Use "explain plan" or sql trace

[Pedro Sousa]

Hi!
You should use glance to check your overall performance. It will give you metrics on network, memmory, disk, swap, kernel, ...
If you don't have it installed, you can install a trial version from the application CDs.

After this, you should check your kernel parameters and optimize them.
dbc_max_pct should be ~10
dbc_min_pct ~6
bufpages 0
nbuf 0

plus, pay attention to shmmax, semmni, semmns.

check this doc for general kernel information:
http://docs.hp.com/hpux/content/KCparams.OverviewAll.html
good luck.

[Alexander M. Ermes]

Hi there.
This may also depend on the network.
Another step may be to separate the files on different disks with ( if possible ) different controler. You can put the breaks on your database, when you have logfiles, offline logfiles and tablespaces on the same physical disk. Try to put logfile on one disk, control files on a second, tablespaces for tables on a third, tablespaces for indexes on a fourth, but only if possible. Also the optimizer mode
can be responsible for the speed.
Perhaps you can put the init.ora and the filelist as an attachment to this place.
Rgds
Alexander M. Ermes