Hi,
Version 10204.
End users are compliend about poor performance of some of our daily jobs.
I have run addm and awr report and adviced that the performance is beacuse of CPU bottleneck .
Addm shows :
============
FINDING 1: 77% impact (11626 seconds)
-------------------------------------
Host CPU was a bottleneck and the instance was consuming 17% of the host CPU.
All wait times will be inflated by wait for CPU.
RECOMMENDATION 1: Host Configuration, 67% benefit (10222 seconds)
ACTION: Consider adding more CPUs to the host or adding instances serving the database on other hosts.
...
RECOMMENDATION 2: SQL Tuning, 9.3% benefit (1415 seconds)
..
RATIONALE: SQL statement with SQL_ID "bys8bks9azc46" was executed 516616
times and had an average elapsed time of 0.0028 seconds.
RATIONALE: Average CPU used per execution was 0.0018 seconds.
....
ADDITIONAL INFORMATION: Host CPU consumption was 94%.
AWR shows :
============
Elapsed: 60.32 (mins)
DB Time: 252.61 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 16,496M 16,496M Std Block Size: 4K
Shared Pool Size: 1,824M 1,824M Log Buffer: 14,340K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,644,501.64 879,327.35
User calls: 5,103.88 2,729.08
Parses: 595.08 318.19
Hard parses: 0.12 0.06
Sorts: 574.83 307.37
Logons: 0.27 0.15
Executes: 3,563.43 1,905.39
Transactions: 1.87
% Blocks changed per Read: 26.58 Recursive Call %: 18.22
Rollback per transaction %: 0.34 Rows per Sort: 5.77
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.96 Redo NoWait %: 100.00
Buffer Hit %: 98.62 In-memory Sort %: 100.00
Library Hit %: 100.01 Soft Parse %: 99.98
Execute to Parse %: 83.30 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 55.60 % Non-Parse CPU: 98.98
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read 1,343,144 9,696 7 64.0 User I/O
CPU time 4,935 32.6
Operating System Statistics
Statistic Total
-------------------------------- --------------------
AVG_BUSY_TIME 340,619
AVG_IDLE_TIME 21,003
AVG_IOWAIT_TIME 8,422
AVG_SYS_TIME 216,556
AVG_USER_TIME 123,968
BUSY_TIME 2,725,866
IDLE_TIME 168,918
IOWAIT_TIME 68,261
SYS_TIME 1,733,286
USER_TIME 992,580
LOAD 3
OS_CPU_WAIT_TIME 6,648,289,867,500 <<<<--------
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 61,071,360
VM_OUT_BYTES 0
PHYSICAL_MEMORY_BYTES 68,649,152,512
NUM_CPUS 8
NUM_CPU_SOCKETS 8
....
TOP SQL ordered by CPU Time is as follow:
As you can see bellow and as was suggested by addm the statments are running very fast and there is no place for tunning the statment themself.
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
974 1,452 516,616 0.00 9.6 bys8bks9azc46
806 848 614,295 0.00 5.6 27k2t7quuvfgw
430 687 646,527 0.00 4.5 4vzwkqwcf57g9
284 1,578 516,638 0.00 10.4 1dzphp4huys5c
227 1,262 149,889 0.00 8.3 a41jpq2za18t1
Also , SQL ordered by Parse Calls shows high volume of parsing (mainly soft pars)
-> Total Parse Calls: 2,153,838
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
140,309 140,310 6.51 6wz3unjt67675
90,425 90,425 4.20 27axdr2bax09z
90,425 90,425 4.20 41rjjukjazn79
90,425 90,425 4.20 4f746wudj51br
90,423 90,423 4.20 0cqfxvkv3x8ca
90,423 90,423 4.20 112k25pgmqtxx
....
But it seems that in total activity it does not effect performance alot.
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 14,557.7 96.0
DB CPU 4,934.6 32.6
PL/SQL execution elapsed time 175.7 1.2
parse time elapsed 77.3 .5
connection management call elapsed time 58.2 .4
inbound PL/SQL rpc elapsed time 36.3 .2
sequence load elapsed time 33.4 .2
RMAN cpu time (backup/restore) 4.7 .0
hard parse elapsed time 3.0 .0
hard parse (sharing criteria) elapsed time 1.1 .0
PL/SQL compilation elapsed time 0.3 .0
repeated bind elapsed time 0.1 .0
failed parse elapsed time 0.1 .0
hard parse (bind mismatch) elapsed time 0.0 .0
DB time 15,156.5 N/A
background elapsed time 5,638.8 N/A
background cpu time 169.2 N/A
Any suggestion what should be done as a DBA (beside adding more cpus ...)
Thanks