本文主要是介绍oracle sql tuning之非常实用的4条sql语句，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
--[1]查看full sql text
SELECTp.sql_fulltext
FROM gv$locked_object l, gv$session s, gv$sqlarea p
WHERE l.session_id = s.sid
and s.sql_id = p.sql_id;
--[2]查看表和锁的相关信息
SELECTdistinct
s.username,
s.inst_id,
o.object_name,
s.wait_class,
'alter system kill session ''' || s.sid ||',' ||s.serial# ||
''' immediate;'as sql_kill_session,
'ps -ef | grep ' || p.spid ||' | grep -v grep'as shell_grep_proc,
'kill -9 ' || p.spidas shell_kill_proc,
s.blocking_session,
s.seconds_in_wait,
machine,
p.program,
s.sql_id
FROM gv$locked_object l, dba_objects o, gv$session s,gv$process p
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
and s.paddr = p.addr
and s.usernamein ('J1_DW','J1_LDCX','J1_LDM','J1_G3_ZBQ')
--and machine='fjstj1ap01'
--and s.inst_id = '1';
orderby o.object_name,s.username;
--[3]查看真实的执行计划
declare
type array_table is table of varchar2(4000 char) index by binary_integer;
lvc_array array_table;
i number;
begin
dbms_output.enable(buffer_size => null);
execute immediate 'select *
from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''1s87w0c9x0z5g'', 0, ''ADVANCED''))' bulk
collect
into lvc_array;
for i in 1 .. lvc_array.count loop
dbms_output.put_line(lvc_array(i));
end loop;
end;
--[4]进行sql tuning
declare
lv_task_name varchar2(30);
begin
--select instance_name,instance_numberfrom v$instance;
lv_task_name :=DBMS_SQLTUNE.create_tuning_task(sql_id =>'ggudmy1v4muuz',
scope =>'comprehensive',
time_limit =>'60',
task_name =>'sql_tuning_DM2_ZS_ZDSYQYRKHXS',
description =>'taskto tune a query');
dbms_output.put_line(lv_task_name);
DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_DM2_ZS_ZDSYQYRKHXS');
end;
--查看report_tuning_task
selectdbms_sqltune.report_tuning_task('sql_tuning_DM2_ZS_ZDSYQYRKHXS')fromdual;
--使用sql profile
begin
dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_DM2_ZS_ZDSYQYRKHXS',
task_owner=> 'J1_DW',
replace =>TRUE);
END;
这篇关于oracle sql tuning之非常实用的4条sql语句的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
