suppose i execute a plsql block , all the statments are not executed. Only high load statments are executed. How to see which of the statements are executed and where are they stored, Like which view/table?
Executed statements are stored in the library cache, a section of the shared pool:
http://docs.oracle.com/database/121/CNCPT/memory.htm#GUID-DE757E9C-3437-408A-8598-3EB4C8E2A3B0 You can see which SQL & PL/SQL statements are currently in the library cache by querying in v$sql:
declare
l int;
begin
if 1=1 then
select /*+ executed */1 into l from dual;
else
select /*+ not_executed */2 into l from dual;
end if;
end;
/
select sql_text from v$sql
where sql_text like '%executed%'
and sql_text not like '%not this%';
SQL_TEXT
declare l int; begin if 1=1 then select /*+ executed */1 into l from dual; else select /*+ not_executed */2 into l from dual; end if; end;
SELECT /*+ executed */1 FROM DUAL
Notice that the PL/SQL block is in there too. But the "not_executed" statement doesn't have its own entry.