大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
网站建设哪家好,找创新互联!专注于网页设计、网站建设、微信开发、成都小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了永仁免费建站欢迎大家使用!
grant advisor to user;
grant select_catalog_role to user; --通过OEM管理必不可少
grant execute on dbms_sqltune to user;
此时分两种情况,一种是在v$sql中存在的SQL,一种是在v$sql中不存在的SQL
跟据SQL_ID取v$sql中bind_data的值
select position, value_string
from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));
select dbms_sqltune.extract_bind(bind_data,1).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||
dbms_sqltune.extract_bind(bind_data, 6).value_string
from sys.wrh$_sqlstat
where sql_id = '1nnrufx6sw8sd'
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from emp where ename=:name and DEPTNO= :deptno';
my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
user_name => 'TEST',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name =>'test_sql_tuning',
description => 'Task to tune a query on emp');
END;
/
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * fromorabpel.cube_scope';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'orabpel',
scope => 'COMPREHENSIVE',
time_limit => 60, --优化限时60s
task_name => 'wxw_sql_tuning_task',
description => 'tune thebad sql');
dbms_sqltune.Execute_tuning_task(task_name =>'TEST_sql_tuning_task');
END;
这种情况通常常用于v$sql中的异常SQL优化
DECLARE
my_task_name VARCHAR2(40);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task(
sql_id => '1hudpukz651tt',
plan_hash_value => NULL,
scope => dbms_sqltune.SCOPE_COMPREHENSIVE,
time_limit => dbms_sqltune.TIME_LIMIT_DEFAULT,
task_name => 'test_sql_tuning_1hudpukz651tt',
description => '1hudpukz651tt');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_1hudpukz651tt');
dbms_output.put_line(my_task_name);
END;
declare
my_task_name varchar2(30);
begin
my_task_name := dbms_sqltune.create_tuning_task(
begin_snap => 24365,
end_snap => 24366,
sql_id =>'d40kghyfbg8sj',
plan_hash_value => null,
scope =>'comprehensive',
time_limit => 60,
task_name =>'wxw_sql_tuning_task',
description => 'tune thebad sql'
);
dbms_sqltune.execute_tuning_task (task_name =>'wxw_sql_tuning_task');
end;
---execute
execdbms_sqltune.execute_tuning_task('TEST_tuning_task');
---checek
SELECT status FROM USER_ADVISOR_TASKS WHERElower(task_name) ='test_tuning_task';
select * from user_advisor_tasks;
---report
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 1000
SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_tuning_task') FROM DUAL;
---drop
execdbms_sqltune.drop_tuning_task('TEST_tuning_task');