Hi
I see the number of fetches and executions to be same in V$SQL while using a select statement with bind variables.
However the number of fetches is just one and executions many in V$SQL when running an insert statement with bind variables.
Could you please enlighten why this is so.?
I was expecting to see one fetch and many executions in the SELECT as well.
create table t3(x int);
insert into t3 select level from dual connect by level<=10;
commit;
declare
l_cnt number;
begin
for i in 1..10
loop
execute immediate 'SELECT /*select_with_bind*/COUNT(*) FROM T3 WHERE x=:y' into l_cnt using i ;
end loop;
for i in 1..10
loop
execute immediate 'SELECT /*select_no_bind*/COUNT(*) FROM T3 WHERE x='||i into l_cnt;
end loop;
end;
/
select t.fetches
,t.executions
,t.sql_text
from v$sql t
where 1=1
and (sql_text like '%select_with_bind%'
or sql_text like '%select_no_bind%'
)
and parsing_schema_name <> user /*I am running in dba schema.Using this condition since i dont want to select the sql statement used for the query.*/
order by t.fetches desc
--output
fetches executions sql_text
50 50 SELECT /*select_with_bind*/COUNT(*) FROM T3 WHERE x=:y /* There are 50 fetches here when using bind*/
1 1 SELECT /*select_no_bind*/COUNT(*) FROM T3 WHERE x=8
1 1 SELECT /*select_no_bind*/COUNT(*) FROM T3 WHERE x=7
1 1 SELECT /*select_no_bind*/COUNT(*) FROM T3 WHERE x=6
.... and some more rows...
-- Checking for inserts using binds.
declare
l_cnt number;
begin
for i in 10..20
loop
execute immediate 'insert /*insert_with_bind*/into T3 values(:y)' using i;
end loop;
for i in 10..20
loop
execute immediate 'insert /*insert_no_bind*/into T3 values('||i||')';
end loop;
end;
/
select t.fetches
,t.executions
,t.sql_text
from v$sql t
where 1=1
and (sql_text like '%insert_with_bind%'
or sql_text like '%insert_no_bind%'
)
and parsing_schema_name <> user /*I am running in dba schema.Using this condition since i dont want to select the sql statement used for the query.*/
order by t.executions desc
--output...
fetches executions sql_text
0 11 insert /*insert_with_bind*/into T3 values(:y)
0 1 insert /*insert_no_bind*/into T3 values(11)
.. and some more rows...
An insert has no fetches because there's nothing to fetch!
Fetching is the process of returning rows from your result set to the client. With insert, update and delete there's nothing to return. So fetches will be zero for these statements as you've observed.
It's possible to have a select statement that you execute but don't fetch from. But that means you don't have the results, which is kind of pointless. If this is happening a lot, it suggests there's something wrong with your app!
You can have this if you open cursors that you do nothing with:
declare
cur sys_refcursor;
var int;
begin
open cur for
select /*+ ref_cur_exec_no_fetch */* from t3;
end;
/
select t.fetches
,t.executions
,t.sql_text
from v$sql t
where 1=1
and sql_text like 'SELECT%ref_cur_e%'
and sql_text not like '%not this%'
order by t.executions desc ;
FETCHES EXECUTIONS
---------- ----------
SQL_TEXT
--------------------------------------------
0 1
SELECT /*+ ref_cur_exec_no_fetch */* FROM T3
It's much more likely that you'll execute a select once and fetch from it multiple times:
declare
cur sys_refcursor;
var int;
begin
open cur for
select /*+ ref_cur_exec_and_fetch */* from t3;
loop
fetch cur into var;
exit when cur%notfound;
end loop;
end;
/
select t.fetches
,t.executions
,t.sql_text
from v$sql t
where 1=1
and sql_text like 'SELECT%ref_cur_e%'
and sql_text not like '%not this%'
order by t.executions desc ;
FETCHES EXECUTIONS
---------- ----------
SQL_TEXT
---------------------------------------------
0 1
SELECT /*+ ref_cur_exec_no_fetch */* FROM T3
33 1
SELECT /*+ ref_cur_exec_and_fetch */* FROM T3