Encountering exception ORA-01555

Question

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain Table size(actually consumed) and index size and no of rows.

So, I wrote a sizing script(PL/SQL) to find the size of all the Table Space on that particular DB server.

But I am getting this particular exception after it runs for days.

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

I am not sure what might be causing this, as the data size is not that huge.

I am attaching the Script

    SET SERVEROUTPUT ON size '10000000'
declare
TYPE cur_typ IS REF CURSOR;
a_Temp number := 0;
x_Total number := 0;
i number := 0;
c_cursor cur_typ;
query_str varchar2(500);
num_long Long;
currentScenarioDB nvarchar2(255);
tableExists number := 0;
scenarioId varchar2(50);
scenarioName varchar2(100);
dbIdentifier nvarchar2(50);
queryToFindScenarioNameAndId varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
selectQuery varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
insertStatement varchar2(2000) := 'Insert Into ScenarioTableAndIndexSize  values (:1,:2,:3,:4,:5,:6,:7) ';
-- scenarioId,scenarioname,,dbIdentifier,tablename,dataSize,IndexSize,rowNumber
tableIndexSize number := 0;
numOfRows number := 0;
rowNum number := 0;
tableDataSize number := 0;
Cursor getScenarioDb is select dbidentifier from scenarios where dbidentifier IN (select Distinct(TABLESPACE_NAME) from dba_tables);
begin
DBMS_OUTPUT.ENABLE(10000000);
execute immediate 'truncate table ScenarioTableAndIndexSize';
open getScenarioDb;
fetch getScenarioDb into currentScenarioDB;
while getScenarioDb%found
loop
queryToFindScenarioNameAndId := selectQuery || '''' || currentScenarioDB || '''';
execute immediate queryToFindScenarioNameAndId  into scenarioId,scenarioName;
              declare
              queryToFindNoofRows varchar2(1000);
        queryConstruct varchar2(32767) := '';
        outputTableInScenarioDb nvarchar2(256);
        Cursor getTablesInScenario is select DISTINCT TABLE_NAME from dba_tables where owner =  currentScenarioDB and TABLE_NAME not like 'BIN%' and table_name != 'SCENARIOTABLEANDINDEXSIZE' order by table_name;
        begin
              tableExists := 0;
        open getTablesInScenario;
        fetch getTablesInScenario into outputTableInScenarioDb;
        while getTablesInScenario%found
        loop
              queryConstruct  := 'select nvl( sum (';
              tableIndexSize  := 0;
              tableDataSize := 0;
              numOfRows := 0;
              queryToFindNoofRows := 'select count(*) from  '||  currentScenarioDB || '.' ||outputTableInScenarioDb;
              execute immediate queryToFindNoofRows into numOfRows;
              if numOfRows > 0 then
---------------------------Beginning Of Section to find Table data Size------------------------------------------------------------------------------------------------
                  declare
                      Cursor getColumnsInTables is select * from dba_tab_columns where Table_Name = outputTableInScenarioDb and owner = currentScenarioDB;
                      dbaTabColumnRow dba_tab_columns%rowtype;
                      dataType varchar2(40);
                      fields varchar2(1000);
                      begin
                      open getColumnsInTables;
                      fetch getColumnsInTables Into dbaTabColumnRow;
                      while getColumnsInTables%found
                      loop
                      dataType := dbaTabColumnRow.DATA_TYPE;
                     if dataType = 'CLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH(' || dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'BLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH('|| dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'LONG' then
                        fields := 'nvl(VSIZE(''''),0)';
                        x_Total := 0;
                        query_str := 'SELECT  ' || dbaTabColumnRow.COLUMN_NAME || '  FROM  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;
                                      OPEN c_cursor FOR query_str;
                                  LOOP
                                  FETCH c_cursor INTO num_long;
                                  EXIT WHEN c_cursor%NOTFOUND;
                             a_Temp:=length(num_long);
                             x_Total:= x_Total + a_Temp;
                                  END LOOP;
                           CLOSE c_cursor;
                     else
                        fields := 'nvl(vsize(' || dbaTabColumnRow.COLUMN_NAME || '),0)';
                     end if;
                           fetch getColumnsInTables Into dbaTabColumnRow;
                         if getColumnsInTables%found then
                       queryConstruct := queryConstruct || fields||'+';
                     else
                     queryConstruct := queryConstruct || fields;
                     end if;
                      end loop;
                      end;
                                      queryConstruct := queryConstruct || '),0) as sizeOfTable from  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;            
                                      --dbms_output.put_line(queryConstruct);
                                      execute immediate queryConstruct into tableDataSize;
---------------------------End Of Section to find Table data Size-------------------------------------------------------------------------------------------------------------
                  ---------------Section To find index size
                      declare
            Index_Name nvarchar2(4000);
            sql_statement varchar2(1000) := 'select nvl(USED_SPACE,0) from index_stats';
            stat1 varchar2(1000) := 'analyze index ';
            stat2 varchar2(1000) := '  validate structure';
            stat3 varchar2(2000) := '';
            size1 number := 0;
            cursor indexOnTable is select INDEX_NAME from dba_indexes where tablespace_name = currentScenarioDB and  table_name = outputTableInScenarioDb and index_type = 'NORMAL';
                begin
                open indexOnTable;
                fetch indexOnTable into Index_Name;
                while indexOnTable%found
                loop
                  stat3 := stat1 || currentScenarioDB ||'.' ||Index_Name || stat2;
                  execute immediate stat3;
                  execute immediate  sql_statement into size1;
                  tableIndexSize := tableIndexSize + size1;
                fetch indexOnTable into Index_Name;
                end loop;
                close indexOnTable;
        end;
                  -----end of section to find index size
          else
            rowNum := rowNum + 1;
          end if;
                        tableDataSize := x_Total + tableDataSize;
          execute immediate insertStatement using   scenarioId,scenarioName,currentScenarioDB,outputTableInScenarioDb,tableDataSize,tableIndexSize,numOfRows;
                           x_Total := 0;
          fetch getTablesInScenario into outputTableInScenarioDb;
    end loop;
    end;
    fetch getScenarioDb into currentScenarioDB;
  end loop;
  close getScenarioDb;
end;

The size of the table is found out this way :

1. List item If the field is of type Lob then to calculate its size I use nvl(DBMS_LOB.GETLENGTH(),0)
2. If the field is of type Long then I loop over all the Long values and find their size using the built in Length() function
3. If the field is of any other Type I use nvl(vsize(),0)

Just to specify the user has permissions on all the DBs

And then I sum all of them up to find the total Data size in the table.

Can some one tell me what I am doing wrong or What I should do to fix the error ?

Thanks.

Answer 1

"Can some one tell me what I am doing wrong"

Where to start?

ORA-01555

This occurs in long-running queries. Oracle's policy of read consistency guarantees that the last record in the result set is consistency with the first record of the result set. In other words, our query will not return changes made in some other session which were committed after we issued our query. Oracle does this by substituting records from the UNDO tablespace for any changed records. When it cannot do this it hurls the SNAPSHOT TOO OLD exception. This means Oracle no longer has the old versions of the records it needs to provide a read-consistent view.

A common reason why Oracle no longer has the data is because our long-running query is a PL/SQL cursor loop, which issues COMMIT statements. As you should know, COMMIT signifies the end of a transaction, and that releases any locks Oracle has been keeping for our session. This obviously includes our sessions interest in the UNDO tablespace; Oracle is then free to overwrite the UNDO blocks which contain the data need for read consistency.

In your case the COMMIT statements are the implicit ones which bracket any DDL statement - including ANALYZE. This might not matter but it seems somebody is updating the SCENARIOS table while your program runs, a likely ocurance for something which takes several days.

Use of ANALYZE

This is bad for several reasons. The first is that it has been deprecated for quite a while: you're on 10g, you should be using DBMS_STATS to gather statistics. But wait, there's more.

Gathering statistics is not something which should be done too often. In most systems the statistics acheive a plateau of stability, in which they are accurate enough even when they are several months old. So, frequent gathering of statistics is at best a waste of cycle. It can be much worse: there is the risk of fresh statistics spawning a less efficient plan than the ciurrent one. So actually statistics gathering should be done in a controlled fashion. One of the advantages of DBMS_STATS is that we can configure it to monitor the rate of changes applied to our tables, and only re-gather statistics when they reach a certain staleness. Find out more.

Of course you're only using ANALYZE to get the up-to-date space usage for the indexes, which bings me to my third point:

Insanity

You are select every row of all the tables you're interest in and totalling the actual size of all their columns, with - if I have understaood correctly - a separate query for each column. This is insane.

Oracle provides views which show teh amount of space used by a given table. USER_SEGMENTS should be sufficient, although USER_EXTENTS is also available. The SEGMENT_NAME is the index or table name. Summing the BYTES column will give you an exact size of the footprint of each table.

Of course, some of those allocated extents will be empty, so you may think those figures will be a slight overestimate. But:

1. The alloocated extents is actually a more accurate picture of the space usage, because it allows for the space which is held by the table.
2. Any perceived loss of "accuracy" will be repaid in queries which will run in seconds rather than days.
3. Following on from that, the queries will return the position now rather than a shifting picture of space usage over the course of three days, so the figures are much more useful.

---

"but the whole motivation behind writing this whole PL/SQL script was to get the ACTUAL not ALLOCATED Space"

Okay, let's tackle that. The main problem with your script is that it tackles things RBAR; in fact worse than that, RBARBAC. So you issue a matrix of queries, one for each column of each row of the table. SQL is a set-based language, and it wroks a lot better if we treat it as such.

This procedure assembles a dynamic query which assembles a single SELECT to get the total size and number of records for a given table.

create or replace procedure get_table_size 
    ( p_tabn in user_tables.table_name%type
      , p_num_rows out pls_integer
      , p_tot_size out pls_integer )
is
    stmt varchar2(32767) := 'select count(*), sum(';
    n_rows pls_integer;
    n_size pls_integer;
begin
    for r in ( select column_name, data_type, column_id
               from user_tab_columns
               where table_name = p_tabn
               order by column_id)
    loop
        if r.column_id != 1
        then
            stmt := stmt ||'+';
        end if;
        stmt := stmt || 'nvl(';
        if r.data_type in ('CLOB', 'BLOB', 'BFILE') 
        then
            stmt := stmt || ' dbms_lob.getlength('||r.column_name||')';
        else
            stmt := stmt || ' vsize('||r.column_name||')';
        end if;
        stmt := stmt || 'nvl)';
    end loop;
    stmt := stmt || ') from '||p_tabn;
    execute immediate stmt into n_rows, n_size;
    p_num_rows := n_rows;
    p_tot_size := n_size;
end;
/

It doesn't include the block header overhead (3 bytes per row) but that's a matter of simple arithmetic.

Here it is in action:

SQL> desc t34
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQ_NUM                                            NUMBER
 UNIQUE_ID                                          NUMBER
 NAME                                               VARCHAR2(20 CHAR)
 LONG_COL                                           CLOB

SQL>
SQL> set timing on
SQL> var n1 number
SQL> var n2 number
SQL> exec get_table_size('T34', p_num_rows=>:n1, p_tot_size=>:n2)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL> print n1

        N1
----------
        11

SQL> print n2

        N2
----------
    135416

SQL>

Small table, perhaps unrealistically fast. Here's a bigger one, no clobs.

SQL> exec get_table_size('BIG_TABLE', p_num_rows=>:n1, p_tot_size=>:n2)

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.65
SQL> print n1

        N1
----------
   4680640

SQL> print n2

        N2
----------
 189919606

SQL>

The elapsed time is still good, mmm?

Regarding space for indexes, a similar query would work, only driving from USER_IND_COLUMNS to get the appropriate column names. I think that is preferable to re-analyzing the indexes. It won't work for sizing any TEXT indexes you may have on CLOB or BLOB columns. For those you need to use CTX_REPORT.INDEX_SIZE(), although that produces a report which you would need to parse in oder to get the useful figures (the XML format might be helpful in that regard).

Answer 2

You're getting the ORA-01555 error because you are fetching across commits. You open a cursor, and while you are reading from the cursor, you commit transactions.

When you open a cursor, Oracle guarantees at that point what data you will get. If you or other users then change the data this cursor would later iterate over, Oracle will go back in the undo to get the original data you would see if the data hadn't been changed. Ultimately, the ORA-01555 error indicates that Oracle ran out of undo and couldn't go any further back. In this case, Oracle is throwing this error because it has to go back through too many committed transactions.

Your code doesn't explicitly COMMIT anywhere, but it seems the ANALYZE statement, like all Oracle DDL, does an implicit commit before and after execution. (The TRUNCATE statement at the top also does an implicit commit before and after, but that's not a problem since you only call it once.)

What I would do instead would be to:

• Fetch the names and owner/tablespace name of all indexes to analyze and bulk-collect them into a PL/SQL nested table. (It seems your tablespace names are the same as your schema owner names - is this correct?)
• Loop through this nested table and call ANALYZE INDEX index_name VALIDATE STRUCTURE for each one.
• Then run the rest of your code, without the ANALYZE INDEX ... statements, as everything else is just querying data.

Answer 3

Your procedure is not optimal. You declare a lot of cursors inside loops. The are parsed for every execution of the loop. Smarter is to define the cursors all at the toplevel. You might also gain a lot of performance by using bulk collections and bulk inserts. You wil get top performance if you can upgrade your code from PL/sql to sql. This might not always be possible but try to use the minimum of Procedural code.

You could try to increase the undo retention of your database but it already is amazingly high, considering the fact that your snapshot too old popped up after 9 days.

What version of database are we talking about?

Answer 4

Oracle uses multi-version concurrency control, i.e. it keeps old versions of records for consistent results in long running queries and as long as the new version isn't commited.

If a long running query would require an old version but this version has been discarded in the mean time, you get the "snapshot too old" error.

The best way to avoid this error is to make your query faster. Another one is to increase the size of the UNDO log.

But since your program is obviously running for several days, you really need to make your program faster, i.e. much faster.