ITPub博客

首页 > 数据库 > Oracle > sql优化:使用sql profile优化sql语句

sql优化:使用sql profile优化sql语句

原创 Oracle 作者:OGG-01161 时间:2015-09-02 15:55:55 0 删除 编辑
使用sql profile优化sql语句

对于复杂的SQL语句优化,可以借助sql 自动优化顾问生成sql profile,以下介绍使用sql优化顾问生成sql profile优化sql语句

1.低效的sql语句

需有优化的sql语句 

update DATAUPLOAD a
    set a.d502_1 =
          (select count(1)
            from D502341222
            where INPUT_DATE between
                 to_date('2015-08-30', 'yyyy-mm-dd hh24:mi:ss') and
                  to_date('2015-08-31', 'yyyy-mm-dd hh24:mi:ss'))
    where a.sareacode = '341222';

2.优化前执行计划
查看优化前SQL执行计划
select * from table(dbms_xplan.display_awr('c7y7hxp8gtn48')); 

Plan hash value: 3868656972
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |               |     1 |    11 |   573K  (2)| 01:54:38 |

|   1 |  UPDATE              | DATAUPLOAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN  | PK_DATAUPLOAD |     1 |    11 |     0   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
|   3 |   SORT AGGREGATE     |               |     1 |     8 |            |          |
|*  4 |    FILTER            |               |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| D502341222    |    11 |    88 |   573K  (2)| 01:54:38 |
------------------------------------------------------------------------------------


3.执行自动优化任务

declare
     my_task_name varchar2(30);
begin
     --dbms_sqltune.drop_tuning_task(task_name => 'test_sql_tuning_task');
     my_task_name := dbms_sqltune.create_tuning_task(
          begin_snap      => 23230,   --awr报告开始snap
          end_snap        => 23236,   --awr报告结束snap
          sql_id          => 'c7y7hxp8gtn48',
          plan_hash_value => null,
          scope           => 'comprehensive',
          time_limit      => 600,
          task_name       => 'test_sql_tuning_task',
          description     => 'tune the bad sql'
     );
     dbms_sqltune.execute_tuning_task (task_name => 'test_sql_tuning_task');
  end;
/
eg:
SQL> declare
  2       my_task_name varchar2(30);
  3  begin
  4       --dbms_sqltune.drop_tuning_task(task_name => 'test_sql_tuning_task');
  5       my_task_name := dbms_sqltune.create_tuning_task(
  6            begin_snap      => 23230,
  7            end_snap        => 23236,
  8            sql_id          => 'c7y7hxp8gtn48',
  9            plan_hash_value => null,
 10            scope           => 'comprehensive',
 11            time_limit      => 600,
 12            task_name       => 'test_sql_tuning_task',
 13            description     => 'tune the bad sql'
 14       );
 15       dbms_sqltune.execute_tuning_task (task_name => 'test_sql_tuning_task');
 16    end;
 17  /
PL/SQL procedure successfully completed.

4.查看结果

set long 99999
col comments format a180
select dbms_sqltune.report_tuning_task('test_sql_tuning_task') from dual;
 
eg:
SQL> set long 99999
SQL> col comments format a180
SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_sql_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 600
Completion Status  : COMPLETED
Started at         : 09/02/2015 15:41:19
Completed at       : 09/02/2015 15:43:52

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SJPT
SQL ID     : c7y7hxp8gtn48
SQL Text   : update DATAUPLOAD a set a.d502_1=
                         (select count(1) from D502341222 where INPUT_DATE
             between
                         to_date('2015-08-30','yyyy-mm-dd hh24:mi:ss') and


                         to_date('2015-08-31', 'yyyy-mm-dd hh24:mi:ss'))
                         where a.sareacode='341222'




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
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<=10%)
  ---------------------------------------
  - Consider accepting the recommended SQL profile.


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'test_sql_tuning_task', task_owner => 'SYS', 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
                           -------------  ----------------  ----------


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .000031           .000024      22.58 %
  CPU Time (s):                    .001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                        1                 1          0 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
  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
-------------------------------------------------------------------------------




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3868656972


--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |               |     1 |    11 |   573K  (2)| 01:54:38 |


|   1 |  UPDATE              | DATAUPLOAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN  | PK_DATAUPLOAD |     1 |    11 |     0   (0)| 00:00:01 |


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
|   3 |   SORT AGGREGATE     |               |     1 |     8 |            |          |
|*  4 |    FILTER            |               |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| D502341222    |    11 |    88 |   573K  (2)| 01:54:38 |
------------------------------------------------------------------------------------
--


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


   2 - access("A"."SAREACODE"='341222')
   4 - filter(TO_DATE('2015-08-30','yyyy-mm-dd


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
              hh24:mi:ss')<=TO_DATE('2015-08-31','yyyy-mm-dd hh24:mi:ss'))
   5 - filter("INPUT_DATE">=TO_DATE('2015-08-30','yyyy-mm-dd hh24:mi:ss') AND
              "INPUT_DATE"<=TO_DATE('2015-08-31','yyyy-mm-dd hh24:mi:ss'))


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


---------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost  |
---------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |               |     1 |    11 |     1 |
|   1 |  UPDATE             | DATAUPLOAD    |       |       |       |
|*  2 |   INDEX UNIQUE SCAN | PK_DATAUPLOAD |     1 |    11 |       |
|   3 |   SORT AGGREGATE    |               |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| D502341222    |    11 |    88 |   316K|
---------------------------------------------------------------------


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


   2 - access("A"."SAREACODE"='341222')


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
   4 - filter("INPUT_DATE">=TO_DATE('2015-08-30','yyyy-mm-dd
              hh24:mi:ss') AND "INPUT_DATE"<=TO_DATE('2015-08-31','yyyy-mm-dd
              hh24:mi:ss'))


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

SQL> 


5.接受sql profile
 
execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task', task_owner => 'SYS', replace => TRUE);

eg:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task', task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
SQL> 

6.再次查看执行计划
 
  
---------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost  |
---------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |               |     1 |    11 |     1 |
|   1 |  UPDATE             | DATAUPLOAD    |       |       |       |
|*  2 |   INDEX UNIQUE SCAN | PK_DATAUPLOAD |     1 |    11 |       |
|   3 |   SORT AGGREGATE    |               |     1 |     8 |       |
|*  4 |    TABLE ACCESS FULL| D502341222    |    11 |    88 |   316K|
---------------------------------------------------------------------




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

请登录后发表评论 登录
全部评论

注册时间:2012-08-11

  • 博文量
    55
  • 访问量
    128675