explain plan in sessions

[dimka9]

Please help me with my little trouble.

Offten i use "sessions" window to monitor user activity. This window has some tabs: "Cursors", "SQl text"... for selected session.

I want add my own tab and this tab with execution plan of selected session. But i can`t compose query for it.

Could anyone help me?

Thanks in advance.

 

[Marco Kalter]

Perhaps this topic can help?

 

[dimka9]

Thanks, but it`s not exactly what I want.

Please tell me how to change this simply code:

"explain plan for
select * from dual;"

in a new tab. It produce error not execution plan.

 

[William Robertson]

Even if you could get that to work, "EXPLAIN PLAN FOR..." just populates the plan table. You would need s second query to display the results.

Ideally you would use DBMS_XPLAN and V$SQL_PLAN, but in 9i there is no easy way to get the execution plan of a session's current statement in one shot. I think 10g makes this a bit easier.

 

[Marco Kalter]

Probably because you have added a semi-colon. Regardless, this method will not produce a query plan, because it does not produce a result set.

Maybe something can be done with the dbms_xplan package though, but I cannot immediately find a solution. For which SQL do you want to display a query plan?

 

[dimka9]

Is it posible to use in query more that one sql statement or pl/sql block?

If not I think the best way is to modify function from link in this topic:

create or replace function sqltext_full(p_address in raw,
p_hash_value in number) return varchar2 is
result varchar2(4000);

begin
result := '';
for c in (select sql_text
from v$sqltext_with_newlines
where address = p_address
and hash_value = p_hash_value
order by piece)
loop
result := result | | c.sql_text;
end loop;
return(result);
end sqltext_full;

add
explain plan for results
get to work dbms-xplan.display
return result.

But I need install this package in every db. Do you know other solution?

 

[Marco Kalter]

No, I do not know any other solution. The restriction that the query needs to return a result set limits the possibilities.