| Snap Shot Error on User_Tables [message #280709] |
Wed, 14 November 2007 10:23  |
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 #280732 is a reply to message #280724] |
Wed, 14 November 2007 10:55  |
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'
Thanks
[Updated on: Wed, 14 November 2007 10:57] Report message to a moderator |
|
|
|
|
|
| Re: Snap Shot Error on User_Tables [message #280817 is a reply to message #280741] |
Wed, 14 November 2007 20:55 |
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 #283616 is a reply to message #280834] |
Tue, 27 November 2007 13:25 |
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 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
