Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

system tablespace I/O issue.

DeleteJun 11 2014 — edited Jun 12 2014

Hello, Now I got a awr report, that's strange, the system tablespace read is very high, and query the data dictionary is slow. the storage is raid5, read speed=200M/s, the write speed=150M/s. I try to gather the data dictionary statistics, but it doesn't  work.   Is the bufer cache too small? how can I solve it? Thinks.

WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numReleaseRACHost
TOPPROD1984288657topprod110.2.0.3.0NOdading-erp
Snap IdSnap TimeSessionsCursors/Session
Begin Snap:2477604-Jun-14 00:00:0584     11.7
End Snap:2479304-Jun-14 17:00:47184     11.1
Elapsed:            1,020.70 (mins)
DB Time:              429.88 (mins)

Report Summary

Cache Sizes

BeginEnd
Buffer Cache:       576M       576MStd Block Size:         8K
Shared Pool Size:       912M       912MLog Buffer:    14,356K

Load Profile

Per SecondPer Transaction
Redo size:            15,280.96             2,464.96
Logical reads:            31,966.06             5,156.43
Block changes:               116.06                18.72
Physical reads:             6,759.82             1,090.42
Physical writes:                 4.44                 0.72
User calls:             1,343.54               216.72
Parses:               557.30                89.90
Hard parses:                 7.30                 1.18
Sorts:                32.26                 5.20
Logons:                 0.48                 0.08
Executes:               587.36                94.75
Transactions:                 6.20
% Blocks changed per Read:   0.36Recursive Call %:   43.41
Rollback per transaction %:   0.16Rows per Sort:   61.37

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:            99.54Redo NoWait %:            99.98
Buffer  Hit   %:            78.99In-memory Sort %:           100.00
Library Hit   %:            96.75Soft Parse %:            98.69
Execute to Parse %:             5.12Latch Hit %:            99.66
Parse CPU to Parse Elapsd %:            95.16% Non-Parse CPU:            93.45

Shared Pool Statistics

BeginEnd
Memory Usage %:            69.99            64.38
% SQL with executions>1:            89.47            70.56
% Memory for SQL w/exec>1:            89.33            78.10

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time           21,422             83.1
log file sync         384,522           2,589               7            10.0Commit
db file scattered read      28,408,688           2,458               0             9.5User I/O
log file parallel write         409,423           2,403               6             9.3System I/O
db file sequential read       7,825,115           1,727               0             6.7User I/O

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 25792.8s
  • 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 NameTime (s)% of DB Time
DB CPU21,422.2783.06
sql execute elapsed time19,281.6974.76
parse time elapsed1,744.426.76
hard parse elapsed time1,155.334.48
PL/SQL execution elapsed time350.731.36
connection management call elapsed time152.790.59
hard parse (sharing criteria) elapsed time81.060.31
failed parse elapsed time33.030.13
repeated bind elapsed time11.780.05
sequence load elapsed time1.110.00
PL/SQL compilation elapsed time0.640.00
hard parse (bind mismatch) elapsed time0.320.00
DB time25,792.78
background elapsed time6,157.55
background cpu time1,656.72

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 ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
User I/O45,498,2940.005,0050119.84
System I/O11,524,0020.002,917030.35
Commit384,5220.002,58971.01
Application279,4130.0245320.74
Network63,555,7500.001990167.40
Configuration26773.411224580.00
Other38,4210.052210.10
Concurrency28,2260.12800.07

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)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
log file sync384,5220.002,58971.01
db file scattered read28,408,6880.002,458074.83
log file parallel write409,4230.002,40361.08
db file sequential read7,825,1150.001,727020.61
read by other session9,062,3620.00814023.87
control file parallel write21,3350.00456210.06
enq: RO - fast object reuse7,5350.49409540.02
SQL*Net more data to client3,665,0610.0011309.65
write complete waits114100.001119780.00
SQL*Net message to client59,764,6070.00840157.42
control file sequential read11,093,1560.0058029.22
enq: TX - row lock contention875.002328240.00
reliable message31,0990.001400.08
SQL*Net break/reset to client249,3080.001100.66
log file switch completion640.00111690.00
enq: KO - fast object checkpoint22,5620.001000.06
latch: shared pool6,4070.00610.02
Data file init write2,5760.00310.01
latch free5640.00350.00
buffer exterminate1010.0032730.00
SQL*Net more data from client126,0820.00100.33
enq: CF - contention60.0011850.00
db file parallel read6170.00120.00
os thread startup360.001310.00
rdbms ipc reply2,2120.00100.01
direct path read194,7240.00100.51
log file single write440.00090.00
cursor: pin S wait on X3889.470100.00
db file single write280.000130.00
latch: cache buffers chains18,9560.00000.05
buffer busy waits2,1890.00000.01
LGWR wait for redo copy4,2870.00000.01
latch: library cache3480.00000.00
local write wait90.000150.00
kksfbc child completion2100.000500.00
SGA: allocation forcing component growth475.000110.00
library cache load lock150.00020.00
library cache lock210.00020.00
row cache lock480.00010.00
enq: TX - index contention260.00010.00
latch: row cache objects1370.00000.00
latch: cache buffers lru chain290.00000.00
log file sequential read440.00000.00
direct path write3,4050.00000.01
kkdlgon90.00000.00
latch: object queue header operation1370.00000.00
direct path write temp3850.00000.00
latch: session allocation450.00000.00
direct path read temp3850.00000.00
enq: HW - contention20.00000.00
undo segment extension8398.80000.00
buffer deadlock14100.00000.00
enq: SQ - contention10.00000.00
cursor: pin S20.00000.00
latch: redo allocation10.00000.00
enq: TX - allocate ITL entry20.00000.00
latch: In memory undo latch30.00000.00
cursor: mutex X20.00000.00
latch: redo writing10.00000.00
SQL*Net message from client59,764,5060.004,354,00673157.42
Streams AQ: qmn slave idle wait2,1800.0059,608273430.01
Streams AQ: qmn coordinator idle wait4,45551.0759,608133800.01
Streams AQ: waiting for time management or cleanup tasks14100.0027,11819370040.00
jobq slave wait20100.005929310.00
SGA: MMAN sleep for component shrink1,26395.0113110.00
class slave wait350.00000.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

  • ordered by wait time desc, waits desc (idle events last)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
log file parallel write409,4240.002,40361.08
control file parallel write21,3170.00455210.06
db file scattered read3,400,6190.0032808.96
control file sequential read9,635,3190.0048025.38
db file sequential read109,5910.00700.29
os thread startup360.001310.00
events in waitclass Other4,5420.00100.01
log file single write440.00090.00
db file single write240.000140.00
latch: shared pool4630.00010.00
direct path read8,9400.00000.02
buffer busy waits450.00000.00
log file sequential read440.00000.00
latch: cache buffers chains150.00000.00
log file sync10.00020.00
latch: library cache90.00000.00
latch: redo writing10.00000.00
latch: row cache objects10.00000.00
direct path write3,3600.00-0-00.01
rdbms ipc message764,99027.92580,0607582.01
pmon timer28,68099.9259,77520840.08
Streams AQ: qmn slave idle wait2,1800.0059,608273430.01
Streams AQ: qmn coordinator idle wait4,45551.0759,608133800.01
smon timer8,0600.0159,05473270.02
Streams AQ: waiting for time management or cleanup tasks14100.0027,11819370040.00
SGA: MMAN sleep for component shrink1,26395.0113110.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
BUSY_TIME9,228,130
IDLE_TIME88,731,285
IOWAIT_TIME920,270
NICE_TIME1
SYS_TIME1,615,171
USER_TIME7,496,175
LOAD2
RSRC_MGR_CPU_WAIT_TIME0
PHYSICAL_MEMORY_BYTES872,928
NUM_CPUS16
NUM_CPU_SOCKETS2

Back to Wait Events Statistics
Back to Top

Service Statistics

  • ordered by DB Time
Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
topprod25,773.7021,419.10361,028,3081,899,815,721
SYS$USERS20.604.603626,692
SYS$BACKGROUND0.000.0052,954,68158,123,438

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 NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
topprod4194566646528927579686006346704719865
SYS$USERS29253810000160
SYS$BACKGROUND3549696347956021460000

Back to Wait Events Statistics
Back to Top

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL    statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided    into the Total Database Time multiplied by 100
Elapsed  Time (s)CPU    Time (s)ExecutionsElap per  Exec (s)% Total DB Time   SQL IdSQL ModuleSQL Text
2,4432,2431,7371.419.47g3vhv5trsb028fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   user_ta...
91991921,3410.043.5624f3dvhg0x842exp@dading-erp (TNS V1-V3)SELECT SYNNAM,  SYNNAM2,  SYNT...
5872153195.772.280n52x7tys9ybpfglrun-bin@dading-erp (TNS V1-V3)select 'N', lower(SUBSTR(all_o...
49148936,2570.011.90dpxx651w1jsywfglrun-bin@dading-erp (TNS V1-V3)select SUM(sfl07) from   sfl_f...
29178117,1530.001.139yxwp3r1wm8gdfglrun-bin@dading-erp (TNS V1-V3)select SUM(tlf10 * tlf12) from...
2842832311.231.106xvp6nxs4a9n4 select nvl(sum(space), 0) from...
2552542251.130.99csnp95dz2r8ss select file#,  block# from rec...
24824619,5370.010.96bcrzvfrjj8w4dfglrun-bin@dading-erp (TNS V1-V3)select SUM(omb12) from   omb_f...
2332334,093,7920.000.9038vzzryzyhvd6fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   smz_fil...
2092094,093,2960.000.81byg1zb9ac1tv5fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   img_fil...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

  • Resources reported for PL/SQL code includes the resources used by all SQL    statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided    into the Total Database Time multiplied by 100
CPU    Time (s)Elapsed  Time (s)ExecutionsCPU per  Exec (s)% Total DB Time   SQL IdSQL ModuleSQL Text
2,2432,4431,7371.299.47g3vhv5trsb028fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   user_ta...
91991921,3410.043.5624f3dvhg0x842exp@dading-erp (TNS V1-V3)SELECT SYNNAM,  SYNNAM2,  SYNT...
48949136,2570.011.90dpxx651w1jsywfglrun-bin@dading-erp (TNS V1-V3)select SUM(sfl07) from   sfl_f...
2832842311.221.106xvp6nxs4a9n4 select nvl(sum(space), 0) from...
2542552251.130.99csnp95dz2r8ss select file#,  block# from rec...
24624819,5370.010.96bcrzvfrjj8w4dfglrun-bin@dading-erp (TNS V1-V3)select SUM(omb12) from   omb_f...
2332334,093,7920.000.9038vzzryzyhvd6fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   smz_fil...
215587371.552.280n52x7tys9ybpfglrun-bin@dading-erp (TNS V1-V3)select 'N', lower(SUBSTR(all_o...
2092094,093,2960.000.81byg1zb9ac1tv5fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   img_fil...
1721724,094,6910.000.675h70zgg2zt4b9fglrun-bin@dading-erp (TNS V1-V3)select smyware from   smy_file...
78291117,1530.001.139yxwp3r1wm8gdfglrun-bin@dading-erp (TNS V1-V3)select SUM(tlf10 * tlf12) from...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

  • Resources reported for PL/SQL code includes the resources used by all SQL    statements called by the code.
  • Total Buffer Gets:   1,957,658,605
  • Captured SQL account for      55.8% of Total
Buffer GetsExecutionsGets     per Exec %TotalCPU    Time (s)Elapsed  Time (s)   SQL IdSQL ModuleSQL Text
502,495,05821,34123,545.9925.67918.94918.9824f3dvhg0x842exp@dading-erp (TNS V1-V3)SELECT SYNNAM,  SYNNAM2,  SYNT...
150,111,0181,73786,419.707.672243.442443.07g3vhv5trsb028fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   user_ta...
58,255,63536,2571,606.742.98489.22491.06dpxx651w1jsywfglrun-bin@dading-erp (TNS V1-V3)select SUM(sfl07) from   sfl_f...
44,099,985314,699,995.002.25214.64587.300n52x7tys9ybpfglrun-bin@dading-erp (TNS V1-V3)select 'N', lower(SUBSTR(all_o...
41,300,747113,754,613.362.1194.5994.81dzxzrufuuug0nexp@dading-erp (TNS V1-V3)SELECT IOBJID,  IDOBJID,  INAM...
14,519,434117,153123.940.7477.63291.409yxwp3r1wm8gdfglrun-bin@dading-erp (TNS V1-V3)select SUM(tlf10 * tlf12) from...
14,081,80523160,960.190.72282.73283.546xvp6nxs4a9n4 select nvl(sum(space), 0) from...
13,715,43422560,957.480.70254.07254.83csnp95dz2r8ss select file#,  block# from rec...
12,700,956108,590116.960.6539.2439.24c4132c3kwjmscfglrun-bin@dading-erp (TNS V1-V3)select SUM(tlf10 * tlf12) from...
12,280,3774,093,4593.000.63162.61162.61ancpjh4uht1fxfglrun-bin@dading-erp (TNS V1-V3)select ze03, ze05 from   ze_fi...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

  • Total Disk Reads:     413,983,335
  • Captured SQL account for     49.4% of Total
Physical ReadsExecutionsReads     per Exec %TotalCPU    Time (s)Elapsed  Time (s)   SQL IdSQL ModuleSQL Text
103,665,6551,73759,680.8625.042243.442443.07g3vhv5trsb028fglrun-bin@dading-erp (TNS V1-V3)select COUNT(*) from   user_ta...
14,034,33223160,754.683.39282.73283.546xvp6nxs4a9n4 select nvl(sum(space), 0) from...
13,621,34322560,539.303.29254.07254.83csnp95dz2r8ss select file#,  block# from rec...
10,772,51015718,167.332.60137.79138.95faza0kw3szm4yexp@dading-erp (TNS V1-V3)SELECT LOWNER,  LIBNAME,  OWNE...
3,433,6236453,650.360.8380.3592.241bajzq0pkf8t6fglrun-bin@dading-erp (TNS V1-V3)select distinct column_id, low...
3,334,6016352,930.170.8179.8890.76gfd7r6m3ru5wafglrun-bin@dading-erp (TNS V1-V3)select distinct column_id, low...
2,733,3955054,667.900.6661.5770.116gz11r05t20h9fglrun-bin@dading-erp (TNS V1-V3)select distinct column_id, low...
2,356,4124256,105.050.5751.5759.55
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 10 2014
Added on Jun 11 2014
5 comments
816 views