Home » RDBMS Server » Performance Tuning » About the sql_text in V$sqlarea
icon6.gif  About the sql_text in V$sqlarea [message #128536] Tue, 19 July 2005 05:07 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
hi all,
I have created a procedure and calling it from a different test procedure.But while after executing the test if i want to see the sql statement that are in my original procedure ,then those statements are not visible in v$sqlarea.
What may be the reason.

But if i give some hard-coded value in the insert statement of my procedure and run the test procedure,then this insert statement is there in v$sqlarea.

Help me to find out the reason.

Thanks
Dinesh
Re: About the sql_text in V$sqlarea [message #128749 is a reply to message #128536] Wed, 20 July 2005 07:43 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
hi all,
no answer to the above qs.I think no one is getting the problem.
Ok..
Thanks
Dinesh Laughing
Re: About the sql_text in V$sqlarea [message #128763 is a reply to message #128536] Wed, 20 July 2005 08:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I did this on 10.1.0.4:

MYDBA@ORCL > 
MYDBA@ORCL > alter system flush shared_pool;

System altered.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          1

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > create table testsqlarea(a number);

Table created.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          2

2 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > pause

MYDBA@ORCL > 
MYDBA@ORCL > insert into testsqlarea select rownum from dual connect by level <= 100;

100 rows created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          3
insert into testsqlarea select rownum from dual connect by level <= 10          1

3 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > pause

MYDBA@ORCL > 
MYDBA@ORCL > select a from testsqlarea where rownum <= 5;

         A
----------
         1
         2
         3
         4
         5

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          4
insert into testsqlarea select rownum from dual connect by level <= 10          1
select a from testsqlarea where rownum <= 5                                     1

4 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > pause

MYDBA@ORCL > 
MYDBA@ORCL > declare
  2  	     l_count number;
  3  begin
  4  	     select count(*) into l_count from testsqlarea;
  5  	     dbms_output.put_line(l_count);
  6  end;
  7  /
100

PL/SQL procedure successfully completed.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          5
insert into testsqlarea select rownum from dual connect by level <= 10          1
select a from testsqlarea where rownum <= 5                                     1
declare  l_count number; begin  select count(*) into l_count from test          1

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > create procedure mytest is
  2  	     l_count number;
  3  begin
  4  	     select count(*) into l_count from testsqlarea where rownum <= 10;
  5  	     dbms_output.put_line(l_count);
  6  end;
  7  /

Procedure created.

MYDBA@ORCL > show errors
No errors.
MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          6
insert into testsqlarea select rownum from dual connect by level <= 10          1
select a from testsqlarea where rownum <= 5                                     1
declare  l_count number; begin  select count(*) into l_count from test          1

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > exec mytest;
10

PL/SQL procedure successfully completed.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          7
insert into testsqlarea select rownum from dual connect by level <= 10          1
select a from testsqlarea where rownum <= 5                                     1
declare  l_count number; begin  select count(*) into l_count from test          1

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > drop table testsqlarea;

Table dropped.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          8
insert into testsqlarea select rownum from dual connect by level <= 10          0
select a from testsqlarea where rownum <= 5                                     0
declare  l_count number; begin  select count(*) into l_count from test          0

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > drop procedure mytest;

Procedure dropped.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
create table testsqlarea(a number)                                              0
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          9
insert into testsqlarea select rownum from dual connect by level <= 10          0
select a from testsqlarea where rownum <= 5                                     0
declare  l_count number; begin  select count(*) into l_count from test          0

5 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > alter system flush shared_pool;

System altered.

MYDBA@ORCL > 
MYDBA@ORCL > select substr(sql_text,1,70), executions from v$sqlarea
  2  where sql_text like '%testsqlarea%' order by first_load_time;

SUBSTR(SQL_TEXT,1,70)                                                  EXECUTIONS
---------------------------------------------------------------------- ----------
select substr(sql_text,1,70), executions from v$sqlarea where sql_text          1

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > set echo off;


The pause is in there just to slow things down enough so that the times will be different so will sort instead of being a tie.
Re: About the sql_text in V$sqlarea [message #128911 is a reply to message #128763] Thu, 21 July 2005 03:29 Go to previous message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
THANKS smartin,
I will must try this.

Thanks
Dinesh
Previous Topic: Temporary Tablespace
Next Topic: B-Tree or Bitmap Indexes
Goto Forum:
  


Current Time: Sat Jan 28 06:51:22 CST 2023