Oracle provides statistics which relate to the capability of rollbacks to shrink back to an optimal size. To make use of these statistics it is suggested that the following two views be created to reduce the number of items contained in the v$rollstat view:
View ROLLBACK1:View ROLLBACK2:
CREATE OR REPLACE VIEW ROLLBACK1 AS CREATE OR REPLACE VIEW ROLLBACK2 AS
SELECT SELECT
NAME, EXTENTS, OPTSIZE, NAME, EXTENTS, XACTS,
SHRINKS, AVESHRINK, AVEACTIVE HWMSIZE, RSSIZE, WAITS,
FROM WRAPS, EXTENDS
V$ROLLNAME N, FROM
V$ROLLSTAT S V$ROLLNAME N,
WHERE V$ROLLSTAT S
N.USN=S.USN; WHERE
N.USN=S.USN;
Once the views exist the following reports provide detailed information concerning the rollbacks and their various storage, wrap and shrink statistics.
- If there are excessive WRAPS, the database rollback segment is probably too small, or, you need more rollback segments. Excessive WRAPS will probably be accompanied by "Snapshot Too Old" errors.
- If you get excessive extends, the rollback segment is too small.
- Excessive WAITS show you need more rollback segments.
If the report shows a number of shrinks (meaning the rollback segment extended beyond the setting of OPTIMAL and then shrank back to OPTIMAL) then the rollback segment extent size or OPTIMAL parameter may be set too small. Use the parameter AVERAGE SHRINK to determine the amount of space to add to the initial and next extent values.
The new optimal size should be:
(OPTIMAL SIZE + AVERAGE SHRINK)
When a rollback segment is taken offline, its status is changed to PENDING OFFLINE and it is taken offline as soon as its pending transactions complete. To determine if a rollback segment has outstanding transactions, the following select statement is used.
SELECT name, xacts 'ACTIVE TRANSACTIONS' FROM v$rollname, v$rollstat WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;
Snapshot Too Old
ORA-01555 error "Snapshot too old" results when a rollback segment is either too small or its extents are too small for a given transaction set. This results in rollback data being reused. For example, we have transaction X which uses a load of data from table A resulting in a large amount of redo generation; we also have transaction Y which is also using table A and the redo data from transaction X. Once transaction X completes its redo, entries are marked as inactive and placed on the reuse list. If another transaction starts up and uses this area of redo, transaction Y will get an ORA-01555. Below are a couple of examples (a bulk delete and an update) which illustrate how to prevent this from happening such as larger rollback segments and larger extents as well as frequent commits and cursor release in procedures. The table used for these examples was generated from the object_name column from DBA_OBJECTS and the SYSDATE system value. The update code requires a work table called TEMP_TAB which has columns numrow as a NUMBER, and idrow as a ROWID data types.
The easiest to see is a bulk delete type operation. Essentially you "chunk" through the table or tables commiting, releasing cursors then resuming where we left off. Here's a simple delete procedure using this logic.
CREATE OR REPLACE PROCEDURE delete_it (
rows IN NUMBER,
tab_name IN VARCHAR2,
delete_date IN DATE)
AS
cur INTEGER;
ret INTEGER;
row_count NUMBER;
com_string VARCHAR2(100);
trunc_date DATE;
BEGIN
com_string :=
'SELECT count(1) row_count FROM '||tab_name;
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
DBMS_SQL.DEFINE_COLUMN(cur, 1, row_count);
ret := DBMS_SQL.EXECUTE(cur);
ret := DBMS_SQL.FETCH_ROWS(cur);
DBMS_SQL.COLUMN_VALUE(cur, 1, row_count);
DBMS_SQL.CLOSE_CURSOR(cur);
DBMS_OUTPUT.PUT_LINE('Count='||TO_CHAR(row_count));
row_count:=(TRUNC(row_count/rows)+1)*rows;
DBMS_OUTPUT.PUT_LINE('New Count='||TO_CHAR(row_count));
trunc_date:=TRUNC(delete_date);
com_string:=
'DELETE FROM '||tab_name||'
WHERE entry_ts<'||chr(39)||trunc_date||chr(39)||'
and rownum<='||rows;
DBMS_OUTPUT.PUT_LINE(com_string);
FOR i IN 0..row_count LOOP
IF MOD(i,rows)=0 THEN
DBMS_OUTPUT.PUT_LINE('i='||TO_CHAR(i));
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,com_string,dbms_sql.v7);
ret := DBMS_SQL.EXECUTE(cur);
DBMS_OUTPUT.PUT_LINE('Rows Deleted:'||TO_CHAR(ret));
COMMIT;
DBMS_SQL.CLOSE_CURSOR(cur);
END IF;
END LOOP;
END;
/
Some things to note about the script above:
- In order to use SELECT in DBMS_SQL you must use the standard format, not the embedded format. To pull the information retrieved by the SELECT into the procedure you use the DBMS_SQL.DEFINE_COLUMN and DBMS_SQL.COLUMN_VALUE procedures.
- The line "row_count:=(TRUNC(row_count/rows)+1)*rows;" ensures you loop through the processing an even multiple of the value of the "rows" input value; otherwise you might miss the last few records that are smaller than "rows" in size.
- The code loops through the specified table deleting "rows" number of rows from the table each loop. At the conclustion of each set of "rows" rows of data, the transaction is committed and the cursors released thus forcing a switch of redo logs and hopefully avoiding the ORA-01555 error.
The next procedure script shows how to do a conditional update in batch mode, and by example, virtually any other complex piece of redo generating transaction.
CREATE OR REPLACE PROCEDURE update_it (
rows IN NUMBER,
tab_name IN VARCHAR2
)
AS
cursor_name INTEGER;
ret INTEGER;
rowcount NUMBER := 1;
maxrows NUMBER;
temp_id ROWID;
i INTEGER := 1;
CURSOR proc_row (row NUMBER, maxr NUMBER)
IS
SELECT idrow
FROM temp_tab
WHERE numrow BETWEEN row AND maxr;
sql_com VARCHAR2(100);
new_date DATE;
maxcount NUMBER;
BEGIN
sql_com := 'TRUNCATE TABLE temp_tab';
cursor_name := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_name, sql_com, DBMS_SQL.v7);
ret := DBMS_SQL.execute (cursor_name);
DBMS_SQL.close_cursor (cursor_name);
sql_com := 'INSERT INTO temp_tab SELECT rownum, rowid FROM ' || tab_name;
cursor_name := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_name, sql_com, DBMS_SQL.v7);
ret := DBMS_SQL.execute (cursor_name);
DBMS_SQL.close_cursor (cursor_name);
SELECT MAX (numrow)
INTO maxcount
FROM temp_tab;
maxrows := rowcount + rows;
new_date := SYSDATE + 2;
LOOP
DBMS_OUTPUT.put_line ('Rowcount:' ||
TO_CHAR (rowcount) ||
' Maxrows:' ||
TO_CHAR (maxrows));
OPEN proc_row (rowcount, maxrows);
FETCH proc_row INTO temp_id;
LOOP
EXIT WHEN proc_row%notfound;
sql_com := 'UPDATE ' || tab_name || '
SET entry_ts=' || CHR (39) || new_date || CHR (39) || '
WHERE rowid=' || CHR (39) || temp_id || CHR (39);
cursor_name := DBMS_SQL.open_cursor;
DBMS_SQL.parse (cursor_name, sql_com, DBMS_SQL.v7);
ret := DBMS_SQL.execute (cursor_name);
DBMS_SQL.close_cursor (cursor_name);
rowcount := rowcount + 1;
FETCH proc_row INTO temp_id;
DBMS_OUTPUT.put_line (TO_CHAR (rowcount));
END LOOP;
CLOSE proc_row;
COMMIT;
maxrows := rowcount + rows;
IF rowcount = maxcount + 1 THEN
EXIT;
END IF;
END LOOP;
END;
/
Some things to note about the above example:
- This script depends on a work table that can be reused by any number of different transactions of this type. The table acts as a repository for the target tables ROWID values (ROWID is a unique identifier for each row in a table and is an Oracle internal column for each table). Also placed in the table is the pseudo column ROWNUM which is generated as requested by a select statement. The set of ROWNUM varies according to the select and is not fixed. However, ROWNUMs for a given select are in sequence which is what we are looking for.
- The script creates a complete set of ROWNUM, ROWID entries for the target table. In the example script, all of the ROWIDs for the table are gathered, however, by simply adding whatever WHERE clause is required to the line:
sql_com:='INSERT INTO temp_tab SELECT rownum, rowid FROM '||tab_name;
- The WHERE clause could even be passed into the procedure as a VARCHAR2 variable.
- Once we have this "indexed" set of ROWIDs (the ROWNUM pseudo column provides a 1 to n correlation to the selected ROWIDs) we can now keep track of the "record" where we left off processing to do our commit and cursor release so we can resume processing at this point.
- The procedure accepts a rows and table name argument as well as the conditional date argument; loads the temp_tab with ROWNUM and ROWID data, and using a standard cursor loop construct uses the rows value to "chunk" through the table performing sets of updates followed by commits and cursor releases — again, hopefully avoiding ORA-01555 errors in the process.
Start the discussion at forums.toadworld.com