Query hangs suddenly

From: Ram Raman <veeeraman_at_gmail.com>
Date: Wed, 28 Feb 2007 13:13:47 -0600
Message-ID: <effc058d0702281113l599f0dd2wab529b59952d64fb@mail.gmail.com>

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.

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

Received on Wed Feb 28 2007 - 13:13:47 CST