Home » RDBMS Server » Performance Tuning » Snap Shot Error on User_Tables
Snap Shot Error on User_Tables [message #280709] Wed, 14 November 2007 10:23 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
I have a Procedure for collecting STATS, different Jobs for Partitions & Non Partition tables.
and which is schedule to run using DBMS_JOB, it fails and retries,
Adn when i see the Alert Log file and trace file this is the output
Oracle Version 9.2.0.7

From Log File
ORA-01555 caused by SQL statement below (Query Duration=3000 sec, SCN: 0x0001.06d8a86f):
Tue Nov 13 22:50:08 2007
SELECT TABLE_NAME FROM USER_TABLES WHERE NUM_ROWS >= 1000000 AND PARTITIONED ='NO'
Tue Nov 13 22:50:08 2007
Errors in file /u01/app/oracle/admin/neor1p1/bdump/App1_Prod_j001_12772.trc:
ORA-12012: error on auto execute of job 2174
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
ORA-06512: at "APP_PROD.PKG_GET_NONPART_BIG_TABLE", line 11
ORA-06512: at line 1
Tue Nov 13 22:50:18 2007


From Trace File

App1_Prod_j001_12772.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:    SunOS
Node name:      neo6f037n2
Release:        5.9
Version:        Generic_118558-28
Machine:        sun4us
Instance name: neor1p1
Redo thread mounted by this instance: 1
Oracle process number: 48
Unix process pid: 12772, image: oracle@neo6f037n2 (J001)
 
*** SESSION ID:(84.4737) 2007-11-13 22:50:08.273
*** 2007-11-13 22:50:08.273
ORA-12012: error on auto execute of job 2174
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small
ORA-06512: at "R1APP.PKG_GET_NONPART_BIG_TABLE", line 11
ORA-06512: at line 1


This is the Package
CREATE OR REPLACE PACKAGE BODY PKG_GET_NONPART_BIG_TABLE AS
procedure GET_ALL_NONPART_TABLE_STATS as
  cursor c1 is

  select  table_name   from user_tables where  num_rows >= 1000000 and partitioned ='NO';
  u varchar2(30);
begin
SELECT username INTO u  fROM user_users;
  for i in c1
  loop
    insert into tbl_stats_info values (tbl_stats_info_seq.nextval,i.table_name,SYSDATE ,null);
    dbms_stats.gather_table_stats(u,i.table_name,cascade=>true);
    update tbl_stats_info set end_dt = sysdate   where tbl_nm = i.table_name and end_dt is null;
    commit;
  end loop;
end;
END;


Any Idea why it gives SNAPSHOT OLD Error for this statement
SELECT TABLE_NAME FROM USER_TABLES WHERE NUM_ROWS >= 1000000 AND PARTITIONED ='NO'



Thanks

[Updated on: Wed, 14 November 2007 21:20]

Report message to a moderator

Re: Snap Shot Error on User_Tables [message #280724 is a reply to message #280709] Wed, 14 November 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Committing inside a cursor loop is the most sure way to get ORA-1555 error.

By the way, "SELECT username INTO u fROM user_users;" is useless USER internal function/keyword gives you the current user.

Regards
Michel
Re: Snap Shot Error on User_Tables [message #280732 is a reply to message #280724] Wed, 14 November 2007 10:55 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Michel for Quick Reponse,

That commit is on a small table inside a loop, I am just storing the time each table takes to gather stats.
This Procedure and Job was in Place for almost a Year, but now all of a sudden since 3-4 days it started giving these Errors,
The DBMS_JOBS keeps trying ,
Do you think other DBMS_JOB accessing on transcational tables purging/adding might change the number of rows and at same time this statement
SELECT TABLE_NAME FROM USER_TABLES WHERE NUM_ROWS >= 1000000 AND PARTITIONED ='NO'
might be causing this, Since num_rows are getting changed while Stats are being collected.

Thanks

[Updated on: Wed, 14 November 2007 10:57]

Report message to a moderator

Re: Snap Shot Error on User_Tables [message #280741 is a reply to message #280709] Wed, 14 November 2007 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
faiz_hyd,
visit http://asktom.oracle.com
& do a keyword search on ORS-01555

I agree with Michel.
The COMMIT inside the loop is the root cause for the ORA-01555 error.
Re: Snap Shot Error on User_Tables [message #280817 is a reply to message #280741] Wed, 14 November 2007 20:55 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I disagree. Removing the COMMIT won't help in this case (IMO).

The main query is on the tables underlying the DD view USER_TABLES. To get ORA-01555, you would need other sessions - or the current session - changing those DD tables and committing.

The purpose of the COMMIT in this script is to commit changes to tbl_stats_info, not the DD tables. Changed to the DD tables have already been committed by the DBMS_STATS call.

If you remove the explicit COMMIT, the implicit commit you get when gathering stats will still be there.

Select all of the USER_TABLES that you want to work on into a PL/SQL nested table or a Global Temporary Table. Then loop on that structure committing as frequently as you please.

Ross Leishman
Re: Snap Shot Error on User_Tables [message #280834 is a reply to message #280817] Wed, 14 November 2007 23:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A simple trick to prevent the error is to add an "order by" clause on the cursor.
This way query result is instantiated at execute time in temporary area (in either memory or temp segment) and no more requires undo data.

Regards
Michel
Re: Snap Shot Error on User_Tables [message #283616 is a reply to message #280834] Tue, 27 November 2007 13:25 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I am using this method on the PArtition table to gather STATS on current month partition
dbms_stats.gather_table_stats('APP_SCHEMA,'TABLE_NAME','P_'||(to_Char(sysdate,'YYYYMM')),5,cascade=>true);


first of all it takes more than 150 minutes to complete 1 month partition and some times if gives
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12$" too small
ORA-06512: at "SYS.DBMS_STATS", line 9643
ORA-06512: at "SYS.DBMS_STATS", line 9657
ORA-06512: at "R1APP.PKG_GET_STATS_PARTITION_TABLE", line 43

Is there a better way of gathering STATs on partition table for current month where for sure data gets added constantly only in current month partition.
Approx 1 month of partition will have around 4.5 million rows.
Even if it is 2 day of the month the time taken is same around 2+ hrs where the number of rows would be less initially on first few days of month.

Thanks
Re: Snap Shot Error on User_Tables [message #283623 is a reply to message #283616] Tue, 27 November 2007 13:43 Go to previous message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ gather statistics during low workload hours
2/ enlarge your undo tablespace

By the way, don't use parallelism for 4.5 million rows, you increase the likelyhood of contention.

Check your disk activity and response time, it took less time on my laptop to gather statistics on million rows.

Regards
Michel
Previous Topic: SGA size
Next Topic: Statspack frequency
Goto Forum:
  


Current Time: Fri Dec 13 00:47:14 CST 2024