Jump to content

p_sony_2000@yahoo.com

Members
  • Content count

    5
  • Joined

  • Last visited

Community Reputation

0 Neutral

About p_sony_2000@yahoo.com

  • Rank
    Newbie
  1. Hi, There is a deadlock occurred in our application and the DBA emailed the Oracle Trace file which contains information on the Deadlock. I am unable to identify how the 2 SQL's in the trace file can cause the deadlock. I could not find the data for the rowids mentioned in the trace file. Need help to resolve the deadlock issue after analyzing the trace file DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00130017-00e3e88b 75 354 X 83 618 X TX-00160010-01fc51aa 83 618 X 75 354 X session 354: DID 0001-004B-00A57146 session 618: DID 0001-0053-00F60097 session 618: DID 0001-0053-00F60097 session 354: DID 0001-004B-00A57146 Rows waited on: Session 354: obj - rowid = 0002C8A3 - AAAsijAAMAAPdyCAAC (dictionary objn - 182435, file - 12, block - 4054146, slot - 2) Session 618: obj - rowid = 0002C8A3 - AAAsijAANAAD6igAAA (dictionary objn - 182435, file - 13, block - 1026208, slot - 0) ----- Information for the OTHER waiting sessions ----- Session 618: sid: 618 ser: 15833 audsid: 1467298090 user: 127/APPOWNER flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 83 O/S info: user: grid, term: UNKNOWN, ospid: 705 image: oracle@usplsabdb001 client details: O/S info: user: ecowner, term: , ospid: 702 machine: usplsabdb001 program: runOneProc@usplsabdb001 (TNS V1-V3) application name: runOneProc@usplsabdb001 (TNS V1-V3), hash value=11319988 current SQL: UPDATE PTR_DATA SET PRL_PROD_UPC_MATCH = 'Y' WHERE PL_RTLR_CO_ID = :B2 AND PRL_PROD_UPC_MATCH IS NULL AND SAR_SEQ_NUM = :B1 AND PL_SEQ_NUM IN ( SELECT DISTINCT PL_SEQ_NUM FROM PROMO_LIST, PTR_DATA WHERE SAR_SEQ_NUM = :B1 AND PRL_PROMO_END_DATE >= TRUNC (SYSDATE) AND ( ( (PRL_PROMO_END_DATE >= TO_DATE (PL_START_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_END_DATE <= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) OR ( (PRL_PROMO_START_DATE >= TO_DATE (PL_START_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_START_DATE <= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) OR ( (PRL_PROMO_END_DATE >= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_START_DATE < TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) ) AND PRL_PH1_WSLR_CO_ID = PL_WSLR_CO_ID AND PRL_PH1_RTLR_CO_ID = PL_RTLR_CO_ID AND ( ( (TRIM (PRL_PH4_MKT_QUAL) = TRIM ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=0qmf3j8nk7f29) ----- UPDATE PTR_DATA A SET (A.SPL_CATEGORY, A.SPL_FAMILY, A.SPL_NAME, A.SPL_BRAND, A.SPL_PACKAGE, A.SPL_CASE_TOTAL_NUM, A.SPL_PACKAGE_GROUP) = (SELECT DISTINCT B.SPL_CATEGORY, B.SPL_FAMILY, B.SPL_NAME, B.SPL_BRAND, B.SPL_PACKAGE, B.SPL_CASE_TOTAL_NUM, B.SPL_PACKAGE_GROUP FROM SA_PRODUCT_LIBRARY B WHERE B.SPL_PROD_UPC = A.PL_PROD_UPC) WHERE A.PL_PROD_UPC IS NOT NULL ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x2adccfb00 15408 package body APPOWNER.PRICING 0x2b14c5770 1 anonymous block =================================================== PROCESS STATE ------------- Process global information: process: 0x2a64284a8, call: 0x290c632a8, xact: 0x29f6e8b68, curses: 0x2a65230b0, usrses: 0x2a65230b0 in_exception_handler: no ---------------------------------------- SO: 0x2a64284a8, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x2a64284a8, name=process, file=ksu.h LINE:12721, pg=0 (process) Oracle pid:75, ser:98, calls cur/top: 0x290c632a8/0x290c5ef18 flags : (0x0) - flags2: (0x0), flags3: (0x10) intr error: 0, call error: 0, sess error: 0, txn error 0 intr queue: empty ksudlp FALSE at location: 0 (post info) last post received: 0 0 264 last post received-location: kgsk2.h LINE:669 ID:kgskrunnextint: posting new vt to run last process to post me: 0x2b839de20 108 0 last post sent: 0 0 26 last post sent-location: ksa2.h LINE:285 ID:ksasnd last process posted by me: 0x29e390508 2 6 (latch info) wait_event=0 bits=0x0 Process Group: DEFAULT, pseudo proc: 0x29e3ef680 O/S info: user: grid, term: UNKNOWN, ospid: 64901 OSD pid info: Unix process pid: 64901, image: oracle@usplsabdb001 ---------------------------------------- SO: 0x2a777a570, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a777a570 flags=2560 fib=0x2a35e4510 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/undotbs_1.292.861270615 fno=36 lblksz=8192 fsiz=4194176 ---------------------------------------- SO: 0x2a777a440, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a777a440 flags=2560 fib=0x2a35d7970 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/undotbs_1.263.863072479 fno=3 lblksz=8192 fsiz=2560000 ---------------------------------------- SO: 0x2a777a310, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a777a310 flags=2560 fib=0x2a35ed310 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/undotbs_1.319.938781973 fno=59 lblksz=8192 fsiz=1572864 ---------------------------------------- SO: 0x2a777a1e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a777a1e0 flags=2560 fib=0x2a35eccd8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/undotbs_1.318.938781535 fno=58 lblksz=8192 fsiz=1572864 ---------------------------------------- SO: 0x2a775f0e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775f0e0 flags=2560 fib=0x2a35db728 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.274.860481367 fno=13 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a776e590, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776e590 flags=2560 fib=0x2a35dfb00 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.285.860482879 fno=24 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a776bcd0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776bcd0 flags=2560 fib=0x2a35e38b8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.294.897330327 fno=34 lblksz=8192 fsiz=3840000 ---------------------------------------- SO: 0x2a776b208, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776b208 flags=2560 fib=0x2a35dc9a0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.277.860481647 fno=16 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a776b0c0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776b0c0 flags=2560 fib=0x2a35d9840 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.269.860480905 fno=8 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a776a728, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776a728 flags=2560 fib=0x2a35e2008 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.291.885268403 fno=30 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7769c60, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7769c60 flags=2560 fib=0x2a35de250 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.281.860482349 fno=20 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a77650d0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77650d0 flags=2560 fib=0x2a35dd5f8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.279.860481913 fno=18 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7764fa0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7764fa0 flags=2560 fib=0x2a35dcfd8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.278.860481845 fno=17 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7764c10, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7764c10 flags=2560 fib=0x2a35dc380 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.276.860481485 fno=15 lblksz=8192 fsiz=3200000 ---------------------------------------- SO: 0x2a775ff50, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775ff50 flags=2560 fib=0x2a35e0758 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.287.860483073 fno=26 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7756bd8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7756bd8 flags=2560 fib=0x2a35ea7d0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.313.915868563 fno=52 lblksz=8192 fsiz=2432000 ---------------------------------------- SO: 0x2a7756370, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7756370 flags=2560 fib=0x2a35ea1b0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.312.915868355 fno=51 lblksz=8192 fsiz=2432000 ---------------------------------------- SO: 0x2a7756240, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7756240 flags=2560 fib=0x2a35e8f20 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.309.913366385 fno=48 lblksz=8192 fsiz=3456000 ---------------------------------------- SO: 0x2a77552a0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77552a0 flags=2560 fib=0x2a35ddc30 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.280.860481981 fno=19 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7754b68, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7754b68 flags=2560 fib=0x2a35dbd48 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.275.860481423 fno=14 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7753248, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7753248 flags=2560 fib=0x2a35db0f0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.273.860481293 fno=12 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a77529e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77529e0 flags=2560 fib=0x2a35daad0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.272.860481231 fno=11 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a77528b0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77528b0 flags=2560 fib=0x2a35d9e78 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.270.860480995 fno=9 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a77431a0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77431a0 flags=2560 fib=0x2a35d8be8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.267.860480625 fno=6 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a7740da0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7740da0 flags=2560 fib=0x2a35e13b0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.289.860483209 fno=28 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a77523d8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77523d8 flags=2560 fib=0x2a35e19e8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.261.884958689 fno=29 lblksz=8192 fsiz=4194176 ---------------------------------------- SO: 0x2a77522a8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77522a8 flags=2560 fib=0x2a35e0d90 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.288.860483181 fno=27 lblksz=8192 fsiz=4185600 SO: 0x2a774edd8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a774edd8 flags=2560 fib=0x2a35eba60 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.320.966325123 fno=55 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a774e588, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a774e588 flags=2560 fib=0x2a35de888 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.282.860482445 fno=21 lblksz=8192 fsiz=3840000 ---------------------------------------- SO: 0x2a774dd20, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a774dd20 flags=2560 fib=0x2a35df4e0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.284.860482773 fno=23 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7743790, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7743790 flags=2560 fib=0x2a35eb428 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.315.915869027 fno=54 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7743530, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7743530 flags=2560 fib=0x2a35e82c8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.307.912580283 fno=46 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a77408e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77408e0 flags=2560 fib=0x2a35e3298 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.293.897330201 fno=33 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7761c18, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7761c18 flags=2560 fib=0x2a35e9b78 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.311.913366471 fno=50 lblksz=8192 fsiz=3456000 SO: 0x2a77614e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77614e0 flags=2560 fib=0x2a35e9558 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.310.913366421 fno=49 lblksz=8192 fsiz=3456000 ---------------------------------------- SO: 0x2a77613b0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77613b0 flags=2560 fib=0x2a35e7ca8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.306.912396193 fno=45 lblksz=8192 fsiz=3328000 ---------------------------------------- SO: 0x2a7760688, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7760688 flags=2560 fib=0x2a35e57a0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.300.902202953 fno=39 lblksz=8192 fsiz=3456000 ---------------------------------------- SO: 0x2a775f818, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775f818 flags=2560 fib=0x2a35e5168 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.297.897330475 fno=38 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a775d418, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775d418 flags=2560 fib=0x2a35eae08 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.314.915868959 fno=53 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a775d1b8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775d1b8 flags=2560 fib=0x2a35e2640 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.268.885268515 fno=31 lblksz=8192 fsiz=4194176 ---------------------------------------- SO: 0x2a775ccf8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775ccf8 flags=2560 fib=0x2a35e4b48 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.296.897330425 fno=37 lblksz=8192 fsiz=3840000 ---------------------------------------- SO: 0x2a775c490, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775c490 flags=2560 fib=0x2a35e63f8 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.302.904197903 fno=41 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a775b750, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a775b750 flags=2560 fib=0x2a35e7670 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.305.912141273 fno=44 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7759828, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7759828 flags=2560 fib=0x2a35e3ef0 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.295.897330381 fno=35 lblksz=8192 fsiz=3840000 ---------------------------------------- SO: 0x2a7758c30, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7758c30 flags=2560 fib=0x2a35da498 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_data.271.860481087 fno=10 lblksz=8192 fsiz=4096000 ---------------------------------------- SO: 0x2a77583c8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a77583c8 flags=2560 fib=0x2a35e2c60 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.258.897330139 fno=32 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7758038, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7758038 flags=2560 fib=0x2a35e8900 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.308.912580345 fno=47 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7757a30, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7757a30 flags=2560 fib=0x2a35e0138 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/ab_index.286.860482995 fno=25 lblksz=8192 fsiz=4185600 ---------------------------------------- SO: 0x2a7757900, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a7757900 flags=2560 fib=0x2a35e7050 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/system.304.911263809 fno=43 lblksz=8192 fsiz=1920000 ---------------------------------------- SO: 0x29d7a4fb8, type: 124, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=ASM map operations, file=kffm2.h LINE:418, pg=0 transition:(nil) busylist:[29d7a5020,29d7a5020] freelist:[2b59bf1a0,2b4d030b8] KFFMOP: hash link:[2b59bf190,2b59bf190] sobj link:[2b4f89cf8,29d7a5010] map kggrp:[0x2b5fab848, 0, valid] map id:44 group:[2,1412979558] file:[302,904197903] extent:19408 flags:0x0000 disk:12 au:25307 lock:0 proc:0x2a64284a8 KFFMOP: hash link:[2b4f89ce8,2b4f89ce8] sobj link:[2b4d030b8,2b59bf1a0] map kggrp:[0x2b5fab848, 0, valid] map id:44 group:[2,1412979558] file:[302,904197903] extent:14252 flags:0x0000 disk:19 au:60250 lock:0 proc:0x2a64284a8 KFFMOP: hash link:[2b4d030a8,2b4d030a8] sobj link:[29d7a5010,2b4f89cf8] map kggrp:[0x2b5fab848, 0, valid] map id:44 group:[2,1412979558] file:[302,904197903] extent:19408 flags:0x0000 disk:12 au:25307 lock:0 proc:0x2a64284a8 ---------------------------------------- SO: 0x2a776de58, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1 proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0 (FOB) 0x2a776de58 flags=2560 fib=0x2a35d6d18 incno=0 pending i/o cnt=0 fname=+DATA_GRP/prod/datafile/system.256.860473317 fno=1 lblksz=8192 fsiz=4192000 ---------------------------------------- SO: 0x2a65230b0, type: 4, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x2a64284a8, name=session, file=ksu.h LINE:12729, pg=0 (session) sid: 354 ser: 14153 trans: 0x29f6e8b68, creator: 0x2a64284a8 flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC DID: , short-term DID: txn branch: (nil) edition#: 100 oct: 6, prv: 0, sql: 0x2b56b2f78, psql: 0x2b53d55b0, user: 127/APPOWNER ksuxds FALSE at location: 0 service name: PROD client details: O/S info: user: ecowner, term: , ospid: 64898 machine: usplsabdb001 program: runOneProc@usplsabdb001 (TNS V1-V3) application name: runOneProc@usplsabdb001 (TNS V1-V3), hash value=11319988 Current Wait Stack: 0: waiting for 'enq: TX - row lock contention' name|mode=0x54580006, usn<<16 | slot=0x160010, sequence=0x1fc51aa wait_id=24015 seq_num=24016 snap_id=1 wait times: snap=6.303755 sec, exc=6.303755 sec, total=6.303755 sec wait times: max=infinite, heur=6.303755 sec wait counts: calls=3 os=3 in_wait=1 iflags=0x15a0 There is at least one session blocking this session. Dumping 1 direct blocker(s): inst: 1, sid: 618, ser: 15833 Dumping final blocker: inst: 1, sid: 618, ser: 15833 Wait State: fixed_waits=0 flags=0x22 boundary=(nil)/-1 Session Wait History: elapsed time of 0.036797 sec since current wait 0: waited for 'db file sequential read' file#=0x29, block#=0x25e825, blocks=0x1 wait_id=24014 seq_num=24015 snap_id=1 wait times: snap=0.000129 sec, exc=0.000129 sec, total=0.000129 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000447 sec of elapsed time 1: waited for 'db file sequential read' file#=0x29, block#=0x1bd666, blocks=0x1 wait_id=24013 seq_num=24014 snap_id=1 wait times: snap=0.000137 sec, exc=0.000137 sec, total=0.000137 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000969 sec of elapsed time 2: waited for 'db file sequential read' file#=0x29, block#=0x25e819, blocks=0x1 wait_id=24012 seq_num=24013 snap_id=1 wait times: snap=0.000128 sec, exc=0.000128 sec, total=0.000128 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000815 sec of elapsed time 3: waited for 'db file sequential read' file#=0x20, block#=0x3d0ef6, blocks=0x1 wait_id=24011 seq_num=24012 snap_id=1 wait times: snap=0.000132 sec, exc=0.000132 sec, total=0.000132 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.001581 sec of elapsed time 4: waited for 'db file sequential read' file#=0x29, block#=0x25e821, blocks=0x1 wait_id=24010 seq_num=24011 snap_id=1 wait times: snap=0.000127 sec, exc=0.000127 sec, total=0.000127 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000073 sec of elapsed time 5: waited for 'db file sequential read' file#=0x29, block#=0x1bd662, blocks=0x1 wait_id=24009 seq_num=24010 snap_id=1 wait times: snap=0.000283 sec, exc=0.000283 sec, total=0.000283 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000869 sec of elapsed time 6: waited for 'db file sequential read' file#=0x20, block#=0x3d0eee, blocks=0x1 wait_id=24008 seq_num=24009 snap_id=1 wait times: snap=0.000136 sec, exc=0.000136 sec, total=0.000136 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.001619 sec of elapsed time 7: waited for 'db file sequential read' file#=0x29, block#=0x25e81d, blocks=0x1 wait_id=24007 seq_num=24008 snap_id=1 wait times: snap=0.000141 sec, exc=0.000141 sec, total=0.000141 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000077 sec of elapsed time 8: waited for 'db file sequential read' file#=0x29, block#=0x1bd60b, blocks=0x1 wait_id=24006 seq_num=24007 snap_id=1 wait times: snap=0.000145 sec, exc=0.000145 sec, total=0.000145 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000102 sec of elapsed time 9: waited for 'db file sequential read' file#=0x27, block#=0x13b300, blocks=0x1 wait_id=24005 seq_num=24006 snap_id=1 wait times: snap=0.008147 sec, exc=0.008147 sec, total=0.008147 sec wait times: max=infinite wait counts: calls=0 os=0 occurred after 0.000448 sec of elapsed time Sampled Session History of session 354 serial 14153 --------------------------------------------------- The sampled session history is constructed by sampling the target session every 1 second. The sampling process captures at each sample if the session is in a non-idle wait, an idle wait, or not in a wait. If the session is in a non-idle wait then one interval is shown for all the samples the session was in the same non-idle wait. If the session is in an idle wait or not in a wait for consecutive samples then one interval is shown for all the consecutive samples. Though we display these consecutive samples in a single interval the session may NOT be continuously idle or not in a wait (the sampling process does not know). The history is displayed in reverse chronological order. sample interval: 1 sec, max history 120 sec --------------------------------------------------- temporary object counter: 0 ---------------------------------------- Virtual Thread: kgskvt: 0x2b8719d90, sess: 0x2a65230b0 sid: 354 ser: 14153 vc: (nil), proc: 0x2a64284a8, id: 354 consumer group cur: OTHER_GROUPS (upd? 0), mapped: DEFAULT_CONSUMER_GROUP, orig: vt_state: 0x8, vt_flags: 0xE030, blkrun: 1, numa: 1 inwait: 1, short wait event: 241 posted_run: 0 location where insched last set: kgskbwt location where insched last cleared: kgskbwt location where inwait last set: kgskbwt location where inwait last cleared: NULL is_assigned: 1, in_sched: 0 (0) qcls: 0x2b3aa0f50, qlink: TRUE vt_active: 0 (pending: 1) vt_pq_active: 0, dop: 0 used quanta (usecs): stmt: 5186860, accum: 0, mapped: 0, tot: 5186860 cpu start time: 0 idle time: 0, active time: 4184715 (cg: 4184715) cpu yields: stmt: 0, accum: 0, mapped: 0, tot: 0 cpu waits: stmt: 0, accum: 0, mapped: 0, tot: 0 cpu wait time (usecs): stmt: 288, accum: 0, mapped: 0, tot: 288 io waits: stmt: 0, accum: 0, mapped: 0, tot: 0 io wait time: stmt: 0, accum: 0, mapped: 0, tot: 0 ASL queued time outs: 0, time: 0 (cur 0, cg 0) PQQ queued time outs: 0, time: 0 (cur 0, cg 0) Queue timeout violation: 0 calls aborted: 0, num est exec limit hit: 0 undo current: 0k max: 0k I/O credits acquired:small=0 large=0 I/O credits waiting for:small=0 large=0 KTU Session Commit Cache Dump for IDLs: KTU Session Commit Cache Dump for Non-IDLs: ---------------------------------------- KKS-UOL used : 0 locks(used=8, free=2) KGX Atomic Operation Log 0x2b541d988 Mutex (nil)(0, 0) idn 0 oper NONE(0) hash table uid 354 efd 15 whr 1 slp 0 oper=NONE pt1=(nil) pt2=(nil) pt3=(nil) pt4=(nil) u41=0 stt=0 KGX Atomic Operation Log 0x2b541d9d8 Mutex (nil)(0, 0) idn 0 oper NONE(0) hash table uid 354 efd 16 whr 3 slp 0 oper=NONE pt1=0x7f1192d8 pt2=0x7f119680 pt3=0x7f1192c8 pt4=(nil) u41=0 stt=0 KGX Atomic Operation Log 0x2b541da28 Mutex (nil)(0, 0) idn 0 oper NONE(0) FSO mutex uid 354 efd 0 whr 0 slp 0 KGX Atomic Operation Log 0x2b541da78 Mutex (nil)(0, 0) idn 0 oper NONE(0) FSO mutex uid 354 efd 0 whr 0 slp 0 KGL-UOL SO Cache(total=111, free=18) KGX Atomic Operation Log 0x2b3b2d8f8 Mutex (nil)(0, 0) idn 0 oper NONE(0) Library Cache uid 354 efd 23 whr 85 slp 0 oper=0 pt1=0x2ab3dab00 pt2=0x2b3768150 pt3=(nil) pt4=(nil) pt5=(nil) ub4=0 KGX Atomic Operation Log 0x2b3b2d950 Mutex (nil)(0, 0) idn 0 oper NONE(0) Library Cache uid 354 efd 23 whr 64 slp 0 oper=0 pt1=(nil) pt2=0x2b3768150 pt3=(nil) pt4=(nil) pt5=(nil) ub4=126172 KGX Atomic Operation Log 0x2b3b2d9a8 Mutex 0x2b5601a48(0, 0) idn b349788 oper NONE(0) Library Cache uid 354 efd 21 whr 70 slp 0 oper=0 pt1=0x2b5601908 pt2=(nil) pt3=(nil) pt4=(nil) pt5=(nil) ub4=4 KGX Atomic Operation Log 0x2b3b2da00 Mutex 0x2b5601a48(0, 0) idn b349788 oper NONE(0) Library Cache uid 354 efd 21 whr 70 slp 0 oper=0 pt1=0x2b5601908 pt2=(nil) pt3=(nil) pt4=(nil) pt5=(nil) ub4=0 KGX Atomic Operation Log 0x2b3b2da58 Mutex (nil)(0, 0) idn 0 oper NONE(0) Library Cache uid 354 efd 0 whr 0 slp 0 oper=0 pt1=(nil) pt2=(nil) pt3=(nil) pt4=(nil) pt5=(nil) ub4=0 KGX Atomic Operation Log 0x2b3b2dab0 Mutex (nil)(0, 0) idn 0 oper NONE(0) Library Cache uid 354 efd 0 whr 0 slp 0 oper=0 pt1=(nil) pt2=(nil) pt3=(nil) pt4=(nil) pt5=(nil) ub4=0 KGX Atomic Operation Log 0x2b3b2db08 Mutex (nil)(0, 0) idn 0 oper NONE(0) Library Cache uid 354 efd 0 whr 0 slp 0 oper=0 pt1=(nil) pt2=(nil) pt3=(nil) pt4=(nil) pt5=(nil) ub4=0 KGL SO cache SO=0x2b3768150 Link=0x2b37681c0[0x2b3768028,0x2b3b2d8e0] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3767fb8 Link=0x2b3768028[0x2b3767e28,0x2b37681c0] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3767db8 Link=0x2b3767e28[0x2b390efe0,0x2b3768028] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b390ef70 Link=0x2b390efe0[0x2b3828010,0x2b3767e28] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827fa0 Link=0x2b3828010[0x2b3827f10,0x2b390efe0] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827ea0 Link=0x2b3827f10[0x2b3827e10,0x2b3828010] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827da0 Link=0x2b3827e10[0x2b3827d10,0x2b3827f10] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827ca0 Link=0x2b3827d10[0x2b3827c10,0x2b3827e10] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827ba0 Link=0x2b3827c10[0x2b3827b10,0x2b3827d10] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3827aa0 Link=0x2b3827b10[0x2b3441b00,0x2b3827c10] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3441a90 Link=0x2b3441b00[0x2b3441a00,0x2b3827b10] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b3441990 Link=0x2b3441a00[0x2b331d5d8,0x2b3441b00] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b331d568 Link=0x2b331d5d8[0x2b331d4d8,0x2b3441a00] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b331d468 Link=0x2b331d4d8[0x2b331d3d8,0x2b331d5d8] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b331d368 Link=0x2b331d3d8[0x2b331d2d8,0x2b331d4d8] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b331d268 Link=0x2b331d2d8[0x2b39ab258,0x2b331d3d8] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b39ab1e8 Link=0x2b39ab258[0x2b39ab158,0x2b331d2d8] flg=8000 use=0x2a65230b0 ses=0x2a65230b0 SO=0x2b39ab0e8 Link=0x2b39ab158[0x2b3b2d8e0,0x2b39ab258] flg=8000 use=0x2a65230b0 ses=0x2a651ffd0
  2. This is on Oracle version 11.2.0.4
  3. Hi, I have a materialized view created along start with and next clause for a complete refresh. The complete refresh takes 1+ hour for 5million rows. I want to change the refresh to non-atomic with the atomic-refresh = False. Can this be achieved in the create MV statement or do we need a separate job. Also tried creating the MV as BUILD DEFERRED REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY and then refresh using DBMS_MVIEW.REFRESH (MV_NAME, METHOD => 'C', ATOMIC_REFRESH => FALSE); But here also the refresh takes 1 hour.
  4. Max of a value among rows

    found the solution on my own
  5. Max of a value among rows

    Hi, I have the below data in one of our Materialized View R879_PH1_SEQ_NUM R879_PH1_WSLR_CO_ID R879_PH1_RTLR_CO_ID R879_PD1_SEQ_NUM R879_PD1_DATE_QUAL R879_PD1_DATE_CALC R879_PD1_UPC_CASE R879_PD1_UPCEAN_PC R879_PD4_SEQ_NUM ORIG_R879_PD4_MAC_QUAL R879_PD4_MAC_ID CONSUMER_NUM CASE_NUM 199714 1000001887 1000000558 32938719 07' 12/28/2015 18200960468 18200001741 48583194 5 02N03 18200001741 18200960468 193703 1000001887 1000000558 32373409 07' 9/26/2016 18200960468 18200001741 47911465 5 02N03 18200001741 18200960468 200335 1000001887 1000000558 33049290 07' 9/26/2016 18200960468 18200001741 48732282 5 02N03 18200001741 18200960468 I want to get the max of R879_PH1_SEQ_NUM along with other columns of the corresponding max r879_PH1_seq_num row. I used the below query which uses co-related subqueries, but the performance is bad for all data. SELECT mvup.r879_ph1_seq_num AS pl_r879_ph1_seq_num, mvup.r879_pd1_seq_num AS pl_r879_pd1_seq_num, mvup.r879_ph1_wslr_co_id AS pl_r879_ph1_wslr_co_id, mvup.r879_ph1_rtlr_co_id AS pl_r879_ph1_rtlr_co_id, mvup.r879_pd1_upcean_pc AS pl_r879_pd1_upcean_pc, mvup.r879_pd1_upc_case AS pl_r879_pd1_upc_case, mvup.orig_r879_pd4_mac_qual AS pl_r879_pd4_mac_qual, mvup.r879_pd4_mac_id AS pl_r879_pd4_mac_id, mvup.r879_pd1_date_calc AS pl_r879_pd1_start_date, ADD_MONTHS (mvup.r879_pd1_date_calc, 24) AS pl_r879_pd1_end_date FROM unauthorized_products mvup WHERE mvup.r879_ph1_rtlr_co_id = 1000000558 AND EXISTS (SELECT 1 FROM r879_ph1 ph1 WHERE ph1.r879_ph1_seq_num = mvup.r879_ph1_seq_num AND ph1.r879_ph1_doc_type = 'PRC') AND mvup.R879_PD1_DATE_QUAL = '07' AND TRUNC (mvup.r879_pd1_date_calc) < TRUNC (SYSDATE + 1) AND mvup.R879_PH1_SEQ_NUM = (SELECT MAX (b.R879_PH1_SEQ_NUM) FROM unauthorized_products b WHERE mvup.r879_ph1_rtlr_co_id = b.r879_ph1_rtlr_co_id AND mvup.r879_ph1_wslr_co_id = b.r879_ph1_wslr_co_id AND mvup.consumer_num = b.consumer_num AND mvup.case_num = b.case_num AND mvup.ORIG_R879_PD4_MAC_QUAL = b.ORIG_R879_PD4_MAC_QUAL AND NVL (mvup.R879_PD4_MAC_ID, ' ') = NVL (b.R879_PD4_MAC_ID, ' ') AND b.R879_PD1_DATE_QUAL = '07' AND TRUNC (mvup.r879_pd1_date_calc) < TRUNC (SYSDATE + 1) ) I need to tune this query or use analytical function which is faster in performance. Can you please help me in modifying the query in using analytical function
×