oracle sql tuning之非常实用的4条sql语句

本文主要是介绍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');