sql调优脚本

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;

 

sql调优脚本

上一篇:leveldb.net对象读写封装


下一篇:基于Berkeley DB实现的持久化队列