| ANALYZE CONTINUES AFTER ORACLE ERROR ? [message #409826] |
Wed, 24 June 2009 03:37  |
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  |
flyboy
Messages: 1903
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.
...
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
