Re: Query hangs suddenly

  • From: "Ram Raman" <veeeraman@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 28 Feb 2007 13:17:24 -0600

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@xxxxxxxxx> 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')  ---<<

           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)


                           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
---------- ----------
--------------- -------------------
        51 SQL*Net message to client
driver id

0         -1
            543 WAITED KNOWN TIME

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

Other related posts: