How to use the dbms_sql to get the analyze for insert into statement

Question

declare 
 v_cursor number;
 stmt varchar2(2000);
 s varchar2(40);
 cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID where voc.SID in(select sid from v$mystat) and to_char(substr(vs.SQL_FULLTEXT,1,2000)=stmt;
begin
delete from table1;
v_cursor:=dbms_sql.open_cursor;
stmt:='inset into table2 (column1,column2) values(''a'',''b'')';
dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
insert into table1 select * from tables(dbms_xplan.display
open cc1;
loop
fetch cc1 into s;
exit;
end loop;
close cc1;
insert into table1 select * from(dbms_xplan.display_cursor(s,0,'ALL'));

I execute this and select from the table1 but the record in table1 is tells me:

it could also be that the plan is no longer in cursor cache(check v$sql_plan).

But if I change the stmt to update statement and execute, the record in table1 is correct. So i want to know where can I get the analyze about the insert statement.

Answer 1

The child_number argument to dbms_xplan.display_cursor needs to be taken from v$sql as well, it's not necessarily zero. (I'm not exactly sure what influences that number, but dropping and recreating table2 in your example gives me a new child_number each time).

Here's a modified version of your procedure (fixed a few typoes in what you have above):

DECLARE
 v_cursor NUMBER;
 stmt VARCHAR2(2000);
 s VARCHAR2(40);
 cn NUMBER;
 CURSOR cc1
 IS
 SELECT voc.SQL_ID, vs.child_number
 FROM v$open_cursor voc
 LEFT JOIN v$sql vs
 ON voc.SQL_ID =vs.SQL_ID
 WHERE voc.SID IN (SELECT sid FROM v$mystat)
 AND TO_CHAR(SUBSTR(vs.SQL_FULLTEXT,1,2000))=stmt;
BEGIN
 DELETE FROM table1;
 v_cursor:=dbms_sql.open_cursor;
 stmt :='insert into table2 values (''a'',''b'')';
 dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
 OPEN cc1;
 LOOP
 FETCH cc1 INTO s, cn;
 EXIT;
 END LOOP;
 CLOSE cc1;
 -- To inspect the child number:
 -- dbms_output.put_line(s || ' '|| cn); 
 INSERT INTO table1 SELECT * FROM table(dbms_xplan.display_cursor(s,cn,'ALL'));
END;
/