暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
sql_plan
emcs
70次下载
1058次浏览
2019-07-24
4.5

脚本内容

--explain查看SQL执行计划 EXPLAIN PLAN FOR select count(*) from steven.AWEN_OGG_TEST; select * from table(dbms_xplan.display()); --查看AWR和CURSOR中的执行计划 select * from table(dbms_xplan.display_awr('&sqlid')); select * from table(dbms_xplan.display_cursor('&sqlid')); --查看内存中的执行计划 select '| Operation |Object Name | Rows | Bytes| Cost |' as "Explain Plan in library cache:" from dual union all select rpad('| '||substr(lpad(' ',1*(depth-1))||operation|| decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'|| rpad(decode(id, 0, '----------------------------', substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name) ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan" from v$sql_plan sp where sp.hash_value=&hash_value or sp.sql_id='&sqlid'; --查看历史执行计划 select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan where SQL_ID='&sqlid' order by TIMESTAMP;

评论

贡献排行榜