SQL Tuning advisor

最新推荐文章于 2024-11-04 16:18:41 发布

june_liu1

最新推荐文章于 2024-11-04 16:18:41 发布

阅读量326

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/june_liu1/article/details/79494482

Oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何使用PL/SQL过程创建并执行SQL调优任务，包括设置SQL语句、指定任务参数、执行调优任务及查看报告等步骤。

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

GRANT ADVISOR TO WMS;

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(1) as TYPE_NUM, TYPE_DESC from WMS.VW_WMS_OUT_WAREHOUSE_TASK_t t1 WHERE 1 = 1 group by TYPE_DESC';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'WMS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_tuning_task1',
description => 'Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
END;
/

user_advisor_tasks或者dba_advisor_tasks来查看调优任务执行的状况
select task_name,ADVISOR_NAME,STATUS from user_advisor_tasks;

SQL> set long 999999
SQL> set LONGCHUNKSIZE 999999
SQL> set serveroutput on size 999999
SQL> set linesize 200
SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;

SQL> select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;

grant select_catalog_role to user; --通过OEM管理必不可少
grant execute on dbms_sqltune to user;

execute dbms_sqltune.accept_sql_profile(task_name =>'test_sql_tuning_task1',

task_owner => 'WMS', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

--删除
begin
DBMS_SQLTUNE.drop_tuning_task(task_name => 'test_sql_tuning_task1');
end;