Very high disk-intensive Oracle application.

[Gulam Mohiuddin]

We have a very high disk-intensive Oracle9i database Application with read write Ratio of about 70:30. We are trying our best to tune specific SQL code and backend Oracle database but would also appreciate your input/advice to optimize LVM disk and HP-UX Kernel configurations on the box.

Our environment is Oracle9i (9.2.0.6 64-Bits) on HP-UX 64-Bits with EMC SAN. We have tried to distribute data files across all allocated EMC LUNs to improve read performance for sequential disk access.

We can not use RAW filesystem but I am exploring the option of bypassing filesystem buffer cache for VxFS along with: “delaylog”, “nodatainlog”, “mincache=direct”, “convosync=direct” parameter settings.

All your input and recommendation would be highly appreciated.

Thanks,

Gulam.

[RAC_1]

You pretty much covered most of the things. With vxfs special options, you won't see much difference, but it does give some increase in performance. The thing with oracle is SAME(Stripe everything and mirror everything.)
If you are going to set server only for this (database apps), then you can set buffer cahe at reasonable level (oracle does it's own buffering) and give maximum memory to oracle. (A large SGA?) and get optimum performance from it.

[A. Clay Stephenson]

The only other tunable you might play with is scsi_max_qdepth and if you do use the vxfs mount options to bypass the buffer cache then you can reduce the size of the buffer cache and increase the size of the SGA. Realistically, all the OS tuning in the world might get you a 10-20% improvement so the real answer is to improve your code. If you are running RAID5 (or a variant of RAID5) then you should consider RAID 1/0.

[Tim Nelson]

I agree with the others.

Review your disk queues and response times. If there are none then the disk responding and the issue lies elsewhere.

If there are queues and poor response then concentrate on layout but do not forget to review the stats from the Symm side. If hit % is nice and high (80%-90%) and the metas are performing then the symm is doing its job and the delay lies somewhere else.

Small OS cache big Oracle SGA.

Heavy writes will use up your symm cache and when in destage all reads will be at spindle speeds.

0+1 is the way to go. In a large environment overlap on the controllers could be an issue as well as overlap on the physical disk.

Keep digging your way in until you find a solution or have to double back and look elsewhere.

If all else fails, more spindles !!

[Bill Hassell]

Definitely run the Oracle statspack to see where the highest loads are occuring. Run explain's on the slowest SQL code to see if searches are partially indexed. Check that SGA is usefully large (3-10 Gb) - by useful, the size allows Oracle to heavily buffer it's data and easily perform row insertions. Using sar -d 2 20 during busy times, make sure the avque is less than 1 and the avserv time less than 10. If the sar data looks high, then EMC tuning may be needed. Are you running 11.23 with lots of processors and lots of RAM?

[TwoProc]

Well,

If you've actually tuned that thing up for all you can get (that's a good thing). You need to start figuring if you can start buffering some of that I/O.

The following train-of-thought is something to address AFTER you've properly tuned the database queries and the database structure, and it sounds like for the most part that you already have.

What is your current db_cache_size? What is your current hit ratio? Do you have room in your server to increase the cache size without going into swap? Looking at Glance, and running the Memory Report, how much free memory do you see available during the peak load period of the day?

[sathish kannan]

Gulam,
I don't know whether you have EMC Symmetrix or Clarrion. If it is a Symmetrix environment, try to map your disk to multiple FA's as possible.

RAID 1 is good for large writes and RAID 5 is good for large reads.

Do you have any multipathing software? Try using multipathing software.


Regards
Sathish

[Steven E. Protter]

Shalom,

A lot can be done on the EMC side. The tablespaces that undergo heavy writes should be on raid 1 not raid 5 storage. This improves write performance dramatically.

SEP