Home » RDBMS Server » Performance Tuning » Oracle 10.2.0.4 (IBM AIX 5.3)
| Oracle 10.2.0.4 [message #429826] |
Thu, 05 November 2009 22:19  |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Hi,
Our DBA built a new database for production on new machine which has more number of CPUs,powerful and more memory.But when multiple jobs are running in parallel its performance going down than the old prod machine which has less powerfull CPU and less memory.What are basic steps need can check and rectify this problem and how to identify whether it is machine problem or database.
Any reply will be really appreciated.
thanks
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle 10.2.0.4 [message #429933 is a reply to message #429826] |
Fri, 06 November 2009 06:31  |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Just sending 5 Timed Events to get some help.
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
PX Deq Credit: send blkd 583,175 27,247 47 60.2 Other
CPU time 6,637 14.7
log file sync 34,031 4,731 139 10.5 Commit
log buffer space 9,052 3,883 429 8.6 Configuration
db file parallel write 14,797 3,026 205 6.7 System I/O
|
|
|
|
|
|
| Re: Oracle 10.2.0.4 [message #430021 is a reply to message #429826] |
Fri, 06 November 2009 23:49 |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Adding the Wait Events Statistics for reference.
Wait Events Statistics
Time Model Statistics
Wait Class
Wait Events
Background Wait Events
Operating System Statistics
Service Statistics
Service Wait Class Stats
Back to Top
Time Model Statistics
Total time in database user-calls (DB Time): 45275.2s
Statistics including the word "background" measure background
process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 40,123.10 88.62
DB CPU 6,636.64 14.66
parse time elapsed 649.56 1.43
hard parse elapsed time 296.52 0.65
hard parse (sharing criteria) elapsed time 42.92 0.09
connection management call elapsed time 15.61 0.03
sequence load elapsed time 1.19 0.00
PL/SQL execution elapsed time 0.71 0.00
PL/SQL compilation elapsed time 0.61 0.00
repeated bind elapsed time 0.06 0.00
hard parse (bind mismatch) elapsed time 0.00 0.00
DB time 45,275.23
background elapsed time 5,515.36
background cpu time 228.44
Back to Wait Events Statistics
Back to Top
Wait Class
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Other 43,070,203 98.92 27,406 1 1,225.57
Commit 34,031 9.73 4,731 139 0.97
Configuration 50,684 87.48 3,892 77 1.44
System I/O 69,119 0.00 3,518 51 1.97
User I/O 54,578,694 0.00 692 0 1,553.05
Application 17,114 0.65 641 37 0.49
Concurrency 22,476 0.41 485 22 0.64
Network 1,761,646 0.00 26 0 50.13
Back to Wait Events Statistics
Back to Top
Wait Events
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
PX Deq Credit: send blkd 583,175 1.13 27,247 47 16.59
log file sync 34,031 9.73 4,731 139 0.97
log buffer space 9,052 32.06 3,883 429 0.26
db file parallel write 14,797 0.00 3,026 205 0.42
log file parallel write 35,831 0.00 476 13 1.02
db file sequential read 51,869,393 0.00 396 0 1,475.95
enq: KO - fast object checkpoint 176 63.64 375 2132 0.01
library cache pin 2,275 0.00 323 142 0.06
SQL*Net break/reset to client 16,938 0.00 266 16 0.48
direct path read 2,704,157 0.00 263 0 76.95
PX qref latch 42,471,296 100.29 136 0 1,208.53
buffer busy waits 18,710 0.50 123 7 0.53
db file scattered read 4,706 0.00 32 7 0.13
latch: In memory undo latch 248 0.00 29 115 0.01
SQL*Net more data from client 590,639 0.00 15 0 16.81
control file parallel write 4,121 0.00 15 4 0.12
SQL*Net more data to client 412,946 0.00 10 0 11.75
os thread startup 258 0.00 10 38 0.01
log file switch completion 170 0.00 9 53 0.00
kksfbc child completion 203 87.68 9 43 0.01
Streams AQ: qmn coordinator waiting for slave to start 1 100.00 5 4883 0.00
latch free 688 0.00 3 4 0.02
latch: redo allocation 475 0.00 3 6 0.01
direct path write 415 0.00 1 3 0.01
rdbms ipc reply 287 0.00 1 4 0.01
PX Deq: Signal ACK 5,837 14.55 1 0 0.17
SQL*Net message to client 758,061 0.00 1 0 21.57
latch: cache buffers chains 657 0.00 1 1 0.02
latch: object queue header operation 1,471 0.00 0 0 0.04
PX Deq: Table Q Get Keys 273 0.00 0 2 0.01
LGWR wait for redo copy 5,013 0.04 0 0 0.14
log file single write 48 0.00 0 3 0.00
control file sequential read 14,274 0.00 0 0 0.41
log file sequential read 48 0.00 0 3 0.00
latch: checkpoint queue latch 3 0.00 0 43 0.00
undo segment extension 41,462 99.94 0 0 1.18
latch: session allocation 597 0.00 0 0 0.02
reliable message 64 0.00 0 1 0.00
enq: PS - contention 721 0.00 0 0 0.02
latch: library cache 32 0.00 0 2 0.00
latch: shared pool 172 0.00 0 0 0.00
latch: cache buffers lru chain 18 0.00 0 1 0.00
cursor: mutex X 117 0.00 0 0 0.00
latch: row cache objects 5 0.00 0 3 0.00
direct path write temp 19 0.00 0 1 0.00
row cache lock 1 0.00 0 6 0.00
PX Deq: Table Q qref 68 0.00 0 0 0.00
latch: undo global data 4 0.00 0 0 0.00
read by other session 4 0.00 0 0 0.00
buffer deadlock 8 100.00 0 0 0.00
latch: library cache lock 1 0.00 0 1 0.00
latch: parallel query alloc buffer 1 0.00 0 0 0.00
SQL*Net message from client 758,050 0.00 80,183 106 21.57
PX Idle Wait 46,072 81.77 77,153 1675 1.31
PX Deq: Execution Msg 193,804 7.53 31,059 160 5.51
Streams AQ: qmn slave idle wait 387 0.52 10,589 27363 0.01
Streams AQ: qmn coordinator idle wait 787 51.59 10,467 13300 0.02
Streams AQ: waiting for time management or cleanup tasks 2 100.00 5,774 2886905 0.00
PX Deq: Table Q Normal 186,606 0.98 5,482 29 5.31
PX Deq: Execute Reply 71,724 0.53 1,138 16 2.04
PX Deq Credit: need buffer 11,022 0.72 328 30 0.31
PX Deq: Parse Reply 3,904 0.00 47 12 0.11
PX Deq: Table Q Sample 979 1.12 23 23 0.03
PX Deq: Msg Fragment 3,345 0.00 4 1 0.10
PX Deq: Join ACK 4,651 0.00 1 0 0.13
class slave wait 22 0.00 0 0 0.00
Back to Wait Events Statistics
Back to Top
Background Wait Events
ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
db file parallel write 14,797 0.00 3,026 205 0.42
log file parallel write 35,831 0.00 476 13 1.02
control file parallel write 4,121 0.00 15 4 0.12
latch: In memory undo latch 28 0.00 8 292 0.00
events in waitclass Other 5,832 0.05 8 1 0.17
buffer busy waits 74 6.76 5 68 0.00
direct path read 341 0.00 1 4 0.01
direct path write 341 0.00 1 3 0.01
os thread startup 25 0.00 1 41 0.00
log buffer space 1 0.00 0 226 0.00
log file single write 48 0.00 0 3 0.00
log file sequential read 48 0.00 0 3 0.00
latch: cache buffers chains 13 0.00 0 10 0.00
control file sequential read 4,378 0.00 0 0 0.12
row cache lock 1 0.00 0 6 0.00
rdbms ipc message 62,783 57.86 119,531 1904 1.79
pmon timer 3,598 99.89 10,525 2925 0.10
Streams AQ: qmn slave idle wait 382 0.00 10,472 27414 0.01
Streams AQ: qmn coordinator idle wait 787 51.59 10,467 13300 0.02
smon timer 108 27.78 10,185 94306 0.00
Streams AQ: waiting for time management or cleanup tasks 2 100.00 5,774 2886905 0.00
Back to Wait Events Statistics
Back to Top
Operating System Statistics
Statistic Total
NUM_LCPUS 0
NUM_VCPUS 0
AVG_BUSY_TIME 37,416
AVG_IDLE_TIME 1,043,257
AVG_IOWAIT_TIME 5,670
AVG_SYS_TIME 9,643
AVG_USER_TIME 27,766
BUSY_TIME 1,047,809
IDLE_TIME 29,211,362
IOWAIT_TIME 158,948
SYS_TIME 270,185
USER_TIME 777,624
LOAD 0
OS_CPU_WAIT_TIME 2,009,000
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES ###############
NUM_CPUS 28
NUM_CPU_CORES 14
Back to Wait Events Statistics
Back to Top
Service Statistics
ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
SYS$USERS 45,168.90 6,624.80 130,622,076 1,383,758,254
SYS$BACKGROUND 0.00 0.00 481 103,324
revpnew.nam.nsroot.net 0.00 0.00 0 0
Back to Wait Events Statistics
Back to Top
Service Wait Class Stats
Wait Class info for services in the Service Statistics section.
Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
Time Waited (Wt Time) in centisecond (100th of a second)
Service Name User I/O Total Wts User I/O Wt Time Concurcy Total
Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
SYS$USERS 54577426 68823 22323 47060 0 0 1735929 2535
SYS$BACKGROUND 1268 369 146 1435 0 0 0 0
Back to Wait Events Statistics
Back to Top
[Updated on: Sat, 07 November 2009 01:23] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Oracle 10.2.0.4 [message #430049 is a reply to message #430021] |
Sat, 07 November 2009 10:09 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Tuning is a multiheaded beast & no silver bullet exists.
From my perspective one of the first tasks when starting to tune,
is to determine whether the primary bottleneck is at the OS/hardware level,
or it exists within Oracle RDBMS itself.
Possible bottlenecks at the OS/hardware level are RAM, CPU, I/O, & network.
If little to no swapping occurs, then RAM is not the problem.
If run queue depth for CPU close to the number of processors then CPU is not the problem.
If the network bandwidth is not being saturated, then network is not the problem.
Determining if a disk I/O bottleneck exist can be very challenging & is directly
linked to Oracle's configuration, application & data.
Since disks are mechanical, they are the slowest component & frequently the 1st bottleneck.
Tuning Oracle DB approaches can be divided into 2 broad categories
1) Top Down
2) Bottom Up
Each has good points & bad point along with supporters & detractors.
In both cases you need to identify exactly what is the bottleneck,
& then make judicious changes in order to eliminate the load or widen the bottleneck
With Top Down folks start with overall performance summary reports such as from AWWR or STATSPACK.
Some/many/most folks then start adjusting initSID.ora parameters in an attempt to obtain better results.
I just want to throw out a word of caution regarding any TOP N list that gets generated.
Just because an item is reported, this in and of itself does not necessarily mean it is a problem.
For example, say a SELECT statement is reported in the TOP 5 CPU consumers section,
but consumes less than 2% of the CPU cycles.
Little will be gained by trying to tune this particular SELECT statement.
Your time would be better spent looking elsewhere for problems to be corrected.
With the Bottom Up approach, you need to identify long running and/or resource intensive SQL
and then figure out a way to accomplish the same results either faster or consuming fewer resources.
|
|
|
|
Goto Forum:
Current Time: Sat May 18 03:57:57 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
