Home » RDBMS Server » Performance Tuning » dbms_stats.gather_schema_stats causes ORA-01555
dbms_stats.gather_schema_stats causes ORA-01555 [message #243055] Tue, 05 June 2007 17:00 Go to next message
ajay_patil22
Messages: 3
Registered: June 2007
Junior Member
Hi List

I am on 9.2.04. Have been gathering stats for a few months on this database in under 1 hour using the below script. Now the stats is taking about 5 hours and mostly crashing with ORA-01555 snapshot too old error. Any suggestions on this will be appreciated.

The offending sql is "SELECT owner, index_name FROM DBA_INDEXES WHERE table_name = :b2 AND owner = :b1" which is refering to a few sys tables with not many rows.

Thanks
Ajay

-------------------------------
SQL> DECLARE
2 l_objList dbms_stats.objectTab;
3 plsql_block VARCHAR2(1000);
4 indexname varchar2(50);
5 ERR_NUM NUMBER := 0;
6 ERR_MSG VARCHAR2(100);
7
8 BEGIN
9 dbms_stats.gather_schema_stats
10 ( ownname => '&1',
11 options => 'LIST STALE',
12 objlist => l_objList );
13
14 FOR i in 1 .. l_objList.count
15 LOOP
16 IF ( l_objList(i).objtype = 'TABLE' ) THEN
17 FOR x in (SELECT owner, index_name
18 FROM DBA_INDEXES
19 WHERE table_name = l_objList(i).objName
20 AND owner = l_objList(i).ownname)
21 LOOP
22 BEGIN
23 plsql_block := 'analyze index '||x.owner||'.'||x.index_name||' compute statistics';
24 EXECUTE IMMEDIATE plsql_block;
25
26 EXCEPTION
27 when OTHERS then
28 ERR_NUM := SQLCODE;
29 ERR_MSG := SUBSTR(SQLERRM,1,100);
30 DBMS_OUTPUT.PUT_LINE('ERROR: index='||x.owner||'.'||x.index_name||' ORACLE ERR NBR: ' || ERR_NUM || ' - ' || ' ERR MSG: ' || ERR_MSG);
31 END;
32 END LOOP;

-------------------------------------------------------


Re: dbms_stats.gather_schema_stats causes ORA-01555 [message #243056 is a reply to message #243055] Tue, 05 June 2007 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats2.htm

DBMS_STATS is a "better" way to maintain DB statistics.
Contrary to your title your posted code is NOT using DBMS_STATS

I do:
execute DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', TRUE, NULL, NULL, 'GATHER STALE', 'LIST' );

[Updated on: Tue, 05 June 2007 17:14] by Moderator

Report message to a moderator

Re: dbms_stats.gather_schema_stats causes ORA-01555 [message #243060 is a reply to message #243056] Tue, 05 June 2007 19:32 Go to previous messageGo to next message
ajay_patil22
Messages: 3
Registered: June 2007
Junior Member
OK. I just took over this site 3 days ago. I am yet to understand a few things. Assuming that I want to continue with this code for a few days before I switch to DBMS_STAT; why do you think a select on DBA_INDEXES should give me an ORA-01555 snap shot too old error? Also I am concerned that why is it taking 5 hours now?

UNDO_RETENTION is set to 3 hours.

Thanks for your earlier reply

Re: dbms_stats.gather_schema_stats causes ORA-01555 [message #243061 is a reply to message #243060] Tue, 05 June 2007 19:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
why do you think a select on DBA_INDEXES should give me an ORA-01555 snap shot too old error?



No

Quote:
Also I am concerned that why is it taking 5 hours now?



Whats max and min records in your tables?
Re: dbms_stats.gather_schema_stats causes ORA-01555 [message #243066 is a reply to message #243061] Tue, 05 June 2007 21:27 Go to previous messageGo to next message
ajay_patil22
Messages: 3
Registered: June 2007
Junior Member
minimum is 84 rows and maximum is 100121 rows

Thanks
Re: dbms_stats.gather_schema_stats causes ORA-01555 [message #243090 is a reply to message #243060] Wed, 06 June 2007 00:54 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and apply How to format your posts.

You're using "fetch accross commit", one of the best way to get ORA-01555 error.

Btw, I see 2 loop beginning but just 1 "end loop" in your code.

Regards
Michel




Previous Topic: Limitation for view
Next Topic: Problem in QUICK TUNE advisor
Goto Forum:
  


Current Time: Fri Dec 02 19:04:23 CST 2016

Total time taken to generate the page: 0.07951 seconds