Home » RDBMS Server » Performance Tuning » About the sql_text in V$sqlarea
 About the sql_text in V$sqlarea [message #128536] |
Tue, 19 July 2005 05:07  |
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 #128763 is a reply to message #128536] |
Wed, 20 July 2005 08:55  |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:57:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
