oracle執行計畫走偏處理步驟

標籤：步驟   advance   .exe   執行計畫   des   table   output   ted   order   

-- sql執行時間
select a.EXECUTIONS,a.ELAPSED_TIME,a.ELAPSED_TIME/a.EXECUTIONS/1000/1000 as 秒,a.SQL_ID,a.HASH_VALUE,a.address,a.SQL_TEXT from gv$session b,gv$sql a where a.hash_value=b.SQL_HASH_VALUE and username in (‘ZLCFTPUB‘,‘ZLCFTUSER‘) and status=‘ACTIVE‘;
0nx7fbv1w5xg2
0nx7fbv1w5xg2

select sql_id,a.* from gv$session a where username in(‘ZLCFTPUB‘,‘ZLCFTUSER‘) and status=‘ACTIVE‘;
-- select ‘alter system kill session ‘‘‘||sid||‘,‘||serial#||‘‘‘;‘ from gv$session where username=‘ZLCFTPUB‘ and status=‘ACTIVE‘

select * from gv$sql a where a.PARSING_SCHEMA_NAME in(‘ZLCFTPUB‘,‘ZLCFTUSER‘);

-- 執行計畫解析
select * from table(dbms_xplan.display_cursor(‘0nx7fbv1w5xg2‘,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘)) --where PLAN_TABLE_OUTPUT like ‘%FULL%‘;
select * from table(dbms_xplan.display_cursor(‘0nx7fbv1w5xg2‘));
select * from table(dbms_xplan.display_awr(‘0nx7fbv1w5xg2‘));

3janh23tkjac4
a77srfk7ybrn8

-- sql語句
select * from v$sql where sql_id=‘0nx7fbv1w5xg2‘

-- 預存程序
SELECT OBJECT_NAME,CREATED,LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_TYPE =‘PROCEDURE‘ order by last_ddl_time desc;

-- 最新執行的sql
select sql_text,last_load_time from v$sql order by last_load_time desc;

 

oracle執行計畫走偏處理步驟