set echo off verify off termout off set doc off doc -- ---------------------------------------------------------------------------------------------- -- -- Script: xplan_extended_display_cursor.sql -- -- Version: 0.9 -- December 2011 -- -- Author: Randolf Geist -- oracle-randolf.blogspot.com -- -- Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the -- DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NUMBER -- -- This is a prototype for an extended analysis of the data provided by the -- Runtime Profile (aka. Rowsource Statistics enabled via -- SQL_TRACE = TRUE, STATISTICS_LEVEL = ALL or GATHER_PLAN_STATISTICS hint) -- and reported via the ALLSTATS/MEMSTATS/IOSTATS formatting option of -- DBMS_XPLAN.DISPLAY_CURSOR -- -- Versions: This utility will work for all versions of 10g and upwards. -- -- Required: The same access as DBMS_XPLAN.DISPLAY_CURSOR requires. See the documentation -- of DISPLAY_CURSOR for your Oracle version for more information -- -- The script directly queries -- 1) V$SESSION -- 2) V$SQL_PLAN_STATISTICS_ALL -- -- Credits: Based on the original XPLAN implementation by Adrian Billington (http://www.oracle-developer.net/utilities.php -- resp. http://www.oracle-developer.net/content/utilities/xplan.zip) -- and inspired by Kyle Hailey's TCF query (http://dboptimizer.com/2011/09/20/display_cursor/) -- -- Features: In addition to the PID (The PARENT_ID) and ORD (The order of execution, note that this doesn't account for the special cases so it might be wrong) -- columns added by Adrian's wrapper the following additional columns over ALLSTATS are provided: -- -- A_TIME_SELF : The time taken by the operation itself - this is the operation's cumulative time minus the direct descendants operation's cumulative time -- LIO_SELF : The LIOs done by the operation itself - this is the operation's cumulative LIOs minus the direct descendants operation's cumulative LIOs -- READS_SELF : The reads performed the operation itself - this is the operation's cumulative reads minus the direct descendants operation's cumulative reads -- WRITES_SELF : The writes performed the operation itself - this is the operation's cumulative writes minus the direct descendants operation's cumulative writes -- A_TIME_SELF_GRAPH : A graphical representation of A_TIME_SELF relative to the total A_TIME -- LIO_SELF_GRAPH : A graphical representation of LIO_SELF relative to the total LIO -- READS_SELF_GRAPH : A graphical representation of READS_SELF relative to the total READS -- WRITES_SELF_GRAPH : A graphical representation of WRITES_SELF relative to the total WRITES -- LIO_RATIO : Ratio of LIOs per row generated by the row source - the higher this ratio the more likely there could be a more efficient way to generate those rows (be aware of aggregation steps though) -- TCF_GRAPH : Each "+"/"-" sign represents one order of magnitude based on ratio between E_ROWS_TIMES_START and A-ROWS. Note that this will be misleading with Parallel Execution (see E_ROWS_TIMES_START) -- E_ROWS_TIMES_START : The E_ROWS multiplied by STARTS - this is useful for understanding the actual cardinality estimate for related combine child operations getting executed multiple times. Note that this will be misleading with Parallel Execution -- -- More information including demos can be found online at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html -- -- Usage: @xplan_extended_display_cursor.sql [sql_id] [cursor_child_number] [format_option] -- -- If both the SQL_ID and CHILD_NUMBER are omitted the previously executed SQL_ID and CHILD_NUMBER of the session will be used -- If the SQL_ID is specified but the CHILD_NUMBER is omitted then CHILD_NUMBER 0 is assumed -- -- This prototype does not support processing multiple child cursors like DISPLAY_CURSOR is capable of -- when passing NULL as CHILD_NUMBER to DISPLAY_CURSOR. Hence a CHILD_NUMBER is mandatory, either -- implicitly generated (see above) or explicitly passed -- -- The default formatting option for the call to DBMS_XPLAN.DISPLAY_CURSOR is ALLSTATS LAST - extending this output is the primary purpose of this script -- -- Note: You need a veeery wide terminal setting for this prototype, something like linesize 400 should suffice -- -- This tool is free but comes with no warranty at all - use at your own risk -- # col plan_table_output format a400 set linesize 400 pagesize 0 tab off /* ALLSTATS LAST is assumed as the default formatting option for DBMS_XPLAN.DISPLAY_CURSOR */ define default_fo = "ALLSTATS LAST" column prev_sql_id new_value prev_sql_id column prev_child_number new_value prev_cn /* Get the previous command as default if no SQL_ID / CHILD_NUMBER is passed */ select prev_sql_id , prev_child_number from v$session where sid = userenv('sid') ; -- The following is a hack to use default -- values for defines column 1 new_value 1 column 2 new_value 2 column 3 new_value 3 select '' as "1" , '' as "2" , '' as "3" from dual where rownum = 0; column si new_value si column cn new_value cn column fo new_value fo /* Use passed parameters else refer to previous SQL_ID / CHILD_NUMBER ALLSTATS LAST is default formatting option */ select nvl('&1', '&prev_sql_id') as si , coalesce('&2', '&prev_cn', '0') as cn , nvl('&3', '&default_fo') as fo from dual ; column last new_value last /* Last or all execution */ select case when instr('&fo', 'LAST') > 0 then 'last_' end as last from dual ; set termout on with -- The next three queries are based on the original XPLAN wrapper by Adrian Billington -- to determine the PID and ORD information, only slightly modified to deal with -- the 10g special case that V$SQL_PLAN_STATISTICS_ALL doesn't include the ID = 0 operation -- and starts with 1 instead for Rowsource Statistics sql_plan_data as ( select id , parent_id from v$sql_plan_statistics_all where sql_id = '&si' and child_number = &cn ), hierarchy_data as ( select id , parent_id from sql_plan_data start with id in ( select id from sql_plan_data p1 where not exists ( select null from sql_plan_data p2 where p2.id = p1.parent_id ) ) connect by prior id = parent_id order siblings by id desc ), ordered_hierarchy_data as ( select id , parent_id as pid , row_number() over (order by rownum desc) as oid , max(id) over () as maxid , min(id) over () as minid from hierarchy_data ), -- The following query uses the MAX values -- rather than taking the values of PLAN OPERATION_ID = 0 (or 1 for 10g V$SQL_PLAN_STATISTICS_ALL) -- for determining the grand totals -- -- This is because queries that get cancelled do not -- necessarily have yet sensible values in the root plan operation -- -- Furthermore with Parallel Execution the elapsed time accumulated -- with the ALLSTATS option for operations performed in parallel -- will be greater than the wallclock elapsed time shown for the Query Coordinator -- -- Note that if you use GATHER_PLAN_STATISTICS with the default -- row sampling frequency the (LAST_)ELAPSED_TIME will be very likely -- wrong and hence the time-based graphs and self-statistics will be misleading -- -- Similar things might happen when cancelling queries -- -- For queries running with STATISTICS_LEVEL = ALL (or sample frequency set to 1) -- the A-TIME is pretty reliable totals as ( select max(&last.cu_buffer_gets + &last.cr_buffer_gets) as total_lio , max(&last.elapsed_time) as total_elapsed , max(&last.disk_reads) as total_reads , max(&last.disk_writes) as total_writes from v$sql_plan_statistics_all where sql_id = '&si' and child_number = &cn ), -- The totals for the direct descendants of an operation -- These are required for calculating the work performed -- by a (parent) operation itself -- Basically this is the SUM grouped by PARENT_ID direct_desc_totals as ( select sum(&last.cu_buffer_gets + &last.cr_buffer_gets) as lio , sum(&last.elapsed_time) as elapsed , sum(&last.disk_reads) as reads , sum(&last.disk_writes) as writes , parent_id from v$sql_plan_statistics_all where sql_id = '&si' and child_number = &cn group by parent_id ), -- Putting the three together -- The statistics, direct descendant totals plus totals extended_stats as ( select stats.id , stats.parent_id , stats.&last.elapsed_time as elapsed , (stats.&last.cu_buffer_gets + stats.&last.cr_buffer_gets) as lio , stats.&last.starts as starts , stats.&last.output_rows as a_rows , stats.cardinality as e_rows , stats.&last.disk_reads as reads , stats.&last.disk_writes as writes , ddt.elapsed as ddt_elapsed , ddt.lio as ddt_lio , ddt.reads as ddt_reads , ddt.writes as ddt_writes , t.total_elapsed , t.total_lio , t.total_reads , t.total_writes from v$sql_plan_statistics_all stats , direct_desc_totals ddt , totals t where stats.sql_id='&si' and stats.child_number = &cn and ddt.parent_id (+) = stats.id ), -- Further information derived from above derived_stats as ( select id , greatest(elapsed - nvl(ddt_elapsed , 0), 0) as elapsed_self , greatest(lio - nvl(ddt_lio, 0), 0) as lio_self , trunc((greatest(lio - nvl(ddt_lio, 0), 0)) / nullif(a_rows, 0)) as lio_ratio , greatest(reads - nvl(ddt_reads, 0), 0) as reads_self , greatest(writes - nvl(ddt_writes,0) ,0) as writes_self , total_elapsed , total_lio , total_reads , total_writes , trunc(log(10, nullif(starts * e_rows / nullif(a_rows, 0), 0))) as tcf_ratio , starts * e_rows as e_rows_times_start from extended_stats ), /* Format the data as required */ formatted_data1 as ( select id , lio_ratio , total_elapsed , total_lio , total_reads , total_writes , to_char(numtodsinterval(round(elapsed_self / 10000) * 10000 / 1000000, 'SECOND')) as e_time_interval /* Imitate the DBMS_XPLAN number formatting */ , case when lio_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E' when lio_self >= 10000000000000000000 then to_char(lio_self/1000000000000000000, 'FM99999') || 'E' when lio_self >= 10000000000000000 then to_char(lio_self/1000000000000000, 'FM99999') || 'P' when lio_self >= 10000000000000 then to_char(lio_self/1000000000000, 'FM99999') || 'T' when lio_self >= 10000000000 then to_char(lio_self/1000000000, 'FM99999') || 'G' when lio_self >= 10000000 then to_char(lio_self/1000000, 'FM99999') || 'M' when lio_self >= 100000 then to_char(lio_self/1000, 'FM99999') || 'K' else to_char(lio_self, 'FM99999') || ' ' end as lio_self_format , case when reads_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E' when reads_self >= 10000000000000000000 then to_char(reads_self/1000000000000000000, 'FM99999') || 'E' when reads_self >= 10000000000000000 then to_char(reads_self/1000000000000000, 'FM99999') || 'P' when reads_self >= 10000000000000 then to_char(reads_self/1000000000000, 'FM99999') || 'T' when reads_self >= 10000000000 then to_char(reads_self/1000000000, 'FM99999') || 'G' when reads_self >= 10000000 then to_char(reads_self/1000000, 'FM99999') || 'M' when reads_self >= 100000 then to_char(reads_self/1000, 'FM99999') || 'K' else to_char(reads_self, 'FM99999') || ' ' end as reads_self_format , case when writes_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E' when writes_self >= 10000000000000000000 then to_char(writes_self/1000000000000000000, 'FM99999') || 'E' when writes_self >= 10000000000000000 then to_char(writes_self/1000000000000000, 'FM99999') || 'P' when writes_self >= 10000000000000 then to_char(writes_self/1000000000000, 'FM99999') || 'T' when writes_self >= 10000000000 then to_char(writes_self/1000000000, 'FM99999') || 'G' when writes_self >= 10000000 then to_char(writes_self/1000000, 'FM99999') || 'M' when writes_self >= 100000 then to_char(writes_self/1000, 'FM99999') || 'K' else to_char(writes_self, 'FM99999') || ' ' end as writes_self_format , case when e_rows_times_start >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, 'FM99999') || 'E' when e_rows_times_start >= 10000000000000000000 then to_char(e_rows_times_start/1000000000000000000, 'FM99999') || 'E' when e_rows_times_start >= 10000000000000000 then to_char(e_rows_times_start/1000000000000000, 'FM99999') || 'P' when e_rows_times_start >= 10000000000000 then to_char(e_rows_times_start/1000000000000, 'FM99999') || 'T' when e_rows_times_start >= 10000000000 then to_char(e_rows_times_start/1000000000, 'FM99999') || 'G' when e_rows_times_start >= 10000000 then to_char(e_rows_times_start/1000000, 'FM99999') || 'M' when e_rows_times_start >= 100000 then to_char(e_rows_times_start/1000, 'FM99999') || 'K' else to_char(e_rows_times_start, 'FM99999') || ' ' end as e_rows_times_start_format , rpad(' ', nvl(round(elapsed_self / nullif(total_elapsed, 0) * 12), 0) + 1, '@') as elapsed_self_graph , rpad(' ', nvl(round(lio_self / nullif(total_lio, 0) * 12), 0) + 1, '@') as lio_self_graph , rpad(' ', nvl(round(reads_self / nullif(total_reads, 0) * 12), 0) + 1, '@') as reads_self_graph , rpad(' ', nvl(round(writes_self / nullif(total_writes, 0) * 12), 0) + 1, '@') as writes_self_graph , ' ' || case when tcf_ratio > 0 then rpad('-', tcf_ratio, '-') else rpad('+', tcf_ratio * -1, '+') end as tcf_graph from derived_stats ), /* The final formatted data */ formatted_data as ( select /*+ Convert the INTERVAL representation to the A-TIME representation used by DBMS_XPLAN by turning the days into hours */ to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), 'FM900') || substr(e_time_interval, 14, 9) as a_time_self , a.* from formatted_data1 a ), /* Combine the information with the original DBMS_XPLAN output */ xplan_data as ( select x.plan_table_output , o.id , o.pid , o.oid , o.maxid , o.minid , a.a_time_self , a.lio_self_format , a.reads_self_format , a.writes_self_format , a.elapsed_self_graph , a.lio_self_graph , a.reads_self_graph , a.writes_self_graph , a.lio_ratio , a.tcf_graph , a.total_elapsed , a.total_lio , a.total_reads , a.total_writes , a.e_rows_times_start_format , x.rn from ( select /* Take advantage of 11g table function dynamic sampling */ /*+ dynamic_sampling(dc, 2) */ /* This ROWNUM determines the order of the output/processing */ rownum as rn , plan_table_output from table(dbms_xplan.display_cursor('&si',&cn, '&fo')) dc ) x , ordered_hierarchy_data o , formatted_data a where o.id (+) = case when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|') then to_number(regexp_substr(x.plan_table_output, '[0-9]+')) end and a.id (+) = case when regexp_like(x.plan_table_output, '^\|[\* 0-9]+\|') then to_number(regexp_substr(x.plan_table_output, '[0-9]+')) end ) /* Inject the additional data into the original DBMS_XPLAN output by using the MODEL clause */ select plan_table_output from xplan_data model dimension by (rn as r) measures ( cast(plan_table_output as varchar2(4000)) as plan_table_output , id , maxid , minid , pid , oid , a_time_self , lio_self_format , reads_self_format , writes_self_format , e_rows_times_start_format , elapsed_self_graph , lio_self_graph , reads_self_graph , writes_self_graph , lio_ratio , tcf_graph , total_elapsed , total_lio , total_reads , total_writes , greatest(max(length(maxid)) over () + 3, 6) as csize , cast(null as varchar2(128)) as inject , cast(null as varchar2(4000)) as inject2 ) rules sequential order ( /* Prepare the injection of the OID / PID info */ inject[r] = case /* MINID/MAXID are the same for all rows so it doesn't really matter which offset we refer to */ when id[cv(r)+1] = minid[cv(r)+1] or id[cv(r)+3] = minid[cv(r)+3] or id[cv(r)-1] = maxid[cv(r)-1] then rpad('-', csize[cv()]*2, '-') when id[cv(r)+2] = minid[cv(r)+2] then '|' || lpad('Pid |', csize[cv()]) || lpad('Ord |', csize[cv()]) when id[cv()] is not null then '|' || lpad(pid[cv()] || ' |', csize[cv()]) || lpad(oid[cv()] || ' |', csize[cv()]) end /* Prepare the injection of the remaining info */ , inject2[r] = case when id[cv(r)+1] = minid[cv(r)+1] or id[cv(r)+3] = minid[cv(r)+3] or id[cv(r)-1] = maxid[cv(r)-1] then rpad('-', case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then 14 else 0 end /* A_TIME_SELF */ + case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then 11 else 0 end /* LIO_SELF */ + case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then 11 else 0 end /* READS_SELF */ + case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then 11 else 0 end /* WRITES_SELF */ + case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then 14 else 0 end /* A_TIME_SELF_GRAPH */ + case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then 14 else 0 end /* LIO_SELF_GRAPH */ + case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then 14 else 0 end /* READS_SELF_GRAPH */ + case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then 14 else 0 end /* WRITES_SELF_GRAPH */ + case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then 11 else 0 end /* LIO_RATIO */ + case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then 11 else 0 end /* TCF_GRAPH */ + case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then 11 else 0 end /* E_ROWS_TIMES_START */ , '-') when id[cv(r)+2] = minid[cv(r)+2] then case when total_elapsed[cv(r)+2] > 0 then lpad('A-Time Self |' , 14) end || case when total_lio[cv(r)+2] > 0 then lpad('Bufs Self |' , 11) end || case when total_reads[cv(r)+2] > 0 then lpad('Reads Self|' , 11) end || case when total_writes[cv(r)+2] > 0 then lpad('Write Self|' , 11) end || case when total_elapsed[cv(r)+2] > 0 then lpad('A-Ti S-Graph |', 14) end || case when total_lio[cv(r)+2] > 0 then lpad('Bufs S-Graph |', 14) end || case when total_reads[cv(r)+2] > 0 then lpad('Reads S-Graph|', 14) end || case when total_writes[cv(r)+2] > 0 then lpad('Write S-Graph|', 14) end || case when total_lio[cv(r)+2] > 0 then lpad('LIO Ratio |' , 11) end || case when total_elapsed[cv(r)+2] > 0 then lpad('TCF Graph |' , 11) end || case when total_elapsed[cv(r)+2] > 0 then lpad('E-Rows*Sta|' , 11) end when id[cv()] is not null then case when total_elapsed[cv()] > 0 then lpad(a_time_self[cv()] || ' |', 14) end || case when total_lio[cv()] > 0 then lpad(lio_self_format[cv()] || '|', 11) end || case when total_reads[cv()] > 0 then lpad(reads_self_format[cv()] || '|', 11) end || case when total_writes[cv()] > 0 then lpad(writes_self_format[cv()] || '|', 11) end || case when total_elapsed[cv()] > 0 then rpad(elapsed_self_graph[cv()], 13) || '|' end || case when total_lio[cv()] > 0 then rpad(lio_self_graph[cv()], 13) || '|' end || case when total_reads[cv()] > 0 then rpad(reads_self_graph[cv()], 13) || '|' end || case when total_writes[cv()] > 0 then rpad(writes_self_graph[cv()], 13) || '|' end || case when total_lio[cv()] > 0 then lpad(lio_ratio[cv()] || ' |', 11) end || case when total_elapsed[cv()] > 0 then rpad(tcf_graph[cv()], 9) || ' |' end || case when total_elapsed[cv()] > 0 then lpad(e_rows_times_start_format[cv()] || '|', 11) end end /* Putting it all together */ , plan_table_output[r] = case when inject[cv()] like '---%' then inject[cv()] || plan_table_output[cv()] || inject2[cv()] when inject[cv()] is present then regexp_replace(plan_table_output[cv()], '\|', inject[cv()], 1, 2) || inject2[cv()] else plan_table_output[cv()] end ) order by r ; undefine default_fo undefine prev_sql_id undefine prev_cn undefine last undefine si undefine cn undefine fo undefine 1 undefine 2 undefine 3 col plan_table_output clear col prev_sql_id clear col prev_child_number clear col si clear col cn clear col fo clear col last clear set doc off doc /* , lio_self , trunc(log(10,abs(case lio_self when 0 then 1 else lio_self end))) as power_10_lio_self , trunc(mod(log(10,abs(case lio_self when 0 then 1 else lio_self end)),3)) as power_10_lio_self_mod_3 , reads_self , trunc(log(10,abs(case reads_self when 0 then 1 else reads_self end))) as power_10_reads_self , trunc(mod(log(10,abs(case reads_self when 0 then 1 else reads_self end)),3)) as power_10_reads_self_mod_3 , writes_self , trunc(log(10,abs(case writes_self when 0 then 1 else writes_self end))) as power_10_writes_self , trunc(mod(log(10,abs(case writes_self when 0 then 1 else writes_self end)),3)) as power_10_writes_self_mod_3 , e_rows_times_start , trunc(log(10,abs(case e_rows_times_start when 0 then 1 else e_rows_times_start end))) as power_10_e_rows_start , trunc(mod(log(10,abs(case e_rows_times_start when 0 then 1 else e_rows_times_start end)),3)) as power_10_e_rows_start_mod_3 */ /*, to_char(round(lio_self / power(10, power_10_lio_self - case when power_10_lio_self > 0 and power_10_lio_self_mod_3 = 0 then 3 else power_10_lio_self_mod_3 end)), 'FM99999') || case power_10_lio_self - case when power_10_lio_self > 0 and power_10_lio_self_mod_3 = 0 then 3 else power_10_lio_self_mod_3 end when 0 then ' ' when 1 then ' ' when 3*1 then 'K' when 3*2 then 'M' when 3*3 then 'G' when 3*4 then 'T' when 3*5 then 'P' when 3*6 then 'E' else '*10^'||to_char(power_10_lio_self - case when power_10_lio_self > 0 and power_10_lio_self_mod_3 = 0 then 3 else power_10_lio_self_mod_3 end) end as lio_self_format2 , to_char(round(reads_self / power(10, power_10_reads_self - case when power_10_reads_self > 0 and power_10_reads_self_mod_3 = 0 then 3 else power_10_reads_self_mod_3 end)), 'FM99999') || case power_10_reads_self - case when power_10_reads_self > 0 and power_10_reads_self_mod_3 = 0 then 3 else power_10_reads_self_mod_3 end when 0 then ' ' when 1 then ' ' when 3*1 then 'K' when 3*2 then 'M' when 3*3 then 'G' when 3*4 then 'T' when 3*5 then 'P' when 3*6 then 'E' else '*10^'||to_char(power_10_reads_self - case when power_10_reads_self > 0 and power_10_reads_self_mod_3 = 0 then 3 else power_10_reads_self_mod_3 end) end as reads_self_format2 , to_char(round(writes_self / power(10, power_10_writes_self - case when power_10_writes_self > 0 and power_10_writes_self_mod_3 = 0 then 3 else power_10_writes_self_mod_3 end)), 'FM99999') || case power_10_writes_self - case when power_10_writes_self > 0 and power_10_writes_self_mod_3 = 0 then 3 else power_10_writes_self_mod_3 end when 0 then ' ' when 1 then ' ' when 3*1 then 'K' when 3*2 then 'M' when 3*3 then 'G' when 3*4 then 'T' when 3*5 then 'P' when 3*6 then 'E' else '*10^'||to_char(power_10_writes_self - case when power_10_writes_self > 0 and power_10_writes_self_mod_3 = 0 then 3 else power_10_writes_self_mod_3 end) end as writes_self_format2 , to_char(round(e_rows_times_start / power(10, power_10_e_rows_start - case when power_10_e_rows_start > 0 and power_10_e_rows_start_mod_3 = 0 then 3 else power_10_e_rows_start_mod_3 end)), 'FM99999') || case power_10_e_rows_start - case when power_10_e_rows_start > 0 and power_10_e_rows_start_mod_3 = 0 then 3 else power_10_e_rows_start_mod_3 end when 0 then ' ' when 1 then ' ' when 3*1 then 'K' when 3*2 then 'M' when 3*3 then 'G' when 3*4 then 'T' when 3*5 then 'P' when 3*6 then 'E' else '*10^'||to_char(power_10_e_rows_start - case when power_10_e_rows_start > 0 and power_10_e_rows_start_mod_3 = 0 then 3 else power_10_e_rows_start_mod_3 end) end as e_rows_times_start_format2*/ #