| dbms_stats.gather_schema_stats causes ORA-01555 [message #243055] |
Tue, 05 June 2007 17:00  |
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  |
 |
BlackSwan
Messages: 26766
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 |
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
