Oracle Database The optimizer executed by the PL / SQL statement, with a cost-based optimizer (CBO) and rule-based optimizer (RBO). RBO optimization, depending on a strict syntax rule, as long as the statement written in accordance with the rules, regardless of whether the content of the data table and the index change, does not affect the "execution plan" of the PL / SQL statement. CBO is introduced from Oracle 7, many new technologies used in Oracle Since 7th editions are only based on CBO, such as star connection arrangement, haveh connection queries, reverse index, index table, partition table, and parallel inquiry, etc. . CBO calculates various "considerations" that may "implement plan", that is, COST, which uses COST's lowest solution as the actual operation. The calculation according to the "execution plan", depending on the statistical distribution of data in the data sheet, and the Oracle database itself is unclear, and must analyze the table and related index to collect the data required to CBO. CBO is an ORACLE recommended to use the optimization method. To use good CBO, make the SQL statement to maximize performance, must guarantee the timeliness of statistics. The generation of statistics can have a complete calculation method and sampling estimate. SQL examples: Analyze Table Abc compute statistics; sampling estimation (sample 20%): Analyze Table Abc Estimate statistics sample 20 percent; time spent on the full calculation of the table is equivalent to do full mete scan, sampling estimate Due to the sample, the method is fast than the fully calculated method, if it is not required to have precise data, try to use a sampling analysis. It is recommended to use sampling estimates to use a full calculation. We can use the following two ways to generate statistics on the table and index and cluster table of the database to ensure the normal performance of the application. 1. Set the timing task in the system to perform an analysis script. In the database server side, we run script analysis in Unix users, in Analyze, we have to perform SQL scripts and run. (Suppose we want to analyze all tables and indexes in scott user) Analyze script as follows: sqlplus scott / tiger << EOFset pagesize 5000set heading offSPOOL ANALYTAB.SQLSELECT '. ANALYZE TABLE SCOTT' || TABLE_NAME || 'ESTIMATE STATISTICS SAMPLE 20 PERCENT ; 'FROM USER_TABLES; SPOOL OFFSPOOL ANALYIND.SQLSELECT'. ANALYZE TABLE SCOTT '|| TABLE_NAME ||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES; 'FROM USER_TABLES; SPOOL OFFSPOOL ANALYZE.LOG @ ANALYTAB.SQL @ ANALYIND.SQLSPOOL OFFEXIT in The crontab is added to the UNIX platform, and the above file is set to run each month or the appropriate time period. 2. Analyze the related database objects with the package provided by Oracle. There are the following packages to analyze the table, index, and cluster table.
The relevant parameters of the stored procedures in the package are as follows: Type can be: Table, INDEX, and Cluster one. Schema is: Table, INDEX, Cluster owner, null is current user. Name is: The name of the related object. Method is: Estimate, Compute, Delete One, elected Estimate, two below, Estimate_Rows and Estimate_Percent cannot be simultaneously empty. Estimate_Rows is: estimated sampling line. Estimate_percent is: estimated sample percentage. Method_opt is: FOR TABLE / * Only Statistics Table * / [for all [indexed] columns] [size n] / * only statistics have indexed table columns * / for all indexes / * only analyze statistics related index * / Partname is: Specify the partition name 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 procedure can be stored for a particular table, an index, and The cluster table is analyzed. For example, for the EMP table of Scott users, 50% sampling analysis, parameters are as follows: dbms_ddl.Analyze_Object ('Table', 'Scott', 'EMP', 'Estimate', NULL, 50); 2) DBMS_UTILITY.AALY_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); wherein, ANALYZE_SCHEMA for Analytical statistics for all Table, INDEX, and Cluster owned by a user. Analyze_database is used to analyze the entire database. 3) DBMS_STATS is a new package in Oracle8i, which makes statistical data generation and processing more flexible, and can generate statistics in parallel. The following procedures in the package analyze the statistics of Table, INDEX, Schema, and Database levels.
