The statistical data of the Oracle database and the specific Generation Method

We all know the actual application optimizer for PL/SQL statement execution in Oracle databases, which has cost-based related optimizer CBO) and regular-based optimizer RBO ). The actual Optimization Method of RBO depends on a set of strict syntax rules. As long as the statements written according to the rules, no matter whether the data table or index content changes, the execution plan of PL/SQL statements is not affected ".

CBO has been introduced since Oracle version 7. Many new technologies adopted by Oracle 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 Oracle 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.

2. Use the PACKAGE provided by Oracle to analyze related Oracle 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 be the same

Is null.

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/* only statistical tables */

[For all [INDEXED] COLUMNS] [size n]/* count only indexed columns */

For all indexes/* only analyze and count related 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', 'estime', 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 collect statistics on the entire Oracle 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 regularly analyze all the tables and indexes in SCOTT mode in the Oracle database using database JOB:

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 
 
 

The preceding job uses DBMS_UTILITY.ANALYZE_SCHEMA to perform statistical analysis on all user SCOTT tables, cluster tables, and indexes every other month.