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.
