Oracle Database Optimization Statistics

1. Introduction to statistical information

The statistic information mainly is a kind of information which describes the table in the database, the index size, the scale, the data distribution condition and so on. For example, the number of rows in a table, the number of blocks, the average size of each row, the leaf blocks of an index, the number of rows in an indexed field, the size of different values, etc., are statistical information. Based on these statistical data, the CBO calculates the cost of various schemes under different access paths, and finally chooses the plan with the least cost.

In the CBO (cost-based optimizer mode), the execution plan of the SQL statement is determined by the statistical information, and if no statistics are taken, the execution plan is determined by dynamic sampling! It can be said that the statistical information is related to the SQL execution plan is correct, is the guiding ideology of SQL implementation, Oracle initialization Parameters Statistics_level control the level of collection statistics, there are three parameter values:

Basic: Collect essential statistical information

Typical: Collect most of the statistics (default settings for the database)

All: Collect all statistics

After Oracle 10g, Query Optimizer has already used the CBO as the default optimizer, and Oracle no longer supports RBO services. However, with the optimizer parameter Optimizer_mode, we can control the Oracle optimizer to generate execution plans in different modes.

2 Collecting statistical information

Oracle statistic Collection, which can be collected using the Analyze command or Dbms_stats package, Oracle recommends using the Dbms_stats package to collect statistics because dbms_stats packages are collected more broadly,
and more accurate. Analyze may be removed in a later release.

Dbms_stats.gather_table_stats collect statistics for tables, columns, and indexes;
Dbms_stats.gather_schema_stats collects statistical information about all objects under the schema;
Dbms_stats.gather_index_stats collection of index statistics;
Dbms_stats.gather_system_stats Collecting System statistics
Dbms_stats. Gather_dictionary_stats: Statistics of all Dictionary objects;
Dbms_stats. Gather_dictionary_stats its collection of all system mode statistics
Dbms_stats.delete_table_stats Deleting a table's statistics
Dbms_stats.delete_index_stats to delete statistics for an index
Dbms_stats.export_table_stats Statistics for output tables
Dbms_stats.create_state_table
Dbms_stats.set_table_stats setting the statistics of a table
Dbms_stats.auto_sample_size

The syntax of the Analyze command is as follows:
Sql>analyze table TableName COMPUTE statistics;
Sql>analyze TABLE TableName Compute statistics for all indexes;
Sql>analyze Table TableName Delete statistics

3 Classification of statistical information

Oracle's collection of statistic information is divided into two types: Automated collection and manual collection.

Oracle's automatic Statistics gathering is collected and maintained through scheduler. The job name is Gather_stats_job, which collects 2 statistics about all objects in the database:

(1) Missing Statistics (statistical information missing)

(2) Stale statistics (statistical information obsolete)

The job is created automatically when the database is created and managed by scheduler. Scheduler run the gather job when maintenance windows open. By default, jobs are opened every night from 10 to 6 in the morning and on weekends throughout the day. The process first detects missing statistics and obsolete objects. Then prioritize and start the statistics.

The Stop_on_window_close property of the Scheduler JOB controls whether Gather_stats_job continues. The default value of this property is true. If the value is set to False, the gather_stats_job is interrupted, and objects that are not collected will continue to be collected the next time they are started.

Gather_stats_job calls the Dbms_stats.gather_database_stats_job_proc procedure to collect statistics information. The process collects objects statistics the following conditions:

(1) The statistics for the object have not been collected before.

(2) When the object has more than 10% rows modified, the object's statistics are also called stale statistics.

Select Job_name, program_name, enabled, Stop_on_window_close
From Dba_scheduler_jobs
where job_name = ' gather_stats_job ';

The statistical information collects the following data:
(1) Analysis of the table itself: including the number of rows in the table, the number of data blocks, the president and other information.
(2) Analysis of columns: includes the number of repetitions of the column values, the null value on the column, and the distribution of the data on the column.
(3) Index Analysis: Including the number of index leaf block, index depth, index aggregation factor, etc.

These statistics are stored in the following data dictionary:
Dba_tables
Dba_object_tables
Dba_tab_statistics
Dba_tab_col_statistics
Dba_tab_histograms
Dba_indexes
Dba_ind_statistics
Dba_clusters
Dba_tab_partitions
Dba_tab_subpartitions
Dba_ind_partitions
Dba_ind_subpartitions
Dba_part_col_statistics
Dba_part_histograms
Dba_subpart_col_statistics
Dba_subpart_histograms

Contains the number of table rows, the number of blocks used, the number of empty blocks, the usage of blocks, the number of row migrations and links, the pctfree,pctused data, the average size of the rows:
SELECT Num_rows,--number of records in the table
BLOCKS, the number of data blocks in the table
Empty_blocks,--Number of empty blocks in the table
Avg_space,--average space used in data blocks
CHAIN_CNT,--number of row connections and line migrations in a table
Avg_row_len--average length of each record
From User_tables

Contains the depth of the index (the level of B-tree), the number of blocks in the index leaf level, the cluster factor (Clustering_factor), and the number of unique values.
SELECT Blevel,--Number of layers indexed
Leaf_blocks,--Number of leaf nodes
Distinct_keys,--Number of unique values
Avg_leaf_blocks_per_key,--the average number of leaf blocks per KEY
Avg_data_blocks_per_key,--average number of data blocks per KEY
Clustering_factor--Cluster factor
From User_indexes

Contains a unique number of values, column maximum small value, density (selection rate), data distribution (histogram information), Number of NULL values
SELECT num_distinct,--Number of unique values
Low_value,--The minimum value on the column
High_value,--the maximum value on the column
DENSITY,--selection rate factor (density)
Num_nulls,--Number of NULL values
Num_buckets,--number of buckets in the histogram
Histogram--Types of histograms
From User_tab_columns



The 1,sql/plus window runs the following command

Set time on; (Description: Open time display) (optional)
Set autotrace on; (Description: Turn on automatic analysis statistics and display the results of the SQL statement)
Set Autotrace traceonly; (Description: Turn on automatic analysis statistics, do not display the results of the SQL statement running)

4, next you run the SQL statement that needs to look at the execution plan and see its analysis statistics. In general, our SQL statements should avoid full table scans of large tables.

5, turn off the above features, run the following command in the Sql/plus window

Set time off; (Description: Turn off time display)
Set Autotrace off;

Attached: The associated set autotrace command:



2. Demand Analysis:

The analysis found that some large tables of the statistical date has been 2, 3 months have not been updated.
Select table_name, Num_rows, last_analyzed from User_tables; 1

Call Dbms_stats.gather_table_stats (' User name ', ' table name ');

Select ' Alter system kill session ' | | Sid | | ' | | ', ' | | serial# | | '; ' from v$session where username= ' Jinrilog '

3. Automation scripts

Keep the following two scripts in the same directory, note that you modify the database configuration parameters, and then perform dynamic updates periodically using Windows scheduled tasks;

Script 1: Automatically update Oracle statistics. BAT

Title automatically updates Oracle statistics [%date%%time%]

Sqlplus username/[email protected]/db_name @gen_sqls. sql | Find "Call Dbms_stats.gather_table_stats" >stats_sqls.sql
Echo Exit>>stats_sqls.sql
Sqlplus username/[email protected]/db_name @stats_sqls. sql
echo Script Execution complete 1

Script 2:gen_sqls.sql

Select
' Call Dbms_stats.gather_table_stats (' username ', ' ' | | table_name | | '); As Sqls
From User_tables A
where a.last_analyzed <sysdate-7
and num_rows > 1000
Order BY Num_rows;
Exit





Analyze all tables under a user
Select ' Call Dbms_stats.gather_table_stats (' | | OWNER | | "," | | table_name | | '); As Sqls from Dba_tables where owner= ' SCOTT '


Select ' Exec dbms_stats.gather_table_stats (' | | OWNER | | "," | | table_name | | '); From Dba_tables where owner= ' HR '

Select ' Exec dbms_stats.gather_table_stats (' | | OWNER | | "," | | table_name | | ') ' From Dba_tables where owner= ' HR '


Way Two

CREATE OR REPLACE PROCEDURE analyzealltable
Is
--2009-10-18 WALLIMN
--Analyze all tables and indexes. Facilitates efficient use of the CBO optimizer
BEGIN
--Analysis of all tables: Analyze table TABLENAME COMPUTE statistics
For Cur_item in (select table_name from user_tables) loop
Begin
Execute immediate ' analyze table ' | | Cur_item.table_name
|| ' Compute statistics ';
exception
When others then
Dbms_output.put_line (' Parse table exception: ' | | SQLERRM);
End
End Loop;

--Analysis of all indexes: Analyze index IndexName estimate statistics
For Cur_item in (select Index_name from user_indexes) loop
Begin
Execute immediate ' analyze index ' | | Cur_item.index_name
|| ' Estimate statistics ';
exception
When others then
Dbms_output.put_line (' Parse index exception: ' | | SQLERRM);
End
End Loop;
END analyzealltable;
/

This article is from the "11300506" blog, please be sure to keep this source http://11310506.blog.51cto.com/11300506/1963264

Oracle Database Optimization Statistics