Statistics

Features Which can use to influence the decision path of the optimizer when selecting a plan:


*    Initialization parameters
*    Statistics
*    Hints
*    SQL profiles
*    SQL plan management (plan baselines)
*    Stored outlines (deprecated in favor of plan baselines)

SQL Profiles


SQL profiles are optionally generated corrections and improvements to statistics. The recommendation (and code) to implement a SQL profile is manifested through the output of the SQL Tuning Advisor. You can manually enable SQL profiles or configure them to be automatically accepted. SQL profiles help the optimizer derive better execution plans.

When working with tuning advice and SQL profiles, ensure that the database account you're using has the ADMINISTER SQL MANAGEMENT OBJECT system privilege granted to it. This privilege contains all of the privileges required to manage tuning tasks and SQL profiles.

How do you know if a SQL profile is being used by the optimizer?

Set AUTOTRACE on and view the execution plan with the profile enabled and then disabled. You should see a lower-cost execution plan being used when the profile is enabled. Additionally, consider inspecting the SQL_PROFILE column of V$SQL.


Lowest Cost in the Case of a Execution plan means least amount of database resources such as CPU, I/O, and memory.


SQL Plan Baseline


If a plan baseline exists for a query, and if the low-cost plan (from step 1) has a lower cost than the plan from the plan baseline, then the low-cost plan is automatically added to the plan history for the query in an unaccepted state. You can choose to move plans from the plan history into the plan baseline so that the optimizer will consider them when choosing an execution plan.

Statistics Collection


You can query the DBA_OPTSTAT_OPERATIONS view to find out the beginning and ending times for the automatic statistics collection job.

You specify the AUTO value for the estimate_percent parameter in the following way:

SQL> exec dbms_stats.gather_table_stats(NULL, <Table_name>, estimate_percent=>
dbms_stats.auto_sample_size)

When you set the AUTO value for the estimate_percent parameter, not only does the database automatically determine the sampling size, but it also adjusts the size of the sample as the data distribution changes. NDV is a good criterion to calculate the accuracy of the statistics collected with varying samples sizes. The NDV of a column is defined as follows:

accuracy rate = 1 - (estimated NDV - actual NDV) /actual NDV

THE ACCURACY RATE CAN RANGE OVER 0 TO 100%. A 100% SAMPLE SIZE WILL ALWAYS GIVE YOU A 100% ACCURACY RATE—WHAT IS SIGNIFICANT IS THAT IN ORACLE 11G, AUTO SAMPLING PROVIDES ACCURACY RATES THAT ARE VERY CLOSE TO 100%, AND TAKE A FRACTION OF THE TIME IT TAKES TO COLLECT COMPLETE STATISTICS FOR A LARGE TABLE.

DYNAMIC SAMPLING is of particular help when dealing with frequently executed queries that involve tables with no statistics.Oracle doesn't perform dynamic sampling for external tables.There's a COST TO DYNAMIC SAMPLING, because the database uses resources to gather statistics during query compilation. If you don't execute these queries many times, the database incurs an overhead each time it executes a query involving table(s) for which it must dynamically collect statistics.

It's important to understand the significance of the dynamic sampling levels, which can range from 0 to 10. Note that the sample size used for dynamic sampling at various sampling levels is in terms of data blocks, not rows and also the sampling levels are to be chosen based on different scenario's.

LOCKING STATS


You may want to lock a table's statistics to freeze the current set of statistics. You may also lock the statistics after you delete the existing statistics first—in this case, you are forcing the database to use dynamic sampling to estimate the table's statistics.

Locking a table also locks all statistics that depend on that table, such as index, histogram and column statistics.

By default, you can't import a table's statistics when the statistics are locked. You can override this property by setting the force parameter to true.

Restoring Previous Version of Statistics


Performance of certain queries has deteriorated suddenly after collecting fresh statistics. You want to see if you can use an older set of statistics that you knew worked well.Use the DBMS_STATS.RESTORE_STATS procedure to revert to an older set of optimizer statistics. Before you restore older statistics, check how far back you can go to restore older statistics using the Query below

SQL> select dbms_stats.get_stats_history_availability from dual;

Execute the RESTORE_*_STATS procedures of the DBMS_STATS package to revert to statistics from an earlier period.

SQL> exec dbms_stats.restore_schema_stats(ownname=>'owner',as_of_timestamp=>'Timestamp',no_invalidate=>false)

Here's how to find out how many days' worth of statistics the database retains by default.

SQL> select dbms_stats.get_stats_history_retention from dual;