日常 sql 优化常用快捷脚本--by lixora

----获取最近一次执行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;