13 Replies Latest reply on Aug 5, 2009 2:30 PM by sb92075

    fetch cross commit doens't throw ora-1555 in 10.2.0.3

    649748
      in this link, Ktye put an example of fetch across commit which will throw ora-1555.
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:546822742166

      But I can't reproduce it in oracle 10.2.0.3. Anything changed for undo in 10g? I set inmemory_undo to false, but can't reproduce the ora-1555.
      SQL> drop table t;
      
      Table dropped.
      
      SQL> create table t as select * from all_objects;
      
      Table created.
      
      SQL> create index t_idx on t(object_id);
      
      Index created.
      
      SQL> 
      SQL> create rollback segment rbs_small storage (initial 1k next 1k maxextents 2) tablespace system;
      
      Rollback segment created.
      
      SQL> alter rollback segment rbs_small online;
      
      Rollback segment altered.
      
      SQL> 
      SQL> begin
        2          for x in ( select * from t where object_id > 0 )
        3          loop
        4                  dbms_transaction.use_rollback_segment( 'rbs_small' );
        5                  update t set object_name = lower(object_name)
        6                    where object_id = x.object_id;
        7                  commit;
        8          end loop;
        9  end;
       10  /
      
      PL/SQL procedure successfully completed.
      
      SQL> 
      SQL> select count(*) from t;
      
        COUNT(*)
      ----------
           16627
      
      SQL> 
        • 1. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
          damorgan
          The undo tablespace is likely retaining the data long enough that it remains available.

          You could try disabling this improvement by using a ridiculously small undo tablespace with a very short retention time but I wouldn't bother.
          Tom's premise has been proven over and over again. You should avoid fetching across commits.
          • 2. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
            26741
            The manually created rollback segment isn't relevant if you have automatic undo management.
            The parameter "undo_retention" is important for 9i.
            Furthermore, 10.2 also has "_autotune_undo" set to TRUE which means that it can automatically increase undo_retention to a higher value if there is enough space in the undo tablespace.
            • 3. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
              649748
              Event I created a undo tablespace of size 200K. and update 151668 rows in the procedure, still doesn't see ora-01555
              SQL> create table t as select * from all_objects;
               
              Table created
              SQL> create index t_idx on t(object_id);
               
              Index created
              SQL> select count(*) from test;
               
                COUNT(*)
              ----------
                  151668
              SQL> drop  tablespace UNDOTBS2;
               
              Tablespace dropped
              SQL> create undo tablespace UNDOTBS2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS2.DBF' size 200k reuse;
               
              Tablespace created
              SQL> alter system set undo_tablespace=UNDOTBS2;
               
              System altered
              SQL> begin
                2          for x in ( select * from t where object_id > 0 )
                3          loop
                4                  update t set object_name = lower(object_name)
                5                    where object_id = x.object_id;
                6                  commit;
                7          end loop;
                8  end;
                9  /
               
              PL/SQL procedure successfully completed
               
              SQL> 
              • 4. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                Timur Akhmadeev
                Try
                alter session set plsql_optimize_level=0;
                • 5. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                  649748
                  Doesn't work be this setting

                  alter session set plsql_optimize_level=0;

                  SQL> drop table t;
                   
                  Table dropped
                   
                  Executed in 0.719 seconds
                  SQL> create table t as select * from all_objects;
                   
                  Table created
                   
                  Executed in 1.047 seconds
                  SQL> create index t_idx on t(object_id);
                   
                  Index created
                   
                  Executed in 0.265 seconds
                  SQL> alter system set undo_tablespace=UNDOTBS2;
                   
                  System altered
                   
                  Executed in 0.078 seconds
                  SQL> alter session set plsql_optimize_level=0;
                   
                  Session altered
                   
                  Executed in 0 seconds
                  SQL> begin
                    2          for x in ( select * from t where object_id > 0 )
                    3          loop
                    4                  update t set object_name = lower(object_name)
                    5                    where object_id = x.object_id and rownum=1;
                    6                  commit;
                    7          end loop;
                    8  end;
                    9  /
                   
                  PL/SQL procedure successfully completed
                   
                  Executed in 15.297 seconds
                   
                  SQL> 
                  • 6. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                    Jonathan Lewis
                    This could be due to a change in execution plan on the driving query.

                    The test case makes the 1555 appear because you need to make the blocks that you have updated read-consistent and you have over-written the undo you generated while updating them.

                    If you select (which then means update) the rows by tablescan, you never reacquire a block you have updated because the driving tablescan keeps it pinned; but if you select the rows by index range scan, you keep reacquiring table blocks, and each time you re-acquire you have to check read-consistency.

                    I think you will find that your test is using tablescan on the select - modify your code to include an index hint on the select and I think you will see the 1555.


                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk

                    "For every expert there is an equal and opposite expert."
                    Arthur C. Clarke

                    P.S. That would make the difference between the 8.1.7 that Tom was using and the 10.2 that you were using a change in the optimizer's behaviour when querying an indexed table for which there are no statistics. Different versions got different execution plans.
                    1 person found this helpful
                    • 7. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                      649748
                      Thanks for Lewis's suggestion
                      But I still can't reproduce ora-01555 after added the index hint. I put the code here so that everyone could easily re-run it.
                      alter system set undo_tablespace=UNDOTBS1;
                      drop table t;
                      create table t as select * from all_objects;
                      create index t_idx on t(object_id);
                      alter system set undo_tablespace=UNDOTBS2;
                      alter system flush BUFFER_CACHE;
                      alter session set plsql_optimize_level=0;
                      begin
                              for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                              loop
                                      update t set object_name = lower(object_name)
                                        where object_id = x.object_id and rownum=1;
                                      commit;
                              end loop;
                      end;
                      /
                      • 8. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                        601585
                        How many rows do you have?
                        select count(*) from t;
                        You might not have enough rows to make SSTO happen?

                        Following is a simple test case just like you made. (unnecessary spool results omitted)
                        UKJA@ukja102> create table t as select * from all_objects where rownum <= 1000;
                        
                        Table created.
                        
                        ...
                        
                        UKJA@ukja102> begin
                          2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                          3          loop
                          4                  update t set object_name = lower(object_name)
                          5                    where object_id = x.object_id and rownum=1;
                          6                  commit;
                          7          end loop;
                          8  end;
                          9  /
                        
                        PL/SQL procedure successfully completed.
                        
                        UKJA@ukja102> create table t as select * from all_objects where rownum <= 10000;
                        
                        Table created.
                        
                        ...
                        
                        UKJA@ukja102> begin
                          2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                          3          loop
                          4                  update t set object_name = lower(object_name)
                          5                    where object_id = x.object_id and rownum=1;
                          6                  commit;
                          7          end loop;
                          8  end;
                          9  /
                        begin
                        *
                        ERROR at line 1:
                        ORA-01555: snapshot too old: rollback segment number  with name "" too small
                        ORA-06512: at line 2
                        
                        
                        Elapsed: 00:00:23.34
                        ==================================
                        Dion Cho - Oracle Performance Storyteller

                        http://dioncho.wordpress.com (english)
                        http://ukja.tistory.com (korean)
                        http://dioncho.blogspot.com (japanese)
                        ==================================
                        • 9. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                          649748
                          One step of my original try is (part of script from tom):
                          create rollback segment rbs_small storage (initial 1k next 1k maxextents 2) tablespace system;
                          Then I was told that was for oracle 8i. Then I switched to set a small undo tablespace, but the procedure supposed to report ora-01555 actually reported:
                          ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
                          ORA-06512: at line 5
                          then I offline the system segment by:
                          alter rollback segment system offline;
                          And rerun the procedure, now it completes without complain, no ora-01555.

                          Full code is:
                          SQL> alter system set undo_tablespace=UNDOTBS1;
                           
                          System altered
                          SQL> drop table t;
                           
                          Table dropped
                          SQL> create table t as select * from all_objects;
                           
                          Table created
                          SQL> create index t_idx on t(object_id);
                           
                          Index created
                          SQL> alter system set undo_tablespace=UNDOTBS2;
                           
                          System altered
                          SQL> alter system flush BUFFER_CACHE;
                           
                          System altered
                          SQL> alter session set plsql_optimize_level=0;
                           
                          Session altered
                          SQL> begin
                            2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                            3          loop
                            4                  update t set object_name = lower(object_name)
                            5                    where object_id = x.object_id and rownum=1;
                            6                  commit;
                            7          end loop;
                            8  end;
                            9  /
                           
                          begin
                                  for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                                  loop
                                          update t set object_name = lower(object_name)
                                            where object_id = x.object_id and rownum=1;
                                          commit;
                                  end loop;
                          end;
                           
                          ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
                          ORA-06512: at line 5
                           
                          SQL> alter rollback segment system offline;
                           
                          Rollback segment altered
                           
                          SQL> 
                          SQL> begin
                            2          for x in ( select /*+ index(t) */ * from t where object_id > 0 )
                            3          loop
                            4                  update t set object_name = lower(object_name)
                            5                    where object_id = x.object_id and rownum=1;
                            6                  commit;
                            7          end loop;
                            8  end;
                            9  /
                           
                          PL/SQL procedure successfully completed
                           
                          SQL> select count(*) from t;
                           
                            COUNT(*)
                          ----------
                               50249
                           
                          SQL> 
                          • 10. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                            mbobak
                            Please try again, this time with a syntactically correct hint specification, i.e.:
                            select /*+ index(t t_idx) */ * from t where object_id > 0
                            See if that makes a difference.

                            -Mark
                            • 11. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                              601585
                              What if you increase the undo tablespace size from 200k to a bit bigger one(300k? 400k?)

                              ==================================
                              Dion Cho - Oracle Performance Storyteller

                              http://dioncho.wordpress.com (english)
                              http://ukja.tistory.com (korean)
                              http://dioncho.blogspot.com (japanese)
                              ==================================
                              • 12. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                                649748
                                Thanks everyone for the suggestion

                                Adding the hint as /*+ index(t t_idx) */ or increase the undo tablespace to 400k doesn't help.

                                I found it used lots of in memory undo (65216004) in the first run, so before the second run, I tuned off the in memory undo, but still can't reproduce ora-01555


                                First run
                                SQL> create undo tablespace UNDOTBS2 datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS2.DBF' size 400k reuse;
                                 
                                Tablespace created
                                SQL> alter system set undo_tablespace=UNDOTBS2;
                                 
                                System altered
                                SQL> alter system flush BUFFER_CACHE;
                                 
                                System altered
                                SQL> alter session set plsql_optimize_level=0;
                                 
                                Session altered
                                SQL> select b.NAME,a.VALUE from v$mystat a , v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like '%undo%size%';
                                 
                                NAME                                                                  VALUE
                                ---------------------------------------------------------------- ----------
                                undo change vector size                                               47680
                                IMU undo allocation size                                                104
                                SQL> begin
                                  2          for x in ( select /*+ index(t t_idx) */ * from t where object_id > 0 )
                                  3          loop
                                  4                  update t set object_name = lower(object_name)
                                  5                    where object_id = x.object_id and rownum=1;
                                  6                  commit;
                                  7          end loop;
                                  8  end;
                                  9  /
                                 
                                PL/SQL procedure successfully completed
                                SQL> select b.NAME,a.VALUE from v$mystat a , v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like '%undo%size%';
                                 
                                NAME                                                                  VALUE
                                ---------------------------------------------------------------- ----------
                                undo change vector size                                            17403772
                                IMU undo allocation size                                           65216004
                                SQL> select count(*) from t;
                                 
                                  COUNT(*)
                                ----------
                                     50249
                                 
                                SQL> alter system set "_in_memory_undo"=false;
                                 
                                System altered
                                After tune off the in memory undo, the second run
                                SQL> alter system set undo_tablespace=UNDOTBS2;
                                 
                                System altered
                                SQL> alter system flush BUFFER_CACHE;
                                 
                                System altered
                                SQL> alter session set plsql_optimize_level=0;
                                 
                                Session altered
                                SQL> select b.NAME,a.VALUE from v$mystat a , v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like '%undo%size%';
                                 
                                NAME                                                                  VALUE
                                ---------------------------------------------------------------- ----------
                                undo change vector size                                            17445460
                                IMU undo allocation size                                           65216004
                                SQL> begin
                                  2          for x in ( select /*+ index(t t_idx) */ * from t where object_id > 0 )
                                  3          loop
                                  4                  update t set object_name = lower(object_name)
                                  5                    where object_id = x.object_id and rownum=1;
                                  6                  commit;
                                  7          end loop;
                                  8  end;
                                  9  /
                                 
                                PL/SQL procedure successfully completed
                                SQL> select b.NAME,a.VALUE from v$mystat a , v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like '%undo%size%';
                                 
                                NAME                                                                  VALUE
                                ---------------------------------------------------------------- ----------
                                undo change vector size                                            34730428
                                IMU undo allocation size                                           65216004
                                SQL> select count(*) from t;
                                 
                                  COUNT(*)
                                ----------
                                     50249
                                 
                                • 13. Re: fetch cross commit doens't throw ora-1555 in 10.2.0.3
                                  sb92075
                                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923
                                  Tom shows how to produce ORA-01555