Introduction to Oracle's analyze usage

The optimizer for Pl/sql statement execution of an Oracle database has a cost-based optimizer (CBO) and a rule-based optimizer (RBO).

The Rbo optimization method relies on a strict set of grammatical rules, and the statement written by rules, regardless of whether the contents of the datasheet or index change, does not affect the "execution plan" of the Pl/sql statement.

The CBO has been introduced since the ORACLE7 version, and many of the new technologies used by Oracle since version 7 are based only on CBO, such as star-link routing queries, hash-connected queries, reverse indexing, index tables, partitioned tables, and parallel queries. The CBO calculates the "cost" of a variety of possible "execution plans", that is, costs, from which the least expensive scheme is chosen as the actual operating plan. The calculation of the cost of each "execution plan" depends on the statistical distribution of the data in the data table, and the Oracle database itself is not clear about the statistical distribution, and needs to analyze tables and related indexes to collect the data required by the CBO.

The CBO is the recommended optimization method for Oracle, and the timeliness of the statistics must be ensured in order to use a good CBO to maximize the effectiveness of the SQL statements.

The statistical information can be generated by the method of complete calculation and sampling estimation. The SQL examples are as follows:

Complete calculation method: Analyze table ABC compute statistics;
Sampling estimation method (sample 20%): Analyze table ABC estimate statistics, sample percent;

The time spent on the complete calculation of the table is equivalent to doing a full table scan, the sampling estimation method is more rapid than the complete calculation method because of sampling, and if it is not required to have accurate data, the sampling analysis method is used as far as possible. It is suggested that sampling estimation should be used for table analysis, and full calculation can be used for index analysis.

We can use the following two methods, the database table and index and cluster table analysis to generate statistical information on a regular basis to ensure the normal performance of the application.

1. In the System Setup timed task, execute analysis script.

On the database server side, we run the script analyze with UNIX user Oracle, and in analyze we generate the pending SQL script and run. (Suppose we want to analyze all the tables and indexes under Scott's user)

The contents of the Analyze script are as follows:

Sqlplus Scott/tiger << EOF
Set PageSize 5000
Set Heading off
SPOOL Analytab. Sql
Select "ANALYZE TABLE SCOTT." | | table_name| | " Estimate STATISTICS SAMPLE PERCENT; " From User_tables;
SPOOL off
SPOOL Analyind. Sql
Select "ANALYZE TABLE SCOTT." | | table_name| | " Estimate STATISTICS SAMPLE PERCENT for all INDEXES; From User_tables;
SPOOL off
SPOOL ANALYZE. LOG
@ANALYTAB. Sql
@ANALYIND. Sql
SPOOL off
EXIT

On the UNIX platform crontab join, the above files, set to run every month or the appropriate time period.

or save the following script as Analyze.sql and execute it in Sqlplus:

Set PageSize 5000
Set Linesize 300
Set trims on
Set Heading off
Set Feedback off
SPOOL Analytab.sql
SELECT ' ANALYZE TABLE zfmi. ' | | table_name| | ' COMPUTE STATISTICS; '

From User_tables;
SPOOL off
SPOOL Analyidx.sql
SELECT ' ANALYZE TABLE zfmi. ' | | table_name| | ' COMPUTE STATISTICS

For all INDEXES; ' From User_tables;
SPOOL off
SPOOL AnalyLog.log
@ @analyTab. sql
@ @analyIdx. sql
SPOOL off

2. Use Oracle-provided packages (PACKAGE) to analyze related database objects.

The following packages can be used to analyze tables, indexes, and cluster tables.

The relevant parameters for the stored procedures in the package are explained as follows:

Type can be: one of the table,index,cluster.
Schema is: Table,index,cluster owner, NULL is the current user.
Name is: the names of related objects.
Method is: One of the Estimate,compute,delete, elected to use estimate,
The following two items, Estimate_rows and estimate_percent, cannot be null at the same time.
Estimate_rows is: Estimated number of sample rows.
Estimate_percent is: The estimated percentage of the sample.
Method_opt: The following options are available,
For TABLE
[For all [INDEXED] COLUMNS] [SIZE N]
For all INDEXES
PartName is: Specifies the name of the partition to be parsed.

1)

Dbms_ddl. Analyze_object (
TYPE VARCHAR2,
SCHEMA VARCHAR2,
NAME VARCHAR2,
Method VARCHAR2,
Estimate_rows number DEFAULT NULL,
Estimate_percent number DEFAULT NULL,
Method_opt VARCHAR2 DEFAULT NULL,
PartName VARCHAR2 DEFAULT NULL);

This stored procedure can be used to analyze specific tables, indexes, and cluster tables. For example, for the Scott User's EMP table, a 50% sample analysis is performed, with the following parameters:

Dbms_ddl. Analyze_object ("TABLE", "SCOTT", "EMP", "estimate", null,50);

2)

Dbms_utility. Analyze_schema (
SCHEMA VARCHAR2,
Method VARCHAR2,
Estimate_rows number DEFAULT NULL,
Estimate_percent number DEFAULT NULL,
Method_opt VARCHAR2 DEFAULT NULL);
Dbms_utility. Analyze_database (
Method VARCHAR2,
Estimate_rows number DEFAULT NULL,
Estimate_percent number DEFAULT NULL,
Method_opt VARCHAR2 DEFAULT NULL);

Where Analyze_schema is used to analyze statistics for all table,index and cluster owned by a user. Analyze_database is used to analyze statistics for the entire database.

3) Dbms_stats is a new package in oracle8i that makes it more flexible to generate and process statistical data, and can generate statistics in parallel. The following procedures in the package analyze the statistics table,index,schema,database level information separately.

Dbms_stats. Gather_table_stats
Dbms_stats. Gather_index_stats
Dbms_stats. Gather_schema_stats
Dbms_stats. Gather_database_stats

Here we analyze all of the tables and indexes in Scott mode in the database at timed intervals in the form of a database job:

To run under Sql*plus:

VARIABLE jobno number;
BEGIN
Dbms_jobs. SUBMIT (: Jobno,
"Dbms_utility.analyze_schema (" Scott "," estimate ", NULL, 20);"
Sysdate, "sysdate+30");
Commit
End
/
Statement processed.
Print Jobno
Jobno
-------------
16

The above work, every one months with dbms_utility. Analyze_schema A statistical analysis of all the tables, clusters, and indexes of user Scott.