topic Re: Oracle slow. in Operating System - HP-UX

Oracle slow.

Atul Thakur — Mon, 16 Apr 2001 17:40:30 GMT

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

Re: Oracle slow.

Gulam Mohiuddin — Mon, 16 Apr 2001 17:47:45 GMT

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

Re: Oracle slow.

Alan Wyskowski — Mon, 16 Apr 2001 19:39:50 GMT

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.

Re: Oracle slow.

Tim Medford — Mon, 16 Apr 2001 19:40:37 GMT

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

Re: Oracle slow.

Volker Borowski — Tue, 17 Apr 2001 05:56:00 GMT

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

Re: Oracle slow.

Volker Borowski — Tue, 17 Apr 2001 05:56:01 GMT

Re: Oracle slow.

Michael Lee_4 — Tue, 17 Apr 2001 07:26:58 GMT

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

Re: Oracle slow.

Pedro Sousa — Tue, 17 Apr 2001 07:57:24 GMT

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.

Re: Oracle slow.

Alexander M. Ermes — Tue, 17 Apr 2001 12:49:24 GMT

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