The Oracle (tm) Users' Co-Operative FAQ
Is there a performance impact on the database of doing an analyze ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.3 - 9.0.1 |
| The question in the title is somewhat ambiguous. The reason for doing an analyze is because you want to have a performance impact, i.e. you want Oracle to find the best possible paths for a query. Having said that, it is perhaps more commonly the case that the question is aimed at the impact on the database as the analyze command runs. |
If you do an analyze .. estimate statistics without a sample size, the impact is usually pretty negligible as Oracle checks only about 1043 rows, so the tests for count distinct, count(*) and so on are quite cheap. However, if you do
analyze table XXX compute statistics for table for all indexes for all indexed columns;
on a very large table, the impact will be severe. To a large degree, the impact comes from the physical I/O that Oracle has to do to acquire its sample set, and the CPU and memory (and as a side-effect I/O) usage involved in sorting. Expect to see db file sequential read, db file direct path read, db file direct path write, buffer free waits, and write complete waits as you do a large analyze.
Of course, there is then a relatively small undo/redo overhead as the data dictionary tables are updated with the new statistics. So even if you are doing a very small estimate, but you have a very large number of objects analyzed in a stream, then this part of the activity could have an impact on the rest of the system. You also have to remember that when the statistics on an object change, any cursors in the library cache that are dependent on that object become invalid so that the optimizer can generate a new execution plan - this could also have a temporary impact on performance as huge amounts of hard-parsing takes place.
Having said that, there are usually not very many objects that need frequent analysis; the ones that need it usually do not need it to be a very high estimate, and then it is likely that only a handful of columns in the database need to have histograms generated..
Further reading: N/A
