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

This article mainly shows you the "production SQL statement suddenly slow down the problem of example analysis", the content is easy to understand, clear, hope to help you solve the doubt, the following let Xiaobian lead you to study and learn "production SQL statement suddenly slow down the problem of example analysis" this article.

In the customer production environment, suddenly a SQL statement is too slow to run the result. The following is a record of the diagnosis process:

1. Locate sql_id:

Select sql_id from v$sql where sql_text like'% xxx%'

Sql_id

564s6g59axuk4'

2. In the statistical AWR view, the execution efficiency of this statement:

Set linesize 155

Col execs for 999999999

Col avg_etime for 999999.999

Col avg_lio for 999999999.9

Col begin_interval_time for a30

Col node for 99999

Break on plan_hash_value on startup_time skip 1

Select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value

Nvl (executions_delta,0) execs

(elapsed_time_delta/decode (nvl (executions_delta,0), 0meme 1 execution delta)) / 1000000 avg_etime

(buffer_gets_delta/decode (nvl (buffer_gets_delta,0), 0meme 1 execution delta)) avg_lio

From DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

Where sql_id = nvl ('& sql_id','564s6g59axuk4')

And ss.snap_id = S.snap_id

And ss.instance_number = S.instance_number

And executions_delta > 0

Order by 1, 2, 3

/

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO

-

20001 1 26-October-17 08.00.30.657 AM 564s6g59axuk4 2005782661 30 14.1517821 1865567.83

20025 1 27-October-17 08.00.20.698 AM 564s6g59axuk4 2005782661 27 13.3753969 1844812.18

20049 1 28-October-17 08.00.49.876 AM 564s6g59axuk4 3677205750 24 11.9541753 1659475.33

20050 1 28-October-17 09.00.03.143 AM 564s6g59axuk4 3677205750 3 13.4740316 2533988.33

20073 1 29-October-17 08.00.19.029 AM 564s6g59axuk4 3677205750 21 8.46142976 1441061.47

20097 1 30-October-17 08.00.09.581 AM 564s6g59axuk4 2810744384 21 9.88548957 1340974.47

20121 1 31-October-17 08.00.14.292 AM 564s6g59axuk4 2810744384 24 9.11253825 1414630.87

20145 January-November-17 08.00.43.216 AM 564s6g59axuk4 2005782661 21 10.182155 1393004.14

20169 1 02-November-17 08.00.09.892 AM 564s6g59axuk4 342558915 9 280.462698 16771588.3

20173 1 02-November-17 12.00.24.738 afternoon 564s6g59axuk4 2005782661 3 19.7334523 3270556

20174 1 02-November-17 01.00.28.307 afternoon 564s6g59axuk4 2005782661 9 12.2578504 1799912.11

20193 1 03-November-17 08.00.38.295 AM 564s6g59axuk4 342558915 9 244.750394 12790174.7

20199 1 03-November-17 02.00.09.612 afternoon 564s6g59axuk4 342558915 1 3515.82643 178237676

20200 1 03-November-17 03.00.03.620 afternoon 564s6g59axuk4 2797223102 1 1660.86502 89454616

As you can see from the report, there are some errors in the execution plan. View the wrong execution plan:

3. Count the database execution plan

Select distinct SQL_ID,PLAN_HASH_VALUE,to_char (TIMESTAMP,'yyyymmdd hh34:mi:ss') TIMESTAMP

From dba_hist_sql_plan

Where SQL_ID='564s6g59axuk4' order by TIMESTAMP

SQL_ID PLAN_HASH_VALUE TIMESTAMP

564s6g59axuk4 2005782661 20170714 11:30:09

564s6g59axuk4 3677205750 20170715 08:16:24

564s6g59axuk4 2810744384 20171030 08:18:15

564s6g59axuk4 342558915 20171102 08:18:12

564s6g59axuk4 2797223102 20171103 15:07:06

4. Execution plan of incorrect SQL:

Col options for a30

Col operation for a40

Col object_name for a20

Select plan_hash_value,id,LPAD (', 2 * (depth-1)) | | OPERATION | |''| | DECODE (ID, 0, 'Cost =' | | POSITION) "OPERATION"

Options,object_name,CARDINALITY,cost,to_char (TIMESTAMP,'yyyymmdd hh34:mi:ss') TIMESTAMP

From DBA_HIST_SQL_PLAN

Where sql_id = '564s6g59axuk4'

And plan_hash_value='342558915'

Order by ID,TIMESTAMP

In the analysis execution plan, the wrong index is selected and the channel date is used for NL, resulting in inefficient execution.

The above is all the contents of this article entitled "example Analysis of the problem of sudden slowdowns in production SQL statements". 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.