OPTIMIZER_MODE and gathering Statistics

There are 3 levels of optimization of database for our getting right statistics.

Type of Statistics is divided into Object and System statistics

For System Statistics we do followings:

At the beginning this is just to delete object statistics for Scott (this is just to get fresh object stats. It is not related to system stats)

Running this block could take a lot of resources and time, therefore it is optimal to save statistics into nominated tables, where we can use it in switching the system OLAP or OLTP frequently. Note: this statistics should be rerun in terms of hardware change.

When we switch stats mode it is always less resource and time consuming to use our stat tables.

For object statistics

We can choose on what object we want to gather stats. (Note: in Oracle 9i, gathering stats on sys objects could crash database)

and

Important info we get here is Number of rows, blocks and average row length. Empty_blocks, avg_space and chain count will be gotten from Analyze table compute statistics

and

Here we see Sample_size which indicate how many rows where analyzed for statistics and Last_analyzed column indicate when statistics were gathered.

Now, what are we going to do if we know that our table is very big with multimillion rows or billions?

We do following:

we are running our procedure with indicating estimate % or our table like

dbms_stats.gather_table_stats(ownername => 'scott', tabname = > 'base', estimate_percent => 20);

This is giving us result of only 20% of table. Oracle recommends the optimal percentage of the size is 20%.

In Oracle 9i if we take 50% it would round it and take like 100%, but in Oracle 11g we get real amount of size which we will indicate.

Another method is to give all to automatically oracle decide how many percent to get. On our example above, our table seemed to be small so Oracle decided to get all rows.

Also, automatically statistics are gathered on the columns and indexes related to this table.

Here is an example on columns.

and

On index of the table

and

Here we see all about gathered indexes info of the table.

Now, after all if we look at our explain plan for our query, we can see all our details on performing query.

In addition to that we should always consider whether we need to gather statistics or not.

This depends on how object are used. Whether they continiuosly changed and increase by size or some sort of all joins....

This is very time and resouse consuming, so before to it we always consider how big and busy objects are and rethink how to perform it.

Thank you.