PL/SQL

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_1.sql
--
-- Purpose: Demonstrate a simple case of adaptive cursor sharing
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading off newpage none tab off serveroutput off

alter session set statistics_level = all;

alter system flush buffer_cache;

alter system flush shared_pool;

var x number

exec :x := 100000

-- pause

set timing on

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

column prev_sql_id new_value sql_id noprint
column sid new_value session_id noprint

select
prev_sql_id
, sid
from
v$session
where
sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

exec :x := 1

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

set heading on

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '&sql_id';

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75795

SQL>
SQL> set echo on linesize 200 heading off newpage none tab off serveroutput off
SQL>
SQL> alter session set statistics_level = all;

Session altered.

SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> var x number
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

SQL>
SQL> -- pause
SQL>
SQL> set timing on
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.09
SQL>
SQL> column prev_sql_id new_value sql_id noprint
SQL> column sid new_value session_id noprint
SQL>
SQL> select
2 prev_sql_id
3 , sid
4 from
5 v$session
6 where
7 sid = sys_context('userenv', 'sid');

Elapsed: 00:00:00.15
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
| 2 | VIEW | | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 13 | 0 |00:00:00.01 | 3 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:01.74
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.7361E+11

Elapsed: 00:00:17.12
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.12 | 1460K| 35371 | 10532 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:17.12 | 1460K| 35371 | 10532 | | | | |
| 2 | VIEW | | 1 | 13 | 1457K|00:00:16.94 | 1460K| 35371 | 10532 | | | | |
| 3 | WINDOW SORT | | 1 | 13 | 1457K|00:00:16.66 | 1460K| 35371 | 10532 | 93M| 3313K| 37M (1)| 84992 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 13 | 1457K|00:00:12.01 | 1460K| 24839 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 13 | 1457K|00:00:01.53 | 3221 | 3220 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.17
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.7361E+11

Elapsed: 00:00:05.10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID fmbq5ytmh0hng, child number 1
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 2719131525

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.10 | 21539 | 29966 | 8375 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.10 | 21539 | 29966 | 8375 | | | | |
| 2 | VIEW | | 1 | 1457K| 1457K|00:00:04.98 | 21539 | 29966 | 8375 | | | | |
| 3 | WINDOW SORT | | 1 | 1457K| 1457K|00:00:04.80 | 21539 | 29966 | 8375 | 74M| 2971K| 37M (1)| 67584 |
|* 4 | TABLE ACCESS FULL| T | 1 | 1457K| 1457K|00:00:01.38 | 21532 | 21528 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

24 rows selected.

Elapsed: 00:00:00.05
SQL>
SQL> set heading on
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = '&sql_id';
old 12: sql_id = '&sql_id'
new 12: sql_id = 'fmbq5ytmh0hng'
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
fmbq5ytmh0hng 0 2 3 1460762 Y N
fmbq5ytmh0hng 1 1 0 21539 Y Y

Elapsed: 00:00:00.00
SQL>

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_2.sql
--
-- Purpose: Demonstrate adaptive cursor sharing is not working with PL/SQL when
-- optimizations are enabled, in particular holding cursors open
-- See bug 8357294
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading on tab off serveroutput off

set timing on

-- This is a workaround that disables also the cursor optimizations in PL/SQL
-- In particular the PL/SQL hold cursor open optimization
-- Therefore the cursor gets reparsed and ACS kicks in
alter session set session_cached_cursors = 0;
--
-- Does this make any difference? No
-- alter session set plsql_optimize_level = 0;

variable sql_id varchar2(255)

-- Static SQL
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
select
sum(row_num)
into
n_dummy
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > x
);
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

-- dynamic SQL with binds
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
execute immediate '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
)' into n_dummy using x;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

-- dynamic SQL with literals
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
execute immediate '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > ' || x || '
)' into n_dummy;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
b.sql_id
, b.child_number
, b.executions
, b.parse_calls
, b.buffer_gets
, b.is_bind_sensitive
, b.is_bind_aware
from
v$sql a, v$sql b
where
a.force_matching_signature = b.force_matching_signature
and a.sql_id = :sql_id;

set pagesize 0

select
c.*
from
v$sql a
, v$sql b
, table(dbms_xplan.display_cursor(b.sql_id)) c
where
a.force_matching_signature = b.force_matching_signature
and a.sql_id = :sql_id;

set pagesize 9999

-- dynamic SQL with binds + OPEN
alter system flush buffer_cache;

alter system flush shared_pool;

declare
x integer;
n_dummy number;
c sys_refcursor;
begin
for i in 1..10 loop
if i = 1 then
x := 100000;
else
x := 1;
end if;
open c for '
select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
)' using x;
fetch c into n_dummy;
close c;
if i = 1 then
select
prev_sql_id
into
:sql_id
from
v$session
where
sid = sys_context('userenv', 'sid');
end if;
end loop;
end;
/

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = :sql_id;

set pagesize 0

select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));

set pagesize 9999

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75819

SQL>
SQL> set echo on linesize 200 heading on tab off serveroutput off
SQL>
SQL> set timing on
SQL>
SQL> -- This is a workaround that disables also the cursor optimizations in PL/SQL
SQL> -- In particular the PL/SQL hold cursor open optimization
SQL> -- Therefore the cursor gets reparsed and ACS kicks in
SQL> -- alter session set session_cached_cursors = 0;
SQL> --
SQL> -- Does this make any difference? No
SQL> -- alter session set plsql_optimize_level = 0;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> variable sql_id varchar2(255)
SQL>
SQL> -- Static SQL
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.63
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 select
12 sum(row_num)
13 into
14 n_dummy
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > x
24 );
25 if i = 1 then
26 select
27 prev_sql_id
28 into
29 :sql_id
30 from
31 v$session
32 where
33 sid = sys_context('userenv', 'sid');
34 end if;
35 end loop;
36 end;
37 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:50.68
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
g89tq3q521rbg 0 10 1 13146414 Y N

Elapsed: 00:00:00.17
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID g89tq3q521rbg, child number 0
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:B1)

29 rows selected.

Elapsed: 00:00:01.86
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.09
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.84
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > :x
23 )' into n_dummy using x;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:17.26
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 10 1 13146434 Y N

Elapsed: 00:00:00.14
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

31 rows selected.

Elapsed: 00:00:01.35
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with literals
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.66
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > ' || x || '
23 )' into n_dummy;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:52.29
SQL>
SQL> select
2 b.sql_id
3 , b.child_number
4 , b.executions
5 , b.parse_calls
6 , b.buffer_gets
7 , b.is_bind_sensitive
8 , b.is_bind_aware
9 from
10 v$sql a, v$sql b
11 where
12 a.force_matching_signature = b.force_matching_signature
13 and a.sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
2tgumwrfx40bu 0 1 1 53 N N
03zrk5tr7zsn4 0 9 1 193851 N N

Elapsed: 00:00:00.10
SQL>
SQL> set pagesize 0
SQL>
SQL> select
2 c.*
3 from
4 v$sql a
5 , v$sql b
6 , table(dbms_xplan.display_cursor(b.sql_id)) c
7 where
8 a.force_matching_signature = b.force_matching_signature
9 and a.sql_id = :sql_id;
SQL_ID 2tgumwrfx40bu, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 100000 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
| 2 | VIEW | | 13 | 195 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">100000)

SQL_ID 03zrk5tr7zsn4, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | | |
| 2 | VIEW | | 1457K| 20M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">1)

51 rows selected.

Elapsed: 00:00:01.24
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds + OPEN
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.66
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 c sys_refcursor;
5 begin
6 for i in 1..10 loop
7 if i = 1 then
8 x := 100000;
9 else
10 x := 1;
11 end if;
12 open c for '
13 select
14 sum(row_num)
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > :x
24 )' using x;
25 fetch c into n_dummy;
26 close c;
27 if i = 1 then
28 select
29 prev_sql_id
30 into
31 :sql_id
32 from
33 v$session
34 where
35 sid = sys_context('userenv', 'sid');
36 end if;
37 end loop;
38 end;
39 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.12
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.36
SQL>
SQL> set pagesize 9999
SQL>

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1457480 2 75821

SQL>
SQL> set echo on linesize 200 heading on tab off serveroutput off
SQL>
SQL> set timing on
SQL>
SQL> -- This is a workaround that disables also the cursor optimizations in PL/SQL
SQL> -- In particular the PL/SQL hold cursor open optimization
SQL> -- Therefore the cursor gets reparsed and ACS kicks in
SQL> alter session set session_cached_cursors = 0;

Session altered.

Elapsed: 00:00:00.00
SQL> --
SQL> -- Does this make any difference? No
SQL> -- alter session set plsql_optimize_level = 0;
SQL>
SQL> variable sql_id varchar2(255)
SQL>
SQL> -- Static SQL
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.71
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 select
12 sum(row_num)
13 into
14 n_dummy
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > x
24 );
25 if i = 1 then
26 select
27 prev_sql_id
28 into
29 :sql_id
30 from
31 v$session
32 where
33 sid = sys_context('userenv', 'sid');
34 end if;
35 end loop;
36 end;
37 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.82
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
g89tq3q521rbg 0 2 3 1460742 Y N
g89tq3q521rbg 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID g89tq3q521rbg, child number 0
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:B1)

SQL_ID g89tq3q521rbg, child number 1
-------------------------------------
SELECT SUM(ROW_NUM) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY
OBJECT_TYPE ORDER BY OBJECT_NAME) AS ROW_NUM , T.* FROM T WHERE
OBJECT_ID > :B1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :B1 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:B1)

57 rows selected.

Elapsed: 00:00:01.50
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.70
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > :x
23 )' into n_dummy using x;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.41
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.13
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.10
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with literals
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.69
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 begin
5 for i in 1..10 loop
6 if i = 1 then
7 x := 100000;
8 else
9 x := 1;
10 end if;
11 execute immediate '
12 select
13 sum(row_num)
14 from
15 (
16 select
17 row_number() over (partition by object_type order by object_name) as row_num
18 , t.*
19 from
20 t
21 where
22 object_id > ' || x || '
23 )' into n_dummy;
24 if i = 1 then
25 select
26 prev_sql_id
27 into
28 :sql_id
29 from
30 v$session
31 where
32 sid = sys_context('userenv', 'sid');
33 end if;
34 end loop;
35 end;
36 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.97
SQL>
SQL> select
2 b.sql_id
3 , b.child_number
4 , b.executions
5 , b.parse_calls
6 , b.buffer_gets
7 , b.is_bind_sensitive
8 , b.is_bind_aware
9 from
10 v$sql a, v$sql b
11 where
12 a.force_matching_signature = b.force_matching_signature
13 and a.sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
2tgumwrfx40bu 0 1 1 53 N N
03zrk5tr7zsn4 0 9 9 193851 N N

Elapsed: 00:00:00.11
SQL>
SQL> set pagesize 0
SQL>
SQL> select
2 c.*
3 from
4 v$sql a
5 , v$sql b
6 , table(dbms_xplan.display_cursor(b.sql_id)) c
7 where
8 a.force_matching_signature = b.force_matching_signature
9 and a.sql_id = :sql_id;
SQL_ID 2tgumwrfx40bu, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 100000 )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
| 2 | VIEW | | 13 | 195 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">100000)

SQL_ID 03zrk5tr7zsn4, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > 1 )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 15 | | | |
| 2 | VIEW | | 1457K| 20M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">1)

51 rows selected.

Elapsed: 00:00:01.17
SQL>
SQL> set pagesize 9999
SQL>
SQL> -- dynamic SQL with binds + OPEN
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.04
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.73
SQL>
SQL> declare
2 x integer;
3 n_dummy number;
4 c sys_refcursor;
5 begin
6 for i in 1..10 loop
7 if i = 1 then
8 x := 100000;
9 else
10 x := 1;
11 end if;
12 open c for '
13 select
14 sum(row_num)
15 from
16 (
17 select
18 row_number() over (partition by object_type order by object_name) as row_num
19 , t.*
20 from
21 t
22 where
23 object_id > :x
24 )' using x;
25 fetch c into n_dummy;
26 close c;
27 if i = 1 then
28 select
29 prev_sql_id
30 into
31 :sql_id
32 from
33 v$session
34 where
35 sid = sys_context('userenv', 'sid');
36 end if;
37 end loop;
38 end;
39 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:05.62
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = :sql_id;

SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
3snga4x0swy99 0 2 3 1460762 Y N
3snga4x0swy99 1 8 7 172312 Y Y

Elapsed: 00:00:00.16
SQL>
SQL> set pagesize 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor(:sql_id,null,'+PEEKED_BINDS'));
SQL_ID 3snga4x0swy99, child number 0
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 1399240396

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 13 | 169 | 18 (6)| 00:00:01 |
| 3 | WINDOW SORT | | 13 | 507 | 18 (6)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 13 | 507 | 17 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I | 13 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 100000

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID 3snga4x0swy99, child number 1
-------------------------------------
select sum(row_num) from (
select row_number() over (partition by object_type
order by object_name) as row_num , t.*
from t where
object_id > :x )

Plan hash value: 2719131525

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 20588 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 1457K| 18M| | 20588 (1)| 00:04:08 |
| 3 | WINDOW SORT | | 1457K| 54M| 72M| 20588 (1)| 00:04:08 |
|* 4 | TABLE ACCESS FULL| T | 1457K| 54M| | 5894 (2)| 00:01:11 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :X (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

61 rows selected.

Elapsed: 00:00:01.57
SQL>
SQL> set pagesize 9999
SQL>

--------------------------------------------------------------------------------
--
-- File name: adaptive_cursor_sharing_3.sql
--
-- Purpose: Check if the session cursor cache affects adaptive cursor sharing
--
-- Author: Randolf Geist
--
-- Last tested: December 2010
--
-- Versions: 11.1.0.7
-- 11.2.0.2
--------------------------------------------------------------------------------

set echo on define on tab off

drop table t;
purge table t;
create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);
create index i on t(object_id);

exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

set echo on

select count(*), min(object_id)
, max(object_id) from t;

set echo on linesize 200 heading off newpage none tab off serveroutput off

alter session set statistics_level = all;

-- alter session set session_cached_cursors = 0;

alter system flush buffer_cache;

alter system flush shared_pool;

var x number

exec :x := 100000

-- pause

set timing on

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

column prev_sql_id new_value sql_id noprint
column sid new_value session_id noprint

select
prev_sql_id
, sid
from
v$session
where
sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

column value new_value value_1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 100000

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

exec :x := 1

select
name
, value
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select
sum(row_num)
from
(
select
row_number() over (partition by object_type order by object_name) as row_num
, t.*
from
t
where
object_id > :x
);

select
name
, value - &value_1 as diff
from
v$statname a
, v$sesstat b
where
a.statistic# = b.statistic#
and a.name = 'session cursor cache hits'
and b.sid = sys_context('userenv', 'sid');

select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));

set heading on

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '&sql_id';

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> drop table t;

Table dropped.

SQL> purge table t;

Table purged.

SQL> create table t as select * from dba_objects, (select rownum from dual connect by level <= 20);

Table created.

SQL> create index i on t(object_id);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set echo on
SQL>
SQL> select count(*), min(object_id)
2 , max(object_id) from t;

COUNT(*) MIN(OBJECT_ID) MAX(OBJECT_ID)
---------- -------------- --------------
1385560 2 71521

SQL>
SQL> set echo on linesize 200 heading off newpage none tab off serveroutput off
SQL>
SQL> alter session set statistics_level = all;

Session altered.

SQL>
SQL> -- alter session set session_cached_cursors = 0;
SQL>
SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> var x number
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

SQL>
SQL> -- pause
SQL>
SQL> set timing on
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> column prev_sql_id new_value sql_id noprint
SQL> column sid new_value session_id noprint
SQL>
SQL> select
2 prev_sql_id
3 , sid
4 from
5 v$session
6 where
7 sid = sys_context('userenv', 'sid');

Elapsed: 00:00:00.06
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.46
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> column value new_value value_1
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5058

Elapsed: 00:00:00.01
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5058 as diff
session cursor cache hits 2

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5063

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5063 as diff
session cursor cache hits 2

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5069

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5069 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 100000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5080

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );

Elapsed: 00:00:00.01
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5080 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
| 3 | WINDOW SORT | | 1 | 12 | 0 |00:00:00.01 | 3 | 1024 | 1024 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 0 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5090

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:17.12
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5090 as diff
session cursor cache hits 3

Elapsed: 00:00:00.00
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:17.10 | 1388K| 44110 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:17.10 | 1388K| 44110 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:16.03 | 1388K| 44110 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:16.03 | 1388K| 44110 | 10005 | 88M| 3227K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:09.70 | 1388K| 24154 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:01.39 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5100

Elapsed: 00:00:00.00
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:12.94
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5100 as diff
session cursor cache hits 3

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | 88M| 3232K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:06.93 | 1388K| 24059 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:00.01 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

25 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> exec :x := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> select
2 name
3 , value
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
session cursor cache hits 5110

Elapsed: 00:00:00.01
SQL>
SQL> select
2 sum(row_num)
3 from
4 (
5 select
6 row_number() over (partition by object_type order by object_name) as row_num
7 , t.*
8 from
9 t
10 where
11 object_id > :x
12 );
2.5311E+11

Elapsed: 00:00:06.02
SQL>
SQL> select
2 name
3 , value - &value_1 as diff
4 from
5 v$statname a
6 , v$sesstat b
7 where
8 a.statistic# = b.statistic#
9 and a.name = 'session cursor cache hits'
10 and b.sid = sys_context('userenv', 'sid');
old 3: , value - &value_1 as diff
new 3: , value - 5110 as diff
session cursor cache hits 3

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'));
old 1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'ALLSTATS LAST'))
new 1: select * from table(dbms_xplan.display_cursor('fmbq5ytmh0hng',null,'ALLSTATS LAST'))
SQL_ID fmbq5ytmh0hng, child number 0
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 1399240396

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.95 | 1388K| 44001 | 10005 | | | | |
| 2 | VIEW | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | | | | |
| 3 | WINDOW SORT | | 1 | 12 | 1385K|00:00:12.29 | 1388K| 44001 | 10005 | 88M| 3232K| 55M (1)| 80896 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 1385K|00:00:06.93 | 1388K| 24059 | 0 | | | | |
|* 5 | INDEX RANGE SCAN | I | 1 | 12 | 1385K|00:00:00.01 | 3060 | 3059 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("OBJECT_ID">:X)

SQL_ID fmbq5ytmh0hng, child number 1
-------------------------------------
select sum(row_num) from ( select
row_number() over (partition by object_type order by object_name) as
row_num , t.* from t
where object_id > :x )

Plan hash value: 2719131525

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.99 | 21058 | 34648 | 7955 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.99 | 21058 | 34648 | 7955 | | | | |
| 2 | VIEW | | 1 | 1385K| 1385K|00:00:05.74 | 21058 | 34648 | 7955 | | | | |
| 3 | WINDOW SORT | | 1 | 1385K| 1385K|00:00:04.35 | 21058 | 34648 | 7955 | 69M| 2893K| 55M (1)| 64512 |
|* 4 | TABLE ACCESS FULL| T | 1 | 1385K| 1385K|00:00:00.01 | 21054 | 21049 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("OBJECT_ID">:X)

49 rows selected.

Elapsed: 00:00:00.21
SQL>
SQL> set heading on
SQL>
SQL> select
2 sql_id
3 , child_number
4 , executions
5 , parse_calls
6 , buffer_gets
7 , is_bind_sensitive
8 , is_bind_aware
9 from
10 v$sql
11 where
12 sql_id = '&sql_id';
old 12: sql_id = '&sql_id'
new 12: sql_id = 'fmbq5ytmh0hng'
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS I I
------------- ------------ ---------- ----------- ----------- - -
fmbq5ytmh0hng 0 7 8 2777315 Y N
fmbq5ytmh0hng 1 1 0 21058 Y Y

Elapsed: 00:00:00.03
SQL>

import java.sql.* ;

class JDBCQuery
{
public static void main( String args[] )
{
try
{
// Load the database driver
Class.forName( "oracle.jdbc.driver.OracleDriver" ) ;

// Get a connection to the database
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:sid",
"username", "pwd");

// Print all warnings
for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() )
{
System.out.println( "SQL Warning:" ) ;
System.out.println( "State : " + warn.getSQLState() ) ;
System.out.println( "Message: " + warn.getMessage() ) ;
System.out.println( "Error : " + warn.getErrorCode() ) ;
}

// Reset the buffer cache and shared pool
Statement ddl = conn.createStatement();
ddl.executeUpdate("alter system flush shared_pool");
ddl.executeUpdate("alter system flush buffer_cache");
ddl.close();

// Prepare the statement
String query =
"select\n" +
" sum(row_num)\n" +
"from\n" +
" (\n" +
" select\n" +
" row_number() over (partition by object_type order by object_name) as row_num\n" +
" , t.*\n" +
" from\n" +
" t\n" +
" where\n" +
" object_id > ?\n" +
" )";

PreparedStatement stmt = conn.prepareStatement(query);

System.out.println(query);

Integer i;
Integer id;

for (i = 1; i <= 10; i++)
{
if (i == 1)
{
id = 100000;
}
else
{
id = 1;
}

System.out.println( "Iteration: " + i.toString() ) ;

stmt.setInt(1, id);

long startTime = System.currentTimeMillis();

// Execute the query
ResultSet rs = stmt.executeQuery();

// Loop through the result set
while( rs.next() )
System.out.println( rs.getLong(1)) ;

// Close the result set
rs.close() ;

long endTime = System.currentTimeMillis();

float seconds = (endTime - startTime) / 1000F;

// Elapsed time
System.out.println(Float.toString(seconds) + " seconds.");
}

stmt.close() ;
conn.close() ;
}
catch( SQLException se )
{
System.out.println( "SQL Exception:" ) ;

// Loop through the SQL Exceptions
while( se != null )
{
System.out.println( "State : " + se.getSQLState() ) ;
System.out.println( "Message: " + se.getMessage() ) ;
System.out.println( "Error : " + se.getErrorCode() ) ;

se = se.getNextException() ;
}
}
catch( Exception e )
{
System.out.println( e ) ;
}
}
}

/*
Check the Shared Pool by this query:

select
sql_id
, child_number
, executions
, parse_calls
, buffer_gets
, disk_reads
, direct_writes
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = 'cq3a8ukkws0su';

*/