I have a question in CURSOR_SHARING parameter.
I would be pleased if any one could help me on this.
Here is the oracle version i am using
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
I have a table employee and here is the record count.
SQL> select count(*),empno from employee group by empno;
COUNT(*) EMPNO
---------- ----------
1 7902
2097152 7934
SQL> CREATE INDEX IDX ON EMPLOYEE(EMPNO);
Index created.
The employee table is analyzed as below
SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMPLOYEE',ESTIMATE_PERCEN
T => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);
PL/SQL procedure successfully completed.
Question 1
I am using CURSOR_SHARING=FORCE Parameter.
My understanding here is, when we use FORCE, oracle should use the same execution plan for different literal values.
SQL> select count(*) from employee where empno=7902;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX' (NON-UNIQUE) (Cost=3 Card=1
Bytes=4)
SQL> select count(*) from employee where empno=7934;
COUNT(*)
----------
2097152
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=424 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX' (NON-UNIQUE) (Cost=424 C
ard=2098320 Bytes=8393280)
When we use the query empno=7934, optimizer use fast full scan.
When i use the query empno=7902, optimizer use range scan.
My question is, why optimizer use different execution plan for cursor_sharing=FORCE. It should force the same execution plan for same query with different literal values..
Question 2
Here i am using CURSOR_SHARING=SIMILAR.
SQL> ALTER system set cursor_sharing=similar scope=both;
System altered.
SQL> select value from v$parameter where name='cursor_sharing';
VALUE
--------------------------------------------------------------------------------
SIMILAR
My understanding here is, when we use query empno=7934, oracle should use full table scan and when we use query empno=7902, optimizer should use index range scan. But the case here.
SQL> select count(*) from employee where empno=7902;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IDX' (NON-UNIQUE) (Cost=3 Card=1
Bytes=4)
SQL> select count(*) from employee where empno=7934;
COUNT(*)
----------
2097152
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=424 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX' (NON-UNIQUE) (Cost=424 C
ard=2098320 Bytes=8393280)
SQL>
I would appreciate if any one can reply for this..