Oracle performance Optimization-SQL Optimization (case 1)

Oracle performance Optimization-SQL Optimization (case 1)

Environment:

OS:Red Hat Enterprise Linux AS release 4

DB:Oracle 10.2.0.1.0

Problem phenomenon:

The customer reports that the operation of ERP system is slow and the server IO load is high.

The cause of the problem:

(1) the execution efficiency of individual SQL is low. Due to the unreasonable SQL execution plan, there are more logical and physical reads in the process of SQL execution, resulting in high server IO load and slow ERP operation.

(2) the JOB execution time for collecting statistics is 9:13 working time, which results in resource contention with normal business.

Solution:

(1) optimize SQL execution efficiency, reduce logical and physical reads generated during SQL execution, and reduce server IO load

Methods:

Statistical histogram of non-uniform columns checkman and senderman for collecting data

(2) it is recommended to change the JOB execution time for collecting statistics to non-working hours.

Problem analysis process:

You can see that the IO load is high through the Oracle AWR report or the iostat command:

Find out why the IO load is high:

From the AWR report, we can see that the SQL that consumes too much resources mainly comes from the following two sources:

1: SQL corresponding to collaborative credential operation

II: JOB for automatic collection of statistics

DECLARE job BINARY_INTEGER: =: job; next_date DATE: =: mydate; broken BOOLEAN: = FALSE; BEGIN ANALYZE_TB;: mydate: = next_date; IF broken THEN: B: = 1; ELSE: B: = 0; END IF; END

View SQL through AWR

View SQL through PLSQL

View SQL through NMC

SQL Optimization:

Through binding variable value and SQL execution plan, it is analyzed that the execution plan produced by SQL is unreasonable. The pub_workflownote table does not go through the index, but the whole table scan.

Check the data distribution and find that the senderman and checkman columns are unevenly distributed

Collect statistical histograms for senderman and checkman columns

Looking at the SQL execution plan again, it is found that the pub_workflownote table can walk the index normally.

After the statistical histogram is generated, the first bound variable snooping is carried out, which will generate the first correct execution plan with parameter values.

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