Online Contents

Oracle Q&A‎ > ‎제 2 차‎ > ‎

01. many child cursors

--------------------------------------------------------------------------------
-- @name: many child cursors
-- @author: dion cho
-- @note: test case for the many child cursors for the same SQL
--------------------------------------------------------------------------------

set pages 100
set trimspool on
set timing off
set echo off


--------------------------------------------------------------------
-- case1: bind length mismatch

-- create table
drop table t1 purge;

create table t1(
c1 varchar2(4000),
c2 varchar2(4000),
c3 varchar2(4000),
c4 varchar2(4000),
c5 varchar2(4000)
);


-- define bind variables
var b1 varchar2(10);
var b2 varchar2(10);
var b3 varchar2(10);
var b4 varchar2(10);
var b5 varchar2(10);

begin
:b1 := 'x';
:b2 := 'x';
:b3 := 'x';
:b4 := 'x';
:b5 := 'x';
end;
/

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

col prev_sql_id new_value prev_sql_id
select 
prev_sql_id,
(select sql_text from v$sqlarea
where sql_id = prev_sql_id) as sql_text
from
v$session
where
sid = userenv('sid')
;


select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1
var b1 varchar2(100);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b2
var b2 varchar2(100);
exec :b2 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b3
var b3 varchar2(100);
exec :b3 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b4
var b4 varchar2(100);
exec :b4 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b5
var b5 varchar2(100);
exec :b5 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1 *more*
var b1 varchar2(1000);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- this is crazy, isn't it?


--------------------------------------------------------------------------
-- v$sql_shared_cursor would help

desc v$sql_shared_cursor

-- this sucks
select
*
from
v$sql_shared_cursor
where
sql_id = '&prev_sql_id'
;

-- this is good
@ed c:\sqlplus\common\shared_cursor2.sql

@shared_cursor2 &prev_sql_id


-- do the same test on 10gR2

--------------------------------------------------------------------------
-- does 10503 diag event work?
alter system flush shared_pool;

select * from table(tpack.oerr(10503));

alter session set events '10503 trace name context forever, level 4000';

-- define bind variables
var b1 varchar2(10);
var b2 varchar2(10);
var b3 varchar2(10);
var b4 varchar2(10);
var b5 varchar2(10);

begin
:b1 := 'x';
:b2 := 'x';
:b3 := 'x';
:b4 := 'x';
:b5 := 'x';
end;
/

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

col prev_sql_id new_value prev_sql_id
select 
prev_sql_id,
(select sql_text from v$sqlarea
where sql_id = prev_sql_id) as sql_text
from
v$session
where
sid = userenv('sid')
;


select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1
var b1 varchar2(100);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b2
var b2 varchar2(100);
exec :b2 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b3
var b3 varchar2(100);
exec :b3 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b4
var b4 varchar2(100);
exec :b4 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b5
var b5 varchar2(100);
exec :b5 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1 *more*
var b1 varchar2(1000);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

alter session set events '10503 trace name context off';

-- unfortunately, 10503 does not work here!

--------------------------------------------------------------------------
-- how about use "big value" first
alter system flush shared_pool;

-- define bind variables(with big size)
var b1 varchar2(1000);
var b2 varchar2(1000);
var b3 varchar2(1000);
var b4 varchar2(1000);
var b5 varchar2(1000);

begin
:b1 := 'x';
:b2 := 'x';
:b3 := 'x';
:b4 := 'x';
:b5 := 'x';
end;
/

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

col prev_sql_id new_value prev_sql_id
select 
prev_sql_id,
(select sql_text from v$sqlarea
where sql_id = prev_sql_id) as sql_text
from
v$session
where
sid = userenv('sid')
;


select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1
var b1 varchar2(100);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b2
var b2 varchar2(100);
exec :b2 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b3
var b3 varchar2(100);
exec :b3 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;

-- increase b4
var b4 varchar2(100);
exec :b4 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b5
var b5 varchar2(100);
exec :b5 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;


-- increase b1 *more*
var b1 varchar2(1000);
exec :b1 := 'x';

-- insert statement
insert into t1(c1, c2, c3, c4, c5)
values(:b1, :b2, :b3, :b4, :b5);

-- check child cursors
select
sql_id,
child_number
from
v$sql
where
sql_id = '&prev_sql_id'
;



------------------------------------------------------------
-- case2: different schemas "but" same SQL

alter system flush shared_pool;

-- session #1
conn tpack/tpack@ukja1120

-- create table
drop table t1 purge;

create table t1(c1 varchar2(100));


-- session #2 as different user
conn ukja/ukja@ukja1120

-- create table
drop table t1 purge;

create table t1(c1 varchar2(100));


-- session #1
-- as user #1
var b1 varchar2(10);
exec :b1 := 'x';

select /*+ placeholder */ 
*
from
t1
where
c1 = :b1
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


-- session #2
-- as user #2
var b1 varchar2(10);
exec :b1 := 'x';

select /*+ placeholder */ 
*
from
t1
where
c1 = :b1
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


-- shared cursor
@shared_cursor 'select /*+ placeholder */%'


-- session #1
var b1 varchar2(1000);
exec :b1 := 'x';

select /*+ placeholder */ 
*
from
t1
where
c1 = :b1
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


-- session #2
var b1 varchar2(1000);
exec :b1 := 'x';

select /*+ placeholder */ 
*
from
t1
where
c1 = :b1
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


-- shared cursor
@shared_cursor 'select /*+ placeholder */%'


-- very annoying, isn't it?


------------------------------------------------------------
-- case3: cursor_sharing = similar
alter system flush shared_pool;

-- create table
drop table t1 purge;

create table t1(c1 number);

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


-- with hard parse, cursors are not shared
alter session set cursor_sharing = exact;

select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 1
;

select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 2
;


select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 3
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


-- for cursor_sharing = similar, cursors are shared
alter system flush shared_pool;
alter system flush shared_pool;

alter session set cursor_sharing = similar;

-- note that this is *RANGE* predicate
select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 1
;

select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 2
;


select /*+ placeholder */ 
*
from
t1
where
c1 between 1 and 3
;

select
sql_id,
child_number
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;

select
sql_text
from
v$sql
where
sql_text like 'select /*+ placeholder */%'
;


@shared_cursor 'select /*+ placeholder */%'

-- same test on 10gR2
Comments