Data statistics for Oracle databases (ANALYZE)

Data statistics for Oracle databases (ANALYZE)

Oracle data Optimizer has two optimization methods: Based on cost/cost optimizer (CBO) and rule-based Optimizer (RBO), when the system optimizes the SQL, which optimization is used to determine the initialization parameter Optimizer_mode, The parameter value can be set to: Choose,rule,all_rows,first_rows. New tuning parameter commands are also introduced in later versions of Oracle9i: First_rows_1,first_rows_10, first_rows_100,first_rows_1000. (Specific instructions will be introduced in a future blog post) Oracle recommends the CBO optimization approach, when the system uses CBO to optimize SQL, to make its execution plan optimized, it is necessary to perform data statistics regularly, and it is necessary to ensure the timeliness of statistical data, otherwise it may not get the expected optimization effect. Or with the expected optimization effect difference.

To generate statistics for a database object, you can have the following methods:
Complete statistical method: Analyze table table_name compute statistics;
Sampling estimation method: Analyze table table_name estimate statistics sample percent;
The sampling estimation method is faster than the complete statistical method, but the statistic data may not be accurate. During the development process, we might have to involve a lot of table queries, and when we use the CBO we often need to perform statistical analysis on these tables to get the statistics the CBO needs. There are usually several ways to collect statistics:
1. Export the statement script for all tables that you want to analyze, and then execute the script.
Sql> SPOOL off;
Sql> SPOOL C:/analyze_tab. Sql
Sql> SELECT ' ANALYZE TABLE ' | | owner| | '. ' | | table_name| | ' COMPUTE STATISTICS; ' From All_tables WHERE OWNER isn't in (' SYS ', ' SYSTEM ');
Then adjust the script and execute:
Sql>@c:/analyze_tab. Sql
You can put the script on the server side and set up automatic execution.

2. Use Oracle-provided process: DBMS_DDL. Analyze_object, this procedure can perform statistics on a specific table for a particular user. For example:
Full statistics:
Sql>execute dbms_ddl.analyze_object (' TABLE ', ' Dinyar ', ' dinya_test01 ', ' COMPUTE ');

Pl/sql procedure successfully completed

Sql>

50% Sampling Statistics

Sql>execute dbms_ddl.analyze_object (' TABLE ', ' Dinyar ', ' dinya_test01 ', ' estimate ', null,50);

Pl/sql procedure successfully completed

Sql>

You can use this procedure to generate a script that analyzes statistical database objects and executes the script at timed intervals.

3. Use Oracle-provided process dbms_utility. Analyze_schema This process performs statistical analysis of Table,index and cluster under a specific user. Such as:

All objects under the full statistics schema:

Sql> Execute Dbms_utility.analyze_schema (' Dinyar ', ' COMPUTE ');

Pl/sql procedure successfully completed

Executed in 6.9 seconds
Sql>

Sampling all objects under the 50% statistical schema:

Sql> Execute Dbms_utility.analyze_schema (' Dinyar ', ' estimate ', null,50);

Pl/sql procedure successfully completed

Executed in 1.933 seconds

Sql>

From the time of execution, the time taken for sampling statistics is shorter than the time spent in full statistics, and execution is faster.

4. Use Oracle-provided process dbms_utility. Analyze_database, this process can analyze the objects in the entire database. However, the system will be prompted for an error if the current login user has sufficient privileges. Such as:

Sql> Execute dbms_utility.analyze_database (' COMPUTE ');

Begin Dbms_utility.analyze_database (' COMPUTE '); End

Ora-20000:you have insufficient privileges for the this database.
Ora-06512:at "SYS. Dbms_utility ", line 501
Ora-06512:at Line 1

Sql>

To log on to a user with DBA authority:
Sql> Execute dbms_utility.analyze_database (' COMPUTE ');
Begin Dbms_utility.analyze_database (' COMPUTE '); End

Ora-30657:operation not supported on external organized table
Ora-06512:at "SYS. Dbms_ddl ", line 179
Ora-06512:at "SYS. Dbms_utility ", line 497
Ora-06512:at Line 1

Sql>

As you can see from the error message above, the analysis of the external table is not supported and the Oracle solution is viewed, Oracle says do not attempt to do this.
Dbms_utility. The sampling analysis statistics of analyze_database are similar to those in the previous example.

5. Using Oracle-Provided procedures: dbms_stats, the process in the package dbms_stats.gather_index_stats,

Dbms_stats.gather_table_stats,dbms_stats.gather_schema_stats,dbms_stats.gather_database_stats,dbms_ Stats.gather_system_stats performs statistical information on indexes, tables, schemas, databases, and systems, respectively. For example:

Sql> Execute dbms_stats.gather_table_stats (' Dinyar ', ' dinya_test01 ');

Pl/sql procedure successfully completed
Executed in 0.29 seconds
Sql> Execute dbms_stats.gather_schema_stats (' Dinyar ');
Pl/sql procedure successfully completed
Executed in 7.07 seconds
Sql>

(There are other procedures in the package that can manipulate objects in the database, not discussed here.) ）

6. Perform analysis statistics regularly, use Dbms_job package, create a job, execute the procedure periodically, analyze the database objects:

Pl/sql procedure successfully completed

Executed in 0.581 seconds

Job_num
---------
41

Sql>

This allows the collection of database object statistics to be performed regularly in the database, ensuring the accuracy of the optimization path when using the CBO optimizer.

Additional features in the packages provided by Oracle refer to the documentation: <<oracle9i supplied Pl_sql Packages and Types Reference >>