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

Editor to share with you how Oracle changes the implementation plan through comments, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Oracle changes the execution plan through comments

One: problem phenomenon

Synchronization delay of T_XXX meter is 1 hour, and synchronization speed of other meters is normal.

It is mainly slow on a delete T_XXX statement during synchronization, and a single execution takes 12 seconds.

Second, the cause of the problem

There is a unique index in T_XXX table, which is very fast in theory.

Check that there is a delete row-level trigger in the T_XXX table, look at the trigger logic, and find that a update statement in the trigger is very slow.

UPDATE CHENJCH.T_CHENJCH_RISK.. where RISK_ID....

Check the execution plan, update statement scan the whole table, the speed is very slow, through the hint to force the primary key index, the speed is very fast

Why don't you take the primary key to execute the plan?

Looking at the T_CHENJCH_RISK table statistics shows that the table has 0 rows of data, but there are actually 2 million rows of data

Due to a large number of delete/update/insert operations in the T_CHENJCH_RISK table during data synchronization, there was no data in this table when statistics were collected last time, but after several days of data synchronization, the amount of data in the table changed greatly, and the statistics were not collected in real time, resulting in a poor execution plan.

Solution:

Try to delete the T_CHENJCH_RISK table statistics to allow the database to dynamically sample real-time receipt information, but the execution plan has not changed, or the full table scan has not been improved.

Begin

Dbms_stats.delete_table_stats (ownname = > 'CHENJCH', tabname = >' Tunable CHENJCHCHCHY RISK')

End

Try to collect T_CHENJCH_RISK table statistics again, let the database sample real-time receipt information through dynamic sampling, but the execution plan has not changed, or take full table scan, the speed is not improved.

Begin

DBMS_STATS.GATHER_TABLE_STATS ('CHENJCH'

'Turing CHENJCHCHESTRISK'

Estimate_percent = > 100

Method_opt = > 'FOR ALL INDEXED COLUMNS'

Degree = > 6

CASCADE = > TRUE)

End

Why has the implementation plan not changed?

(database version Oracle 12.2.0.1.0)

Because there are binding variables in the SQL statement, and the SQL text has not changed, the execution plan has not changed.

You can have the database regenerate the execution plan by adding and removing comments to the table T_CHENJCH_RISK

Comment on column CHENJCH.T_CHENJCH_RISK.RISK_ID is' PK_T_CHENJCH_RISK'

Comment on column CHENJCH.T_CHENJCH_RISK.RISK_ID is''

Looking at the newly generated execution plan, T_CHENJCH_RISK has started to follow the primary key index, and the speed has been significantly improved.

The above is all the content of the article "how Oracle changes the execution plan through comments". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.