Skip to Main Content
  • Questions
  • where are the executed statments stored?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, abhishek .

Asked: July 06, 2016 - 4:38 am UTC

Answered by: Chris Saxon - Last updated: July 06, 2016 - 7:53 am UTC

Category: Database - Version: 11g release 2

Viewed 100+ times

You Asked

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?

and we said...

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.

Is this answer out of date? If it is, please let us know via a Review