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