Hi,
i am using oracle 10g and i am getting ORA-1555 i know what it is and why it arise.
but in my case. these are some of the observations and i know increasing undo tablespace size is the last solution but i want to make it at last.
my alert log file
Fri Dec 11 03:51:20 2009
Thread 1 advanced to log sequence 72351
Current log# 2 seq# 72351 mem# 0: /nfmp/dbs/redo/redo02.log
Fri Dec 11 04:09:56 2009
ORA-01555 caused by SQL statement below (SQL ID: 82h3avk0wnw6g, Query Duration=580 sec, SCN: 0x0983.b1fdb20d):
Fri Dec 11 04:09:56 2009
select NVL(CON_BILL_PHONE,' ') ,NVL(GEN_ACCT_NUM,' ') ,NVL(CREDIT_RISK,' ') ,TO_CHAR(NVL(SVC_CON_DT,TO_DATE('19700101000000','yyyymmddhh24miss')),'yyyymmddhh24miss') ,NVL(SPARE_8,0) from SUBSCRIBERS where CON_BILL_PHONE like '%'
Fri Dec 11 04:09:56 2009
ORA-01555 caused by SQL statement below (SQL ID: 82h3avk0wnw6g, Query Duration=578 sec, SCN: 0x0983.b1fdb31b):
Fri Dec 11 04:09:56 2009
select NVL(CON_BILL_PHONE,' ') ,NVL(GEN_ACCT_NUM,' ') ,NVL(CREDIT_RISK,' ') ,TO_CHAR(NVL(SVC_CON_DT,TO_DATE('19700101000000','yyyymmddhh24miss')),'yyyymmddhh24miss') ,NVL(SPARE_8,0) from SUBSCRIBERS where CON_BILL_PHONE like '%'
Fri Dec 11 04:09:56 2009
ORA-01555 caused by SQL statement below (SQL ID: 82h3avk0wnw6g, Query Duration=581 sec, SCN: 0x0983.b1fdb18d):
Fri Dec 11 04:09:56 2009
ORA-01555 caused by SQL statement below (SQL ID: 82h3avk0wnw6g, Query Duration=581 sec, SCN: 0x0983.b1fdb16e):
Fri Dec 11 04:09:56 2009
select NVL(CON_BILL_PHONE,' ') ,NVL(GEN_ACCT_NUM,' ') ,NVL(CREDIT_RISK,' ') ,TO_CHAR(NVL(SVC_CON_DT,TO_DATE('19700101000000','yyyymmddhh24miss')),'yyyymmddhh24miss') ,NVL(SPARE_8,0) from SUBSCRIBERS where CON_BILL_PHONE like '%'
Fri Dec 11 04:09:56 2009
select NVL(CON_BILL_PHONE,' ') ,NVL(GEN_ACCT_NUM,' ') ,NVL(CREDIT_RISK,' ') ,TO_CHAR(NVL(SVC_CON_DT,TO_DATE('19700101000000','yyyymmddhh24miss')),'yyyymmddhh24miss') ,NVL(SPARE_8,0) from SUBSCRIBERS where CON_BILL_PHONE like '%'
Fri Dec 11 04:11:34 2009
Thread 1 advanced to log sequence 72352
Current log# 1 seq# 72352 mem# 0: /nfmp/dbs/redo/redo01.log
Fri Dec 11 04:21:21 2009
Thread 1 cannot allocate new log, sequence 72353
Private strand flush not complete
Current log# 1 seq# 72352 mem# 0: /nfmp/dbs/redo/redo01.log
Thread 1 advanced to log sequence 72353
Current log# 3 seq# 72353 mem# 0: /nfmp/dbs/redo/redo03.log
Fri Dec 11 04:41:58 2009
Thread 1 advanced to log sequence 72354
Current log# 2 seq# 72354 mem# 0: /nfmp/dbs/redo/redo02.log
Fri Dec 11 04:51:27 2009
as per my observation * Query Duration=578 sec,* like 10 minutes but by undo parameters are like
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDO
which is more than query is running
this is the query taking longest time
SQL> select * from v$undostat where MAXQUERYLEN =(select max(maxquerylen) from v$undostat);
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXQUERYLEN MAXQUERYID MAXCONCURRENCY UNXPSTEALCNT UNXPBLKRELCNT UNXPBLKREUCNT EXPSTEALCNT EXPBLKRELCNT EXPBLKREUCNT SSOLDERRCNT NOSPACEERRCNT ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
----------- ----------- ---------- ---------- ---------- ----------- ------------- -------------- ------------ ------------- ------------- ----------- ------------ ------------ ----------- ------------- ---------- ------------- ----------- -------------------
08/12/2009 08/12/2009 1 1176 21837 13318 dpkb8fgz1h8m8 6 0 0 0 0 0 0 0 0 1792 63408 1664 28374
while this query is not longest running query
SQL> select BEGIN_TIME,END_TIME,MAXQUERYLEN,MAXQUERYSQLID,TUNED_UNDORETENTION from dba_hist_undostat where snap_id > 27227 order by begin_time desc;
BEGIN_TIME END_TIME MAXQUERYLEN MAXQUERYSQLID TUNED_UNDORETENTION
----------- ----------- ----------- ------------- -------------------
11/12/2009 11/12/2009 871 dpkb8fgz1h8m8 13304
11/12/2009 11/12/2009 0 12862
11/12/2009 11/12/2009 0 12423
11/12/2009 11/12/2009 0 11971
11/12/2009 11/12/2009 0 11522
11/12/2009 11/12/2009 920 82h3avk0wnw6g 11064
11/12/2009 11/12/2009 321 82h3avk0wnw6g 10606
11/12/2009 11/12/2009 0 10142
11/12/2009 11/12/2009 0 9676
11/12/2009 11/12/2009 0 9204
11/12/2009 11/12/2009 786 82h3avk0wnw6g 8730
11/12/2009 11/12/2009 186 82h3avk0wnw6g 8250
12 rows selected
please advice what is the reason for it and how to avoid it.how to find detail what is making this happen.
thanks
umesh