Introduction to Oracle analyze

Optimizer for PL/SQL statement execution in Oracle databases, including cost-based optimizer (CBO) and rule-based optimizer (RBO ).

The RBO optimization method depends on a set of strict syntax rules, as long as the statements written according to the rules, regardless of whether the data table and index content change, the execution plan of PL/SQL statements is not affected ".

CBO was introduced from oracle7. Many new technologies adopted by Oracle since version 7 are only based on CBO, such as star join arrangement query, hash join query, reverse index, and index table, partition tables and parallel queries. CBO calculates the "cost" of various possible "execution plans", namely, cost. It selects the lowest cost solution as the actual operation plan. The cost calculation of each "Execution Plan" depends on the statistical distribution of data in the data table. The Oracle database itself is not clear about the statistical distribution and requires analysis tables and related indexes, to collect the data required by CBO.

CBO is a recommended Optimization Method for Oracle. To use CBO well and maximize the efficiency of SQL statements, the timeliness of statistical data must be ensured.

Statistical information can be generated using the full calculation method and sampling estimation method. SQL example:

Full calculation method: Analyze table ABC compute statistics;
Sample estimation method (sample 20%): Analyze table ABC estimate statistics sample 20 percent;

The full Calculation of a table takes the same time as a full table scan. The sampling estimation method uses sampling to generate statistics faster than the full calculation method. If accurate data is not required, sampling analysis is recommended. We recommend that you use sample Estimation for table analysis and use full Calculation for index analysis.

We can use the following two methods to regularly analyze and generate statistical information for database tables, indexes, and cluster tables to ensure the normal performance of applications.

1. Set scheduled tasks in the system and execute the analysis script.

On the database server side, we run the analyze script with the Unix user oracle. In analyze, we generate and run the SQL script to be executed. (Suppose we want to analyze all the tables and indexes under Scott)

The content of the analyze script is as follows:

Sqlplus Scott/tiger <EOF
Set pagesize 5000
Set heading off
Spool analytab. SQL
Select "analyze table Scott." | table_name | "estimate statistics sample 20 percent;" from user_tables;
Spool off
Spool analyind. SQL
Select "analyze table Scott." | table_name | "estimate statistics sample 20 percent for all indexes;" from user_tables;
Spool off
Spool analyze. Log
@ Analytab. SQL
@ Analyind. SQL
Spool off
Exit

Add crontab on the UNIX platform. The preceding files are set to run every month or a suitable 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 the package provided by Oracle to analyze related database objects.

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

Parameters of the stored procedure in the package are described as follows:

Type can be one of table, index, and cluster.
Schema: Table, index, cluster owner, null is the current user.
Name: name of the object.
Method: Estimate, compute, or delete. estimate is used,
In the following two items, estimate_rows and estimate_percent cannot both be null values.
Estimate_rows is the estimated number of samples.
Estimate_percent is the estimated sample percentage.
Method_opt is: there are the following options,
For Table
[For all [indexed] Columns] [size N]
For all indexes
Partname is the name of the partition to be analyzed.

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 analyze specific tables, indexes, and cluster tables. For example, perform a 50% sample analysis on Scott's EMP table. The parameters are as follows:

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 );

Analyze_schema is used to analyze statistics on all tables, indexes, and clusters owned by a user. Analyze_database is used to analyze and count the entire database.

3) dbms_stats is a new package in Oracle8i. It makes statistics generation and processing more flexible and convenient, and can generate statistics in parallel. The following process in the package analyzes and counts table, index, schema, and database-level information respectively.

Dbms_stats.gather_table_stats
Dbms_stats.gather_index_stats
Dbms_stats.gather_schema_stats
Dbms_stats.gather_database_stats

Here, we analyze all the tables and indexes in the Scott mode in the database regularly in the form of database jobs:

Run 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

In the preceding job, dbms_utility.analyze_schema is used every other month to perform statistical analysis on all the tables, cluster tables, and indexes of user Scott.