Re: Query hangs suddenly

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

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