About the author.

Zeng Lingjun, a technical expert of Cloud and Enmo, started to contact ORACLE database in 2009, with 8 years of database operation and maintenance experience. Quick thinker with expertise in database development, troubleshooting tough database problems and performance issues. He has served many customers in South China, and has participated in the deployment and construction of core business systems and data warehouses and production operation and maintenance of many domestic joint-stock banks and city commercial banks, and has accumulated rich experience in database troubleshooting, operation and maintenance monitoring, and performance optimization.

Problem phenomena

Customer feedback in the core business system, there are multiple Sql execution efficiency is very low, the execution plan went to the wrong index, the reason is that the statistics information is not correct, after the statistics information collection and display authorization (equivalent to the collection of statistics information set noinvalidation=false option, the original cursor in the shared pool will be invalid), the execution plan is not changed in time. After a while it returns to normal.

Points of confusion.

1) Why did the execution plan not change immediately after collecting statistics and displaying authorization for the table of the SQL query

2) Why the task of collecting statistical information on a regular daily basis is not working

Analysis of the problem

1. Based on the sql_id provided by the customer, the execution plan for that sql_id is analyzed and goes to the wrong index.

The h_kns_tran_fund table has 100 million+ record counts and the fields in the sql condition should obviously go to the pk_h_kns_tran_fund index, but the CBO optimizer chooses the index idx_h_kns_tran_fund_acctno, which causes a partitioned full scan. Usually this is caused by incorrect stats, check the stats and something is indeed wrong. So it was collected manually, but it took an hour or so for the execution plan to return to normal.

In response to this question.

Checking the business program, I found that there is an if else judgment in the program that does not check the table h_kns_tran_fund if the date passed in is the current day, but only if it is a historical date. Examining the dba_hist_sql_bind_capture view with this clue reveals that the sql is executed very infrequently, only once every hour or two, so the reason the execution plan has not changed can be concluded to be that the SQL has not been executed after the original execution plan has failed and no new SQL parsing has occurred to produce a new and correct execution plan.

2. Why is the table not collecting statistics when the system has turned on the automatic daily task of collecting statistics?

Checking the table structure, I found that this is a partitioned table, one partition per day (e.g. p20160428), and confirmed with the developer that the business data for that day is recorded in the kns_tran_fund table, and this data is inserted by the business process into the pever partition of the history table h_kns_tran_fund every night, and then the pever is split after the data is inserted, and a partition for the previous day is split (e.g. p20160429 and pever), which means that after p20160429 is split out on that day, the data will not change again.

The principle of automatic collection of oracle 10g statistics is to see if the amount of data change exceeds 10% , this amount of change can be checked in the dba_tab_modifications view, if this threshold is reached, the timed task that collects statistics will collect statistics for this table when it runs.

For example.

Insert 100,000 records into the pever partition and manually execute dbms_stats. flush_database_monitoring_info (), the dba_tab_modifications view will then have an additional record for the pever partition with 100,000 inserts.

The problem with this case is that.

The data is inserted into the history table, and then a p20160429 partition is SPLITTED. p20160429 This partition has 100,000 records at this point, but the data is no longer changing, so the dba_tab_modifications view will not have the change records for this new partition, and the statistics collection does not collect this partition.

Knowledge: the dba_tab_modifications view gets its data from mon_mods_all$, and the database backend has a task to refresh the object's dml statistics to mon_mods_all$. This refresh cycle is 15 minutes at 9i and becomes a day after 10g.

Based on the above speculation, let's do an experiment to verify.

Scene 1.

Normal insertion of partition data for comparison

--At this point, you can see the changes to table and partition 0502 recorded in dba_tab_modifications.

--Check the statistics of the partition once, it has not been updated

select table_name,partition_name,num_rows,blocks from user_tab_partitions where table_name='TEST_PART';

--Execute program_action for automatic statistics collection once and the statistics for partition 0502 are updated.

begin dbms_stats.gather_database_stats_job_proc; end;

Conclusion.

normal data insertion operations, the amount of data change reaches the proportion of statistical information collected that the automated task is able to collect

Scenario two.

Insert data into the largest partition, then SPLIT the new partition and observe the collection of statistics on the new partition

--Here we see that only the table and partition PEVER changes are recorded in dba_tab_modifications, but not for partition 0503

--Check the statistics of the partition once, here partition 0503 is currently null

select table_name,partition_name,num_rows,blocks from user_tab_partitions where table_name='TEST_PART';

--Execute program_action for automatic statistics collection once, and the statistics for partition 0503 are updated.

begin dbms_stats.gather_database_stats_job_proc; end;

Conclusion.

There is no record of changes to the new partition in the dba_tab_modifications view, but the statistics are still updated because the new partition has empty statistics at this time, and such objects are collected together with the automatic collection. Inadvertently, another point of knowledge was discovered.

Scenario Three.

Insert data into the largest partition again, then SPLIT the new partition

--Execute again. The situation has changed.

--Note that at this point, after partition 0504 is split, the statistics are not empty, but 0

--execute program_action for automatic statistics collection once, and the statistics for partition 0504 are not updated

begin dbms_stats.gather_database_stats_job_proc; end;

But the actual number of records in this partition is 10,000 lines.

--Several subsequent insertions, splits, and collections were done, and it was found that for the later insertions, the partition statistics were 0 and not updated again:.

Conclusion.

There is no change record for the new partition in the dba_tab_modifications view, and the statistics for the new partition are 0. The statistics for this new partition are not collected during automatic collection.

A new question arises here: why is the initial statistic of the partition, after the first split partition, empty, but the statistic of the new partition of the subsequent split partition operation is 0? Through repeated experiments and careful comparative observations, it turns out that the initial statistics of the new partition generated by split inherits the statistics of the source partition of the split operation. For example, if two partitions P1 and P2 are SPLIT from the partition pever, then both P1 and P2 have the same statistics as pever, pever has empty rows and the new partition is empty, and pever has 0 rows and the new partition is 0. A more stealthy design.

With all that said, it's time to wrap it up. What we learned through this case.

SQL optimization is often more effective when you can analyze and understand the logic of the business process

The table or partition has more than 10% data changes that are recorded by the database before the automatic collection task updates the statistics for these objects

When the object's statistics are empty, the automatic collection task updates these objects

When Split partitioning, the statistics may not be updated for the new partition even if there is a large amount of data

When Split partitioning, the initial statistics of the new partition are inherited from the source partition

Recommended>>
1、LuckyFrameV25 official version released
2、How blockchain scam actually lures investors into the game
3、Waymo releases 360degree panoramic video touting the benefits and safety of driverlessness
4、The United States 500G high security server rental is reliable
5、City Quadrant a lovehate report on the imperial capital Magic City in big data

已发送

发送中