Skip to Main Content
Forums

SQL & PL/SQL

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!

TUNING PLSQL Program

sk12345678May 6 2014 — edited May 6 2014


I am working on an existing plsql program which processes 23million rows (month by month using cursor) and this program has multiple select statements and loads two tables(TABLE1 6 million records, other TABLE2 IS 17 million rows). Currently taking 50 hours. I have to use this plsql code.I am committing every 10,000 rows

I have altered table1 and table in nologging mode, dropped indexes on table1 and table2 before running.

1) once I execute alter table table1 nologging, is it guaranteed oracle is not logging in the behind?

2) awr report dba gave me for 1 hour of execution is like below. can someone guide me with problem areas I should be looking on this report.

Thanks

AWR Report

DB NameDB IdInstanceInst numReleaseRACHost
Snap IdSnap TimeSessionsCursors/Session
Begin Snap:257402-May-14 15:16:3381     10.2
End Snap:257602-May-14 16:55:3093      9.2
Elapsed:              98.96 (mins)
DB Time:             556.09 (mins)

Report Summary

Cache Sizes
BeginEnd
Buffer Cache:     3,808M     3,808MStd Block Size:        16K
Shared Pool Size:       240M       240MLog Buffer:    14,356K
Load Profile
Per SecondPer Transaction
Redo size:           233,936.43           641,003.85
Logical reads:            35,794.52            98,079.73
Block changes:             1,463.33             4,009.63
Physical reads:               225.41               617.64
Physical writes:                33.56                91.97
User calls:                 4.45                12.18
Parses:               195.46               535.57
Hard parses:                 1.20                 3.28
Sorts:               486.53             1,333.14
Logons:                 0.12                 0.33
Executes:             2,017.23             5,527.37
Transactions:                 0.36
% Blocks changed per Read:   4.09Recursive Call %:   99.89
Rollback per transaction %:   0.00Rows per Sort:   66.37
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %:           100.00Redo NoWait %:           100.00
Buffer  Hit   %:            99.38In-memory Sort %:           100.00
Library Hit   %:            99.72Soft Parse %:            99.39
Execute to Parse %:            90.31Latch Hit %:           100.00
Parse CPU to Parse Elapsd %:            81.38% Non-Parse CPU:            98.55
Shared Pool Statistics
BeginEnd
Memory Usage %:            74.69            74.34
% SQL with executions>1:            88.34            89.45
% Memory for SQL w/exec>1:            80.47            85.20
Top 5 Timed Events
EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
PX Deq Credit: send blkd          17,008          27,347           1,608            82.0Other
db file sequential read       1,024,985           3,658               4            11.0User I/O
CPU time           2,282             6.8
db file scattered read          23,207              92               4              .3User I/O
log file parallel write           4,584              56              12              .2System I/O

Main Report

Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 33365.1s
  • 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
sql execute elapsed time33,343.8299.94
DB CPU2,281.676.84
PL/SQL execution elapsed time152.170.46
sequence load elapsed time78.950.24
parse time elapsed57.210.17
hard parse elapsed time45.250.14
hard parse (sharing criteria) elapsed time1.840.01
PL/SQL compilation elapsed time1.780.01
hard parse (bind mismatch) elapsed time1.710.01
connection management call elapsed time0.700.00
failed parse elapsed time0.560.00
repeated bind elapsed time0.260.00
DB time33,365.12
background elapsed time179.43
background cpu time17.53
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
Other18,58374.7827,34814728.58
User I/O1,063,7330.003,7534490.88
System I/O48,3040.00146322.29
Concurrency1,98183.5426130.91
Commit2140.004190.10
Network53,1020.002024.50
Configuration330.001200.02
Application1480.00000.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
PX Deq Credit: send blkd17,00880.3927,34716087.85
db file sequential read1,024,9850.003,6584473.00
db file scattered read23,2070.0092410.71
log file parallel write4,5840.0056122.12
db file parallel write28,4970.0041113.15
cursor: pin S wait on X1,66499.4626150.77
Log archive I/O1,3520.0024180.62
log file sequential read1,4570.001180.67
control file parallel write3,7430.00721.73
control file sequential read8,6110.00613.97
log file sync2140.004190.10
read by other session4390.00250.20
SQL*Net more data to client34,4270.002015.89
direct path read11,8460.00105.47
log file switch completion310.001210.01
kksfbc child completion1275.001430.01
direct path write3,2560.00001.50
os thread startup600.00030.03
latch: shared pool1440.00000.07
PX Deq: Signal ACK49511.52000.23
log file single write600.00010.03
latch free2280.00000.11
SQL*Net break/reset to client1480.00000.07
SGA: allocation forcing component growth250.000120.00
SQL*Net message to client17,9320.00008.28
rdbms ipc reply2630.00000.12
latch: cache buffers chains370.00000.02
SQL*Net more data from client7430.00000.34
LGWR wait for redo copy3160.00000.15
latch: library cache120.00000.01
PX qref latch17888.20000.08
latch: session allocation230.00000.01
latch: cache buffers lru chain100.00000.00
enq: PS - contention390.00000.02
latch: parallel query alloc buffer60.00000.00
buffer busy waits130.00000.01
latch: row cache objects90.00000.00
library cache load lock10.00000.00
library cache pin90.00000.00
latch: object queue header operation30.00000.00
cursor: mutex S240.00000.01
cursor: mutex X70.00000.00
undo segment extension20.00000.00
latch: library cache lock10.00000.00
SQL*Net message from client17,9370.00117,34565428.28
Streams AQ: waiting for messages in the queue11,10599.7935,60032065.12
PX Deq: Execution Msg25,39962.6031,912125611.72
PX Idle Wait11,51096.3622,28819365.31
Streams AQ: qmn slave idle wait2,9720.0011,87139941.37
Streams AQ: qmn coordinator idle wait3,32943.775,93617831.54
pipe get10,14711.995,9315854.68
jobq slave wait2,01993.565,92029320.93
virtual circuit status198100.005,914298680.09
PX Deq Credit: need buffer2,79669.313,87513861.29
Streams AQ: waiting for time management or cleanup tasks1080.00671671190.00
PX Deq: Table Q Normal87,8890.31633740.56
PX Deq: Execute Reply3,0370.03721.40
PX Deq: Parse Reply1910.00290.09
PX Deq: Msg Fragment7080.00000.33
PX Deq: Join ACK3230.00000.15
HS message to agent20.00000.00
class slave wait30.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 write4,5850.0056122.12
db file parallel write28,4970.0041113.15
Log archive I/O1,3520.0024180.62
log file sequential read1,4570.001180.67
control file parallel write3,7390.00721.73
control file sequential read7,6020.00513.51
db file sequential read2,2450.00311.04
direct path read11,8440.00105.47
db file scattered read2540.00130.12
direct path write3,2340.00001.49
os thread startup220.00040.01
log file single write600.00010.03
events in waitclass Other6190.00000.29
buffer busy waits50.00000.00
latch: row cache objects10.00000.00
latch: shared pool20.00000.00
rdbms ipc message24,25878.9761,304252711.19
Streams AQ: qmn slave idle wait2,9720.0011,87139941.37
Streams AQ: qmn coordinator idle wait3,32943.775,93617831.54
pmon timer2,025100.005,93329300.93
smon timer535.665,8581105260.02
Streams AQ: waiting for time management or cleanup tasks1080.00671671190.00
Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
AVG_BUSY_TIME62,623
AVG_IDLE_TIME530,897
AVG_SYS_TIME6,272
AVG_USER_TIME56,211
BUSY_TIME251,088
IDLE_TIME2,124,196
SYS_TIME25,685
USER_TIME225,403
RSRC_MGR_CPU_WAIT_TIME0
VM_IN_BYTES###############
VM_OUT_BYTES###############
PHYSICAL_MEMORY_BYTES17,178,972,160
NUM_CPUS4
NUM_CPU_CORES4
Back to Wait Events Statistics
Back to Top

Service Statistics

  • ordered by DB Time
Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
awhdev.AMERISURE.INT33,267.902,258.301,294,233210,772,659
SYS$USERS48.9021.3026,5871,030,196
SYS$BACKGROUND0.000.0017,332698,395
awhdevXDB0.000.0000
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
awhdev.AMERISURE.INT1036216371916190425850048239184
SYS$USERS835922684010043761
SYS$BACKGROUND1911810683490000
This post has been answered by unknown-7404 on May 6 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 3 2014
Added on May 6 2014
5 comments
409 views