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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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

Whilst you are here, check out some content from the AskTom team: I Love Backups and APIs, a.k.a., Test Delete Processes VERY THOROUGHLY

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