sql tuning task和sql profile

最新推荐文章于 2021-04-13 19:53:58 发布
最新推荐文章于 2021-04-13 19:53:58 发布
阅读量261 收藏
点赞数
文章标签： 数据库
sql tuning task和sql profile的关系
如果希望oracle对指定sql提出优化建议，需要如下步骤：
1 找出sql的sql_id
2 利用sql_id创建sql tuning task
3 执行sql tuning task
4 查看sql tuning task报告

接下来会有两种情况：
1 如果是缺少统计信息，或者oracle的建议是创建索引只来的，则需要手工按照建议操作。
2 如果oracle发现sql语句本身写法有问题导致执行计划不合理，则会创建一个“sql profile”，建议用户接受该profile。并且给出使用profile前后执行计划的对比。

如果用户接受了sql profile，则不需要修改sql语句，oracle会在适当的时候选择优化后的执行计划。

SQL TUNING TASK

创建测试数据
SQL> select count(*) from big;

  COUNT(*)
----------
   4238336

SQL> select count(*) from small;

  COUNT(*)
----------
      4140



执行sql查询
select * from big b, small s where s.object_id = b.object_id and b.status = ' INVALID'

寻找该sql的sql_id
SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from big b, small s where s.object_id = b.object_id and b.status = ''INVALID''';

SQL_ID           SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
gb35pax1n9t9w select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'

利用sql_id查看该sql执行时的执行计划
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'gb35pax1n9t9w', null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID     gb35pax1n9t9w, child number 0
-------------------------------------
select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'

Plan hash value: 3626037456

----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |        |        | 14296 (100)|        |
|*  1 |  HASH JOIN        |        |   577 |   144K| 14296   (1)| 00:02:52 |
|*  2 |   TABLE ACCESS FULL| BIG   |   577 | 73856 | 14280   (1)| 00:02:52 |
|   3 |   TABLE ACCESS FULL| SMALL |  4140 |   517K|     16   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."STATUS"='INVALID')

Note
-----
   - dynamic sampling used for this statement


26 rows selected.


利用sql_id创建sql tuning task(记住它生成的task名，或者在创建task的时候执行task_name)
set serverout on
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'gb35pax1n9t9w');
dbms_output.put_line(tuning_task);
end;
  7  /

TASK_12

PL/SQL procedure successfully completed.


执行sql tuning task
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_12');

PL/SQL procedure successfully completed.


查看sql tuning结果
SQL> set long 99999
SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_12') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name            : TASK_13
Tuning Task Owner            : SYS
Scope                      : COMPREHENSIVE
Time Limit(seconds)            : 1800
Completion Status            : COMPLETED
Started at                 : 11/28/2013 14:55:49
Completed at                 : 11/28/2013 14:55:53
Number of Index Findings       : 1

-------------------------------------------------------------------------------
Schema Name: LS
SQL ID        : gb35pax1n9t9w
SQL Text   : select * from big b, small s where s.object_id = b.object_id and
          b.status = 'INVALID'

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

1- 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: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
     create index LS.IDX$$_000D0001 on LS.BIG('STATUS');

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
     create index LS.IDX$$_000D0002 on LS.SMALL('OBJECT_ID');

  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: 3626037456

----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |   166 | 14291   (1)| 00:02:52 |
|*  1 |  HASH JOIN        |        |     1 |   166 | 14291   (1)| 00:02:52 |
|*  2 |   TABLE ACCESS FULL| BIG   |     1 |     83 | 14275   (1)| 00:02:52 |
|   3 |   TABLE ACCESS FULL| SMALL |  4140 |   335K|     16   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."STATUS"='INVALID')

2- Using New Indices
--------------------
Plan hash value: 1262730234

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |   166 |     6     (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | SMALL            |     1 |    83 |     2     (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |     1 |   166 |     6     (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG            |     1 |    83 |     4     (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | IDX$$_000D0001 |     1 |       |     3     (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX$$_000D0002 |     1 |       |     1     (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   4 - access("B"."STATUS"='INVALID')
   5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")

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

报告显示，建议增加两个索引。这样一来，执行计划就会由原来的“1- Original”变成“2- Using New Indices”。
这里创建三个索引测试用
create index idx1 on big(status);
create index idx2 on big(status, object_id);
create index idx3 on big(object_id);


SQL PROFILE

将上面的SQL改写，不让它走索引
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';

打印执行计划
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'bhw1175zgq048', null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID     bhw1175zgq048, child number 0
-------------------------------------
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where
s.object_id = b.object_id and b.status = 'INVALID'

Plan hash value: 566152358

-------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |         |         | 14278 (100)|         |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| SMALL |       1 |     83 |       2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |         |       1 |     166 | 14278   (1)| 00:02:52 |
|*  3 |     TABLE ACCESS FULL         | BIG   |       1 |     83 | 14276   (1)| 00:02:52 |
|*  4 |    INDEX RANGE SCAN         | IDX3  |       1 |         |       1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - filter("B"."STATUS"='INVALID')
   4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")

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


23 rows selected.


创建sql tuning task报告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_task') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name            : my_task
Tuning Task Owner            : SYS
Scope                      : COMPREHENSIVE
Time Limit(seconds)            : 1800
Completion Status            : COMPLETED
Started at                 : 11/28/2013 16:16:37
Completed at                 : 11/28/2013 16:16:38
Number of SQL Profile Findings       : 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID        : bhw1175zgq048
SQL Text   : select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s
          where s.object_id = b.object_id and b.status = 'INVALID'

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

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.85%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
     execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace
         => TRUE);

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     99 | 16434 | 14342   (1)| 00:02:53 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SMALL |       1 |     83 |       2   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
|   2 |   NESTED LOOPS              |         |     99 | 16434 | 14342   (1)| 00:02:53 |
|*  3 |    TABLE ACCESS FULL         | BIG   |     33 |  2739 | 14276   (1)| 00:02:52 |
|*  4 |    INDEX RANGE SCAN         | IDX3  |       1 |         |       1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - filter("B"."STATUS"='INVALID')
   4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2031531658

--------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       99 | 16434 |       21   (5)| 00:00:01 |
|*  1 |  HASH JOIN               |          |       99 | 16434 |       21   (5)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG   |       33 |     2739 |        4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1  |        1 |          |        3   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
|   4 |   TABLE ACCESS FULL          | SMALL |     4140 |     335K|       16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   3 - access("B"."STATUS"='INVALID')

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

报告中也发现执行计划变成全表扫描了，oracle生成了一个sql profile，建议我们执行下面语句来接受sql profile，这样oracle就可以在适当的时候使用优化后的“2- Using SQL Profile”执行计划：
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace => TRUE);


接受profile后，不修改sql，执行计划却发生了变化，说明sql profile起作用了
SQL> set lines 120
SQL> set autot  traceonly
SQL> select /*+ no_index(b idx1) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4150764910

---------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |   166 |     6     (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | SMALL |     1 |    83 |     2     (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |       |     1 |   166 |     6     (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG   |     1 |    83 |     4     (0)| 00:00:01 |
|*  4 |      INDEX RANGE SCAN           |  IDX2  |     1 |       |     3     (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX3  |     1 |       |     1     (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   4 - access("B"."STATUS"='INVALID')
   5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")


Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
     142  consistent gets
       0  physical reads
       0  redo size
       8251  bytes sent via SQL*Net to client
     450  bytes received via SQL*Net from client
       8  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
     99  rows processed


查看tuning任务和执行状态
     select * from dba_advisor_log


查看SQL PROFILE
SQL> SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_SQL_PROFILES;

NAME                      CATEGORY                 SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_SQLPROF_0150a772d1540000   DEFAULT                     select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_i

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26239116/viewspace-1076288/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26239116/viewspace-1076288/

cmbj68996
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Sql profiles-->使用sql tuning生成sql profile
gumengkai的博客
03-23 416
一. sqlprofile理解 sql profile可以为某一sql语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器选择更适合的执行计划。 使用sql profile的目的： a. 锁定或者说是稳定执行计划 b. 在不能修改应用中的sql的情况下使sql语句按照执行的执行计划运行。 相较于outline，sql pr
人工智能自动sql优化工具--SQLTuning for SQL Server
09-10
SQLTuning for SQL Server作为一款强大的SQL优化工具，通过人工智能算法帮助用户自动化地找出性能更优的SQL语句，同时也为数据库管理员提供了学习和提升SQL编写技能的机会。在日常数据库管理和开发中，熟练运用这类...
SQL Tuning Task
csqm87956的博客
07-04 276
1. Creating a SQL Tuning Task DECLAREmy_task_name VARCHAR2(30);my_sqltext CLOB;BEGINmy_task_name := DBMS_SQLTUNE....
SQL Tuning Task功能笔记
congjiao5415的博客
09-11 334
http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php 自动SQL调优建议：统计信息，SQL Profile，访问路径，SQL结构分析 1 创建一个...
oracle11g中SQL优化（SQL TUNING）新特性之SQL Plan Management(SPM)
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
06-30 1823
之前这些Oracle11g的新特性都了解过，但当时都没做归纳和总结，以至随着时间的推移，很多都有点忘记了，这几天正好有时间，重新复习下这些知识点，并对资料和文档做了归纳和总结，记录于此，希望对自己今后的学习和工作有帮助，也希望对各位朋友有所帮助。
Automatic SQL tuning and SQL Profile
有梦就别怕痛
04-24 449
Automatic SQL Tuning process in Oracle1. 察看是否启用SQL TUNING ADVISORselect client_name,status from DBA_AUTOTASK_CLIENT; select name,currently_used,last_usage_date,description from dba_feature_usage_stati...
Oracle 12C SQL Tuning for Developers
05-30
8. **SQL Profile与SQL Plan Baseline**：SQL Profile是优化器为特定SQL语句生成的优化建议，而SQL Plan Baseline则记录了过去成功执行的计划，防止未来出现性能下降。 9. **Index Compression**：12C提供了索引...
Oracle SQL tuning 数据库优化步骤分享(图文教程)
09-10
在Quest公司的Quest Central这款强大的数据库管理解决方案中，SQL Tuning是一个专门针对SQL优化的工具，它能适用于Oracle、DB2和SQL Server等多种数据库。 SQL Tuning for SQL Server首先需要通过Quest Database ...
SQL Tuning.ppt
10-27
"SQL Tuning.ppt"这份资料主要涵盖了SQL查询优化的多个方面，包括了解释计划(Explain Plan)、扩展行源统计(ERSS)、调优通过基数反馈(TCF)、视觉SQL调优(VST)、实时SQL监控(RTSM)以及对象统计、计划变化、提示(hints)...
oracle sqltuning workshop
01-28
在这个"Oracle SQLTuning Workshop"中，虽然资料可能源自2004年，但它依然包含了许多至今仍具价值的基础知识和实践经验。以下是一些核心知识点的详细说明： 1. **SQL基础**：SQL（结构化查询语言）是用于管理和处理...
人工智能自动sql优化工具–SQLTuning for SQL Server
12-15
【SQLTuning for SQL Server】是一款由Quest公司开发的人工智能自动SQL优化工具，它集成在Quest Central软件中，提供了一种高效的方式来管理和优化SQL Server数据库的SQL查询性能。Quest Central是一个多平台的...
SQL Tuning Advisor使用总结
03-14
关于oracle的SQL Tuning Advisor全面的使用介绍，包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set，sql_profile的操作。里面涉及到的内容都有可执行的代码。
sql-tuning-guide.pdf
09-17
Oracle SQL 优化手册是 Oracle 官方发布的一份重要的技术文档，旨在帮助开发者和数据库管理员 tối ưu化 Oracle 数据库的性能。该手册涵盖了 Oracle 数据库的各个方面，包括 SQL 语句优化、索引优化、存储过程优化...
SQL Server 2017 Query Performance Tuning
最新发布
11-30
SQL Server 2017 Query Performance Tuning，SQL Server 2017查询性能调优，不得不读的经典著作，英文原版，最新版
SQL Tuning
08-26
SQL Tuning author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database ...
oracle sqltune,oraclet通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK获取优化建议
weixin_39724382的博客
04-09 315
DBMS_SQLTUNE.EXECUTE_TUNING_TASK1 通过取得来自cursor cache 的sql_id来指定sql语句来建立任务2 sql调优的集合包括的多个语句来建立任务3 单一sql语句的文本来建立任务4 通过用awr中相应的sql_id来取得sql语句建立任务用sql_id获取当sql_id不在v$session a, v$process b, v$sqlarea内，获取到...
Oracle手工调用SQL TUNING调优神器
10-25 428
十年磨一剑，厚积薄发！ 1.网站具有丰富的数据库实战方案，系统运维方案，高价值精品视频教程等； 2.注册会员可以发布自己的文章资源，赚取稿费！详情请访问并关注PC端: https://dbs-service.cn/ 会员QQ群：532272497 手机/微信：13926108245
Oracle调整顾问(SQL Tuning Advisor 与 SQL Access Advisor )
拙言的专栏
12-01 3003
在Oracle数据库出现性能问题时，使用Oracle本身的工具包，给出合理的调优建议是比较省力的做法。  下面两种包的介绍及具体做法。 SQL Tuning Advisor 粒度最小的调整工具. 将一条或多条SQL语句做为输入内容，分析所有访问路径，然后生成改进SQL语句的建议,并提出索引，物化视图和分区方案来提高应用程序的总体性能。 在维护窗口中,Oracle会针对Automatic W
查看oracle自动优化,查看Oracle自动优化建议
weixin_30043999的博客
04-13 135
Oracle运行一段时间后，系统会自动给出一些调优建议，放在dba_advisor_actions视图中，视图的几个主要字段见下：task_name --优化任务名execution_name --执行名object_id --对象IDcommand --使用的命令attr1 --具体命令：如，alter table xxx shri...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值