Re: Query hangs suddenly

Does the query continue to accumulate CPU time and increase in logical I/O? Is it generating a read consistent view because of another process changing the data? 



Regards,
Daniel Fink

Ram Raman 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.

Other related posts: