8 Replies Latest reply: Mar 3, 2013 4:22 AM by CKPT RSS

    ORA-01555 while gathering table statistics

    Neo-b
      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
          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
            Increase the size of your rollback.
            Do you mean the undo_retention ?
            • 3. Re: ORA-01555 while gathering table statistics
              CKPT
              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....
                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
                  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
                            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
                      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
                        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]*