Home » SQL & PL/SQL » SQL & PL/SQL » Cursor_sharing (Oracle9i)
Cursor_sharing [message #327501] Mon, 16 June 2008 11:13 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
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..


Re: Cursor_sharing [message #328236 is a reply to message #327501] Thu, 19 June 2008 07:53 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
I ran the tkprof for my queries. The real execution plan shows correctly based on the cursor_sharing parameter(FORCE/SIMILAR).

Cursor sharing='FORCE'

select count(ename) from employee where empno=7934;

select count(ename) 
from
employee where empno=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.31       0.31          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.79       4.29      11851      11861          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.10       4.60      11851      11861          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
2097152   TABLE ACCESS FULL OBJ#(30800) 



select count(ename) from employee where empno=7902;

select count(ename) 
from
employee where empno=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.71       3.84      11120      11861          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.71       3.84      11120      11861          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS FULL OBJ#(30800) 



Cursor_sharing='SIMILAR'

select count(ename) from employee where empno=7934;

select count(ename) 
from
employee where empno=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.84       4.10      11131      11861          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.84       4.10      11131      11861          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
2097152   TABLE ACCESS FULL OBJ#(30800) 



select count(ename) from employee where empno=7902

select count(ename) 
from
employee where empno=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS BY INDEX ROWID OBJ#(30800) 
      1    INDEX RANGE SCAN OBJ#(30801) (object id 30801)

Previous Topic: Explain Plan Vs set autotrace Vs Tkprof
Next Topic: comparing data in the same field and table
Goto Forum:
  


Current Time: Sat Dec 03 06:10:35 CST 2016

Total time taken to generate the page: 0.14003 seconds