with t as (
  select /*+ materialize */ SQL_PLAN_LINE_ID, LISTAGG(TABLESPACE_NAME || ' ('||prc||'%)', '; ') WITHIN GROUP (ORDER BY prc desc) TABLESPACE_NAME, SUM(prc) as prc
  from (
    select nvl( SQL_PLAN_LINE_ID, 0) SQL_PLAN_LINE_ID, f.TABLESPACE_NAME, round( (RATIO_TO_REPORT(count(*)) OVER () *100 ), 2) as prc,
      round( (RATIO_TO_REPORT(count(*)) OVER (partition by nvl( SQL_PLAN_LINE_ID, 0)) *100 ), 2) as prc_per_line
    FROM   dba_hist_active_sess_history h
    left join DBA_DATA_FILES f on f.FILE_ID = h.CURRENT_FILE# 
    where 
    h.sample_time between :ST_DT and :ED_DT
    and h.sql_id = :SQL_ID AND h.SQL_PLAN_HASH_VALUE = :PLAN_HASH_VALUE
    group by nvl( SQL_PLAN_LINE_ID, 0), f.TABLESPACE_NAME
  )
  where prc_per_line > 1
  GROUP BY SQL_PLAN_LINE_ID
)
select DISTINCT p.id, RPAD(' ', p."DEPTH", ' ') || P.OPERATION || ' ' || p.OPTIONS  as OPERATION, p.object_name, 
  t.TABLESPACE_NAME, t.prc
from SYS.DBA_HIST_SQL_PLAN p
left join t on T.SQL_PLAN_LINE_ID = p.id 
where p.sql_id = :SQL_ID AND p.PLAN_HASH_VALUE = :PLAN_HASH_VALUE
order by p.id