一次oracle调优的相关经历

mhy119 · 发表于 2011-8-17 11:31

该死的10000字限定，只能上传文件啦。

mhy119 · 发表于 2011-8-17 11:33

欢迎大家多提意见，多多指教。

outdo · 发表于 2011-8-17 11:56

鼓励原创，好像就是统计信息不及时更想，导致错误的执行计划。

outdo · 发表于 2011-8-17 12:09

lz的两个脚本看上去比较有意思，能否传上来。

mhy119 · 发表于 2011-8-17 12:28

col MESSAGE format a40;
  col username format a10;
  set linesize 350;
  set pagesize 50;
  col program format a30;

  select * from (  select sid,USERNAME,MESSAGE,ELAPSED_SECONDS, TIME_REMAINING from v$session_longops  where  TIME_REMAINING is not null order by ELAPSED_SECONDS desc ) where rownum<10;
  --prompt ===============================sql catched by sum ELAPSED_SECONDS====================;
  select * from (  select sid,USERNAME,MESSAGE,ELAPSED_SECONDS, TIME_REMAINING from v$session_longops  where  TIME_REMAINING is not null order by TIME_REMAINING desc ) where rownum<10;
prompt ================================================================================
prompt =============================get top file scattered read objects===================;
select * from (select target ,count(*) from v$session_longops group by target order by 2 desc) where rownum<10;
truncate table merch.session_longops_temp ;
insert into session_longops_temp select * from v$session_longops where  TIME_REMAINING>60;
commit;
select a.sid,a.username,a.program ,b.TIME_REMAINING from v$session a ,session_longops_temp b where a.sid=b.sid order by 4 desc;

注意session_longops_temp表是自建，为提高v$session_longops 的查询效率。

mhy119 · 发表于 2011-8-17 12:31

accept sid prompt 'Enter sid [%] :'
set linesize 180;
col object_name format a30;
col description format a50;
col sessions  format a30;
col program format a20;
col message format a40;
col SCHEMA_USER format a10;
col what format a50;
col INTERVAL format a20;
col usermame format a10;
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select username,osuser,process,terminal,machine,status,program from v$session where sid=&sid;
select sid,job from dba_jobs_running where sid=&sid;
select JOB,SCHEMA_USER,what,LAST_SEC,INTERVAL from dba_jobs where job in (select job from dba_jobs_running where sid=&sid);
select /*+ ordered */ c.sid, d.sql_id,d.hash_value,d.sql_text from sys.v_$session c, sys.v_$sqltext d where c.sid in (&sid) and c.sql_address = d.address and c.sql_hash_value = d.hash_value order by  c.sid, d.piece;
select sid,USERNAME,MESSAGE,ELAPSED_SECONDS, TIME_REMAINING from v$session_longops  where  TIME_REMAINING is not null and sid=&sid;
select a.sid||','||a.serial# sessions ,a.username,substr(a.program,1,15) program ,c.object_name  from v$session a ,v$locked_object b ,dba_objects c  where a.sid=&sid and a.sid=b.session_id and b.object_id=c.object_id order by 1;

select t.*
from v$sql s
, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALLSTATS LAST')) t where s.sql_id  in (select  distinct d.sql_id from sys.v_$session c, sys.v_$sqltext d where c.sid in (&sid) and c.sql_address = d.address and c.sql_hash_value = d.hash_value ) ;

以上数据库版本为10.2.0.4，仅供参考。