1 2 Previous Next 25 Replies Latest reply: Aug 16, 2013 8:41 AM by J-Oracle RSS

    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 !!  

        • 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