oracle 8.1.7, HP-UX 11i Performance

[Sameer_Nirmal]

Hi Guys,

I am looking for quite valuable information from with regard to my problems and queries. Full marks for the significant information!!!!!!

I am facing system performance issue , saying system working slow if number of oracle user increases ( > 250)
End user complain slow dataprocessing , query etc....
My system acts as Database server. Fornt end is D2K and middleware is one application server ( Novell based )
Let me give here the system details.

I have one rp7410 server with 4 ( 875MHz ) CPUs, 8GB RAM, only one hard partition, HP-UX 11.11. This server is connected to 2 EVA 5000 ( 2C2D-C) boxes. Each box contains 16x73GB HDDs, 2X HSV110 Controllers, one 16 port
Brocade SAN Switch ( BS ) with SMAIII installed in one of the boxes.

There are 4 internal disk each of 36GB . 2 disk are mirrored for OS and two for Oracle software ( 8.1.7 ) . The archive
log ( VxFS filesystem ) is stored on external 2x DS 2100 with mirroring ( 72GB ). We are using raw devices created with LVM on EVAs for storing Oracle datafiles redologs and control files. Each EVA has only one disk group
with VRAID 1 ( protection level 1 ) . Zoning is done with 3 HBAs for Each EVA from server side. Similarly SP is installed.

My initial investigation shows that system has intensive I/O (%wio upto 90) maybe because of sync i/o used by oracle . I have read couple of threads here for ASYNC I/O. But I still have doubts.... In our system , in oracle AIO is enabled . there is no /dev/async or /dev/asyncdsk file. My queries in this regards are

1. Which AIO is being used by Oracle if OS AIO is not enabled ( currently ) ? How to measure its performance ?
2. What way OS AIO is different from Oracle AIO?
3. Why oracle suggest to use /dev/async ( OS AIO ) even it works without it?
4. What is differnce between /dev/async and /dev/asyncdsk?
5. If i enable the OS AIO, then how do I find its statistics? Which other kernel parameters related to AIO can be tuned?

As per HP , EVA is smart to do self monitoring and adjustments depending on the load. How do I measure the performance of EVAs????

I have suggested to DBA to increase the size of SGA ( currenyl 1.9GB and incresing ) to reduce disk I/O .
What are other oracle parameters could be changed to improve I/O? . Current AIO kernel parameters are

aio_listio_max - 256
aio_max_ops - 2048
aio_physmem_pct - 10
aio_prio_delta_max - 20
max_async_ports - 50
dbc_max_pct - 20
maxuprc - 800
semmni - 800
semmns - 2048
semmnu - 512
shmmax - 4294967295
shmmni - 2000

Oracle paramters are

processes - 1200
disk_async_io - TRUE
db_file_direct_io_count - 64
db_file_multiblock_read_count - 64
db_block_buffers - 160000
db_block_size - 8192
shared_pool_size - 450000000
sort_area_size - 3072000

If you need anyother details. pls let me know
If any one of can share the Oracle Metalink docs, Hp docs on the whole issue would be appretiated

Any network statistics needs to be known???? We have only one instance. We have two un-used 1000Mbps Lan
connections free.. How do i load balance the network I/O across these ethernet cards??

Is there any other way to improve the I/O ???? the size of the database interms of objects is 90GB . in term sof datafiles - 135GB
Client is expecting more users around 1000 on the same system. Hence I need to work out ASAP
Your early Replies are appretiated.

Thanks in advance
Sam

[Brian Crabtree]

Ok. There are going to be alot of questions that we will need answers too as well, but.

1. AIO is not available outside of raw filesystems. If you aren't using raw filesystems, you probably aren't using AIO.

2. No difference, same thing, oracle just uses it.

3. They normally suggest it to stop errors.

4 & 5. No idea.

Ok, now for mine.

What kind of application is it (OLTP, DSS), and what kind of queries are being sent against the database?

Have you checked the memory usage on the system when the number of users increased?

Have you considered setting up multi-threaded servers on the database?

Does this server do anything else (web, app server, etc) or is it just database?

Have you checked internal to oracle about what waits are being generated?

Have you considered, and do you have a way to generate a trace file for a standard session to see what it is doing?

Are you using true temporary tablespaces (create temporary tablespace)?

Thanks,

Brian

[Steven E. Protter]

Oracle will peform better with asynchronous i/o. Thats beeen pretty well tested over the years.

All the databases I run do better running in with asynchronous i/o

I'm attaching some data performance collection scripts that run background. They'll allow you to measure performance over any time period and make good comparisons.

SEP

[Indira Aramandla]

Hi Sameer,

As a DBA / system administrator, you will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
· Quantifiable performance
· Unmeasurable performance like user dissatisfaction.
Attached document will help identify quantifiable performance problems and methods to solve them. When performance-tuning databases, it is a good to split the problem into parts to facilitate the process. Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning


Wish you good luck.

Indi

[Indira Aramandla]

Hi Sameer,

Here are few more documents.

[Yogeeraj_1]

hi Sam,

I would first start by running a Statspack report to see what is happening at the Oracle Level and analyze the report.

If you find something that need to be tuned the database level, do it first before looking at the OS Level..

Hope this helps too!

regards
Yogeeraj

[Jean-Luc Oudart]

Sam,

You says that the system response time is heavily hit if number of users is > 250.

Could you monitor the OS and database (statspack) when you have less than 250 users and when you have more than 250 users.

The 1st set of reports (system + oracle) will give you the baseline when the system is ok (users prospective).
Then compare the second set of reports for response time is a problem.

If you can run it before the SGA is increased as you don't want to hide the problem.

Post the results, there are plenty of guys around here to help.

Regards,
Jean-Luc

[Sameer_Nirmal]

Thanks Everyone for your prompt replies, appretiated....
Thanks Indira and SEP for sharing valuable documents.

Today I collected system statistics which shows that application is I/O bound. CPU load is 40% and 2GB memory is free. Hence I am thinking that enabling OS AIO would definatly going to give improvement. But before this change, I would be checking Statpak report as well with DBA ( not present today). BTW, what is important in the Statpak report to look into for I/O or rather all?

Being OLTP database server only , I see equal ratio for read and write transactions.
Users queries looks like reading data from multiple tablespaces and processing it..
I see temporary tablespaces as well created.

How do I trace sessions to know what there are doing? How the oracle partitioning work?
Can we think of partitioning the huge tables across two EVAs????

Well, this is quite in advance but need to ask. How to do proper sizing of the whole system ( OS and Oracle) for 1000 users ???

Any comments on my EVA and network questions??
Are those unix kernel and init.ora parameters ok??

Thanks & Regards
Sam

[Sameer_Nirmal]

I see ora_snp0 is the processes which is using lot of CPU resources.. I see some oracle processes like COMMAND=prespown,Protocol=TCP ( Service )
What are these processes???
There distributed transactions as well. I remember DBA telling, transations are replicated in the local database and as well as remote database over a wan link. Snapshots compilation was done for remote database as well.

Any best practises for Snapshots ??? tuning etc???
any tuning for listner????

[Jean-Luc Oudart]

Sam,

as mentioned in my previous post, it would be good to have more than one report (baseline and pb time).

In you statspack report, check :
- Instance Efficiency Percentages
(are all sort in memory)
- the top 5 wait events

the check the SQL ordered by read and so on

cf. attached document re statspack

Regards
Jean-Luc

[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

[Sameer_Nirmal]

Jean,

The issue is being referred to Oracle for further investigation.

Anyway, How the proper sizing of the system could be done for 1000 oracle users considering the system as whole?

Regards,
Sam