Operating System - HP-UX
1748051 Members
4969 Online
108758 Solutions
New Discussion юеВ

Re: Slow IO Performance for Oracle on 11.31 versus 11.23

 
CameronY
Frequent Advisor

Slow IO Performance for Oracle on 11.31 versus 11.23

Hi,

I made a comparison on Oracle query response time on Oracle 10g database on HP-UX 11.31 and 11.23 (production). The database on 11.31 in a production copy.

The response time of query on 11.31 is 3 times slower on 11.31. I ran Oracle AWR reports on both servers, confirmed that they both using the same Oracle execution plan and the same number of the Oracle block read requests. The only different between them is the average IO wait on 11.31 is higher.
5 REPLIES 5
Shibin_2
Honored Contributor

Re: Slow IO Performance for Oracle on 11.31 versus 11.23

Have you found any issues at server side, other than this slowness in DB ?
Regards
Shibin
Hein van den Heuvel
Honored Contributor

Re: Slow IO Performance for Oracle on 11.31 versus 11.23


Looks like you did your homework on this problem.

When you mention average IO wait, is that as per Oracle, or as per the OS?

Too few folks realize that an accute CPU shortage will cause Oracle to report slow IO. This is because for Oracle to 'see' the IO complete, the initiating process needs to be scheduled again, so there is some CPU and possibly a lot of scheduling time included in the IO time as reported by Oracle. Admittedly, typically an application would need to be in the high cpu load ranges for this to play a role.

What does oracle report as the main component for the transaction (wait_time)?
Is the cpu time for top queries similar (and significant?)

Are the servers and storage sufficiently comparable?

Hope this help some,
Hein van den Heuvel.

Re: Slow IO Performance for Oracle on 11.31 versus 11.23

One of the BIG changes between 11.23 and 11.31 is the new MPIO stack... how that affects your IO perf depends on the sort of storage you have. So what sort of storage are you using here and tell us some details of how the IO stack is configured...

ASM? Files? Logical vols - How many? Physical disks or RAID'd LUNs on external storage, how many and of what type etc etc...

Specificaly what sort of storage array, and how were you managing MPIO on the 11.23 system?

HTH

Duncan

I am an HPE Employee
Accept or Kudo
Hein van den Heuvel
Honored Contributor

Re: Slow IO Performance for Oracle on 11.31 versus 11.23

What he said! (Duncan).

While I do not like 'shooting from the hip', in this case I would first check the SCSI queue depth. The default is just 8 which is often not nearly enough for a lun with many physical disks behind it. Perhaps it was adjusted on the 11.23 system and not on the 11.31 ?

google for queue_depth for details.

It may need to be in the 42.

Or 50 or 60 or 100. The science is too hard. If you have an important, repeateable query then try a few settings (8, 16, 32, 64, 128) and look for diminishing returns. Tweak test values for binary search if it matters a lot.

fwiw,
Hein

Court Campbell
Honored Contributor

Re: Slow IO Performance for Oracle on 11.31 versus 11.23

I wish I could compare with what you have given. But You have not stated any differences in hardware. Is one PA and another IA. More info would definitely be useful.
"The difference between me and you? I will read the man page." and "Respect the hat." and "You could just do a search on ITRC, you don't need to start a thread on a topic that's been answered 100 times already." Oh, and "What. no points???"