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

Source: Internet
Author: User

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:

 
 
  1. analyze table abc compute statistics; 

Sample estimation method (sample 20% ):

 
 
  1. 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:

 
 
  1. sqlplus scott/tiger << EOF 
  2. set pagesize 5000  
  3. set heading off  
  4. SPOOL ANALYTAB.SQL  
  5. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES;  
  6. SPOOL OFF  
  7. SPOOL ANALYIND.SQL  
  8. SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||

    ' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;  
  9. SPOOL OFF  
  10. SPOOL ANALYZE.LOG  
  11. @ANALYTAB.SQL  
  12. @ANALYIND.SQL  
  13. SPOOL OFF  
  14. 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)

 
 
  1. DBMS_DDL.ANALYZE_OBJECT(  
  2. TYPE VARCHAR2,  
  3. SCHEMA VARCHAR2,  
  4. NAME VARCHAR2,  
  5. METHOD VARCHAR2,  
  6. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  7. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  8. METHOD_OPT VARCHAR2 DEFAULT NULL,  
  9. 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)

 
 
  1. DBMS_UTILITY.ANALYZE_SCHEMA (  
  2. SCHEMA VARCHAR2,  
  3. METHOD VARCHAR2,  
  4. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  5. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  6. METHOD_OPT VARCHAR2 DEFAULT NULL ) ;  
  7. DBMS_UTILITY.ANALYZE_DATABASE (  
  8. METHOD VARCHAR2,  
  9. ESTIMATE_ROWS NUMBER DEFAULT NULL,  
  10. ESTIMATE_PERCENT NUMBER DEFAULT NULL,  
  11. 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.

 
 
  1. DBMS_STATS.GATHER_TABLE_STATS  
  2. DBMS_STATS.GATHER_INDEX_STATS  
  3. DBMS_STATS.GATHER_SCHEMA_STATS  
  4. 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:

 
 
  1. VARIABLE jobno number;  
  2. BEGIN  
  3. DBMS_JOBS.SUBMIT ( :jobno ,  
  4. ' dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ',  
  5. sysdate, 'sysdate+30');  
  6. commit;  
  7. end;  
  8. /  
  9. Statement processed.  
  10. Print jobno  
  11. JOBNO  
  12. -------------  
  13. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.