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
