Oracle statistics collection

We all know good old “analyze table” SQL statement to collect statistics of a table. With newer version of Oracle database release, dbms_stats package becomes preferred method and it is much more powerful than “Analyze table” SQL. Starting with Oracle 10g, oracle uses dedicated background process to collect database statistics and it is on by default. However, because oracle considering column cardinality is independent to each other, the optimizer can generate bad execution plan if the column’s data distributions are related, such as column state and column zip code. To make the oracle to do the right thing, we can use dynamic sampling hint to ask Oracle collect statistics on fly just for the query to be run to get correct cardinality. Dynamic sampling does add some load tax on database and Oracle would not store the good execution plan generated with dynamic sampling. Start with Oracle 10g, Oracle SQL profile would collect stats for a query and store the execution plan to run such query all the time later. It is particularly useful that you can modify the SQL profile manually to let a query run the execution plan you like and with “force match” option, the execution plan can be applied to similar query that does not use binding variables. Add hint to a SQL becomes so much a thing of yesterday with SQL profile.

Many faces of an Oracle data block

There are all kinds of data block in Oracle database, such as table segment block, index data block, undo segment block, or data file head block, to name a few. Let’s just talk about the table data block for now.

For a single table data block, Oracle can have many copies of it and many versions of it. The data block could be a disk image or memory image in data buffer cache. Oracle can update the data block and such change is not necessary to be written to disk immediately. In other word, the versions of the data block in memory and on disk are different. Things get more complicated in RAC. Current version of data block can exist in multiple Oracle instances. To implement Oracle read consistence, the old image of data block called Past Image in memory is constructed and is sent to remote instance because remote instance has no access of local Undo segment to construct read consistent data.It is possible that many versions of Past Image data blocks in a single instance.