Oracle SQL优化器(STA)的使用

原创 2013年12月05日 12:34:24

最近在群里聊天,有朋友笑言STA是现在M的优化“利器”,很是好奇,我也来学学这个工具的用法:

一、模拟测试环境

SQL> create table t_tables as select OWNER,TABLE_NAME,

  2       TABLESPACE_NAME,NUM_ROWS from dba_tables;

Table created.

SQL> create table t_objects as select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
  2       DATA_OBJECT_ID,OBJECT_TYPE,CREATED from dba_objects;

Table created.


SQL> insert into t_tables select * from t_tables;

1529 rows created.

SQL> /

12232 rows created.

SQL> commit;

Commit complete.

SQL> insert into t_objects  select * from t_objects;

49824 rows created.

SQL> /
199296 rows created.

SQL> commit;

Commit complete.

SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from t_objects a, t_tables b where a.object_name=b.table_name;

  COUNT(*)
----------
    218368

Elapsed: 00:00:00.11

Execution Plan
-------------------------------------------------------------------------------------------------------
Plan hash value: 3023195286

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

| Id  | Operation                               | Name      | Rows  | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    83 |   798   (5)| 00:00:10|
|   1 |  SORT AGGREGATE           |           |     1 |    83 |            ||
|*  2 |   HASH JOIN                                            |  1308K|   103M|   798   (5)| 00:00:10|
|   3 |    TABLE ACCESS FULL    | T_TABLES  | 24415 |   405K|    43   (3)| 00:00:01|
|   4 |    TABLE ACCESS FULL    | T_OBJECTS |   500K|    31M|   735   (3)| 00:00:09|
--------------------------------------------------------------------------------

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

   2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3432  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off
SQL> set timing off
SQL>


二、使用工具进行优化


1.创建优化任务
通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程 EXECUTE_TUNING_TASK执行该任务:
SQL>
SQL>
SQL> DECLARE
  2   my_task_name VARCHAR2(30);
  3   my_sqltext   CLOB;
  4  BEGIN
  5   my_sqltext := 'select count(*) from t_tables t1,t_objects t2 where t1.table_name=t2.object_name';
  6   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7           sql_text    => my_sqltext,
  8           user_name   => 'TEST',
  9           scope       => 'COMPREHENSIVE',
 10           time_limit  => 60,
 11           task_name   => 'my_sql_tuning_task',
 12           description => 'Task to tune a query on a specified table');
 13  END;
 14  /

PL/SQL procedure successfully completed.

第二步: 执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的

SQL> BEGIN
  2    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
  3  END;
  4  /

PL/SQL procedure successfully completed.

第三步:检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='my_sql_tuning_task';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task             COMPLETED

SQL>
SQL> select task_name, status from USER_ADVISOR_LOG where task_name='my_sql_tuning_task';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task             COMPLETED


第四步:查看优化器的建议
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。

SQL>
SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task')
FROM   DUAL;SQL> SQL> SQL>   2  

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 12/01/2013 08:03:39
Completed at                      : 12/01/2013 08:03:41
Number of Statistic Findings      : 2
Number of Index Findings          : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : 76u1asv25u8wt
SQL Text   : select count(*) from t_tables t1,t_objects t2 where
             t1.table_name=t2.object_name

-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  Table "TEST"."T_OBJECTS" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
            'T_OBJECTS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

2- Statistics Finding
---------------------
  Table "TEST"."T_TABLES" was not analyzed.

  Recommendation
  --------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>
            'T_TABLES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

3- Index Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
--------------------------------------------------
  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 TEST.IDX$$_00E40001 on TEST.T_TABLES('TABLE_NAME');

  - Consider running the Access Advisor to improve the physical schema design

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
    or creating the recommended index.
    create index TEST.IDX$$_00E40002 on TEST.T_OBJECTS('OBJECT_NAME');

  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.


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3023195286

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     1 |    83 |   798   (5)| 00:00:10 |
|   1 |  SORT AGGREGATE     |           |     1 |    83 |            |          |
|*  2 |   HASH JOIN         |           |  1308K|   103M|   798   (5)| 00:00:10 |
|   3 |    TABLE ACCESS FULL| T_TABLES  | 24415 |   405K|    43   (3)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T_OBJECTS |   500K|    31M|   735   (3)| 00:00:09 |
---------------------------------------------------------------------------------

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

   2 - access("T1"."TABLE_NAME"="T2"."OBJECT_NAME")

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------

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

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |    83 |   106  (20)| 00:00:02 |
|   1 |  SORT AGGREGATE        |                |     1 |    83 |            |          |
|*  2 |   HASH JOIN            |                |  1308K|   103M|   106  (20)| 00:00:02 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
|   3 |    INDEX FAST FULL SCAN| IDX$$_00E40001 | 24415 |   405K|     3   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX$$_00E40002 |   500K|    31M|    83   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("T1"."TABLE_NAME"="T2"."OBJECT_NAME")

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

第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到的语句等等。

第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述:可以通过建立更多的索引来提高性能;然后是建议的具体内容建立相关索引,走INDEX FAST FULL SCAN;最后是相关注意事项:此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。

最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。

5.删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务

exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task');


版权声明:本文为博主原创文章,未经博主允许不得转载。 举报

相关文章推荐

Oracle STA的使用

Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选...
  • hzhvv
  • hzhvv
  • 2016-06-22 16:56
  • 111

SAA-sql access advisor使用(oracle 11.2后版本)

最近使用SAA,发现网上很多博客讲解的其实在oracle 11.2之后的后续版本中使用或多或少都有问题, 要么版本改变语法改变要么不够全,花点时间罗列一下新版本的使用.

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

SQL Profiles-STA使用方法

1、创建优化任务并执行 使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限...

用 SQL Tuning Advisor (STA) 优化SQL语句

转自http://blog.csdn.net/tianlesoftware/article/details/5630888 在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计...

如何用 SQL Tuning Advisor (STA) 优化SQL语句

在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等。在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning...

sql profiles和dbms_sqltune(sta)

1.先通过v$SQL查找SQL_ID var tuning_task varchar2(100);   DECLARE   l_sql_id v$session.sql_id%TYPE;   ...

ORACLE调优概述以及ADDM以及STA辅助生成示例

整理了笔者日常工作中的经验,概述了常见的ORACLE数据库优化的方法,并展示了如何通过ADDM以及STA辅助生成优化意见

SQL Tuning Advisor(SQL调优顾问,STA)

SQL调优化问需要一个或多个SQL语句作为输入,并调用自动优化器执行SQL调优。SQL调优顾问输出是以一种意见或者建议的形式,以及对每一项建议和期望效益的理由。该建议涉及对象的统计收集,新索引的创建,...

Wifi Sta驱动移植与wifi链接使用

开发环境: 开发板:hisi3520D 内核linux3.0.8 交叉编译器:arm-hisiv100nptl-linux-gcc wifi模块:rt5572和mt7601     在介绍wp...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)