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 Go to next message
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 #429877 is a reply to message #429826] Fri, 06 November 2009 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Run ADDM or AWR report (if you purchase them otherwise take a statspack report).

Regards
Michel
Re: Oracle 10.2.0.4 [message #429911 is a reply to message #429826] Fri, 06 November 2009 04:37 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
Michel,

very big report generated.which part i will paste to get some help from you.

thanks.
Re: Oracle 10.2.0.4 [message #429913 is a reply to message #429911] Fri, 06 November 2009 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this is a ADDM report, investigate the points that have the greatest weight.

Regards
Michel
Re: Oracle 10.2.0.4 [message #429921 is a reply to message #429826] Fri, 06 November 2009 05:33 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member

Its AWR Report and i am not able to load that in this site.It didnt accept (.html) format.
Re: Oracle 10.2.0.4 [message #429933 is a reply to message #429826] Fri, 06 November 2009 06:31 Go to previous messageGo to next message
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 #429942 is a reply to message #429933] Fri, 06 November 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For PX event, see Metalink note 271767.1 but it is most often an idle event (which you don't have to care).

For log events, see
Database Performance Tuning Guide
Chapter 10 Instance Tuning Using Performance Views
Section 10.3 Wait Events Statistics

Regards
Michel
Re: Oracle 10.2.0.4 [message #430021 is a reply to message #429826] Fri, 06 November 2009 23:49 Go to previous messageGo to next message
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 #430027 is a reply to message #430021] Sat, 07 November 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change my answer.

Regards
Michel
Re: Oracle 10.2.0.4 [message #430049 is a reply to message #430021] Sat, 07 November 2009 10:09 Go to previous message
BlackSwan
Messages: 25037
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.
Previous Topic: SQL Tune
Next Topic: to check for temporary space usage
Goto Forum:
  


Current Time: Mon Dec 05 08:48:21 CST 2016

Total time taken to generate the page: 0.10204 seconds