high cpu priority queue > 15 on hp-ux 11i v2 servers

[JanShu150]

Hi All, Greetings.

My DB team said their dataloading has been taking longer time (20% slower) to complete since last month. It is on a hp-ux 11.23 (patch level 12/2010) oracle db 10g server, it has global cpu util 100% (high user cpu %) and cpu priority queue > 15 when the data loading and batch job were running (I don't have the OVPA logs for 12/2011 to compare with the 2/2012 perf logs). The dataloading usually 4 days to complete 2 months ago, last week it took 4.5 days. There are 8 cpu processors and 40GB physical memory on this vpar server. It is connected to a EMC san storage. The compressed data load file size had 8% increased last week.

I checked the HP Performance manager 9.0 graphs and drill down to the process tables, I found several of the Oracle processes had stop reason PRI or CACHE or sometimes IO. How to tell if this cpu bottleneck (cpu priority queue > 15 ) caused by the increase of the 8% data load file size? or slow sql statements? or the network traffic? or the disk IO? What should I ask my DBA team to check on this performance issue (I need specific db questions)? I have read the

HP-UX Performance Cookbook 7/2009 doc, but I am still trying to figure out the solution for this issue.

Thank you.

Kind Regards,

Jan

[Dennis Handly]

>How to tell if this CPU bottleneck (CPU priority queue > 15) caused by the increase of the 8% data load file size? or slow sql statements? or the network traffic? or the disk I/O

CPU bottlenecks are usually not caused by I/O or network traffic.

[Henry Fauni]

> The compressed data load file size had 8% increased last week

With 8% increase of "compressed" data load, you're also looking at 12.5 % increase in length of time to complete. Do you have any history data to compare these rates of changes in order to determine if it's normal? Using glance during the actual "data load or batch job" might help to determine where the bottlenecks are. Having to wait 4.5 days to run something - is quite a long time for me. I'm impatient, I would look for a new solution or perhaps redesign the whole process.

[Bill Hassell · Accepted solution]

Your system is being asked to use 100% CPU so if you want better performance without fixing the SQL and DB settings, you'll need to add a bunch more, perhaps a total of 16. All processes will be WRI, CACHE, IO (and many other) states and these states change far faster than you can see -- so these are not useful. If you disk rates are in the 10's of thousands, storage is probably OK. Make sure your HP-UX buffer cache is *not* 50% (the kernel parameter dbc_max_pct) so more RAM can be given to the Oracle SGA.

However, when debugging database performance, I find that bad SQL designs and poor Oracle settings are the biggest bottlenecks. Your DBAs need to use Statspack and use the numerous performance tuning articles available on the net.

You mentioned vpars. Are you trying to run a bunch of other vpars on this server at the same time?

[JanShu150]

Hi Dennis,

Thanks for the info, but I was told that if a priority process was waiting for IO, then it would show as a cpu priority queue as well. Although on this server, I didn't see high bydisk physical IO utilization, so you are correct :)

Thank you.

Kind Regards,

Jan

[JanShu150]

Hi Henry,

That's a great idea and I can ask the programmers if they have time to redesign this process.

How to assign points to the reply?

Thank you.

Kind Regards,

Jan

[JanShu150]

Hi Bill,

Thanks for the helpful input. This 11.23 vpar server is going to migrate to a new i2 blade 11.31 server in 2 weeks.

How to assign points to your reply?

Thank you.

Kind Regards,

Jan

[Dennis Handly]

>How to assign points to the reply?

Just click on the kudos star on each helpful post.

http://h30499.www3.hp.com/t5/help/faqpage/faq-category-id/kudos#kudos