Re: Query hangs suddenly

From: V R <vragunat_at_yahoo.com>
Date: Wed, 28 Feb 2007 13:36:14 -0800 (PST)
Message-ID: <557744.11513.qm@web36711.mail.mud.yahoo.com>

Do you have the row source information from 10046 trace? You mentioned that the cost is almost same. Has the plan changed?

Ram Raman <veeeraman_at_gmail.com> wrote: oops, the date we use is '26-FEB-2007', not '30-AUG-2006'  ie the line "AND a.audit_stamp > to_date('30-AUG-2006','DD-MON-YYYY')"   

 What we run is   

 "AND a.audit_stamp > to_date('26-FEB-2007','DD-MON-YYYY')    

 On 2/28/07, Ram Raman <veeeraman_at_gmail.com> wrote: Hi all,   

 We have a query which is run everyday and was running OK till last week. The query starts hanging for the past 2 days when run. Oracle is 9.2. We run this query for today's date ("audit_stamp" - See the comment on the query) or yesterday's date and it returns data quickly. But when the audit_stamp is older than the past 2 days, it hangs. The cost from the plan in all the cases is the almost the same using autotrace traceonly.         

 We reanalyzed all the tables yesterday evening as part of trying to solve the problem. We bounced the instance too.      

 Here is the query:   

  SELECT sysdate, substr(a.key1, 1, 15), b.account_alias, substr(a.key2, 1, 15), ' ', 'N'

           FROM psadm.psaudit a, psadm.ps_acct_alias b
          WHERE (a.recname = 'ACCOUNT'
            AND a.fieldname IN ('EFFDT', 'ACCOUNT_TYPE')
            OR  a.recname = 'ACCT_OWNER'
            AND a.fieldname IN ('MSIS_ID', 'MSIS_TYPE'))
            AND a.audit_stamp > to_date('30-AUG-2006','DD-MON-YYYY')  ---<< DATE 
            AND substr(a.key1, 1, 3) = b.clear_firm
            AND substr(a.key2, 1, 15) = b.account_no
            AND b.effdt = (SELECT max(b1.effdt)
                     FROM psadm.ps_acct_alias b1
                     WHERE b.clear_firm = b1.clear_firm
                       AND b.account_no = b1.account_no
                       AND b1.effdt <= sysdate)
            AND NOT EXISTS (SELECT 'X'
                            FROM psadm.ps_reprice_tbl x
                            WHERE x.process_name = 'CXOCDB04'
                              AND x.process_date = sysdate
                              AND x.exec_firm_no = substr(a.key1, 1, 15)
                              AND x.entry_account = b.account_alias
                              AND x.account_no = substr(a.key2, 1, 15)
                              AND x.processed_flag = 'N')
          GROUP BY sysdate, substr(a.key1, 1, 15), b.account_alias,
                   substr(a.key2, 1, 15)
  

 #of rows:

 psadm.psaudit: 787,827 rows
 psadm.ps_acct_alias: 224,807 rows
 psadm.ps_reprice_tbl: 0 rows
  

  

 Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=667 Card=1 Bytes=24)    1 0 SORT (GROUP BY) (Cost=667 Card=1 Bytes=24)

   2    1     VIEW (Cost=665 Card=1 Bytes=24)  
   3    2       FILTER
   4    3         SORT (GROUP BY) (Cost=665 Card=1 Bytes=101)
   5    4           NESTED LOOPS (Cost=663 Card=1 Bytes=101)
   6    5             NESTED LOOPS (Cost=662 Card=1 Bytes=82)
    7    6               TABLE ACCESS (FULL) OF 'PSAUDIT' (Cost=657 Car
          d=5 Bytes=280)
     8    6               INDEX (RANGE SCAN) OF 'PS_ACCT_ALIAS' (UNI
          QUE) (Cost=1 Card=1 Bytes=26)
    9    8                 TABLE ACCESS (BY INDEX ROWID) OF 'PS_REP
          RICE_TBL' (Cost=1 Card=1 Bytes=46)
   10    9                   INDEX (UNIQUE SCAN) OF 'PS_REPRICE_TBL
          ' (UNIQUE)
   11    5             INDEX (RANGE SCAN) OF 'PS_ACCT_ALIAS' (UNIQU
          E) (Cost=1 Card=1 Bytes=19)

 The query and the plan are slightly modified for confidentiality.  Here is select from V$session_wait for the session:

        SID EVENT

---------- ----------------------------------------------------------------
P1TEXT                                                                   P1
---------------------------------------------------------------- ----------  
P2TEXT                                                                   P2
---------------------------------------------------------------- ----------
P3TEXT                                                                   P3  WAIT_TIME  
---------------------------------------------------------------- ---------- ----------

SECONDS_IN_WAIT STATE

--------------- -------------------
        51 SQL*Net message to client
driver id                                                        1413697536  
#bytes                                                                    1
                                                                          0         -1
            543 WAITED KNOWN TIME

 I do not understand why it waits for "SQL*Net message to client".

 Thanks.       

---

Everyone is raving about the all-new Yahoo! Mail beta.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 28 2007 - 15:36:14 CST