Re: how can I get the ssql plan wrapped in a pl/sql block
Date: Sun, 12 Mar 2017 21:37:39 +0100
Message-ID: <b00dfdc4-9a7e-8069-91fc-3ae95468a22e_at_bluewin.ch>
Hi,
there are several ways to achieve what you want.
You normally display a plan using
Select * from table (dbms_xplan.display_cursor ('&sql_id'));
All you need to know is the sql_id of the embedded statement.
One easy way is to mark the statement using a comment and then search
for it in v$sql.
When the sql is called from PL/SQL comments will be striped of. You need
a hint to mark your statement.
When you put the mark at the end of the hint it is normally no issue
that it is not a valid hint.
I normally use my initial and a number as a mark.
Thus your block would look like
begin
select /*+ QL1 */ count(*) from sys_base where part_num='11111';
end;
To display the plan you can use:
select * from table (dbms_xplan.display_cursor((select sql_id from v$sql
where sql_text like '%QL1%' and
sql_text not like '%v$sql%' and command_type=3 )))
/
BTW: you need an into clause for your statement.
Regards
Lothar
On 11.03.2017 02:36, 刘 Qinliu wrote:
>
> I execute a pl/sql blcok as following:
> begin
> select count(*) from sys_base where part_num='11111';
> end;
> I want to view the execute the sql plan select count(*) from sys_base
> where part_num='11111'' .
> However, in the v$sql I just get 'begin select count(*) from sys_base
> where part_num='11111'' end;'
>
> so I can't view th plan . How can I view the sqls wrapped in pl/sql
> procedure.
>
> Is there any way I can get the sql plan from a procedure or funcation
>
-- -- http://www.freelists.org/webpage/oracle-lReceived on Sun Mar 12 2017 - 21:37:39 CET