【原】聊聊SQL優(yōu)化的基礎(chǔ)思路

數(shù)據(jù)和云 2021-05-24

展開(kāi)全文

SQL優(yōu)化是Oracle數(shù)據(jù)庫(kù)中比較難的部分，需要對(duì)Oracle數(shù)據(jù)庫(kù)具備非常扎實(shí)的理論基礎(chǔ)。但是在剛開(kāi)始接觸時(shí)，往往不能很好地將理論知識(shí)應(yīng)用到實(shí)踐，或者有了一定的思路，又不自信或不敢確定是不是正確的。那么如何入門將理論知識(shí)轉(zhuǎn)化為實(shí)踐經(jīng)驗(yàn)？本文介紹一下基于ADDM與SQL tuning的SQL優(yōu)化，希望入門學(xué)習(xí)者能夠從中獲取一定的收獲。

使用ADDM定位SQL
如果你沒(méi)有從AWR中定位到需要優(yōu)化的SQL，可以結(jié)合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為出于隱私進(jìn)行準(zhǔn)換。

以上信息描述SQL_ID XXXXXXXXXXX 99％用于CPU，I/O和群集等待已執(zhí)行1094801次，并且平均執(zhí)行時(shí)間為0.015秒?；趚xxx索引（object ID 2133671）的I/O和群集等待占用數(shù)據(jù)庫(kù)時(shí)間的47％，建議使用SQL tuning進(jìn)行優(yōu)化分析。

使用SQL tuning進(jìn)行分析
基于快照之間sql_id優(yōu)化。

--1、創(chuàng)建任務(wù)

set autot offset timing offDECLAREmy_task_name VARCHAR2(30);BEGINmy_task_name := dbms_sqltune.create_tuning_task(begin_snap => 22176, --開(kāi)始快照號(hào)end_snap => 22184, --結(jié)束快照號(hào)sql_id => '2hrbkst309jyj', --sqlidscope => 'COMPREHENSIVE', --優(yōu)化范圍（limited或comprehensive）time_limit => 60, --優(yōu)化過(guò)程的時(shí)間限制task_name => 'tuning_sql_test', --優(yōu)化任務(wù)名稱description => 'tuning'); --優(yōu)化任務(wù)描述DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');END;/

--2、執(zhí)行任務(wù)

exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

--3、查詢執(zhí)行當(dāng)前狀態(tài)

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

--4、查看優(yōu)化結(jié)果

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

--5、刪除已經(jīng)存在的優(yōu)化任務(wù)，釋放資源

exec dbms_sqltune.drop_tuning_task('tuning_sql_test');

第4步中查詢SQL tuning建議內(nèi)容如下：

綁定sql profile
SQL tuning的第一個(gè)建議是綁定推薦的profile，使用并行。但也提示使用parallel可能帶來(lái)的高資源消耗。最后部分可以看到未使用parallel與使用parallel DB time對(duì)比。

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)

建立索引
第二個(gè)建議是建立索引，可以看到不同的執(zhí)行計(jì)劃：
Plan hash value: 612724806，現(xiàn)使用執(zhí)行計(jì)劃，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')

通過(guò)以上信息，可以對(duì)SQL的優(yōu)化方向以及優(yōu)化后的帶來(lái)的效益和資源有了一定的了解，并根據(jù)優(yōu)化思路反推思考為什么如此做。日積月累之下，相信大家都能夠?qū)QL優(yōu)化有自己的理解。

關(guān)于作者

王茂材，云和恩墨北區(qū)交付團(tuán)隊(duì)技術(shù)顧問(wèn)。從事Oracle DBA工作5年，維護(hù)過(guò)200+ Oracle數(shù)據(jù)庫(kù)，涉及能源、醫(yī)療、體彩、銀行、運(yùn)營(yíng)商等行業(yè)數(shù)據(jù)庫(kù)的維護(hù)和操作。對(duì)Oracle數(shù)據(jù)庫(kù)具備扎實(shí)的理論基礎(chǔ)與豐富的實(shí)踐經(jīng)驗(yàn)，擅長(zhǎng)故障處理、遷移、備份恢復(fù)、SQL優(yōu)化等。

END