4 Replies Latest reply: May 30, 2014 6:45 AM by Vikas0204 RSS

    IO issue in database  (locking issue)

    Vikas0204

      AWR is as follows:

       

      WORKLOAD REPOSITORY report for

       

      DB NameDB IdInstanceInst numReleaseRACHost
      DBIWM1558635572dbiwm110.2.0.1.0NOdbserver.iwm.com.pk

       

      Snap IdSnap TimeSessionsCursors/Session
      Begin Snap:1487629-May-14 00:00:36274.4
      End Snap:1489329-May-14 17:00:092539.0
      Elapsed: 1,019.55 (mins)
      DB Time: 837.19 (mins)

       

      Report Summary

      Cache Sizes

       

      BeginEnd
      Buffer Cache:3,472M3,328MStd Block Size:8K
      Shared Pool Size:304M448MLog Buffer:14,404K

      Load Profile

       

      Per SecondPer Transaction
      Redo size:10,244.348,274.16
      Logical reads:65,921.2853,243.39
      Block changes:61.7749.89
      Physical reads:39.2331.69
      Physical writes:41.9233.86
      User calls:152.86123.46
      Parses:12.149.80
      Hard parses:1.531.24
      Sorts:10.798.72
      Logons:0.050.04
      Executes:42.4634.30
      Transactions:1.24

       

      % Blocks changed per Read:0.09Recursive Call %:37.89
      Rollback per transaction %:7.87Rows per Sort:2509.48

      Instance Efficiency Percentages (Target 100%)

       

      Buffer Nowait %:100.00Redo NoWait %:100.00
      Buffer Hit %:99.99In-memory Sort %:100.00
      Library Hit %:96.88Soft Parse %:87.37
      Execute to Parse %:71.42Latch Hit %:99.97
      Parse CPU to Parse Elapsd %:99.05% Non-Parse CPU:96.26

      Shared Pool Statistics

       

      BeginEnd
      Memory Usage %:97.8276.80
      % SQL with executions>1:88.8344.86
      % Memory for SQL w/exec>1:77.0742.38

      Top 5 Timed Events

       

      EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
      CPU time 35,724 71.1
      enq: TM - contention3,1799,2632,91418.4Application
      log file parallel write72,1043,111436.2System I/O
      log file sync66,9033,007456.0Commit
      control file parallel write21,484588271.2System I/O

      Main Report

       

      Back to Top

      Wait Events Statistics

      Back to Top

      Time Model Statistics

      • Total time in database user-calls (DB Time): 50231.5s
      • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
      • Ordered by % or DB time desc, Statistic name
      Statistic NameTime (s)% of DB Time
      sql execute elapsed time45,801.5591.18
      DB CPU35,724.1571.12
      parse time elapsed5,495.4010.94
      hard parse elapsed time5,383.9110.72
      inbound PL/SQL rpc elapsed time4,843.909.64
      failed parse elapsed time3,870.787.71
      PL/SQL execution elapsed time146.510.29
      PL/SQL compilation elapsed time6.820.01
      connection management call elapsed time6.510.01
      Java execution elapsed time6.360.01
      hard parse (sharing criteria) elapsed time4.540.01
      RMAN cpu time (backup/restore)1.710.00
      hard parse (bind mismatch) elapsed time0.550.00
      repeated bind elapsed time0.510.00
      sequence load elapsed time0.180.00
      DB time50,231.47
      background elapsed time4,265.76
      background cpu time77.30

      Back to Wait Events Statistics

      Back to Top

      Wait Class

      • s - second
      • cs - centisecond - 100th of a second
      • ms - millisecond - 1000th of a second
      • us - microsecond - 1000000th of a second
      • ordered by wait time desc, waits desc
      Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      Application7,30843.089,26612680.10
      System I/O134,5760.003,709281.78
      Commit66,9030.023,007450.88
      User I/O359,4330.0018114.75
      Network11,525,1470.00330152.17
      Concurrency2,3560.0031130.03
      Configuration1,13394.5320170.01
      Other28,1750.01500.37
      Administrative50.0047520.00

      Back to Wait Events Statistics

      Back to Top

      Wait Events

      • s - second
      • cs - centisecond - 100th of a second
      • ms - millisecond - 1000th of a second
      • us - microsecond - 1000000th of a second
      • ordered by wait time desc, waits desc (idle events last)
      EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      enq: TM - contention3,17999.029,26329140.04
      log file parallel write72,1040.003,111430.95
      log file sync66,9030.023,007450.88
      control file parallel write21,4840.00588270.28
      db file sequential read64,5700.0012520.85
      db file scattered read30,0430.005520.40
      SQL*Net more data to client2,930,1340.0030038.69
      os thread startup9330.0024250.01
      log buffer space527.69183420.00
      log file sequential read6630.00570.01
      switch logfile command50.0047520.00
      library cache lock30.00412400.00
      SQL*Net message to client8,592,4230.0030113.45
      latch: cache buffers chains1060.002220.00
      BFILE get path object27,8410.00200.37
      control file sequential read38,0970.00200.50
      enq: TX - row lock contention30.0026450.00
      log file switch completion50.0023600.00
      buffer exterminate250.0028560.00
      rdbms ipc reply1730.00180.00
      RMAN backup & recovery I/O1,4440.00110.02
      Log archive I/O6740.00110.01
      latch: shared pool8910.00110.01
      SQL*Net break/reset to client4,1200.00100.05
      log file single write200.000250.00
      direct path write temp123,7100.00001.63
      direct path read temp139,1290.00001.84
      enq: RO - fast object reuse60.000220.00
      control file single write900.00010.00
      buffer busy waits1250.00010.00
      latch free250.00030.00
      recovery area: computing obsolete files60.000130.00
      SGA: allocation forcing component growth728.570110.00
      latch: library cache2950.00000.00
      SQL*Net more data from client2,5900.00000.03
      latch: enqueue hash chains10.000140.00
      direct path read1,5710.00000.02
      library cache load lock10.00020.00
      enq: CF - contention60.00000.00
      undo segment extension1,07599.26000.01
      LGWR wait for redo copy860.00000.00
      reliable message90.00000.00
      recovery area: computing dropped files60.00000.00
      recovery area: computing backed up files60.00000.00
      recovery area: computing applied logs60.00000.00
      log file switch (private strand flush incomplete)10.00000.00
      direct path write4100.00000.01
      latch: In memory undo latch10.00000.00
      cursor: mutex X10.00000.00
      latch: session allocation10.00000.00
      SQL*Net message from client8,592,1940.002,822,907329113.44
      Streams AQ: qmn slave idle wait2,1780.0059,579273550.03
      Streams AQ: qmn coordinator idle wait4,42650.7959,579134610.06
      virtual circuit status2,039100.0059,492291770.03
      jobq slave wait18,91799.5655,30629240.25
      Streams AQ: waiting for time management or cleanup tasks21567.4440,4831882920.00
      class slave wait35100.0017148840.00
      SGA: MMAN sleep for component shrink23296.122110.00

      Back to Wait Events Statistics

      Back to Top

      Background Wait Events

      • ordered by wait time desc, waits desc (idle events last)
      EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      log file parallel write72,1050.003,111430.95
      control file parallel write21,1920.00580270.28
      os thread startup9330.0024250.01
      db file sequential read7340.00460.01
      log file sequential read4120.00370.01
      Log archive I/O4100.00120.01
      db file scattered read910.00180.00
      log file single write200.000250.00
      control file sequential read19,0250.00000.25
      buffer busy waits290.00030.00
      log buffer space20.000400.00
      log file sync10.000360.00
      events in waitclass Other2510.00000.00
      latch: shared pool100.00000.00
      latch: library cache160.00000.00
      direct path read1020.00000.00
      direct path write1060.00000.00
      rdbms ipc message264,37573.95637,30624113.49
      pmon timer21,70199.9559,71827520.29
      Streams AQ: qmn slave idle wait2,1780.0059,579273550.03
      Streams AQ: qmn coordinator idle wait4,42650.7959,579134610.06
      smon timer1,9096.3958,223304990.03
      Streams AQ: waiting for time management or cleanup tasks21567.4440,4831882920.00
      SGA: MMAN sleep for component shrink23296.122110.00

      Back to Wait Events Statistics
      Back to Top

      Operating System Statistics

      StatisticTotal
      BUSY_TIME3,682,867
      IDLE_TIME44,620,007
      NICE_TIME593
      SYS_TIME45,487
      USER_TIME3,636,787
      LOAD2
      RSRC_MGR_CPU_WAIT_TIME0
      PHYSICAL_MEMORY_BYTES182,424
      NUM_CPUS8
      NUM_CPU_SOCKETS1

      Back to Wait Events Statistics
      Back to Top

      Service Statistics

      • ordered by DB Time
      Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
      dbiwm43,025.5031,407.902,202,5963,674,547,163
      SYS$USERS7,205.104,316.10194,999357,433,648
      SYS$BACKGROUND0.000.002,347617,822
      dbiwmXDB0.000.0000

      Back to Wait Events Statistics

      Back to Top

      Service Wait Class Stats

      • Wait Class info for services in the Service Statistics section.
      • Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
      • Time Waited (Wt Time) in centisecond (100th of a second)
      Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
      dbiwm3270831364051762400106704113151
      SYS$USERS301083144843435376852086158
      SYS$BACKGROUND2242131799623880000

      Back to Wait Events Statistics

      Back to Top

       

      SQL Statistics

      Back to Top

      SQL ordered by Elapsed Time

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text
      8,3458,34520,0450.4216.612rk418s89vbg7javaw.exe SELECT DECODE(IN_OUT, '02', LE...
      4,2964,29633130.198.5558h1fq5xkf24cjavaw.exe select wd.empid, cardid, (fna...
      3,0533,05321526.486.08cz0rkvmpd8dwsfrmweb.exe INSERT INTO PAYROLL.INOUT_ST S...
      2,68607433.615.35432hn0n4m6639frmweb.exe UPDATE QUOTATIONDT SET QTID=:1...
      1,8071,8072,3590.773.60dq950yjmrg9ztfrmweb.exe SELECT S.OLD_CODE , R.C_BAL ,...
      1,70704426.643.4096k6sk68ja1rwfrmweb.exe INSERT INTO QUOTATIONHD(QTID, ...
      1,4961,4953793.952.98cu18cs4tkryhdfrmweb.exe select * from rawmat where spi...
      1,3821,3822690.932.7525d2tp8rhztazfrmweb.exe INSERT INTO PAYROLL.INOUT_WK S...
      1,0151,0142507.392.025qtj8cwkq1ydtsqlplus@dbserver.iwm.com.pk (TNS V1-V3) DECLARE cnt NUMBER; b...
      7153673023.831.42gxzxvt2fb1c83frmweb.exe SELECT GDNID , DECODE(TYPE , ...
      6776771534.431.35g4np2agkrpk63javaw.exe SELECT DECODE(COUNT(ENTERANCE)...
      6773941677.001.35b9h5dj3suk09mjavaw.exe select CT . SPID , ct . cusid...
      5675666860.831.13apt95wynqzz3rjavaw.exe SELECT C_BAL FROM RMIM.RAWMAT ...
      5455454841.131.09at0wvf674y395frmweb.exe SELECT RMIM.GETMOVINGAVGRATE(:...
      5455454841.131.082hftw9ggaxc50frmweb.exe SELECT * FROM ( SELECT 'O' TTY...
      5035038890.571.008fvf4fvbc90s3javaw.exe SELECT DISTINCT ROUND(DECODE(W...

      Back to SQL Statistics

      Back to Top

      SQL ordered by CPU Time

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      CPU Time (s)Elapsed Time (s)Executions CPU per Exec (s)% Total DB TimeSQL IdSQL ModuleSQL Text
      8,3458,34520,0450.4216.612rk418s89vbg7javaw.exe SELECT DECODE(IN_OUT, '02', LE...
      4,2964,29633130.188.5558h1fq5xkf24cjavaw.exe select wd.empid, cardid, (fna...
      3,0533,05321526.486.08cz0rkvmpd8dwsfrmweb.exe INSERT INTO PAYROLL.INOUT_ST S...
      1,8071,8072,3590.773.60dq950yjmrg9ztfrmweb.exe SELECT S.OLD_CODE , R.C_BAL ,...
      1,4951,4963793.952.98cu18cs4tkryhdfrmweb.exe select * from rawmat where spi...
      1,3821,3822690.932.7525d2tp8rhztazfrmweb.exe INSERT INTO PAYROLL.INOUT_WK S...
      1,0141,0152507.032.025qtj8cwkq1ydtsqlplus@dbserver.iwm.com.pk (TNS V1-V3) DECLARE cnt NUMBER; b...
      6776771534.431.35g4np2agkrpk63javaw.exe SELECT DECODE(COUNT(ENTERANCE)...
      5665676860.831.13apt95wynqzz3rjavaw.exe SELECT C_BAL FROM RMIM.RAWMAT ...
      5455454841.131.09at0wvf674y395frmweb.exe SELECT RMIM.GETMOVINGAVGRATE(:...
      5455454841.131.082hftw9ggaxc50frmweb.exe SELECT * FROM ( SELECT 'O' TTY...
      5035038890.571.008fvf4fvbc90s3javaw.exe SELECT DISTINCT ROUND(DECODE(W...
      3946771393.641.35b9h5dj3suk09mjavaw.exe select CT . SPID , ct . cusid...
      3677153012.231.42gxzxvt2fb1c83frmweb.exe SELECT GDNID , DECODE(TYPE , ...
      02,6867430.005.35432hn0n4m6639frmweb.exe UPDATE QUOTATIONDT SET QTID=:1...
      01,70740.003.4096k6sk68ja1rwfrmweb.exe INSERT INTO QUOTATIONHD(QTID, ...

      Back to SQL Statistics

      Back to Top

      SQL ordered by Gets

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • Total Buffer Gets: 4,032,601,111
      • Captured SQL account for 49.1% of Total
      Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      283,774,3791283,774,379.007.04393.64677.00b9h5dj3suk09mjavaw.exe select CT . SPID , ct . cusid...
      220,543,8361821,211,779.325.47360.55360.58bd0dpwu95xz52javaw.exe SELECT SUM (( NET_INVOICE_VALU...
      189,002,4321215,750,202.674.69286.36298.03af8xcd3an1qp1frmweb.exe SELECT WD.BDATE , NORMAL , P...
      177,242,99620,0458,842.254.408344.598345.092rk418s89vbg7javaw.exe SELECT DECODE(IN_OUT, '02', LE...
      152,476,686305,082,556.203.78366.84714.82gxzxvt2fb1c83frmweb.exe SELECT GDNID , DECODE(TYPE , ...
      90,875,665379239,777.482.251495.271496.24cu18cs4tkryhdfrmweb.exe select * from rawmat where spi...
      72,676,670332,202,323.331.804296.064296.1658h1fq5xkf24cjavaw.exe select wd.empid, cardid, (fna...
      66,961,569233,480,784.501.663052.953052.95cz0rkvmpd8dwsfrmweb.exe INSERT INTO PAYROLL.INOUT_ST S...
      61,269,784230,634,892.001.521014.061014.785qtj8cwkq1ydtsqlplus@dbserver.iwm.com.pk (TNS V1-V3) DECLARE cnt NUMBER; b...
      60,966,285203,048,314.251.51140.13140.1359u1aw2psdk75javaw.exe select replace(item_sname||' '...
      60,173,728228263,919.861.4984.7684.761pkn3jm05jk48frmweb.exe SELECT SUM (QTY) FROM DELIVERY...
      44,089,56998449,893.561.0983.0283.068gjcypbzddr55frmweb.exe SELECT NVL(SUM (QTY) , 0 ) F...

      Back to SQL Statistics

      Back to Top

      SQL ordered by Reads

      • Total Disk Reads: 2,399,946
      • Captured SQL account for 45.3% of Total
      Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      550,8193018,360.6322.95366.84714.82gxzxvt2fb1c83frmweb.exe SELECT GDNID , DECODE(TYPE , ...
      361,4871361,487.0015.06393.64677.00b9h5dj3suk09mjavaw.exe select CT . SPID , ct . cusid...
      94,760247,380.003.9535.96103.489by1v876pkp07frmweb.exe select gdnid, decode(type, 'N'...
      15,593115,593.000.655.2913.3498xzvnh35xdpb  SELECT ALL ORDERS_IN_PRODUCTIO...
      14,520121,210.000.61286.36298.03af8xcd3an1qp1frmweb.exe SELECT WD.BDATE , NORMAL , P...
      12,90013992.310.5443.8553.45drcubkr437k3sjavaw.exe SELECT r . req_date , r . req...
      7,4268928.250.31104.38109.331rnd3wqfg764tjavaw.exe SELECT DISTINCT O . ORDID , O...
      5,40148112.520.23444.25447.801tkh9gujgzvkzfrmweb.exe SELECT ALL R.CODE, R.SPRDID, ...
      3,6308453.750.1518.5621.59c04bfq6tsmnm5javaw.exe SELECT r.req_date, r.requied_...
      3,60213526.680.15150.78153.804vvkzjngn0pbafrmweb.exe SELECT C.CUSTYPE , DECODE(C.C...

      Back to SQL Statistics

      Back to Top

      SQL ordered by Executions

      • Total Executions: 2,597,701
      • Captured SQL account for 24.1% of Total
      Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text
      126,895125,7450.990.000.007dfrg4ay4c2zufrmweb.exe SELECT ROUND((TO_DATE(TO_CHAR(...
      42,97342,9731.000.000.00grwydz59pu6mc  select text from view$ where r...
      39,97539,9751.000.000.002kckyyfhydmk9frmweb.exe select rtrim(sys_context('user...
      25,33725,1350.990.000.003c1kubcdjnppq  update sys.col_usage$ set eq...
      20,04518,4980.920.420.422rk418s89vbg7javaw.exe SELECT DECODE(IN_OUT, '02', LE...
      12,87312,8731.000.000.001k4c3dxwpsxw8frmweb.exe SELECT ORDER_HD.CUSNAME , CUS...
      12,85910,5500.820.000.003u8wmb9jfbhx7frmweb.exe SELECT DISTINCT ORDID FROM SAL...
      12,8598,9240.690.010.01g3cf8qq5zy8nyfrmweb.exe SELECT SUM (NVL(PERCENT_BILLED...
      12,85112,8511.000.000.007wgnz7bf7z15qfrmweb.exe SELECT UNITS.UNITNAME FROM SAL...
      11,62511,3200.970.000.009n4vr3qvdddr1frmweb.exe SELECT SPNAME FROM SALES_POINT...

      Back to SQL Statistics

      Back to Top

      SQL ordered by Parse Calls

      • Total Parse Calls: 742,459
      • Captured SQL account for 49.6% of Total
      Parse CallsExecutions % Total ParsesSQL IdSQL ModuleSQL Text
      42,97342,9735.79grwydz59pu6mc  select text from view$ where r...
      39,97539,9755.382kckyyfhydmk9frmweb.exe select rtrim(sys_context('user...
      12,87312,8731.731k4c3dxwpsxw8frmweb.exe SELECT ORDER_HD.CUSNAME , CUS...
      12,85912,8591.733u8wmb9jfbhx7frmweb.exe SELECT DISTINCT ORDID FROM SAL...
      12,85912,8591.73g3cf8qq5zy8nyfrmweb.exe SELECT SUM (NVL(PERCENT_BILLED...
      12,85112,8511.737wgnz7bf7z15qfrmweb.exe SELECT UNITS.UNITNAME FROM SAL...
      10,92711,6251.479n4vr3qvdddr1frmweb.exe SELECT SPNAME FROM SALES_POINT...
      10,15210,1521.370h6b2sajwb74n  select privilege#, level from ...
      8,55425,3371.153c1kubcdjnppq  update sys.col_usage$ set eq...
      8,5242021.1553btfq0dt9bs9  insert into sys.col_usage$ val...
      7,7927,7921.050sfvm0sf84qan  select parent_owner, parent_na...

      Back to SQL Statistics

      Back to Top

       

      SQL ordered by Sharable Memory

      No data exists for this section of the report.

      Back to SQL Statistics
      Back to Top

      SQL ordered by Version Count

      • Only Statements with Version Count greater than 20 are displayed
      Version Count Executions SQL IdSQL ModuleSQL Text
      3625,2172ujjymsc0qn7cfrmweb.exe SELECT SPNAME FROM SALES.SALES...
      17511,6259n4vr3qvdddr1frmweb.exe SELECT SPNAME FROM SALES_POINT...
      564,979dxudm7zx7tjymfrmweb.exe SELECT MENU_DATA , MENU_TYPE ...
      502684vrp4zstn78kvjavaw.exe SELECT SPADD FROM SALES_POINT ...
      226,0470nrjm91swt5usjavaw.exe SELECT SNAME FROM WH.SECTIONS ...

      Back to SQL Statistics
      Back to Top

       

      Complete List of SQL Text

       

      SQL IdSQL Text
      0h6b2sajwb74nselect privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      0nrjm91swt5usSELECT SNAME FROM WH.SECTIONS WHERE DEPTNO = :1 AND SECTIONID = :1
      0sfvm0sf84qanselect parent_owner, parent_name, parent_link_name, parent_type, parent_timestamp, property from ora_kglr7_dependencies where owner=:1 and name=:2 and type=:3 and obj#=:4 order by order_number
      1k4c3dxwpsxw8SELECT ORDER_HD.CUSNAME , CUSADD , TEL , SRNAME FROM SALES.ORDER_HD , SALES.SALES_REP WHERE ORDER_HD.ORDID = :1 AND ORDER_HD.SPID = :1 AND ORDER_HD.SRID = SALES_REP.SRID
      1pkn3jm05jk48SELECT SUM (QTY) FROM DELIVERY_VALUE_GST_DETAIL WHERE ORDID = :1 AND ORDSID = :1 AND SPID = :1
      1rnd3wqfg764tSELECT DISTINCT O . ORDID , O . SPID , D . SPID , D . QTY , D . FROM_TYPE , D . CHANGE_TYPE , D . PDESC , D . PRDID , O . BDATE , O . DDATE , O . CUSNAME , SP . SPNAME , SR . SRNAME , S . RESERVED_QTY , S . JOB_QTY , S . AVAILABLE_QTY , S . DELIVERED_QTY , O . QTID , O . ADVANCE , O . DISCOUNT , o . sale_type , o . total_order_value , O . SHIPTO_NAME , O . SHIPTO_ADD , O . SHIPTO_CON , O . SHIPTO_TEL , D . PDETAIL , D . RATE , D . PF , D . SERIAL_NO , P . PREF , P . UNITNAME , D . PDESC || ' ' || D . PDETAIL PRODUCT_NAME , D . ORDSID , D . ORDID , c . CATNAME , j . JOBID , j . JOBSID , j . QTY jdqty , available_wh , physical_wh FROM WH . CUSTOMER_ORDER_STATUS S , ORDER_HD O , SALES_POINT SP , SALES_REP SR , ORDER_DT D , WH . PRODUCT_DETAILS_WITH_PIC P , CATEGORY c , WH . JOBDT j , stock_7 st WHERE ( O.ORDID = : PORDID AND O.SPID = : PSPID ) AND ( ( O.SRID = SR.SRID ) AND ( O.ORDID = D.ORDID ) AND ( O.SPID = D.SPID ) AND ( D.ORDID = S.ORDID ) AND ( D.ORDSID = S.ORDSID ) AND ( D.SPID = S.SPID ) AND ( D.PRDID = P.PRDID ) AND ( st.PRDID = P.PRDID ) AND ( O.SPID = SP.SPID ) AND ( O.CATID = c.CATID ) AND ( D.ORDID = j.ORDID ) AND ( D.ORDsID = j.ORDSID ) AND ( D.SPID = j.SPID ) and 1 = 1 ) ORDER BY 11 ASC, 37 ASC, 18 ASC, 19 ASC, 20 ASC, 23 ASC, 24 ASC, 25 ASC, 26 ASC, 1 ASC, 13 ASC, 12 ASC, 9 ASC, 10 ASC, 2 ASC, 36 ASC, 21 ASC, 22 ASC , D.SERIAL_NO
      1tkh9gujgzvkzSELECT ALL R.CODE, R.SPRDID, R.PRODUCT_NAME, R.UNIT, R.C_BAL, R.ORD_LEV, R.ORD_QTY, R.LP_RATE, R.LP_DATE, R.C_PRR, R.QTY_3MONTH, NVL(E.EXPECTED, 0) AS C_BOM, r.location FROM rmim.RAWMAT R, (select sprdid, sum(balance_qty)expected from rmim.v_bom_mrp group by sprdid) E , sprod s where spid=:1 AND R.SPRDID=E.SPRDID(+) and r.sprdid = s.sprdid
      25d2tp8rhztazINSERT INTO PAYROLL.INOUT_WK SELECT * FROM PAYROLL.V_INOUT_WK WHERE TO_DATE(TO_CHAR(ENTERANCE, 'DD-MON-YYYY'), 'DD-MON-YYYY') BETWEEN :B2 AND :B1
      2hftw9ggaxc50SELECT * FROM ( SELECT 'O' TTYPE, CLOSING_DATE TDATE, NVL(QTY, 0) QTY, NVL(RATE, 0) RATE, VALUE FROM RMIM.RM_CLOSING WHERE SPID = :B2 AND SPRDID = :B1 AND CLOSING_DATE = '31-DEC-2011' UNION ALL SELECT 'I' TTYPE, ISSUE_DATE TDATE, NVL(ISSUED_QTY, 0) QTY, NVL(MOVING_AVG_RATE, 0) RATE , NVL(ISSUED_QTY, 0)*NVL(MOVING_AVG_RATE, 0) VALUE FROM INV_ISSUANCE_HEADER IH, INV_ISSUANCE_DETAIL ID, STORE_ISSUANCE_HEADER S WHERE IH.ISSUANCE_ID = ID.ISSUANCE_ID AND IH.SIN_NO = S.SIN_NO AND IH.SPID = S.SPID AND TRAN_TYPE IN ('I', 'T') AND IH.SPID = :B2 AND SPRDID = :B1 AND TO_DATE(TO_CHAR(ISSUE_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') > TO_DATE('31-DEC-2011', 'DD-MON-YYYY') UNION ALL SELECT 'R' TTYPE, ISSUE_DATE TDATE, NVL(ISSUED_QTY, 0) QTY, NVL(MOVING_AVG_RATE, 0) RATE , NVL(ISSUED_QTY, 0)*NVL(MOVING_AVG_RATE, 0) VALUE FROM INV_ISSUANCE_HEADER IH, INV_ISSUANCE_DETAIL ID, STORE_ISSUANCE_HEADER S WHERE IH.ISSUANCE_ID = ID.ISSUANCE_ID AND IH.SIN_NO = S.SIN_NO AND IH.SPID = S.SPID AND TRAN_TYPE IN ('R', 'N') AND IH.SPID = :B2 AND SPRDID = :B1 AND TO_DATE(TO_CHAR(ISSUE_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') > TO_DATE('31-DEC-2011', 'DD-MON-YYYY') UNION ALL SELECT 'G' TTYPE, GRNDATE TDATE, NVL(QTY, 0) QTY, NVL(GRN_RATE, 0), NVL(QTY, 0)*NVL(GRN_RATE, 0) VALUE FROM GRN WHERE SPID = :B2 AND SPRDID = :B1 AND TO_DATE(TO_CHAR(GRNDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') > TO_DATE('31-DEC-2011', 'DD-MON-YYYY') UNION ALL SELECT 'G' TTYPE, TRAN_DATE TDATE, SUM(D.QTY) PURCHASED_QTY, RATE, RATE*QTY VALUE FROM WH.STOCK_TRANSFER_HD H, WH.STOCK_TRANSFER_DT D WHERE H.TRAN_ID = D.TRAN_ID AND H.TRAN_TYPE IN ('R') AND SPID=:B2 AND SPRDID = :B1 AND TO_DATE(TO_CHAR(TRAN_DATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') > TO_DATE('31-DEC-2011', 'DD-MON-YYYY') GROUP BY TRAN_DATE, QTY, RATE UNION ALL SELECT 'N' TTYPE, PRNDATE TDATE, SUM(P.QTY) QTY, GRN_RATE RATE, SUM(P.QTY)*GRN_RATE VALUE FROM SPROD_RETURN P, GRN G WHERE P.GRNID = G.GRNID AND P.GRNSID = G.GRNSID AND P.SPID = G.SPID AND P.SPID = :B2 AND P.SPRDID = :B1 AND TO_DATE(TO_CHAR(PRNDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') > TO_DATE('31-DEC-2011', 'DD-MON-YYYY') GROUP BY PRNDATE, GRN_RATE ) ORDER BY TDATE
      2kckyyfhydmk9select rtrim(sys_context('userenv', 'current_schema')) from dual
      2rk418s89vbg7SELECT DECODE(IN_OUT, '02', LEAVE, NULL) FROM( SELECT IN_OUT, TRDATE LEAVE FROM HR.V_ATTENDANCE WHERE TO_NUMBER(EMPID) = :B2 AND TO_DATE(TO_CHAR(TRDATE, 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI') > TO_DATE(TO_CHAR(:B1 , 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI') AND (TO_DATE(TO_CHAR(TRDATE, 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI')- TO_DATE(TO_CHAR(:B1 , 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI'))*24 < 30 ORDER BY TRDATE ASC , IN_OUT DESC ) WHERE ROWNUM = 1
      2ujjymsc0qn7cSELECT SPNAME FROM SALES.SALES_POINT WHERE SPID = :1
      3c1kubcdjnppqupdate sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag, 1), 0, 0, 1), equijoin_preds = equijoin_preds + decode(bitand(:flag, 2), 0, 0, 1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag, 4), 0, 0, 1), range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1), like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1), null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1), timestamp = :time where obj# = :objn and intcol# = :coln
      3u8wmb9jfbhx7SELECT DISTINCT ORDID FROM SALES.ORDER_HD WHERE ORDID = :1 AND SPID = :1 AND TO_DATE (TO_CHAR (BDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) < '15-MAR-2011'
      432hn0n4m6639UPDATE QUOTATIONDT SET QTID=:1, SPID=:2, QTSID=:3, SERIAL_NO=:4, PRDID=:5, QQTY=:6, QDESC=:7, QDETAIL=:8, QUNIT=:9, QRATE=:10, PF=:11, SETID=:12, DISC=:13, CHANGE_TYPE=:14, FROM_TYPE=:15, location=:16, floor=:17, entry_type=:18, est_no=:19 WHERE ROWID=:20
      4vrp4zstn78kvSELECT SPADD FROM SALES_POINT WHERE SPID = :1
      4vvkzjngn0pbaSELECT C.CUSTYPE , DECODE(C.CUSTYPE , 'E' , '12' , 'R' , '11' , 'C' , '10' , 'D' , '10' ) , DECODE(C.CUSTYPE , 'E' , '03' , 'R' , '02' , 'C' , '01' , 'D' , '01' ) , CC_CODE , LPAD(D.CUSID , 6 , '0' ) , 'Ord No.' || ORDID || ' ' || CATNAME , D.CUSID , D.CUSNAME , ROUND(NVL(DELIVERED_VALUES , 0 ) - NVL(DISCOUNT_ADJUST , 0 ) ) , ROUND(NVL(DISCOUNT_ADJUST , 0 ) ) , ROUND(NVL(PFCHARGES_DELIVERED , 0 ) ) , ROUND(NVL(OUTSTATION , 0 ) ) , ROUND(NVL(NET_SALES_VALUE , 0 ) ) , ROUND(NVL(GST_VALUE , 0 ) ) FROM DELIVERY_VALUE_GST D , CUSTOMER C , ACT_CC A WHERE D.CUSID = C.CUSID AND D.SPID = A.SPID AND TYPE NOT IN ( 'G' , 'S' ) AND D.TYPE = DECODE(:1 , 'SRGDN' , 'W' , 'N' ) AND GDNID = :1
      53btfq0dt9bs9insert into sys.col_usage$ values ( :objn, :coln, decode(bitand(:flag, 1), 0, 0, 1), decode(bitand(:flag, 2), 0, 0, 1), decode(bitand(:flag, 4), 0, 0, 1), decode(bitand(:flag, 8), 0, 0, 1), decode(bitand(:flag, 16), 0, 0, 1), decode(bitand(:flag, 32), 0, 0, 1), :time)
      58h1fq5xkf24cselect wd.empid, cardid, (fname||' '||mname||' '||lname) emp_name, titlename, date_joined, ot_authorized, round(sal*.66667) basic_pay, round(sal*.66667)*.1 medical, round(sal*.66667)*.4 house_rent, (nvl(sal, 0)+arm_allowance+ washing_allowance+ other_allowances+ nvl(petrol_limit, 0)+ nvl(accomodation_allow, 0)+nvl(cola, 0)) gross_sal, nvl(sal, 0) sal, arm_allowance, washing_allowance, other_allowances, cash_payment, cola, nvl(petrol_limit, 0) petrol_limit, nvl(accomodation_allow, 0) accomodation_allow, nvl(sadv, 0) sadv, nvl(loan.loan_amount, 0) loan, nvl(itax.amount, 0) itax, nvl(mb.amount, 0) mb_amount, nvl(eobi.amount, 0) eobi_amount, decode(shift, 'A', null, shift) shift, case when add_months(date_joined, 4) <= :pFromDate and emp_status <> 5 and substr(cardid, 1, 2) not in ('PW', 'BL', 'WP') and nvl(provident_fund, 'Y') <> 'N' then round((sal*.66667)*.025) end pf, nvl(mc.deduction_amount, 0) mc_ded, nvl(spl_fine, 0) spl_fine, wd.trdate, enterance, leave, case when row_number = 1 and to_date(to_char(enterance, 'HH24:MI'), 'HH24:MI') > to_date(time_in, 'HH24:MI') and wd.description is null and substr(cardid, 1, 2) not in ('AD', 'SW', 'PT', 'GD') then greatest(payroll.getLateHrs_st(row_number, shift_id, enterance, time_in, wd.description, break_start, break_end) -getLeave_LateHrs_st(wd.empid, wd.trdate, enterance), 0) end late_hrs, round(case w hen row_number = 1 and to_date(to_char(enterance, 'HH24:MI'), 'HH24:MI') > to_date(time_in, 'HH24:MI')+.003472 and wd.description is null and substr(cardid, 1, 2) not in ('AD', 'SW', 'PT', 'GD') then case when nvl(getLeave_LateFine_st(wd.empid, wd.trdate, enterance), 0) = 0 then greatest(least((payroll.getLateHrs_st(row_number, shift_id, enterance, time_in, wd.description, break_start, break_end) - getLeave_LateHrs_st(wd.empid, wd.trdate, enterance) )*60, 50), 0) end end)late_fine, case when row_number = 1 and to_date(to_char(enterance, 'HH24:MI'), 'HH24:MI') > to_date(time_in, 'HH24:MI') and wd.description is null and substr(cardid, 1, 2) not in ('AD', 'SW', 'PT', 'GD') then greatest(payroll.getLateHrs_st(row_number, shift_id, enterance, time_in, wd.description, break_start, break_end) - getLeave_LateHrs_st(wd.empid, wd.trdate, enterance), 0) end net_late_hrs, wd.description, case when substr(cardid, 1, 2) not in ('AD', 'PT') and ((row_number = 1 and ( to_date(time_in, 'HH24:MI') < to_date(time_out, 'HH24:MI') and leave < to_date(to_char(leave, 'DD-MON-YY')||' '||time_out, 'DD-MON-YY HH24:MI'))) --(leave - to_date(to_char(enterance, 'DD-MON-YY')||' '||time_in, 'DD-MON-YY HH24:MI') )*24 < ((to_date( time_out, 'HH24:MI')-to_date(time_in, 'HH24:MI'))*24) )) or ( row_number = 1 and to_date(time_in, 'HH24:MI') > to_date(time_out, 'HH24:MI') and to_char(enterance, 'HH24:MI') =to_char(leave, 'HH24:MI') and leave < to_date(to_char(leave, 'DD-MON-YY')||' '||time_out, 'DD-MON-YY HH24:MI') ) or (enterance is null) ) then payroll.getAbsHrs_st(:pempid, to_date(to_char(wd.trdate, 'DD-MON-YY'), 'DD-MON-YY'), enterance, leave, shift_id, wd.description, row_number) end abs_hrs, case when (row_number = 1 and leave < decode(leave, null, to_date(null), to_date(to_char(leave, 'DD-MON-YY')||' '||time_out , 'DD-MON-YY HH24:MI'))) or (row_number = 1 and enterance > decode(enterance, null, to_date(null), to_date(to_char(enterance, 'DD-MON-YY')||' '||time_in , 'DD-MON-YY HH24:MI'))) or enterance is null then nvl(payroll.getLHrs_st(wd.empid, wd.trdate, time_in) , 0)+ nvl(getLeave_LateHrs_st(wd.empid, wd.trdate, enterance), 0) end leave_hrs, case when substr(cardid, 1, 2) not in ('AD', 'PT') then payroll.getOTHrs_St(enterance, leave, shift_id, wd.description, time_in, time_out, row_number) when substr(cardid, 1, 2) in ('PT') then payroll.getOTHrs_St(enterance, leave, 99, wd.description, time_in, time_out, row_number) end ot_hrs , /* case when shift_id in (3, 9) and wd.description is not null then PAYROLL.getHolHrs_st(Enterance, leave , shift_id, wd.Description, Time_in , Time_out ) end*/ null hol_hrs, case when enterance is null and wd.description is null then (select description from hr.holidays where h_date = to_date(to_char(wd.trdate, 'DD-MON-YYYY'), 'DD-MON-YYYY')) else wd.description end remarks from (select io.empid, shift_name, case when row_number = 1 then time_in when row_number = 2 then lag(time_in) over (order by enterance, row_number ) end time_in, case when row_number = 1 then time_out when row_number = 2 then lag(time_out) over (order by enterance, row_number ) end time_out, break_start, break_end, break_time, enterance, leave, shift, shift_id, row_number, description from v_inout_st io , (select 0 as empid, to_date(null) leave_from from dual) lhr where to_number(io.empid) = lhr.empid(+) and to_date(to_char(enterance, 'DD-MON-YY')||' '||time_in, 'DD-MON-YY HH24:MI') = to_date(to_char(leave_from(+), 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI') and io.empid = :pEmpId -- and io.enterance is not n ull and io.leave is not null and to_date(to_char(io.enterance, 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate) io, ( select * from ( select * from (SELECT dt + LEVEL trdate FROM (SELECT :pFromDate-1 dt FROM dual) CONNECT BY LEVEL <= :pToDate - dt) wd, holidays hd where wd.trdate = hd.h_date(+)) wd , v_emp where empid = :pEmpid ) wd, (select empid, sum(nvl(deduction, 0)) sadv from emp_advances where trtype = 2 and ac_approval = 'A' and to_date(to_char(ac_app_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate group by empid ) sadv, (select empid, sum(installment_amount) loan_amount from loan_schedule_hd h, loan_schedule_dt d where h.trid = d.trid and due_date = last_day(:pFromDate) and payment_cycle = 'M' and (status is null or status = 'S' or status = 'P') group by empid ) loan, (select empid, amount from payroll.income_tax ) itax, (select empid, sum(amount) spl_fine from payroll.special_fine where trdate between :pFromDate and :pToDate group by empid) spl_fine, (select empid, sum(amount) amount from mobile_bills where trdate between :pFromDate and :pToDate group by empid) mb, payroll.mc_advance_hd mc, (select empid, 80 amount from eobi) eobi where to_date(to_char(io.enterance(+), 'DD-MON-YYYY'), 'DD-MON-YYYY') = wd.trdate and wd.empid = sadv.empid(+) and wd.empid = loan.empid(+) and wd.empid = itax.empid(+) and wd.empid = mc.empid(+) and wd.empid = spl_fine.empid(+) and wd.empid = mb.empid(+) and wd.empid = eobi.empid(+) order by trdate, enterance
      59u1aw2psdk75select replace(item_sname||' '||pdesc, chr(10), '') pdesc, s.sprdid, s.old_code, u.unitname, s.reorder_qty, reorder_level, tran.tran_date, doc_no, tran.tran_type, tran_detail, decode(tran_type, 'Purchase Requisition', tran.qty) pur_qty, decode(tran_type, 'Opening Balance', tran.qty, 'Goods Received', tran.qty, 'Return to Store', tran.qty, 'Received From Factory', tran.qty) Rec_qty, decode(tran_type, 'Issued from Store', tran.qty, 'Purchase Return', tran.qty) Issued_qty from sproduct_details_with_pic s, unit u, ((select to_date('08-JAN-2010') as tran_date, 0 as doc_no, s.sprdid, (nvl(o.qty, 0)+nvl(rec_qty.purchased_qty, 0)+nvl(i_r.qty, 0)+nvl(g.qty, 0)-nvl(pur_ret.qty, 0)) as Qty, 'Opening Balance' as tran_Type, ' ' as tran_detail from sprod s , sprod_adjustment o, (select sprdid, sum(decode(tran_type, 'I', (issued_qty*-1), 'T', (issued_qty*-1), 'R', issued_qty, 'N', issued_qty)) qty from inv_issuance_header h, inv_issuance_detail d, store_issuance_header s where h.issuance_id = d.issuance_id and s.sin_no=h.sin_no and h.spid = s.spid AND H.SPID=:PSPID and TO_DATE(TO_CHAR(issue_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') > '08-JAN-2010' and TO_DATE(TO_CHAR(issue_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') < :pFromDate group by sprdid) i_r, (select sprdid, sum(qty) qty from grn where TO_DATE(TO_CHAR(grndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') > '08-JAN-2010' and TO_DATE(TO_CHAR(grndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') < :pFromDate and spid = :pspid group by sprdid ) g, ( select sprdid, sum(qty) qty, 'Purchase Return' as tran_type, ' ' as tran_detail from sprod_return where TO_DATE(TO_CHAR(prndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') > '08-JAN-2010' and TO_DATE(TO_CHAR(prndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') <:pFromDate and spid = :pspid group by sprdid ) pur_ret, (select h.spid, d.SPRDID, sum(d.qty) purchased_qty from wh.stock_transfer_hd h, wh.stock_transfer_dt d where h.tran_id = d.tran_id and h.tran_type IN ('R') and TO_DATE(TO_CHAR(tran_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') > '08-JAN-2010' and TO_DATE(TO_CHAR(tran_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') < :pFromDate and h.spid = :pSpid group by h.spid, d.SPRDID) rec_qty where s.sprdid= o.sprdid(+) and s.sprdid = i_r.sprdid(+) and s.sprdid=g.sprdid(+) and s.sprdid=pur_ret.sprdid(+) and s.sprdid = rec_qty.sprdid(+) and o.spid =:pspid ) UNION ALL select grndate tran_date, grnid as doc_no, g.sprdid sprdid , (nvl(g.qty, 0)) Qty, 'Goods Received' as Tran_Type, '@'||' '||G.GRN_RATE as tran_detail from grn g where TO_DATE(TO_CHAR(grndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate and spid = :pspid UNION ALL select prndate tran_date, prn_id as doc_no, sprdid, qty, 'Purchase Return' as tran_type, ' ' as tran_detail from sprod_return where TO_DATE(TO_CHAR(prndate , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate and spid = :pspid UNION ALL select issue_date tran_date, h.sin_no as doc_no, d.sprdid sprdid, d.issued_qty qty, 'Return to Store' as tran_type, ' ' as tran_detail from inv_issuance_header h, inv_issuance_detail d, store_issuance_header s where h.issuance_id = d.issuance_id and h.sin_no = s.sin_no and h.spid = s.spid and h.spid =:pspid and s.tran_type in ('R', 'N') and TO_DATE(TO_CHAR(issue_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate UNION ALL select preqdate, preqid as doc_no, sprdid, qty, 'Purchase Requisition' as tran_type , s.sname as tran_detail from pur_req p, sections s where TO_DATE(TO_CHAR(preqdate , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate and spid = :pspid and p.deptno=s.deptno(+) and p.sectionid=s.sectionid(+) UNION ALL select issue_date tran_Date, h.issuance_id as doc_no, d.sprdid, (d.issued_Qty) qty, 'Issued from Store' as tran_type, 'Job:'||job_id||' Dept:'||sname||' '||'sin no:'||' '||S.SIN_NO as tran_detail from inv_issuance_header h, inv_issuance_detail d, store_issuance_header s, dept d, sections se where h.issuance_id = d.issuance_id and h.sin_no = s.sin_no and se.deptno = d.deptno and s.section_id = se.sectionid and d.deptno = s.dept_no(+) and s.tran_type in ('I', 'T') and h.spid = s.spid and h.spid =:pspid and TO_DATE(TO_CHAR(issue_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and :pToDate UNION ALL select tran_date, h.tran_id as doc_no, d.SPRDID, sum(d.qty) purchased_qty, 'Received From Factory' as tran_Type, Remarks as tran_detail from wh.stock_transfer_hd h, wh.stock_transfer_dt d where h.tran_id = d.tran_id and h.tran_type IN ('R') and TO_DATE(TO_CHAR(tran_date , 'DD-MON-YYYY'), 'DD-MON-YYYY') between :pFromDate and ToDate and h.spid = :pSpid group by tran_date, h.tran_id, h.spid, d.SPRDID, remarks order by sprdid ) tran where s.sprdid=tran.sprdid(+) and s.unitid=u.unitid and s.sprdid=:pSprdid order by tran_date
      5qtj8cwkq1ydtDECLARE cnt NUMBER; bid NUMBER; eid NUMBER; BEGIN -- If it's not in V$SQL we will have to query the workload repository select count(*) into cnt from V$SQLSTATS where sql_id = '1rnd3wqfg764t'; IF (cnt > 0) THEN :task_name := dbms_sqltune.create_tuning_task(sql_id => '1rnd3wqfg764t'); ELSE select min(snap_id) into bid from dba_hist_sqlstat where sql_id = '1rnd3wqfg764t'; select max(snap_id) into eid from dba_hist_sqlstat where sql_id = '1rnd3wqfg764t'; :task_name := dbms_sqltune.create_tuning_task(begin_snap => bid, end_snap => eid, sql_id => '1rnd3wqfg764t'); END IF; dbms_sqltune.execute_tuning_task(:task_name); EXCEPTION WHEN OTHERS THEN :err := 1; IF (SQLCODE = -13780) THEN dbms_output.put_line ('ERROR: statement is not in the cursor cache ' || 'or the workload repository.'); dbms_output.put_line('Execute the statement and try again'); ELSE RAISE; END IF; END;
      7dfrg4ay4c2zuSELECT ROUND((TO_DATE(TO_CHAR(:B3 , 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI') - TO_DATE(TO_CHAR(:B2 , 'DD-MON-YY HH24:MI'), 'DD-MON-YY HH24:MI'))/ (TO_DATE(TIME_OUT, 'HH24:MI') - TO_DATE(TIME_IN, 'HH24:MI')), 1) FROM HR.V_EMP V, PAYROLL.DUTY_TIMINGS_DT D WHERE V.DUTY_TIMINGS = D.SHIFT_ID AND TO_DATE(TO_CHAR(:B2 , 'DD-MON-YYYY'), 'DD-MON-YYYY') BETWEEN FROMDATE AND NVL(TODATE, SYSDATE) AND V.EMPID = :B1
      7wgnz7bf7z15qSELECT UNITS.UNITNAME FROM SALES.UNITS WHERE UNITS.UNIT_ID = :1
      8fvf4fvbc90s3SELECT DISTINCT ROUND(DECODE(W.PRATEXL , 0 , 0 , ( BOM_VALUE / ( W.QTY * W.PRATEXL ) * 100 ) ) , 2 ) COST FROM JOBDT J , WH_REQ_DT W , (SELECT DREQID , D.PRDID , SUM (QTY * DECODE(RATE , NULL , LP_RATE , RATE ) ) BOM_VALUE FROM DEPT_REQ D , V_LP_RATE_DATE V WHERE D.SPRDID = V.SPRDID AND V.SPID = 5 AND JOBID = :1 GROUP BY DREQID , D.PRDID ) BOM WHERE J.REQID = W.REQID AND J.REQSID = W.REQSID AND W.PRDID = BOM.PRDID AND ROWNUM = 1 AND J.JOBID = (SELECT DISTINCT JOBID FROM DEPT_REQ WHERE JOBID = :1 )
      8gjcypbzddr55SELECT NVL(SUM (QTY) , 0 ) FROM DELIVERY_VALUE_GST_DETAIL WHERE ORDID = :1 AND SPID = DECODE(:1 , 12 , 3 , :1 ) AND ORDSID = :1
      96k6sk68ja1rwINSERT INTO QUOTATIONHD(QTID, QDATE, DPERIOD, VALIDITY, QTYPE, SRID, SPID, DDATE, CUSID, CUSNAME, CUSADD, LFR, CONPER, TEL, SHIPTO_ID, SHIPTO_NAME, SHIPTO_ADD, SHIPTO_CON, SHIPTO_TEL, SHIP_BASIS, DUTIES, SFR, DISCOUNT, ADVANCE, AMEND_NO, AMEND_DATE, QNOTES, FR_CHARGES, FX_CHARGES, EXPIRED, EXPIRY_DATE, EXP_REASON_ID, GST_NO, CATID, TOTAL_PF_VALUE, TOTAL_QUOTED_VALUE, TOTAL_OS_CHARGES, PAYMENT_TERMS, source_info, source_other, SOURCE_ARCHITECTURE, receiptid, glass, iwmid, iwsid, WH, exp, un, nature, citid, INCOME_TAX_WH, SALE_TAX_WH, RETENTION_MONEY) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53)
      98xzvnh35xdpbSELECT ALL ORDERS_IN_PRODUCTION.CUSNAME, ORDERS_IN_PRODUCTION.ORDID, ORDERS_IN_PRODUCTION.DDATE, ORDERS_IN_PRODUCTION.BDATE, ORDERS_IN_PRODUCTION.JOBID, ORDERS_IN_PRODUCTION.SPID FROM ORDERS_IN_PRODUCTION
      9by1v876pkp07select gdnid, decode(type, 'N', 'GDN', 'W', 'SRGDN', 'GDN') type, dated , d.cusname, decode(iwsid, null, 'IWM', 'IWS') company from delivery_value_gst d, quotationhd q, act_voucher_detail v where d.qtid = q.qtid and d.spid = q.spid and vd_doc_type(+) = decode(type, 'N', 'GDN', 'W', 'SRGDN', 'GDN') and vd_doc_no(+) = gdnid and d.spid = :1 and type not in ('G', 'S') and dated >= '01-JUL-2012' and vd_doc_no is null order by gdnid desc
      9n4vr3qvdddr1SELECT SPNAME FROM SALES_POINT WHERE SPID = :1
      af8xcd3an1qp1SELECT WD.BDATE , NORMAL , PROJECT , EXPORT , DEALERS , TOTAL , INVOICES , RAMOUNT , PRODUCTION , RETAIL.AMOUNT RETAIL_AMOUNT FROM (SELECT DT + LEVEL BDATE FROM (SELECT :1 - 1 DT FROM DUAL ) CONNECT BY LEVEL <= :1 - DT ) WD , (SELECT BDATE , SUM (NORMAL) NORMAL , SUM (PROJECT) PROJECT , SUM (EXPORT) EXPORT , SUM (DEALERS) DEALERS , NVL(SUM (NORMAL) , 0 ) + NVL(SUM (PROJECT) , 0 ) + NVL(SUM (EXPORT) , 0 ) + NVL(SUM (DEALERS) , 0 ) TOTAL FROM (SELECT TO_DATE (TO_CHAR (BDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BDATE , SUM (DECODE(NVL(NATURE , 'N' ) , 'N' , NET_ORDER_VALUE_EXGST ) ) NORMAL , SUM (DECODE(NVL(NATURE , 'N' ) , 'P' , NET_ORDER_VALUE_EXGST ) ) PROJECT , SUM (DECODE(NVL(NATURE , 'N' ) , 'E' , NET_ORDER_VALUE_EXGST ) ) EXPORT , SUM (DECODE(NVL(NATURE , 'N' ) , 'D' , NET_ORDER_VALUE_EXGST ) ) DEALERS FROM V_ORDER_VALUE WHERE SPID = DECODE(:1 , '0' , SPID , :1 ) AND SPTYPE = 'S' AND NVL(IWM , 'N' ) = 'N' AND TO_DATE (TO_CHAR (BDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN :1 AND :1 GROUP BY TO_DATE (TO_CHAR (BDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) , NATURE ) GROUP BY BDATE ORDER BY BDATE DESC ) SALES , (SELECT DATED , SUM (DECODE(TYPE , 'I' , AMOUNT , 'R' , AMOUNT * -1 ) ) INVOICES FROM (SELECT DATED , 'R' TYPE , SUM (NVL(NET_RETURN_VALUE , 0 ) + NVL(DISCOUNT_ADJUST , 0 ) ) AMOUNT FROM RETURN_VALUE_GST R , CUSTOMER C WHERE R.CUSID = C.CUSID AND NVL(C.IWM , 'N' ) != 'Y' AND DATED BETWEEN :1 AND :1 AND R.SPID = DECODE(:1 , '0' , R.SPID , :1 ) GROUP BY DATED UNION ALL SELECT DATED , 'I' TYPE , SUM (NET_SALES_VALUE + PFCHARGES_DELIVERED + OUTSTATION - NVL(DISCOUNT_ADJUST , 0 ) ) AMOUNT FROM DELIVERY_VALUE_GST D , CUSTOMER C WHERE D.CUSID = C.CUSID AND NVL(C.IWM , 'N' ) != 'Y' AND DATED BETWEEN :1 AND :1 AND D.SPID = DECODE(:1 , '0' , D.SPID , :1 ) GROUP BY DATED ) GROUP BY DATED ORDER BY DATED DESC ) INV , (SELECT RDATE , SUM (RAMOUNT) RAMOUNT FROM (SELECT RDATE , SUM (AMOUNT) RAMOUNT FROM RECEIPTHD WHERE SPID = DECODE(:1 , '0' , SPID , :1 ) AND RDATE BETWEEN :1 AND :1 AND PAYMENT_MODE NOT IN ( 'Z' ) GROUP BY RDATE UNION ALL SELECT TO_DATE (TO_CHAR (TRDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) , SUM (P.AMOUNT) AMOUNT FROM RETAIL_HD H , RETAIL_PAYMENTS P WHERE H.BILLID = P.BILLID AND H.SPID = P.SPID AND P.PAYMENT_MODE != 'R' AND H.SPID = DECODE(:1 , '0' , H.SPID , :1 ) AND TO_DATE (TO_CHAR (TRDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN :1 AND :1 GROUP BY TRDATE ) GROUP BY RDATE ORDER BY RDATE DESC ) REC , (SELECT TDATE , ROUND(SUM (EX_GST) ) PRODUCTION FROM DAILY_PRODUCTION_TOTAL GROUP BY TDATE ) PROD , (SELECT TO_DATE (TO_CHAR (TRDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) TRDATE , SUM (AMOUNT_EXGST) AMOUNT FROM SALES.V_RETAIL WHERE SPID = DECODE(:1 , '0' , SPID , :1 ) AND TO_DATE (TO_CHAR (TRDATE , 'DD-M ON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN :1 AND :1 GROUP BY TO_DATE (TO_CHAR (TRDATE , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) ) RETAIL WHERE WD.BDATE = SALES.BDATE AND WD.BDATE = INV.DATED AND WD.BDATE = REC.RDATE AND WD.BDATE = PROD.TDATE AND WD.BDATE = RETAIL.TRDATE ORDER BY WD.BDATE DESC
      apt95wynqzz3rSELECT C_BAL FROM RMIM.RAWMAT WHERE SPRDID = :1 AND SPID = :1
      at0wvf674y395SELECT RMIM.GETMOVINGAVGRATE(:1 , :1 ) FROM DUAL
      b9h5dj3suk09mselect CT . SPID , ct . cusid , ct . cusname , SUM ( nvl ( op , 0 ) ) OP , nvl ( sum ( tr . dr ) , 0 ) dr , nvl ( sum ( tr . cr ) , 0 ) cr , SUM ( nvl ( op , 0 ) ) + ( nvl ( sum ( tr . dr ) , 0 ) - nvl ( sum ( tr . cr ) , 0 ) ) Balance from SALES . V_customerS_SPID ct , ( SELECT C . CUSID , C . CUSNAME , TRAN_DATE , TRAN_TYPE , DOC_NO , op , DR , CR FROM CUSTOMER C , ( SELECT cusid , tran_date , 'O' as tran_type , NULL AS DOC_NO , DECODE ( trim ( tran_type ) , 'DB' , AMOUNT , 'CR' , AMOUNT * - 1 ) op , null as dr , NULL AS CR FROM ( SELECT CUSID , TRAN_TYPE , tran_date , AMOUNT FROM CUSTOMER_BALANCE where TRAN_TYPE = 'DB' UNION ALL SELECT CUSID , TRAN_TYPE , tran_date , AMOUNT FROM CUSTOMER_BALANCE where TRAN_TYPE = 'CR' ) UNION ALL select cusid , RDATE AS tran_date , 'R' AS tran_type , RECEIPTID AS DOC_NO , null as op , null AS DR , amount AS CR from sales . receipthd where to_date ( to_char ( rdate , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) >= '01-JUL-2010' AND to_date ( to_char ( rdate , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN '01-JUL-2010' AND : PDATE and amount <> 0 UNION ALL select cusid , DATEd AS tran_date , 'D' AS tran_type , GDNID AS DOC_NO , null as op , round ( ( ( net_invoice_value ) + ( nvl ( pfcharges_delivered , 0 ) ) + ( nvl ( ( outstation ) , 0 ) ) ) ) AS DR , null AS CR from delivery_value_gst where to_date ( to_char ( dated , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) >= '01-JUL-2010' AND to_date ( to_ char ( dateD , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN '01-JUL-2010' AND : PDATE union all select cusid , DATED AS tran_date , 'T' AS tran_type , GDRID AS DOCID , null as op , null AS DR , RETURN_VALUE AS CR from wh . return_value_gst where to_date ( to_char ( dated , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) >= '01-JUL-2010' AND to_date ( to_char ( dateD , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) BETWEEN '01-JUL-2010' AND : PDATE ) T WHERE C . CUSID = T . CUSID ) tr where ct.cusid = tr.cusid and ct.spid = 5 and 1 = 1 having ( ( nvl ( sum ( tr.OP ) , 0 ) + ( nvl ( sum ( tr.dr ) , 0 ) - nvl ( sum ( tr.cr ) , 0 ) ) ) ) <> 0 group by CT.SPID , ct.cusid , ct.cusname ORDER BY 1 ASC
      bd0dpwu95xz52SELECT SUM (( NET_INVOICE_VALUE + OUTSTATION ) ) FROM DELIVERY_VALUE_GST WHERE ORDID = :1 AND SPID = :1
      c04bfq6tsmnm5SELECT r.req_date, r.requied_date, r.rtype, r.po_tag, r.preqid||'-'||r.preqsid reqid, r.preqid, r.preqsid, r.sprdid, r.dreqsid, r.dreqid, r.deptno, r.sectionid, s.sname, R.JOBID, r.jobid||'-'||r.jobsid job_number, r.item_name, r.item_sname, r.product_name, r.itemid, r.unitname, r.old_code, r.lead_time, r.origin, r.c_prr, r.c_bal, r.qty, r.approval_remarks, r.approval_date, r.received, r.rejected, r.pending_qty, t.lp_rate, p.poid, p.qty po_qty, p.required_before supplier_date, h.podate, h.potype, h.vendorid, h.purchaserid, r.plan_date, r.revised_date FROM rmim.pending_purchase r, rmim.podt p, rmim.pohd h, sections s , sprod d, rawmat_tbl t WHERE ( r.preqid = p.preqid ) AND ( r.preqsid = p.preqsid ) AND ( p.poid = h.poid ) AND ( R.ORIGIN = 'L' ) and d.sprdid = r.sprdid and r.sprdid = t.sprdid and t.sprdid = d.sprdid AND d.VENDORID NOT IN ( 473 ) and r.deptno = s.deptno and r.sectionid = s.sectionid and r.spid = : pspid AND TO_DATE ( TO_CHAR ( r.plan_date , 'DD-MON-YYYY' ) , 'DD-MON-YYYY' ) is null ORDER BY 16 ASC, 23 ASC, 21 ASC, 20 ASC, 18 ASC, 1 ASC, 2 ASC, 6 ASC, 12 ASC, 15 ASC, 26 ASC, 30 ASC, 29 ASC, 31 ASC, 40 ASC, 41 ASC, 27 ASC
      cu18cs4tkryhdselect * from rawmat where spid = :1 order by product_name
      cz0rkvmpd8dwsINSERT INTO PAYROLL.INOUT_ST SELECT * FROM PAYROLL.V_INOUT_ST WHERE TO_DATE(TO_CHAR(ENTERANCE, 'DD-MON-YYYY'), 'DD-MON-YYYY') BETWEEN :B2 AND :B1
      dq950yjmrg9ztSELECT S.OLD_CODE , R.C_BAL , R.C_BOM , R.C_PRR , R.ORD_LEV , R.ORD_QTY , R.QTY_3MONTH , LP_RATE FROM RMIM.SPROD S , RMIM.RAWMAT R WHERE S.SPRDID = :1 AND SPID = :1 AND S.SPRDID = R.SPRDID
      drcubkr437k3sSELECT r . req_date , r . requied_date , r . rtype , r . po_tag , r . preqid || '-' || r . preqsid reqid , r . preqid , r . preqsid , r . sprdid , r . dreqsid , r . dreqid , r . deptno , r . sectionid , s . sname , R . JOBID , r . jobid || '-' || r . jobsid job_number , r . item_name , r . item_sname , r . product_name , r . itemid , r . unitname , r . old_code , r . lead_time , r . origin , r . c_prr , r . c_bal , r . qty , r . approval_remarks , r . approval_date , r . received , r . rejected , r . pending_qty , t . lp_rate , p . poid , p . qty po_qty , p . required_before supplier_date , h . podate , h . potype , h . vendorid , h . purchaserid , r . plan_date , r . revised_date FROM rmim . pending_purchase r , rmim . podt p , rmim . pohd h , sections s , sprod d , rawmat_tbl t WHERE ( r.preqid = p.preqid ) AND ( r.preqsid = p.preqsid ) AND ( p.poid = h.poid ) AND ( R.ORIGIN = 'L' ) and d.sprdid = r.sprdid and r.sprdid = t.sprdid and t.sprdid = d.sprdid AND d.VENDORID NOT IN ( 473 ) and r.deptno = s.deptno and r.sectionid = s.sectionid and r.spid = : pspid ORDER BY 16 ASC, 28 ASC, 23 ASC, 21 ASC, 20 ASC, 18 ASC, 32 ASC, 1 ASC, 2 ASC, 13 ASC, 5 ASC, 12 ASC, 15 ASC, 26 ASC, 30 ASC, 29 ASC, 31 ASC, 40 ASC, 27 ASC, 41 ASC, 14 ASC
      dxudm7zx7tjymSELECT MENU_DATA , MENU_TYPE , MENU_PARAM_FORM , MENU_REPORT_FORMAT FROM IWM_MENU WHERE MENU_CODE = :1
      g3cf8qq5zy8nySELECT SUM (NVL(PERCENT_BILLED , 0 ) ) , SUM (NVL(QTY_BILLED , 0 ) ) FROM INVOICEDT WHERE ORDID = :1 AND ORDSID = :1 AND SPID = :1 GROUP BY SPID , ORDSID , ORDID
      g4np2agkrpk63SELECT DECODE(COUNT(ENTERANCE), 0, :B4 , 0) FROM V_INOUT_ST WHERE EMPID = :B3 AND (TO_DATE(TO_CHAR(ENTERANCE, 'DD-MON-YYYY'), 'DD-MON-YYYY') = :B2 OR TO_DATE(TO_CHAR(ENTERANCE, 'DD-MON-YYYY'), 'DD-MON-YYYY') = :B1 )
      grwydz59pu6mcselect text from view$ where rowid=:1
      gxzxvt2fb1c83SELECT GDNID , DECODE(TYPE , 'N' , 'GDN' , 'W' , 'SRGDN' , 'GDN' ) TYPE , DATED , D.CUSNAME , DECODE(IWSID , NULL , 'IWM' , 'IWS' ) COMPANY FROM DELIVERY_VALUE_GST D , QUOTATIONHD Q , ACT_VOUCHER_DETAIL V WHERE D.QTID = Q.QTID AND D.SPID = Q.SPID AND VD_DOC_TYPE = DECODE(TYPE , 'N' , 'GDN' , 'W' , 'SRGDN' , 'GDN' ) AND VD_DOC_NO = GDNID AND D.SPID = :1 AND TYPE NOT IN ( 'G' , 'S' ) AND DATED >= '01-JUL-2012' AND VD_DOC_NO IS NULL ORDER BY GDNID DESC

      Back to SQL Statistics
      Back to Top

       

      Instance Activity Statistics

      Back to Top

      Instance Activity Stats

      StatisticTotalper Secondper Trans
      CPU used by this session3,652,72259.7148.23
      CPU used when call started3,642,39059.5448.09
      CR blocks created48,1290.790.64
      Cached Commit SCN referenced3,129,25351.1541.32
      Commit SCN cached1160.000.00
      DB time10,806,126176.65142.68
      DBWR checkpoint buffers written88,6231.451.17
      DBWR checkpoints140.000.00
      DBWR object drop buffers written80.000.00
      DBWR tablespace checkpoint buffers written00.000.00
      DBWR transaction table writes3,1590.050.04
      DBWR undo block writes37,6460.620.50
      IMU CR rollbacks23,2580.380.31
      IMU Flushes3,2900.050.04
      IMU Redo allocation size11,860,532193.89156.60
      IMU commits45,9000.750.61
      IMU contention2800.000.00
      IMU ktichg flush00.000.00
      IMU pool not allocated23,1260.380.31
      IMU recursive-transaction flush150.000.00
      IMU undo allocation size97,760,5921,598.101,290.76
      IMU- failed to get a private strand4,4280.070.06
      Misses for writing mapping00.000.00
      SMON posted for undo segment recovery10.000.00
      SMON posted for undo segment shrink240.000.00
      SQL*Net roundtrips to/from client8,589,833140.42113.41
      active txn count during cleanout56,9780.930.75
      application wait time926,57215.1512.23
      background checkpoints completed60.000.00
      background checkpoints started50.000.00
      background timeouts196,6473.212.60
      branch node splits00.000.00
      buffer is not pinned count2,983,343,11448,768.9739,389.79
      buffer is pinned count1,264,651,35020,673.3716,697.49
      bytes received via SQL*Net from client392,487,8096,416.035,182.11
      bytes sent via SQL*Net to client11,915,401,556194,782.10157,321.88
      calls to get snapshot scn: kcmgss6,018,74898.3979.47
      calls to kcmgas162,5572.662.15
      calls to kcmgcs14,1130.230.19
      change write time2,8680.050.04
      cleanout - number of ktugct calls56,8130.930.75
      cleanouts and rollbacks - consistent read gets46,9290.770.62
      cleanouts only - consistent read gets1700.000.00
      cluster key scan block gets6,160,138100.7081.33
      cluster key scans3,772,91661.6849.81
      commit batch performed180.000.00
      commit batch requested180.000.00
      commit batch/immediate performed780.000.00
      commit batch/immediate requested780.000.00
      commit cleanout failures: block lost00.000.00
      commit cleanout failures: buffer being written00.000.00
      commit cleanout failures: callback failure1030.000.00
      commit cleanout failures: cannot pin10.000.00
      commit cleanouts245,5274.013.24
      commit cleanouts successfully completed245,4234.013.24
      commit immediate performed600.000.00
      commit immediate requested600.000.00
      commit txn count during cleanout8720.010.01
      concurrency wait time3,0550.050.04
      consistent changes711,69811.639.40
      consistent gets4,028,827,10465,859.5953,193.56
      consistent gets - examination1,564,070,38225,567.9920,650.79
      consistent gets direct1,6880.030.02
      consistent gets from cache4,028,825,41665,859.5653,193.54
      cursor authentications69,1141.130.91
      data blocks consistent reads - undo records applied367,3846.014.85
      db block changes3,778,48261.7749.89
      db block gets3,774,00761.6949.83
      db block gets direct5,6700.090.07
      db block gets from cache3,768,33761.6049.75
      deferred (CURRENT) block cleanout applications168,8672.762.23
      dirty buffers inspected2460.000.00
      enqueue conversions13,7330.220.18
      enqueue releases845,44913.8211.16
      enqueue requests846,25713.8311.17
      enqueue timeouts7950.010.01
      enqueue waits480.000.00
      execute count2,597,70142.4634.30
      frame signature mismatch130.000.00
      free buffer inspected351,1365.744.64
      free buffer requested498,9428.166.59
      heap block compress10,2480.170.14
      hot buffers moved to head of LRU197,6633.232.61
      immediate (CR) block cleanout applications47,0990.770.62
      immediate (CURRENT) block cleanout applications14,1090.230.19
      index fast full scans (full)15,4900.250.20
      index fetch by key1,336,145,94121,842.0917,641.45
      index scans kdiixs174,789,9961,222.60987.47
      java call heap collected bytes4,435,15272.5058.56
      java call heap collected count77,4221.271.02
      java call heap gc count730.000.00
      java call heap live object count46,6510.760.62
      java call heap live object count max46,6510.760.62
      java call heap live size4,475,12073.1659.09
      java call heap live size max4,475,12073.1659.09
      java call heap object count58,0280.950.77
      java call heap object count max58,5110.960.77
      java call heap total size6,684,672109.2788.26
      java call heap total size max6,684,672109.2788.26
      java call heap used size5,020,10482.0666.28
      java call heap used size max5,020,10482.0666.28
      leaf node 90-10 splits2240.000.00
      leaf node splits6210.010.01
      lob reads42,8570.700.57
      lob writes95,7911.571.26
      lob writes unaligned95,7891.571.26
      logons cumulative2,9930.050.04
      messages received104,3841.711.38
      messages sent104,3841.711.38
      no buffer to keep pinned count100.000.00
      no work - consistent read gets2,375,238,96938,828.2431,360.84
      opened cursors cumulative716,74011.729.46
      parse count (failures)1,8800.030.02
      parse count (hard)93,7521.531.24
      parse count (total)742,45912.149.80
      parse time cpu133,5192.181.76
      parse time elapsed134,7982.201.78
      physical read IO requests234,9263.843.10
      physical read bytes19,797,245,952323,627.30261,387.74
      physical read total IO requests274,8534.493.63
      physical read total bytes21,784,718,336356,116.68287,628.81
      physical read total multi block requests137,5462.251.82
      physical reads2,399,94639.2331.69
      physical reads cache341,2675.584.51
      physical reads cache prefetch247,2264.043.26
      physical reads direct2,058,67933.6527.18
      physical reads direct (lob)1,5030.020.02
      physical reads direct temporary tablespace2,056,88933.6227.16
      physical reads prefetch warmup00.000.00
      physical write IO requests159,0622.602.10
      physical write bytes21,104,066,560344,990.01278,642.00
      physical write total IO requests369,8126.054.88
      physical write total bytes24,858,255,360406,360.06328,209.45
      physical write total multi block requests274,9334.493.63
      physical writes2,564,63641.9233.86
      physical writes direct2,474,86040.4632.68
      physical writes direct (lob)3040.000.00
      physical writes direct temporary tablespace2,469,57340.3732.61
      physical writes from cache89,7761.471.19
      physical writes non checkpoint2,507,63240.9933.11
      pinned buffers inspected40.000.00
      prefetch warmup blocks aged out before use00.000.00
      prefetch warmup blocks flushed out before use00.000.00
      prefetched blocks aged out before use23,6250.390.31
      process last non-idle time64,2171.050.85
      recursive calls5,704,89493.2675.32
      recursive cpu usage1,612,09326.3521.28
      redo blocks written1,305,89221.3517.24
      redo buffer allocation retries770.000.00
      redo entries1,620,19726.4921.39
      redo log space requests60.000.00
      redo log space wait time1830.000.00
      redo ordering marks21,8610.360.29
      redo size626,676,72810,244.348,274.16
      redo synch time308,1835.044.07
      redo synch writes89,1811.461.18
      redo wastage19,946,708326.07263.36
      redo write time318,5475.214.21
      redo writer latching time00.000.00
      redo writes72,1111.180.95
      rollback changes - undo records applied122,6092.001.62
      rollbacks only - consistent read gets1,1070.020.01
      rows fetched via callback500,496,8848,181.676,608.18
      session connect time00.000.00
      session cursor cache hits485,7177.946.41
      session logical reads4,032,601,11165,921.2853,243.39
      session pga memory2,257,368,36836,901.4029,804.57
      session pga memory max5,836,428,46495,408.6077,059.75
      session uga memory###############68,817,280.3855,582,435.04
      session uga memory max17,973,223,424293,809.84237,304.74
      shared hash latch upgrades - no wait86,439,5281,413.031,141.28
      sorts (disk)10.000.00
      sorts (memory)660,12010.798.72
      sorts (rows)1,656,563,02827,079.9821,871.99
      sql area purged1,7560.030.02
      summed dirty queue length4680.010.01
      switch current to new buffer5,9370.100.08
      table fetch by rowid1,284,411,06920,996.3816,958.38
      table fetch continued row697,28411.409.21
      table scan blocks gotten650,541,33410,634.468,589.25
      table scan rows gotten79,894,247,1991,306,038.201,054,862.72
      table scans (long tables)140.000.00
      table scans (short tables)892,05514.5811.78
      total number of times SMON posted1,7870.030.02
      transaction rollbacks780.000.00
      undo change vector size234,184,2363,828.233,091.99
      user I/O wait time18,1170.300.24
      user calls9,350,659152.86123.46
      user commits69,7771.140.92
      user rollbacks5,9620.100.08
      workarea executions - onepass1,1580.020.02
      workarea executions - optimal933,35115.2612.32
      write clones created in background00.000.00
      write clones created in foreground1610.000.00

      Back to Instance Activity Statistics
      Back to Top

      Instance Activity Stats - Absolute Values

      • Statistics with absolute values (should not be diffed)
      StatisticBegin ValueEnd Value
      session cursor cache count119,952152,053
      opened cursors current1182,273
      workarea memory allocated5,4307,775
      logons current27253

      Back to Instance Activity Statistics
      Back to Top

      Instance Activity Stats - Thread Activity

      • Statistics identified by '(derived)' come from sources other than SYSSTAT
      StatisticTotalper Hour
      log switches (derived)50.29

      Back to Instance Activity Statistics

      Back to Top

       

      IO Stats

      Back to Top

      Tablespace IO Stats

      • ordered by IOs (Reads + Writes) desc
      TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
      TEMP139,16620.0414.78123,688200.00
      SYSTEM59,55111.092.162,282000.00
      SYSAUX3,21804.861.1310,522000.00
      IMOS_DATA10,35300.727.28133000.00
      UNDOTBS13401.471.008,77601250.72
      SALES_RMIM3,89008.391.432,949000.00
      RMIM3,07808.578.362,964000.00
      WH4,88602.397.771,059000.00
      SALES5,15601.223.55278000.00
      PAYROLL97901.6313.071,956000.00
      USERS1,62300.9912.581,140000.00
      HR1,26803.334.031,482000.00
      ACCOUNTS74306.183.241,140000.00
      COSTING31802.366.32288000.00
      SH_WH22503.163.35196000.00
      AC_AUDIT31202.3710.9569000.00
      ADMIN11303.892.40134000.00
      EXAMPLE600.001.006000.00

      Back to IO Stats
      Back to Top

      File IO Stats

      • ordered by Tablespace, File
      TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
      ACCOUNTS/opt/app/oracle/oradata/dbiwm/accounts.dbf74306.183.241,140000.00
      AC_AUDIT/opt/app/oracle/oradata/dbiwm/ac_audit.dbf31202.3710.9569000.00
      ADMIN/opt/app/oracle/oradata/dbiwm/admin.dbf11303.892.40134000.00
      COSTING/opt/app/oracle/oradata/dbiwm/costing.dbf31802.366.32288000.00
      EXAMPLE/opt/app/oracle/oradata/dbiwm/example01.dbf600.001.006000.00
      HR/opt/app/oracle/oradata/dbiwm/hr.dbf1,26803.334.031,482000.00
      IMOS_DATA/opt/app/oracle/oradata/dbiwm/imos_data.dbf10,35300.727.28133000.00
      PAYROLL/opt/app/oracle/oradata/dbiwm/payroll.dbf97901.6313.071,956000.00
      RMIM/opt/app/oracle/oradata/dbiwm/rmim.dbf3,07808.578.362,964000.00
      SALES/opt/app/oracle/oradata/dbiwm/sales.dbf5,15601.223.55278000.00
      SALES_RMIM/opt/app/oracle/oradata/dbiwm/SALES_RMIM.dbf3,89008.391.432,949000.00
      SH_WH/opt/app/oracle/oradata/dbiwm/sh_wh.dbf22503.163.35196000.00
      SYSAUX/opt/app/oracle/oradata/dbiwm/sysaux01.dbf3,21804.861.1310,522000.00
      SYSTEM/opt/app/oracle/oradata/dbiwm/system01.dbf59,55111.092.162,282000.00
      TEMP/opt/app/oracle/oradata/dbiwm/temp01.dbf139,16620.0414.78123,68820
      UNDOTBS1/opt/app/oracle/oradata/dbiwm/undotbs01.dbf3401.471.008,77601250.72
      USERS/opt/app/oracle/oradata/dbiwm/users01.dbf1,62300.9912.581,140000.00
      WH/opt/app/oracle/oradata/dbiwm/wh.dbf4,88602.397.771,059000.00

      Back to IO Stats

      Back to Top

       

      Buffer Pool Statistics

      • Standard block size Pools D: default, K: keep, R: recycle
      • Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
      PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
      32k8,112100840,504,4315,5643,569000
      D412,653100-1,103,438,857335,69986,20700125

       

      Back to Top

       

      Advisory Statistics

      Back to Top

      Instance Recovery Stats

      • B: Begin snapshot, E: End snapshot
      Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual Redo BlksTarget Redo BlksLog File Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
      B04961281890311892761843200189276
      E018991293351337031843200133703

      Back to Advisory Statistics
      Back to Top

      Buffer Pool Advisory

      • Only rows with estimated physical reads >0 are displayed
      • ordered by Block Size, Buffers For Estimate
      PSize for Est (M)Size FactorBuffers for EstimateEst Phys Read FactorEstimated Physical Reads
      D3200.1039,68030.5287,590,445
      D6400.1979,3609.8628,289,574
      D9600.29119,0403.4910,013,430
      D1,2800.38158,7201.885,397,254
      D1,6000.48198,4001.434,101,127
      D1,9200.58238,0801.283,661,760
      D2,2400.67277,7601.203,444,980
      D2,5600.77317,4401.143,269,819
      D2,8800.87357,1201.083,106,881
      D3,2000.96396,8001.032,942,201
      D3,3281.00412,6721.002,869,792
      D3,5201.06436,4800.962,761,273
      D3,8401.15476,1600.912,597,852
      D4,1601.25515,8400.872,493,659
      D4,4801.35555,5200.852,438,749
      D4,8001.44595,2000.842,411,808
      D5,1201.54634,8800.842,401,252
      D5,4401.63674,5600.832,389,921
      D5,7601.73714,2400.822,356,471
      D6,0801.83753,9200.792,254,094
      D6,4001.92793,6000.681,946,887

      Back to Advisory Statistics

      Back to Top

      PGA Aggr Summary

      • PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
      PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
      94.88600,50932,411

      Back to Advisory Statistics
      Back to Top

      PGA Aggr Target Stats

      • B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
      • Auto PGA Target - actual workarea memory target
      • W/A PGA Used - amount of memory used for all Workareas (manual + auto)
      • %PGA W/A Mem - percentage of PGA memory allocated to workareas
      • %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
      • %Man W/A Mem - percentage of workarea memory under manual control
      PGA Aggr Target(M)Auto PGA Target(M)PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem%Auto W/A Mem%Man W/A MemGlobal Mem Bound(K)
      B1,024876120.930.000.000.000.00104,850
      E1,0242791,088.252.290.21100.000.00104,850

      Back to Advisory Statistics

      Back to Top

      PGA Aggr Target Histogram

      • Optimal Executions are purely in-memory operations
      Low OptimalHigh OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
      2K4K621,277621,27700
      64K128K11,19311,19300
      128K256K3,8503,85000
      256K512K1,6411,64100
      512K1024K142,283142,28300
      1M2M122,622122,62200
      2M4M6,8356,805300
      4M8M8,0767,8162600
      8M16M12,14411,4327120
      16M32M4,2054,141640
      32M64M27326580
      64M128M259160
      128M256M610610
      512M1024M6060
      2G4G1010

      Back to Advisory Statistics
      Back to Top

      PGA Memory Advisory

      • When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
      PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc Count
      1280.132,381,937.801,285,043.5565.0020,932
      2560.252,381,937.801,191,709.3767.0018,554
      5120.502,381,937.80670,431.3178.008,661
      7680.752,381,937.8067,410.2597.00120
      1,0241.002,381,937.8039,076.9998.000
      1,2291.202,381,937.8029,939.6899.000
      1,4341.402,381,937.8029,939.6899.000
      1,6381.602,381,937.8029,939.6899.000
      1,8431.802,381,937.8029,939.6899.000
      2,0482.002,381,937.8029,939.6899.000
      3,0723.002,381,937.8029,939.6899.000
      4,0964.002,381,937.8029,939.6899.000
      6,1446.002,381,937.8029,939.6899.000
      8,1928.002,381,937.8029,939.6899.000

      Back to Advisory Statistics

      Back to Top

      Shared Pool Advisory

      • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
      • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
      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
      2560.57535,31655,4170.9912,9921.0622,568,854
      3040.681007,39755,5600.9912,8491.0522,625,638
      3520.791479,21355,9391.0012,4701.0222,674,305
      4000.8919411,12856,0571.0012,3521.0122,713,962
      4481.0024113,15056,1561.0012,2531.0022,746,021
      4961.1128815,39356,2291.0012,1800.9922,771,659
      5441.2133517,30556,2751.0012,1340.9922,791,701
      5921.3238219,30556,3071.0012,1020.9922,807,031
      6401.4342921,21756,3311.0012,0780.9922,818,776
      6881.5447623,09256,3501.0012,0590.9822,827,621
      7361.6452325,25556,3671.0012,0420.9822,834,267
      7841.7557027,27456,3831.0012,0260.9822,839,529
      8321.8661729,32956,3961.0012,0130.9822,844,151
      8801.9666431,36456,4061.0012,0030.9822,848,592
      9282.0771133,34356,4161.0011,9930.9822,852,780

      Back to Advisory Statistics

      Back to Top

      SGA Target Advisory

      SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
      1,0240.25420,62187,588,751
      2,0480.50262,1205,397,190
      3,0720.75258,2883,444,880
      4,0961.00257,1822,869,777
      5,1201.25256,3592,493,549
      6,1441.50256,2822,411,761
      7,1681.75256,2822,356,374
      8,1922.00256,2831,946,857

      Back to Advisory Statistics
      Back to Top

       

      Streams Pool Advisory

      No data exists for this section of the report.

      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
      321.0017873131.0012,2531.002,706
      481.50321,226131.0012,2531.002,706
      642.00471,634131.0012,2531.002,706

      Back to Advisory Statistics
      Back to Top

       

      Wait Statistics

      Back to Top

      Buffer Wait Statistics

      • ordered by wait time desc, waits desc
      ClassWaitsTotal Wait Time (s)Avg Time (ms)
      undo header12501

      Back to Wait Statistics
      Back to Top

      Enqueue Activity

      • only enqueues with waits are shown
      • Enqueue stats gathered prior to 10g should not be compared with 10g data
      • ordered by Wait Time desc, Waits desc
      Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
      TM-DML109,492109,004486339,484287,397.58
      TX-Transaction (row lock contention)305330232660.00
      RO-Multiple Object Reuse (fast object reuse)818106023.33
      CF-Controlfile Transaction25,83125,8310600.00

      Back to Wait Statistics

      Back to Top

       

      Undo Statistics

      Back to Top

      Undo Segment Summary

      • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
      • STO - Snapshot Too Old count, OOS - Out of Space count
      • Undo segment block stats:
      • uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
      • eS - expired Stolen, eR - expired Released, eU - expired reUsed
      Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOSuS/uR/uU/ eS/eR/eU
      134.3376,8193,5761415/73.61666666666666666666666666666666666670/00/0/0/0/0/0

      Back to Undo Statistics

      Back to Top

      Undo Segment Stats

      • Most recent 35 Undostat rows, ordered by Time desc
      End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOSuS/uR/uU/ eS/eR/eU
      29-May 16:521,3918,4451,80810440/00/0/0/0/0/0
      29-May 16:42676591,2079340/00/0/0/0/0/0
      29-May 16:321,2836786067240/00/0/0/0/0/0
      29-May 16:221,33311,8144010150/00/0/0/0/0/0
      29-May 16:121,3657,4231,55712400/00/0/0/0/0/0
      29-May 16:0224372795513300/00/0/0/0/0/0
      29-May 15:524022435512200/00/0/0/0/0/0
      29-May 15:42405831,8468450/00/0/0/0/0/0
      29-May 15:324913651,45410380/00/0/0/0/0/0
      29-May 15:222299303,5768740/00/0/0/0/0/0
      29-May 15:124863,6202,9758640/00/0/0/0/0/0
      29-May 15:021,21813,0992,3769540/00/0/0/0/0/0
      29-May 14:521,3086,2971,7758440/00/0/0/0/0/0
      29-May 14:421232571,1749340/00/0/0/0/0/0
      29-May 14:321,55353557210240/00/0/0/0/0/0
      29-May 14:222812817777270/00/0/0/0/0/0
      29-May 14:129438212928190/00/0/0/0/0/0
      29-May 14:026273808806290/00/0/0/0/0/0
      29-May 13:529674292816190/00/0/0/0/0/0
      29-May 13:42173541,6654420/00/0/0/0/0/0
      29-May 13:32131201,0637320/00/0/0/0/0/0
      29-May 13:22224674617220/00/0/0/0/0/0
      29-May 13:12376351,17310340/00/0/0/0/0/0
      29-May 13:021,78243349812220/00/0/0/0/0/0
      29-May 12:52274901,58014400/00/0/0/0/0/0
      29-May 12:424852251,17614340/00/0/0/0/0/0
      29-May 12:32375101,96911470/00/0/0/0/0/0
      29-May 12:22327081,36910370/00/0/0/0/0/0
      29-May 12:124742681,8559450/00/0/0/0/0/0
      29-May 12:025785852,2387510/00/0/0/0/0/0
      29-May 11:52913111,6356410/00/0/0/0/0/0
      29-May 11:42934691,0376310/00/0/0/0/0/0
      29-May 11:32435301,2508350/00/0/0/0/0/0
      29-May 11:223,0532592,33012530/00/0/0/0/0/0

      Back to Undo Statistics
      Back to Top

       

      Latch Statistics

      Back to Top

      Latch Activity

      • "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
      • "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
      • "Pct Misses" for both should be very close to 0.0
      Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
      AWR Alerted Metric Element list372,5870.00 00
      Consistent RBA72,1110.00 00
      FAL request queue1,2330.00 00
      FAL subheap alocation1,2330.00 00
      FIB s.o chain latch2780.00 00
      FOB s.o list latch3,1880.00 00
      In memory undo latch577,7210.001.00068,9680.00
      JOX SGA heap latch5,2870.00 00
      JS mem alloc latch1560.00 00
      JS queue access latch1560.00 00
      JS queue state obj latch442,1620.00 00
      JS slv state obj latch3,9060.00 00
      KGX0 01,959,0350.02
      KGX_diag10.00 00
      KMG MMAN ready and startup request latch20,8540.00 00
      KMG resize request state object freelist440.00 00
      KTF sga latch1410.00 017,7000.00
      KWQMN job cache list latch6500.00 00
      MQL Tracking Latch0 01,2220.00
      Memory Management Latch8750.00 020,8490.00
      OS process10,9590.00 00
      OS process allocation27,9160.00 00
      OS process: request allocation3,8000.00 00
      PL/SQL warning settings11,1020.00 00
      SGA IO buffer pool latch520.00 0520.00
      SQL memory manager latch370.00 020,1860.00
      SQL memory manager workarea list latch2,876,7410.000.0000
      Shared B-Tree2,1780.00 00
      X$KSFQP420.00 00
      active checkpoint queue latch53,8930.00 00
      active service list134,9420.00 021,7010.00
      archive control1,4060.00 00
      archive process latch21,4190.011.0000
      begin backup scn array1,0650.00 00
      buffer pool2940.00 00
      cache buffer handles1,420,7350.000.0000
      cache buffers chains6,510,387,0580.030.0021,195,7880.02
      cache buffers lru chain413,7280.000.0001,200,6470.00
      cache table scan latch0 030,0430.00
      cas latch39,7090.00 00
      channel handle pool latch4,9500.00 00
      channel operations parent latch306,2860.000.0000
      checkpoint queue latch2,846,7030.00 0110,8030.00
      client/application info31,3160.00 00
      commit callback allocation8580.00 00
      compile environment latch109,5370.00 00
      dictionary lookup210.00 00
      dml lock allocation217,5550.000.0000
      dummy allocation5,7620.00 00
      enqueue hash chains1,709,9730.000.06012,9610.01
      enqueues1,408,8400.000.0000
      event group latch2,0130.00 00
      file cache latch3,3510.00 00
      global KZLD latch for mem in SGA1,0480.00 00
      hash table column usage latch12,1620.030.00084,879,8310.01
      hash table modification latch1,8110.00 00
      job workq parent latch0 01,9600.00
      job_queue_processes parameter latch1,9990.00 00
      kks stats329,2380.010.0900
      kmcptab latch40.00 00
      kmcpvec latch0 040.00
      krbmrosl9220.00 00
      ksuosstats global area4,1120.00 00
      ktm global data1,9630.00 00
      kwqbsn:qsga2,1780.00 00
      lgwr LWN SCN82,4450.00 00
      library cache19,497,5360.010.140277,6250.12
      library cache load lock87,1580.00 00
      library cache lock3,406,5920.000.00060.00
      library cache lock allocation79,2550.00 00
      library cache pin12,503,3560.000.0000
      library cache pin allocation73,5810.00 00
      list of block allocation5,7210.00 00
      loader state object freelist3,3740.00 00
      longop free list parent7200.00 0460.00
      message pool operations parent latch3,5470.00 00
      messages673,5600.000.0000
      mostly latch-free SCN245,2190.010.0000
      multiblock read objects78,5140.00 00
      ncodef allocation latch1,0380.00 00
      object queue header heap14,2990.00 013,5720.00
      object queue header operation1,745,6850.000.0000
      object stats modification710.00 00
      parallel query alloc buffer8,0760.00 00
      parallel txn reco latch10.00 00
      parameter list4,0190.00 00
      parameter table allocation management4,9920.00 00
      post/wait queue108,5810.00 066,9690.00
      process allocation3,8000.031.0002,0130.00
      process group creation3,8000.00 00
      qmn task queue latch8,7120.00 00
      redo allocation372,2570.000.0001,620,1270.00
      redo copy0 01,620,1500.01
      redo on-disk SCN265,8860.00 00
      redo writing312,6650.000.0000
      resmgr group change latch6,4920.00 00
      resmgr:actses active list5,7130.00 00
      resmgr:actses change group2,0600.00 00
      resmgr:actses change state40.00 00
      resmgr:free threads list5,6880.00 00
      resmgr:resource group CPU method40.00 00
      resmgr:schema config170.00 00
      rm cas latch16,9340.00 00
      row cache objects295,520,9150.060.0001,7980.00
      rules engine aggregate statistics2690.00 00
      rules engine rule set statistics5380.00 00
      sequence cache8,6980.00 00
      session allocation1,923,0410.000.0300
      session idle bit18,816,5300.020.0000
      session state list latch6,6940.00 00
      session switching1,2010.00 00
      session timer21,7010.00 00
      shared pool14,516,2060.020.2710
      simulator hash latch269,535,8970.000.0000
      simulator lru latch267,018,3570.060.0002,499,7710.01
      slave class360.00 00
      slave class create1440.00 00
      sort extent pool35,6680.00 00
      state object free list340.00 00
      statistics aggregation2,3800.00 00
      temp lob duration state obj allocation420.00 00
      temporary table state object allocation40.00 00
      threshold alerts latch9,2210.00 00
      transaction allocation54,8500.00 00
      transaction branch allocation1,0380.00 00
      undo global data870,2730.000.0000
      user lock6,0800.00 00

      Back to Latch Statistics

      Back to Top

      Latch Sleep Breakdown

      • ordered by misses desc
      Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
      cache buffers chains6,510,387,0581,698,2031061,698,097000
      simulator lru latch267,018,357169,70117169,685000
      shared pool14,516,2063,3058912,458000
      library cache19,497,5362,1402951,848000
      kks stats329,23845441000
      session allocation1,923,04138137000
      enqueue hash chains1,709,97316115000
      archive process latch21,419330000
      In memory undo latch577,721110000
      process allocation3,800110000

      Back to Latch Statistics
      Back to Top

      Latch Miss Sources

      • only latches with sleeps are shown
      • ordered by name, sleeps desc
      Latch NameWhereNoWait MissesSleepsWaiter Sleeps
      In memory undo latchktiFlush: child010
      archive process latchkcrrsarc030
      cache buffers chainskcbgtcr: fast path0607
      cache buffers chainskcbchg: kslbegin: bufs not pinned0300
      cache buffers chainskcbgtcr: kslbegin excl0594
      cache buffers chainskcbchg: kslbegin: call CR func040
      cache buffers chainskcbgcur: kslbegin030
      cache buffers chainskcbrls: kslbegin034
      cache buffers chainskcbzib: multi-block read: nowait010
      enqueue hash chainsksqgtl3010
      kks statskks stats alloc/free044
      library cachekglobpn: child:02033
      library cachekglpndl: child: before processing0761
      library cachekglhdgc: child:062
      library cachekgldti: 2child057
      library cachekglpndl: child: after processing049
      library cachekglukp: child0445
      library cachekglhdgn: child:0216
      library cache lockkgllkdl: child: no lock handle01133
      process allocationksucrp010
      session allocationksuxds: KSUSFCLC not set010
      shared poolkghalo0563286
      shared poolkghfrunp: alloc: cursor dur02750
      shared poolkghfrunp: clatch: nowait01560
      shared poolkghfrunp: clatch: wait0809
      shared poolkghalp02142
      shared poolkghupr1015382
      shared poolkghfrunp: alloc: session dur0130
      shared poolkghfre03179
      shared poolkghfrunp: alloc: wait010
      simulator lru latchkcbs_simulate: simulate set0717
      simulator lru latchkcbs_free_granule_sim_buffers040
      simulator lru latchkcbs_shrink_pool030
      simulator lru latchkcbs_resize_pool : interpolation020
      simulator lru latchkcbs_grow_pool010

      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

      • Total Logical Reads: 4,032,601,111
      • Captured Segments account for 91.6% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
      SALESSALES_RMIMPK_ORDSID_ORDERHD INDEX561,569,82413.93
      WHWHPK_GDOSID_GDODT INDEX428,715,26410.63
      SH_WHSH_WHSYS_C006194 INDEX285,100,9447.07
      WHWHGDODT TABLE221,840,9445.50
      HRHRATTENDANCE TABLE196,973,6004.88

      Back to Segment Statistics

      Back to Top

      Segments by Physical Reads

      • Total Physical Reads: 2,399,946
      • Captured Segments account for 0.2% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
      SALESSALES_RMIMQUOTATIONDT TABLE1,6340.07
      SYSSYSAUXSYS_LOB0000008932C00004$$ LOB7910.03
      SALESSALES_RMIMPK_QTSID_QUOTATIONDT INDEX7400.03
      SYSSYSTEMSMON_SCN_TIME TABLE2900.01
      SALESSALES_RMIMRECEIPTHD TABLE1990.01

      Back to Segment Statistics

      Back to Top

      Segments by Row Lock Waits

      • % of Capture shows % of row lock waits for each top segment compared
      • with total row lock waits for all segments captured by the Snapshot
      OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
      SALESSALES_RMIMQUOTATIONDT TABLE42447.32
      RMIMRMIMPK_SIN_NO INDEX16117.97
      SALESSALES_RMIMQUOTATIONHD TABLE10011.16
      DEALERSUSERSD_QUOTATIONDT TABLE444.91
      RMIMSALES_RMIMPK_POID_POHD INDEX404.46

      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

      No data exists for this section of the report.

      Back to Segment Statistics
      Back to Top

       

      Dictionary Cache Stats

      • "Pct Misses" should be very low (< 2% in most cases)
      • "Final Usage" is the number of cache entries being used
      CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
      dc_awr_control1,1180.000 341
      dc_constraints3333.330 331
      dc_files510.000 017
      dc_global_oids2,192,0100.000 0161
      dc_hintsets2100.000 00
      dc_histogram_data4,659,1750.020 6079,119
      dc_histogram_defs1,576,9280.430 1617,915
      dc_object_grants724,1580.020 01,359
      dc_object_ids3,733,5040.000 121,889
      dc_objects549,8390.150 4563,158
      dc_profiles2,0660.000 01
      dc_rollback_segments21,8460.000 036
      dc_segments964,9500.070 2632,014
      dc_sequences4940.000 4949
      dc_table_scns6100.000 00
      dc_tablespace_quotas2718.520 273
      dc_tablespaces42,317,7180.000 020
      dc_usernames70,6270.020 062
      dc_users45,532,2150.000 094
      global database name140.000 01
      outstanding_alerts4,1873.220 26937

       

      Back to Top

       

      Library Cache Activity

      • "Pct Misses" should be very low
      NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
      BODY11,7670.3753,7571.597820
      CLUSTER1,2780.162,4961.64390
      INDEX3558.1788821.851570
      JAVA DATA230.000 00
      JAVA RESOURCE12015.0071124.75790
      SQL AREA75,35268.123,136,8495.5043,2152,155
      TABLE/PROCEDURE219,7641.223,708,6771.1219,7250
      TRIGGER1,2452.977,0504.962960


      Back to Top

       

      Memory Statistics

      Back to Top

      Process Memory Summary

      • B: Begin snap E: End snap
      • All rows below contain absolute values (i.e. not diffed over the interval)
      • Max Alloc is Maximum PGA Allocation size at snapshot time
      • Hist Max Alloc is the Historical Max Allocation for still-connected processes
      • ordered by Begin/End snapshot, Alloc (MB) desc
      CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
      BOther105.76 3.656.9022242929
      Freeable13.880.000.770.522 1818
      SQL0.980.490.050.050891918
      PL/SQL0.340.180.010.02002727
      EOther927.86 3.6417.82230235255255
      Freeable133.440.000.780.302 172172
      SQL13.036.880.050.122144245231
      JAVA10.019.972.000.102355
      PL/SQL4.092.980.020.0200253253

      Back to Memory Statistics

      Back to Top

      SGA Memory Summary

      SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
      Database Buffers3,909,091,3283,758,096,384
      Fixed Size2,026,296
      Redo Buffers14,749,696
      Variable Size369,099,976520,094,920

      Back to Memory Statistics
      Back to Top

      SGA breakdown difference

      • ordered by Pool, Name
      • N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
      PoolNameBegin MBEnd MB% Diff
      javafree memory15.9914.70-8.03
      javajoxlod exec hp15.2616.558.42
      javajoxs heap0.750.750.00
      largePX msg pool1.031.030.00
      largefree memory14.9714.970.00
      sharedASH buffers15.5015.500.00
      sharedCCursor13.7914.605.81
      sharedFileOpenBlock4.40 -100.00
      sharedKCB Table Scan Buffer4.00 -100.00
      sharedKGH: NO ACCESS15.9547.88200.10
      sharedKGLS heap5.18 -100.00
      sharedKQR L PO3.30 -100.00
      sharedKQR M PO9.216.04-34.40
      sharedKQR M SO5.82 -100.00
      sharedKSFD SGA I/O b4.00 -100.00
      sharedKTI-UNDO4.10 -100.00
      sharedPCursor10.1512.0018.26
      sharedPL/SQL MPCODE2.96 -100.00
      sharedXDB Schema Cac7.637.630.00
      shareddb_block_hash_buckets16.8116.810.00
      sharedevent statistics per sess4.844.840.00
      sharedfree memory6.62103.941470.84
      sharedkglsim hash table bkts4.00 -100.00
      sharedkglsim heap5.115.130.45
      sharedkglsim object batch10.8110.810.00
      sharedlibrary cache41.6546.1710.86
      sharedprivate strands7.687.680.00
      sharedrow cache7.137.130.00
      sharedsql area22.4968.34203.90
      sharedtrace buffer4.31 -100.00
      buffer_cache3,728.003,584.00-3.86
      fixed_sga1.931.930.00
      log_buffer14.0714.070.00

      Back to Memory Statistics

      Back to Top

       

      Streams Statistics

      Back to Top

       

      Streams CPU/IO Usage

      No data exists for this section of the report.

      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 Apply

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Buffered Queues

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Buffered Subscribers

      No data exists for this section of the report.

      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

       

       

      Resource Limit Stats

      No data exists for this section of the report.


      Back to Top

       

      init.ora Parameters

      Parameter NameBegin valueEnd value (if different)
      audit_file_dest/opt/app/oracle/admin/dbiwm/adump 
      background_dump_dest/opt/app/oracle/admin/dbiwm/bdump 
      compatible10.2.0.1.0 
      control_files/opt/app/oracle/oradata/dbiwm/control01.ctl, /opt/app/oracle/oradata/dbiwm/control02.ctl, /opt/app/oracle/oradata/dbiwm/control03.ctl 
      core_dump_dest/opt/app/oracle/admin/dbiwm/cdump 
      db_32k_cache_size268435456 
      db_block_size8192 
      db_domain   
      db_file_multiblock_read_count16 
      db_namedbiwm 
      db_recovery_file_dest/opt/app/oracle/flash_recovery_area 
      db_recovery_file_dest_size2147483648 
      dispatchers(PROTOCOL=TCP) (SERVICE=dbiwmXDB) 
      job_queue_processes10 
      log_archive_dest_1location=/opt/app/oracle/archivelog 
      open_cursors300 
      pga_aggregate_target1073741824 
      processes500 
      remote_login_passwordfileEXCLUSIVE 
      sessions555 
      sga_max_size4294967296 
      sga_target4294967296 
      undo_managementAUTO 
      undo_tablespaceUNDOTBS1 
      user_dump_dest/opt/app/oracle/admin/dbiwm/udump 


      Back to Top

       

       

       

       

       

      End of Report