Shulou(Shulou.com)06/01 Report--

Oracle performance Optimization-SQL Optimization (case 4)

Environment:

DB:Oracle 11.2.0.1.0

Question:

ERP payroll node takes a long time to calculate, which takes about 15 minutes.

The cause of the problem:

There are two SQL that execute very slowly.

The first query SQL of WA_CACU_DATA, which executes very quickly in plsql, returns 0 entries. It is suspected that some heap tables are used as temporary tables, resulting in problems in the execution plan. The query SQL speed is significantly improved after manually deleting and locking the statistics of these tables.

The second update WA_CACU_DATA SQL, the first execution is fast, the second execution is slow, the execution plan is unstable, and the speed is normal after disabling cardinality feedback (_ optimizer_use_feedback).

Resolution process:

When the problem reappears, the view is mainly slow in two SQL, one select wa_cacu_data..., the other update.

A time-consuming query SQL is as follows

The implementation plan is as follows:

Solution:

The execution is very fast in plsql, and 0 entries are returned. It is suspected that some heap tables are used as temporary tables, resulting in problems in the execution plan. The query SQL speed is significantly improved after manually deleting and locking the statistics of these tables.

SQL > exec dbms_stats.delete_table_stats ('cjc', 'tbm_period')

SQL > exec dbms_stats.delete_table_stats ('cjc', 'org_adminorg')

SQL > exec dbms_stats.delete_table_stats ('cjc', 'org_hrorg')

SQL > exec dbms_stats.lock_table_stats ('cjc', 'tbm_period')

SQL > exec dbms_stats.lock_table_stats ('cjc', 'org_adminorg')

SQL > exec dbms_stats.lock_table_stats ('cjc', 'org_hrorg')

Second: time-consuming update statements

When fetching the complete sql is executed alone, it is found that the first execution is very fast, and the second execution is very slow, and the first execution plan is different from the second generation, and the second execution plan has "cardinality feedback used for this statement", which is suspected to be related to the cardinality feedback of oracle 11g, resulting in unstable execution plan and low efficiency of SQL execution.

Solution:

After the cardinality feedback is disabled at the session level, SQL is executed manually several times, and the speed is stable and fast.

Alter session set "_ optimizer_use_feedback" = false

Temporary solutions can consider disabling cardinality feedback at the system level, or developing code changes to add hint disabled cardinality feedback at the sql level.

Alter system set "_ optimizer_use_feedback" = false

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.