Home » SQL & PL/SQL » SQL & PL/SQL » ANALYZE CONTINUES AFTER ORACLE ERROR ? (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409826] Wed, 24 June 2009 03:37 Go to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
Its something very strange, i hope someone has an idea ...

I have a job that populates data into a warehouse. The basic steps being : 1. truncate all tables 2. disable all indexes 3. populate all tables 3. recreate indexes 4. analyze all tables.

Each individual step is carried out in a for loop for all tables and a record is inserted in a logs table for each individual action performed to keep a track of the job progress. A commit is also performed after every insertion to the log table.

Of late, this job has started failing in the ANALYSE step for a particular table and we recieve the following error:
"Error - analyze_object_proc XXX failed: ORA-01476: divisor is equal to zero"

on refering an oracle metalink we found that this is an oracle bug and would be installing a path for the same.

My problem is, even AFTER this error is encountered
1, all the remaining tables after this table in error(in the FOR loop) are getting analysed (i can verify this by the Last_analysed date on these tables)
2, BUT the same is not getting reflected in my logs table

i.e. if my tables are a,b,c,d (processed in a for loop)
and if analyse fails for b then my log table only contains details confirming 'a' was anlysed and analyse for 'b' resulted in error. BUT the last analysed date is updated for a,b,c,d ....

I have verified this on a number of occasions ...

does any one have an explanation for this behaviour please ?

Re: ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409839 is a reply to message #409826] Wed, 24 June 2009 04:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Oracle Database Performance Tuning Guide book (a part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/) contains very useful information - even the answer to your question.

Chapter 14 Managing Optimizer Statistics
14.2 Automatic Statistics Gathering
Quote:
...
Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:
* Missing statistics
* Stale statistics
This job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
...
Re: ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409871 is a reply to message #409839] Wed, 24 June 2009 05:13 Go to previous messageGo to next message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
thanks flyboy, this should be it...
Re: ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409887 is a reply to message #409871] Wed, 24 June 2009 06:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
On 10g, you really should be using Dbms_Stats, rather than Analyze.
Re: ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409905 is a reply to message #409887] Wed, 24 June 2009 07:12 Go to previous message
niteshsabharwal
Messages: 17
Registered: June 2009
Location: Manchester, UK
Junior Member
@JRowbottom,yes, we are actually using dbms_stats to analyze tables...
Previous Topic: Creating procedure dynamically-Insufficient privilages
Next Topic: BFILE,LOB
Goto Forum:
  


Current Time: Sun Dec 04 16:35:30 CST 2016

Total time taken to generate the page: 0.10440 seconds