Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to collect statistical information does not affect the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn "how to collect statistical information does not affect the database"!

In most cases, the incorrect statistical information of the table leads to the incorrect calculation of the execution plan by the optimizer, so the statistical information of the table needs to be corrected.

So that the optimizer can reselect the exact execution plan.

However, in the case of production, random collection of statistical information will bring hidden dangers to the database:

1. For tables that re-collect statistics, some SQL may need to reparse to generate execution plans.

2. For some SQL of tables that re-collect statistics, there may be situations where the statistics are collected, but the execution plan is worse.

3. Collecting statistical information during the peak period of business will require additional resource overhead and affect the performance of the database.

When SQL optimization is carried out, by looking at the execution plan, the statistics of the table and the specific situation of the table, to analyze whether the inaccuracy of statistical information leads to the execution plan.

Problem, when it is determined that it is a problem of statistics, we should not collect statistics blindly. We need to further verify that "re-collecting statistics can improve SQL performance".

Therefore, in view of "re-collecting statistics can improve SQL performance", we will mainly introduce how to re-collect statistics without affecting the database.

SQL that is running.

In Oracle, the collection of statistics is stored in the corresponding data dictionary, so after the normal collection of statistics, it will be used by the corresponding SQL to generate

Carry out the plan. However, Oracle also provides a way to collect statistics but not be recorded in the data dictionary, so it will not be used by the corresponding SQL, only

When you need to use these statistics, you can use these statistics normally by setting some parameters.

Pending Statistics in DBMS_STATS can be used in Oracle to control that newly collected statistics will not be stored in the data dictionary.

The package gather statistics and stores it in the dictionary by default. User's can store these statistics in the system's private area instead of the dictionary by turning the PUBLISH option to FALSE using the SET*PREFS procedures. The default value for PUBLISH is TRUE.The statistics stored in private area are not used by Cost Based Optimizer unless parameter optimizer_use_pending_statistics is set to TRUE. The default value of this parameter is FALSE and this boolean parameter can be set at the session/system level. Users can verify the impact of the new statistics on query plans by using the pending statistics on a session.Pending statistics provide a mechanism to verify the impact of the new statistics on query plans before making them available for general use. There are two scenarios to verify the query plans:Export the pending statistics (use the EXPORT_PENDING_STATS Procedure) to a test system, then run the query workload and check the performance or plans.Set optimizer_use_pending_statistics to TRUE in a session on the system where pending statistics have been gathered, run the workload, and check the performance or plans.Once the performance or query plans have been verified, the pending statistics can be published (run the PUBLISH_PENDING_STATS Procedure) if the performance is acceptable or delete (run the DELETE_PENDING_STATS Procedure) if not.

General meaning: this method can be used to verify the impact of the statistics on the execution plan.

Then a test is used to verify it.

1. Create a test table

SQL > drop table demo purge;Table dropped.SQL > create table demo as select * from dba_objects;Table created.

2. Create an index on the owner column

SQL > create index idx_owner_demo on demo (owner); Index created.

3. Statistics of statistical tables and collection of histogram information of owner columns:

Begin dbms_stats.gather_table_stats (ownname = > 'DEMO', tabname = >' DEMO', estimate_percent = > 100, method_opt = > 'for columns owner size skewonly', no_invalidate = > false Degree = > 1, cascade = > true) End;/

4. View the execution plan of SQL

Take a look at the data with owner of demo and sys:

SELECT (SELECT COUNT (*) FROM DEMO) CNT, OWNER, COUNT (*) FROM DEMOWHERE OWNER IN ('DEMO',' SYS') GROUP BY OWNER CNT OWNER COUNT (*)-87069 DEMO 44 87069 SYS 37815

There are 87096 rows of records in table demo, of which 44 rows are recorded with owner as demo and 37815 rows with owner as sys.

View the execution plan for the following SQL:

SQL > set autot traceSQL > select / * demo * / * from demo where owner = 'DEMO' 44 rows selected.Execution Plan---Plan hash value: 3014608035 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEMO | 44 | 4312 | 3 (0) | 00 | : 00:01 | | * 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("OWNER" = 'DEMO')

As you can see from the execution plan, the index range scan is used and the cost is 3

SQL > select / * sys * / * from demo where owner = 'SYS' 37815 rows selected.Execution Plan---Plan hash value: 4000794843 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 37815 | 3619K | 347 (1) | 00:00:05 | | * 1 | TABLE ACCESS FULL | DEMO | 37815 | 3619K | 347 (1) | 00:00:05 |- -Predicate Information (identified by operation id):-1-filter ("OWNER" = 'SYS')

As you can see from the execution plan, a full table scan is used with a cost of 347

Update the data in the table, but do not collect statistics:

SQL > update demo set owner = 'DEMO' where object_id

Commit;Commit complete queries again: SQL > select / * demo * / * from demo where owner = 'DEMO' 59703 rows selected.Execution Plan---Plan hash value: 3014608035 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEMO | 44 | 4312 | 3 (0) | 00 | : 00:01 | | * 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("OWNER" = 'DEMO')

As can be seen from the execution plan, the index range scan is used and the cost is 3.

The execution plan at this time is wrong, and the number of rows returned is 59659, which is not suitable for scanning in the index range, but should use a full table scan.

However, because the statistics are not updated, the optimizer still thinks that the data in the table is from the previous statistics, so the previous execution plan is extended.

Statistics need to be re-collected, but if the statistics are collected directly, the SQL related to the table demo will be hard parsed, and the execution plan at this time may not be necessary.

It is efficient, so you need to collect statistics on the table demo, but do not allow these SQL to use statistics.

Use the Pending Statistics method to collect statistics to ensure that other SQL will not use new statistics, and can also judge the execution plan

Whether it's reasonable.

To use Pending Statistics:

1. Set the PUBLISH parameter on table demo to false, and default to true

This parameter indicates whether the collected statistics are stored in the data dictionary (normally) or in a private area.

When true, it stores statistics to a data dictionary and can be used by SQL

When false, indicates that statistics are stored in private areas and cannot be used by SQL unless parameters are set to use these statistics

EXEC DBMS_STATS.SET_TABLE_PREFS ('DEMO',' DEMO', 'PUBLISH','FALSE')

2. Collect the statistical information of table demo

Begin dbms_stats.gather_table_stats (ownname = > 'DEMO', tabname = >' DEMO', estimate_percent = > 100, method_opt = > 'for columns owner size skewonly', no_invalidate = > false Degree = > 1, cascade = > true) End;/

3. Set the parameter of optimizer_use_pending_statistics of the session to true, and default to false

This parameter indicates whether the statistics in the private area are used by the session or the system.

When true, indicates that these statistics are available to the session / system

When false, these statistics cannot be used by the session / system

Alter session set optimizer_use_pending_statistics = true

4. View the execution plan of SQL

5. Operation of statistical information stored in private areas

Method 1: delete these statistics directly, and then collect the statistics normally again

Method 2: publish these statistics directly, which can be used by SQL (there are hidden dangers)

EXEC DBMS_STATS.DELETE_PENDING_STATS ('DEMO',' DEMO')

6. Change the PUBLISH parameter on the setting table demo to true.

EXEC DBMS_STATS.SET_TABLE_PREFS ('DEMO',' DEMO', 'PUBLISH','TRUE')

7. Collect statistical information

Specific steps:

1. Set the PUBLISH parameter on table demo to false, and default to true

SQL > EXEC DBMS_STATS.SET_TABLE_PREFS ('DEMO',' DEMO', 'PUBLISH','FALSE'); PL/SQL procedure successfully completed.

2. Collect the statistical information of table demo

Begin dbms_stats.gather_table_stats (ownname = > 'DEMO', tabname = >' DEMO', estimate_percent = > 100, method_opt = > 'for columns owner size skewonly', no_invalidate = > false Degree = > 1, cascade = > true) End;/

3. Set the parameter of optimizer_use_pending_statistics of the session to true, and default to false

View the execution plan of SQL

SQL > select / * demo * / * from demo where owner = 'DEMO' 59703 rows selected.Execution Plan---Plan hash value: 3014608035 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEMO | 44 | 4312 | 3 (0) | 00 | : 00:01 | | * 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("OWNER" = 'DEMO')

At this point, the previous execution plan is still used, and the index range scan indicates that the newly collected statistics are not used.

Modify the parameters:

SQL > alter session set optimizer_use_pending_statistics = true;Session altered.

4. View the execution plan of SQL

SQL > select / * demo * / * from demo where owner = 'DEMO' 59703 rows selected.Execution Plan---Plan hash value: 4000794843 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 59703 | 5713K | 347 (1) | 00:00:05 | | * 1 | TABLE ACCESS FULL | DEMO | 59703 | 5713K | 347 (1) | 00:00:05 |- -Predicate Information (identified by operation id):-1-filter ("OWNER" = 'DEMO')

At this point, the execution plan has changed to a full table scan, indicating that the newly collected statistics have been used.

5. Operation of statistical information stored in private areas

Here you choose to delete these statistics.

SQL > EXEC DBMS_STATS.DELETE_PENDING_STATS ('DEMO',' DEMO'); PL/SQL procedure successfully completed.

View the execution plan of SQL again:

SQL > select / * demo * / * from demo where owner = 'DEMO' 59703 rows selected.Execution Plan---Plan hash value: 3014608035 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 44 | 4312 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | DEMO | 44 | 4312 | 3 (0) | 00 | : 00:01 | | * 2 | INDEX RANGE SCAN | IDX_OWNER_DEMO | 44 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("OWNER" = 'DEMO')

The execution plan is the initial execution plan, and the index range scan indicates that the old statistics are used and the newly collected statistics are not used.

6. Change the PUBLISH parameter on the setting table demo to true.

SQL > EXEC DBMS_STATS.SET_TABLE_PREFS ('DEMO',' DEMO', 'PUBLISH','TRUE'); PL/SQL procedure successfully completed.

7. Collect statistical information:

Begin dbms_stats.gather_table_stats (ownname = > 'DEMO', tabname = >' DEMO', estimate_percent = > 100, method_opt = > 'for columns owner size skewonly', no_invalidate = > false Degree = > 1, cascade = > true) End;/

View the execution plan of SQL

SQL > select / * demo * / * from demo where owner = 'DEMO' 59703 rows selected.Execution Plan---Plan hash value: 4000794843 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 59703 | 5713K | 347 (1) | 00:00:05 | | * 1 | TABLE ACCESS FULL | DEMO | 59703 | 5713K | 347 (1) | 00:00:05 |- -Predicate Information (identified by operation id):-1-filter ("OWNER" = 'DEMO')

As you can see from the execution plan, full table scans are used and newly collected statistics are used.

Thank you for your reading, the above is the content of "how to collect statistical information does not affect the database". After the study of this article, I believe you have a deeper understanding of how to collect statistical information without affecting the database. The specific use of the situation also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.