Introduction to solutions to ensure performance of Oracle database tables and Indexes

PL/SQL statements are the optimizer executed by the Oracle database, which has cost-based optimizer CBO) and rule-based optimizer RBO ). We all know that the optimization methods of RBO depend on a set of strict syntax rules.

The "Execution Plan" of PL/SQL statements is not affected as long as the statements written according to the rules are changed regardless of whether the data table or index content changes ".

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 Oracle database server, 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', '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 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.

Article by: http://database.51cto.com/art/200703/43583.htm