In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-04 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.