Home » SQL & PL/SQL » SQL & PL/SQL » Cursor_sharing=similar (Oracle9i)
Cursor_sharing=similar [message #328775] Sun, 22 June 2008 14:18 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I am using the below Oracle version.

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 am using CURSOR_SHARING=SIMILAR. I ran bunch of queries after i changed the CURSOR_SHARING parameter.


SQL> select count(*),empno from employee group by empno;

  COUNT(*)      EMPNO
---------- ----------
         1       7902
   2097152       7934
         1       9996
         1       9997
         1       9998
         1       9999

6 rows selected.

SQL> 

SQL> alter system flush shared_pool;

System altered.

SQL> alter system set cursor_sharing=similar;

System altered.

SQL> alter system set cursor_sharing=similar scope=both;

System altered.

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

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9996;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9997;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9998;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9999;

COUNT(ENAME)
------------
           1

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

COUNT(ENAME)
------------
     2097152

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

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9996;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9997;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9998;

COUNT(ENAME)
------------
           1

SQL> select count(ename) from employee where empno=9999;

COUNT(ENAME)
------------
           1

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

COUNT(ENAME)
------------
     2097152



My understanding is, oracle should generate only two execution plan and reuse the plan for subsequent queries.

One plan should use for the below queries.

select count(ename) from employee where empno=7902;
select count(ename) from employee where empno=9996;
select count(ename) from employee where empno=9997;
select count(ename) from employee where empno=9998;
select count(ename) from employee where empno=9999;


Another plan should use the below query

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


Please correct me if i am wrong. Thanks

Report message to a moderator

Re: Cursor_sharing=similar [message #328778 is a reply to message #328775] Sun, 22 June 2008 14:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHY, WHY, WHY do you post this type of question here as opposed to simply testing & see the answer for yourself?

Report message to a moderator

Re: Cursor_sharing=similar [message #328780 is a reply to message #328775] Sun, 22 June 2008 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And use ALTER SESSION if you want to test on a session.

Quote:
My understanding is, oracle should generate only two execution plan

Why?

Regards
Michel

Report message to a moderator

Re: Cursor_sharing=similar [message #328802 is a reply to message #328780] Sun, 22 June 2008 22:45 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
anacedent, I tested the result before i post in this forum. I do google and search in this fourms before i ask any question. I do not want to burn anybodies time in this forum. I should have posted my test result when i ask question. But that was my bad. If you feel that, you are not comfortable to respond, please not to respond for this thread. I am sorry for the inconvenience. I appreciate your time for answering this thread.

Michel, here is the test i have done before i create this thread.

SQL> alter system flush shared_pool;

System altered.

SQL> select value from v$parameter where name='cursor_sharing';

VALUE
----------------------------------------------------------------------------------------------------
SIMILAR

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

COUNT(ENAME)
------------
           1

SQL> select count(ename)  from employee where empno=9996;

COUNT(ENAME)
------------
           1

SQL> select count(ename)  from employee where empno=9997;

COUNT(ENAME)
------------
           1

SQL> select count(ename)  from employee where empno=9998;

COUNT(ENAME)
------------
           1

SQL> select count(ename)  from employee where empno=9999;

COUNT(ENAME)
------------
           1

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

COUNT(ENAME)
------------
     2097152

SQL> select lpad(' ', 2*(level-1))||operation||' '||
  2  decode(id, 0, 'Cost = '||position) "OPERATION",
  3  options, object_name
  4  from v$sql_plan
  5  where address in(SELECT address  from v$sql_plan where hash_value in(
  6  select hash_value from v$sql where sql_text 
  7  like  'select count(ename)  from employee where empno%'))
  8  and
  9  hash_value in(SELECT  hash_value from v$sql_plan where hash_value in(
 10  select hash_value from v$sql where sql_text 
 11  like  'select count(ename)  from employee where empno%'))
 12  /

OPERATION                                                                                                       
----------------------------------------------------------------------------------------------------
SELECT STATEMENT Cost = 0
SORT                                                                                                            
TABLE ACCESS                                                                                                    

SQL>


In the above output of v$sql_plan(i tried to add connect by prior. but the query ran for ever. Then i removed the connect by and result was returned immediatley.) shows me that oracle generated only one execution plan.

But i am execting oracle to generate two execution plan. Let me explain WHY?

When i run the below queries, query returns only one record. So optimizer should go for index path. 

select count(ename)  from employee where empno=7902;
select count(ename)  from employee where empno=9996;
select count(ename)  from employee where empno=9997;
select count(ename)  from employee where empno=9998;
select count(ename)  from employee where empno=9999;


When i run the below query, oracle should generate full table scan. Because, this query returns 99.99 percentage of rows.


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

COUNT(ENAME)
------------
     2097152



So based on these, i am expecting two plans(one is index path and another one is FTS).

Please correct me if i am wrong...

Thanks

Report message to a moderator

Re: Cursor_sharing=similar [message #328803 is a reply to message #328775] Sun, 22 June 2008 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We don't have your table.
We don't have your data.
(We don't have your buggy & UNSUPPORTED version.)
We have ONLY what you post.


You post only a single plan, therefore I conclude only 1 plan is generated.

>shows me that oracle generated only one execution plan.
Rhetorical question - how/why can a single plan
"the query ran for ever. Then i removed the connect by and result was returned immediatley."

I would expect the same plan to give the same results; both in number of rows returned & elapsed time.


SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> SELECT ...........

[Updated on: Sun, 22 June 2008 23:05] by Moderator

Report message to a moderator

Re: Cursor_sharing=similar [message #328812 is a reply to message #328802] Sun, 22 June 2008 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But i am execting oracle to generate two execution plan. Let me explain WHY?

WHY do you expect that?
Explain the reasons of this expectation.

Regards
Michel

Report message to a moderator

Re: Cursor_sharing=similar [message #329250 is a reply to message #328812] Tue, 24 June 2008 13:10 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member

I will research on this and update my comments/answers. I appreciate all your answers. Thank you.

Report message to a moderator

Previous Topic: Password Invalid
Next Topic: Any ideas how to concatenate two fields depending on length?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 18 12:34:59 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.