Daily sql optimization commonly used shortcut scripts--by lixora
---- Get the execution plan of the last execution of 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'));
---Get the sql_id of this sql statement:
SELECT SQL_ID, SQL_TEXTFROM V$SQL WHERE SQL_TEXT LIKE '%SELECT e.last_name,%' ;
--- • From AWR: You can see all execution plans
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'));
--- View the row operation with the most overhead :
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' ;
How to get the execution plan of 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
--get sql id:
SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE ' selectcount(t.id) from pr_transfer t%‘；
get sql_id
Substitute sql_id into the following sql to get the execution plan:
Select * from table(dbms_xplan.display_awr('&sql_id',null,null,'ALLSTATS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID',null,'ALLSTATS'));
--- Through the following SQL, you can get historical bind variables :
---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';
Save the execution plan to word or 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');
------ View the execution time of each sql plan of sql
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;