sql调优常用脚本

--定位SQL
---查询当前正在执行的SQL
select 
INST_ID
,sid
,serial#
,USERNAME
,STATUS
,MACHINE
,SQL_ID
,EVENT
,(sysdate-LOGON_TIME)*86400 as "s"
,LAST_CALL_ET
 from gv$session where status='ACTIVE' and username is not null;

--会话模式：
alter session set current_schema=&user_name;

--获取SQL的执行计划
set linesize 500
set termout off
alter session set statistics_level = all;
exec -sql 
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('6r5vz5gcm0bb0','','typical'));
select * from table(dbms_xplan.display_awr('6r5vz5gcm0bb0'));

--sql_monitor
set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') as report from dual;

--最消耗时间的执行计划步骤
select 
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event,
count(*) cnt
 from gv$active_session_history 
where sql_id='6r5vz5gcm0bb0' and 
sample_time >to_date('2019-07-29 17:01','yyyy-mm-dd hh24:mi') 
and sample_time <to_date('2019-07-29 18:01','yyyy-mm-dd hh24:mi')
  group by 
inst_id,sql_plan_hash_value,sql_plan_line_id,
sql_plan_operation,sql_plan_options,event
 order by count(*) ;
 
--查询SQL执行时间
select plan_hash_value,instance_number,snap_id,round(elapsed_time_delta/1e6,3) ela,
(select to_char(begin_interval_time,'mm-dd hh24:mi')||'--'||to_char(end_interval_time,'hh24:mi')
  from dba_hist_snapshot where  from dba_hist_sqlstat where sql_id='xx' order by snap_id;
  
--确认表的用户
select owner,table_name from dba_tables where table_name=upper('xx');

--查询表相关的索引列信息
select index_owner,index_name,table_name,column_name,column_position from dba_ind_columns where table_name='xx' order by index_name,column_position;

--查询数据库表的统计信息
select * from dba_tab_col_statistics where table_name='xx' order by column_name;

--GET_DDL
set long 10000 pagesize 100\n
select dbms_metadata.get_ddl('&OBJECT_TYPE','&OBJECT_NAME','&OBJECT_OWNER') ddl_text from dual;