Re: Re: cursor_sharing setting

In theory the optimizer should be eble to use the correct indexes. It should create multiple plans (aka childs) each fitting to a range of peaked values. In reality I have seen this not working even in very clear cases where the feature was absolutely needed. In this case you can call it a shortcomming at least or a bug. With the partition statis and bind values for the partition key it is something else. Since the optimizer at parse time would not know which value would be provided at runtime, IMHO partition stats can never be used at parse time. That is not shortcomming, it is unavoidable. It would use table level stats which can be desasterous in some cases. Regards
----Ursprüngliche Nachricht----
Von : andrew.kerber_at_gmail.com
Datum : 10/02/2020 - 20:30 (MN)
An : carlospena999_at_gmail.com
Cc : gogala.mladen_at_gmail.com, oracle-l_at_freelists.org Betreff : Re: cursor_sharing setting
 _optim_peek_user_binds. When set to true, the first time a query is parsed, Oracle will look at the values behind the bind variables to determine the optimum plan. When false, will not peek. This setting can be problematic when combined with cursor_sharing=force if by some chance the first run of the query has unusual distribution on an index. eg, you are looking for a very common value of an index for the initial query, which can result in an FTS, when more commonly the query is used to find an uncommon or even unique value in the index, which would produce an index scan.    

  On Mon, Feb 10, 2020 at 1:16 PM Cee Pee <   carlospena999_at_gmail.com> wrote:          

   Thanks everyone.                 

    Andrew:                     

    NAME                                          VALUE                    DEFLT
    
--------------------------------------------- ------------------------ ------------------------
    
_optim_peek_user_binds                        TRUE                     TRUE
    
   
   
    
   
   
    
   
   

    "Bind varaibles for a partition key should     inhibit from partion stats being used on parsing." I think the optimizer may not know to use indexes or not, etc. which applies even to an OLTP system. I remember reading about those things in Christian's TOP book. (Thank you Christian if you are reading this thread, a great book).                     

    what does that parameter do? I couldnt get any good results searching. Does it let the optimizer peek at bind variable values once a while?                                    

    On Mon, Feb 10, 2020 at 11:24 AM Andrew Kerber <     andrew.kerber_at_gmail.com> wrote:                  

     
      What is the _optim_peek_user_binds setting? 
      
     
     
      
     
     
      In any case, if the warehouse has been running for some time without serious problem, you probably dont want to change that setting without understanding why it was set the way it was to begin with.  Of course, the answer is likely to be that it was set that way 10 years ago in oracle 9i and never changed it, but it is still not something to change without testing.
      
     
    
    
    
     
      On Mon, Feb 10, 2020 at 11:13 AM Mladen Gogala <
      gogala.mladen_at_gmail.com> wrote:
      
     
     
      I would not set it anywhere. CS = FORCE is a source of many optimizer 
      
 bugs and is only recommended in case of an emergency, where one needs to 
      
 break the glass first.
      
      
 Regards
      
      
      
 On 2/10/20 7:54 AM, 
      l.flatz_at_bluewin.ch wrote:
      


 > Hi,

      


 >

      


 > I would not set cs=forced in a DWH. These a logrunning queries and the 


      


 > plans should be as  good as possible.

      


 > Parse time will normally be a lesser concern.

      


 > Just to give an example: Bind varaibles for a partition key should 


      


 > inhibit from partion stats being used on parsing.

      


 >

      


 > Regards

      


 >

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

-- 
    
    
     Andrew W. Kerber
     
     
'If at first you dont succeed, dont take up skydiving.'
    
   
  
 
-- 
 Andrew W. Kerber
 
 
'If at first you dont succeed, dont take up skydiving.'


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