Sql profiles-->使用sql tuning生成sql profile

最新推荐文章于 2021-04-08 23:48:45 发布

bitko

最新推荐文章于 2021-04-08 23:48:45 发布

阅读量411

点赞数

分类专栏： Sql Tuning

本文链接：https://blog.csdn.net/gumengkai/article/details/65447546

版权

Sql Tuning 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一. sqlprofile理解

sql profile可以为某一sql语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器选择更适合的执行计划。

使用sql profile的目的：

a. 锁定或者说是稳定执行计划

b. 在不能修改应用中的sql的情况下使sql语句按照执行的执行计划运行。

相较于outline，sql profile更容易生成、更改和控制，在对sql语句的支持上也做得更好，适用范围更广。

二. (测试)借助sql tuning advisor生成sql profile

1. 创建测试表，收集统计信息

SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;

表已创建。

SQL> create table t2 as select * from dba_objects;

表已创建。

SQL> create index t2_idx on t2(object_id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');

PL/SQL 过程已成功完成。

2. 执行测试sql，观察执行计划

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   415   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   415   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    70   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 86645 |   930K|   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1484  consistent gets
       1477  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

可以看出，这条sql在两个表上都是全表扫描。在第一个表T1上，有 like '%T1%'这样的条件，导致只能全表扫描，这是ok的。但是第二个表也是全表扫描，为什么呢，重点在于id=1的那一列， oracle优化器评估T1 Like '%T1%'返回的结果为2500行，如果对第二个表采用nested loop+index range scan的方式，oracle评估的成本会高于full table scan+hash join

3. 查看oraclee优化器评估的index range scan+nested loop的成本

SQL> explain plan for select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

19 rows selected.

可以看到，oracle优化器评估的成本为5071,远高于原来的415.

4. 查看index range scan+nested loop实际的物理读

SQL> select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

--------------------------------------------------------------------------------
-------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5071   (1)| 00:
01:01 |

|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5071   (1)| 00:
01:01 |

|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5071   (1)| 00:
01:01 |

|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    70   (0)| 00:
00:01 |

|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        303  consistent gets
        248  physical reads
          0  redo size
       2111  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

加了hint之后，实际的逻辑读只有303，低于原始sql的1484。所以可以得出，由于 oracle优化器过高的估计了T1表经过Like操作过滤后返回的行数，也就过高估计了nest loop的成本，导致最终选择了非最优的执行计划。

5. 使用sql tuning advisor 来尝试优化这条sql

SQL> var tuning_task varchar2(100); 
SQL> DECLARE  
  2        l_sql_id v$session.prev_sql_id%TYPE;  
  3        l_tuning_task VARCHAR2(30);  
  4      BEGIN  
  5        l_sql_id:='4zbqykx89yc8v';  
  6        l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  7        :tuning_task:=l_tuning_task;  
  8        dbms_sqltune.execute_tuning_task(l_tuning_task);  
  9        dbms_output.put_line(l_tuning_task);  
 10      END;  
 11      /

PL/SQL procedure successfully completed.

SQL> print tuning_task;  

TUNING_TASK
--------------------------------------------------------------------------------
TASK_771

查看sql tuning建议

SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_771
Tuning Task Owner  : TEST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 03/23/2017 19:31:14
Completed at       : 03/23/2017 19:31:21

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 79.89%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile. #考虑接受推荐的sql
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_771',
            task_owner => 'TEST', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .089893            .03349      62.74 %
  CPU Time (s):                 .044293           .031795      28.21 %
  User I/O Time (s):            .039801                 0        100 %
  Buffer Gets:                     1484               296      80.05 %
  Physical Read Requests:            19                 0        100 %
  Physical Write Requests:            0                 0
  Physical Read Bytes:           901120                 0        100 %
  Physical Write Bytes:               0                 0
  Rows Processed:                    36                36
  Fetches:                           36                36
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    36 |  1476 |   415   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |    36 |  1476 |   415   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |    36 |  1080 |    70   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 86645 |   930K|   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

2- Using SQL Profile
--------------------
Plan hash value: 1022743391

--------------------------------------------------------------------------------
-------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:
00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:
00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:
00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:
00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

-------------------------------------------------------------------------------

sql tuning advisor找到了理想的执行计划，T1表上经过Like过滤后返回的行数估为36，比较准确。

6. accept sql profile

SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);

PL/SQL procedure successfully completed.

再次查看原sql的执行计划

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

--------------------------------------------------------------------------------
-------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:
00:01 |

|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        305  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

7. sql profile不光适用于一样的sql，对其他类似的sql也能生效

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;

57 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

--------------------------------------------------------------------------------
-------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:
00:02 |

|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:
00:01 |

|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        316  consistent gets
          9  physical reads
          0  redo size
       2932  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         57  rows processed

从执行计划的note信息也可以看出sql采用了刚才的sql profile

8. 查询生成的sql profile

SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; 

NAME                           CATEGORY                        SIGNATURE TYPE    STATUS   FOR
------------------------------ ------------------------------ ---------- ------- -------- ---
SYS_SQLPROF_015afaf3c7f80000   DEFAULT                        3.9607E+18 MANUAL  ENABLED  YES

sql profile的实际上就是一些hints，与outlines没有本质上的区别，只是 sql profle中的hint没有指定sql使用哪个索引，也没有指定表的连接方法和连接顺序。在此例中，sql profile只是指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数，即36/2500，即是告诉优化器,T1经过Like后返回的行数应为评估的36/2500。

所以sql profile不会锁定sql的执行计划，只是提供了更多，更准确的统计信息给优化器。

9. 改变T1表的统计信息，观察原sql的执行计划

SQL> exec dbms_stats.set_table_stats('TEST','T1',numrows=>5000000); 

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3600 |   144K|   452   (9)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  3600 |   144K|   452   (9)| 00:00:06 |
|*  2 |   TABLE ACCESS FULL| T1   |  3600 |   105K|   107  (35)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   | 86645 |   930K|   345   (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

Note
-----
   - SQL profile "SYS_SQLPROF_015afaf3c7f80000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        305  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

这里优化器依然采用了sql profile，但根据新的统计信息评估的返回结果数为3600行。执行计划又变回了full scan+hash join。可以看到，虽然sql profile起作用了，但是并没有锁定执行计划。

bitko

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Sql profiles-->使用sql tuning生成sql profile

一. sqlprofile理解sql profile可以为某一sql语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器选择更适合的执行计划。使用sql profile的目的：a. 锁定或者说是稳定执行计划b. 在不能修改应用中的sql的情况下使sql语句按照执行的执行计划运行。相较于outline，sql pr
复制链接

扫一扫