oracle 8.1.7, HP-UX 11i Performance

[Jean-Luc Oudart]

Sam

ora_snp processes are used for snapshot refresh.

Regards,
Jean-Luc

[Sameer_Nirmal]

Guys,

I have one more doubt about OS AIO...
Currently in my system /dev/async and /dev/asyncdsk are nor configured in kernel.. But there is a kernel parameter
max_async_ports which is 50 currently.
Suppose if i increase the value to 1000, can i see improvement in the I/O without adding /dev/async to system and for oracle???

Today I collected the BSTAT/ESTAT reports. I am attaching both OS and Oracle statistics herewith for your refernce.

I am expecting replies on EVA question and existing kernel parameters, sizing as well..

BTW , anyone know how to do proper shutdown of EVAs?

Regards
Sam

[Brian Crabtree]

Sam,

I/O doesn't appear to be a huge issue for your system. Memory usage does from one of the reports (Was the 'a' report taken close to a restart?)

I would recommend changing the dbc_max_pct kernel parameter to 5, and the dbc_min_pct kernel parameter to 3.

Then increase the db_block_buffers to 300000 (roughly 2.5g). This should help with the cache buffer chains waits and buffer waits.

Note 139272.1 on Metalink appears to be the best resource for configuring async I/O.

Brian

[Sameer_Nirmal]

Hi Guys,

I referred the metalink document 139272.1 for Async I/O. This doc says, It is not recommended to use both multiple DBWRs and aio.

DBA had already enabled multiple DBWRs ( 4 in my case as no of processors ) . So can't I enable aio???? Any advise???

Brian, you are right. In fact, I had recommanded to DBA to increase the SGA around 3GB to reduce disk I/O. DBA is grdually doing it though , it is now 2.1GB
I feel this is still low for current application.

Anyone can put more light on the BSTAT/ESTAT report attached before so that proper sizing of SGA could be done for 1000 users???

I would like to know a proactive assessment for accomodating more users by sizing the system based on some logical calculations..

There is no one to eloborate on EVA performance with database ???????

Regards,
Sam

[Jean-Luc Oudart]

Sam

BSTAT/ESTAT aren't as good as statspack + more information to be provided. Could you forward statspack reports (when response time is ok and when it's not).

Also, what kind of snapshot do you run on the database, size, frequency.

Also, are you using MTS ?

Regards,
Jean-Luc

[Sameer_Nirmal]

Jean,

To my knowledge, Statpack is available with 9i. Is it available for 8i as well?
If yes, pls let me know where can I get it and how to use it. DBA has not yet used Statpak.

How do I know the details about snapshots?? pls advise...

I am not using MTS , but dedicated one. Do I need to enable MTS?? , How can see the performance improvement after enabling it? Is there any overheads involved???

In my last reply, I had mentioned about DBRWs and AIO.. DBA has enabled multiple DBRWs to improve the performance. I guess AIO should weigh more than multiple DBRWs, am I right????

I referred a metalink document, whch says enabling both AIO and DBRWs is not recommanded...
I think , multiple DBRWs has to be diabled and AIO should be enabled.. This should work and give better improvement in performance
your advise pls...

Regards
Sam

[Jean-Luc Oudart]

Sam,

Statspack is available on 8i (I'm using it).
follow instructions (attached)

If you don't use MTS and don't have memory issue (you aren't paging on the server) I would not go to MTS.

Regards
Jean-Luc

PS :don't forget to assign points to the forumers

[Rita C Workman]

Alot of great advice above...just thought I'd throw in some 'things' I look at for a quick check on Oracle and possible I/O performance..

1. I check the ~init.ora file to see just exactly where they are writing their 'log' files. You can get alot of I/O contention if they are writing their cntl logs,and especially archive logs, etc. to the same disk ! So check where they say they are putting it, and then check what disk they all hit. Any on the same disk...your I/O busy at certain times. Keep archive log writes to their own disk...it will help improve performance.
...I even seen where they had db files & on same disk they had a copy of their control logs & some of the archive logs had all hit on the same disk...[Talk about certain disks staying clobbered.]
2. The more users the slower it gets...sounds normal, but is Oracle logging every transaction ??...do you need to do this. Takes alot of time and may not be needed overhead.
3. You might even check what disks are getting hit the hardest..use Glance or sar. See what is on those disks...can you move the data around on disks to improve performance. pvmove does a great job for you on this.
4. If you can....I find setting up my mount options for Oracle to really help things. Example:
-o delaylog Makes the intent log writes asynchronous
-o mincache=direct All reads and non o-sync writes are unbuffered. This degrades performance..but..trade off is to reduce the need for pages in buffer cache vs process blocking. Good to use for large i/o direct from apps.
-o convosync=direct Converst reads and o-sync writes to unbuffered. Improve performance. Trade off is b/t increased perforamnce when source code is not available vs possible loss of data integrity.
-o nodatainlog Posts o-sync writes of data directly to the file. Trade off is fewer writes with possible increase head movement vs double writes w/less head moves.
...Do you/can you take advantage of any special mounting options...??


On your parms...do you really need dbc_max% to be at 20....I have 4CPU with 8GB of RAM running on a 7410, and find that 7% max with a 5% min works well too....Remember 300-400Mb should be plenty for the system. At 20, this may be something to consider reducing a little.
I also would look at your semmaphore settings..just a thought.

HTH,
Rita

[Yogeeraj_1]

hi sam,

Me too use statspack on oracle 8.1.7.4.

The general guideline is to take a "level 10" every 15 minutes for a day. Then, pick a representative 15 minute window when performance was "felt to be at its worst" and analyze that one.

You may wish to refer to the Metalink note: 149113.1 - "Installing and Configuring StatsPack Package" for more information concerning the installation.

good luck!
best regards
Yogeeraj

[Jean-Luc Oudart]

Hi Sam

any update ?
did you manage to run the statspack report at normal and peak time ?
any findings ?

Regards
Jean-Luc