聊聊SQL优化的基础思路

数据分析学习社
数据分析学习社
数据分析学习社
348
文章
0
评论
2021-06-1710:48:55 评论 68 7620字
摘要

本文介绍一下基于ADDM与SQL tuning的SQL优化,希望入门学习者能够从中获取一定的收获

SQL优化是Oracle数据库中比较难的部分,需要对Oracle数据库具备非常扎实的理论基础。但是在刚开始接触时,往往不能很好地将理论知识应用到实践,或者有了一定的思路,又不自信或不敢确定是不是正确的。那么如何入门将理论知识转化为实践经验?

  • 使用ADDM定位SQL如果你没有从AWR中定位到需要优化的SQL,可以结合ADDM查看分析。示例如下:

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Finding 1: Top SQL StatementsImpact is 17.86 active sessions, 61.29% of total activity.----------------------------------------------------------SQL statements consuming significant database time were found. Thesestatements offer a good opportunity for performance improvement.   Recommendation 1: SQL Tuning   Estimated benefit is 4.76 active sessions, 16.35% of total activity.   --------------------------------------------------------------------   Action      Run SQL Tuning Advisor on the SELECT statement with SQL_ID      "XXXXXXXXXXX".      Related Object         SQL statement with SQL_ID XXXXXXXXXXX.            Rationale      The SQL spent 99% of its database time on CPU, I/O and Cluster waits.      This part of database time may be improved by the SQL Tuning Advisor.   Rationale      Database time for this SQL was divided as follows: 100% for SQL      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java      execution.   Rationale      SQL statement with SQL_ID "XXXXXXXXXXX" was executed 1094801 times and      had an average elapsed time of 0.015 seconds.   Rationale      I/O and Cluster wait for INDEX "XXXXXX.XXXXXXXX" with      object ID 2133671 consumed 47% of the database time spent on this SQL      statement.XXXXXXXX为出于隐私进行准换。

以上信息描述SQL_ID XXXXXXXXXXX 99%用于CPU,I/O和群集等待已执行1094801次,并且平均执行时间为0.015秒。基于xxxx索引(object ID 2133671)的I/O和群集等待占用数据库时间的47%,建议使用SQL tuning进行优化分析。

  • 使用SQL tuning进行分析

    基于快照之间sql_id优化。

--1、创建任务

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
set autot offset timing offDECLAREmy_task_name VARCHAR2(30);BEGINmy_task_name := dbms_sqltune.create_tuning_task(begin_snap => 22176, --开始快照号end_snap => 22184, --结束快照号sql_id => "2hrbkst309jyj", --sqlidscope => "COMPREHENSIVE", --优化范围(limited或comprehensive)time_limit => 60, --优化过程的时间限制task_name => "tuning_sql_test", --优化任务名称description => "tuning"); --优化任务描述DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => "tuning_sql_test");END;/

--2、执行任务

  •  
exec dbms_sqltune.execute_tuning_task("tuning_sql_test");

--3、查询执行当前状态

  •  
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ="tuning_sql_test";

--4、 查看优化结果

  •  
  •  
  •  
  •  
set long 999999set serveroutput on size 999999set line 120select DBMS_SQLTUNE.REPORT_TUNING_TASK( "tuning_sql_test") from dual;

--5、删除已经存在的优化任务,释放资源

  •  
exec dbms_sqltune.drop_tuning_task("tuning_sql_test");

第4步中查询SQL tuning建议内容如下:

  • 绑定sql profileSQL tuning的第一个建议是绑定推荐的profile,使用并行。但也提示使用parallel可能带来的高资源消耗。最后部分可以看到未使用parallel与使用parallel DB time对比。

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
1- SQL Profile Finding (see explain plans section below)--------------------------------------------------------  A potentially better execution plan was found for this statement.  Recommendation (estimated benefit: 99.13%)  ------------------------------------------  - Consider accepting the recommended SQL profile to use parallel execution    for this statement.    execute dbms_sqltune.accept_sql_profile(task_name => "tuning_sql_test",            task_owner => "SYS", replace => TRUE, profile_type =>            DBMS_SQLTUNE.PX_PROFILE);  Executing this query parallel with DOP 128 will improve its response time  99.13% over the original plan. However, there is some cost in enabling  parallel execution. It will increase the statement"s resource consumption by  an estimated 11.03% which may result in a reduction of system throughput.  Also, because these resources are consumed over a much smaller duration, the  response time of concurrent statements might be negatively impacted if  sufficient hardware capacity is not available.  The following data shows some sampled statistics for this SQL from the past  week and projected weekly values when parallel execution is enabled.                                 Past week sampled statistics for this SQL                                 -----------------------------------------  Number of executions                                                2648  Percent of total activity                                           1.79  Percent of samples with #Active Sessions > 2*CPU                       0  Weekly DB time (in sec)                                        483633.69                              Projected statistics with Parallel Execution                              --------------------------------------------  Weekly DB time (in sec)
  • 建立索引第二个建议是建立索引,可以看到不同的执行计划:Plan hash value: 612724806,现使用执行计划,Time为00:36:55;Plan hash value: 2621731162,使用新的索引后,Time从00:36:55提升为00:05:53;Plan hash value: 3522323416,使用并行后,Time从00:36:55提升为00:00:20。

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
2- Index Finding (see explain plans section below)--------------------------------------------------  The execution plan of this statement can be improved by creating one or more  indices.  Recommendation (estimated benefit: 84.07%)  ------------------------------------------  - Consider running the Access Advisor to improve the physical schema design    or creating the recommended index.    create index XXXXX.IDX$$_5191F0001 on    XxXX.XXXXXXxx(SUBSTR("ESN",-1),"STAT");  Rationale  ---------    Creating the recommended indices significantly improves the execution plan    of this statement. However, it might be preferable to run "Access Advisor"    using a representative SQL workload as opposed to a single statement. This    will allow to get comprehensive index recommendations which takes into    account index maintenance overhead and additional space consumption.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original-----------Plan hash value: 612724806------------------------------------------------------------------------------------------------| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |                           |  8052 |  2665K|   184K  (1)| 00:36:55 ||*  1 |  COUNT STOPKEY     |                           |       |       |            |          ||*  2 |   TABLE ACCESS FULL| xxxxxxxxxx                |  8052 |  2665K|   184K  (1)| 00:36:55 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<=:1)   2 - filter("STAT"="0" AND SUBSTR("ESN",-1)="6" AND "INFO_TYPE"<>"4")2- Using New Indices--------------------Plan hash value: 2621731162----------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                           | 10000 |  3310K| 29383   (1)| 00:05:53 ||*  1 |  COUNT STOPKEY               |                           |       |       |            |          ||*  2 |   TABLE ACCESS BY INDEX ROWID|xxxxxxxxxxxxxxxxxxxxxxxxxxx| 50325 |    16M| 29383   (1)| 00:05:53 ||*  3 |    INDEX RANGE SCAN          | IDX$$_5191F0001           | 46977 |       |   115   (0)| 00:00:02 |----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<=:1)   2 - filter("INFO_TYPE"<>"4")   3 - access("DM_DATAREG_USER_INFO_ZL_T"."qsmmix_VCol_5001"="6" AND "STAT"="0")3- Using Parallel Execution---------------------------Plan hash value: 3522323416---------------------------------------------------------------------------------------------------------------------------------| Id  | Operation              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | TQ  |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |                           |  8052 |  2665K|  1601   (0)| 00:00:20 |        |      |            ||*  1 |  COUNT STOPKEY         |                           |       |       |            |          |        |  |            ||   2 |   PX COORDINATOR       |                           |       |       |            |          |     |      |            ||   3 |    PX SEND QC (RANDOM) | :TQ10000                  |  8052 |  2665K|  1601   (0)| 00:00:20 |  Q1,00 | P->S | QC (RAND)  ||*  4 |     COUNT STOPKEY      |                           |       |       |     |          |  Q1,00 | PCWC |            ||   5 |      PX BLOCK ITERATOR |                           |  8052 |  2665K|  1601   (0)| 00:00:20 |  Q1,00 | PCWC |            ||*  6 |       TABLE ACCESS FULL| xxxxxxxxxxxxxxx           |  8052 |  2665K|  1601   (0)| 00:00:20 |  Q1,00 | PCWP |            |---------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(ROWNUM<=:1)   4 - filter(ROWNUM<=:1)   6 - filter("STAT"="0" AND SUBSTR("ESN",-1)="6" AND "INFO_TYPE"<>"4")

通过以上信息,可以对SQL的优化方向以及优化后的带来的效益和资源有了一定的了解,并根据优化思路反推思考为什么如此做。日积月累之下,相信大家都能够对SQL优化有自己的理解。

 

End.

作者:王茂材

转载如果涉及作品问题请联们第一时间删除(微信lovedata0520

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: