Oracle----showplan
1 -- ################################################################################ 2 -- # 3 -- # name: showplan.sql v1.0 4 -- # 5 -- # File: showplan.sql 6 -- # Description: Show SQL Plan and performance details 7 -- # Usage: @showplan <SQL_ID> [Plan Hash Value] [Details: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics] 8 -- # Created: 2014-03-12 9 -- # Author: Wei Huang 10 -- # Web Site: www.HelloDBA.com 11 -- #Latest Version: http://www.HelloDBA.com/download/showplan.zip 12 -- # User run as: dba 13 -- # Tested DB: 11gR2 14 -- # Parameters: 1: SQL_ID of SQL to be shown 15 -- # Parameters: 2: Plan Hash Value, if null (Default), will show all plans 16 -- # Parameters: 3: Details to be shown: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics, 17 -- # default is BPDTLWS; + stand for the default options 18 -- # 19 -- # Copyright (c) 2014 Wei Huang 20 -- # 21 -- # History 22 -- # Modified by When Why 23 -- # ----------- ------- ---------------------------------------------------- 24 -- ################################################################################ 25 set autot off verify off feedback off pagesize 50000 lines 2000 long 10000000 longchunksize 10000000 serveroutput on size unlimited format wrapped buffer 99999999 head off 26 set termout off 27 col p1 noprint 28 col p2 new_value 2 noprint 29 col p3 new_value 3 noprint 30 select null p2, null p3 from dual where 1=2; 31 select nvl(upper(decode(upper('&2'),'NULL',null,upper('&2'))),null) p2, nvl(upper(decode(upper('&3'),'NULL','BPDTLWS',upper('&3'))),'BPDTLWS')||decode(instr('&3','+'),0,'','BPDTLWS') p3 from dual; 32 set termout on 33 clear columns 34 col xxx format a2000 35 36 prompt Usage: @showplan <SQL_ID> [Plan Hash Value] [Details: [+](B)inds|SQL (T)ext|(Pee(K)ed Binds|(P)lan|(O)utlines|Pre(D)icate|Plan (L)oading|(W)ait events|(S)tatistics] 37 prompt Description: Show SQL Plan 38 prompt 39 40 set termout off 41 var sqlid varchar2(32); 42 var planHashValue varchar2(32); 43 var showOptions varchar2(32); 44 begin select '&1', decode(upper('&2'),'NULL',null,'&2'), nvl(upper(decode(upper('&3'),'NULL','BPDTLWS',upper('&3'))),'BPDTLWS')||decode(instr('&3','+'),0,'','BPDTLWS') into :sqlid, :planHashValue, :showOptions from dual; end; 45 / 46 set termout on 47 48 with q as (select /*+materialize*/* 49 from (select sql_fulltext from v$sqlarea where sql_id=:sqlid 50 union all 51 select sql_text from dba_hist_sqltext 52 where sql_id=:sqlid and not exists (select 1 from v$sqlarea where sql_id=:sqlid))), 53 p as (select /*+materialize*/* 54 from (select m.SQL_ID,SQL_PLAN_HASH_VALUE PLAN_HASH_VALUE,PLAN_LINE_ID ID,PLAN_PARENT_ID PARENT_ID, 55 PLAN_OPERATION OPERATION,p.OTHER_TAG,PLAN_OPTIONS OPTIONS,PLAN_OBJECT_NAME OBJECT_NAME, 56 PLAN_OBJECT_TYPE OBJECT_TYPE,p.OPTIMIZER,PLAN_COST COST,OUTPUT_ROWS||' rows' CARDINALITY, 57 PHYSICAL_READ_BYTES+PHYSICAL_WRITE_BYTES||'/'||PLAN_BYTES BYTES, 58 p.access_predicates, p.filter_predicates, p.parsing_schema_name 59 from v$sql_plan_monitor m, (select p.SQL_ID, p.PLAN_HASH_VALUE, p.ID, p.CHILD_ADDRESS, p.OTHER_TAG, 60 p.OPTIMIZER, p.access_predicates, p.filter_predicates, q.parsing_schema_name 61 from v$sql_plan p, v$sql q 62 where p.SQL_ID=:sqlid AND (:planHashValue is NULL or p.PLAN_HASH_VALUE=to_number(:planHashValue)) 63 and p.sql_id=q.sql_id(+) and p.CHILD_ADDRESS=q.CHILD_ADDRESS(+) 64 union 65 select p.SQL_ID, p.PLAN_HASH_VALUE, ID, null CHILD_ADDRESS, p.OTHER_TAG, 66 p.OPTIMIZER, access_predicates, p.filter_predicates, q.parsing_schema_name 67 from dba_hist_sql_plan p, dba_hist_sqlstat q 68 where p.SQL_ID=:sqlid AND (:planHashValue is NULL or p.PLAN_HASH_VALUE=to_number(:planHashValue)) 69 and p.sql_id=q.sql_id(+) and p.PLAN_HASH_VALUE=q.PLAN_HASH_VALUE(+) 70 and not exists (select 1 from V$SQL_PLAN p1 71 where p1.SQL_ID=:sqlid AND (:planHashValue is NULL or p1.PLAN_HASH_VALUE=to_number(:planHashValue)))) p 72 where m.SQL_ID=:sqlid AND (:planHashValue is NULL or m.SQL_PLAN_HASH_VALUE=to_number(:planHashValue)) 73 and last_refresh_time = (select max(last_refresh_time) from v$sql_plan_monitor m 74 where m.SQL_ID=:sqlid AND (:planHashValue is NULL or m.SQL_PLAN_HASH_VALUE=to_number(:planHashValue))) 75 and m.SQL_ID=p.SQL_ID(+) and m.SQL_PLAN_HASH_VALUE=p.PLAN_HASH_VALUE(+) and m.PLAN_LINE_ID=p.ID(+) and m.SQL_CHILD_ADDRESS=p.CHILD_ADDRESS(+) 76 union 77 select p.SQL_ID,p.PLAN_HASH_VALUE, p.ID, p.PARENT_ID,p.OPERATION,p.OTHER_TAG,p.OPTIONS,p.OBJECT_NAME, 78 p.OBJECT_TYPE, p.OPTIMIZER,p.COST,''||p.CARDINALITY CARDINALITY,''||p.BYTES BYTES, 79 p.access_predicates, p.filter_predicates, q.parsing_schema_name 80 from V$SQL_PLAN p, v$sql q 81 where p.SQL_ID=:sqlid AND (:planHashValue is NULL or p.PLAN_HASH_VALUE=to_number(:planHashValue)) 82 and p.child_number = (select max(child_number) from V$SQL_PLAN p1 83 where p1.SQL_ID=:sqlid AND (:planHashValue is NULL or p1.PLAN_HASH_VALUE=to_number(:planHashValue))) 84 and not exists (select 1 from v$sql_plan_monitor m 85 where m.SQL_ID=:sqlid AND (:planHashValue is NULL or m.SQL_PLAN_HASH_VALUE=to_number(:planHashValue))) 86 and p.sql_id=q.sql_id(+) and p.CHILD_ADDRESS=q.CHILD_ADDRESS(+) 87 union 88 select p.SQL_ID,p.PLAN_HASH_VALUE, p.ID, p.PARENT_ID,p.OPERATION,p.OTHER_TAG,p.OPTIONS,p.OBJECT_NAME, 89 p.OBJECT_TYPE,p.OPTIMIZER,p.COST,''||p.CARDINALITY CARDINALITY,''||p.BYTES BYTES, 90 p.access_predicates, p.filter_predicates, q.parsing_schema_name 91 from dba_hist_sql_plan p, dba_hist_sqlstat q 92 where p.SQL_ID=:sqlid AND (:planHashValue is NULL or p.PLAN_HASH_VALUE=to_number(:planHashValue)) 93 and timestamp = (select max(timestamp) from dba_hist_sql_plan p1 94 where p1.SQL_ID=:sqlid AND (:planHashValue is NULL or p1.PLAN_HASH_VALUE=to_number(:planHashValue))) 95 and not exists (select 1 from v$sql_plan_monitor m 96 where m.SQL_ID=:sqlid AND (:planHashValue is NULL or m.SQL_PLAN_HASH_VALUE=to_number(:planHashValue))) 97 and not exists (select 1 from V$SQL_PLAN p1 98 where p1.SQL_ID=:sqlid AND (:planHashValue is NULL or p1.PLAN_HASH_VALUE=to_number(:planHashValue))) 99 and p.sql_id=q.sql_id(+) and p.PLAN_HASH_VALUE=q.PLAN_HASH_VALUE(+))), 100 pa as ( select /*+materialize*/sql_plan_hash_value plan_hash_value, sql_plan_line_id, 101 sql_plan_operation||''||nvl(sql_plan_options,'') sql_plan_op,nvl(event, 'ON CPU') event, 102 TEMP_SPACE_ALLOCATED, PGA_ALLOCATED, current_obj#, count(*) over () total_waits 103 from v$active_session_history 104 where sql_plan_line_id is not null and sql_id=:sqlid AND (:planHashValue is NULL or SQL_PLAN_HASH_VALUE=to_number(:planHashValue)) 105 union all 106 select sql_plan_hash_value plan_hash_value, sql_plan_line_id, 107 sql_plan_operation||''||nvl(sql_plan_options,'') sql_plan_op,nvl(event, 'ON CPU') event, 108 TEMP_SPACE_ALLOCATED, PGA_ALLOCATED, current_obj#, count(*) over () total_waits 109 from dba_hist_active_sess_history 110 where not exists (select 1 from v$active_session_history 111 where sql_id=:sqlid AND (:planHashValue is NULL or SQL_PLAN_HASH_VALUE=to_number(:planHashValue))) 112 and sql_plan_line_id is not null and sql_id=:sqlid AND (:planHashValue is NULL or SQL_PLAN_HASH_VALUE=to_number(:planHashValue))), 113 pl as ( select plan_hash_value, sql_plan_line_id, sql_plan_op, total_waits, count(*) waits 114 from pa 115 group by plan_hash_value, sql_plan_line_id, sql_plan_op, total_waits), 116 we as (select pa.plan_hash_value, pa.event, o.owner||'.'||o.object_name||'('||o.object_type||')' wait_object, 117 count(*) waits, total_waits from pa, dba_objects o 118 where pa.current_obj#=o.object_id 119 group by pa.plan_hash_value, pa.event, o.owner, o.object_name, o.object_type, total_waits), 120 pb as (select /*+inline*/plan_hash_value,b.name,b.value, 121 decode(b.type#, 122 1, 'VARCHAR2('||b.maxlength||')', 123 2, decode(b.scale, null, 124 decode(b.precision#, null, 'NUMBER', 'FLOAT'), 125 'NUMBER'), 126 8, 'LONG', 127 9, 'VARCHAR('||b.maxlength||')', 128 12, 'DATE', 129 23, 'RAW', 24, 'LONG RAW', 130 69, 'ROWID', 131 96, 'CHAR('||b.maxlength||')', 132 100, 'BINARY_FLOAT', 133 101, 'BINARY_DOUBLE', 134 105, 'MLSLABEL', 135 106, 'MLSLABEL', 136 112, 'CLOB', 137 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 138 178, 'TIME(' ||b.scale|| ')', 139 179, 'TIME(' ||b.scale|| ')' || ' WITH TIME ZONE', 140 180, 'TIMESTAMP(' ||b.scale|| ')', 141 181, 'TIMESTAMP(' ||b.scale|| ')' || ' WITH TIME ZONE', 142 231, 'TIMESTAMP(' ||b.scale|| ')' || ' WITH LOCAL TIME ZONE', 143 182, 'INTERVAL YEAR(' ||b.precision#||') TO MONTH', 144 183, 'INTERVAL DAY(' ||b.precision#||') TO SECOND(' || 145 b.scale || ')', 146 208, 'UROWID', 147 'UNDEFINED') data_type 148 from v$sql_plan m, xmltable('/*/peeked_binds/bind' passing xmltype(m.OTHER_XML) 149 columns name varchar2(4000) path '/bind/@nam', 150 type# varchar2(4000) path '/bind/@dty', 151 precision# varchar2(4000) path '/bind/@pre', 152 scale varchar2(4000) path '/bind/@scl', 153 maxlength varchar2(4000) path '/bind/@mxl', 154 value varchar2(4000) path '/bind') b 155 where m.sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 156 and trim(OTHER_XML) is not null), 157 mb as (select /*+inline*/m.sid,m.session_serial#,sql_id,b.name,b.data_type,b.value 158 from V$SQL_MONITOR m, xmltable('/binds/bind' passing xmltype(m.BINDS_XML) 159 columns name varchar2(30) path '/bind/@name', 160 data_type varchar2(30) path '/bind/@dtystr', 161 value varchar2(4000) path '/bind') b 162 where m.sql_id = :sqlid 163 and exists (select 1 from V$SQL_MONITOR m1 164 where m1.sid=m.sid and m1.session_serial#=m.session_serial# and m1.sql_id=m.sql_id 165 and (not exists (select 1 from v$sql_plan 166 where sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue))) 167 or exists (select 1 from v$sql_plan p 168 where sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 169 and m1.sql_child_address=p.child_address)) 170 group by sid,session_serial#,sql_id 171 having max(m1.last_refresh_time)=m.last_refresh_time) 172 and m.BINDS_XML is not null), 173 ol as (select /*+inline*/plan_hash_value,b.hint 174 from v$sql_plan m, xmltable('/*/outline_data/hint' passing xmltype(m.OTHER_XML) 175 columns hint varchar2(4000) path '/hint') b 176 where m.sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 177 and trim(OTHER_XML) is not null), 178 bc as ( select distinct name,datatype,datatype_string,value_string from v$sql_bind_capture 179 where sql_id = :sqlid 180 and last_captured = (select max(last_captured) from v$sql_bind_capture c 181 where sql_id = :sqlid 182 and (not exists (select 1 from v$sql_plan 183 where sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue))) 184 or exists (select 1 from v$sql_plan p 185 where sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 186 and c.child_address=p.child_address)))), 187 bc1 as ( select distinct b.name,b.datatype,b.datatype_string,b.value_string,b.snap_id from dba_hist_sqlbind b, dba_hist_snapshot s 188 where b.sql_id = :sqlid and b.snap_id=s.snap_id and b.instance_number=s.instance_number 189 and not exists (select 1 from bc) 190 and b.snap_id = (select max(c.snap_id) from dba_hist_sqlbind c 191 where sql_id = :sqlid)), 192 cb as (select /*+materialize*/* 193 from (select LISTAGG('var '||substr(name,2)||''||decode(datatype_string,'VARCHAR2(4001)','CLOB',datatype_string)||';' ,chr(10)) WITHIN GROUP (ORDER BY name) var, 194 LISTAGG('exec '||name||':='||nvl2(value_string,decode(datatype,1,'''','')||value_string||decode(datatype,1,'''','')||';','null;'),chr(10)) WITHIN GROUP (ORDER BY name) exe 195 from bc 196 union all 197 select LISTAGG('var '||substr(name,2)||''||decode(datatype_string,'VARCHAR2(4001)','CLOB',datatype_string)||';' ,chr(10)) WITHIN GROUP (ORDER BY name) var, 198 LISTAGG('exec '||name||':='||nvl2(value_string,decode(datatype,1,'''','')||value_string||decode(datatype,1,'''','')||';','null;'),chr(10)) WITHIN GROUP (ORDER BY name) exe 199 from bc1 200 group by snap_id) 201 where (var is not null or exe is not null)), 202 sd as (select PLAN_HASH_VALUE, '1,Loads: '||q.LOADS||'; 2,Load Versions: '||q.LOADED_VERSIONS||'; 3,First Load Time: '||q.FIRST_LOAD_TIME||'; 4,Last Load Time: '||q.LAST_LOAD_TIME||'; 5,User Openings: '||q.USERS_OPENING||'; 6,Parse Calls: '||q.PARSE_CALLS||'; 7,Executions: '||q.EXECUTIONS||'; 8,Sorts(Average): '||round(q.SORTS/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||'; 9,Fetches(Average): '||round(q.FETCHES/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||'; 10,Disk Reads(Average): '||round(q.DISK_READS/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||'; 11,Buffer Gets(Average): '||round(q.BUFFER_GETS/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||'; 12,Elapsed Time(Average): '||ROUND(q.ELAPSED_TIME/1000/1000/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||' seconds; 13,CPU Time(Average): '||ROUND(q.CPU_TIME/1000/1000/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||' seconds; 14,Run Time Memory(Average): '||ROUND(q.RUNTIME_MEM/1024/1024/decode(nvl(q.EXECUTIONS,0),0,1,q.EXECUTIONS),3)||'M' str, 203 ';' spliter 204 from (select PLAN_HASH_VALUE, sum(LOADS) LOADS, min(FIRST_LOAD_TIME) FIRST_LOAD_TIME, max(LAST_LOAD_TIME) LAST_LOAD_TIME, 205 sum(LOADED_VERSIONS) LOADED_VERSIONS, sum(USERS_OPENING) USERS_OPENING, sum(EXECUTIONS) EXECUTIONS, 206 sum(PARSE_CALLS) PARSE_CALLS, sum(SORTS) SORTS, sum(FETCHES) FETCHES, sum(DISK_READS) DISK_READS, 207 sum(BUFFER_GETS) BUFFER_GETS, max(RUNTIME_MEM) RUNTIME_MEM, sum(CPU_TIME) CPU_TIME, 208 sum(ELAPSED_TIME) ELAPSED_TIME 209 from v$sql 210 where sql_id=:sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 211 group by PLAN_HASH_VALUE 212 union 213 select PLAN_HASH_VALUE, max(LOADS_TOTAL) LOADS, null FIRST_LOAD_TIME, null LAST_LOAD_TIME, 214 max(LOADED_VERSIONS) LOADED_VERSIONS, 0 USERS_OPENING, max(EXECUTIONS_TOTAL) EXECUTIONS, 215 max(PARSE_CALLS_TOTAL) PARSE_CALLS, max(SORTS_TOTAL) SORTS, max(FETCHES_TOTAL) FETCHES, 216 max(DISK_READS_TOTAL) DISK_READS, max(BUFFER_GETS_TOTAL) BUFFER_GETS, 0 RUNTIME_MEM, 217 max(CPU_TIME_TOTAL) CPU_TIME, max(ELAPSED_TIME_TOTAL) ELAPSED_TIME 218 from dba_hist_sqlstat 219 where sql_id=:sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue)) 220 and not exists (select 1 from v$sqlarea where sql_id = :sqlid and (:planHashValue is NULL or PLAN_HASH_VALUE=to_number(:planHashValue))) 221 group by PLAN_HASH_VALUE) q 222 where EXECUTIONS is not null and CPU_TIME is not null and ELAPSED_TIME is not null), 223 ss as (select /*+materialize*/* 224 from (select PLAN_HASH_VALUE, max(temp_size) temp_size, 0 pga_size 225 from (select t.SESSION_ADDR,nvl(q.PLAN_HASH_VALUE,99999999999999) PLAN_HASH_VALUE, 226 nvl(sum(t.BLOCKS*to_number(p.value)/1024/1024/1024),0) temp_size 227 from v$sort_usage t, v$parameter p, v$session s, v$sql q 228 where p.name = 'db_block_size' and t.sql_id=:sqlid 229 and t.SESSION_ADDR=s.saddr(+) and t.sql_id=s.sql_id(+) 230 and s.sql_id=q.sql_id(+) and s.sql_child_number=q.child_number(+) 231 and (:planHashValue is NULL or q.PLAN_HASH_VALUE is null or q.PLAN_HASH_VALUE=to_number(:planHashValue)) 232 group by t.SESSION_ADDR,nvl(q.PLAN_HASH_VALUE,99999999999999)) 233 group by PLAN_HASH_VALUE 234 union all 235 select nvl(q.PLAN_HASH_VALUE,99999999999999) PLAN_HASH_VALUE,0 temp_size, nvl(max(PGA_MAX_MEM/1024/1024/1024),0) pga_size 236 from v$process p, v$session s, v$sql q 237 where s.paddr=p.addr and s.sql_id = :sqlid 238 and s.sql_id=q.sql_id(+) and s.sql_child_number=q.child_number(+) 239 and (:planHashValue is NULL or q.PLAN_HASH_VALUE is null or q.PLAN_HASH_VALUE=to_number(:planHashValue)) 240 group by nvl(q.PLAN_HASH_VALUE,99999999999999) 241 union all 242 select pa.PLAN_HASH_VALUE,nvl(max(TEMP_SPACE_ALLOCATED/1024/1024/1024),0) temp_size, 243 nvl(max(PGA_ALLOCATED/1024/1024/1024),0) pga_size 244 from pa 245 group by pa.PLAN_HASH_VALUE)) 246 select /*+no_monitoring*/xxx 247 from ( 248 select 0 PLAN_HASH_VALUE, 1 seq, 0 ID, 'SQL ID: '||:sqlid xxx from dual 249 union 250 select 0 PLAN_HASH_VALUE, 1 seq, 1 ID, chr(10)||'------------- Last Monitored Binds --------------' xxx from dual where exists (select 1 from mb) and instr(:showOptions,'B')>0 251 union 252 select 0 PLAN_HASH_VALUE, 2 seq, to_number(sid||'.'||session_serial#||'000001') ID, 253 '--SID: '||sid||','||session_serial#||chr(10)||LISTAGG('var '||substr(b.name,2)||''||b.data_type,chr(10)) WITHIN GROUP (ORDER BY b.name) xxx 254 from mb b 255 where instr(:showOptions,'B')>0 256 GROUP BY sid,session_serial#,sql_id 257 union 258 select 0 PLAN_HASH_VALUE, 2 seq, to_number(sid||'.'||session_serial#||'000002') ID, 259 '--SID: '||sid||','||session_serial#||chr(10)||LISTAGG('exec '||b.name||':='||decode(instr(b.data_type,'NUMBER'),0,''''||b.value||''';',b.value),chr(10)) WITHIN GROUP (ORDER BY b.name) xxx 260 from mb b 261 where instr(:showOptions,'B')>0 262 GROUP BY sid,session_serial#,sql_id 263 union 264 select 0 PLAN_HASH_VALUE, 3 seq, 1 ID, chr(10)||'------------- Last Captured Binds --------------' xxx from dual where exists (select 1 from cb) and instr(:showOptions,'B')>0 and not exists (select 1 from mb) 265 union 266 select 0 PLAN_HASH_VALUE, 3 seq, 2 ID, var xxx from cb 267 where instr(:showOptions,'B')>0 and not exists (select 1 from mb) 268 union 269 select 0 PLAN_HASH_VALUE, 3 seq, 3 ID, exe xxx from cb 270 where instr(:showOptions,'B')>0 and not exists (select 1 from mb) 271 union 272 select 0 PLAN_HASH_VALUE, 10 seq, 0 ID, chr(10)||'------------- SQL Text --------------' xxx from dual 273 where instr(:showOptions,'T')>0 274 union 275 select * 276 from (select /*+no_merge*/0 PLAN_HASH_VALUE, 11 seq, level ID, to_char(substr(sql_fulltext,(level-1)*2000+1,2000)) sql_text 277 from q 278 where instr(:showOptions,'T')>0 279 connect by level<=ceil(length(sql_fulltext)/2000)) 280 UNION 281 select distinct PLAN_HASH_VALUE, 30 seq, -1 ID, chr(10)||'------------- SQL Plan (Plan Hash Value:'||PLAN_HASH_VALUE||'; Parsed by schema:'||PARSING_SCHEMA_NAME||') --------------' xxx 282 from p 283 where instr(:showOptions,'P')>0 284 UNION 285 select * 286 from (SELECT /*+no_merge*/PLAN_HASH_VALUE, 31 seq, ID, 287 lpad(nvl2(access_predicates,'*','')||nvl2(filter_predicates,'#','')||ID,6,'')||lpad('('||nvl(PARENT_ID||'','')||')',6,'')||LPAD('',(LEVEL-1))||OPERATION||DECODE(OTHER_TAG,NULL,'','*')||DECODE(OPTIONS,NULL,'',' ('||OPTIONS||')')||DECODE(OBJECT_NAME,NULL,'',' OF '''||OBJECT_NAME||'''')||DECODE(OBJECT_TYPE,NULL,'',' ('||OBJECT_TYPE||')')||DECODE(ID,0,DECODE(OPTIMIZER,NULL,'',' Optimizer='||OPTIMIZER))||DECODE(COST,NULL,'',' (Cost='||COST||DECODE(CARDINALITY,NULL,'',' Card='||CARDINALITY)||DECODE(BYTES,NULL,'',' Bytes='||BYTES)||')') xxx --,OBJECT_NODE OBJECT_NODE_PLUS_EXP 288 FROM P 289 where instr(:showOptions,'P')>0 290 START WITH ID=0 291 CONNECT BY PRIOR ID=PARENT_ID AND PRIOR SQL_ID=SQL_ID AND PRIOR PLAN_HASH_VALUE=PLAN_HASH_VALUE) 292 UNION 293 select distinct PLAN_HASH_VALUE, 33 seq, 0 ID, chr(10)||'------------- Stored Outline (Plan Hash Value:'||PLAN_HASH_VALUE||') --------------' xxx 294 from OL 295 where instr(:showOptions,'O')>0 296 UNION 297 select PLAN_HASH_VALUE, 33 seq, 1 ID, '/*+' xxx from OL 298 where instr(:showOptions,'O')>0 299 UNION 300 select PLAN_HASH_VALUE, 33 seq, 2 ID, lpad('',3,'')||'BEGIN_OUTLINE_DATA' xxx from OL 301 where instr(:showOptions,'O')>0 302 UNION 303 select PLAN_HASH_VALUE, 33 seq, 3 ID,lpad('',3,'')||hint xxx from OL 304 where instr(:showOptions,'O')>0 305 union 306 select PLAN_HASH_VALUE, 33 seq, 4 ID, lpad('',3,'')||'END_OUTLINE_DATA' xxx from OL 307 where instr(:showOptions,'O')>0 308 UNION 309 select PLAN_HASH_VALUE, 33 seq, 5 ID, '*/' xxx from OL 310 where instr(:showOptions,'O')>0 311 UNION 312 select distinct PLAN_HASH_VALUE, 35 seq, 0 ID, chr(10)||'------------- Peeked Binds (Plan Hash Value:'||PLAN_HASH_VALUE||') --------------' xxx 313 from pb 314 where instr(:showOptions,'K')>0 315 UNION 316 select PLAN_HASH_VALUE, 35 seq, 1 ID, 317 LISTAGG('var '||substr(name,2)||''||data_type,chr(10)) WITHIN GROUP (ORDER BY name) xxx 318 from pb 319 where instr(:showOptions,'K')>0 320 group by PLAN_HASH_VALUE 321 UNION 322 select PLAN_HASH_VALUE, 35 seq, 2 ID,LISTAGG('exec '||name||':='||decode(instr(data_type,'NUMBER'),0,''''||value||''';',value),chr(10)) WITHIN GROUP (ORDER BY name) xxx 323 from pb 324 where instr(:showOptions,'K')>0 325 group by PLAN_HASH_VALUE 326 UNION 327 select distinct PLAN_HASH_VALUE, 36 seq, -1 ID, chr(10)||'------------- Predicate Information (Plan Hash Value:'||PLAN_HASH_VALUE||') --------------' xxx 328 from P 329 where ((access_predicates is not null) or (filter_predicates is not null)) 330 and instr(:showOptions,'D')>0 331 UNION 332 select PLAN_HASH_VALUE, 36 seq, ID,lpad(id,3,'')||' Access: '||access_predicates xxx 333 from P 334 where (access_predicates is not null) 335 and instr(:showOptions,'D')>0 336 union 337 select PLAN_HASH_VALUE, 36 seq, ID,lpad(id,3,'')||' Filter: '||filter_predicates xxx 338 from P 339 where (filter_predicates is not null) 340 and instr(:showOptions,'D')>0 341 union 342 select distinct P.PLAN_HASH_VALUE, 50 seq, -1 ID, chr(10)||'------------- Plan Loading (Plan Hash Value:'||P.PLAN_HASH_VALUE||') --------------' xxx 343 from P, PL 344 where P.PLAN_HASH_VALUE=PL.PLAN_HASH_VALUE and p.ID=SQL_PLAN_LINE_ID 345 and total_waits>0 346 and instr(:showOptions,'L')>0 347 UNION 348 select P.PLAN_HASH_VALUE, 50 seq, PL.TOTAL_WAITS-PL.WAITS ID, 349 lpad(P.ID,3,'')||': '||RPAD(PL.sql_plan_op,50,'')||rpad('#',round(pl.waits/pl.total_waits*50),'#')||'('||round(pl.waits/pl.total_waits*100,2)||'%)' xxx 350 from P, PL 351 where P.PLAN_HASH_VALUE=PL.PLAN_HASH_VALUE and p.ID=SQL_PLAN_LINE_ID 352 and PL.total_waits>0 353 and instr(:showOptions,'L')>0 354 union 355 select distinct PLAN_HASH_VALUE, 55 seq, -1 ID, chr(10)||'------------- Waits Events (Plan Hash Value:'||PLAN_HASH_VALUE||') --------------' xxx 356 from we 357 where total_waits>0 358 and instr(:showOptions,'W')>0 359 UNION 360 select PLAN_HASH_VALUE, 55 seq, TOTAL_WAITS-WAITS ID, 361 rpad(event||' on '||wait_object,75,'')||rpad('#',round(waits/total_waits*50),'#')||'('||round(waits/total_waits*100,2)||'%)' xxx 362 from we 363 where total_waits>0 364 and instr(:showOptions,'W')>0 365 union 366 select PLAN_HASH_VALUE, 60 seq, 1 ID, chr(10)||'------------- Statistics Data '||decode(PLAN_HASH_VALUE,99999999999999,'','(Plan Hash Value:'||PLAN_HASH_VALUE||')')||'--------------' xxx from sd 367 where instr(:showOptions,'S')>0 368 union 369 select PLAN_HASH_VALUE, 60 seq, 370 10+to_number(substr(str,1,instr(str,',')-1)) ID, substr(str,instr(str,',')+1) xxx 371 from (select PLAN_HASH_VALUE, trim(regexp_substr(str, '[^'||spliter||']+', 1, level)) str from sd 372 connect by level <= length (regexp_replace (str, '[^'||spliter||']+')) + 1) 373 union 374 select PLAN_HASH_VALUE, 60 seq, 101 ID, 375 'PGA Size(Maximum): '||round(max(nvl(pga_size,0)),3)||'G' xxx 376 from ss 377 where instr(:showOptions,'S')>0 378 group by PLAN_HASH_VALUE 379 union 380 select PLAN_HASH_VALUE, 60 seq, 102 ID, 381 'Temp Space(Maximum): '||round(max(nvl(temp_size,0)),3)||'G' xxx 382 from ss 383 where instr(:showOptions,'S')>0 384 group by PLAN_HASH_VALUE 385 order by PLAN_HASH_VALUE, SEQ, ID, XXX) 386 ; 387 388 undef 1 2 3 389 set head on 390 clear columns
如有侵权请联系我删除。
Nothing is true,Nothing is fake.
