This discussion is archived
8 Replies Latest reply: Mar 3, 2013 2:22 AM by CKPT RSS

ORA-01555 while gathering table statistics

Neo-b Newbie
Currently Being Moderated
Hello All,

While running a job to gather table statics it failed and I saw in my alert logs ORA-01555, below is the job:
DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO');
What is the reason behind the ORA-01555 ?
How can I prevent it in the future ?

Regards,
  • 1. Re: ORA-01555 while gathering table statistics
    mseberg Guru
    Currently Being Moderated
    Hello;

    Increase the size of your rollback.

    Run dbms_stats.gather_schema_stats when activity is less.

    Check MOS doc

    ORA-01555 Using Automatic Undo Management - Causes and Solutions [ID 269814.1]

    Best Regards

    mseberg
  • 2. Re: ORA-01555 while gathering table statistics
    Neo-b Newbie
    Currently Being Moderated
    Increase the size of your rollback.
    Do you mean the undo_retention ?
  • 3. Re: ORA-01555 while gathering table statistics
    CKPT Guru
    Currently Being Moderated
    Neo-b wrote:
    Hello All,

    While running a job to gather table statics it failed and I saw in my alert logs ORA-01555, below is the job:
    DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO');
    What is the reason behind the ORA-01555 ?
    How can I prevent it in the future ?

    Regards,
    Can you gather which query ran when snapshot too old error occurred? You saw it in alert log or the statement given error?
    select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time;
    Do you have timestamp when you ran gather stats? Then crosscheck the time with the below output of the query
    set pagesize 25
    set linesize 120
    
    select inst_id,
              to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
              UNXPSTEALCNT "# Unexpired|Stolen",
              EXPSTEALCNT "# Expired|Reused",
              SSOLDERRCNT "ORA-1555|Error",
              NOSPACEERRCNT "Out-Of-space|Error",
              MAXQUERYLEN "Max Query|Length"
    from gv$undostat
    where begin_time between
         to_date('03/30/2012 09:00:00','MM/DD/YYYY HH24:MI:SS')
    and
         to_date('03/30/2012 10:00:00','MM/DD/YYYY HH24:MI:SS')
    order by inst_id, begin_time;
  • 4. Re: ORA-01555 while gathering table statistics
    Aman.... Oracle ACE
    Currently Being Moderated
    Neo-b wrote:
    Increase the size of your rollback.
    Do you mean the undo_retention ?
    Besides that, the size of your Undo tablespace as well as you would need it to contain the Undo of your committed transactions.

    Aman....
  • 5. Re: ORA-01555 while gathering table statistics
    Neo-b Newbie
    Currently Being Moderated
    Can you gather which query ran when snapshot too old error occurred? You saw it in alert log or the statement given error?
    I saw it in alter log and the procedure that execute the below statement failed, and exception is raised:
    DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO');
    >
    set pagesize 25
    set linesize 120

    select inst_id,
    to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
    UNXPSTEALCNT "# Unexpired|Stolen",
    EXPSTEALCNT "# Expired|Reused",
    SSOLDERRCNT "ORA-1555|Error",
    NOSPACEERRCNT "Out-Of-space|Error",
    MAXQUERYLEN "Max Query|Length"
    from gv$undostat
    where begin_time between
    to_date('03/30/2012 09:00:00','MM/DD/YYYY HH24:MI:SS')
    and
    to_date('03/30/2012 10:00:00','MM/DD/YYYY HH24:MI:SS')
    order by inst_id, begin_time;
    Below is the result of the above query at the time I faced the issue:
            INST_ID     BEGIN_TIME     # Unexpired|Stolen     # Expired|Reused     ORA-1555|Error     Out-Of-space|Error     Max Query|Length
    1     1     03/01/2013 13:31     0     0     0     0     773
    2     1     03/01/2013 13:41     0     0     0     0     48
    3     1     03/01/2013 13:51     0     0     0     0     766
    4     1     03/01/2013 14:01     0     0     0     0     61
    5     1     03/01/2013 14:11     0     0     1     0     1644
    6     1     03/01/2013 14:21     0     0     0     0     110
    7     2     03/01/2013 13:22     0     0     0     0     313
    8     2     03/01/2013 13:32     0     0     0     0     912
    9     2     03/01/2013 13:42     0     0     0     0     307
    10     2     03/01/2013 13:52     0     0     0     0     907
    11     2     03/01/2013 14:02     0     0     0     0     299
    12     2     03/01/2013 14:12     0     0     1     0     900
    13     2     03/01/2013 14:22     0     0     0     0     292
  • 6. Re: ORA-01555 while gathering table statistics
    CKPT Guru
    Currently Being Moderated
            INST_ID     BEGIN_TIME     # Unexpired|Stolen     # Expired|Reused     ORA-1555|Error     Out-Of-space|Error     Max Query|Length
    5     1     03/01/2013 14:11     0     0     1     0     1644
    So error occurred in the above time, Now the question is how much is undo retention period ?
    and how much undo size you have configured.

    There are two queries,
    Below query for optimal undo size based on your undo retention
        select d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
        substr(e.value, 1, 25) "UNDO RETENTION (Secs)",
        (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /
        (1024 * 1024) "NEEDED UNDO SIZE (MEGS)"
        from (select sum(a.bytes) undo_size
        from v$datafile a, v$tablespace b, dba_tablespaces c
        where c.contents = 'UNDO'
        and c.status = 'ONLINE'
        and b.name = c.tablespace_name
        and a.ts# = b.ts#) d,
        v$parameter e,
        v$parameter f,
        (select max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
        from v$undostat) g
        where e.name = 'undo_retention'
        and f.name = 'db_block_size';
    Another query is based how much undo retention you have to configure as per the undo size
        select d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
        substr(e.value, 1, 25) "UNDO RETENTION (Secs)",
        round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION (Secs)"
        from (select sum(a.bytes) undo_size
        from v$datafile a, v$tablespace b, dba_tablespaces c
        where c.contents = 'UNDO'
        and c.status = 'ONLINE'
        and b.name = c.tablespace_name
        and a.ts# = b.ts#) d,
        v$parameter e,
        v$parameter f,
        (select max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
        from v$undostat) g
        where e.name = 'undo_retention'
        and f.name = 'db_block_size';
    BTW, have you tried again by executing the same ?

    And i strongly recommend to read Thomos kyte article on "Snapshot too old error"

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:1441804355350
    asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:1441804355350

    Cheers
  • 7. Re: ORA-01555 while gathering table statistics
    Neo-b Newbie
    Currently Being Moderated
    There are two queries,
    Below query for optimal undo size based on your undo retention
    select d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
    substr(e.value, 1, 25) "UNDO RETENTION (Secs)",
    (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /
    (1024 * 1024) "NEEDED UNDO SIZE (MEGS)"
    from (select sum(a.bytes) undo_size
    from v$datafile a, v$tablespace b, dba_tablespaces c
    where c.contents = 'UNDO'
    and c.status = 'ONLINE'
    and b.name = c.tablespace_name
    and a.ts# = b.ts#) d,
    v$parameter e,
    v$parameter f,
    (select max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
    from v$undostat) g
    where e.name = 'undo_retention'
    and f.name = 'db_block_size';
    Below is the result from my database;
            ACTUAL UNDO SIZE (MEGS)     UNDO RETENTION (Secs)     NEEDED UNDO SIZE (MEGS)
           20860                     900          955.6171875
    Another query is based how much undo retention you have to configure as per the undo size
    select d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE (MEGS)",
    substr(e.value, 1, 25) "UNDO RETENTION (Secs)",
    round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION (Secs)"
    from (select sum(a.bytes) undo_size
    from v$datafile a, v$tablespace b, dba_tablespaces c
    where c.contents = 'UNDO'
    and c.status = 'ONLINE'
    and b.name = c.tablespace_name
    and a.ts# = b.ts#) d,
    v$parameter e,
    v$parameter f,
    (select max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
    from v$undostat) g
    where e.name = 'undo_retention'
    and f.name = 'db_block_size';
    Below is the result from my database;
            ACTUAL UNDO SIZE (MEGS)     UNDO RETENTION (Secs)     OPTIMAL UNDO RETENTION (Secs)
         20860                           900                         19646
    BTW, have you tried again by executing the same ?
    Yes with no problem, it only happened once

    In the link you mention I read the below
    "The ORA-1555 happens when people try to save space typically. They'll have small
    rollback segments that could grow if they needed (and will shrink using OPTIMAL). So,
    they'll start with say 10 or so 1meg rollback segments. These rollback segments COULD
    grow to 100meg each if we let them (in this example) however, they will NEVER grow unless
    you get a big transaction.

    If your database does lots of little transactions, the RBS will never grow on their own.
    They will stay small.

    Now, someone needs to run a query that will take 5 minutes. On your system however the
    rollback wraps every 2 minutes due to lots of little transactions going on. In this
    system, ORA-1555's will happen frequently. What you need to do here is size rollback so
    that it wraps less frequently (less frequently then your long running queries). Here if
    I sized the rollback so that I had 10, 10meg segments (not so they could GROW to 10meg
    but that they are starting at 10meg) we would wrap maybe every 20minutes now. that'll
    give that 5minute query plenty of time to complete without reusing rollback it needs.
    "

    I Think this is exactly my case, how can I resize my redo segments ? and how can I check its current size?
  • 8. Re: ORA-01555 while gathering table statistics
    CKPT Guru
    Currently Being Moderated
    I Think this is exactly my case, how can I resize my redo segments ? and how can I check its current size?
    Here the issue with rollback segments, If you have issue with redo log sizing then the issue can be different with wait events as checkpoint not complete, logs are not archived.
    The ORA-01555 can be even with any bugs too and you may have to use undocumented parameter sometimes after contacting oracle support or many.
    Before that please follow MOS note *TROUBLESHOOTING GUIDE (TSG) - ORA-1555 [ID 467872.1]*

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points