Re: how can I get the ssql plan wrapped in a pl/sql block

From: Lothar Flatz <l.flatz_at_bluewin.ch>
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-l
Received on Sun Mar 12 2017 - 21:37:39 CET

Original text of this message