4 Replies Latest reply: Mar 26, 2014 4:44 AM by user11978929 RSS

    Analyze why aud$ table making more IO?

    user11978929

      Analyze why aud$ table making more IO. Looking for urgent help

      WORKLOAD REPOSITORY report for

      DB NameDB IdInstanceInst numReleaseRACHost
      GLODIST964540828glodist110.2.0.4.0NOxsj-psdbgen01
      Snap IdSnap TimeSessionsCursors/Session
      Begin Snap:3799524-Mar-14 08:00:59528.2
      End Snap:3800524-Mar-14 18:00:18628.3
      Elapsed: 599.32 (mins)
      DB Time: 529.35 (mins)

       

      Report Summary

      Cache Sizes

      BeginEnd
      Buffer Cache:224M224MStd Block Size:8K
      Shared Pool Size:300M300MLog Buffer:2,112K

      Load Profile

      Per SecondPer Transaction
      Redo size:37,208.4237,830.37
      Logical reads:3,540.413,599.59
      Block changes:196.65199.93
      Physical reads:2,222.122,259.26
      Physical writes:8.108.24
      User calls:22.1822.55
      Parses:2.822.87
      Hard parses:0.040.04
      Sorts:4.234.30
      Logons:0.040.04
      Executes:24.6125.02
      Transactions:0.98
      % Blocks changed per Read:5.55Recursive Call %:83.54
      Rollback per transaction %:0.03Rows per Sort:71.66

      Instance Efficiency Percentages (Target 100%)

      Buffer Nowait %:100.00Redo NoWait %:99.99
      Buffer Hit %:37.40In-memory Sort %:100.00
      Library Hit %:99.54Soft Parse %:98.46
      Execute to Parse %:88.53Latch Hit %:100.00
      Parse CPU to Parse Elapsd %:70.72% Non-Parse CPU:99.79

      Shared Pool Statistics

      BeginEnd
      Memory Usage %:75.4889.20
      % SQL with executions>1:90.3794.14
      % Memory for SQL w/exec>1:86.8588.94

      Top 5 Timed Events

      EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
      PX Deq Credit: send blkd40,80615,62938349.2Other
      CPU time 10,294 32.4
      db file scattered read2,703,0845,088216.0User I/O
      db file sequential read1,024,3381,82125.7User I/O
      log file parallel write42,965415101.3System I/O

      Main Report

       

      Back to Top   

      Wait Events Statistics

      Back to Top   

      Time Model Statistics

      • Total time in database user-calls (DB Time): 31761.3s
      • 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 time31,377.5598.79
      DB CPU10,293.5932.41
      connection management call elapsed time86.790.27
      parse time elapsed31.390.10
      hard parse elapsed time17.610.06
      PL/SQL execution elapsed time14.610.05
      sequence load elapsed time9.100.03
      PL/SQL compilation elapsed time3.390.01
      repeated bind elapsed time0.180.00
      hard parse (bind mismatch) elapsed time0.090.00
      hard parse (sharing criteria) elapsed time0.090.00
      failed parse elapsed time0.040.00
      DB time31,761.28
      background elapsed time1,189.05
      background cpu time421.22

        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
      Other42,73419.1515,6323661.21
      User I/O3,755,0980.007,0082106.17
      System I/O140,2210.0080563.96
      Commit15,7087.68246160.44
      Scheduler4,5270.0075170.13
      Network915,0740.0038025.87
      Configuration41764.2722520.01
      Concurrency4180.0014330.01
      Administrative300.00113670.00
      Application160.001400.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
      PX Deq Credit: send blkd40,80618.9515,6293831.15
      db file scattered read2,703,0840.005,088276.43
      db file sequential read1,024,3380.001,821228.96
      log file parallel write42,9650.00415101.21
      log file sync15,7087.68246160.44
      control file parallel write15,7740.00187120.45
      db file parallel write29,1550.0015750.82
      resmgr:cpu quantum4,5270.0075170.13
      direct path read7,7040.004150.22
      log file sequential read1,8150.0033180.05
      direct path write temp4,0560.002770.11
      db file parallel read8,5560.002430.24
      SQL*Net more data to client127,4680.002303.60
      log file switch completion15710.83221370.00
      os thread startup690.00121770.00
      switch logfile command300.00113670.00
      SQL*Net more data from dblink1180.008640.00
      SQL*Net message to client783,7450.007022.16
      Log archive I/O1,4130.00640.04
      control file sequential read48,8630.00501.38
      direct path read temp4,0360.00410.11
      direct path write3,2290.00310.09
      latch free870.002250.00
      latch: cache buffers chains1740.00180.00
      log file single write2360.00130.01
      enq: PR - contention20.0013620.00
      read by other session950.00170.00
      SQL*Net break/reset to client100.001640.00
      SQL*Net more data from client3,5160.00100.10
      rdbms ipc reply3630.00010.01
      buffer busy waits1200.00020.00
      log file switch (private strand flush incomplete)40.000420.00
      LGWR wait for redo copy5940.17000.02
      latch: shared pool110.00040.00
      PX Deq: Signal ACK492.04010.00
      latch: session allocation340.00010.00
      latch: In memory undo latch330.00010.00
      PX qref latch76558.95000.02
      latch: object queue header operation130.00010.00
      enq: KO - fast object checkpoint60.00010.00
      SQL*Net message to dblink2260.00000.01
      undo segment extension25698.05000.01
      latch: library cache110.00000.00
      reliable message80.00000.00
      latch: redo allocation40.00010.00
      latch: cache buffers lru chain70.00000.00
      enq: PS - contention20.00000.00
      SQL*Net more data to dblink10.00000.00
      SQL*Net message from client783,7350.00842,558107522.16
      PX Idle Wait144,84999.99282,77219524.10
      Streams AQ: waiting for messages in the queue7,184100.0035,05748800.20
      Streams AQ: qmn slave idle wait1,2800.0034,983273300.04
      Streams AQ: qmn coordinator idle wait2,60050.8834,983134550.07
      PX Deq: Execution Msg10,48286.2317,85017030.30
      Streams AQ: waiting for time management or cleanup tasks8100.008,65010812410.00
      PX Deq Credit: need buffer6,43113.191,6672590.18
      PX Deq: Table Q Normal82,3550.0351862.33
      SQL*Net message from dblink2260.0021930.01
      PX Deq: Execute Reply7020.7115210.02
      single-task message110.0011360.00
      PX Deq: Msg Fragment740.001110.00
      class slave wait210.00070.00
      PX Deq: Parse Reply210.00030.00
      PX Deq: Join ACK570.00010.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 write42,9640.00415101.21
      control file parallel write15,7740.00187120.45
      db file parallel write29,1550.0015750.82
      log file sequential read1,8150.0033180.05
      db file sequential read6,6560.00810.19
      Log archive I/O1,4130.00640.04
      db file scattered read6300.00580.02
      control file sequential read31,7950.00400.90
      os thread startup210.0031590.00
      direct path write3,2100.00310.09
      log file single write2360.00130.01
      events in waitclass Other9810.10110.03
      direct path read4,2160.00000.12
      buffer busy waits210.00000.00
      latch: cache buffers chains30.00000.00
      rdbms ipc message166,55376.47411,66024724.71
      pmon timer11,975100.0035,05129270.34
      Streams AQ: qmn slave idle wait1,2800.0034,983273300.04
      Streams AQ: qmn coordinator idle wait2,60050.8834,983134550.07
      smon timer2906.9034,5471191280.01
      Streams AQ: waiting for time management or cleanup tasks8100.008,65010812410.00

      Back to Wait Events Statistics
      Back to Top

      Operating System Statistics

      StatisticTotal
      AVG_BUSY_TIME3,225,705
      AVG_IDLE_TIME367,471
      AVG_IOWAIT_TIME0
      AVG_SYS_TIME405,795
      AVG_USER_TIME2,818,869
      BUSY_TIME25,814,038
      IDLE_TIME2,947,890
      IOWAIT_TIME0
      SYS_TIME3,254,672
      USER_TIME22,559,366
      LOAD7
      OS_CPU_WAIT_TIME6,716,800
      RSRC_MGR_CPU_WAIT_TIME7,333
      VM_IN_BYTES4,978,737,152
      VM_OUT_BYTES0
      PHYSICAL_MEMORY_BYTES46,324,703,232
      NUM_CPUS8

      Back to Wait Events Statistics
      Back to Top

      Service Statistics

      • ordered by DB Time
      Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
      SYS$USERS28,520.807,364.5043,945,07090,464,286
      glodist3,246.302,931.3035,932,66336,447,697
      SYS$BACKGROUND0.000.0033,845405,030

        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
      SYS$USERS224225359852633210513011019060433742
      glodist1488152965773920032828
      SYS$BACKGROUND247185750453350000

        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,4322318432.2426.55czbcbbrs9m00boracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...
      4,3374922168.4913.658bu6baap1snwyoracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...
      3,2252,9133,2550.9910.15376u7a6frhp20JDBC Thin Client select * from dba_audit_trail ...
      3,1201913119.849.82868n3yds47cg4oracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...
      1,2517342062.553.943qfdsumvk7yt5SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      5801801579.811.8304384fcb6137joracle@lilo (TNS V1-V3) SELECT "A1"."GD_CUST_ID", "A1"...
      5693212028.461.794yc06ra33csvzSQL*Plus DECLARE v_print1 varchar2(2000...
      5613181929.521.77fhgft51ugknqdSQL*Plus SELECT DISTINCT C.GD_CUST_ID, ...
      5092171926.811.607g3wv47q7aa40SQL*Plus SELECT * FROM XXSUB_QUOTE X WH...
      5011231050.151.584mx0ubtp5b8dsSQL*Plus DECLARE /* $Id: gd_insert_mis...
      5011231050.121.582y41msd1654mrSQL*Plus SELECT DISTINCT POS_PART_NO , ...
      478193548.861.516gcd7yy7jjw0uSQL*Plus DECLARE v_print1 varchar2(2000...
      4463241453.071.405md9mz2rrs0jcSQL*Plus SELECT COUNT(*) FROM GD_MKTG_Q...
      4301771043.041.367tr0031mjpkscSQL*Plus begin publish_to_pos(FALSE); e...
      4291761042.891.35gdvvuza0axnhbSQL*Plus UPDATE GD_EDI_POS_ITEMS SET TP...
      4171711416.521.313r45tkag2xj15SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      396253579.261.2520hjqfdkcm6umSQL*Plus SELECT DISTINCT c.disti...
      375280575.061.18dnzbcmgb9h0phSQL*Plus SELECT DISTINCT c.disti...
      3362012001.681.06928mhrahfhkpdJDBC Connect Client SELECT a.processed_records, b...

        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
      2,9133,2253,2550.8910.15376u7a6frhp20JDBC Thin Client select * from dba_audit_trail ...
      7341,2512036.683.943qfdsumvk7yt5SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      3244461452.231.405md9mz2rrs0jcSQL*Plus SELECT COUNT(*) FROM GD_MKTG_Q...
      3215692016.041.794yc06ra33csvzSQL*Plus DECLARE v_print1 varchar2(2000...
      3185611916.751.77fhgft51ugknqdSQL*Plus SELECT DISTINCT C.GD_CUST_ID, ...
      280375555.971.18dnzbcmgb9h0phSQL*Plus SELECT DISTINCT c.disti...
      253396550.571.2520hjqfdkcm6umSQL*Plus SELECT DISTINCT c.disti...
      2175091911.451.607g3wv47q7aa40SQL*Plus SELECT * FROM XXSUB_QUOTE X WH...
      2013362001.011.06928mhrahfhkpdJDBC Connect Client SELECT a.processed_records, b...
      193478543.571.516gcd7yy7jjw0uSQL*Plus DECLARE v_print1 varchar2(2000...
      1805801179.771.8304384fcb6137joracle@lilo (TNS V1-V3) SELECT "A1"."GD_CUST_ID", "A1"...
      1774301017.671.367tr0031mjpkscSQL*Plus begin publish_to_pos(FALSE); e...
      1764291017.611.35gdvvuza0axnhbSQL*Plus UPDATE GD_EDI_POS_ITEMS SET TP...
      1714171171.301.313r45tkag2xj15SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      1235011012.351.584mx0ubtp5b8dsSQL*Plus DECLARE /* $Id: gd_insert_mis...
      1235011012.341.582y41msd1654mrSQL*Plus SELECT DISTINCT POS_PART_NO , ...
      494,337224.3813.658bu6baap1snwyoracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...
      238,432122.7726.55czbcbbrs9m00boracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...
      193,120119.319.82868n3yds47cg4oracle@lilo (TNS V1-V3) SELECT "A1"."PART_NUMBER", "A1...

        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: 127,310,179
      • Captured SQL account for 93.8% of Total
      Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      36,436,5013,25511,194.0128.622912.733224.54376u7a6frhp20JDBC Thin Client select * from dba_audit_trail ...
      19,054,32420952,716.2014.97320.81569.144yc06ra33csvzSQL*Plus DECLARE v_print1 varchar2(2000...
      19,042,900191,002,257.8914.96318.33560.92fhgft51ugknqdSQL*Plus SELECT DISTINCT C.GD_CUST_ID, ...
      6,397,99910639,799.905.03123.46501.464mx0ubtp5b8dsSQL*Plus DECLARE /* $Id: gd_insert_mis...
      6,397,83910639,783.905.03123.42501.212y41msd1654mrSQL*Plus SELECT DISTINCT POS_PART_NO , ...
      4,920,29520246,014.753.86733.691251.063qfdsumvk7yt5SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      3,380,91313,380,913.002.66179.77579.8104384fcb6137joracle@lilo (TNS V1-V3) SELECT "A1"."GD_CUST_ID", "A1"...
      3,340,8087,207463.552.6245.8451.02bmj5us86kyf8aJDBC Connect Client SELECT * FROM XXPUB_FULFILLM...
      2,443,24512,443,245.001.92171.30416.523r45tkag2xj15SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      2,391,92314516,496.021.88324.06445.565md9mz2rrs0jcSQL*Plus SELECT COUNT(*) FROM GD_MKTG_Q...
      2,177,06120010,885.311.71201.22335.96928mhrahfhkpdJDBC Connect Client SELECT a.processed_records, b...
      2,125,9335439,369.131.67192.78478.356gcd7yy7jjw0uSQL*Plus DECLARE v_print1 varchar2(2000...
      1,863,0287,193259.011.4630.1432.511y2pqs88ub6uhJDBC Connect Client SELECT * FROM XXPUB_CUST_LOC...
      1,379,970184,7447.471.08100.75131.07g294qgxf8jqhbJDBC Connect Client INSERT INTO XXSUB_ITEM_ATTR ( ...
      1,288,72410128,872.401.01176.72430.387tr0031mjpkscSQL*Plus begin publish_to_pos(FALSE); e...
      1,284,09110128,409.101.01176.14428.92gdvvuza0axnhbSQL*Plus UPDATE GD_EDI_POS_ITEMS SET TP...
      1,284,05010128,405.001.01132.85182.08fjxusy4bv8b85SQL*Plus select DISTINCT TPOS_FILENAME ...

        Back to SQL Statistics

      Back to Top 

      SQL ordered by Reads

      • Total Disk Reads: 79,905,605
      • Captured SQL account for 96.5% of Total
      Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      35,921,6213,25511,035.8344.962912.733224.54376u7a6frhp20JDBC Thin Client select * from dba_audit_trail ...
      4,827,07820241,353.906.04733.691251.063qfdsumvk7yt5SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      2,376,10214516,386.912.97324.06445.565md9mz2rrs0jcSQL*Plus SELECT COUNT(*) FROM GD_MKTG_Q...
      2,163,22120010,816.112.71201.22335.96928mhrahfhkpdJDBC Connect Client SELECT a.processed_records, b...
      1,283,93410128,393.401.61176.72430.387tr0031mjpkscSQL*Plus begin publish_to_pos(FALSE); e...
      1,283,80310128,380.301.61176.14428.92gdvvuza0axnhbSQL*Plus UPDATE GD_EDI_POS_ITEMS SET TP...
      1,277,30010127,730.001.60132.85182.08fjxusy4bv8b85SQL*Plus select DISTINCT TPOS_FILENAME ...
      1,209,9517416,350.691.51174.14219.9662wkpdthx9k57SQL*Plus UPDATE GD_MKTG_QUOTE_HEADERS S...
      1,168,1801961,483.161.46217.49509.357g3wv47q7aa40SQL*Plus SELECT * FROM XXSUB_QUOTE X WH...
      1,140,7409126,748.891.43106.88147.071fuwtfb2z47ahSQL*Plus select DISTINCT TPOS_FILENAME ...
      1,113,68211,113,682.001.39162.38256.743bduct0mt1hhcoracle@lilo (TNS V1-V3) SELECT "PART_NUMBER", "PRODUCT...
      1,113,68111,113,681.001.39132.14199.314djmu315d0jvnoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39126.71175.435cfvfw9wpx8t5oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39125.32157.565ygxta8q6thgnoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39122.67147.896qpsh27z33zy5oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39125.23148.95cpwrux6mwsfy6oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39123.06141.47cvzb1cvmdpp34oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39121.55131.70fchznr7bs81dqoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68111,113,681.001.39126.05155.03fy7595g0tp46voracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,68011,113,680.001.39128.03181.6460pzpx9jt35qnoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,113,61511,113,615.001.39122.57126.702hp96qz5uuwp6oracle@lilo (TNS V1-V3) SELECT "PART_NUMBER", "PRODUCT...
      1,113,51211,113,512.001.39125.72134.8079vkpt3hzg03woracle@lilo (TNS V1-V3) SELECT "PART_NUMBER", "PRODUCT...
      1,112,19111,112,191.001.39119.38136.125ptzsdnruqrhworacle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,112,17611,112,176.001.39123.08140.11dwt9jadadx51aoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,112,12011,112,120.001.39122.56142.2572bpcq74qrwatoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,112,00611,112,006.001.39129.90206.14dngx7pmc9ka72oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,111,99211,111,992.001.39127.89170.991gkzk315sq90xoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,111,99211,111,992.001.39125.71167.323rbh076r95nuvoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,106,80911,106,809.001.39124.36178.346jgfda6a3r7w0oracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      1,055,50511,055,505.001.32112.95128.670pj1cq7zq8x6joracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      909,1771909,177.001.14171.30416.523r45tkag2xj15SQL*Plus DECLARE v_msg VARCHAR2(200); ...
      860,9730 1.0899.31172.88bnzuc1x96jbkdoracle@lilo (TNS V1-V3) SELECT /*+ NOREWRITE */ DECODE...
      804,9425160,988.401.01252.84396.3220hjqfdkcm6umSQL*Plus SELECT DISTINCT c.disti...

        Back to SQL Statistics

      Back to Top 

      SQL ordered by Executions

      • Total Executions: 885,036
      • Captured SQL account for 60.8% of Total
      Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text
      184,744184,7441.000.000.00g294qgxf8jqhbJDBC Connect Client INSERT INTO XXSUB_ITEM_ATTR ( ...
      121,090121,0901.000.000.000nrch9d09zjfbJDBC Connect Client INSERT INTO XXSUB_ITEM_XREF_AT...
      65,54765,5471.000.000.00gfq05puhdqs8vJDBC Connect Client INSERT INTO XXSUB_BOM_COMP_REF...
      35,04935,0491.000.000.00bmuqkwgyxq95mJDBC Connect Client INSERT INTO XXSUB_BOM_COMP_REF...
      12,59512,5951.000.000.0083v08gtfj7q88JDBC Connect Client INSERT INTO XXSUB_ITEM_XREF ( ...
      11,633461,75939.690.010.02749tad6uzbksvSQL*Plus DELETE FROM XXSUB_ITEM_XREF_AT...
      11,633539,71846.400.010.01artwjs76kssvySQL*Plus DELETE FROM XXSUB_ITEM_ATTR WH...
      9,6309,6301.000.000.00b41awu29rqx6sJDBC Connect Client INSERT INTO XXSUB_BOM_COMP_ATT...
      7,2071,2580.170.010.01bmj5us86kyf8aJDBC Connect Client SELECT * FROM XXPUB_FULFILLM...
      7,193230.000.000.001y2pqs88ub6uhJDBC Connect Client SELECT * FROM XXPUB_CUST_LOC...

        Back to SQL Statistics

      Back to Top 

      SQL ordered by Parse Calls

      • Total Parse Calls: 101,545
      • Captured SQL account for 73.5% of Total
      Parse CallsExecutions % Total ParsesSQL IdSQL ModuleSQL Text
      7,2077,2077.10bmj5us86kyf8aJDBC Connect Client SELECT * FROM XXPUB_FULFILLM...
      7,1937,1937.081y2pqs88ub6uhJDBC Connect Client SELECT * FROM XXPUB_CUST_LOC...
      7,1877,1877.0858wzdu30azxcrJDBC Connect Client SELECT * FROM XXPUB_GENERIC ...
      7,1867,1867.085cku00pvuksrkJDBC Connect Client SELECT * FROM XXPUB_DACLAIM ...
      5,2035,2035.124m7m0t6fjcs5x  update seq$ set increment$=:2,...
      4,0274,0273.970h6b2sajwb74n  select privilege#, level from ...
      3,0853,0853.04gd38axjtgv5uqJDBC Connect Client select XXSUB_ITEM_SEQ.nextval ...
      2,9412,9412.9081ky0n97v4zsgemagent_SQL_oracle_database /* OracleOEM */ select s.sid, ...
      1,8521,8521.82350f5yrnnmshs  lock table sys.mon_mods$ in ex...
      1,8521,8521.82g00cj285jmgsw  update sys.mon_mods$ set inser...
      1,5431,5431.520gy268rg3cxj3JDBC Connect Client select XXSUB_BOM_SEQ.nextval f...
      1,3821,3821.364cbf4gc30cwn7JDBC Thin Client SELECT SERVER_NAME FROM SYS.XX...
      1,3821,3821.3666snhg0v0c18dJDBC Thin Client SELECT USERNAME, OSUSER, MAC...
      1,3821,3821.368suz9a9ykkwwqJDBC Thin Client DECLARE tserver VARCHAR...
      1,3681,3681.35459f3z9u4fb3u  select value$ from props$ wher...
      1,3601,3601.349wapxfsbzds5hJDBC Thin Client INSERT INTO SYS.XXSOX_INVALIDC...
      1,3521,3521.330k8522rmdzg4k  select privilege# from sysauth...
      1,3521,3611.3315zytu14qzw6pJDBC Thin Client insert into sys.aud$( sessioni...
      1,3511,3511.330ws7ahf1d78qa  select SYS_CONTEXT('USERENV', ...
      1,3461,3461.331rd8um1taq1fn  update sys.aud$ set action#=:2...
      1,3211,3211.305ur69atw3vfhj  select decode(failover_method,...
      1,1101,1101.095aa4qy7098w5k  SELECT seq, message#, fac, hdr...

        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

      No data exists for this section of the report.

      Back to SQL Statistics
      Back to Top

       

      Complete List of SQL Text

      SQL IdSQL Text
      04384fcb6137jSELECT "A1"."GD_CUST_ID", "A1"."HUB_PARTY_ID", "A1"."PARTY_NAME", "A1"."CITY", "A1"."XLNX_HUB_GU_PARTY_ID", "A1"."XLNX_HUB_XU_PARTY_ID", "A1"."CREATION_DATE", "A1"."COUNTRY", "A1"."CREATED_BY" FROM "GD"."GD_CUSTOMERS_3MONTHS_DLVIEW" "A1"
      0gy268rg3cxj3select XXSUB_BOM_SEQ.nextval from dual
      0h6b2sajwb74nselect privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      0k8522rmdzg4kselect privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
      0nrch9d09zjfbINSERT INTO XXSUB_ITEM_XREF_ATTR ( SEQUENCE_NO, XREF_RELATIONSHIP_NAME, XREF_ITEM_TYPE, XREF_ITEM_NAME, XREF_ITEM_REVISION, ATTR_TYPE, ATTR_NAME, ATTR_VALUE) VALUES (:v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7)
      0pj1cq7zq8x6jSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'HEWLETT%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR ("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1 "."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      0ws7ahf1d78qaselect SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
      15zytu14qzw6pinsert into sys.aud$( sessionid, entryid, statement, ntimestamp#, userid, userhost, terminal, action#, returncode, obj$creator, obj$name, auth$privileges, auth$grantee, new$owner, new$name, ses$actions, ses$tid, logoff$pread, logoff$lwrite, logoff$dead, comment$text, spare1, spare2, priv$used, clientid, sessioncpu, proxy$sid, user$guid, instance#, process#, xid, scn, auditid, sqlbind, sqltext) values(:1, :2, :3, SYS_EXTRACT_UTC(SYSTIMESTAMP), :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)
      1fuwtfb2z47ahselect DISTINCT TPOS_FILENAME from gd_edi_pos_items where tpos_process_ind='N' and tpos_territory in ('EUROPE', 'JAPAN', 'SE ASIA')
      1gkzk315sq90xSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'LSI CORP%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBST R("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A 1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      1rd8um1taq1fnupdate sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(systimestamp as date), logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
      1y2pqs88ub6uhSELECT * FROM XXPUB_CUST_LOC WHERE WHEN_PROCESSED = '01-jan-1970' order by SEQNO
      20hjqfdkcm6umSELECT DISTINCT c.disti_edi_849_filename || '!' || a.dar_session_no FROM gd_edi_844_rejects a, gd_edi_844_msgs b, gd_distributors c WHERE a.dar_header_fk = b.header_fk AND a.dar_contract_fk = b.contract_fk AND a.dar_pad_pk = b.pad_pk AND a.dar_session_no = b.session_no AND a.dar_process_ind != 'Y' AND b.created_by = 'EDI-DA-844' AND DECODE(RTRIM(TO_CHAR(TO_DATE(a.created_date), 'DAY')), 'THURSDAY', TRUNC(a.created_date+4), 'FRIDAY', TRUNC(a.created_date+4), 'SATURDAY', TRUNC(a.created_date+3), TRUNC(a.created_date + 2)) <= TRUNC(SYSDATE+4) AND b.disti_name = c.disti_name AND c.disti_name = 'AVNET LOGISTICS, US L.P.' AND not exists ( Select 1-- Added not to process more than one file CAR#6286 from gd_edi_845_header where trunc(log_date) >= trunc(sysdate) - 7 -- this condition will improve performance not to check old data and trunc(LOG_DATE) = trunc(a.created_date) )
      2hp96qz5uuwp6SELECT "PART_NUMBER", "PRODUCT_LINE", "ACCOUNT", "REVENUE_AMOUNT", "PERIOD_NAME", "REVENUE_TYPE", "PART_FAMILY", "COMPANY", "ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "O100663" WHERE "ACCOUNT"='4400' AND "REVENUE_TYPE" NOT LIKE 'INT%' AND UPPER("PERIOD_NAME")='NOV-14' AND "PART_NUMBER" LIKE '%-SERV'
      2y41msd1654mrSELECT DISTINCT POS_PART_NO , POS_TXN_DATE , 'SC' CST_TYPE , DECODE(C.PART_NO, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') DESCR FROM COST_PART_VALIDATION C , GD_POS_ITEMS L WHERE POS_PERIOD_NAME = (SELECT VAL_VALUE FROM GD_VALIDATIONS WHERE VAL_TYPE='CURRENT_ACTG_PERIOD' ) AND POS_PRODUCTION_FLAG = 'Y' AND NOT EXISTS (SELECT 1 FROM GD_PART_COSTS WHERE CST_PART_NO = L.POS_PART_NO AND L.POS_TXN_DATE BETWEEN CST_EFF_DATE AND NVL(CST_EXP_DATE, L.POS_TXN_DATE) AND CST_TYPE = 'SC' ) AND C.PART_NO(+) = SUBSTR(L.POS_PART_NO, 1, 3) UNION SELECT DISTINCT POS_PART_NO , POS_TXN_DATE , 'OH' CST_TYPE , DECODE(C.PART_NO, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') DESCR FROM COST_PART_VALIDATION C , GD_POS_ITEMS L WHERE POS_PERIOD_NAME = (SELECT VAL_VALUE FROM GD_VALIDATIONS WHERE VAL_TYPE='CURRENT_ACTG_PERIOD' ) AND POS_PRODUCTION_FLAG = 'Y' AND NOT EXISTS (SELECT 1 FROM GD_PART_COSTS WHERE CST_PART_NO = L.POS_PART_NO AND L.POS_TXN_DATE BETWEEN CST_EFF_DATE AND NVL(CST_EXP_DATE, L.POS_TXN_DATE) AND CST_TYPE = 'OH' ) AND C.PART_NO(+) = SUBSTR(L.POS_PART_NO, 1, 3) UNION SELECT D.POS_PARTNUMBER_ID , DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) POS_TXN_DATE , 'SC' CST_TYPE , DECODE(C.PART_NO, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') DESC R FROM COST_PART_VALIDATION C , XXSUB_POS_DETAIL D , XXSUB_POS_HEADER H , XXSUB_POS M WHERE TRUNC(WHEN_PROCESSED) = '01-JAN-1970' AND M.SEQUENCE_NO = H.SEQUENCE_NO AND H.SEQUENCE_NO = D.SEQUENCE_NO AND H.POS_HEADER_ID = D.POS_HEADER_ID AND NOT EXISTS (SELECT 1 FROM GD_PART_COSTS WHERE CST_PART_NO = D.POS_PARTNUMBER_ID AND DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) BETWEEN CST_EFF_DATE AND NVL(CST_EXP_DATE, DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) ) AND CST_TYPE = 'SC' ) AND C.PART_NO(+) = SUBSTR(D.POS_PARTNUMBER_ID, 1, 3) UNION SELECT D.POS_PARTNUMBER_ID , DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) POS_TXN_DATE , 'OH' CST_TYPE , DECODE(C.PART_NO, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') DESCR FROM COST_PART_VALIDATION C , XXSUB_POS_DETAIL D , XXSUB_POS_HEADER H , XXSUB_POS M WHERE TRUNC(WHEN_PROCESSED) = '01-JAN-1970' AND M.SEQUENCE_NO = H.SEQUENCE_NO AND H.SEQUENCE_NO = D.SEQUENCE_NO AND H.POS_HEADER_ID = D.POS_HEADER_ID AND NOT EXISTS (SELECT 1 FROM GD_PART_COSTS WHERE CST_PART_NO = D.POS_PARTNUMBER_ID AND DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) BETWEEN CST_EFF_DATE AND NVL(CST_EXP_DATE, DECODE(SUBSTR(H.POS_FILENAME, 1, 2), 'ha', D.POS_SHIPPED , 'nh', D.POS_SHIPPED , 'na', D.POS_SHIPPED , SYSDATE ) ) AND CST_TYPE = 'OH' ) AND C.PART_NO(+) = SUBSTR(D.POS_PARTNUMBER_ID, 1, 3)
      350f5yrnnmshslock table sys.mon_mods$ in exclusive mode nowait
      376u7a6frhp20select * from dba_audit_trail where extended_timestamp > :1 order by extended_timestamp
      3bduct0mt1hhcSELECT "PART_NUMBER", "PRODUCT_LINE", "ACCOUNT", "REVENUE_AMOUNT", "PERIOD_NAME", "REVENUE_TYPE", "PART_FAMILY", "COMPANY", "ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "O100663" WHERE "ACCOUNT"='4400' AND "REVENUE_TYPE" NOT LIKE 'INT%' AND (UPPER("PERIOD_NAME")='JAN-14' OR UPPER("PERIOD_NAME")='FEB-14') AND "PART_NUMBER" LIKE '%-SERV'
      3qfdsumvk7yt5DECLARE v_msg VARCHAR2(200); BEGIN gd_sub_quote_dda_from_compass; EXCEPTION WHEN OTHERS THEN v_msg := SUBSTR(SQLERRM, 1, 200); INSERT INTO gd_tib_errors VALUES ('GD_TIB_DDA_MSGS', 'S', sysdate, 'Error in calling GD_SUB_QUOTE_DDA_FROM_COMPASS procedure '||v_msg, null); END;
      3r45tkag2xj15DECLARE v_msg VARCHAR2(200); BEGIN gd_tib_pos_publish(NULL); EXCEPTION WHEN OTHERS THEN v_msg := SUBSTR(SQLERRM, 1, 200); INSERT INTO gd_tib_errors VALUES ('GD_TIB_POS_MSGS', 'S', sysdate, 'Error in calling GD_POS_PUBLISH procedure '||v_msg, null); END;
      3rbh076r95nuvSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'DOT HILL%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBST R("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A 1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      459f3z9u4fb3uselect value$ from props$ where name = 'GLOBAL_DB_NAME'
      4cbf4gc30cwn7SELECT SERVER_NAME FROM SYS.XXSOX_VALIDSERVERS WHERE UPPER(SERVER_NAME) = UPPER(:B1 )
      4djmu315d0jvnSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'ORACLE%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      4m7m0t6fjcs5xupdate seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1
      4mx0ubtp5b8dsDECLARE /* $Id: gd_insert_missing_sc_oh_cost.sql, v 1.1 2010/03/23 08:55:28 saravan Exp $ */ -- ===================================================================== -- FILE NAME : GD_Insert_Missing_SC_OH_Cost.sql -- AUTHOR : Rajesh Solanki -- DATE CREATED : 30/09/2009 -- APPLICATION : Global Disti (GD) -- VERSION : 1.0 -- -- DESCRIPTION : Function to Insert missing SC/OH cost for software parts -- that are published through POS sesseion -- -- ===================================================================== -- MODIFICATION HISTORY -- ===================================================================== -- DATE VERSION LOGIN CHANGE DESCRIPTION -- --------------------------------------------------------------------- -- 30/09/2009 1.0 rajeshs Created -- ===================================================================== -- -- Declaration Section -- lv_sqlerrm VARCHAR2(500); lv_qtr_start DATE; lv_qtr_end DATE; -- -- Declaring Local Cursor's -- -- -- Cursor to reterive parts/transaction dqate and cost type for parts that has missing SC/OH cost -- Both from gd_pos_items and xxsub_pos tables -- CURSOR c1 IS SELECT DISTINCT pos_part_no , pos_txn_date , 'SC' cst_type , DECODE(c.part_no, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part' ) Descr FROM cost_part_validation c , gd_pos_items l WHERE pos_period_name = (SELECT val_value FROM gd_validations WHERE val_type='CURRENT_ACTG_PERIOD' ) AND pos_production_flag = 'Y' AND NOT EXISTS (SELECT 1 FROM gd_part_costs WHERE cst_part_no = l.pos_part_no AND l.pos_txn_date BETWEEN cst_eff_date AND NVL(cst_exp_date, l.pos_txn_date) AND cst_type = 'SC' ) AND c.part_no(+) = SUBSTR(l.pos_part_no, 1, 3) UNION SELECT DISTINCT pos_part_no , pos_txn_date , 'OH' cst_type , DECODE(c.part_no, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') Descr FROM cost_part_validation c , gd_pos_items l WHERE pos_period_name = (SELECT val_value FROM gd_validations WHERE val_type='CURRENT_ACTG_PERIOD' ) AND pos_production_flag = 'Y' AND NOT EXISTS (SELECT 1 FROM gd_part_costs WHERE cst_par t_no = l.pos_part_no AND l.pos_txn_date BETWEEN cst_eff_date AND NVL(cst_exp_date, l.pos_txn_date) AND cst_type = 'OH' ) AND c.part_no(+) = SUBSTR(l.pos_part_no, 1, 3) UNION SELECT d.pos_partnumber_id , DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.pos_shipped , 'na', d.pos_shipped , SYSDATE ) pos_txn_date , 'SC' cst_type , DECODE(c.part_no, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') Descr FROM cost_part_validation c , xxsub_pos_detail d , xxsub_pos_header h , xxsub_pos m WHERE TRUNC(when_processed) = '01-JAN-1970' AND m.sequence_no = h.sequence_no AND h.sequence_no = d.sequence_no AND h.pos_header_id = d.pos_header_id AND NOT EXISTS (SELECT 1 FROM gd_part_costs WHERE cst_part_no = d.pos_partnumber_id AND DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.po s_shipped , 'na', d.pos_shipped , SYSDATE ) BETWEEN cst_eff_date AND NVL(cst_exp_date, DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.pos_shipped , 'na', d.pos_shipped , SYSDATE ) ) AND cst_type = 'SC' ) AND c.part_no(+) = SUBSTR(d.pos_partnumber_id, 1, 3) UNION SELECT d.pos_partnumber_id , DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.pos_shipped , 'na', d.pos_shipped , SYSDATE ) pos_txn_date , 'OH' cst_type , DECODE(c.part_no, NULL, 'Missing entry in cost_part_validation table for this part', 'Missing SC/OH cost for this part') Descr FROM cost_part_validation c , xxsub_pos_detail d , xxsub_pos_header h , xxsub_pos m WHERE TRUNC(when_processed) = '01-JAN-1970' AND m.sequence_no = h.sequence_no AND h.sequence_no = d.sequence_no AND h.pos_header_id = d.pos_header_id AND NOT EXISTS (SELECT 1 FROM gd_part_costs WHERE cst_part_no = d.pos_partnumber_id AND DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.pos_shipped , 'na', d.pos_shipped , SYSDATE ) BETWEEN cst_eff_date AND NVL(cst_exp_date, DECODE(SUBSTR(h.pos_filename, 1, 2), 'ha', d.pos_shipped , 'nh', d.pos_shipped , 'na', d.pos_shipped , SYSDATE ) ) AND cst_type = 'OH' ) AND c.part_no(+) = SUBSTR(d.pos_partnumber_id, 1, 3) ; -- -- Main Section -- BEGIN BEGIN DELETE FROM temp_missing_cost_table; COMMIT; EXCEPTION WHEN OTHERS THEN lv_sqlerrm := SQLERRM; ROLLBACK; RAISE_APPLICATION_ERROR('-20000', 'Err Deleting Data from temp_missing_cost_table (Contact IT/Support): '||lv_sqlerrm); END; -- -- Fetching all parts that has missing SC/OH cost and inserting them into a temporary table -- FOR c in c1 LOOP BEGIN INSERT INTO temp_missing_cost_table VALUES (c.pos_part_no , c.pos_txn_date , c.cst_type , c.descr ); EXCEPTION WHEN OTHERS THEN lv_sqlerrm := SQLERRM; ROLLBACK; RAISE_APPLICATION_ERROR('-20001', 'Err Inserting Missing SC/OH Cost in temp table (Contact IT/Support): '||lv_sqlerrm); END; END LOOP; COMMIT; dbms_output.put_line('02'); EXCEPTION WHEN OTHERS THEN lv_sqlerrm := SQLERRM; RAISE_APPLICATION_ERROR('-20008', 'Please Contact IT/Support For Error: '||lv_sqlerrm); END;
      4yc06ra33csvzDECLARE v_print1 varchar2(2000); v_last_published_date DATE; i number; v_first_row number; BEGIN xxcm_cust_loc_pub; v_first_row := 0; FOR rec in (SELECT SEQUENCE_NO, ERROR_MESSAGE from xxcm_cust_pub_error WHERE EMAIL_FLAG = 'N') LOOP -- v_print1 := 'ERR SEQ NO:' || TO_CHAR(rec.SEQUENCE_NO) ||' ERR MSG:'|| rec.ERROR_MESSAGE; if v_first_row = 0 then dbms_output.put_line(' GD CUST ID ' || RPAD(' CUSTOMER NAME', 50)|| RPAD(' DISTI NAME', 33) || RPAD(' POS TX#', 10) || RPAD(' ASP TX#', 10) ); v_first_row := 1; end if; v_print1 := rec.ERROR_MESSAGE; FOR i IN 0..LENGTH(v_print1)/250 LOOP DBMS_OUTPUT.PUT_LINE(SUBSTR(v_print1, i*250+1, 250) ); END LOOP; --update when_processed to sysdate UPDATE xxcm_cust_pub_error SET EMAIL_FLAG = 'Y' WHERE SEQUENCE_NO = rec.SEQUENCE_NO ; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;
      58wzdu30azxcrSELECT * FROM XXPUB_GENERIC WHERE WHEN_PROCESSED = '01-jan-1970' order by SEQUENCE_NO
      5aa4qy7098w5kSELECT seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5, count(*) over () from sys.wri$_adv_message_groups WHERE id = :1 ORDER BY seq
      5cfvfw9wpx8t5SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'GIGAMON%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR ("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1 "."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      5cku00pvuksrkSELECT * FROM XXPUB_DACLAIM WHERE WHEN_PROCESSED = '01-jan-1970' order by SEQUENCE_NO
      5md9mz2rrs0jcSELECT COUNT(*) FROM GD_MKTG_QUOTE_HEADERS WHERE NVL(MQH_QUOTE_NO, '!') = NVL(:B4 , '!') AND NVL(MQH_FSO_CODE, '!') = NVL(:B3 , '!') AND NVL(MQH_DISTI_NAME, '!') = NVL(:B2 , '!') AND NVL(MQH_CUST_NAME, '!') = NVL(:B1 , '!')
      5ptzsdnruqrhwSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'SIMPLY%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      5ur69atw3vfhjselect decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failover_delay, flags from service$ where name = :1
      5ygxta8q6thgnSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'FIBERHOME%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBS TR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR(" A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      60pzpx9jt35qnSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'PALO ALTO%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBS TR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR(" A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      62wkpdthx9k57UPDATE GD_MKTG_QUOTE_HEADERS SET MQH_COMMENTS = '' , MQH_STATUS = 'A' , MQH_QUOTE_ENTRY_DATE = SYSDATE , MODIFIED_DATE = SYSDATE WHERE NVL(MQH_QUOTE_NO, '!') = NVL(:B4 , '!') AND NVL(MQH_FSO_CODE, '!') = NVL(:B3 , '!') AND NVL(MQH_DISTI_NAME, '!') = NVL(:B2 , '!') AND NVL(MQH_CUST_NAME, '!') = NVL(:B1 , '!')
      66snhg0v0c18dSELECT USERNAME, OSUSER, MACHINE, PROGRAM, MODULE FROM V$SESSION WHERE USERENV('sessionid') = AUDSID AND ROWNUM = 1
      6gcd7yy7jjw0uDECLARE v_print1 varchar2(2000); BEGIN -- Call to the procedure which will filter the data from xxsub_item XXMSG_ITEM_TYPE_UPD; -- Call to the procedure which will filter the data from xxsub_item -- Delete data from Queue tables older than the retention time FOR rec_cleanup IN ( SELECT * FROM XXSUB_ITEM WHERE WHEN_PROCESSED <= sysdate - 10 AND WHEN_PROCESSED != '1-JAN-1970' ) LOOP BEGIN DELETE FROM XXSUB_ITEM WHERE sequence_no = rec_cleanup.sequence_no; DELETE FROM XXSUB_ITEM_ATTR WHERE sequence_no = rec_cleanup.sequence_no; DELETE FROM XXSUB_ITEM_XREF WHERE sequence_no = rec_cleanup.sequence_no; DELETE FROM XXSUB_ITEM_XREF_ATTR WHERE sequence_no = rec_cleanup.sequence_no; COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; --Read the xstatus_eror_tib_pub table and send email to application specific alias or support FOR rec in (SELECT msg_seq_no, sub_msgseqno, message_name, insert_timestamp, message_text FROM XSTATUS_ERR_TIB_PUB WHERE TRUNC(when_processed) = to_date('1-Jan-1970', 'DD-MON-YYYY') AND publish_facility = 'GD' AND message_name like 'ITEM%') LOOP v_print1 := SUBSTR(rec.sub_msgseqno || chr(9) || rec.message_name || chr(9) || rec.insert_timestamp || chr(9) || rec.message_text, 1, 254); dbms_output.put_line( v_print1); --update when_processed to sysdate UPDATE XSTATUS_ERR_TIB_PUB SET when_processed = SYSDATE WHERE msg_seq_no = rec.msg_seq_no; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;
      6jgfda6a3r7w0SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'LG%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PE RIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      6qpsh27z33zy5SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'DATANG%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      72bpcq74qrwatSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'JDS UNI%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR ("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1 "."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      749tad6uzbksvDELETE FROM XXSUB_ITEM_XREF_ATTR WHERE SEQUENCE_NO = :B1
      79vkpt3hzg03wSELECT "PART_NUMBER", "PRODUCT_LINE", "ACCOUNT", "REVENUE_AMOUNT", "PERIOD_NAME", "REVENUE_TYPE", "PART_FAMILY", "COMPANY", "ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "O100663" WHERE "ACCOUNT"='4400' AND "REVENUE_TYPE" NOT LIKE 'INT%' AND UPPER("PERIOD_NAME")='DEC-14' AND "PART_NUMBER" LIKE '%-SERV'
      7g3wv47q7aa40SELECT * FROM XXSUB_QUOTE X WHERE TRUNC(WHEN_PROCESSED) = '01-JAN-1970' ORDER BY SEQUENCE_NO
      7tr0031mjpkscbegin publish_to_pos(FALSE); end;
      81ky0n97v4zsg/* OracleOEM */ select s.sid, s.serial# from v$session s where s.sid = (select sid from v$mystat where rownum=1)
      83v08gtfj7q88INSERT INTO XXSUB_ITEM_XREF ( SEQUENCE_NO, XREF_RELATIONSHIP_NAME, XREF_ITEM_TYPE, XREF_ITEM_NAME, XREF_ITEM_REVISION) VALUES (:v0, :v1, :v2, :v3, :v4)
      868n3yds47cg4SELECT "A1"."PART_NUMBER", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."ENDCUST_SS_ID", "A1"."XCM_ENDCUST_XU_NAME", SUM("A1"."NET_RESALE") FROM "GD"."ASP_REVENUE_TABLE_XCM" "A1" WHERE "A1"."PERIOD_NAME" LIKE 'JAN-14' OR "A1"."PERIOD_NAME" LIKE 'FEB-14' GROUP BY "A1"."PART_NUMBER", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."ENDCUST_SS_ID", "A1"."XCM_ENDCUST_XU_NAME"
      8bu6baap1snwySELECT "A1"."PART_NUMBER", "A1"."DISTRIBUTOR_NAME", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."XCM_ENDCUST_XU_NAME", SUM("A1"."QUANTITY"), SUM("A1"."REVENUE_AMOUNT"), SUM("A1"."EXTENDED_RESALE"), SUM("A1"."NET_RESALE") FROM "GD"."ASP_REVENUE_TABLE_XCM" "A1" WHERE "A1"."PERIOD_NAME" LIKE 'JAN-14' OR "A1"."PERIOD_NAME" LIKE 'FEB-14' OR "A1"."PERIOD_NAME" LIKE 'MAR-14' GROUP BY "A1"."PART_NUMBER", "A1"."DISTRIBUTOR_NAME", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."XCM_ENDCUST_XU_NAME"
      8suz9a9ykkwwqDECLARE tserver VARCHAR2(80) := NULL; tusername VARCHAR2(80) := NULL; tosuser VARCHAR2(80) := NULL; tmachine VARCHAR2(80) := NULL; tprogram VARCHAR2(80) := NULL; tmodule VARCHAR2(80) := NULL; BEGIN BEGIN SELECT username, osuser, machine, program, module INTO tusername, tosuser, tmachine, tprogram, tmodule FROM v$session WHERE userenv('sessionid') = audsid AND rownum = 1; END; BEGIN SELECT server_name INTO tserver FROM SYS.XXSOX_ValidServers WHERE UPPER(server_name) = UPPER(tmachine); EXCEPTION WHEN no_data_found THEN IF substr(ltrim(nvl(tprogram, 'NULL')), 1, 7) != 'bifrost' THEN XXSOX_UserLog(tusername, tosuser, tmachine, tprogram, tmodule); END IF; END; END;
      928mhrahfhkpdSELECT a.processed_records, b.unprocessed_records, a.processed_records_hours FROM (SELECT COUNT(*) as processed_records, nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '00', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '01', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '02', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '03', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '04', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '05', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '06', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '07', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '08', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '09', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '10', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '11', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '12', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '13', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '14', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '15', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '16', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '17', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_proce ssed, 'hh24'), '18', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '19', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '20', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '21', 1, 0)), 0) ||'~'||nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '22', 1, 0)), 0) ||'~'|| nvl(SUM(DECODE(TO_CHAR(when_processed, 'hh24'), '23', 1, 0)), 0) processed_records_hours FROM XXSUB_OPPORTUNITY_PROGRAM WHERE when_processed > TRUNC(SYSDATE)) a, (SELECT COUNT(*) as unprocessed_records FROM XXSUB_OPPORTUNITY_PROGRAM WHERE when_processed = '01-JAN-1970') b
      9wapxfsbzds5hINSERT INTO SYS.XXSOX_INVALIDCONNECTIONS VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , SYSDATE)
      artwjs76kssvyDELETE FROM XXSUB_ITEM_ATTR WHERE SEQUENCE_NO = :B1
      b41awu29rqx6sINSERT INTO XXSUB_BOM_COMP_ATTR ( SEQUENCE_NO, COMP_RELATIONSHIP_NAME, COMP_RELATIONSHIP_ID, COMP_ITEM_TYPE, COMP_ITEM_NAME, COMP_ITEM_REVISION, ATTR_NAME, ATTR_VALUE ) VALUES ( :v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7 )
      bmj5us86kyf8aSELECT * FROM XXPUB_FULFILLMENT WHERE WHEN_PROCESSED = '01-jan-1970' order by SEQUENCE_NO
      bmuqkwgyxq95mINSERT INTO XXSUB_BOM_COMP_REF ( SEQUENCE_NO, COMP_RELATIONSHIP_NAME, COMP_RELATIONSHIP_ID, COMP_ITEM_TYPE, COMP_ITEM_NAME, COMP_ITEM_REVISION, REF_RELATIONSHIP_NAME, REF_ITEM_TYPE , REF_ITEM_NAME, REF_ITEM_REVISION ) VALUES ( :v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9 )
      bnzuc1x96jbkdSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'DELL%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A 1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"." PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      cpwrux6mwsfy6SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'GIGA%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A 1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"." PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      cvzb1cvmdpp34SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'ARISTA%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      czbcbbrs9m00bSELECT "A1"."PART_NUMBER", "A1"."DISTRIBUTOR_NAME", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."XCM_ENDCUST_XU_NAME", SUM("A1"."QUANTITY"), SUM("A1"."REVENUE_AMOUNT"), SUM("A1"."EXTENDED_RESALE"), SUM("A1"."NET_RESALE") FROM "GD"."ASP_REVENUE_TABLE_XCM" "A1" WHERE "A1"."PERIOD_NAME" LIKE 'JAN-14' OR "A1"."PERIOD_NAME" LIKE 'FEB-14' GROUP BY "A1"."PART_NUMBER", "A1"."DISTRIBUTOR_NAME", "A1"."PRODUCT_LINE", "A1"."ACCOUNT", "A1"."SUB_ACCOUNT", "A1"."EXTENDED_RESALE", "A1"."REVENUE_AMOUNT", "A1"."ID_NUMBER", "A1"."PERIOD_NAME", "A1"."QUANTITY", "A1"."CHANNEL_NAME", "A1"."XCM_ENDCUST_XU_NAME"
      dngx7pmc9ka72SELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'BAIDU%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR(" A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1". "PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      dnzbcmgb9h0phSELECT DISTINCT c.disti_edi_849_filename || '!' || a.dar_session_no FROM gd_edi_844_rejects a, gd_edi_844_msgs b, gd_distributors c WHERE a.dar_header_fk = b.header_fk AND a.dar_contract_fk = b.contract_fk AND a.dar_pad_pk = b.pad_pk AND a.dar_session_no = b.session_no AND a.dar_process_ind != 'Y' AND b.created_by = 'EDI-DA-844' AND DECODE(RTRIM(TO_CHAR(TO_DATE(a.created_date), 'DAY')), 'THURSDAY', TRUNC(a.created_date+4), 'FRIDAY', TRUNC(a.created_date+4), 'SATURDAY', TRUNC(a.created_date+3), TRUNC(a.created_date + 2)) <= TRUNC(SYSDATE+4) AND b.disti_name = c.disti_name
      dwt9jadadx51aSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'SIMPLYVITI%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUB STR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      fchznr7bs81dqSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'AMAZON%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR( "A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1" ."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      fhgft51ugknqdSELECT DISTINCT C.GD_CUST_ID, C.HUB_PARTY_ID, C.CUSTOMER_NAME, C.ADDRESS_LINE1, C.ADDRESS_LINE2, C.ADDRESS_LINE3, C.ADDRESS_LINE4, C.CITY, C.STATE, C.PROVINCE, C.COUNTY, C.COUNTRY, C.POSTAL_CODE, C.STATUS, C.DISTI_CUST_ID, C.CREATION_DATE, C.LAST_UPDATE_DATE, C.DISTI_NAME FROM GD_CUSTOMERS C, XXCM_CUST_LOC_REPUBLISH REP WHERE C.GD_CUST_ID = REP.GD_CUST_ID(+) AND ( C.CREATION_DATE >= :B1 OR C.LAST_UPDATE_DATE >= :B1 OR REP.LAST_UPDATE_DATE >= :B1 ) UNION SELECT DISTINCT C.GD_CUST_ID, C.HUB_PARTY_ID, C.CUSTOMER_NAME, C.ADDRESS_LINE1, C.ADDRESS_LINE2, C.ADDRESS_LINE3, C.ADDRESS_LINE4, C.CITY, C.STATE, C.PROVINCE, C.COUNTY, C.COUNTRY, C.POSTAL_CODE, C.STATUS, C.DISTI_CUST_ID, C.CREATION_DATE, C.LAST_UPDATE_DATE, C.DISTI_NAME FROM GD_CUSTOMERS C, XXCM_CUST_LOC_REPUBLISH REP WHERE C.GD_CUST_ID = REP.GD_CUST_ID(+) AND ( C.CREATION_DATE < :B1 OR C.LAST_UPDATE_DATE < :B1 OR REP.LAST_UPDATE_DATE < :B1 ) AND ( C.CREATION_DATE > :B1 -30 OR C.LAST_UPDATE_DATE > :B1 -30 OR REP.LAST_UPDATE_DATE > :B1 -30 ) AND C.GD_CUST_ID NOT IN (SELECT SOURCE_SYSTEM_REFERENCE FROM XXPUB_CUST_LOC)
      fjxusy4bv8b85select DISTINCT TPOS_FILENAME from gd_edi_pos_items where tpos_process_ind='N' and tpos_territory = 'NORTH AMERICA'
      fy7595g0tp46vSELECT /*+ NOREWRITE */ DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER'), "A1"."CUSTOMER_CITY", "A1"."CUSTOMER_COUNTRY", "A1"."DISTRIBUTOR_NAME", "A1"."EXTENDED_RESALE", "A1"."PART_NUMBER", "A1"."QUANTITY", "A1"."REGION", "A1"."SHIPCUST_XCM_NAME", "A1"."ENDCUST_XCM_NAME" FROM "GD"."ASP_REVENUE_TABLE" "A1" WHERE "A1"."ENDCUST_XCM_NAME" LIKE 'INTELLIPROP%' AND (DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SU BSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q314' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q414' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q214' OR DECODE(SUBSTR("A1"."PERIOD_NAME", 1, 3), 'APR', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAY', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUN', 'Q1'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JUL', 'Q2'||SUBSTR ("A1"."PERIOD_NAME", 5, 6), 'AUG', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'SEP', 'Q2'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OCT', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'NOV', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'DEC', 'Q3'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'JAN', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'FEB', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'MAR', 'Q4'||SUBSTR("A1"."PERIOD_NAME", 5, 6), 'OTHER')='Q114') AND ("A1"."REVENUE_TYPE" LIKE 'POS%' OR "A1"."REVENUE_TYPE" LIKE 'DIRECT')
      g00cj285jmgswupdate sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
      g294qgxf8jqhbINSERT INTO XXSUB_ITEM_ATTR ( SEQUENCE_NO, ATTR_NAME, ATTR_VALUE ) VALUES (:v0, :v1, :v2)
      gd38axjtgv5uqselect XXSUB_ITEM_SEQ.nextval from dual
      gdvvuza0axnhbUPDATE GD_EDI_POS_ITEMS SET TPOS_PROCESS_IND = 'N' WHERE TPOS_PROCESS_IND = 'X'
      gfq05puhdqs8vINSERT INTO XXSUB_BOM_COMP_REF_ATTR ( SEQUENCE_NO, COMP_RELATIONSHIP_NAME, COMP_RELATIONSHIP_ID, COMP_ITEM_TYPE, COMP_ITEM_NAME, COMP_ITEM_REVISION, REF_RELATIONSHIP_NAME, REF_ITEM_TYPE, REF_ITEM_NAME, REF_ITEM_REVISION, ATTR_NAME, ATTR_VALUE ) VALUES ( :v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11 )

      Back to SQL Statistics
      Back to Top

       

      Instance Activity Statistics

      Back to Top   

      Instance Activity Stats

      StatisticTotalper Secondper Trans
      CPU used by this session1,046,17329.0929.58
      CPU used when call started1,034,05928.7629.24
      CR blocks created7440.020.02
      Cached Commit SCN referenced299,9928.348.48
      Commit SCN cached500.000.00
      DB time7,123,161198.09201.40
      DBWR checkpoint buffers written102,4652.852.90
      DBWR checkpoints650.000.00
      DBWR object drop buffers written00.000.00
      DBWR revisited being-written buffer2660.010.01
      DBWR thread checkpoint buffers written00.000.00
      DBWR transaction table writes1,0600.030.03
      DBWR undo block writes90,3862.512.56
      DFO trees parallelized30.000.00
      IMU CR rollbacks820.000.00
      IMU Flushes33,2620.920.94
      IMU Redo allocation size282,489,1527,855.837,987.14
      IMU commits7,7890.220.22
      IMU contention1100.000.00
      IMU ktichg flush1130.000.00
      IMU pool not allocated2,0330.060.06
      IMU recursive-transaction flush6810.020.02
      IMU undo allocation size1,075,551,96829,910.3630,410.31
      IMU- failed to get a private strand2,0330.060.06
      PX local messages recv'd148,3584.134.19
      PX local messages sent148,4134.134.20
      Parallel operations downgraded 1 to 25 pct30.000.00
      Parallel operations downgraded to serial30.000.00
      SMON posted for undo segment recovery20.000.00
      SMON posted for undo segment shrink790.000.00
      SQL*Net roundtrips to/from client779,62521.6822.04
      SQL*Net roundtrips to/from dblink2260.010.01
      active txn count during cleanout21,3590.590.60
      application wait time640.000.00
      background checkpoints completed580.000.00
      background checkpoints started590.000.00
      background timeouts127,5523.553.61
      branch node splits1000.000.00
      buffer is not pinned count48,155,8441,339.181,361.57
      buffer is pinned count49,846,2201,386.191,409.36
      bytes received via SQL*Net from client105,053,9412,921.482,970.31
      bytes received via SQL*Net from dblink336,9489.379.53
      bytes sent via SQL*Net to client338,562,4499,415.199,572.56
      bytes sent via SQL*Net to dblink42,5401.181.20
      calls to get snapshot scn: kcmgss521,08114.4914.73
      calls to kcmgas160,1174.454.53
      calls to kcmgcs33,1710.920.94
      change write time9,3520.260.26
      cleanout - number of ktugct calls31,4030.870.89
      cleanouts and rollbacks - consistent read gets410.000.00
      cleanouts only - consistent read gets7140.020.02
      cluster key scan block gets273,8217.617.74
      cluster key scans5,5520.150.16
      commit batch performed10.000.00
      commit batch requested10.000.00
      commit batch/immediate performed3510.010.01
      commit batch/immediate requested3510.010.01
      commit cleanout failures: block lost1290.000.00
      commit cleanout failures: buffer being written450.000.00
      commit cleanout failures: callback failure5,9240.160.17
      commit cleanout failures: cannot pin410.000.00
      commit cleanouts458,56512.7512.97
      commit cleanouts successfully completed452,42612.5812.79
      commit immediate performed3500.010.01
      commit immediate requested3500.010.01
      commit txn count during cleanout45,9711.281.30
      concurrency wait time1,3880.040.04
      consistent changes26,1830.730.74
      consistent gets120,536,7183,352.043,408.07
      consistent gets - examination2,485,35769.1270.27
      consistent gets direct89,2322.482.52
      consistent gets from cache120,447,4863,349.563,405.55
      current blocks converted for CR60.000.00
      cursor authentications1330.000.00
      data blocks consistent reads - undo records applied20,1690.560.57
      db block changes7,071,281196.65199.93
      db block gets6,773,461188.37191.51
      db block gets direct910.000.00
      db block gets from cache6,773,370188.36191.51
      deferred (CURRENT) block cleanout applications90,2862.512.55
      dirty buffers inspected38,9251.081.10
      enqueue conversions12,4320.350.35
      enqueue releases550,62015.3115.57
      enqueue requests550,73915.3215.57
      enqueue timeouts1200.000.00
      enqueue waits100.000.00
      execute count885,03624.6125.02
      failed probes on index block reclamation50.000.00
      free buffer inspected79,874,3462,221.252,258.38
      free buffer requested79,787,9852,218.852,255.94
      heap block compress9,0540.250.26
      hot buffers moved to head of LRU499,63013.8914.13
      immediate (CR) block cleanout applications7550.020.02
      immediate (CURRENT) block cleanout applications132,1093.673.74
      index crx upgrade (found)00.000.00
      index crx upgrade (positioned)66,6171.851.88
      index fast full scans (full)4280.010.01
      index fetch by key651,81418.1318.43
      index scans kdiixs115,902,576442.24449.63
      leaf node 90-10 splits2,8490.080.08
      leaf node splits10,4410.290.30
      lob reads1,0530.030.03
      lob writes1,9610.050.06
      lob writes unaligned1,9610.050.06
      logons cumulative1,4890.040.04
      messages received71,2631.982.01
      messages sent71,2611.982.01
      no buffer to keep pinned count00.000.00
      no work - consistent read gets117,854,3093,277.453,332.23
      opened cursors cumulative103,6812.882.93
      parse count (failures)50.000.00
      parse count (hard)1,5610.040.04
      parse count (total)101,5452.822.87
      parse time cpu2,1280.060.06
      parse time elapsed3,0090.080.09
      physical read IO requests3,756,646104.47106.22
      physical read bytes###############18,203,603.6818,507,880.46
      physical read total IO requests3,814,558106.08107.85
      physical read total bytes###############18,254,915.0718,560,049.52
      physical read total multi block requests2,712,57475.4376.70
      physical reads79,905,6052,222.122,259.26
      physical reads cache79,691,2472,216.162,253.20
      physical reads cache prefetch75,962,6622,112.472,147.78
      physical reads direct214,3585.966.06
      physical reads direct (lob)60.000.00
      physical reads direct temporary tablespace120,9103.363.42
      physical reads prefetch warmup00.000.00
      physical write IO requests73,0332.032.06
      physical write bytes2,387,124,22466,384.2767,493.90
      physical write total IO requests199,5265.555.64
      physical write total bytes6,820,727,296189,679.71192,850.24
      physical write total multi block requests112,2643.123.17
      physical writes291,3978.108.24
      physical writes direct123,9963.453.51
      physical writes direct (lob)60.000.00
      physical writes direct temporary tablespace120,6933.363.41
      physical writes from cache167,4014.664.73
      physical writes non checkpoint283,7807.898.02
      pinned buffers inspected3310.010.01
      prefetch clients - default00.000.00
      prefetch warmup blocks aged out before use00.000.00
      prefetched blocks aged out before use1730.000.00
      process last non-idle time35,6710.991.01
      queries parallelized30.000.00
      recursive calls4,048,725112.59114.47
      recursive cpu usage241,8866.736.84
      redo blocks written2,724,06675.7577.02
      redo buffer allocation retries1950.010.01
      redo entries3,069,53485.3686.79
      redo log space requests2460.010.01
      redo log space wait time2,2260.060.06
      redo ordering marks88,8142.472.51
      redo size1,337,984,40037,208.4237,830.37
      redo subscn max counts109,3633.043.09
      redo synch time25,2430.700.71
      redo synch writes14,5780.410.41
      redo wastage11,858,984329.79335.30
      redo write time43,0441.201.22
      redo writer latching time100.000.00
      redo writes42,9641.191.21
      rollback changes - undo records applied9870.030.03
      rollbacks only - consistent read gets7090.020.02
      rows fetched via callback236,7366.586.69
      session connect time00.000.00
      session cursor cache hits60,9701.701.72
      session logical reads127,310,1793,540.413,599.59
      session pga memory45,811,7601,273.991,295.29
      session pga memory max449,533,26412,501.2112,710.17
      session uga memory47,480,2561,320.391,342.46
      session uga memory max2,043,083,92856,816.7557,766.45
      shared hash latch upgrades - no wait85,1852.372.41
      shared hash latch upgrades - wait00.000.00
      sorts (disk)20.000.00
      sorts (memory)152,0944.234.30
      sorts (rows)10,898,508303.08308.15
      sql area evicted390.000.00
      sql area purged40.000.00
      summed dirty queue length452,75712.5912.80
      switch current to new buffer3,0320.080.09
      table fetch by rowid14,024,220390.00396.52
      table fetch continued row1,081,01830.0630.56
      table scan blocks gotten80,547,3002,239.962,277.41
      table scan rows gotten1,292,651,95135,947.7636,548.63
      table scans (cache partitions)00.000.00
      table scans (direct read)5130.010.01
      table scans (long tables)4,9640.140.14
      table scans (rowid ranges)5130.010.01
      table scans (short tables)21,8600.610.62
      total number of times SMON posted2700.010.01
      transaction rollbacks3510.010.01
      transaction tables consistent read rollbacks40.000.00
      transaction tables consistent reads - undo records applied1470.000.00
      undo change vector size641,739,68817,846.3418,144.64
      user I/O wait time700,66519.4919.81
      user calls797,52222.1822.55
      user commits35,3590.981.00
      user rollbacks90.000.00
      workarea executions - onepass170.000.00
      workarea executions - optimal131,2263.653.71
      write clones created in background30.000.00
      write clones created in foreground2120.010.01

      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 count56,24776,946
      opened cursors current426512
      logons current5262

      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)595.91

        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)
      SYSTEM1,505,025420.6624.011,17301156.87
      GLODIST1,151,454323.3817.3237,618165.00
      ASP_REV_DATA973,357271.8424.17240000.00
      GLODIST_INDEX78,92523.391.035,645000.00
      ASP_REV_INDEX27,76911.721.00171000.00
      UNDOTBS67100.541.0020,7661941.38
      SYSAUX12,01904.242.422,250000.00
      TEMP4,948013.6124.454,176000.00
      INVTEST_DATA1,48306.5116.44254000.00
      TOOLS79507.0715.77511000.00
      INVTESTI19400.771.00116000.00
      XRO_USER_SPACE11600.861.0057000.00
      QUEST7701.301.0057000.00
      TEMP_XRO100.001.000000.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)
      ASP_REV_DATA/db/oradata/glodist/ASP_DATA01.DBF356,535101.3717.7668000.00
      ASP_REV_DATA/db/oradata/glodist/ASP_DATA02.DBF206,91662.2529.6857000.00
      ASP_REV_DATA/db/oradata/glodist/ASP_DATA03.DBF221,95162.0827.9157000.00
      ASP_REV_DATA/db/oradata/glodist/ASP_DATA04.DBF187,95552.0025.8758000.00
      ASP_REV_INDEX/db/oradata/glodist/ASP_INDEX01.DBF5,54002.341.0057000.00
      ASP_REV_INDEX/db/oradata/glodist/ASP_INDEX02.DBF19,64711.261.0057000.00
      ASP_REV_INDEX/db/oradata/glodist/ASP_INDEX03.DBF2,58203.821.0057000.00
      GLODIST/db/oradata/glodist/GLODIST01.DBF91,81433.8712.702,595000.00
      GLODIST/db/oradata/glodist/GLODIST02.DBF94,67633.2816.101,914000.00
      GLODIST/db/oradata/glodist/GLODIST03.DBF179,38052.6020.455,0030210.00
      GLODIST/db/oradata/glodist/GLODIST04.DBF69,52824.5412.4212,048033.33
      GLODIST/db/oradata/glodist/GLODIST05.DBF68,43222.8210.851,337000.00
      GLODIST/db/oradata/glodist/GLODIST06.DBF42,00113.4815.311,255000.00
      GLODIST/db/oradata/glodist/GLODIST07.DBF33,24913.9017.621,155000.00
      GLODIST/db/oradata/glodist/GLODIST08.DBF52,61912.8812.79493000.00
      GLODIST/db/oradata/glodist/GLODIST09.DBF28,49913.9615.42463000.00
      GLODIST/db/oradata/glodist/GLODIST10.DBF31,33313.9818.40167000.00
      GLODIST/db/oradata/glodist/GLODIST11.DBF44,10413.1424.31376000.00
      GLODIST/db/oradata/glodist/GLODIST12.DBF30,31913.2224.60106000.00
      GLODIST/db/oradata/glodist/GLODIST13.DBF53,58812.7520.01733000.00
      GLODIST/db/oradata/glodist/GLODIST14.DBF51,66113.4522.79858000.00
      GLODIST/db/oradata/glodist/GLODIST15.DBF9,46805.6617.991,310000.00
      GLODIST/db/oradata/glodist/GLODIST16.DBF39,75911.965.581,413000.00
      GLODIST/db/oradata/glodist/GLODIST17.DBF25,14012.187.38506000.00
      GLODIST/db/oradata/glodist/GLODIST18.DBF13,44503.6526.9296000.00
      GLODIST/db/oradata/glodist/GLODIST19.DBF20,35013.5527.33276000.00
      GLODIST/db/oradata/glodist/GLODIST20.DBF7,26605.9325.72223000.00
      GLODIST/db/oradata/glodist/GLODIST21.DBF18,03315.7616.13734000.00
      GLODIST/db/oradata/glodist/GLODIST22.DBF19,20015.7617.372,011000.00
      GLODIST/db/oradata/glodist/GLODIST23.DBF14,91005.1425.24286000.00
      GLODIST/db/oradata/glodist/GLODIST24.DBF23,66613.3719.25266000.00
      GLODIST/db/oradata/glodist/GLODIST25.DBF26,52313.3626.14949010.00
      GLODIST/db/oradata/glodist/GLODIST26.DBF30,36313.6316.45766000.00
      GLODIST/db/oradata/glodist/GLODIST27.DBF32,12813.0920.73279000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX01.DBF9,06402.271.00459000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX02.DBF5,23104.391.00522000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX03.DBF5,30603.991.00544000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX04.DBF6,73703.121.00353000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX05.DBF15,33003.311.02642000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX06.DBF4,09504.461.03438000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX07.DBF4,24404.481.09654000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX08.DBF10,83102.191.03426000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX09.DBF7,33902.821.00320000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX10.DBF7,51702.791.05350000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX11.DBF1,48509.411.25524000.00
      GLODIST_INDEX/db/oradata/glodist/GLODIST_INDEX12.DBF1,74608.161.21413000.00
      INVTESTI/db/oradata/glodist/INVTESTI01.DBF9700.721.0058000.00
      INVTESTI/db/oradata/glodist/INVTESTI02.DBF9700.821.0058000.00
      INVTEST_DATA/db/oradata/glodist/INVEST_DATA01.DBF73005.8116.39105000.00
      INVTEST_DATA/db/oradata/glodist/INVEST_DATA02.DBF75307.1816.48149000.00
      QUEST/db/oradata/glodist/QUEST01.DBF7701.301.0057000.00
      SYSAUX/db/oradata/glodist/SYSAUX01.DBF12,01904.242.422,250000.00
      SYSTEM/db/oradata/glodist/SYSTEM01.DBF1,505,025420.6624.011,17301156.87
      TEMP/db/oraredo01/glodist/TEMP01.DBF2,225015.3323.761,86600
      TEMP/db/oraredo01/glodist/TEMP02.DBF2,723012.2125.012,31000
      TEMP_XRO/db/oraredo01/glodist/TEMP_RO_01.dbf100.001.00000
      TOOLS/db/oradata/glodist/TOOLS01.DBF79507.0715.77511000.00
      UNDOTBS/db/oradata/glodist/UNDOTBS01.DBF67100.541.0020,7661941.38
      XRO_USER_SPACE/db/oradata/glodist/USER_SPACE.DBF11600.861.0057000.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
      D27,66437127,187,87479,691,617167,40100215

       

      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
      B271530145132609623958026096
      E43202096470118953323958089533

      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
      D200.092,4701.15354,780,022
      D400.184,9401.08335,279,685
      D600.277,4101.06328,354,962
      D800.369,8801.05323,193,960
      D1000.4512,3501.03319,603,441
      D1200.5414,8201.02316,533,122
      D1400.6317,2901.02313,992,553
      D1600.7119,7601.01312,248,216
      D1800.8022,2301.01310,910,816
      D2000.8924,7001.00309,924,893
      D2200.9827,1701.00309,166,923
      D2241.0027,6641.00309,034,782
      D2401.0729,6401.00308,496,861
      D2601.1632,1101.00307,899,504
      D2801.2534,5800.99307,283,334
      D3001.3437,0500.99306,726,520
      D3201.4339,5200.99306,260,766
      D3401.5241,9900.99305,809,724
      D3601.6144,4600.99305,319,391
      D3801.7046,9300.99304,672,530
      D4001.7949,4000.98303,261,648

        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.2816,6211,009

      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)
      B200116140.750.000.000.000.0040,960
      E200110147.860.000.000.000.0040,960

        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
      2K4K115,513115,51300
      64K128K56056000
      128K256K2200
      256K512K363600
      512K1024K14,51914,51900
      1M2M40240200
      2M4M16116010
      4M8M2200
      8M16M191720
      16M32M121020
      32M64M4220
      64M128M100100

      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
      250.1377,742.11125,417.2438.004,636
      500.2577,742.11125,417.2438.004,636
      1000.5077,742.1138,322.7567.00110
      1500.7577,742.1117,779.6081.000
      2001.0077,742.1110,073.6989.000
      2401.2077,742.119,570.3089.000
      2801.4077,742.119,327.7789.000
      3201.6077,742.119,327.7789.000
      3601.8077,742.119,290.8589.000
      4002.0077,742.117,835.1891.000
      6003.0077,742.115,850.0193.000
      8004.0077,742.115,850.0193.000
      1,2006.0077,742.115,850.0193.000
      1,6008.0077,742.115,850.0193.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
      1400.47373,063167,7160.9215,63861.576,781,091
      1720.57684,802181,6520.991,7026.706,789,158
      2040.68965,625183,0731.002811.116,790,711
      2360.791287,771183,0811.002731.076,791,218
      2680.8915911,271183,0881.002661.056,791,682
      3001.0019013,455183,1001.002541.006,792,216
      3321.1122215,485183,1021.002520.996,792,270
      3641.2124417,402183,1021.002520.996,792,270
      3961.3224417,402183,1021.002520.996,792,270
      4281.4324417,402183,1021.002520.996,792,270
      4601.5324417,402183,1021.002520.996,792,270
      4921.6424417,402183,1021.002520.996,792,270
      5241.7524417,402183,1021.002520.996,792,270
      5561.8524417,402183,1021.002520.996,792,270
      5881.9624417,402183,1021.002520.996,792,270
      6202.0724417,402183,1021.002520.996,792,270

        Back to Advisory Statistics

      Back to Top 

      SGA Target Advisory

      SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
      4000.50108,016318,154,491
      6000.75103,132311,603,524
      8001.00101,748309,007,858
      1,0001.25101,748309,007,858
      1,2001.50101,748309,007,858
      1,4001.75101,748309,007,858
      1,6002.00101,748309,007,858

      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)
      160.160000
      280.280000
      400.400000
      520.520000
      640.640000
      760.760000
      880.880000
      1001.000000
      1121.120000
      1241.240000
      1361.360000
      1481.480000
      1601.600000
      1721.720000
      1841.840000
      1961.960000
      2082.080000
      2202.200000
      2322.320000
      2442.440000

      Back to Advisory Statistics
      Back to Top

       

      Java Pool Advisory

      No data exists for this section of the report.

      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)
      data block12117
      undo header9201
      undo block200

      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)
      PR-Process Startup6969021371.00
      KO-Multiple Object Checkpoint (fast object checkpoint)54540602.00
      PS-PX Process Reservation456336120201.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
      190.7367,3213,4597120/1200/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
      24-Mar 18:041,5411,694051200/00/0/0/0/0/0
      24-Mar 17:541,2081,034051200/00/0/0/0/0/0
      24-Mar 17:441176011200/00/0/0/0/0/0
      24-Mar 17:3417145021200/00/0/0/0/0/0
      24-Mar 17:241,3192,223021200/00/0/0/0/0/0
      24-Mar 17:14301331051200/00/0/0/0/0/0
      24-Mar 17:0463268021200/00/0/0/0/0/0
      24-Mar 16:542,0161,761061200/00/0/0/0/0/0
      24-Mar 16:441,08566862151200/00/0/0/0/0/0
      24-Mar 16:34737493021200/00/0/0/0/0/0
      24-Mar 16:241,4281,039031200/00/0/0/0/0/0
      24-Mar 16:144,3784,275051200/00/0/0/0/0/0
      24-Mar 16:043,2612,623061200/00/0/0/0/0/0
      24-Mar 15:542,3331,74412351200/00/0/0/0/0/0
      24-Mar 15:444973351,44621200/00/0/0/0/0/0
      24-Mar 15:3484557684321200/00/0/0/0/0/0
      24-Mar 15:244123691,43521200/00/0/0/0/0/0
      24-Mar 15:145129883531200/00/0/0/0/0/0
      24-Mar 15:0489762523131200/00/0/0/0/0/0
      24-Mar 14:54818577021200/00/0/0/0/0/0
      24-Mar 14:44548213021200/00/0/0/0/0/0
      24-Mar 14:344941,000031200/00/0/0/0/0/0
      24-Mar 14:24453328021200/00/0/0/0/0/0
      24-Mar 14:141,635608031200/00/0/0/0/0/0
      24-Mar 14:041,428701031200/00/0/0/0/0/0
      24-Mar 13:544,6712,281061200/00/0/0/0/0/0
      24-Mar 13:443,4452,057051200/00/0/0/0/0/0
      24-Mar 13:343,2141,9671,70461200/00/0/0/0/0/0
      24-Mar 13:242,4201,1141,10451200/00/0/0/0/0/0
      24-Mar 13:141,46937454321200/00/0/0/0/0/0
      24-Mar 13:0474481114441200/00/0/0/0/0/0
      24-Mar 12:54748998021200/00/0/0/0/0/0
      24-Mar 12:449501,890031200/00/0/0/0/0/0
      24-Mar 12:349631,344041200/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
      ASM db client latch24,2510.00 00
      AWR Alerted Metric Element list167,7140.00 00
      Consistent RBA43,0300.00 00
      FAL request queue8370.00 00
      FAL subheap alocation8370.00 00
      FIB s.o chain latch5900.00 00
      FOB s.o list latch20,5440.090.0000
      In memory undo latch767,7070.010.77052,6170.00
      JS queue state obj latch215,2800.00 00
      KMG MMAN ready and startup request latch11,9830.00 00
      KTF sga latch750.00 010,9350.00
      KWQMN job cache list latch80.00 00
      KWQP Prop Status200.00 00
      MQL Tracking Latch0 07180.00
      Memory Management Latch0 011,9830.00
      OS process4,7790.00 00
      OS process allocation15,1760.010.0000
      OS process: request allocation2,9200.100.0000
      PL/SQL warning settings7,0620.00 00
      SGA IO buffer pool latch2,428,6330.120.0002,428,6320.09
      SQL memory manager latch15019.330.69011,9120.00
      SQL memory manager workarea list latch869,4940.000.0000
      Shared B-Tree1,7880.00 00
      active checkpoint queue latch53,5720.030.0000
      active service list95,3360.030.45017,1800.00
      archive control1,3850.00 00
      archive process latch13,7040.011.0000
      begin backup scn array180.00 00
      cache buffer handles1,4410.00 00
      cache buffers chains349,094,0790.000.021167,278,9480.00
      cache buffers lru chain359,9570.570.000170,247,3040.36
      cache table scan latch0 02,699,0440.15
      channel handle pool latch3,2920.00 00
      channel operations parent latch175,3670.000.0000
      checkpoint queue latch1,393,5770.000.000166,6730.00
      client/application info43,4150.00 00
      commit callback allocation2300.00 00
      compile environment latch1,5370.00 00
      dml lock allocation402,5650.000.0000
      dummy allocation3,0640.160.0000
      enqueue hash chains1,113,8530.000.000900.00
      enqueues1,272,6560.010.0000
      error message lists1470.680.0000
      event group latch1,4650.00 00
      event range base latch10.00 00
      file cache latch5,8190.00 00
      global KZLD latch for mem in SGA1,3560.00 00
      global tx hash mapping16,8670.00 00
      hash table column usage latch3740.00 018,4850.00
      hash table modification latch1,0760.00 00
      job_queue_processes parameter latch5980.00 00
      kks stats3,2620.00 00
      kokc descriptor allocation latch200.00 00
      ksuosstats global area2,4170.081.0000
      ktm global data4500.00 00
      kwqbsn:qsga1,7780.00 00
      lgwr LWN SCN50,7440.00 00
      library cache1,532,7840.010.0701,9770.00
      library cache load lock5640.00 00
      library cache lock418,6730.000.0000
      library cache lock allocation15,9560.00 00
      library cache pin336,8630.000.0000
      library cache pin allocation1,1000.00 00
      list of block allocation11,1300.00 00
      loader state object freelist3,1260.00 00
      logminer context allocation100.00 00
      longop free list parent2410.00 01480.00
      message pool operations parent latch3940.00 00
      messages443,2110.010.0000
      mostly latch-free SCN50,8060.010.4000
      multiblock read objects6,042,2510.020.0100
      ncodef allocation latch5710.00 00
      object queue header heap117,7440.00 011,0000.00
      object queue header operation160,213,9050.000.0100
      object stats modification2590.00 00
      parallel query alloc buffer7,9440.620.0000
      parallel query stats826.100.0000
      parameter list6270.00 00
      parameter table allocation management3,1770.030.0000
      post/wait queue23,9660.010.00015,9060.00
      process allocation2,9200.030.0001,4640.07
      process group creation2,9200.00 00
      process queue2,3300.170.0000
      process queue reference6,127,6230.010.000192,8100.40
      qmn task queue latch5,1200.00 00
      query server freelists2,1304.740.0800
      query server process480.00 0480.00
      redo allocation258,6480.100.0203,069,0330.07
      redo copy0 03,069,6940.02
      redo writing210,4870.010.0000
      resmgr group change latch8,4540.00 00
      resmgr:active threads10,2160.00 00
      resmgr:actses change group1,4720.00 00
      resmgr:actses change state90.00 00
      resmgr:free threads list3,0220.130.0000
      resmgr:incr/decr stats460,2650.000.0000
      resmgr:resource group CPU method115,5840.020.0500
      resmgr:schema config7,2050.00 011,9750.00
      row cache objects920,7850.010.0001910.00
      rules engine rule set statistics1,0000.00 00
      sequence cache50,0310.00 00
      session allocation1,431,2220.080.0300
      session idle bit2,305,0290.000.0900
      session state list latch5,9070.100.0000
      session switching5710.00 00
      session timer11,9750.00 00
      shared pool5,150,8170.020.0100
      shared pool sim alloc1070.00 00
      shared pool simulator674,6240.000.0000
      sim partition latch100.00 010,5570.00
      simulator hash latch27,737,4730.000.0100
      simulator lru latch167,4040.140.00017,595,1050.11
      slave class210.00 00
      slave class create8411.901.0020
      sort extent pool3,1550.100.0000
      state object free list1200.00 00
      statistics aggregation3,3600.00 00
      temp lob duration state obj allocation360.00 00
      threshold alerts latch4,0650.00 00
      trace latch120.00 00
      transaction allocation243,7300.00 00
      transaction branch allocation20,0110.00 00
      undo global data380,6170.000.0000
      user lock5,4240.00 00

        Back to Latch Statistics

      Back to Top 

      Latch Sleep Breakdown

      • ordered by misses desc
      Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
      cache buffers chains349,094,0799,6841749,517000
      SGA IO buffer pool latch2,428,6332,84142,837000
      cache buffers lru chain359,9572,03672,029000
      multiblock read objects6,042,2511,381141,370000
      session allocation1,431,2221,110341,081000
      shared pool5,150,8171,072111,061000
      object queue header operation160,213,9051,020131,007000
      process queue reference6,127,6237721771000
      simulator hash latch27,737,4733122310000
      redo allocation258,6482554251000
      library cache1,532,78415511144000
      query server freelists2,130101893000
      session idle bit2,305,02979772000
      In memory undo latch767,707433310000
      active service list95,336331520000
      SQL memory manager latch15029209000
      resmgr:resource group CPU method115,58420119000
      slave class create8410100000
      mostly latch-free SCN50,806523000
      ksuosstats global area2,417220000
      archive process latch13,704110000

      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: child0350
      In memory undo latchkticmt: child011
      SGA IO buffer pool latchksfd_free_sgabuffer034
      SGA IO buffer pool latchksfd_alloc_sgabuffer010
      SQL memory manager latchqesmmIQueryRefreshBound02020
      active service listksws_event: ksws event02521
      active service listkswssyis: find service entry 1010
      archive process latchkcrrgpll010
      cache buffers chainskcbzib: multi-block read: nowait02380
      cache buffers chainskcbgtcr: kslbegin excl0109235
      cache buffers chainskcbrls: kslbegin05856
      cache buffers chainskcbzgb: scan from tail. nowait0150
      cache buffers chainskcbzib: finish free bufs010140
      cache buffers chainskcbgcur: kslbegin074
      cache buffers chainskcbgtcr: fast path075
      cache buffers chainskcbnew: new latch again070
      cache buffers chainskcbget: pin buffer036
      cache buffers chainskcbchg: kslbegin: call CR func025
      cache buffers chainskcbbxsv011
      cache buffers chainskcbchg: kslbegin: bufs not pinned015
      cache buffers chainskcbget: exchange011
      cache buffers chainskcbgtcr: kslbegin shared010
      cache buffers lru chainkcbzgws070
      ksuosstats global areaksugetosstat022
      lgwr LWN SCNkcs023020
      library cachekglScanDependency070
      library cachekgldte: child 00422
      multiblock read objectskcbzib: normal mbr free0910
      multiblock read objectskcbzib: mbr get054
      object queue header operationkcbw_unlink_q0107
      object queue header operationkcbw_link_q050
      object queue header operationkcbo_switch_mq_bg036
      object queue header operationkcbo_swap_buf_bg013
      process queue referencekxfpqrsnd010
      query server freelistskxfpobadf088
      redo allocationkcrfw_redo_gen: redo allocation 1040
      resmgr:resource group CPU methodkgkpupdateclassbitmap011
      session allocationksuxds: KSUSFCLC not set0149
      session allocationksucri087
      session allocationksursi0412
      session allocationksuxds: not user session036
      session allocationkxfprdp030
      session allocationksudlp011
      session allocationkxfpqidqr015
      session idle bitksuxds043
      session idle bitksupuc: clear busy023
      session idle bitksupuc: if ksurcp022
      shared poolkghalo078
      shared poolkghupr1043
      simulator hash latchkcbs_lookup_setid: lookup dba012
      simulator hash latchkcbsacc: insert dba010
      slave class createksvcreate0100

        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: 127,310,179
      • Captured Segments account for 97.6% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
      SYSSYSTEMAUD$ TABLE36,662,99228.80
      GDASP_REV_DATAASP_REVENUE_TABLE TABLE25,567,42420.08
      GDGLODISTXXCM_CUST_LOC_REPUBLISH_I1 INDEX18,727,13614.71
      GDGLODISTGD_PART_COSTS TABLE5,821,8884.57
      GDGLODISTGD_EDI_POS_ITEMS TABLE5,397,2804.24

        Back to Segment Statistics

      Back to Top 

      Segments by Physical Reads

      • Total Physical Reads: 79,905,605
      • Captured Segments account for 98.8% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
      SYSSYSTEMAUD$ TABLE36,115,79245.20
      GDASP_REV_DATAASP_REVENUE_TABLE TABLE23,531,99829.45
      GDGLODISTGD_EDI_POS_ITEMS TABLE5,356,3046.70
      GDGLODISTGD_MKTG_QUOTE_HEADERS TABLE3,650,9154.57
      GDGLODISTXXSUB_OPPORTUNITY_PROGRAM TABLE2,339,5512.93

        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
      GDGLODIST_INDEXDC_RULES_N1 INDEX1372.22
      GDGLODIST_INDEXPK_DH_DC_NO INDEX316.67
      GDGLODIST_INDEXPK_DL_LINE_ID INDEX15.56
      GDGLODISTXXSUB_QUOTE_I2 INDEX15.56

        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

      • % of Capture shows % of Buffer Busy Waits for each top segment compared
      • with total Buffer Busy Waits for all segments captured by the Snapshot
      OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
      SYSSYSTEMSEQ$ TABLE1453.85
      SYSSYSTEMAUD$ TABLE934.62
      SYSSYSTEMI_AUD1 INDEX27.69
      GDGLODISTXXSUB_ITEM TABLE13.85

        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_control6490.000 201
      dc_database_links2,8980.000 05
      dc_files24,6400.000 056
      dc_global_oids2,7450.000 023
      dc_histogram_data55,8801.440 06,436
      dc_histogram_defs21,1381.890 04,292
      dc_object_grants6,8010.000 0129
      dc_object_ids30,6450.230 01,340
      dc_objects9,5070.970 01,157
      dc_profiles1,3660.000 02
      dc_rollback_segments4,1020.000 019
      dc_segments7,4091.130 361,191
      dc_sequences5,2950.020 5,29533
      dc_table_scns890.000 28
      dc_tablespace_quotas10.000 12
      dc_tablespaces37,9660.000 015
      dc_usernames7,2860.000 035
      dc_users157,5490.000 084
      outstanding_alerts1,6800.000 026

       

      Back to Top 

       

      Library Cache Activity

      • "Pct Misses" should be very low
      NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
      BODY3,0090.0013,4130.0000
      CLUSTER70.00120.0000
      INDEX80.00780.0000
      SQL AREA31,8679.09920,6800.517311
      TABLE/PROCEDURE1,9711.67136,0770.211530
      TRIGGER254.005,6300.0410


      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
      BOther114.46 1.914.6122306060
      Freeable21.560.000.770.613 2828
      SQL3.011.560.060.04095142
      PL/SQL1.760.880.030.02006052
      EOther122.39 1.754.2822307070
      Freeable19.940.000.800.673 2525
      SQL3.371.750.060.040496152
      PL/SQL2.211.130.030.02007062

        Back to Memory Statistics

      Back to Top 

      SGA Memory Summary

      SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
      Database Buffers234,881,024
      Fixed Size2,142,568
      Redo Buffers2,162,688
      Variable Size599,674,520

      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 memory152.00152.000.00
      largePX msg pool1.031.030.00
      largefree memory14.9714.970.00
      sharedASH buffers15.5015.500.00
      sharedCCursor14.9418.5724.24
      sharedHeap0: KGL2.882.88-0.04
      sharedKCB Table Scan Buffer3.803.800.00
      sharedKGLS heap5.655.680.51
      sharedKQR M PO3.483.490.24
      sharedKSFD SGA I/O b3.793.790.00
      sharedKTI-UNDO3.223.220.00
      sharedPCursor7.9312.7961.21
      sharedPL/SQL DIANA 3.41
      sharedPL/SQL MPCODE3.813.922.90
      sharedevent statistics per sess3.943.940.00
      sharedfree memory73.5632.39-55.97
      sharedkglsim hash table bkts4.004.000.00
      sharedlibrary cache13.7414.394.72
      sharedrow cache7.167.160.00
      sharedsql area69.2698.4942.21
      streamsfree memory100.00100.000.00
      buffer_cache224.00224.000.00
      fixed_sga2.042.040.00
      log_buffer2.062.060.00

        Back to Memory Statistics

      Back to Top 

       

      Streams Statistics

      Back to Top   

      Streams CPU/IO Usage

      • Streams processes ordered by CPU usage
      • CPU and I/O Time in micro seconds
      Session TypeCPU TimeUser I/O TimeSys I/O Time
      QMON Coordinator579,72600
      QMON Slaves477,70700

        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

      • Rule Sets ordered by Evaluations
      Ruleset NameEvalsFast EvalsSQL ExecsCPU TimeElapsed Time
      SYS.ALERT_QUE_R00000

      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)
      _shared_pool_reserved_min_alloc10000000 
      aq_tm_processes1 
      audit_file_dest/db/oralog/glodist 
      audit_trailDB 
      background_dump_dest/db/oralog/glodist 
      compatible10.2.0.4 
      control_files/db/oractrl01/glodist/CONTROL01.ctl, /db/oractrl02/glodist/CONTROL02.ctl 
      core_dump_dest/db/oralog/glodist 
      db_block_size8192 
      db_cache_size159383552 
      db_file_multiblock_read_count32 
      db_files200 
      db_nameglodist 
      dml_locks1000 
      fast_start_mttr_target300 
      global_namesFALSE 
      java_pool_size159383552 
      job_queue_processes4 
      large_pool_size16777216 
      log_archive_dest/db/oraarch/glodist/arch_ 
      log_archive_format%t_%s_%r.ARC 
      log_buffer2097152 
      max_dump_file_size10240 
      nls_date_formatDD-MON-RR HH24:MI:SS 
      open_cursors300 
      os_authent_prefix   
      parallel_max_servers24 
      parallel_min_servers8 
      pga_aggregate_target209715200 
      processes400 
      query_rewrite_enabledTRUE 
      query_rewrite_integrityTRUSTED 
      remote_os_authentTRUE 
      resource_manager_planXRO_PLAN 
      session_max_open_files50 
      sga_target838860800 
      shared_pool_size314572800 
      streams_pool_size104857600 
      timed_statisticsTRUE 
      undo_managementAUTO 
      undo_retention7200 
      undo_tablespaceUNDOTBS 
      user_dump_dest/db/oralog/glodist 
      utl_file_dir/tmp 


      Back to Top

       

       

       

       

       

      End of Report

      Thanks,

      jayraj

        • 1. Re: Analyze why aud$ table making more IO?
          John Stegeman

          More I/O than what?

           

          Do you understand what AUD$ is and what it's used for? Do you understand what your auditing settings are and whether they are appropriate for your needs?

           

          Oh, and if you want urgent help, you need to pay for it, either via Oracle Support or hiring someone. This is a community help forum, so it's not the place for your urgent requests.

          • 2. Re: Analyze why aud$ table making more IO?
            Jonathan Lewis

            Looking at your headline figure for 10 hours

            a) You're not doing very much work anyway

            b) Most of the work you are doing looks like a couple of parallel queries that might be more important

            c) The AUD$ thing might be related to this:  Failed Login | Oracle Scratchpad

             

            You need to look at the execution plan, and look for the session that's running that query. (v$session by sql_id).

             

            Regards

            Jonathan Lewis

            • 3. Re: Analyze why aud$ table making more IO?
              Adrian Angelov

              Hi,

               

              sql_id: 376u7a6frhp20 is on the top in the AWR  SQL Stats

              the top-most by cpu time

              the top-most by reads

              the top-most by gets

              the 4th one by elapsed time

               

              Its sql text is: select * from dba_audit_trail where extended_timestamp > :1 order by extended_timestamp

               

              dba_audit_trail uses SYS.AUD$ and this table tends to increase in size over time( especially when you audit frequently executed commands). The table has no indexes(at least this was the case when I last dealt with it in 10g/11g, not sure about 12c) and because of this full table scan is used when statements use it. I guess this is your case too.

               

              Get the size of the table by executing select bytes from dba_segments where segment_name='AUD$';

               

              To workaround this excessive resource usage, you can export the contents of AUD$ and truncate it.

              Something like:

               

              create table a1 as select * from sys.AUD$; (set tablespace and any other additional clauses)

              truncate table sys.AUD$;

               

              Please, note that the above approach will delete records that were added while you're creating a1. Doing such maintenance work on regular basis will reduce the size of AUD$ and thus resources used for full table scans on it. If you want to not have data loss because of the truncate, do the exports frequently but instead of truncate, use deletes of older data.

               

              You'll have to use a modified version of your audit SQLs - to use not only the AUD$ data but your old data in other backup tables too, in the case where older data than the one in AUD$ is needed.

               

              Hope this helps

               

              Thank you

              • 4. Re: Analyze why aud$ table making more IO?
                user11978929

                Thanks Adrian for the update.