12-8. Using the Optimizer Statistics
Go to the Enterprise Manager page for managing optimizer statistics by clicking Manage Optimizer Statistics on the Administration tabbed page. Note that GATHER_STATS_JOB should be enabled and for this to work, you should set STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.
From Manage Optimizer Statistcs page, you can perform the following tasks on statistics:
- Gather optimizer statistics manually. this action submits the job that GATHER_STATS_JOB automatically does. This should be done in the case where a table’s contents have changed so much between automatic gathering jobs that the statistics no longer represent the table accurately. Examples of this are a table that is truncated in the middle of the day and a batch job that runs and adds large amounts of data to a table.
- Restore optimizer statistics to a point in the past. The point in time chosen must be within the optimizer statistics retention period, which defaults to 30 days.
- Lock optimizer statistics to guarantee that the statistics for certain objects are never overwritten. This is useful if statistics have been calculated for a certain table at a time when well-representative data is present, and you want to always have those statistics. No fluctuation in the table affects the statistics if they are locked.
- Unlock optimizer statistics to undo the previously done lock
- Delete optimizer statistics to delete statistics.
When to re-organize a table
Notes from: All about chained rows @ Ask Tom
Before jumping into the topic, we need to first clarify some concepts:
- A row that doesn’t fit into a block A needs to be “chained” with another block B.
- Now, if there was another row in the block A then it needs to be “migrated” to another block when block A fills up.
- a Full table scan example is SELECT * FROM t
- a query using an index is for example SELECT * FROM t WHERE t.col1 = 1 (it uses the index on col1 to find the row where t.col1 = 1)
Migrated rows and their impact on:
- Full table scan: are not affected by migrated rows since it picks up data while reading the disks
- Querying migrated rows with index (instead of full table scan): go through the ROWID pointer (that was left behind when a row was migrated) to find the data it needs so it adds 1 to Table Fetch Continue Row count.
Chained rows and their impact on:
- Full table scan: since we can’t find the data in one place only, we will need an additional read to find all the data
- Querying chained rows with index (instead of full table scan): idem.
Conclusion
Chained rows are inevitable so there’s not much we can do unless the data model could be changed so that fewer data is stored – but that’s a functional problem. On the other hand, if we have lots of migrated rows that each of them would perfectly fit in one datablock, then we could potentially reorganize the table to reduce the additional disk lookup.
12-5. Optimizer Statistics
Optimizer statistics include table, column, index and system statistics. They provide the optimizer a statistically correct snapshot of data storage and distribution, which the optimizer uses to make decisions on how to access data.
The statistics that are collected include:
- Size of the table or index (in database blocks)
SELECT blocks FROM dba_tables WHERE owner=’hr’ AND table_name=’EMPLOYEES’ - Number of rows
SELECT num_rows FROM dba_tables WHERE owner=’hr’ AND table_name=’EMPLOYEES’ - Average row size and chain count (tables only)
SELECT avg_row_len, chain_cnt FROM dba_tables WHERE owner=’hr’ AND table_name=’EMPLOYEES’ - Height and number of deleted leaf rows (indexes only)
Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day.
A large table that experiences 10 percent growth (or reduction) within a 24-hour period is usually considered too volatile for statistics collection once per day to be sufficient. For tables that experience this level of change, Oracle recommends collecting statistics more frequently so it won’t exceed 10 percent growth between collection periods.
Statistics can be manually collected by using Enterprise Manager or through the use of the DBMS_STATS package as shown here:
SQL> EXEC dbms_stats.gather_table_stats(‘HR’, ‘EMPLOYEES’);
