----获取最近一次执行sql 的执行计划
select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'ALLSTATS'));
set linesize 230 pagesize 9999
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED note last'));
/*+ gather_plan_statistics */
select * from table(dbms_xplan.display_cursor(null,null,'allstats note last'));
---获取这个sql语句的sql_id:
SELECT SQL_ID, SQL_TEXTFROM V$SQL WHERE SQL_TEXT LIKE '%SELECT e.last_name,%' ;
---•From AWR:可以看到所有执行计划
Select * from table(dbms_xplan.display_awr('&sql_id',null,null, 'ADVANCED'));
---•From Cursor Cache:
set linesize 230 pagesize 9999
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID',null,'ADVANCED'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID',null,'ADVANCEDnote'));
---查看开销最大的row operation:
alter session set statistics_level=all;
/*+ gather_plan_statistics */
select t.*
from v$sql s
,table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
如何获取一下sql 的执行计划:
selectcount(t.id)
from pr_transfer t
where t.sts = 'Y'
and t.pop_sts in ('Y', 'W')
and t.lastflag = 'Y'
and t.trs_status in ('10', '30')
and t.trs_out_dt isnull
and t.name = :1
and t.live_code = :2
--获取sql id :
SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE ' selectcount(t.id) from pr_transfer t%‘;
得到sql_id
将sql_id 代入到以下sql 中获取执行计划:
Select * from table(dbms_xplan.display_awr('&sql_id',null,null,'ALLSTATS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID',null,'ALLSTATS'));
---通过以下SQL, 可以获取历史绑定变量:
---11g:
select * from ( select snap_id, to_char(sn.begin_interval_time,'MM/DD-HH24:MI')snap_time, sq.sql_id,bm.position,dbms_sqltune.extract_bind(bind_data,bm.position).value_string value_string fromdba_hist_snapshot sn natural join dba_hist_sqlstat sq,dba_hist_sql_bind_metadata bm
where sq.sql_id = bm.sql_id and sq.sql_id ='&sql'
) PIVOT (max(value_string) for position in(1,2,3,4,5,6,7,8,9,10)) order by snap_id;
---10G:
select snap_id,to_char(sn.begin_interval_time,'MM/DD-HH24:MI') snap_time,sq.sql_id,bm.position, dbms_sqltune.extract_bind(bind_data,bm.position).value_stringvalue_string from dba_hist_snapshot sn natural join dba_hist_sqlstat sq,dba_hist_sql_bind_metadata bm
where sq.sql_id = bm.sql_id and sq.sql_id ='&sql';
保留执行计划到word 或者excel 中
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCEDlast'));
execdbms_stats.gather_table_stats(ownname=>'&owner',tabname=>'&tab_name',estimate_percent=>100,cascade=>true);
execute dbms_stats.gather_table_stats
(ownname => 'LUNAR', tabname =>'LUNARTEST1',
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
------查看sql 各个sql plan执行执行时间
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id =TRIM('&&sql_id.')
AND other_xml ISNOT NULL
UNION
SELECT plan_hash_value
FROMdba_hist_sql_plan
WHERE sql_id =TRIM('&&sql_id.')
AND other_xml ISNOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id =TRIM('&&sql_id.')
AND executions> 0
GROUP BY plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROMdba_hist_sqlstat
WHERE sql_id =TRIM('&&sql_id.')
ANDexecutions_total > 0
GROUP BY plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHEREp.plan_hash_value = m.plan_hash_value(+)
ANDp.plan_hash_value = a.plan_hash_value(+)
ORDER BY avg_et_secs NULLS LAST;