Re: cursor sharing

From: <genegurevich_at_discoverfinancial.com>
Date: Tue, 20 Jun 2006 12:46:24 -0500
Message-ID: <OF94749C16.59DD11B7-ON86257193.0061949B-86257193.00618E76@discoverfinancial.com>

Yes, you are right. The plan I got from sql_plan is different from the one I got via explain plan.

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

                                                                           
             "Ghassan Salem"                                               
             <salem.ghassan_at_gm                                             
             ail.com>                                                   To 
             Sent by:                  genegurevich_at_discoverfinancial.com  
             oracle-l-bounce_at_f                                          cc 
             reelists.org              oracle-l <oracle-l_at_freelists.org>   
                                                                   Subject 
                                       Re: cursor sharing                  
             06/20/2006 12:31                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             salem.ghassan_at_gma                                             
                  il.com

Well,
you better get the plan from v$sql_plan, as the explain plan does not do bind-peeking (when you use the cursor_sharing=force setting). so the plan given by explain can be different from what you have when you actually execute the statement (depeding on bind values, whether this is the first time you execute the statement or not , and the presence of histograms)

rgds

On 6/20/06, genegurevich_at_discoverfinancial.com < genegurevich_at_discoverfinancial.com> wrote: No. I am using the following to get the explain plan:

select plan_table_output from
table(dbms_xplan.display('PLAN_TABLE',null,'ALL')) (found it on metalink)

thank you

  Gene Gurevich
  Oracle Engineering
  224-405-4079

               "goran
               bogdanovic"
               <goran00_at_gmail.co
  To
               m>

genegurevich_at_discoverfinancial.com

               06/20/2006 04:20          oracle-l <oracle-l_at_freelists.org >
               AM
  Subject
                                         Re: cursor sharing

Did you checked in v$sql_plan the real execution plan with CURSOR_SHARING_EXACT ? genegurevich_at_discoverfinancial.com wrote:

Hi all:

Can someone recommend a good article on the cursor sharing in Oracle

        9. I
        just found a query that runs in 5 seconds
         with CURSOR_SHARING_EXACT and in more than 1 hour without it
        (cursor_sharing is set to force on the DB level)

        I'm trying to find out why it happens.

        thank you

        Gene Gurevich


        --
        http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 20 2006 - 12:46:24 CDT