WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numStartup TimeReleaseRAC
KLASH1701927951klash106-Jan-15 15:0111.2.0.4.0NO

Host NamePlatformCPUsCoresSocketsMemory (GB)
oradb11Linux x86 64-bit 48 24 2 126.13

Snap IdSnap TimeSessionsCursors/Session
Begin Snap:320408-Jan-15 11:00:151007 6.5
End Snap:320508-Jan-15 12:00:311259 6.4
Elapsed:  60.26 (mins)  
DB Time:  317.79 (mins)  

Report Summary

Load Profile

Per SecondPer TransactionPer ExecPer Call
DB Time(s): 5.3 0.3 0.00 0.00
DB CPU(s): 4.9 0.3 0.00 0.00
Redo size (bytes): 8,615,639.5 491,932.4  
Logical read (blocks): 542,404.1 30,970.0  
Block changes: 73,204.7 4,179.8  
Physical read (blocks): 931.8 53.2  
Physical write (blocks): 742.4 42.4  
Read IO requests: 71.5 4.1  
Write IO requests: 62.9 3.6  
Read IO (MB): 7.3 0.4  
Write IO (MB): 5.8 0.3  
User calls: 1,511.5 86.3  
Parses (SQL): 291.2 16.6  
Hard parses (SQL): 12.7 0.7  
SQL Work Area (MB): 190.8 10.9  
Logons: 0.5 0.0  
Executes (SQL): 14,145.9 807.7  
Rollbacks: 1.4 0.1  
Transactions: 17.5   

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00Redo NoWait %: 100.00
Buffer Hit %: 99.94In-memory Sort %: 100.00
Library Hit %: 99.74Soft Parse %: 95.65
Execute to Parse %: 97.94Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 93.71% Non-Parse CPU: 97.90

Top 10 Foreground Events by Total Wait Time

EventWaitsTotal Wait Time (sec)Wait Avg(ms)% DB timeWait Class
DB CPU 17.8K 93.6 
db file sequential read160,083391.122.1User I/O
log file sync9,354205.4221.1Commit
enq: TX - row lock contention203165.4815.9Application
db file scattered read36,239108.63.6User I/O
SQL*Net more data to client768,03285.70.4Network
db file parallel read6,36784.313.4User I/O
direct path read16,99081.95.4User I/O
SQL*Net more data from client5,9639.32.0Network
SQL*Net break/reset to client2,8496.12.0Application

Wait Classes by Total Wait Time

Wait ClassWaitsTotal Wait Time (sec)Avg Wait (ms)% DB timeAvg Active Sessions
DB CPU 17,841 93.64.9
System I/O282,71012,8234567.33.5
User I/O224,56367933.60.2
Commit9,358206221.10.1
Application3,14017556.90.0
Network6,250,3651040.50.0
Configuration616101.00.0
Other4,97641.00.0
Concurrency6,03820.00.0

Host CPU

CPUsCoresSocketsLoad Average BeginLoad Average End%User%System%WIO%Idle
48 24 2 9.20 10.78 10.1 0.5 1.7 89.4

Instance CPU

%Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
10.3 97.4 0.0

IO Profile

Read+Write Per SecondRead per SecondWrite Per Second
Total Requests: 161.7 74.0 87.6
Database Requests: 134.4 71.5 62.9
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 23.6 0.0 23.6
Total (MB): 21.6 7.3 14.3
Database (MB): 13.1 7.3 5.8
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 8.5 0.0 8.5
Database (blocks): 1,674.1 931.8 742.4
Via Buffer Cache (blocks): 1,075.2 332.9 742.3
Direct (blocks): 598.9 598.9 0.0

Memory Statistics

BeginEnd
Host Mem (MB): 129,156.5 129,156.5
SGA use (MB): 43,008.0 43,008.0
PGA use (MB): 3,540.5 4,121.5
% Host Mem used for SGA+PGA: 36.04 36.49

Cache Sizes

BeginEnd
Buffer Cache: 25,600M 25,600MStd Block Size: 8K
Shared Pool Size: 12,361M 12,334MLog Buffer: 147,576K

Shared Pool Statistics

BeginEnd
Memory Usage %: 74.97 74.80
% SQL with executions>1: 93.18 94.27
% Memory for SQL w/exec>1: 88.94 89.77

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic NameTime (s)% of DB Time
sql execute elapsed time18,508.5897.07
DB CPU17,840.6493.57
inbound PL/SQL rpc elapsed time2,365.7712.41
PL/SQL execution elapsed time741.533.89
parse time elapsed254.471.33
hard parse elapsed time231.411.21
hard parse (sharing criteria) elapsed time45.050.24
connection management call elapsed time18.410.10
PL/SQL compilation elapsed time0.700.00
repeated bind elapsed time0.410.00
sequence load elapsed time0.150.00
failed parse elapsed time0.140.00
hard parse (bind mismatch) elapsed time0.010.00
DB time19,067.11 
background elapsed time12,941.49 
background cpu time111.11 


Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticValueEnd Value
BUSY_TIME1,843,489 
IDLE_TIME15,548,278 
IOWAIT_TIME302,273 
NICE_TIME270 
SYS_TIME89,660 
USER_TIME1,749,799 
LOAD911
RSRC_MGR_CPU_WAIT_TIME0 
VM_IN_BYTES0 
VM_OUT_BYTES0 
PHYSICAL_MEMORY_BYTES135,430,369,280 
NUM_CPUS48 
NUM_CPU_CORES24 
NUM_CPU_SOCKETS2 
GLOBAL_RECEIVE_SIZE_MAX4,194,304 
GLOBAL_SEND_SIZE_MAX1,048,576 
TCP_RECEIVE_SIZE_DEFAULT87,380 
TCP_RECEIVE_SIZE_MAX4,194,304 
TCP_RECEIVE_SIZE_MIN4,096 
TCP_SEND_SIZE_DEFAULT16,384 
TCP_SEND_SIZE_MAX4,194,304 
TCP_SEND_SIZE_MIN4,096 


Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Snap TimeLoad%busy%user%sys%idle%iowait
08-Jan 11:00:159.20     
08-Jan 12:00:3110.7810.6010.060.5289.401.74


Back to Wait Events Statistics
Back to Top

Foreground Wait Class

Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)%DB time
DB CPU  17,841 93.57
User I/O222,371066633.49
Commit9,3540205221.08
Application3,0640175570.92
Network6,247,175010400.54
Configuration614861010.03
Other1,7310320.01
System I/O9410110.00
Concurrency5,5770000.00


Back to Wait Events Statistics
Back to Top

Foreground Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% DB time
db file sequential read160,083039122.532.05
log file sync9,3540205220.151.08
enq: TX - row lock contention20301658150.000.87
db file scattered read36,239010930.570.57
SQL*Net more data to client768,032086012.130.45
db file parallel read6,367084130.100.44
direct path read16,99008250.270.43
SQL*Net more data from client5,9630920.090.05
SQL*Net break/reset to client2,8490620.040.03
TCP Socket (KGAS)5,76124610.090.03
log file switch (private strand flush incomplete)28051910.000.03
enq: KO - fast object checkpoint12043140.000.02
SQL*Net message to client5,467,41903086.330.02
enq: MS - contention10112850.000.01
reliable message1,6230110.030.01
control file sequential read9410110.010.00
undo segment extension30971270.000.00
latch: cache buffers chains2,8080000.040.00
ADR block file read360040.000.00
Disk file operations I/O2,6770000.040.00
buffer busy waits3050000.000.00
ADR block file write500140.000.00
direct path write temp70070.000.00
library cache: mutex X2,1540000.030.00
read by other session50050.000.00
cursor: pin S1580000.000.00
latch: shared pool710000.000.00
direct path read temp30010.000.00
latch: row cache objects800000.000.00
latch: cache buffers lru chain260000.000.00
latch free230000.000.00
SQL*Net message from client5,467,16402,636,29948286.33 
jobq slave wait17,854958,8214940.28 
wait for unread message on broadcast channel3,663983,6159870.06 
Streams AQ: waiting for messages in the queue7231003,61550000.01 


Back to Wait Events Statistics
Back to Top

Background Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% bg time
db file parallel write225,062011,929533.5592.17
log file parallel write46,9810786170.746.07
control file parallel write1,868078420.030.60
control file sequential read7,85603140.120.24
db file sequential read1,1930870.020.06
db file single write5000480.010.03
os thread startup8302190.000.01
db file scattered read12601110.000.01
log file sync4011640.000.01
enq: JS - queue lock3280120.010.00
reliable message1,5570100.020.00
direct path write500280.000.00
log file single write180070.000.00
ADR block file write500200.000.00
SQL*Net break/reset to client760010.000.00
log file sequential read180030.000.00
ADR block file read160030.000.00
latch: cache buffers lru chain1280000.000.00
latch free80010.000.00
LGWR wait for redo copy1,1930000.020.00
Disk file operations I/O3720000.010.00
library cache: mutex X3730000.010.00
SQL*Net message to client3,1900000.050.00
rdbms ipc message94,3814288,3439361.49 
Streams AQ: qmn slave idle wait1,03407,23069920.02 
DIAG idle wait7,2131007,22010010.11 
Space Manager: slave idle wait1,059975,22249310.02 
smon timer24253,8471602970.00 
Streams AQ: waiting for time management or cleanup tasks12673,7523126700.00 
shared server idle wait1211003,631300110.00 
Streams AQ: waiting for messages in the queue1,2051003,61630010.02 
pmon timer1,2051003,61630010.02 
Streams AQ: qmn coordinator idle wait1,181443,61530610.02 
dispatcher timer601003,601600120.00 
SQL*Net message from client4,3760151350.07 
class slave wait170000.00 


Back to Wait Events Statistics
Back to Top

Wait Event Histogram

  % of Waits
EventTotal Waits <1ms <2ms <4ms <8ms<16ms<32ms <=1s >1s
ADR block file read5244.23.823.117.33.87.7  
ADR block file write10  30.030.020.0 20.0 
ADR file lock12100.0       
Disk file operations I/O3050100.0       
LGWR wait for redo copy1194100.0       
SQL*Net break/reset to client292761.4.617.517.32.7.5.1 
SQL*Net message to client5468K100.0       
SQL*Net more data from client596369.24.712.311.32.4.2  
SQL*Net more data to client768K99.5.0.1.1.1.0.1 
TCP Socket (KGAS)576177.714.23.04.4.4.1.1.0
buffer busy waits30999.7     .3 
control file parallel write1868  .36.429.724.039.6 
control file sequential read879758.44.512.815.65.41.32.1 
cursor: pin S15799.4   .6   
db file parallel read636720.74.210.922.822.310.98.2 
db file parallel write225.3K.0.12.117.719.011.849.4 
db file scattered read36.4K68.62.34.413.97.72.4.8 
db file sequential read161.3K57.06.715.515.63.61.0.5 
db file single write500  40.637.816.04.21.4 
direct path read17K43.414.64.118.714.14.2.9 
direct path read temp366.7 33.3     
direct path write5   20.040.020.020.0 
direct path write temp7  28.642.928.6   
enq: JS - queue lock32872.025.0 1.5.6.3.6 
enq: KO - fast object checkpoint12     8.391.7 
enq: MS - contention1       100.0
enq: SQ - contention3100.0       
enq: TX - row lock contention20412.3 1.0 1.52.052.930.4
latch free3196.8  3.2    
latch: In memory undo latch1100.0       
latch: cache buffers chains280898.31.6.0 .0.0  
latch: cache buffers lru chain154100.0       
latch: call allocation1100.0       
latch: object queue header operation6100.0       
latch: redo allocation5100.0       
latch: row cache objects80100.0       
latch: shared pool71100.0       
latch: undo global data3100.0       
library cache: mutex X252799.9.0  .1   
log file parallel write47K.31.715.424.232.416.59.6 
log file sequential read1838.911.116.727.8 5.6  
log file single write18   83.316.7   
log file switch (private strand flush incomplete)283.6  3.63.6 89.3 
log file sync9356.73.817.230.022.412.813.2 
os thread startup83     100.0  
read by other session5 20.020.060.0    
reliable message318096.01.42.0.3 .1.2 
undo segment extension3033.3   20.016.730.0 
DIAG idle wait7212      100.0 
SQL*Net message from client5468.8K62.96.311.512.44.9.5.7.7
Space Manager: slave idle wait1057.1     .599.4
Streams AQ: qmn coordinator idle wait118156.1.1     43.8
Streams AQ: qmn slave idle wait1031       100.0
Streams AQ: waiting for messages in the queue1930       100.0
Streams AQ: waiting for time management or cleanup tasks1233.3     33.333.3
class slave wait17100.0       
dispatcher timer60       100.0
jobq slave wait17.9K .0.0.0.1.199.8 
pmon timer1203       100.0
rdbms ipc message94.3K16.1.51.01.97.714.635.322.9
shared server idle wait121       100.0
smon timer24       100.0
wait for unread message on broadcast channel3663  .2.9.2 98.7 


Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

  % of Total Waits
EventWaits 64ms to 2s<32ms<64ms<1/8s<1/4s<1/2s <1s <2s>=2s
ADR block file write280.010.010.0     
SQL*Net break/reset to client299.9.0.0     
SQL*Net more data to client99199.9.1.0.0.0   
TCP Socket (KGAS)999.8.1.0.0.0 .0 
buffer busy waits199.7 .3     
control file parallel write74060.48.127.63.9    
control file sequential read18297.91.11.0     
db file parallel read52091.84.62.7.6.2.0  
db file parallel write111.3K50.610.930.67.7.3.0  
db file scattered read28699.2.5.3.0    
db file sequential read84599.5.3.3.0    
db file single write798.6.4.8.2    
direct path read15599.1.4.5     
direct path write180.0 20.0     
enq: JS - queue lock299.4.3.3     
enq: KO - fast object checkpoint118.38.3 25.041.716.7  
enq: MS - contention1      100.0 
enq: TX - row lock contention15216.72.96.95.913.723.521.68.8
log file parallel write449390.44.44.3.8.1   
log file switch (private strand flush incomplete)2510.73.628.642.97.17.1  
log file sync123386.84.94.82.6.7.1  
reliable message799.8.1.1.0.0   
undo segment extension970.013.313.33.3    


Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

  % of Total Waits
EventWaits 4s to 2m<2s <4s <8s<16s<32s< 1m< 2m>=2m
enq: TX - row lock contention1891.28.8      


Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

Service NameDB Time (s)DB CPU (s)Physical Reads (K)Logical Reads (K)
SYS$USERS19,06417,8473,3621,961,544
klash123041
SYS$BACKGROUND00758
klashXDB0000


Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
SYS$USERS 22235766655740006237293104
klash 120300098710
SYS$BACKGROUND 21921346120000


Back to Wait Events Statistics
Back to Top

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IO SQL IdSQL ModuleSQL Text
4,298.15127,4570.0322.5499.810.00f9utav8z8czs4   SELECT SUM(QTY) FROM (SELECT S...
3,616.690 18.9799.470.31742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,385.654346.417.2799.470.1338vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,010.2011,010.205.3099.800.00cdy4c52fdcanv   SELECT DISTINCT D.PO_NO, D.ITE...
1,003.43181,2300.015.2699.820.00c6rkc2anypq67   SELECT SUM(NVL(QTY_IN, 0)-NVL(...
768.814192.204.0399.470.178r6x06b59nuyt DBMS_SCHEDULER /* MV_REFRESH (INS) */INSERT /...
610.554152.643.2099.470.0995hp4vzj95d6b DBMS_SCHEDULER /* MV_REFRESH (DEL) */ delete ...
606.063020.203.1899.790.01ab21w6xh2m3r2 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
605.263020.183.1799.790.01620nmfq8upvsb DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
556.602278.302.9299.730.000qjt2sznvf0r9 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
555.612277.802.9199.730.005n4u7kgq4hwgm DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
512.0168,5370.012.6999.790.0073mzjsxgt8kdb   SELECT NVL(SUM(H.QTY), 0) QTY ...
511.8268,0700.012.6899.780.0042w5jvwyztq0h   SELECT NVL(GET_PLAN_PO_QTY(:B2...
463.097480.622.4399.720.0014n50dm1hxqad   SELECT DISTINCT C.SR# FROM PRO...
405.48844,7300.002.1399.910.004uw118z2skgz1 DBMS_SCHEDULER SELECT WM_CONCAT(PROCESS_NAME)...
364.563611.011.9199.630.002jzkmzmzxza9p DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
354.243640.971.8699.710.00228ak5wrhrzt8   SELECT ITEM_DESC, ITEM_CODE, U...
298.353610.831.5699.790.000hdxxufz5jb36 DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
257.95832.241.3599.440.376xuhp70u9mhz0   SELECT CCODE, DEPT, deptcode, ...
257.821257.821.3599.760.05f36gkv9w0n0yk   SELECT T.PLAN_NO, PRODUCTION.G...
255.266680.381.3499.690.007grsf923r1m4g   select distinct Z.ACT_SIZE S, ...
253.112,940,9710.001.33100.710.001t8bh6fd2yug3   SELECT DISTINCT R.RATE FROM PR...
251.505240.481.3299.700.00d8h9553qm1h2j   SELECT R.LOT#, DECODE(NVL(SUM(...
247.651220.641.3099.270.0117x173b8v53p7 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
234.361219.531.2399.810.00axqbs2kdrnra7 DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
214.20229.741.1299.790.000cju83fh33y95   SELECT COUNT(*) FROM (SELECT D...
210.5032,7090.011.1098.661.22a63g1ffphjkdc   SELECT SUM(DECODE(NVL(DYS, 0),...
208.22952.191.0984.820.00ch0thvp9q6y1w   SELECT DISTINCT T.PLAN_NO, T.P...
203.711118.521.0771.6427.683ss16xn9jc3by   select /*+ NO_CPU_COSTING */ m...
202.71367.571.0699.720.09g55xjd69zmcmm   SELECT ALL E . EMPCODE , D . ...
201.80450.451.0699.680.137dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
198.83299.411.0499.530.01932827s2cyx97 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...


Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

CPU Time (s)Executions CPU per Exec (s)%TotalElapsed Time (s)%CPU%IO SQL IdSQL ModuleSQL Text
4,289.87127,4570.0324.054,298.1599.810.00f9utav8z8czs4   SELECT SUM(QTY) FROM (SELECT S...
3,597.610 20.173,616.6999.470.31742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,378.354344.597.731,385.6599.470.1338vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,008.2311,008.235.651,010.2099.800.00cdy4c52fdcanv   SELECT DISTINCT D.PO_NO, D.ITE...
1,001.62181,2300.015.611,003.4399.820.00c6rkc2anypq67   SELECT SUM(NVL(QTY_IN, 0)-NVL(...
764.764191.194.29768.8199.470.178r6x06b59nuyt DBMS_SCHEDULER /* MV_REFRESH (INS) */INSERT /...
607.314151.833.40610.5599.470.0995hp4vzj95d6b DBMS_SCHEDULER /* MV_REFRESH (DEL) */ delete ...
604.803020.163.39606.0699.790.01ab21w6xh2m3r2 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
603.993020.133.39605.2699.790.01620nmfq8upvsb DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
555.112277.563.11556.6099.730.000qjt2sznvf0r9 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
554.122277.063.11555.6199.730.005n4u7kgq4hwgm DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
510.9368,5370.012.86512.0199.790.0073mzjsxgt8kdb   SELECT NVL(SUM(H.QTY), 0) QTY ...
510.6868,0700.012.86511.8299.780.0042w5jvwyztq0h   SELECT NVL(GET_PLAN_PO_QTY(:B2...
461.797480.622.59463.0999.720.0014n50dm1hxqad   SELECT DISTINCT C.SR# FROM PRO...
405.13844,7300.002.27405.4899.910.004uw118z2skgz1 DBMS_SCHEDULER SELECT WM_CONCAT(PROCESS_NAME)...
363.223611.012.04364.5699.630.002jzkmzmzxza9p DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
353.233640.971.98354.2499.710.00228ak5wrhrzt8   SELECT ITEM_DESC, ITEM_CODE, U...
297.723610.821.67298.3599.790.000hdxxufz5jb36 DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
257.191257.191.44257.8299.760.05f36gkv9w0n0yk   SELECT T.PLAN_NO, PRODUCTION.G...
256.50832.061.44257.9599.440.376xuhp70u9mhz0   SELECT CCODE, DEPT, deptcode, ...
254.902,940,9710.001.43253.11100.710.001t8bh6fd2yug3   SELECT DISTINCT R.RATE FROM PR...
254.466680.381.43255.2699.690.007grsf923r1m4g   select distinct Z.ACT_SIZE S, ...
250.735240.481.41251.5099.700.00d8h9553qm1h2j   SELECT R.LOT#, DECODE(NVL(SUM(...
245.841220.491.38247.6599.270.0117x173b8v53p7 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
233.911219.491.31234.3699.810.00axqbs2kdrnra7 DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
213.76229.721.20214.2099.790.000cju83fh33y95   SELECT COUNT(*) FROM (SELECT D...
207.6732,7090.011.16210.5098.661.22a63g1ffphjkdc   SELECT SUM(DECODE(NVL(DYS, 0),...
202.14367.381.13202.7199.720.09g55xjd69zmcmm   SELECT ALL E . EMPCODE , D . ...
201.14450.291.13201.8099.680.137dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
197.89298.941.11198.8399.530.01932827s2cyx97 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...


Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

User I/O Time (s)Executions UIO per Exec (s)%TotalElapsed Time (s)%CPU%IO SQL IdSQL ModuleSQL Text
56.38115.138.30203.7171.6427.683ss16xn9jc3by   select /*+ NO_CPU_COSTING */ m...
11.160 1.643,616.6999.470.31742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
9.5711,2040.001.41153.9193.626.22f56xhasgt6st2   SELECT /* OPT_DYN_SAMP */ /*+ ...
2.5832,7090.000.38210.5098.661.22a63g1ffphjkdc   SELECT SUM(DECODE(NVL(DYS, 0),...
1.8440.460.271,385.6599.470.1338vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1.2940.320.19768.8199.470.178r6x06b59nuyt DBMS_SCHEDULER /* MV_REFRESH (INS) */INSERT /...
0.9480.120.14257.9599.440.376xuhp70u9mhz0   SELECT CCODE, DEPT, deptcode, ...
0.5540.140.08610.5599.470.0995hp4vzj95d6b DBMS_SCHEDULER /* MV_REFRESH (DEL) */ delete ...
0.2740.070.04201.8099.680.137dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
0.1830.060.03202.7199.720.09g55xjd69zmcmm   SELECT ALL E . EMPCODE , D . ...


Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets ExecutionsGets per Exec %TotalElapsed Time (s) %CPU %IO SQL IdSQL ModuleSQL Text
351,776,468487,944,117.0017.941,385.6599.5.138vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
340,362,3410 17.353,616.6999.5.3742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
290,346,144127,4572,277.9914.804,298.1599.80f9utav8z8czs4   SELECT SUM(QTY) FROM (SELECT S...
200,865,668450,216,417.0010.24610.5599.5.195hp4vzj95d6b DBMS_SCHEDULER /* MV_REFRESH (DEL) */ delete ...
150,247,250437,561,812.507.66768.8199.5.28r6x06b59nuyt DBMS_SCHEDULER /* MV_REFRESH (INS) */INSERT /...
75,539,014361209,249.353.85364.5699.602jzkmzmzxza9p DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
65,812,185165,812,185.003.361,010.2099.80cdy4c52fdcanv   SELECT DISTINCT D.PO_NO, D.ITE...
65,797,027181,230363.063.351,003.4399.80c6rkc2anypq67   SELECT SUM(NVL(QTY_IN, 0)-NVL(...
62,825,790361174,032.663.20298.3599.800hdxxufz5jb36 DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
57,920,664228,960,332.002.95556.6099.700qjt2sznvf0r9 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
57,826,849228,913,424.502.95555.6199.705n4u7kgq4hwgm DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
54,689,17068,537797.952.79512.0199.8073mzjsxgt8kdb   SELECT NVL(SUM(H.QTY), 0) QTY ...
54,316,88168,070797.962.77511.8299.8042w5jvwyztq0h   SELECT NVL(GET_PLAN_PO_QTY(:B2...
42,409,65274856,697.402.16463.0999.7014n50dm1hxqad   SELECT DISTINCT C.SR# FROM PRO...
40,248,521301,341,617.372.05606.0699.80ab21w6xh2m3r2 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
40,232,334301,341,077.802.05605.2699.80620nmfq8upvsb DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
36,687,547136,687,547.001.87257.8299.80f36gkv9w0n0yk   SELECT T.PLAN_NO, PRODUCTION.G...
35,408,13948,852,034.751.81201.8099.7.17dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
34,362,148217,181,074.001.75198.8399.50932827s2cyx97 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
23,385,85437,795,284.671.19202.7199.7.1g55xjd69zmcmm   SELECT ALL E . EMPCODE , D . ...
21,136,098844,73025.021.08405.4899.904uw118z2skgz1 DBMS_SCHEDULER SELECT WM_CONCAT(PROCESS_NAME)...
20,514,03536456,357.241.05354.2499.70228ak5wrhrzt8   SELECT ITEM_DESC, ITEM_CODE, U...


Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical ReadsExecutionsReads per Exec %TotalElapsed Time (s)%CPU%IO SQL IdSQL ModuleSQL Text
1,443,76711131,251.5542.85203.7171.6427.683ss16xn9jc3by   select /*+ NO_CPU_COSTING */ m...
1,3350 0.043,616.6999.470.31742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,07911,2040.100.03153.9193.626.22f56xhasgt6st2   SELECT /* OPT_DYN_SAMP */ /*+ ...
85532,7090.030.03210.5098.661.22a63g1ffphjkdc   SELECT SUM(DECODE(NVL(DYS, 0),...
212453.000.01201.8099.680.137dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
2091209.000.01257.8299.760.05f36gkv9w0n0yk   SELECT T.PLAN_NO, PRODUCTION.G...
188447.000.011,385.6599.470.1338vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
182822.750.01257.9599.440.376xuhp70u9mhz0   SELECT CCODE, DEPT, deptcode, ...
117429.250.00768.8199.470.178r6x06b59nuyt DBMS_SCHEDULER /* MV_REFRESH (INS) */INSERT /...
71417.750.00610.5599.470.0995hp4vzj95d6b DBMS_SCHEDULER /* MV_REFRESH (DEL) */ delete ...


Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%Total SQL IdSQL ModuleSQL Text
8,5508,55011777.270.003.313ss16xn9jc3by   select /*+ NO_CPU_COSTING */ m...
1,3341,3340 0.000.52742vw5aqd5947 DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
1,0791,07911,2040.100.000.42f56xhasgt6st2   SELECT /* OPT_DYN_SAMP */ /*+ ...
211211452.750.000.087dp2ntks3umau   SELECT D.PLAN_NO, D.MS_PO, D.I...
2092091209.000.000.08f36gkv9w0n0yk   SELECT T.PLAN_NO, PRODUCTION.G...
188188447.000.000.0738vxaybzk778b DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
182182822.750.000.076xuhp70u9mhz0   SELECT CCODE, DEPT, deptcode, ...
3838312.670.000.01g55xjd69zmcmm   SELECT ALL E . EMPCODE , D . ...
252512,5880.000.000.01fm1jr4dtpjyx2   SELECT SUM(SQTY) FROM UKPO P, ...
12126,5360.000.000.00485jmv7xv085c   SELECT QUALITY_REF, FIBER_COMP...


Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows ProcessedRows per ExecElapsed Time (s) %CPU %IO SQL IdSQL ModuleSQL Text
2,940,9712,940,6111.00253.11100.701t8bh6fd2yug3   SELECT DISTINCT R.RATE FROM PR...
844,730844,7301.00405.4899.904uw118z2skgz1 DBMS_SCHEDULER SELECT WM_CONCAT(PROCESS_NAME)...
181,230181,2301.001,003.4399.80c6rkc2anypq67   SELECT SUM(NVL(QTY_IN, 0)-NVL(...
127,457127,4231.004,298.1599.80f9utav8z8czs4   SELECT SUM(QTY) FROM (SELECT S...
68,53768,5371.00512.0199.8073mzjsxgt8kdb   SELECT NVL(SUM(H.QTY), 0) QTY ...
68,07068,0701.00511.8299.8042w5jvwyztq0h   SELECT NVL(GET_PLAN_PO_QTY(:B2...
66,950241,6223.6160.5099.50cm5vu20fhtnq1   獥汥捴 ⨫⁣潮湥捴形祟晩汴敲楮朠⨯⁰物癩汥来⌬汥癥氠晲潭⁳祳...
50,423723,64314.353.8490.900k8522rmdzg4k   獥汥捴⁰物癩汥来⌠晲潭⁳祳慵瑨␠睨敲攠⡧牡湴敥⌽㨱爠杲慮瑥...
47,19547,1951.004.3927.206rf1xb3rsb3c9   SELECT CCODE_VALIDATION(:b1) F...
32,70932,7081.00210.5098.71.2a63g1ffphjkdc   SELECT SUM(DECODE(NVL(DYS, 0),...


Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL Text
66,95066,9506.36cm5vu20fhtnq1   獥汥捴 ⨫⁣潮湥捴形祟晩汴敲楮朠⨯⁰物癩汥来⌬汥癥氠晲潭⁳祳...
50,42350,4234.790k8522rmdzg4k   獥汥捴⁰物癩汥来⌠晲潭⁳祳慵瑨␠睨敲攠⡧牡湴敥⌽㨱爠杲慮瑥...
47,19347,1954.486rf1xb3rsb3c9   SELECT CCODE_VALIDATION(:b1) F...
26,54526,5452.528k4xz9kr1brkt   SELECT NVL(MAX(OPR_DTL_ID), 0)...
26,41626,4162.51fys1zsgmvh9ka   SELECT PHASE_DESC FROM PHASE W...
26,41126,4112.510307xvv5xhu0f   SELECT ART_DESC FROM MKT_ARTIC...
26,40826,4082.51f4032m9c04cv1   SELECT DSC FROM UKPRODUCT WHER...
26,40226,4022.510pzg71pnur0kp   SELECT NAME FROM UKLICENSOR WH...
26,40226,4022.514xn0tw9j8rcnh   SELECT SUM(SQTY) FROM UKPO P, ...
26,40226,4022.515fg74rb3ru4ac   SELECT PRINT_NAME FROM UKPRINT...
26,40226,4022.51671prjcayrc77   SELECT NAME FROM UKAGENT WHERE...
26,40226,4022.51a8bkyjcv4nuvx   SELECT C.CUS_NAME || '-' || A....
21,02021,0202.0061jagxsr76cr0   SELECT SMP_DESC FROM MKT_SMP W...
16,97016,9701.613gzbt6t7c97cu   SELECT PROSDESC, PROSGRP FROM ...
12,58812,5881.203xqr3nrwy1zyy   SELECT WM_CONCAT1(PONO) FROM K...
12,58812,5881.20fm1jr4dtpjyx2   SELECT SUM(SQTY) FROM UKPO P, ...
11,20411,2041.06f56xhasgt6st2   SELECT /* OPT_DYN_SAMP */ /*+ ...


Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Sharable Mem (b)Executions % Total SQL IdSQL ModuleSQL Text
5,588,651110.043ss16xn9jc3by   select /*+ NO_CPU_COSTING */ m...
4,721,79510.041ytt2nppswspc   select /*+ NO_CPU_COSTING */ M...
3,187,81830.0218z93abjz28am   SELECT PONO, 'A' O, 'REQUIRED'...
2,635,274300.02620nmfq8upvsb DBMS_SCHEDULER INSERT /*+ BYPASS_RECURSIVE_CH...
1,575,91170.019pc5hdmqdgx9j   UPDATE tblhrmemployee SET EMPC...
1,109,5552020.0146x2m3gxmrbsq   UPDATE processing.lab_mst SET ...


Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL IdSQL Text
0307xvv5xhu0fSELECT ART_DESC FROM MKT_ARTICLE WHERE ART_ID = :b1
0cju83fh33y95SELECT COUNT(*) FROM (SELECT DISTINCT DATED FROM OPR_WAGES_EMP_MASTER WHERE TO_DATE(DATED, 'DD-MM-RR') BETWEEN :b1 AND :b2 )
0hdxxufz5jb36INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."YARN_BAG_STK_LOC" select t.LOC_ID, t.BAG_NO, sum(t.BAG_QTY)BAG_QTY from yarn_po_stock_barcode t group by t.LOC_ID, t.BAG_NO having sum(t.BAG_QTY)>0
0k8522rmdzg4k獥汥捴⁰物癩汥来⌠晲潭⁳祳慵瑨␠睨敲攠⡧牡湴敥⌽㨱爠杲慮瑥攣㴱⤠慮搠灲楶楬敧攣㸰
0pzg71pnur0kpSELECT NAME FROM UKLICENSOR WHERE LICENSORID = :b1
0qjt2sznvf0r9DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.MV_SHIPPING'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
14n50dm1hxqadSELECT DISTINCT C.SR# FROM PRODUCTION.CUT_SUPLY_BUNDL C, CUT_SUPLY_BUNDL_M M WHERE C.DOC_ID=M.DOC_ID AND C.CARD_NO=:B2 AND M.PLANNO=:B1 AND C.STATUS=0 AND C.SR# IN (SELECT NVL(A.SR#, 0) FROM STIT_REC_ISSUE_D A )
17x173b8v53p7DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.MV_STITCHING'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
18z93abjz28am SELECT PONO, 'A' O, 'REQUIRED' TTYPE, HIT_TYPE, HIT_NO, DELIVERY_DATE, NULL SHIPMODE, '0' MANUL_INV_NO, NULL ETA_DATE, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO UNION ALL SELECT PONO, 'A' O, 'PREPACK RATIO' TTYPE, HIT_TYPE, 0, NULL, NULL, NULL, NULL, SUM(S1/PACK_QTY) S1, SUM(S2/PACK_QTY) S2, SUM(S3/PACK_QTY) S3, SUM(S4/PACK_QTY) S4, SUM(S5/PACK_QTY) S5, SUM(S6/PACK_QTY) S6, SUM(S7/PACK_QTY) S7, SUM(S8/PACK_QTY) S8, SUM(S9/PACK_QTY) S9, SUM(S10/PACK_QTY) S10, SUM(S11/PACK_QTY) S11, SUM(S12/PACK_QTY) S12, SUM(S13/PACK_QTY) S13, SUM(S14/PACK_QTY) S14, SUM(S15/PACK_QTY) S15, SUM(S16/PACK_QTY) S16, SUM(S17/PACK_QTY) S17, SUM(S18/PACK_QTY) S18, SUM(S19/PACK_QTY) S19, SUM(S20/PACK_QTY) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO AND HIT_TYPE = 'PREPACK' GROUP BY PONO , HIT_TYPE UNION ALL SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SHIP_DATE, SHIPMODE, MANUL_INV_NO, ETA_DATE, SUM(S1) S1 , SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13), SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM( SELECT M.PONO, 'B' O, 'SHIPPED' TTYPE, D.HIT_TYPE, D.HIT_NO, E.INV_DATE SHIP_DATE, E.SHIP_MODE SHIPMODE, E.INV_NO_M MANUL_INV_NO, E.ETA_DATE ETA_DATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FC TN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'B' , 'SHIPPED', D.HIT_TYPE, D.HIT_NO, E.INV_DATE, E.SHIP_MODE, E.INV_NO_M, E.ETA_DATE UNION ALL -----------------SPLIT PACKING LIST SELECT M.PONO, 'B' O, 'SHIPPED' TTYPE, S.HIT_TYPE, S.HIT_NO, E.INV_DATE SHIP_DATE, E.SHIP_MODE SHIPMODE, E.INV_NO_M MANUL_INV_NO, E.ETA_DATE ETA_DATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S 7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'B' , 'SHIPPED', S.HIT_TYPE, S.HIT_NO, E.INV_DATE, E.SHIP_MODE, E.INV_NO_M, E.ETA_DATE ) GROUP BY PONO , O , TTYPE , HIT_TYPE , HIT_NO , SHIP_DATE , SHIPMODE , MANUL_INV_NO , ETA_DATE UNION ALL SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, NULL, NULL, NULL, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6 ) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT M.PONO, 'BB' O, 'TOTAL' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D. FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BB' , 'TOTAL', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BB' O, 'TOTAL' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BB' , 'TOTAL', S.HIT_TYPE, S.HIT_NO -- END SHIPPED HITWISE TOTAL ------------------------------- ) GROUP BY PONO , O , TTYPE , HIT_TYPE , HIT_NO , SYSDATE union all SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, NULL, NULL, NULL, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S 1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_N O IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK and s.hit_type='PREPACK' AND P.INV_NO= E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', S.HIT_TYPE, S.HIT_NO union all ------------ SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, -TRUNC(NVL(SUM(S1)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S1, -TRUNC(NVL(SUM(S2)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S2, -TRUNC(NVL(SUM(S3)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S3, -TRUNC(NVL(SUM(S4)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S4, -TRUNC(NVL(SUM(S5)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S5, -TRUNC(NVL(SUM(S6)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S6, -TRUNC(NVL(SUM(S7)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S7, -TRUNC(NVL(SUM(S8)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S8, -TRUNC(NVL(SUM(S9)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S9, -TRUNC(NVL(SUM(S10)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S10, -TRUNC(NVL(SUM(S11)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S11, -TRUNC(NVL(SUM(S12)/ge t_qty_po(:PONO), 0))*get_qty_po(:PONO) S12, -TRUNC(NVL(SUM(S13)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S13, -TRUNC(NVL(SUM(S14)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S14, -TRUNC(NVL(SUM(S15)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S15, -TRUNC(NVL(SUM(S16)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S16, -TRUNC(NVL(SUM(S17)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S17, -TRUNC(NVL(SUM(S18)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S18, -TRUNC(NVL(SUM(S19)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S19, -TRUNC(NVL(SUM(S20)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S20 FROM ( SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)) , 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO --and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO --and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', S.HIT_TYPE, S.HIT_NO ) GROUP BY PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE -------- /* SELECT PONO, 'BBBBB' O, 'RATIO BALANCE' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , -SUM(S1/PACK_QTY)*get_qty_po(:PONO) S1, -SUM(S2/PACK_QTY)*get_qty_po(:PONO) S2, -SUM(S3/PACK_QTY)*get_qty_po(:PONO) S3, -SUM(S4/PACK_QTY)*get_qty_po(:PONO) S4, -SUM(S5/PACK_QTY)*get_qty_po(:PONO) S5, -SUM(S6/PACK_QTY)*get_qty_po(:PONO) S6, -SUM(S7/PACK_QTY)*get_qty_po(:PONO) S7, -SUM(S8/PACK_QTY)*get_qty_po(:PONO) S8, -SUM(S9/PACK_QTY)*get_qty_po(:PONO) S9, -SUM(S10/PACK_QTY)*get_qty_po(:PONO) S10, -SUM(S11/PACK_QTY)*get_qty_po(:PONO) S11, -SUM(S12/PACK_QTY)*get_qty_po(:PONO) S12, -SUM(S13/PACK_QTY)*get_qty_po(:PONO) S13, -SUM(S14/PACK_QTY)*get_qty_po(:PONO) S14, -SUM(S15/PACK_QTY)*get_qty_po(:PONO) S15, -SUM(S16/PACK_QTY)*get_qty_po(:PONO) S16, -SUM(S17/PACK_QTY)*get_qty_po(:PONO) S17, -SUM(S18/PACK_QTY)*get_qty_po(:PONO) S18, -SUM(S19/PACK_QTY)*get_qty_po(:PONO) S19, -SUM(S20/PACK_QTY)*get_qty_po(:PONO) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO=:PONO AND HIT_TYPE='PREPACK' GROUP BY PONO, HIT_TYPE, hit_no */ ) GROUP BY PONO , O , TT YPE , HIT_TYPE , HIT_NO , SYSDATE union all SELECT PONO, 'BBBB' O, 'PREPACK AS PER TOTAL' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , to_char((NVL(SUM(S1/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S2/PACK_QTY)*get_qty_po(:PONO), 0)+NVL(SUM(S3/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S4/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S5/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S6/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S7/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S8/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S9/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S10/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S11/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S12/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S13/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S14/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S15/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S16/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S17/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S18/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S19/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S20/P ACK_QTY)*get_qty_po(:PONO), 0) )/(NVL(SUM(S1/PACK_QTY), 0)+NVL(SUM(S2/PACK_QTY), 0)+NVL(SUM(S3/PACK_QTY), 0) +NVL(SUM(S4/PACK_QTY), 0)+NVL(SUM(S5/PACK_QTY), 0)+ NVL(SUM(S6/PACK_QTY), 0)+NVL(SUM(S7/PACK_QTY), 0)+NVL(SUM(S8/PACK_QTY), 0)+NVL(SUM(S9/PACK_QTY), 0)+NVL(SUM(S10/PACK_QTY), 0)+ NVL(SUM(S11/PACK_QTY), 0)+NVL(SUM(S12/PACK_QTY), 0)+NVL(SUM(S13/PACK_QTY), 0)+NVL(SUM(S14/PACK_QTY), 0)+ NVL(SUM(S15/PACK_QTY), 0) + NVL(SUM(S16/PACK_QTY), 0) +NVL(SUM(S17/PACK_QTY), 0)+NVL(SUM(S18/PACK_QTY), 0)+NVL(SUM(S19/PACK_QTY), 0)+NVL(SUM(S20/PACK_QTY), 0) )) SHIPMODE, 'PACKED QTY' MANUL_INV_NO, NULL ETA_DATE, SUM(S1/PACK_QTY)*get_qty_po(:PONO) S1, SUM(S2/PACK_QTY)*get_qty_po(:PONO) S2, SUM(S3/PACK_QTY)*get_qty_po(:PONO) S3, SUM(S4/PACK_QTY)*get_qty_po(:PONO) S4, SUM(S5/PACK_QTY)*get_qty_po(:PONO) S5, SUM(S6/PACK_QTY)*get_qty_po(:PONO) S6, SUM(S7/PACK_QTY)*get_qty_po(:PONO) S7, SUM(S8/PACK_QTY)*get_qty_po(:PONO) S8, SUM(S9/PACK_QTY)*get_qty_po(:PONO) S9, SUM(S10/PACK_QTY)*get_qty_po(:PONO) S10 , SUM(S11/PACK_QTY)*get_qty_po(:PONO) S11, SUM(S12/PACK_QTY)*get_qty_po(:PONO) S12, SUM(S13/PACK_QTY)*get_qty_po(:PONO) S13, SUM(S14/PACK_QTY)*get_qty_po(:PONO) S14, SUM(S15/PACK_QTY)*get_qty_po(:PONO) S15, SUM(S16/PACK_QTY)*get_qty_po(:PONO) S16, SUM(S17/PACK_QTY)*get_qty_po(:PONO) S17, SUM(S18/PACK_QTY)*get_qty_po(:PONO) S18, SUM(S19/PACK_QTY)*get_qty_po(:PONO) S19, SUM(S20/PACK_QTY)*get_qty_po(:PONO) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO AND HIT_TYPE = 'PREPACK' GROUP BY PONO , HIT_TYPE , hit_no UNION ALL SELECT PONO, 'C' O, 'BALANCE TO SHIP' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , NULL SHIPMODE, NULL MANUL_INV_NO, NULL ETA_DATE, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT PONO, HIT_TYPE, HIT_NO, -S1 S1, -S2 S2, -S3 S3, -S4 S4, -S5 S5, -S 6 S6, -S7 S7, -S8 S8, -S9 S9, -S10 S10, -S11 S11, -S12 S12, -S13 S13, -S14 S14, -S15 S15, -S16 S16, -S17 S17, -S18 S18, -S19 S19, -S20 S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO=:PONO UNION ALL SELECT M.PONO, D.HIT_TYPE, D.HIT_NO, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCT N -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, S.HIT_TYPE, S.HIT_NO, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_P L P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, S.HIT_TYPE, S.HIT_NO ) HIT_BAL GROUP BY PONO , 'BALANCE TO SHIP' , HIT_TYPE , HIT_NO , SYSDATE , '0' , '0' , '0' ORDER BY 1 ASC, 4 DESC, 5 ASC , HIT_TYPE DESC , HIT_NO , O ASC
1t8bh6fd2yug3SELECT DISTINCT R.RATE FROM PRODUCTION.RES_WAGES_PLANOPR R WHERE R.PLANNO = :B2 AND R.OPERATION_ID=:B1
1ytt2nppswspc select /*+ NO_CPU_COSTING */ MM.A, mm.col, mm.ACT_SIZE, mm.REQ_QTY, MM.TO_CUT_PCS TO_CUT_PC, (MM.TO_CUT_PCS-ab.qty1) RJ_QTY, ab.qty1-NVL(UA.MISSED_QTY, 0) QTY1, nvl(S.S_QTY, 0) S_QTY, NVL(UA.MISSED_QTY, 0) MISS_QTY, round(mm.REQ_QTY + (mm.REQ_QTY*max(tp.waste)/100), 0) req_2, Z.REJ_QTY, ST_QTY, s.cut_status, s.ind_status, s.stit_status, s.plan_status, nvl(WR.WHQTY, 0)whqty, nvl(SR.WRQTY, 0)wrqty, (nvl(WR.WHQTY, 0)-nvl(SR.WRQTY, 0))netwhqty from LS_ACT_CUT_PCS_VIEW1 MM, tblplanorder tp, plan_status s, ( select m.planno, d.g_size, m.garment_id , NVL(sum(d.qty), 0) QTY1, D.HITID from stit_rec_issue_m m, stit_rec_issue_d d where d.doc_id=m.doc_id and m.document_type='STR' and m.planno=:plan AND D.HITID=:P_HIT and d.status=0 group by m.planno, d.g_size, m.garment_id, D.HITID ) ab , ( select V.COL, t.plan_no, t.g_size, v.garment_id, count(t.g_size) MISSED_Qty, V.HITID from missed_gmt_id t, LS_act_cut_pcs_view1 v where t.plan_no = :plan AND V.HITID=:P_HIT AND v.PLAN_NO=t.plan_no and V. ACT_SIZE=T.G_SIZE group by t.plan_no, t.g_size, V.COL, v.garment_id, V.HITID ORDER BY V.COL )UA, ( select v.planno, v.g_size, nvl(sum(v.rej_qty), 0) rej_qty from LS_cut_stch_iss_bal_v v where v.planno=:plan AND V.HITID=:P_HIT group by v.planno, v.g_size ) z , ( select --t.ACT_SIZE COL, v.s col, round(sum(v.s1)/ nvl(GET_PLAN_COUNTGMT(v.plan_no), 1), 0) S_QTY, V.HIT_ID from LS_SHIP_SIZE_VIEW v /*, act_cut_pcs_view t*/ where --t.PLAN_NO=v.plan_no -- and v.plan_no= :PLAN AND V.HIT_ID=:P_HIT --and t.COL=v.S group by v.s, v.plan_no, V.HIT_ID--t.ACT_SIZE order by v.S ) S, ( SELECT T.ACT_SIZE COL, T.GARMENT_ID, SUM(V.QTY) WHQTY, T.HITID FROM LS_STITCH_RECIVE_WHAREHOUSE V, LS_ACT_CUT_PCS_VIEW1 T WHERE T.PLAN_NO=V.PLANNO AND T.PLAN_NO= :PLAN AND V.HITID=:P_HIT AND V.HITID=T.HITID AND T.COL=V.COL_SIZE and v.garment_id(+)=t.garment_id GROUP BY T.ACT_SIZE, V.COL_SIZE, T.GARMENT_ID, T.HITID HAVING SUM(V.QTY)>0 ORDER BY V.COL_SIZE ) WR, ( select d.g_size COL, v.garment_id, SUM(d.qty)WRQTY, D. HITID from stit_rec_issue_m m, stit_rec_issue_d d, LS_ACT_CUT_PCS_VIEW1 v where m.document_type='SRW' and m.doc_id=d.doc_id and m.planno=:PLAN and v.PLAN_NO=m.planno and v.COL=d.col_size and m.garment_id=v.garment_id AND D.HITID=:P_HIT AND D.HITID=V.HITID group by d.g_size , v.GARMENT_ID, D.HITID, d.col_size HAVING SUM(d.QTY)>0 ORDER BY d.col_size )SR, ( select d.g_size, m.garment_id, sum(d.stitched_qty) ST_QTY, D.HITID from cut_suply_bundl_m m, cut_suply_bundl d where d.doc_id=m.doc_id and d.ent_type='IITS' and m.planno=:PLAN AND D.HITID=:P_HIT and d.status=0 and d.stitched_qty is not null group by m.planno, d.g_size, m.garment_id, D.HITID, d.col_size order by d.col_size ) h where tp.plan_no =MM.plan_no AND MM.PLAN_NO = :PLAN AND MM.HITID=:P_HIT and ab.g_size(+)=mm.ACT_SIZE AND UA.G_SIZE(+)=mm.ACT_SIZE and z.g_size(+)=mm.ACT_SIZE and S.COL(+)=mM.COL and h.g_size(+)=mM.act_size and wr.col(+)=mM.act_size and sr.col(+)=mM.act_size --AND MM.REQ_QTY>0 and tp.waste is not null and m m.garment_id=ab.garment_id(+) and mm.garment_id=ua.garment_id(+) and mm.garment_id=h.garment_id(+) and mm.garment_id=sr.garment_id(+) and mm.garment_id=wr.garment_id(+) and mm.plan_no=s.plan_no(+) group by mm.col, MM.PLAN_NO, mm.ACT_SIZE, mm.REQ_QTY, ab.qty1, rej_qty, S.S_QTY, MM.TO_CUT_PCS, ST_QTY, MISSED_QTY, MM.A , s.cut_status, s.ind_status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY, MM.HITID Order by 1
228ak5wrhrzt8SELECT ITEM_DESC, ITEM_CODE, UNIT_CODE, NVL(SUM(DECODE(D.ENTRYTYPE, 'RE', 1, 'IR', 1, 'SR', 1, 'TF', 1, 'RR', -1, 'IS', -1, 'SP', -1, 'TR', -1, 0) * QTY), 0) BQTY FROM ITEM I, STORE_DETAIL D, STORE_MASTER M WHERE M.SRNO=D.SRNO AND I.ITEM_CODE = D.ITEMCODE (+) AND I.SUB_CODE > 2100 AND D.CCODE = :1 AND D.ENTRYTYPE IN ('RE', 'IR', 'SR', 'TF', 'RR', 'IS', 'SP', 'TR') AND M.SUBSTOREID=:2 GROUP BY ITEM_DESC, ITEM_CODE, UNIT_CODE ORDER BY 1
2jzkmzmzxza9pDECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.yarn_bag_stk_loc'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
38vxaybzk778bDECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.wages_sheet_mv'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
3gzbt6t7c97cuSELECT PROSDESC, PROSGRP FROM UKPROS WHERE PROSID = :b1
3ss16xn9jc3by select /*+ NO_CPU_COSTING */ mm.col, mm.ACT_SIZE, mm.REQ_QTY, MM.TO_CUT_PCS TO_CUT_PC , ab.qty1, nvl(S.S_QTY, 0) S_QTY, round(mm.REQ_QTY + (mm.REQ_QTY*tp.waste/100), 0) req_2, Z.REJ_QTY, ST_QTY, s.cut_status, s.ind_status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY from ACT_CUT_PCS_VIEW MM, tblplanorder tp, plan_status s, ( SELECT m.planno, d.g_SIZe, SUM(nvl(D.QTY, 0)) QTY1 FROM CUT_SUPLY_BUNDL D, CUT_SUPLY_BUNDL_M M WHERE (M.ENTRY_TYPE = 'IITS') AND (M.DOC_ID = D.DOC_ID) and m.planno=:plan GROUP BY M.PLANNO, M.ENTRY_TYPE, D.G_SIZE ) ab , ( select v.planno, v.g_size, nvl(sum(v.rej_qty), 0) rej_qty from cut_stch_iss_bal_v v where v.planno=:plan group by v.planno, v.g_size ) z , ( select t.ACT_SIZE COL, v.s1 S_QTY from SHIP_SIZE_VIEW v, act_cut_pcs_view t where t.PLAN_NO=v.plan_no and t.plan_no= :PLAN and t.COL=v.S order by v.S ) S, (SELECT T.ACT_SIZE COL, SUM(V.QTY) WHQTY FROM STITCH_RECIVE_WHAREHOUSE V, ACT_CUT_PCS_VIEW T WHERE T.PLAN_NO=V.PLANNO AND T.PLAN_NO= :PLAN AND T.COL=V.COL_SIZE GROUP BY T.ACT_SIZE, V.COL_SIZE HAVING SUM(V.QTY)>0 ORDER BY V.COL_SIZE) WR, (SELECT T.ACT_SIZE COL, SUM(R.QTY)WRQTY FROM STITCH_RETURN_WHAREHOUSE R, ACT_CUT_PCS_VIEW T WHERE T.PLAN_NO=R.PLANNO AND T.PLAN_NO=:PLAN AND T.COL=R.COL_SIZE GROUP BY T.ACT_SIZE, R.COL_SIZE HAVING SUM(R.QTY)>0 ORDER BY R.COL_SIZE)SR, (select d.g_size, sum(d.stitched_qty) ST_QTY from cut_suply_bundl_m m, cut_suply_bundl d where d.doc_id=m.doc_id and d.ent_type='IITS' and m.planno=:PLAN and d.stitched_qty is not null group by m.planno, d.g_size order by d.col_size ) h where tp.plan_no =MM.plan_no AND MM.PLAN_NO = :PLAN and ab.g_size(+)=mm.ACT_SIZE and z.g_size(+)=mm.ACT_SIZE and S.COL(+)=mM.act_size and h.g_size(+)=mM.act_size and wr.col(+)=mM.act_size and sr.col(+)=mM.act_size AND MM.REQ_QTY>0 and tp.waste is not null and mm.plan_no=s.plan_no(+) group by mm.col, MM.PLAN_NO, mm.ACT_SIZE, mm.REQ_QTY, ab.qty1, rej_qty, tp.waste, S.S_QTY, MM.TO_CUT_PCS, ST_QTY , s.cut_status, s.ind_ status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY Order by 1
3xqr3nrwy1zyySELECT WM_CONCAT1(PONO) FROM KCL_PO_MAIN WHERE ORDERID = :b1
42w5jvwyztq0hSELECT NVL(GET_PLAN_PO_QTY(:B2 , :B1 ), 0) FROM DUAL
46x2m3gxmrbsqUPDATE processing.lab_mst SET LABID=:1, REC_DATE=:2, TEST_TYPE=:3, LOTNO=:4, PLANNO=:5, ORDERNO=:6, ROLL_NO=:7, REF_CODE=:8, ccode=:9, CDK_ID=:10, ITEM_CODE=:11, FABRIC_CODE=:12, COLORS_ASSESSED=:13, F_GSM=:14, F_WIDTH=:15, COLOR=:16, STYLE=:17, CLAIMED=:18, CUS_ID=:19, SAMPLENO=:20, ENDUSE=:21, BATCHNO=:22, SPECNO=:23, BUYING_DIV=:24, STATUS=:25, COMPLETE_PASS=:26, APP_BY=:27, APPROVE_CODE=:28, APP_DATE=:29, ACTION_TAK=:30, TEST_BY=:31, remarks=:32, SCOLOR=:33, READY_DISP=:34 WHERE ROWID=:35
485jmv7xv085cSELECT QUALITY_REF, FIBER_COMPOSITION, AW_WT FROM UKFAB WHERE FAB_ID = :b1
4uw118z2skgz1SELECT WM_CONCAT(PROCESS_NAME) FROM ( SELECT DISTINCT P.PROCESS_NAME, FR.ORD FROM RND_SR_DTL D, PRODUCTION.SD_FINISH_ROUT FR, PRODUCTION.FINISH_ROUT P WHERE D.SUP_REC_NO=:B2 AND D.PK=FR.PK AND D.PK=:B1 AND FR.PROCESS_ID=P.PROCESS_ID ORDER BY ORD )
4xn0tw9j8rcnhSELECT SUM(SQTY) FROM UKPO P, UKPOHIT H WHERE P.PK = H.PK AND P.ORDERID = :b1 AND H.ENTRYTYPE = 'BOOKING' AND H.STATUS = 1 AND P.STATUS NOT IN ( 'CANCELLED' )
5fg74rb3ru4acSELECT PRINT_NAME FROM UKPRINTER WHERE PRINT_ID = :b1
5n4u7kgq4hwgm INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."MV_SHIPPING" select t.ccode, c.c_name, s.plan_no, 'BY- ' || e.ship_mode ship_mode, e.inv_date ship_date, t.pono, t.pl_no, trunc(t.pl_date) PL_DATE, m.style, nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) CMT_RATE, e.inv_no, e.inv_no_m, GetPL_Qty_PCS(t.pl_no) PCS, GetPL_Qty_PCS(t.pl_no)*nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) AMOUNT, get_cmt_contract_no(t.ccode, s.plan_no, g.garment_id)contract_no , g.garment_id , t.po_close from wpl_mst t, tblplanorder s, kcl_po_main m, expinv_mst e, dye.cdtl c, expinv_dtl_pl ed, tblplanpics g where t.pack_type = 'WP' and s.order_no = t.pono and e.inv_no = ed.inv_no and ed.pl_no = t.pl_no and m.pono = s.order_no AND E.CANCEL_STATUS='N' and e.approved=1 and g.plan_no=s.plan_no and t.ccode=c.c_code --and s.plan_no LIKE NVL(:planno, '%') --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(e.inv_date) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_date(sysdate, 'DD-MM-RRRR')) --and t.ccode=decode(:P_cCODE, 4, 15, NULL, T.CCODE, :P_CCODE) union all select t.ccode, c.c_name, s.plan_no, 'BY- ' || e.ship_mode ship_mode, e.inv_date ship_date, t.pono, t.pl_no, trunc(t.pl_date) PL_DATE, m.style, nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) CMT_RATE, e.inv_no, e.inv_no_m, PL.TOTAL_QTY PCS , PL.TOTAL_QTY*nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) AMOUNT, get_cmt_contract_no(t.ccode, s.plan_no, g.garment_id)contract_no , g.garment_id, t.po_close from wpl_mst t, wpl_dtl_aeopo pl, tblplanorder s, kcl_po_main m, expinv_mst e, dye.cdtl c, expinv_dtl_pl ed, tblplanpics g where t.pack_type = 'WP' and s.order_no = pl.pono and e.inv_no = ed.inv_no and ed.pl_no = t.pl_no and m.pono = s.order_no AND E.CANCEL_STATUS='N' -- and t.unit_12 LIKE NVL(:unit_12, '%') --and c.c_code = e.ccode and e.approved=1 and t.ccode=c.c_code --and s.plan_no LIKE NVL( :planno, '%') and pl.pl_no=t.pl_no and g.plan_no=s.plan_no --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(e.inv_date) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_date(sysdate, 'DD-MM-RRRR')) --and t.ccode=decode(:P_CCODE, 4, 15, NULL, T.CCODE, :P_CCODE) union all select m.ccode, c.c_name, m.plan_no, 'LOCAL' ship_mode, m.cdate ship_date, ' ' pono, ' ' pl_no, m.cdate PL_DATE, ' ' style, nvl(get_cmt_rate(m.ccode, m.plan_no, m.gmt_id), 0) CMT_RATE, 0 inv_no, ' ' inv_no_m, m.plan_qty PCS , m.plan_qty*nvl(get_cmt_rate(m.ccode, m.plan_no, m.gmt_id), 0) AMOUNT, get_cmt_contract_no(m.ccode, m.plan_no, m.gmt_id)contract_no , m.gmt_id garment_id, ' 'po_close from tblcontractmast m, dye.cdtl c where m.ccode=c.c_code and m.remarks='LOCALCMT' and m.sam_pcs is not null --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(m.cdate) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_d ate(sysdate, 'DD-MM-RRRR')) -- and m.ccode=decode(:P_CCODE, 4, 15, NULL, m.CCODE, :P_CCODE)
61jagxsr76cr0SELECT SMP_DESC FROM MKT_SMP WHERE SMP_ID = :b1
620nmfq8upvsbINSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PROCESSING"."MV_GSTK" SELECT DISTINCT A.PLANNO , ENTRYTYPE, NARRATION, B.COLOR, CORPORATE_SUP_NO , A.REC_DATE FROM ( /* QUERY FOR RND*/ SELECT DISTINCT A.PLAN_NO PLANNO, DECODE(A.ENTRY_TYPE, 'DHR', 'GLOT', 'DIR', 'RLOT', 'DFR', 'FLOT') ENTRYTYPE, E.NARRATION, NULL PLAN_NO, CORPORATE_SUP_NO , A.REC_DATE FROM PROCESSING.DYE_SR_DTL B, PROCESSING.DYE_SR_MST A, ENTRYTYPE E WHERE A.ENTRY_NO = B.ENTRY_NO AND A.ENTRY_TYPE IN ('DHR', 'DIR', 'DFR') AND E.STOCKTYPE='DYE' AND A.DEPT_CODE=3502 AND E.ENTRYTYPE=DECODE(A.ENTRY_TYPE, 'DHR', 'GLOT', 'DIR', 'RLOT', 'DFR', 'FLOT') ) A , ( select distinct sup_rec_no, color from rnd_sr_dtl ) b, ( select plan_no from PROCESSING.GSTK WHERE SUBSTR(PLAN_NO, 1, 1)='S' ) S where A.CORPORATE_SUP_NO = b.sup_rec_no AND S.PLAN_NO=A.PLANNO
671prjcayrc77SELECT NAME FROM UKAGENT WHERE AGENTID = :b1
6rf1xb3rsb3c9SELECT CCODE_VALIDATION(:b1) FROM PRODUCTION.TBLPLANSHEET WHERE PLAN_NO = :b2
6xuhp70u9mhz0 SELECT CCODE, DEPT, deptcode, round(SUM(MAKEUP)) MAKEUP FROM (SELECT CCODE, empcode, empname, fname, PRODUCTION.DESIGCODE_VALIDATION(desigcode) DESIGNATION, desigcode, HRM.DEPTCODE_VALIDATION(deptcode) DEPT, deptcode, DAYS WDAYS, EMP_UNIT_SAL, EMP_SAL, EMP_SAL*DAYS EMP_TOTAL, CASE WHEN ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL))<0 and EMP_UNIT_SAL>0 and DAYS>0 THEN 0 when ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL))>=0 and EMP_UNIT_SAL>0 and DAYS>0 THEN ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL)) ELSE (EMP_SAL*DAYS) END MAKEUP FROM ( SELECT DISTINCT w.ccode, w.empcode, w.empname, w.fname, decode(e.desigcode, NULL, w.desigcode, e.desigcode) desigcode, decode(e.deptcode, NULL, w.deptcode, e.deptcode)deptcode, (26-GET_EMP_ABS_datewise(w.empcode, :fdate, :tdate)) days, NVL(SUM (ROUND (amt, 2)) OVER (PARTITION B Y w.ccode, w.empcode, w.empname, w.fname ), 0)U_EMPSUM_sal, NVL(SUM (ROUND (amt, 2)) OVER (PARTITION BY w.ccode, w.empcode, w.empname, w.fname, decode(e.desigcode, NULL, w.desigcode, e.desigcode) , decode(e.deptcode, NULL, w.deptcode, e.deptcode) ), 0)emp_unit_sal, NVL(round(hrm.GET_EMPCODE_SALARY(w.empcode)/26, 0), 0)emp_sal FROM (select distinct t.empcode, e.ccode, e.empname, e.fname, e.desigcode, e.deptcode from daily_attendance t, tblhrmemployee e where t.dated BETWEEN :fdate AND :tdate and t.timein is not null and e.emptype IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) and e.empcode=t.empcode and e.deptcode!=1102 and decode(e.ccode, 4, 21, e.ccode)=:cc) w, (SELECT DISTINCT a.ccode, b.emp_code empcode, e.empname, e.fname, e.desigcode, SUM (ROUND ((a.stitched_qty / 12), 2) * get_plane_opration_rate(a.planno, b.opration_id)) amt, DECODE (a.unit_num, 2547, 2514, a.unit_num) deptcode FROM opr_wages_emp_master a, opr_wages_emp_d tl b, hrm.tblhrmemployee e WHERE a.opr_main_id = b.opr_main_id AND b.emp_code IS NOT NULL AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND e.empcode = b.emp_code AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND a.dated BETWEEN :fdate AND :tdate AND a.ccode = :cc --AND NVL (a.paid, 'N') = 'N' GROUP BY a.ccode, e.empname, e.fname, b.emp_code, e.desigcode, DECODE(a.unit_num, 2547, 2514, a.unit_num) UNION ALL SELECT DISTINCT a.ccode, md.empcode empcode, e.empname, e.fname, e.desigcode, SUM(ROUND ((a.stitched_qty / 12), 2)* get_plane_opration_rate(a.planno, opration)) amt, DECODE (a.unit_num, 2547, 2514, a.unit_num) deptcode FROM opr_wages_emp_master a, opr_wages_emp_m md, hrm.tblhrmemployee e WHERE a.opr_main_id = md.opr_main_id AND e.empcode = md.empcode AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND ddate BETWEEN :fdate AND :tdate AND a.ccode = :cc -- AND NVL (dpaid, 'N') = 'N' GROUP BY a.ccode, e.empname, e.f name, md.empcode, e.desigcode, DECODE(a.unit_num, 2547, 2514, a.unit_num) UNION ALL SELECT DISTINCT a.ccode, a.empcode empcode, e.empname, e.fname, e.desigcode, a.amt, a.unit_no FROM production.opr_wages_emp_add a, tblhrmemployee e WHERE a.empcode = e.empcode AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND a.dated BETWEEN :fdate AND :tdate AND a.ccode = :cc -- AND NVL (a.paid, 'N') = 'N' ) e where w.empcode=e.empcode(+) )) --WHERE MAKEUP>0 WHERE (:P_UNIT IS NULL OR deptcode=:P_UNIT) group by CCODE, DEPT, deptcode ORDER BY deptcode
73mzjsxgt8kdbSELECT NVL(SUM(H.QTY), 0) QTY FROM TBLPLANORDER O, KCL_PO_HITS H WHERE O.ORDER_NO=H.PONO AND O.PLAN_NO=:B2 AND H.GARID=:B1
742vw5aqd5947DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin PRODUCTION.POP_ACH_ALL; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
7dp2ntks3umauSELECT D.PLAN_NO, D.MS_PO, D.ITEM_CODE, SUBSTR(PRODUCTION.GET_YARN(D.ITEM_CODE), 1, 100)ITEM_DESC , D.FTHR_CODE, SUBSTR(PRODUCTION.GET_YARN(D.FTHR_CODE), 1, 100)FTHR_DESC , PROCESSING.GET_ST_STK(:P_TO, D.PK, :P_STK)STK FROM PROCESSING.ST_MST M, PROCESSING.ST_DTL D WHERE M.ENTRY_NO = D.ENTRY_NO AND TRUNC ( M.DATED ) <= : P_TO AND TO_NUMBER ( SUBSTR ( D.ENTRY_NO , 4 , 2 ) ) = : P_CCODE AND D.PLAN_NO = NVL ( : P_PLAN , D.PLAN_NO ) AND D.ITEM_CODE = NVL ( : P_ITEM , D.ITEM_CODE ) AND D.FTHR_CODE = NVL ( : P_FTHR , D.FTHR_CODE ) AND PROCESSING.GET_ST_STK ( : P_TO , D.PK , : P_STK ) <> 0 AND PROCESSING.GET_CONT_DATE ( D.MS_PO ) > '28-FEB-2014' ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC
7grsf923r1m4gselect distinct Z.ACT_SIZE S, Z.COL SIZ, ORD from planno_size_qty_view Z where plan_no = :1 and z.req_qty>0 ORDER BY ORD
8k4xz9kr1brktSELECT NVL(MAX(OPR_DTL_ID), 0) + 1 FROM OPR_WAGES_EMP_DTL
8r6x06b59nuyt /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."WAGES_SHEET_MV"("PLANNO", "OPRATION_ID", "DATED", "CCODE", "FLOW_ID", "UNIT_NUM", "EMPCODE", "EARN_AMT") SELECT "M"."PLANNO", "D"."OPRATION_ID", TRUNC("M"."DATED"), "M"."CCODE", "SMT"."FLOW_ID", "M"."UNIT_NUM", "D"."EMP_CODE", SUM(ROUND("M"."STITCHED_QTY"/12, 2)) FROM "OPR_WAGES_EMP_MASTER" "M", "OPR_WAGES_EMP_DTL" "D", "STIT_REC_ISSUE_M" "SMT", "STIT_REC_ISSUE_D" "SDT" WHERE "M"."OPR_MAIN_ID"="D"."OPR_MAIN_ID" AND "D"."EMP_CODE" IS NOT NULL AND "SMT"."DOC_ID"="SDT"."DOC_ID" AN D "SMT"."DOCUMENT_TYPE"='STR' AND "SDT"."SR#"="M"."CUT_BUNDLE_ID" AND TRUNC("M"."DATED")>'01-JUL-2013' GROUP BY "D"."EMP_CODE", "M"."UNIT_NUM", "SMT"."FLOW_ID", TRUNC("M"."DATED"), "M"."CCODE", "M"."PLANNO", "D"."OPRATION_ID"
932827s2cyx97DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin PRODUCTION.ACTIVITY_TABLE_DATA; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
95hp4vzj95d6b/* MV_REFRESH (DEL) */ delete from "PRODUCTION"."WAGES_SHEET_MV"
9pc5hdmqdgx9jUPDATE tblhrmemployee SET EMPCODE=:1, EMPNAME=:2, FNAME=:3, CCODE=:4, NIC=:5, OLDNIC=:6, EXM=:7, LATE_EXM=:8, GM_ATT=:9, DOB=:10, DEPTCODE=:11, DESIGCODE=:12, HOMEADDRESS=:13, CITY=:14, PERMANENTADDRESS=:15, REFRENCE=:16, CELLNO=:17, MOB_CARD_WEFDATE=:18, PERSONALEMAIL=:19, STATUS=:20, EMPTYPE=:21, PHONE=:22, NTNNO=:23, PROBOTIONPERIOD=:24, ENDPRODATE=:25, REASON=:26, LEAVINGDATE=:27, BGROUP=:28, RPLC_CODE=:29, RELIGION=:30, GENDER=:31, SSNO=:32, EOBINO=:33, OTALLOW=:34, RESTDAY=:35, SHIFT=:36, EMPCODE_OLD=:37, TEMPBLOCK=:38, NIGHTSHIFT=:39, HANDITYPE=:40, HANDICAP=:41, IM_BOSS=:42, INDEXDIS=:43, TOTALSALARY=:44, FORNIGHT=:45, SALMODE=:46, BANKID=:47, BRANCHID=:48, ACCOUNTNO=:49, MODIFYDATE=:50, MODIFYBY=:51, TEMPCODE=:52, MOB_CARD=:53, FUEL=:54, GUN_ALLOUNCE=:55, VEHICLE_MAIN=:56, CNIC_EXPIRY_DATE=:57, JOINIGDATE=:58, MOTHER_NAME=:59 WHERE ROWID=:60
a63g1ffphjkdcSELECT SUM(DECODE(NVL(DYS, 0), 1, 0, .75, .25, .50, .50, .25, .75, 0, 1, 0)) FROM ( SELECT NVL(A.EMPCODE, :B2 ) EMPCODE, P.DATED , NVL(STATUS, NVL( DECODE(RTRIM(TO_CHAR(P.DATED, 'DAY')), RTRIM(HRM.GET_EMP_REST(:B2 )), 'WR', NULL), 'A') ) STATUS, NVL(DYS, 0) DYS FROM PERIOD P, ( SELECT EMPCODE, DATED, STATUS, DYS FROM DAILY_ATTENDANCE WHERE EMPCODE=:B2 AND TO_CHAR(DATED, 'MMRRRR')=:B1 ) A WHERE TO_CHAR(P.DATED, 'mmrrrr')=:B1 AND P.DATED=A.DATED(+) ) WHERE STATUS NOT IN ('WR', 'GH') AND EMPCODE=:B2 AND TO_CHAR(DATED, 'MMRRRR')=:B1
a8bkyjcv4nuvxSELECT C.CUS_NAME || '-' || A.ACC_DESC , A.S1, A.S2, A.S3, A.S4, A.S5, A.S6, A.S7, A.S8, A.S9, A.S10, A.S11, A.S12, A.S13, A.S14, A.S15, A.S16, A.S17, A.S18, A.S19, A.S20 FROM ACC_SIZE A, CUSTOMER C WHERE A.CUS_ID = C.CUS_ID AND A.ACC_ID = :b1
ab21w6xh2m3r2DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PROCESSING.mv_gstk'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
axqbs2kdrnra7INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."MV_STITCHING" select distinct trunc(t.dated) dated, TO_CHAR(t.DATED, 'MON-RRRR') MTH, TO_CHAR(t.DATED, 'RRRR') YR, to_number(TO_CHAR(t.DATED, 'MM'))mt, C.CUS_NAME, P.PHASE_DESC, V.c_Name, t.planno, dept_code_validation(t.unit_num) unit, g.GARMANT_DESCRIPTION PRODUCT, nvl(GET_SAM(t.planno, t.garment_id), 0) SAM, sum(t.stitched_qty) Sth_qty from opr_wages_emp_master t, tblplanorder o, kcl_po_main m, GARMENT_TYPE g, CUSTOMER C, PHASE P, cdtl v where o.plan_no=t.planno and m.pono=o.order_no and t.garment_id=g.garment_id and c.cus_id=m.cus_id and p.phase_id=m.phase_id and v.c_code=t.ccode group by TO_CHAR(t.DATED, 'MON-RRRR'), TO_CHAR(t.DATED, 'RRRR') , to_number(TO_CHAR(t.DATED, 'MM')), C.CUS_NAME, P.PHASE_DESC, v.c_name, g.GARMANT_DESCRIPTION , t.planno, t.unit_num, t.garment_id, trunc(t.dated)
c6rkc2anypq67SELECT SUM(NVL(QTY_IN, 0)-NVL(QTY_OUT, 0)) FROM ST_MST A, ST_DTL B WHERE A.ENTRY_NO=B.ENTRY_NO AND TRUNC(A.DATED) BETWEEN '13-DEC-12' AND :B3 AND B.PO_NO=:B2 AND B.ITEM_CODE=:B1 AND A.ENTRY_TYPE IN ('RST', 'IFD')
cdy4c52fdcanvSELECT DISTINCT D.PO_NO, D.ITEM_CODE, SUBSTR(PRODUCTION.GET_YARN(D.ITEM_CODE), 1, 100)ITEM_DESC , PROCESSING.ST_YARN_STK(:P_TO, D.PO_NO, D.ITEM_CODE, :P_STK)STK FROM PROCESSING.ST_MST M, PROCESSING.ST_DTL D WHERE M.ENTRY_NO=D.ENTRY_NO AND TRUNC(M.DATED)<=:P_TO AND TO_NUMBER(SUBSTR(D.ENTRY_NO, 4, 2))=:P_CCODE AND D.ITEM_CODE=NVL(:P_ITEM, D.ITEM_CODE) AND PROCESSING.ST_YARN_STK(:P_TO, D.PO_NO, D.ITEM_CODE, :P_STK)<>0
ch0thvp9q6y1wSELECT DISTINCT T.PLAN_NO, T.PLAN_DATE, to_char(WM_CONCAT(DISTINCT ORDER_NO))PAKPO FROM TBLPLANSHEET T, tblplanorder o WHERE T.PLAN_DATE >='01-JAN-2011' and T.PLAN_NO=O.PLAN_NO GROUP BY T.PLAN_NO, T.PLAN_DATE /*AND UNIT=decode(:GLOBAL.COMPANY_CODE, 13, unit, :GLOBAL.COMPANY_CODE)*/ ORDER BY T.PLAN_DATE DESC
cm5vu20fhtnq1獥汥捴 ⨫⁣潮湥捴形祟晩汴敲楮朠⨯⁰物癩汥来⌬汥癥氠晲潭⁳祳慵瑨␠捯湮散琠批⁧牡湴敥⌽灲楯爠灲楶楬敧攣⁡湤⁰物癩汥来⌾〠獴慲琠睩瑨⁧牡湴敥⌽㨱⁡湤⁰物癩汥来⌾
d8h9553qm1h2jSELECT R.LOT#, DECODE(NVL(SUM(R.QTY), 0), 0, 1, NVL(SUM(R.QTY), 0)) SIZZ FROM (SELECT DISTINCT M.DOC_DATE, M.LOT#, B.PLAN_NO, B.JOB#, A.SIZ, B.QTY FROM CUT_PLAN_STS_VIEW B, ACC_SIZE_VIEW A, CUT_JBCARD_M M WHERE A.ACC_ID = B.ACC_ID AND B.COL = A.COL AND B.PLAN_NO = :b1 AND A.SIZ IS NOT NULL AND M.LOT# = :b2 AND M.PLAN_NO = B.PLAN_NO AND M.JOB# = B.JOB# UNION ALL SELECT T.DOC_DATE, T.LOT#, T.PLAN_NO, T.JOB#, D.FABRIC_CODE SIZ, NVL(SUM(D.QTY), 0) QTY FROM CUT_JBCARD_M T, CUT_JBCARD_D D WHERE D.DOC_ID = T.DOC_ID AND T.PLAN_NO = :b1 GROUP BY T.JOB#, T.PLAN_NO, D.FABRIC_CODE, D.USED_FAB, T.DOC_DATE, T.LOT# ) R WHERE R.SIZ NOT LIKE ('FAB%') AND R.LOT# = :b2 GROUP BY R.LOT#
f36gkv9w0n0yk SELECT T.PLAN_NO, PRODUCTION.GET_FABRIC_PERSON(PS.FAB_PER_ID) FABRIC_PERSON, CCODE_VALIDATION(PRODUCTION.GET_PLAN_CCODE(T.PLAN_NO) ) UNIT, CDK_ID, FABRIC, COLOR, MA, DW, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, STK FROM ( SELECT PLAN_NO, X.CDK_ID, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'FABRIC') FABRIC, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'FABRIC_CODE') FABRIC_CODE, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'COLOR') COLOR, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'MAIN_BODY') MA, X.RACK_NO, Y.DW, NVL(SUM(REC), 0)-NVL(SUM(ISU), 0) STK FROM ( SELECT PLAN_NO, D.RACK_NO, D.CDK_ID, NVL(SUM(D.REC_QTY), 0) REC, 0 ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DHR') AND M.REC_DATE <= :P_TODATE GROUP BY D.CDK_ID, D.RACK_NO, PLAN_NO UNION ALL SELECT PLAN_NO, D.RACK_NO, D.CDK_ID, 0 REC, NVL(SUM(D.REC_QTY), 0) ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D WHERE M.ENTRY_NO=D. ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DGR') AND M.REC_DATE <= :P_TODATE GROUP BY D.CDK_ID, D.RACK_NO, PLAN_NO UNION ALL SELECT PLANNO, D.RACKID, D.CDK_ID, 0 REC , NVL(SUM(QTY), 0) ISU FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' GROUP BY D.CDK_ID, D.RACKID, PLANNO ) X, ( SELECT DISTINCT C.CDK_ID, DECODE(F.PROCESS_ID, 7, 'DYE', PROCESS_NAME) DW FROM PRODUCTION.CDK_FINISH_ROUT C, PRODUCTION.Finish_rout F where f.PROCESS_ID=c.PROCESS_ID AND C.ORD=(select min(ord) from production.cdk_finish_rout x where x.cdk_id=C.CDK_ID) and C.PROCESS_ID in(2, 3, 7) ) Y WHERE X.CDK_ID=Y.CDK_ID AND 'KPL'=NVL(:P_KPL, 'KPL') GROUP BY X.CDK_ID, Y.DW, PLAN_NO, X.RACK_NO HAVING NVL(SUM(REC), 0)-NVL(SUM(ISU), 0) >:P_STK ) T, PRODUCTION.TBLPLANSHEET PS WHERE T.PLAN_NO = PS.PLAN_NO AND PS.DH_GS = 'Y' AND PS.FAB_HEAD_ID = NVL ( : P_FAB_ER_ID , PS.FAB_HEAD_ID ) AND PS.FAB_PER_ID = NVL ( : P_FAB_PER , PS.FAB_PER_ID ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND DW = NVL ( : P_DYEWASH , DW ) AND MA = NVL ( : P_MA , MA ) UNION ALL SELECT T.PLAN_NO, '' FABIC_PERSON, '' UNIT, T.CDK_ID, PRODUCTION.GET_FABRIC(T.FABRIC_CODE) FABRIC, T.COLOR, T.MA, PROCESSING.GET_DW(T.CDK_ID, 'C') DW, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, NVL(SUM(REC), 0)- NVL(SUM(ISU), 0) STK FROM ( SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , NVL(SUM(D.REC_QTY), 0) REC, 0 ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PRODUCTION.TBLDYECONTRACTDTL P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN('DHR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLAN_NO, D.CDK_ID, D.RAC K_NO , P.FABRIC_CODE, P.COLOR, P.MA UNION ALL SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , 0 REC, NVL(SUM(D.REC_QTY), 0) ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PRODUCTION.TBLDYECONTRACTDTL P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN('DGR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO, P.FABRIC_CODE, P.COLOR, P.MA UNION ALL SELECT M.PLANNO, D.RACKID, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , 0 REC , NVL(SUM(QTY), 0) ISU FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D, TBLDYECONTRACTDTL P WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' AND M.PLANNO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLANNO, D.CDK_ID, D.RACKID , P.FABRIC_CODE, P.COLOR, P.MA ) T WHERE T.FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) AND MA = NVL ( : P_MA , MA ) AND PROCESSING.GET_DW ( T.CDK_ID , 'C' ) = NVL ( : P_DYEWASH , PROCESSING.GET_DW ( T.CDK_ID , 'C' ) ) AND 'LS' = NVL ( : P_KPL , 'LS' ) GROUP BY T.PLAN_NO , CDK_ID , T.FABRIC_CODE , T.COLOR , T.MA , T.RACK_NO HAVING NVL ( SUM ( REC ) , 0 ) - NVL ( SUM ( ISU ) , 0 ) > : P_STK UNION ALL SELECT T.PLAN_NO, '' FABIC_PERSON, unit_name UNIT, T.CDK_ID, FABRIC, T.COLOR, T.MA, DYE_WASH, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, NVL(SUM(REC), 0)- NVL(SUM(ISU), 0) STK FROM ( SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, NVL(SUM(D.REC_QTY), 0) REC, 0 ISU, P.FABRIC_CODE, unit_name FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PROCESSING.RND_DYE P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DHR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.dem_no AND D.CDK_ID=P.sd_id AND CORPORATE_SUP_NO=sup_rec_no GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO , D.CDK_ID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name UNION ALL SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, 0 REC, NVL(SUM(D.REC_QTY), 0) ISU, P.FABRIC_CODE, unit_name FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PROCESSING.RND_DYE P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DGR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.dem_no AND CORPORATE_SUP_NO=sup_rec_no AND D.CDK_ID=P.sd_id GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO , D.CDK_ID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name UNION ALL SELECT M.PLANNO, D.RACKID, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, 0 REC , NVL(SUM(QTY), 0) ISU , P.FABRIC_CODE, unit_name FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D, PROCESSING.RND_DYE P WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' AND M.PLANNO=P.dem_no AND M.RND_SUPNO=P.sup_rec_no AND D.CDK_ID=P.sd_id GROUP BY M.PLANNO, D.CDK_ID, D.RACKID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name ) T WHERE T.FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND MA = NVL ( : P_MA , MA ) AND DYE_WASH = NVL ( : P_DYEWASH , DYE_WASH ) AND 'RND' = NVL ( : P_KPL , 'RND' ) GROUP BY T.PLAN_NO , CDK_ID , T.FABRIC_CODE , T.COLOR , T.MA , FABRIC , DYE_WASH , unit_name , RACK_NO HAVING NVL ( SUM ( REC ) , 0 ) - NVL ( SUM ( ISU ) , 0 ) <> : P_STK ORDER BY 1 ASC, 2 ASC, 3 ASC, 6 ASC, 10 ASC, 9 ASC , 1
f4032m9c04cv1SELECT DSC FROM UKPRODUCT WHERE PRODID = :b1
f56xhasgt6st2SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1), 0), NVL(SUM(C2), 0) FROM (SELECT /*+ NO_PARALLEL("MLOG$_PRODUCTION_ACHIEVEME") FULL("MLOG$_PRODUCTION_ACHIEVEME") NO_PARALLEL_INDEX("MLOG$_PRODUCTION_ACHIEVEME") */ 1 AS C1, 1 AS C2 FROM "PRODUCTION"."MLOG$_PRODUCTION_ACHIEVEME" SAMPLE BLOCK (0.007177 , 1) SEED (1) "MLOG$_PRODUCTION_ACHIEVEME") SAMPLESUB
f9utav8z8czs4 SELECT SUM(QTY) FROM (SELECT S1 QTY, 'S1' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S2 QTY, 'S2' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S3 QTY, 'S3' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S4 QTY, 'S4' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S5 QTY, 'S5' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S6 QTY, 'S6' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S7 QTY, 'S7' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S8 QTY, 'S8' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S9 QTY, 'S9' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S10 QTY, 'S10' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S11 QTY, 'S11' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S12 QTY, 'S12' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S13 QTY, 'S13' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S14 QTY, 'S14' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S15 QTY, 'S15' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S16 QTY, 'S16' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S17 QTY, 'S17' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S18 QTY, 'S18' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S19 QTY, 'S19' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S20 QTY, 'S20' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL ) M WHERE TYPE=:B2 AND SIZ=:B1 AND EXISTS (SELECT DISTINCT HITID FROM STITCHING_PLAN_MAIN B WHERE PLAN_NO=:B4 AND B.HITID=M.HITID AND GET_HIT_GAR(B.HITID)=:B3 )
fm1jr4dtpjyx2SELECT SUM(SQTY) FROM UKPO P, UKPOHIT H WHERE P.PK = H.PK AND P.ORDERID = :b1 AND H.ENTRYTYPE = 'BOOKING' AND H.STATUS = 1 AND P.STATUS NOT IN ( 'CANCELLED', 'LIABILITY' )
fys1zsgmvh9kaSELECT PHASE_DESC FROM PHASE WHERE PHASE_ID = :b1
g55xjd69zmcmm SELECT ALL E . EMPCODE , D . DATED ATT_DATE , D . TIMEIN , D . TIMEOUT , D . STATUS , D . WHOURS || ':' || D . WMIN TOTALTIME , DECODE ( D . STATUS , 'WR' , 0 , DYS ) DYS , HRM . DEPTCODE_VALIDATION ( E . DEPTCODE ) DEPARTMENT , E . EMPNAME , E . FNAME , TO_NUMBER ( TO_CHAR ( DATED , 'DD' ) ) DAY , HRM . DESIGCODE_VALIDATION ( E . DESIGCODE ) DESIGNATION , HRM . GET_OVERTIME ( E . EMPCODE , D . DATED ) OVERTIME , 26 - hrm . GET_EMP_ABS ( E . EMPCODE , to_char ( : P_FDATE , 'MMRRRR' ) ) TOT_DYS , case when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) < 0 or mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) < 0 ) and d . status not in ( 'WR' , 'GH' ) then '00:00' when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) = 0 AND mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) <= 40 ) and d . status not in ( 'WR' , 'GH' ) then '00:00' when d . status in ( 'WR' , 'GH' ) and nvl ( e . otallow , 0 ) = 1 then WHOURS || ':' || D . WMIN when nvl ( e . otallow , 0 ) = 1 then lpad ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) , 2 , 0 ) || ':' || lpad ( mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) , 2 , 0 ) else '00:00' end dif_time , case when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) < 0 or mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) < 0 ) and d . status not in ( 'WR' , 'GH' ) then 0 when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) = 0 AND mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) <= 40 ) and d . status not in ( 'WR' , 'GH' ) then 0 when d . status in ( 'WR' , 'GH' ) and nvl ( e . otallow , 0 ) = 1 then WHOURS * 60 + D . WMIN when nvl ( e . otallow , 0 ) = 1 then nvl ( ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) * 60 ) + ( mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) ) , 0 ) else 0 end tmint FROM DAILY_ATTENDANCE D , TBLHRMEMPLOYEE E WHERE ( E . EMPCODE = D . EMPCODE ) AND E . EMPCODE = NVL ( : P_EMPCODE , E . EMPCODE ) AND E . DEPTCODE = NVL ( : P_DEPTCODE , E . DEPTCODE ) AND E . SALMODE = NVL ( : P_SALMODE , E . SALMODE ) AND E . EMPTYPE = NVL ( : P_EMPTYPE , E . EMPTYPE ) AND E . STATUS = NVL ( : P_STATUS , E . STATUS ) AND E . CCODE = : P_CCODE AND DATED BETWEEN : P_FDATE AND : P_TDATE AND E.EMPCODE = D.EMPCODE ORDER BY 1 , 2

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Key Instance Activity Stats

StatisticTotalper Secondper Trans
db block changes264,699,21873,204.714,179.81
execute count51,149,74514,145.88807.70
logons cumulative1,7880.490.03
opened cursors cumulative50,810,48814,052.05802.34
parse count (total)1,052,755291.1516.62
parse time elapsed39,96511.050.63
physical reads3,369,251931.7953.20
physical writes2,684,236742.3542.39
redo size31,153,092,6568,615,639.49491,932.36
session cursor cache hits6,294,6991,740.8599.40
session logical reads1,961,266,316542,404.0530,969.97
user calls5,465,3211,511.4886.30
user commits58,26016.110.92
user rollbacks5,0681.400.08
workarea executions - onepass00.000.00
workarea executions - optimal2,972,645822.1146.94


Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

StatisticTotalper Secondper Trans
Batched IO (bound) vector count33,3539.220.53
Batched IO (full) vector count570.020.00
Batched IO block miss count144,54239.972.28
Batched IO buffer defrag count2650.070.00
Batched IO double miss count1,0980.300.02
Batched IO same unit count85,73123.711.35
Batched IO single block count28,2447.810.45
Batched IO slow jump count1,1360.310.02
Batched IO vector block count30,7258.500.49
Batched IO vector read count5,3911.490.09
Block Cleanout Optim referenced2290.060.00
CCursor + sql area evicted7,4262.050.12
CPU used by this session1,746,747483.0827.58
CPU used when call started993,681274.8115.69
CR blocks created229,69063.523.63
Cached Commit SCN referenced24,173,7446,685.44381.72
Commit SCN cached1,1820.330.02
DBWR checkpoint buffers written2,682,291741.8142.36
DBWR checkpoints210.010.00
DBWR object drop buffers written00.000.00
DBWR revisited being-written buffer130.000.00
DBWR tablespace checkpoint buffers written3890.110.01
DBWR transaction table writes5370.150.01
DBWR undo block writes1,710,515473.0627.01
Effective IO time00.000.00
HSC Heap Segment Block Changes16,763,9164,636.20264.72
HSC IDL Compressed Blocks00.000.00
Heap Segment Array Inserts193,86953.623.06
Heap Segment Array Updates2300.060.00
IMU CR rollbacks24,4096.750.39
IMU Flushes8,4442.340.13
IMU Redo allocation size27,246,8527,535.34430.25
IMU commits9,1932.540.15
IMU contention2730.080.00
IMU ktichg flush280.010.00
IMU pool not allocated43,03711.900.68
IMU recursive-transaction flush160.000.00
IMU undo allocation size66,161,40018,297.471,044.74
IMU- failed to get a private strand1,3910.380.02
LOB table id lookup cache misses00.000.00
Misses for writing mapping00.000.00
Number of read IOs issued16,9934.700.27
PX local messages recv'd00.000.00
PX local messages sent00.000.00
Requests to/from client5,466,8761,511.9186.33
RowCR - row contention340.010.00
RowCR attempts310,04785.754.90
RowCR hits309,99185.734.90
SMON posted for undo segment shrink80.000.00
SQL*Net roundtrips to/from client5,467,2201,512.0086.33
TBS Extension: bytes extended00.000.00
TBS Extension: files extended00.000.00
TBS Extension: tasks created60.000.00
TBS Extension: tasks executed60.000.00
active txn count during cleanout443,170122.567.00
background checkpoints completed100.000.00
background checkpoints started90.000.00
background timeouts31,0848.600.49
branch node splits20.000.00
buffer is not pinned count503,468,655139,238.327,950.17
buffer is pinned count2,718,379,468751,789.8142,925.40
bytes received via SQL*Net from client426,246,801117,882.006,730.78
bytes sent via SQL*Net to client3,204,400,299886,202.7950,600.06
calls to get snapshot scn: kcmgss61,945,00217,131.39978.16
calls to kcmgas372,010102.885.87
calls to kcmgcs4,763,4131,317.3675.22
cell physical IO interconnect bytes82,052,938,24022,692,403.041,295,681.82
change write time31,9678.840.50
cleanout - number of ktugct calls445,296123.157.03
cleanouts and rollbacks - consistent read gets227,64962.963.59
cleanouts only - consistent read gets7640.210.01
cluster key scan block gets56,08015.510.89
cluster key scans36,67910.140.58
commit batch performed40.000.00
commit batch requested40.000.00
commit batch/immediate performed810.020.00
commit batch/immediate requested810.020.00
commit cleanout failures: block lost170.000.00
commit cleanout failures: buffer being written2450.070.00
commit cleanout failures: callback failure2510.070.00
commit cleanout failures: cannot pin890.020.00
commit cleanouts915,410253.1614.46
commit cleanouts successfully completed914,808253.0014.45
commit immediate performed770.020.00
commit immediate requested770.020.00
commit txn count during cleanout8,8082.440.14
consistent changes11,492,6423,178.38181.48
consistent gets1,589,266,507439,524.4925,095.80
consistent gets - examination355,918,78898,432.225,620.24
consistent gets direct2,165,582598.9134.20
consistent gets from cache1,587,100,945438,925.5925,061.60
consistent gets from cache (fastpath)1,215,455,932336,144.1619,193.03
cursor authentications2,6880.740.04
data blocks consistent reads - undo records applied720,647199.3011.38
db block gets371,994,559102,878.105,874.09
db block gets direct1230.030.00
db block gets from cache371,994,415102,878.065,874.09
db block gets from cache (fastpath)108,212,66629,927.091,708.76
deferred (CURRENT) block cleanout applications236,66665.453.74
dirty buffers inspected1730.050.00
enqueue conversions26,5167.330.42
enqueue releases397,992110.076.28
enqueue requests398,449110.196.29
enqueue timeouts1990.060.00
enqueue waits5470.150.01
failed probes on index block reclamation7,6832.120.12
file io service time69,371,50019,185.251,095.43
free buffer inspected25,353,4937,011.71400.35
free buffer requested5,946,4011,644.5393.90
global undo segment hints helped540.010.00
global undo segment hints were stale10.000.00
heap block compress113,30931.341.79
hot buffers moved to head of LRU955,246264.1815.08
immediate (CR) block cleanout applications228,41363.173.61
immediate (CURRENT) block cleanout applications613,734169.739.69
index crx upgrade (positioned)5,5821.540.09
index crx upgrade (prefetch)3040.080.00
index fast full scans (full)39,21510.850.62
index fetch by key330,494,25091,400.865,218.77
index scans kdiixs171,994,32819,910.611,136.85
java call heap collected bytes00.000.00
java call heap collected count00.000.00
java call heap gc count00.000.00
java call heap object count00.000.00
java call heap object count max00.000.00
java call heap total size00.000.00
java call heap total size max00.000.00
java call heap used size00.000.00
java call heap used size max00.000.00
java session heap live size00.000.00
java session heap live size max00.000.00
java session heap used size00.000.00
java session heap used size max00.000.00
leaf node 90-10 splits2650.070.00
leaf node splits2,3320.640.04
lob reads2,689,321743.7542.47
lob writes2,689,881743.9142.48
lob writes unaligned2,689,877743.9142.48
logical read bytes from cache16,048,909,860,8644,438,455,694.39253,425,180.98
max cf enq hold time200.010.00
messages received338,55593.635.35
messages sent338,55593.635.35
min active SCN optimization applied on CR2,1680.600.03
no buffer to keep pinned count6,2651.730.10
no work - consistent read gets1,217,241,939336,638.0919,221.23
non-idle wait count6,793,7121,878.86107.28
parse count (describe)00.000.00
parse count (failures)11,4523.170.18
parse count (hard)45,84712.680.72
parse time cpu37,45010.360.59
physical read IO requests258,36871.454.08
physical read bytes27,600,904,1927,633,253.06435,840.45
physical read total IO requests267,73374.044.23
physical read total bytes27,749,549,0567,674,362.00438,187.67
physical read total multi block requests28,8817.990.46
physical reads cache1,203,671332.8819.01
physical reads cache prefetch1,000,183276.6115.79
physical reads direct2,165,580598.9134.20
physical reads direct (lob)210.010.00
physical reads direct temporary tablespace30.000.00
physical reads prefetch warmup00.000.00
physical write IO requests227,50762.923.59
physical write bytes21,989,261,3126,081,307.89347,228.10
physical write total IO requests316,85587.635.00
physical write total bytes54,303,389,18415,018,041.04857,494.14
physical write total multi block requests176,46748.802.79
physical writes direct1230.030.00
physical writes direct (lob)70.000.00
physical writes direct temporary tablespace80.000.00
physical writes from cache2,684,113742.3142.38
physical writes non checkpoint1,721,470476.0927.18
pinned buffers inspected7470.210.01
prefetch clients - default00.000.00
prefetch warmup blocks aged out before use00.000.00
prefetched blocks aged out before use9700.270.02
process last non-idle time3,6071.000.06
recovery blocks read00.000.00
recursive calls57,744,29215,969.65911.83
recursive cpu usage933,491258.1614.74
redo KB read00.000.00
redo blocks checksummed by FG (exclusive)1,749,386483.8127.62
redo blocks read for recovery00.000.00
redo blocks written62,976,53617,416.67994.45
redo buffer allocation retries2640.070.00
redo entries127,382,35935,228.622,011.47
redo k-bytes read for recovery00.000.00
redo log space requests3210.090.01
redo ordering marks6,4111.770.10
redo size for direct writes2600.070.00
redo subscn max counts516,914142.968.16
redo synch long waits340.010.00
redo synch time20,6605.710.33
redo synch time (usec)206,371,58357,073.733,258.77
redo synch time overhead (usec)76,875,25421,260.471,213.92
redo synch time overhead count (<128 msec)00.000.00
redo synch time overhead count (<2 msec)9,3572.590.15
redo synch time overhead count (<32 msec)10.000.00
redo synch time overhead count (<8 msec)00.000.00
redo synch time overhead count (>=128 msec)330.010.00
redo synch writes9,4012.600.15
redo wastage17,695,3164,893.78279.42
redo write info find9,3912.600.15
redo write info find fail00.000.00
redo write time78,60321.741.24
redo writes46,95612.990.74
rollback changes - undo records applied2,2990.640.04
rollbacks only - consistent read gets7,0041.940.11
root node splits00.000.00
rows fetched via callback89,235,35724,678.761,409.10
securefile direct read bytes00.000.00
securefile direct read ops00.000.00
session connect time00.000.00
shared hash latch upgrades - no wait8,658,6652,394.62136.73
shared hash latch upgrades - wait2,4650.680.04
shared io pool buffer get success00.000.00
sorts (disk)00.000.00
sorts (memory)5,070,6231,402.3280.07
sorts (rows)1,436,625,604397,310.4222,685.47
sql area evicted45,24112.510.71
sql area purged11,6543.220.18
summed dirty queue length2330.060.00
switch current to new buffer149,28141.282.36
table fetch by rowid1,500,549,444414,989.0723,694.88
table fetch continued row2,264,550626.2835.76
table scan blocks gotten898,920,017248,603.5914,194.67
table scan rows gotten85,775,828,53523,721,998.431,354,469.25
table scans (direct read)120.000.00
table scans (long tables)11,2153.100.18
table scans (rowid ranges)00.000.00
table scans (short tables)1,694,117468.5226.75
temp space allocated (bytes)108,003,32829,869.191,705.46
total cf enq hold time25,7607.120.41
total number of cf enq holders2010.060.00
total number of times SMON posted180.000.00
transaction rollbacks810.020.00
transaction tables consistent read rollbacks10.000.00
transaction tables consistent reads - undo records applied650.020.00
undo change vector size13,084,708,4403,618,681.84206,618.06
user logons cumulative1,1860.330.02
user logouts cumulative9320.260.01
write clones created in background10.000.00
write clones created in foreground18,8785.220.30


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

StatisticBegin ValueEnd Value
logons current1,0071,259
opened cursors current6,4988,044
session cursor cache count2,444,2572,523,640
session pga memory78,376,315,77680,503,412,728
session pga memory max5,339,392,340,7365,442,827,142,872
session uga memory34,006,440,15234,935,315,336
session uga memory max836,668,243,376859,816,340,848
workarea memory allocated344,656280,548


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

StatisticTotalper Hour
log switches (derived)98.96


Back to Instance Activity Statistics
Back to Top

IO Stats

Back to Top

IOStat by Function summary

Function NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Tm(ms)
LGWR3M0.06.001M30G23.608.51M94.3K16.22
DBWR0M0.000M20.5G62.945.802M225.2K52.50
Direct Reads16.5G4.704.679M0M0.000M17K4.24
Buffer Cache Reads9.2G66.772.601M0M0.000M209.9K2.90
Others139M2.53.038M60M1.12.017M13.2K14.16
Direct Writes0M0.000M1M0.000M1215.00
TOTAL:25.8G74.067.319M50.6G87.6614.328M559.7K25.41


Back to IO Stats
Back to Top

IOStat by Filetype summary

Filetype NameReads: DataReqs per secData per secWrites: DataReqs per secData per secSmall ReadLarge Read
Data File25.7G71.617.28M20.5G63.065.802M2.225.16
Log File0M0.000M30G23.558.508M2.83 
Control File137M2.43.038M58M1.03.016M3.35 
Temp File1M0.000M0M0.010M1.76 
TOTAL:25.8G74.057.318M50.6G87.6614.326M2.265.16


Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

Function/File NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Tm(ms)
LGWR 3M0.06.001M30G23.608.508M2183.35
LGWR (Log File) 0M0.000M30G23.558.507M364.61
LGWR (Control File) 3M0.05.001M3M0.05.001M1823.10
DBWR 0M0.000M20.5G62.935.8M0 
DBWR (Data File) 0M0.000M20.5G62.935.8M0 
Direct Reads 16.5G4.704.679M0M0.000M0 
Direct Reads (Data File) 16.5G4.704.679M0M0.000M0 
Buffer Cache Reads 9.2G66.772.601M0M0.000M197.2K2.26
Buffer Cache Reads (Data File) 9.2G66.772.601M0M0.000M197.2K2.26
Others 138M2.53.038M60M1.12.017M96613.67
Others (Control File) 134M2.38.037M56M0.98.015M86083.36
Others (Data File) 4M0.15.001M4M0.14.001M10536.20
Direct Writes 0M0.000M1M0.000M0 
Direct Writes (Data File) 0M0.000M1M0.000M0 
TOTAL: 25.8G74.057.319M50.6G87.6514.325M207K2.33


Back to IO Stats
Back to Top

Tablespace IO Stats

TablespaceReadsAv Rds/sAv Rd(ms)Av Blks/Rd 1-bk Rds/sAv 1-bk Rd(ms)WritesWrites avg/sBuffer WaitsAv Buf Wt(ms)
UNDOTBS1 000.000.00126,1500.00035591.69
ACC 93,655261.0324.2564817.411000.00
WAGES_REORG0 52806.461.3074,5930.1462100.00
HRM 62,694172.121.001,23817.342000.00
WAGES 45,785133.7620.4712,9717.81341430.00
PRODUCTION 21,96261.571.035,6606.0422340.29
DYE 14,82340.491.264294.030000.00
PICTURES 7,39822.221.00152.0520110.00
EXAMPLE 5,41811.316.332160.311000.00
SYSAUX 1,49307.165.071,9670.387100.00
SYSTEM 1,30104.001.021,4210.36401520.07
USERS 1,82717.632.136450.458052.00
PROCESSING 1,08503.331.338050.293000.00
ERP 19104.031.222250.054000.00
EXPIMP 12606.751.222800.037010.00
TEMP1 1502.001.00290.002000.00
TEMP 200.001.00120.000000.00


Back to IO Stats
Back to Top

File IO Stats

TablespaceFilenameReadsAv Rds/sAv Rd(ms)Av Blks/Rd 1-bk Rds/sAv 1-bk Rd(ms)WritesWrites avg/sBuffer WaitsAv Buf Wt(ms)
ACC/u01/app/oracle/oradata/klash/acc 93,655261.0324.25171.38648000.00
DYE/u01/app/oracle/oradata/klash/dye.dbf 6,68620.491.1420.47200000.00
DYE/u01/app/oracle/oradata/klash/dye02.dbf 1407.1460.6403.3313000.00
DYE/u01/app/oracle/oradata/klash/dye03.dbf 7,70320.401.1420.3995000.00
DYE/u01/app/oracle/oradata/klash/dye04.dbf 42002.053.5103.14121000.00
ERP/u01/app/oracle/oradata/klash/erp.dbf 13604.041.3104.53128000.00
ERP/u01/app/oracle/oradata/klash/erp1 5504.001.0004.0097000.00
EXAMPLE/u01/app/oracle/oradata/klash/example01.dbf 5,41811.316.3300.68216000.00
EXPIMP/u01/app/oracle/oradata/klash/expimp.dbf 12606.751.2206.86280010.00
HRM/u01/app/oracle/oradata/klash/hrm.dbf 33,08992.161.0092.16459000.00
HRM/u01/app/oracle/oradata/klash/hrm1.dbf 27,40082.051.0082.05126000.00
HRM/u01/app/oracle/oradata/klash/hrm2.dbf 2,20512.451.0012.45653000.00
PICTURES/u01/app/oracle/oradata/klash/PICTURES 2,05212.731.0012.7300110.00
PICTURES/u01/app/oracle/oradata/klash/PICTURES02.DBF 1,34502.011.0002.009000.00
PICTURES/u01/app/oracle/oradata/klash/PICTURES03.DBF 4,00112.031.0012.036000.00
PROCESSING/u01/app/oracle/oradata/klash/PROCESSING 1,08503.331.3303.27805000.00
PRODUCTION/u01/app/oracle/oradata/klash/production.dbf 3,87411.451.0411.441,156000.00
PRODUCTION/u01/app/oracle/oradata/klash/production02.dbf 92801.511.0501.48470000.00
PRODUCTION/u01/app/oracle/oradata/klash/production03.dbf 4,05611.281.0211.26817000.00
PRODUCTION/u01/app/oracle/oradata/klash/production05.dbf 3,32711.771.0111.77846000.00
PRODUCTION/u01/app/oracle/oradata/klash/production06.dbf 5,53521.931.0321.921,0570300.33
PRODUCTION/u01/app/oracle/oradata/klash/production07.dbf 9903.231.0003.33304000.00
PRODUCTION/u01/app/oracle/oradata/klash/production4.dbf 4,14311.281.0411.271,010040.00
SYSAUX/u01/app/oracle/oradata/klash/sysaux01.dbf 1,49307.165.0706.821,967100.00
SYSTEM/u01/app/oracle/oradata/klash/system01.dbf 1,30104.001.0204.011,42101520.07
TEMP/u01/app/oracle/oradata/klash/temp01.dbf 200.001.0000.001200 
TEMP1/u01/app/oracle/oradata/klash/temp01 1502.001.0002.002900 
UNDOTBS1/u01/app/oracle/oradata/klash/undotbs01.dbf 00  0 126,15035591.69
USERS/u01/app/oracle/oradata/klash/users01.dbf 1,82717.632.1308.12645052.00
WAGES/u01/app/oracle/oradata/klash/WAGES 2,98113.9234.1301.62658090.00
WAGES/u01/app/oracle/oradata/klash/WAGES03.DBF 6,06623.2919.1812.331,0020810.00
WAGES/u01/app/oracle/oradata/klash/WAGES04.DBF 5,87823.3520.6012.11803060.00
WAGES/u01/app/oracle/oradata/klash/WAGES05.DBF 4,85713.7224.7112.87897070.00
WAGES/u01/app/oracle/oradata/klash/wages02.dbf 3,59814.2128.4412.815930290.00
WAGES/u01/app/oracle/oradata/klash/wages06.dbf 5,14114.1123.3613.271,370050.00
WAGES/u01/app/oracle/oradata/klash/wages07.dbf 6,37523.3019.5312.251,438050.00
WAGES/u01/app/oracle/oradata/klash/wages08 4,88613.3925.5911.851,325010.00
WAGES/u01/app/oracle/oradata/klash/wages09.dbf 2,07114.451.0014.461,335000.00
WAGES/u01/app/oracle/oradata/klash/wages10.dbf 2,20014.601.0014.612,081100.00
WAGES/u01/app/oracle/oradata/klash/wages11 1,73205.401.0205.431,469000.00
WAGES_REORG0/u01/app/oracle/oradata/klash/WAGES03_reorg0.DBF 1705.881.0005.886,233200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/WAGES04_reorg0.DBF 8403.571.5103.387,315200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/WAGES05_reorg0.DBF 3407.061.7107.007,777200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/WAGES_reorg0 1107.271.0007.275,905200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages02_reorg0.dbf 5607.321.3007.367,044200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages06_reorg0.dbf 4808.131.9407.076,349200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages07_reorg0.dbf 10705.981.2605.986,616200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages08_reorg0 9307.851.0007.857,586200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages09_reorg0.dbf 2108.101.0008.106,892200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages10_reorg0.dbf 2109.051.0009.056,367200.00
WAGES_REORG0/u01/app/oracle/oradata/klash/wages11_reorg0 3604.441.0004.446,509200.00


Back to IO Stats
Back to Top

Buffer Pool Statistics

Back to Top

Buffer Pool Statistics

PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
D3,153,7001001,958,186,3601,203,6582,683,44000395


Back to Buffer Pool Statistics
Back to Top

Checkpoint Activity

MTTR WritesLog Size WritesLog Ckpt WritesOther Settings WritesAutotune Ckpt WritesThread Ckpt Writes
00002,681,7510


Back to Buffer Pool Statistics
Back to Top

Advisory Statistics

Back to Top

Instance Recovery Stats

Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual RedoBlksTarget RedoBlksLog Sz RedoBlksLog Ckpt Timeout RedoBlksLog Ckpt Interval RedoBlksOpt Log Sz(M)Estd RAC Avail Time
B047756602315687963237816812378168138071229   
E042248692513706483237816812378168135042031   


Back to Advisory Statistics
Back to Top

MTTR Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

PSize for Est (M)Size FactorBuffers (thousands)Est Phys Read FactorEstimated Phys Reads (thousands)Est Phys Read TimeEst %DBtime for Rds
D2,5600.1031511.30539,10311018864.00
D5,1200.206315.71272,3391471894.00
D7,6800.309463.92186,9281296768.00
D10,2400.401,2612.79133,3221186855.00
D12,8000.501,5771.9592,8711103915.00
D15,3600.601,8921.4971,179159439.00
D17,9200.702,2081.1554,827125910.00
D20,4800.802,5231.0851,639119374.00
D23,0400.902,8381.0248,718113384.00
D25,6001.003,1541.0047,712111321.00
D28,1601.103,4690.9947,00519873.00
D30,7201.203,7840.9746,34018510.00
D33,2801.304,1000.9746,05217919.00
D35,8401.404,4150.9645,86017525.00
D38,4001.504,7310.9645,69417185.00
D40,9601.605,0460.9545,35716493.00
D43,5201.705,3610.9545,11115988.00
D46,0801.805,6770.9444,78815325.00
D48,6401.905,9920.9344,39214515.00
D51,2002.006,3070.7133,82914475.00


Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
100.00708,0290


Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K4K2,608,5692,608,56900
64K128K1,5461,54600
128K256K57257200
256K512K1,5481,54800
512K1024K173,948173,94800
1M2M104,127104,12700
2M4M76,70176,70100
4M8M2,0352,03500
8M16M1,8151,81500
16M32M67867800
32M64M1,6951,69500
64M128M646400
128M256M202000
256M512M2200
512M1024M262600
1G2G141400


Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc CountEstd Time
3,5840.1311,881,704.58205,172.4298.00028,682,586,574
7,1680.2511,881,704.58205,172.4298.00028,682,586,574
14,3360.5011,881,704.58205,172.4298.00028,682,586,574
21,5040.7511,881,704.58205,172.4298.00028,682,586,574
28,6721.0011,881,704.581,412.59100.00028,199,057,293
34,4061.2011,881,704.581,403.02100.00028,199,034,580
40,1411.4011,881,704.581,403.02100.00028,199,034,580
45,8751.6011,881,704.581,403.02100.00028,199,034,580
51,6101.8011,881,704.581,403.02100.00028,199,034,580
57,3442.0011,881,704.581,403.02100.00028,199,034,580
86,0163.0011,881,704.581,403.02100.00028,199,034,580
114,6884.0011,881,704.581,403.02100.00028,199,034,580
172,0326.0011,881,704.581,403.02100.00028,199,034,580
229,3768.0011,881,704.581,403.02100.00028,199,034,580


Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits (K)
7,1680.5430488,3775,411,1420.731,997,009270.19476,511
8,7040.651,838165,0577,364,2731.0043,8785.94688,725
10,2400.773,373234,8827,376,6441.0031,5074.26690,631
10,7520.813,885258,7077,380,5781.0027,5733.73691,244
11,0080.834,141269,6497,382,5481.0025,6033.46691,549
11,2640.854,397281,2117,384,4971.0023,6543.20691,855
11,5200.874,653292,9757,386,4521.0021,6992.94692,159
11,7760.884,909305,9537,388,3731.0019,7782.68692,463
12,0320.905,165319,8897,390,3191.0017,8322.41692,766
12,2880.925,421333,2537,392,2401.0015,9112.15693,069
12,5440.945,676347,9387,394,1921.0013,9591.89693,372
12,8000.965,932362,5867,396,1091.0012,0421.63693,675
13,0560.986,188377,0697,398,0531.0010,0981.37693,978
13,3121.006,444391,5997,400,7601.007,3911.00694,475
13,5681.026,700406,9327,400,8381.007,3130.99694,483
13,8241.046,956419,5117,400,8401.007,3110.99694,484
14,0801.067,212430,8067,400,8431.007,3080.99694,485
14,3361.087,468442,2077,400,8441.007,3070.99694,486
14,5921.107,724453,5547,400,8451.007,3060.99694,487
14,8481.127,980463,7757,400,8481.007,3030.99694,488
15,1041.138,236473,9637,400,8531.007,2980.99694,490
15,3601.158,492485,2747,400,8591.007,2920.99694,493
15,6161.178,748496,0427,400,8621.007,2890.99694,497
16,3841.239,516528,4497,400,8681.007,2830.99694,506
17,9201.3511,052605,5407,400,8981.007,2530.98694,527
19,4561.4612,587681,2267,400,9481.007,2030.97694,573
20,9921.5814,123746,9297,401,4221.006,7290.91694,666
22,5281.6915,658825,0047,401,7421.006,4090.87694,727
24,0641.8117,194917,0867,401,7511.006,4000.87694,728
25,6001.9218,730980,1767,401,7521.006,3990.87694,728
27,1362.0419,164995,8057,401,7521.006,3990.87694,729


Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
16,1280.38934,859276,288,993
21,5040.50649,183135,222,204
26,8800.63521,64472,239,059
32,2560.75454,17555,645,211
37,6320.88438,81049,447,566
43,0081.00435,28447,710,890
48,3841.13433,32546,742,359
53,7601.25432,58546,379,756
59,1361.38430,80145,458,936
64,5121.50429,88634,342,299
69,8881.63429,32145,058,165
75,2641.75428,97234,342,299
80,6401.88428,97234,342,299
86,0162.00428,97234,342,299


Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Size for Est (MB)Size FactorEst Spill CountEst Spill Time (s)Est Unspill CountEst Unspill Time (s)
2560.500000
5121.000000
7681.500000
1,0242.000000
1,2802.500000
1,5363.000000
1,7923.500000
2,0484.000000
2,3044.500000
2,5605.000000
2,8165.500000
3,0726.000000
3,3286.500000
3,5847.000000
3,8407.500000
4,0968.000000
4,3528.500000
4,6089.000000
4,8649.500000
5,12010.000000


Back to Advisory Statistics
Back to Top

Java Pool Advisory

Java Pool Size(M)JP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits
2560.1439531.007,3911.0045
5120.2939531.007,3911.0045
7680.4339531.007,3911.0045
1,0240.5739531.007,3911.0045
1,2800.7139531.007,3911.0045
1,5360.8639531.007,3911.0045
1,7921.0039531.007,3911.0045
2,0481.1439531.007,3911.0045
2,3041.2939531.007,3911.0045
2,5601.4339531.007,3911.0045
2,8161.5739531.007,3911.0045
3,0721.7139531.007,3911.0045
3,3281.8639531.007,3911.0045
3,5842.0039531.007,3911.0045
3,8402.1439531.007,3911.0045
4,0962.2939531.007,3911.0045


Back to Advisory Statistics
Back to Top

Wait Statistics

Back to Top

Buffer Wait Statistics

ClassWaitsTotal Wait Time (s)Avg Time (ms)
file header block1507
data block33600
undo block2900
undo header1500


Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
TX-Transaction (row lock contention) 367203164203165814.88
KO-Multiple Object Checkpoint (fast object checkpoint) 1201200124315.00
MS-Materialized View Refresh Log 3,2993,2990111,280.00
JS-Job Scheduler (queue lock) 29,05329,053032801.43
SQ-Sequence Cache 4444440300.00


Back to Wait Statistics
Back to Top

Undo Statistics

Back to Top

Undo Segment Summary

Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
21,710.8175,82939,8082748.9/109.10/00/0/0/273/968031/0


Back to Undo Statistics
Back to Top

Undo Segment Stats

End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
08-Jan 11:5594,0706,5952,81426590/00/0/0/0/0/0
08-Jan 11:45370,1807,1002,21226490/00/0/0/30/119600/0
08-Jan 11:35356,72823,90439,808261080/00/0/0/57/246311/0
08-Jan 11:2589,6545,58339,205251080/00/0/0/26/95784/0
08-Jan 11:15370,8979,23638,604271010/00/0/0/39/101256/0
08-Jan 11:05429,28123,4119,260231090/00/0/0/121/405080/0


Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
AQ deq hash table latch10.00 00 
AQ dequeue txn counter latch3,1340.00 00 
ASM db client latch5,5380.00 00 
ASM map operation hash table10.00 00 
ASM network state latch570.00 00 
AWR Alerted Metric Element list94,8370.00 00 
Change Notification Hash table latch1,2020.00 00 
Consistent RBA47,0700.040.0000 
DML lock allocation275,9840.00 00 
Event Group Locks3,0580.00 00 
FOB s.o list latch5,0920.020.0000 
File State Object Pool Parent Latch10.00 00 
I/O Staticstics latch10.00 00 
IPC stats buffer allocation latch10.00 00 
In memory undo latch362,3710.000.09022,3750.01
JS Sh mem access6570.00 00 
JS mem alloc latch9210.00 00 
JS queue access latch9220.00 00 
JS queue state obj latch58,1060.00 00 
JS slv state obj latch2020.00 00 
KFC FX Hash Latch10.00 00 
KFC Hash Latch10.00 00 
KFCL LE Freelist10.00 00 
KGNFS-NFS:SHM structure10.00 00 
KGNFS-NFS:SVR LIST10.00 00 
KJC message pool free list10.00 00 
KJCT flow control latch10.00 00 
KMG MMAN ready and startup request latch1,2050.00 00 
KTF sga latch2540.00 01,2100.00
KWQMN job cache list latch210.00 00 
KWQP Prop Status4660.00 00 
KWQS pqsubs latch50.00 00 
KWQS pqueue ctx latch2970.00 00 
Locator state objects pool parent latch10.00 00 
Lsod array latch10.00 00 
MQL Tracking Latch0  0730.00
Memory Management Latch10.00 01,2050.00
Memory Queue5790.00 00 
Memory Queue Message Subscriber #110.00 00 
Memory Queue Message Subscriber #210.00 00 
Memory Queue Message Subscriber #310.00 00 
Memory Queue Message Subscriber #410.00 00 
Memory Queue Subscriber10.00 00 
MinActiveScn Latch410.00 00 
Mutex10.00 00 
Mutex Stats10.00 00 
OS process4,7360.00 00 
OS process allocation9,8140.00 00 
OS process: request allocation2,2860.00 00 
PL/SQL warning settings6,1200.00 00 
PX hash array latch10.00 00 
QMT10.00 00 
Real-time plan statistics latch46,4820.001.0000 
SGA IO buffer pool latch20.00 020.00
SGA blob parent10.00 00 
SGA bucket locks10.00 00 
SGA heap locks10.00 00 
SGA pool locks10.00 00 
SQL memory manager latch1210.00 01,1700.00
SQL memory manager workarea list latch12,400,7470.010.0000 
Shared B-Tree6,1570.080.0000 
Streams Generic10.00 00 
Testing10.00 00 
Token Manager10.00 00 
Undo Hint Latch0  02240.00
VPSO SGA780.00 00 
WCR: sync10.00 00 
Write State Object Pool Parent Latch10.00 00 
X$KSFQP10.00 00 
XDB NFS Security Latch10.00 00 
XDB unused session pool10.00 00 
XDB used session pool10.00 00 
active checkpoint queue latch297,2800.120.0000 
active service list22,8690.010.0003,3770.00
begin backup scn array50.00 00 
bq:time manger info latch1290.00 00 
buffer pool10.00 00 
bufq statistics5970.00 00 
business card10.00 00 
cache buffer handles82,1850.000.0000 
cache buffers chains4,005,953,8970.010.01011,366,007197.02
cache buffers lru chain5,559,8430.020.17019,434,7380.03
cache table scan latch36,3650.00 036,3650.00
call allocation74,7430.040.0300 
cas latch10.00 00 
change notification client cache latch10.00 00 
channel handle pool latch2,3220.00 00 
channel operations parent latch53,7250.00 00 
checkpoint queue latch8,068,3120.000.0002,604,0350.00
client/application info12,9320.00 00 
compile environment latch1,7880.00 00 
constraint object allocation8760.00 00 
corrupted undo seg latch6,9780.070.0000 
cp cmon/server latch10.00 00 
cp pool latch10.00 00 
cp server hash latch10.00 00 
cp sga latch570.00 00 
cvmap freelist lock10.00 00 
deferred cleanup latch570.00 00 
dml lock allocation570.00 00 
done queue latch10.00 00 
dummy allocation3,3250.00 00 
eighth spare latch - X parent10.00 00 
eleventh spare latch - children10.00 00 
enqueue freelist latch10.00 0356,0850.00
enqueue hash chains823,3860.010.000560.00
enqueues3350.300.0000 
fifteenth spare latch - children10.00 00 
file cache latch6,1860.00 00 
first Audit Vault latch1,1540.00 00 
flashback copy10.00 00 
fourteenth spare latch - children10.00 00 
fourth Audit Vault latch10.00 00 
gc element10.00 00 
gcs commit scn state10.00 00 
gcs partitioned table hash10.00 00 
gcs pcm hashed value bucket hash10.00 00 
gcs resource freelist10.00 00 
gcs resource hash10.00 00 
gcs resource scan list10.00 00 
gcs resource validate list10.00 00 
gcs shadows freelist10.00 00 
ges domain table10.00 00 
ges enqueue table freelist10.00 00 
ges group table10.00 00 
ges process hash list10.00 00 
ges process parent latch10.00 00 
ges resource hash list10.00 00 
ges resource scan list10.00 00 
ges resource table freelist10.00 00 
ges value block free list10.00 00 
global KZLD latch for mem in SGA1,1980.00 00 
global ctx hash table latch50.00 00 
global tx hash mapping10.00 00 
granule operation10.00 00 
hash table column usage latch7430.00 01,738,0900.00
hash table modification latch2720.00 00 
heartbeat check10.00 00 
interrupt manipulation30.00 00 
intra txn parallel recovery10.00 00 
io pool granule metadata list10.00 00 
job workq parent latch5180.00 052110.36
job_queue_processes free list latch1,9510.210.0000 
job_queue_processes parameter latch1,5650.00 00 
k2q lock allocation10.00 00 
kcbtsemkid latch90.00 00 
kdlx hb parent latch10.00 00 
kgb parent10.00 00 
kgnfs mount latch10.00 00 
kokc descriptor allocation latch160.00 00 
ksfv messages10.00 00 
ksim group membership cache10.00 00 
kss move lock350.00 00 
ksuosstats global area3640.00 00 
ksv allocation latch1260.00 00 
ksv class latch710.00 00 
ksv msg queue latch10.00 00 
ksz_so allocation latch2,2860.00 00 
ktm global data1020.00 00 
kwqbsn:qsga2,4520.240.0000 
lgwr LWN SCN47,2410.210.0000 
list of block allocation72,5190.010.0000 
loader state object freelist620.00 00 
lob segment dispenser latch10.00 00 
lob segment hash table latch130.00 00 
lob segment query latch10.00 00 
lock DBA buffer during media recovery10.00 00 
logical standby cache10.00 00 
logminer context allocation20.00 00 
logminer local10.00 00 
logminer work area10.00 00 
longop free list parent570.00 0560.00
mapped buffers lru chain10.00 00 
message pool operations parent latch9,6720.00 00 
messages827,2350.180.0000 
mostly latch-free SCN53,1966.920.0000 
msg queue latch10.00 00 
multiblock read objects137,3440.00 00 
name-service namespace bucket10.00 00 
ncodef allocation latch570.00 00 
nineth spare latch - X parent10.00 00 
object queue header heap3,734,1380.000.0002,527,3580.00
object queue header operation23,488,7630.010.0000 
object stats modification5770.00 00 
parallel query alloc buffer1,4050.00 00 
parallel query stats10.00 00 
parameter list1160.00 00 
parameter table management30,5330.400.0100 
peshm10.00 00 
pesom_free_list10.00 00 
pesom_hash_node10.00 00 
post/wait queue23,2570.090.00015,6050.04
process allocation2,3690.041.0001,2690.00
process group creation2,2860.00 00 
process queue10.00 00 
process queue reference10.00 00 
qmn task queue latch3,9063.050.0000 
query server freelists10.00 00 
queued dump request120.00 00 
queuing load statistics10.00 00 
recovery domain hash list10.00 00 
redo allocation272,7041.880.000127,369,6260.05
redo copy10.00 0127,376,9870.00
redo writing469,5280.160.0000 
resmgr group change latch2,1850.00 00 
resmgr:active threads3,3250.00 00 
resmgr:actses change group1,6890.00 00 
resmgr:actses change state10.00 00 
resmgr:free threads list3,3240.00 00 
resmgr:plan CPU method10.00 00 
resmgr:resource group CPU method10.00 00 
resmgr:schema config210.00 00 
resmgr:session queuing10.00 00 
rm cas latch10.00 00 
row cache objects54,159,4050.420.0002290.44
rules engine rule set statistics1000.00 00 
second Audit Vault latch10.00 00 
sequence cache32,7120.060.0000 
session allocation136,0790.00 0133,0380.00
session idle bit11,062,1980.000.0000 
session queue latch10.00 00 
session state list latch5,5220.510.6100 
session switching1,0750.00 00 
session timer1,2040.00 00 
seventh spare latch - X parent10.00 00 
shared pool2,980,2590.010.1801420.00
shared pool sim alloc1,2590.00 00 
shared pool simulator131,5730.00 00 
sim partition latch10.00 00 
simulator hash latch128,740,2600.000.0000 
simulator lru latch2,060,4180.050.010126,543,3690.25
sixth spare latch - X parent10.00 00 
sort extent pool1,3450.00 00 
space background state object latch50.00 00 
space background task latch4,5730.280.0002,4140.00
state object free list20.00 00 
statistics aggregation5600.00 00 
tablespace key chain10.00 00 
temp lob duration state obj allocation3420.00 00 
temporary table state object allocation50.00 00 
tenth spare latch - X parent10.00 00 
test excl. parent l010.00 00 
test excl. parent2 l010.00 00 
thirteenth spare latch - children10.00 00 
threshold alerts latch2690.00 00 
trace latch2350.00 00 
transaction allocation10,9790.00 00 
twelfth spare latch - children10.00 00 
twenty-fifth spare latch - S par10.00 00 
twenty-first spare latch - S par10.00 00 
twenty-fourth spare latch - S par10.00 00 
twenty-second spare latch - S par10.00 00 
twenty-third spare latch - S par10.00 00 
undo global data2,711,0230.010.0100 
virtual circuit buffers10.00 00 
virtual circuit holder10.00 00 
virtual circuit queues10.00 00 
write info latch0  046,9810.00


Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch NameGet RequestsMissesSleepsSpin Gets
cache buffers chains4,005,953,897473,2942,896470,493
row cache objects54,159,405226,07081225,990
redo allocation272,7045,12655,121
mostly latch-free SCN53,1963,68123,679
object queue header operation23,488,7632,70162,695
simulator hash latch128,740,2601,32621,324
simulator lru latch2,060,4181,11971,113
cache buffers lru chain5,559,843899157745
shared pool2,980,25942575354
undo global data2,711,0234004397
parameter table management30,5331231122
call allocation74,74332131
session state list latch5,522281711
In memory undo latch362,37111110
Real-time plan statistics latch46,482220
process allocation2,369110


Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch NameWhereNoWait Misses SleepsWaiter Sleeps
In memory undo latchkticmt: child010
Real-time plan statistics latchkeswxAddNewPlanEntry022
cache buffers chainskcbchg1: mod cr pin02,219653
cache buffers chainskcbgtcr: fast path (cr pin)0331784
cache buffers chainskcbivbr02168
cache buffers chainskcbnew: new latch again02146
cache buffers chainskcbzgb: scan from tail. nowait0870
cache buffers chainskcbgtcr: kslbegin excl07340
cache buffers chainskcbgcur_203812
cache buffers chainskcbgtcr: fast path074
cache buffers chainskcbzibmlt: multi-block read: nowait070
cache buffers chainskcbgtcr_2041
cache buffers chainskcbbxsv011
cache buffers chainskcbrls: fast release011,082
cache buffers chainskcbrls_201220
cache buffers chainskcbzwb0116
cache buffers lru chainkcbzgws0810
cache buffers lru chainkcbo_link_q06810
cache buffers lru chainkcbzgb0714
cache buffers lru chainkcbbic201130
call allocationksuxds010
lgwr LWN SCNkcs023010
mostly latch-free SCNkcslcu3012
object queue header operationkcbo_unlink_q073
object queue header operationkcbo_switch_cq022
parameter table managementksp_param_table_free010
process allocationksucrp:1010
redo allocationkcrfw_redo_gen: redo allocation 1050
row cache objectskqrpre: find obj03344
row cache objectskqreqd: reget0293
row cache objectskqreqd01031
row cache objectskqrso083
row cache objectskqrplk011
session state list latchkpseqd0130
session state list latchkpscad0410
shared poolkghalo06731
shared poolkghalp068
shared poolkghupr10228
simulator hash latchkcbsacc: lookup dba022
simulator lru latchkcbs_simulate: simulate set070
undo global dataktusm_stealext_2030
undo global dataktusmupst: KSLBEGIN014


Back to Latch Statistics
Back to Top

Mutex Sleep Summary

Mutex TypeLocationSleepsWait Time (ms)
Library Cachekglpndl1 951,2750
Library Cachekglpnal1 904560
Library Cachekglhdgn1 623120
Library Cachekglpin1 42830
Cursor Pinkksfbc [KKSCHLFSP2]1070
Cursor PinkksLockDelete [KKSCHLPIN6]540
Library Cachekglhdgn2 106520
Library Cachekgllkdl1 85500
Library Cachekglget2 2470
Library Cachekgllkc1 57360
Library Cachekglget1 1260
Library Cachekglhdgh1 6450
Library Cachekgldtin1 4210
Library Cachekglobpn1 7110


Back to Latch Statistics
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Segment Statistics

Back to Top

Segments by Logical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
PRODUCTIONWAGESPRODUCTION_ACHIEVEMENTS_DTL TABLE317,405,55216.18
EXPIMPEXPIMPWAREHOUSE_REC TABLE152,349,1047.77
PRODUCTIONPRODUCTIONKCL_PO_HITS TABLE118,768,7686.06
PRODUCTIONPRODUCTIONSTORE_DETAIL TABLE91,801,4244.68
PRODUCTIONWAGES_REORG0I_SNAP$_WAGES_SHEET_MV INDEX64,331,5203.28


Back to Segment Statistics
Back to Top

Segments by Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
PRODUCTIONACCCUT_SUPLY_BUNDL TABLE2,270,80267.40
PRODUCTIONWAGESOPR_WAGES_EMP_DTL TABLE909,79027.00
HRMHRMDAILY_ATTENDANCE TABLE20,7520.62
PRODUCTIONPRODUCTIONTBLPLANPICS TABLE9,9430.30
PRODUCTIONWAGESEMPCODE_NR INDEX9,5840.28


Back to Segment Statistics
Back to Top

Segments by Physical Read Requests

OwnerTablespace NameObject NameSubobject NameObj. TypePhys Read Requests%Total
PRODUCTIONACCCUT_SUPLY_BUNDL TABLE93,51536.19
HRMHRMDAILY_ATTENDANCE TABLE20,7528.03
PRODUCTIONWAGESOPR_WAGES_EMP_DTL TABLE19,6717.61
PRODUCTIONPRODUCTIONTBLPLANPICS TABLE9,9433.85
PRODUCTIONWAGESEMPCODE_NR INDEX9,5843.71


Back to Segment Statistics
Back to Top

Segments by UnOptimized Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeUnOptimized Reads%Total
PRODUCTIONACCCUT_SUPLY_BUNDL TABLE93,51536.19
HRMHRMDAILY_ATTENDANCE TABLE20,7528.03
PRODUCTIONWAGESOPR_WAGES_EMP_DTL TABLE19,6717.61
PRODUCTIONPRODUCTIONTBLPLANPICS TABLE9,9433.85
PRODUCTIONWAGESEMPCODE_NR INDEX9,5843.71


Back to Segment Statistics
Back to Top

Segments by Optimized Reads

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Direct Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeDirect Reads%Total
PRODUCTIONACCCUT_SUPLY_BUNDL TABLE2,165,556100.00


Back to Segment Statistics
Back to Top

Segments by Physical Writes

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Writes%Total
PRODUCTIONWAGES_REORG0I_SNAP$_WAGES_SHEET_MV INDEX176,3126.57
PRODUCTIONWAGES_REORG0PLAN_WGS2 INDEX166,1226.19
PRODUCTIONWAGES_REORG0DATED_WGS2 INDEX157,9725.89
PRODUCTIONWAGES_REORG0OPRATION_ID_WGS2 INDEX129,5974.83
PRODUCTIONWAGES_REORG0UNIT_NUM_WGS2 INDEX107,1103.99


Back to Segment Statistics
Back to Top

Segments by Physical Write Requests

OwnerTablespace NameObject NameSubobject NameObj. TypePhys Write Requests%Total
PRODUCTIONWAGES_REORG0DATED_WGS2 INDEX16,3537.19
PRODUCTIONWAGES_REORG0I_SNAP$_WAGES_SHEET_MV INDEX13,7286.03
PRODUCTIONWAGES_REORG0PLAN_WGS2 INDEX12,1495.34
PRODUCTIONWAGES_REORG0OPRATION_ID_WGS2 INDEX9,9464.37
PRODUCTIONWAGES_REORG0UNIT_NUM_WGS2 INDEX8,4563.72


Back to Segment Statistics
Back to Top

Segments by Direct Physical Writes

OwnerTablespace NameObject NameSubobject NameObj. TypeDirect Writes%Total
SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_1701927951_0TABLE PARTITION11593.50


Back to Segment Statistics
Back to Top

Segments by Table Scans

OwnerTablespace NameObject NameSubobject NameObj. TypeTable Scans%Total
PRODUCTIONUSERSMLOG$_PRODUCTION_ACHIEVEME TABLE11,21022.23
PRODUCTIONWAGESPKSUPRECNO INDEX440.09
PRODUCTIONPRODUCTIONITEM_PK INDEX270.05
PRODUCTIONPRODUCTIONBN_PK INDEX80.02
PRODUCTIONPRODUCTIONPLAN_NO INDEX80.02


Back to Segment Statistics
Back to Top

Segments by DB Blocks Changes

OwnerTablespace NameObject NameSubobject NameObj. TypeDB Block Changes% of Capture
PRODUCTIONWAGES_REORG0I_SNAP$_WAGES_SHEET_MV INDEX21,591,32818.60
PRODUCTIONWAGES_REORG0OPRATION_ID_WGS2 INDEX21,370,48018.41
PRODUCTIONWAGES_REORG0DATED_WGS2 INDEX17,749,04015.29
PRODUCTIONWAGES_REORG0PLAN_WGS2 INDEX14,502,19212.49
PRODUCTIONWAGES_REORG0UNIT_NUM_WGS2 INDEX14,008,88012.07


Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
PRODUCTIONPRODUCTIONOPR_WAGES_EMP_MASTER_PK INDEX17333.27
PROCESSINGPROCESSINGLAB_MST TABLE11822.69
PRODUCTIONWAGESEMPCODE_NR INDEX489.23
PRODUCTIONWAGESOPR_WAGES_EMP_DTL TABLE387.31
PRODUCTIONWAGES_REORG0I_SNAP$_WAGES_SHEET_MV INDEX326.15


Back to Segment Statistics
Back to Top

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
PRODUCTIONWAGESPRODUCTION_ACHIEVEMENTS_DTL TABLE14343.87
SYSSYSTEMI_SCHEDULER_JOB4 INDEX10030.67
SYSSYSTEMDBMS_LOCK_ALLOCATED TABLE319.51
PRODUCTIONPRODUCTIONOPR_WAGES_EMP_MASTER_PK INDEX309.20
SYSSYSTEMSNAP_LOADERTIME$ TABLE154.60


Back to Segment Statistics
Back to Top

Dictionary Cache Stats

CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control650.000 21
dc_files500.000 050
dc_global_oids4,2700.000 0504
dc_histogram_data657,8760.000 013,244
dc_histogram_defs421,7760.010 013,882
dc_object_grants2,198,2600.010 010,634
dc_objects1,296,4690.060 86233,043
dc_profiles5,3670.000 05
dc_rollback_segments62,6810.000 0111
dc_segments558,6210.000 76,097
dc_sequences6630.000 66316
dc_table_scns20,8980.010 64570
dc_tablespaces6,490,1110.000 019
dc_users8,952,6420.000 01,744
global database name4,9670.000 02
outstanding_alerts920.000 016
qmtmrciq_cache_entries50.000 088
qmtmrctn_cache_entries50.000 01
qmtmrctq_cache_entries50.000 01
sch_lj_oids9090.000 065



Back to Top

Library Cache Activity

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
ACCOUNT_STATUS3,5360.820 00
APP CONTEXT0 2,4360.0000
BODY12,4430.021,336,6530.0000
CLUSTER880.00880.0000
DBLINK3,5280.000 00
EDITION1,6860.003,3570.0000
HINTSET OBJECT825.00850.0000
INDEX742.86742.8600
JAVA DATA742.86742.8600
JAVA RESOURCE742.86742.8600
JAVA SOURCE742.86742.8600
OBJECT ID1100.000 00
PIPE2100.002100.0000
QUEUE1,1860.0013,1970.0000
SCHEMA35,8540.020 00
SECURITY CLASS3810.003810.0000
SQL AREA146,13319.3052,007,3900.2314,53111,650
SQL AREA BUILD45,38974.720 00
SQL AREA STATS45,55169.8645,55169.8600
SUBSCRIPTION130.00130.0000
SUMMARY3,8170.003,4141.8563201
TABLE/PROCEDURE531,5220.194,422,7800.023140
TRIGGER2,1190.1916,8490.11150
XDB ACL400.00400.0000
XDB CONFIG120.00120.0000
XML SCHEMA600.001080.0000



Back to Top

Memory Statistics

Back to Top

Memory Dynamic Components

ComponentBegin Snap Size (Mb)Current Size (Mb)Min Size (Mb)Max Size (Mb)Oper CountLast Op Typ/Mod
ASM Buffer Cache0.000.000.000.000STA/
DEFAULT 16K buffer cache0.000.000.000.000STA/
DEFAULT 2K buffer cache0.000.000.000.000STA/
DEFAULT 32K buffer cache0.000.000.000.000STA/
DEFAULT 4K buffer cache0.000.000.000.000STA/
DEFAULT 8K buffer cache0.000.000.000.000STA/
DEFAULT buffer cache25,600.0025,600.0025,600.0025,600.000INI/
KEEP buffer cache0.000.000.000.000STA/
PGA Target28,672.0028,672.0028,672.0028,672.000STA/
RECYCLE buffer cache0.000.000.000.000STA/
SGA Target43,008.0043,008.0043,008.0043,008.000STA/
Shared IO Pool256.00256.00256.00256.000STA/
java pool1,792.001,792.001,792.001,792.000STA/
large pool1,024.001,024.001,024.001,024.000STA/
shared pool13,312.0013,312.0013,312.0013,312.000STA/
streams pool512.00512.00512.00512.000STA/


Back to Memory Statistics
Back to Top

Memory Resize Operations Summary

No data exists for this section of the report.

Back to Memory Statistics
Back to Top

Memory Resize Ops

No data exists for this section of the report.

Back to Memory Statistics
Back to Top

Process Memory Summary

CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
BOther2,012.33 1.992.40611391,0091,009
Freeable1,072.630.001.126.09184 956956
SQL369.88284.680.387.48229460984973
PL/SQL80.8532.980.080.18351,0071,007
EOther2,459.79 1.951.88461391,2611,261
Freeable1,269.500.001.056.25217 1,2051,205
SQL294.47248.920.246.052114601,2371,219
PL/SQL98.2840.100.080.16351,2591,259


Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers27,111,981,056 
Fixed Size2,262,656 
Redo Buffers151,117,824 
Variable Size58,250,496,384 

Back to Memory Statistics
Back to Top

SGA breakdown difference

PoolNameBegin MBEnd MB% Diff
javafree memory1,783.141,783.140.00
largefree memory1,016.191,016.190.00
sharedKGLDA154.58154.58-0.00
sharedKGLH03,348.013,357.500.28
sharedKGLHD477.96471.25-1.40
sharedSQLA5,108.095,064.46-0.85
shareddb_block_hash_buckets178.00178.000.00
sharedfree memory3,094.613,107.870.43
streamsfree memory509.30509.300.00
 buffer_cache25,600.0025,600.000.00
 fixed_sga2.162.160.00
 log_buffer144.12144.120.00
 shared_io_pool256.00256.000.00


Back to Memory Statistics
Back to Top

Streams Statistics

Back to Top

Streams CPU/IO Usage

Session TypeFirst LogonCPU time(s)User IO Wait time(s)SYS IO Wait time(s)
QMON Slaves0106 15:57:080.170.000.00
QMON Coordinator0106 15:56:580.060.000.00


Back to Streams Statistics
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Capture Rate

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Apply

Apply NameCoord Txns RcvdCoord Txns AppliedCoord Txns RollbkdCoord Wait Deps%Coord Wait Comit%Server Mesgs AppliedServer Dequeue Time(s)Server Apply Time(s)Reader Dequeue MesgsReader Lag (s)
STREAMS_APPLY 0000.000.0000.000.000 


Back to Streams Statistics
Back to Top

Streams Apply Rate

Apply NameCoord Txns Rcvd/secCoord Txns Appl/secCoord Txns Rbk/secServer Msgs Appl/secServer Dequeue Time/msgServer Apply Time/msgReader Dequeue Msgs/sec
STREAMS_APPLY 0.000.000.000.00  0.00


Back to Streams Statistics
Back to Top

Buffered Queues

Queue NameEnq MsgsEnq Msgs/secDeq MsgsDeq Msgs/secSpill MsgsSpill Msgs/sec%Spill Msgs
STRMADMIN.STREAMS_APPLY_Q(94826) 00.0000.0000.00 


Back to Streams Statistics
Back to Top

Buffered Queue Subscribers

Subscriber/QueueEnq MsgsEnq Msgs/secDeq MsgsDeq Msgs/secSpill MsgsSpill Msgs/sec%Spill Msgs
STREAMS_APPLY(101)/STRMADMIN.STREAMS_APPLY_Q 00.0000.0000.00 


Back to Streams Statistics
Back to Top

Rule Set

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Persistent Queues

Queue NameEnq MsgsDeq Msgs%Exp Msgs%Delay MsgsEnq Time(s)Deq Time(s)%Transf Time%Eval Time
SYS.ALERT_QUE(13069) 00  0.000.00  
SYSMAN.MGMT_NOTIFY_Q(110503) 00  0.000.00  
SYSMAN.MGMT_TASK_Q(110173) 61610.000.000.010.030.000.00


Back to Streams Statistics
Back to Top

Persistent Queues Rate

Queue NameEnqueue Msgs/secDequeue Msgs/secAvg Enqueue sec / msgAvg Dequeue sec / msg
SYS.ALERT_QUE(13069) 0.000.00  
SYSMAN.MGMT_NOTIFY_Q(110503) 0.000.00  
SYSMAN.MGMT_TASK_Q(110173) 0.020.020.000.00


Back to Streams Statistics
Back to Top

Persistent Queue Subscribers

Subscriber/QueueEnqueue MsgsDequeue MsgsExpire MsgsEnqueue Msgs/secDequeue Msgs/secExpire Msgs/sec
HAE_SUB(1)/SYS.ALERT_QUE 000   
ORADB11_3938_KLASH(41)/SYS.ALERT_QUE 0000.000.000.00


Back to Streams Statistics
Back to Top

Resource Limit Stats

No data exists for this section of the report.


Back to Top

Shared Server Statistics

Back to Top

Shared Servers Activity

Avg Total ConnectionsAvg Active ConnectionsAvg Total Shared SrvrsAvg Active Shared SrvrsAvg Total DispatchersAvg Active Dispatchers
001010


Back to Shared Server Statistics
Back to Top

Shared Servers Rates

Common Queue Per SecDisp Queue Per SecServer Msgs/SecServer KB/SecCommon Queue TotalDisp Queue TotalServer Total MsgsServer Total(KB)
0000.000000


Back to Shared Server Statistics
Back to Top

Shared Servers Utilization

Total Server Time (s)%Busy%IdleIncoming Net %Outgoing Net %
3,6160.00100.000.000.00


Back to Shared Server Statistics
Back to Top

Shared Servers Common Queue

No data exists for this section of the report.

Back to Shared Server Statistics
Back to Top

Shared Servers Dispatchers

NameAvg ConnsTotal Disp Time (s)%Busy%IdleTotal QueuedTotal Queue Wait (s)Avg Queue Wait (ms)
D0000.003,6160.00100.0000 


Back to Shared Server Statistics
Back to Top

init.ora Parameters

Back to Top

init.ora Parameters

Parameter NameBegin valueEnd value (if different)
_optimizer_adaptive_cursor_sharingFALSE  
_optimizer_extended_cursor_sharingNONE  
_optimizer_extended_cursor_sharing_relNONE  
audit_file_dest/u01/app/oracle/admin/klash/adump  
audit_sys_operationsFALSE  
audit_trailNONE  
compatible11.2.0.4.0  
control_files/u01/app/oracle/oradata/klash/control01.ctl, /u01/app/oracle/fast_recovery_area/klash/control02.ctl  
cursor_sharingEXACT  
db_block_size8192  
db_domain    
db_nameklash  
db_recovery_file_dest/u01/app/oracle/fast_recovery_area  
db_recovery_file_dest_size4385144832  
db_writer_processes12  
diagnostic_dest/u01/app/oracle  
disk_asynch_ioFALSE  
dispatchers(PROTOCOL=TCP) (SERVICE=klashXDB)  
filesystemio_optionsSETALL  
job_queue_processes1000  
memory_max_target85899345920  
memory_target75161927680  
open_cursors10000  
pga_aggregate_target0  
plsql_warningsDISABLE:ALL  
processes2000  
remote_login_passwordfileEXCLUSIVE  
sec_case_sensitive_logonFALSE  
sessions3024  
sga_target0  
undo_tablespaceUNDOTBS1  


Back to init.ora Parameters
Back to Top

init.ora Multi-Valued Parameters

Parameter NameBegin valueEnd value (if different)
control_files/u01/app/oracle/fast_recovery_area/klash/control02.ctl  
/u01/app/oracle/oradata/klash/control01.ctl  


Back to init.ora Parameters
Back to Top

Dynamic Remastering Stats

No data exists for this section of the report.


Back to Top

End of Report