How to use STA(sql tuning advisor)

it2025-06-24  10

一、手工生成Sql tuning advisor  1、SQL text format: DECLARE   my_task_name VARCHAR2(30);   my_sqltext   CLOB; BEGIN   my_sqltext := 'SELECT * FROM DBA_SEGMENTS WHERE OWNER=''CLIC'' AND SEGMENT_TYPE=''TABLE''';   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,                                                   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; / 2、SQL id format: DECLARE   my_task_name VARCHAR2(30);   my_sqltext   CLOB; BEGIN   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'b3uaak09jfaxc',                                                   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; / 二、查看生成的STAreport: set long 999999 set LONGCHUNKSIZE 999999 set serveroutput on size 999999 set linesize 200 select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual; exec dbms_sqltune.drop_tuning_task('test_sql_tuning_task1'); 删除优化任务 SQL> execdbms_sqltune.drop_tuning_task(task_name => 'li_sql_1'); 三、accept sql profile 接受建议的 SQL 概要文件,即创建SQL_Profle SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task1',task_owner =>'SYS', replace => TRUE);   查看创建起来的SQL_Profile信息 SQL>select a.name,a.task_id,a.createdfrom dba_sql_profiles a,dba_advisor_log bwhere a.task_id=b.task_idand b.task_name='test_sql_tuning_task1'; 删除SQL_Profile SQL>exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_01411bdf99410002');

转载于:https://www.cnblogs.com/bhlsheji/p/5160151.html

相关资源:数据结构—成绩单生成器
最新回复(0)