Forum Stats

  • 3,701,023 Users
  • 2,239,262 Discussions


query optimization??

Jorge1-JavaNetJorge1-JavaNet Posts: 184 Blue Ribbon


I have this query , could you help me?, is very it's very slow

select s.sql_id,

sa.sql_fulltext sql_text,

s.sid, s.serial#, s.inst_id, spid, s.username, s.osuser, s.status, s.program, s.machine, s.blocking_session, s.logon_time,

(select listagg(sp.object_owner||'.'||sp.object_name||'('||substr(sp.object_type,1,3)||')',',') within group (order by sp.object_owner,sp.object_name)

from gv$sql_plan sp join dba_objects o on (sp.object_owner=o.owner and sp.object_name=o.object_name and sp.object_type=o.object_type)

where sp.inst_id=sa.inst_id and sp.sql_id=sa.sql_id and sp.plan_hash_value=sa.plan_hash_value and sp.child_number=sa.child_number group by sql_id) objects

from gv$session s

left join gv$sql sa on (s.inst_id=sa.inst_id and s.sql_id=sa.sql_id and s.sql_child_number=sa.child_number)

left join gv$process p on (s.inst_id=p.inst_id and s.paddr=p.addr)

where regexp_like(s.username,'^U(XXX|XXY)+')

Best Answer


Sign In or Register to comment.