Oracle SQL tuning useful sql statements

1. View full sql text

SELECT p.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. View table and lock related information

SELECT distinct
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.spid as 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.username in ( 'J1_DW' , 'J1_LDCX' , 'J1_LDM' , 'J1_G3_ZBQ' )
--and machine='fjstj1ap01'
--and s.inst_id = '1';
order by o.object_name,s.username;

3. View the real execution plan

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. Perform sql tuning(Sql Tuning Advisor)

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_ZDSNYZRKHXS' ,
description => 'taskto tune a query' );
dbms_output.put_line(lv_task_name);
DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_DM2_ZS_ZDSNYZRKHXS' );
end ;
- View report_tuning_task
select dbms_sqltune.report_tuning_task( 'sql_tuning_DM2_ZS_ZDSNYZRKHXS' ) from dual;
- Use sql profile
begin
dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_DM2_ZS_ZDSNYZRKHXS' ,
task_owner=> 'J1_DW' ,
replace     => TRUE );
END ;

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on Oracle.

Thanks for reading this.