HPUX Itanuim perfermance with Oracle database 10g

[ouazir]

Hello every one,
i have a problem with my server/database configuration,
my server is HP 6600
8 Dual core with 32Go of RAM
I think the resource is not exploited optimally, in the case of the of CPUs, so I have a job which runs under the Oracle database in my server and requires an enormous time (12 hours) to run this job When I noticed that the CPU is not used for optimum, you find the result of TOP command:
Load averages: 0.05, 0.05, 0.06
496 processes: 391 sleeping, 105 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.06 2.2% 0.0% 0.2% 97.6% 0.0% 0.0% 0.0% 0.0%
2 0.04 0.6% 0.0% 0.4% 99.0% 0.0% 0.0% 0.0% 0.0%
4 0.05 1.4% 0.0% 0.2% 98.4% 0.0% 0.0% 0.0% 0.0%
6 0.05 1.2% 0.0% 1.0% 97.8% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.05 1.4% 0.0% 0.4% 98.2% 0.0% 0.0% 0.0% 0.0%

System Page Size: 4Kbytes
Memory: 13880636K (12730484K) real, 37761112K (35715352K) virtual, 2313344K free Page# 1/20

[Dennis Handly]

Your 4 (I don't see 8) CPUs appear to be mostly idle. Are you waiting for I/O?
What does "swapinfo -tam" show? Do you have enough memory?
What are your top 10 processes?

[Emil Velez]

Have you looked at oracle tuning and query optimization. Many times the queryies in the jobs can be made faster with optimization and indexes.

[ouazir]

Hello Dennis of course this is the apps server where the EBS application is installed, but what is sure is that the job runs on database server because it's where the data are manipulated here is the results of system commandes:
TOP :
System: erpdb Sun May 29 07:32:17 2011
Load averages: 0.04, 0.04, 0.06
904 processes: 812 sleeping, 92 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.03 0.0% 0.0% 12.1% 87.9% 0.0% 0.0% 0.0% 0.0%
2 0.05 1.0% 0.0% 0.4% 98.6% 0.0% 0.0% 0.0% 0.0%
4 0.04 15.2% 0.0% 0.2% 84.6% 0.0% 0.0% 0.0% 0.0%
6 0.03 0.2% 0.0% 0.2% 99.6% 0.0% 0.0% 0.0% 0.0%
8 0.03 0.4% 0.0% 0.2% 99.4% 0.0% 0.0% 0.0% 0.0%
10 0.04 0.0% 0.0% 0.2% 99.8% 0.0% 0.0% 0.0% 0.0%
12 0.04 0.2% 0.0% 1.4% 98.4% 0.0% 0.0% 0.0% 0.0%
14 0.03 0.2% 0.0% 0.4% 99.4% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.04 2.2% 0.0% 1.8% 96.0% 0.0% 0.0% 0.0% 0.0%

System Page Size: 4Kbytes
Memory: 11097732K (4854468K) real, 17319116K (8876216K) virtual, 2722160K free Page# 1/42

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
4 ? 29925 oraprod 191 20 2430M 32044K run 0:07 6.85 6.83 oraclePROD
6 ? 29491 oraprod 154 20 2422M 21308K sleep 0:02 3.39 3.39 oraclePROD
12 ? 1741 root 152 20 44040K 2804K run 4305:18 3.13 3.12 cimprovagt
2 ? 24732 oraprod 154 20 2653M 23964K sleep 0:09 2.08 2.07 oraclePROD
12 ? 2521 root 168 20 15620K 1528K sleep 2697:35 0.96 0.96 utild
12 pts/ta 523 root 178 20 11716K 2584K run 0:00 0.73 0.66 top
0 ? 664 oraprod 154 20 2400M 6368K sleep 0:00 1.89 0.49 oraclePROD
8 ? 29549 oraprod 154 20 2410M 15420K sleep 0:00 0.40 0.40 oraclePROD
8 ? 662 oraprod 154 20 2399M 4936K sleep 0:00 0.69 0.27 oraclePROD
0 ? 654 oraprod 154 20 2400M 6032K sleep 0:00 0.44 0.24 oraclePROD
10 ? 25707 oraprod 154 20 2422M 12228K sleep 0:05 0.24 0.24 oraclePROD
2 ? 1929 root -16 20 95232K 66308K run 2060:56 0.21 0.21 midaemon
0 ? 511 hypdb 154 20 1400M 10504K sleep 0:05 0.20 0.20 ora_cjq0_hypdb
4 ? 29524 oraprod 154 20 2405M 10128K sleep 0:00 0.19 0.19 oraclePROD
10 ? 639 hypdb 154 20 1396M 5972K sleep 0:00 0.23 0.17 ora_j000_hypdb
12 ? 12812 orapprd 154 20 1308M 7892K sleep 28:31 0.15 0.15 ora_cjq0_PPRD
14 ? 25057 oraprod 154 20 2422M 24324K sleep 0:18 0.15 0.15 oraclePROD
8 ? 82 root 152 20 25560K 22720K run 365:14 0.09 0.09 vxfsd
6 ? 2 root 128 20 72K 64K sleep 555:55 0.07 0.07 vhand
12 ? 9953 oraprod 154 20 2399M 5236K sleep 0:06 0.07 0.07 ora_mmnl_PROD
4 ? 84 root 152 20 648K 576K run 241:43 0.07 0.07 pm_schedcpu

#swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 8192 6 8186 0% 0 - 1 /dev/vg00/lvol2
reserve - 8186 -8186
memory 31137 14185 16952 46%
total 39329 22377 16952 57% - 0 -

- I have attached a snapshot for glance commande, and to answer your question i have no waite for I/O, max of memory is 2Go free when every thing is up,but my problem is
why is the number sleeping process (812 sleeping) contrariwise the runing process 92 running ?

[Dennis Handly]

This other system is 96.0% idle and your top processes are oraclePROD.
And you have free memory.
Most of your I/O is to /oraprod.
You don't seem to have any page outs.
And your Oracle processes seem to be waiting for Other or Socktl

>my problem is why is the number sleeping process (812 sleeping) contrariwise 92 running?

It seems your apps server isn't sending it enough work to keep it busy.

[ouazir]

Its the same thing that i have thinked about, the database submit the threads for the system but this one takes a long time to allocate CPUs for them and it puts them in idle state. so this is a part of ASH report for the TOp events :
Top User Events

Event Event Class % Activity Avg Active Sessions
CPU + Wait for CPU CPU 88.09 1.06
db file sequential read User I/O 5.26 0.06
log file sync Commit 1.94 0.02
this provide to us an idea, that the cause of waitting time is from the CPUs,
i have attached the result of this sql statement. it gives us more detailed wait event information,
Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
-- see code depot for full script
And c.wait_class in ('Application','Concurrency')
order by average_wait desc;

So how can i identify the reason of watting for CPUs at system level. thnx for your help.

[Volker Borowski]

Hi,

this needs to be analyzed in the DB.
If your license allows, create an oracle awr report for the timeframe where the job is executed.
This should give you plenty of information of what happens inside the DB.

Volker

[Steven E. Protter]

Shalom,

>my problem is why is the number sleeping process (812 sleeping) contrariwise 92 running?

This is not a problem. Your server is under utilized.

Increased use of the application will more than likely remedy this issue.

More basic, is there a response problem being reported by users of the application? If not, you might want to move on to a more productive dispute.

Regards,

SEP