【oracle】STA基本使用步骤

最新推荐文章于 2024-05-26 10:00:00 发布

原创最新推荐文章于 2024-05-26 10:00:00 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

60 篇文章

订阅专栏

本文详细介绍了如何使用Oracle的STA工具进行SQL调优，包括创建调优任务、执行任务、查看调优报告以及清理任务的全过程。通过实例展示了如何设置任务参数、执行调优并解读调优结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

STA 使用方法

--- 传入sql_id进行解析
declare
  my_tuning_task varchar2(30);
begin
  my_tuning_task := dbms_sqltune.create_tuning_task(sql_id      => '&sqlid',
                                                    scope       => 'comprehensive',
                                                    task_name   => '123_task',
                                                    description => '123_task_des');
  dbms_sqltune.execute_tuning_task(my_tuning_task);
end;

----查询解析后建议
set head off set linesize 300
  select text
    from (select 1 as id,
                 dbms_lob.substr(dbms_sqltune.report_tuning_task(task_name => '123_task'),3000) as text
            from dual
          union
          select 2 as id,
                 dbms_lob.substr(dbms_sqltune.report_tuning_task(task_name => '123_task'),3000,3000) as text
            from dual
          union
          select 3 as id,
                 dbms_lob.substr(dbms_sqltune.report_tuning_task(task_name => '123_task'),3000,6000) as text
            from dual
          union
          select 4 as id,
                 dbms_lob.substr(dbms_sqltune.report_tuning_task(task_name => '123_task'),3000,9000) as text
            from dual
          union
          select 5 as id,
                 dbms_lob.substr(dbms_sqltune.report_tuning_task(task_name => '123_task'),3000,12000) as text
            from dual
           order by 1) aa;

--- 清理调优任务
exec dbms_sqltune.drop_tuning_task('123_task');