1 2 Previous Next 25 Replies Latest reply on Aug 16, 2013 1:41 PM by J-Oracle

    Library cache miss - Parsing in Oracle

    GTS (DBA)

      Hello Experts ;

       

       

      When a  SQL statement  is  executed,  If  it is in  the shared  pool  but  it  cannot  be shared.   It is  a   " Library  cache  miss ”. 

       

                          May i know the some specific reason , at what situation  Library  cache  miss will happen ?

       

      HR >select * From  user1.emp ;

      U1>select *   From user1.emp            

      Even if two statements are identical, this doesn't mean they are shareable -  Why so ?

                  Any specific reason   when accessing  same object  from different terminals  -  why here hard  Parse  will  happen  ??              

               

                  Please Note  : i am not asking difference between "hard parse Vs soft parse , i know well difference between them.

                  Thanks in  advance !!  

        • 1. Re: Library cache miss - Parsing in Oracle
          SomeoneElse
          1 person found this helpful
          • 2. Re: Library cache miss - Parsing in Oracle
            GTS (DBA)

            @ Thanks  somoneElse

             

                       Here also few things explained. i will come back  later !           

            http://www.oracle.com/technetwork/issue-archive/o42asktom-083098.htmlAsk Tom

            • 3. Re: Library cache miss - Parsing in Oracle
              SAYAN_RMAN

              hi

               

              if u change this optimizer_index_cost_adj parameter it will reduce or enhance FTS and Index scan.then there is possibilities that it may not share cursor for same type of query.It also depends on bind variables are used  or not.


              alter session set optimiser_index_cost_adj=3
              


              HTH

              • 4. Re: Library cache miss - Parsing in Oracle
                Alvaro

                Oracle DOES share the parsed statement if the statements are the same. This is the basis of correct usage of the Shared Pool.

                 

                What is likely is that your statement is being aged out of the shared pool between executions.

                 

                Did a small demonstration on my personal R&D 12C, but it's the same behavior on 11g:

                 

                FIrst logged in SYS:

                 

                SQL> show user

                USER is "SYS"

                SQL> set autotrace traceonly;

                SQL> select count(*) from MAXIMO.WORKLOG;

                 

                 

                 

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 1140622091

                 

                 

                ------------------------------------------------------------------------------

                | Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |

                ------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |

                |   1 |  SORT AGGREGATE       |              |     1 |            |          |

                |   2 |   INDEX FAST FULL SCAN| WORKLOG_NDX1 |  9516 |     7   (0)| 00:00:01 |

                ------------------------------------------------------------------------------

                 

                 

                 

                 

                Statistics

                ----------------------------------------------------------

                        101  recursive calls

                          0  db block gets

                        174  consistent gets

                         33  physical reads

                          0  redo size

                        544  bytes sent via SQL*Net to client

                        544  bytes received via SQL*Net from client

                          2  SQL*Net roundtrips to/from client

                          6  sorts (memory)

                          0  sorts (disk)

                          1  rows processed

                 

                 

                101 recursive calls shows we did the parse, now I repeat as SYS:

                 

                 

                 

                SQL> select count(*) from MAXIMO.WORKLOG;

                 

                 

                 

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 1140622091

                 

                 

                ------------------------------------------------------------------------------

                | Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |

                ------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |

                |   1 |  SORT AGGREGATE       |              |     1 |            |          |

                |   2 |   INDEX FAST FULL SCAN| WORKLOG_NDX1 |  9516 |     7   (0)| 00:00:01 |

                ------------------------------------------------------------------------------

                 

                 

                 

                 

                Statistics

                ----------------------------------------------------------

                          0  recursive calls

                          0  db block gets

                         30  consistent gets

                          0  physical reads

                          0  redo size

                        544  bytes sent via SQL*Net to client

                        544  bytes received via SQL*Net from client

                          2  SQL*Net roundtrips to/from client

                          0  sorts (memory)

                          0  sorts (disk)

                          1  rows processed

                 

                 

                SQL> show user

                USER is "SYS"

                 

                 

                0 recursive calls shows we reused the statement in the shared pool. Now I log in with user MAXIMO:

                 

                 

                SQL> select count(*) from MAXIMO.WORKLOG;

                 

                 

                 

                 

                Execution Plan

                ----------------------------------------------------------

                Plan hash value: 1140622091

                 

                 

                ------------------------------------------------------------------------------

                | Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |

                ------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |

                |   1 |  SORT AGGREGATE       |              |     1 |            |          |

                |   2 |   INDEX FAST FULL SCAN| WORKLOG_NDX1 |  9516 |     7   (0)| 00:00:01 |

                ------------------------------------------------------------------------------

                 

                 

                 

                 

                Statistics

                ----------------------------------------------------------

                          0  recursive calls

                          0  db block gets

                         30  consistent gets

                          0  physical reads

                          0  redo size

                        544  bytes sent via SQL*Net to client

                        544  bytes received via SQL*Net from client

                          2  SQL*Net roundtrips to/from client

                          0  sorts (memory)

                          0  sorts (disk)

                          1  rows processed

                 

                 

                SQL> show user

                USER is "MAXIMO"

                 

                 

                0 recursive calls shows that user MAXIMO also used the same parse I did previously as SYS, as documentation says.

                • 5. Re: Library cache miss - Parsing in Oracle
                  Aman....

                  You have got two different schemas. Have a good read of the below link,

                   

                  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2588723819082

                   

                  Aman....

                  • 7. Re: Library cache miss - Parsing in Oracle
                    GTS (DBA)

                    @ aman ;

                     

                    Parsing sql - Parent and child cursor  well explained.

                                      could  you eloborate little more : 

                    Child object is comprised of two heaps numbered 0 and 6. Heap 0

                               Concept  is hardly explained  :   Ixora Answers - Parent and child cursors in the library cache 

                    • 8. Re: Library cache miss - Parsing in Oracle
                      GTS (DBA)

                      @ someoneeElse

                       

                      I  am sorry for being late reply. I  am seeing severl  OTN  threads related to  child cursor.  

                      They are helping me  to  learn  more quickly. 

                       

                      Hmm ..  i have a doubt  ..  ( i'm n't asking  abt  parsing :  one of my  thread ,  you well  explained recently)

                      i want to take little deep insight about child cursors.

                       

                      If existing children can’t be reused  (e.g. because of different size of  bind variables, or because of different optimizer settings, or because of different  NLS setting etc.),

                      there  will be  “HARD  PARSE”

                       

                      Ques

                      " because of different size of  bind variables"  - what  does it mean ?

                      I know about bind variables  -  ( different size of  bind variables) - Please clarify with simple example .


                        Thanks in advance !

                      • 9. Re: Library cache miss - Parsing in Oracle
                        jgarry

                        a6152e9a-1e2f-4272-8553-01edbfa4b772 wrote:

                         

                        hi

                         

                        if u change this optimizer_index_cost_adj parameter it will reduce or enhance FTS and Index scan.then there is possibilities that it may not share cursor for same type of query.It also depends on bind variables are used  or not.


                        1. alter session set optimiser_index_cost_adj=3 


                        HTH

                        I'm wondering where you got this advice?  It seems old, and crude. See Optimizer_index_cost_adj | Oracle Scratchpad

                         

                        Speaking of old, the ixora answers are good in context, but they tend to be very old.  The internals have evolved greatly since they were written.  Sometimes you can find more recent answers on Tanel's website, like child cursor | Tanel Poders blog: IT & Mobile for Geeks and Pros (there are more appropriate posts there, but you'll have to find them yourself)

                        • 10. Re: Library cache miss - Parsing in Oracle
                          GTS (DBA)

                          @ jagarry

                           

                          Can  i all information for parent cursor ?

                           

                          When i surf in Google ,  they mentioned to check  v$sqlarea

                          but i  could n't see any information related to parent cursor.

                          • 11. Re: Library cache miss - Parsing in Oracle
                            sb92075

                            If you eliminated every Library Cache Miss, how much faster would the application/database be?

                             

                            you are wasting time & effort on totally irrelevant minutia.

                            • 12. Re: Library cache miss - Parsing in Oracle
                              GTS (DBA)

                              @sb

                               

                              If you eliminated every Library Cache Miss, how much faster would the application/database be?

                                        i  never said (what you mentioned)  or   i don't  understand  what  do you mean ?  

                               


                              QUES :  

                              Could you please solve my question ?

                              I  have child a  cursor , so  definitely  one parent cursor  being here.

                              where can i see  associated information for  parent  cursor.


                                            I have collected all information's from old OTN  threads.  This is only question  Google also not answered.

                                            i tried with v$sqlarea -  nothing is there about parent cursor.

                                          

                              Thanks !

                              • 13. Re: Library cache miss - Parsing in Oracle
                                SomeoneElse

                                I've looked through the recent posts in this thread and I'm not sure what your latest question is.

                                1 2 Previous Next