create table emp_t

as select * from emp;

create index emp_empno_idx on emp_t(empno);

analyze table emp_t compute statistics

for table for all indexes for columns empno size 1;

show parameters cursor_sharing;

alter session set cursor_sharing = force;

alter system flush shared_pool;

set serveroutput on;

declare

   l_condition varchar2(20);

   l_ename emp_t.ename%type;

  begin

   for c in ( select empno from emp_t )

   loop

     l_condition := 'empno = '|| c.empno;

     execute immediate 'select ename ' || 'from emp_t where ' || l_condition

          into l_ename;

     dbms_output.put_line( l_condition ||' : ' || l_ename );

   end loop;

end;

/

select sql_id, sql_text, version_count

from v$sqlarea

where sql_text like 'select ename%';

select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, parse_calls, executions, fetches

from v$sql

where sql_text like 'select ename%';

SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

6y7xpur0d4jty 0 3220957464 1 12 12 12

(alter session set cursor_sharing = force;를 사용하지 않으면 아래 결과가 나온다.)

SQL> select sql_id, child_number chld_no, plan_hash_value plan_hash, loads, pars

fetches

2 from v$sql

3 where sql_text like 'select ename%';

SQL_ID CHLD_NO PLAN_HASH LOADS PARSE_CALLS EXECUTIONS FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

7btg75m1un6mh 0 3220957464 1 1 1 1

4z85t036cstxu 0 3220957464 1 1 1 1

8t1hstds8svrg 0 3220957464 1 1 1 1

1p0dw95jhp6f1 0 3220957464 1 1 1 1

4k5uyn3kq1jkf 0 3220957464 1 1 1 1

1qsw3fn3j5pqr 0 3220957464 1 1 1 1

2kwq0tcdfa9b4 0 3220957464 1 1 1 1

9a3p6t37v2q3d 0 3220957464 1 1 1 1

2xauzardzur0f 0 3220957464 1 1 1 1

72127hc1t6zdw 0 3220957464 1 1 1 1

d4sqtxgazkzns 0 3220957464 1 1 1 1

9mjc6sbjubgsc 0 3220957464 1 1 1 1