Oracle Scratchpad

July 3, 2007

Parse Calls

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:55 pm GMT Jul 3,2007

Here’s an extract from a report of activity in an Oracle session that I’ve just been running. Spot the anomaly:

Name                           Value
----                           -----
session cursor cache hits          3
parse count (total)                5
parse count (hard)                31
execute count                     35

There are no tricks involved with this output, though the database activity is a little unusual, and I haven’t faked any numbers. So how come I’ve got more “hard parses” than “parses” ?

I really hate the expression “hard parse”, I’m not keen on “soft parse”, and I get irritated by the frequent failure to distinguish the difference between “parse calls” and “parsing”.

The oddity of the statistics I’ve quoted can be explained very easily  – the ‘parse count (hard)’ should really be labelled ‘optimizations’, and optimisation may be required for an ‘execute call’ as well as for a ‘parse call’.

The ‘parse count (total)’ should really be labelled ‘parse calls’, and there are parse calls (the ones met through the session cursor cache) which don’t require any parsing (optimisation) to take place.

All I’ve done in my demonstration is run a small pl/sql block:

declare 
        m_n number; 
begin 
        for i in 1..30 loop 
                select  count(*) 
                into    m_n 
                from    t1; 

                dbms_lock.sleep(1); 
        end loop; 
end; 
/ 

But note the call to dbms_lock.sleep. As this block executed, I issued a truncate statement against table t1 once per second from another session.

Because I’m operating from a pl/sql block, the ‘select’ cursor is automatically held in the pl/sql cursor cache – so I don’t issue 30 ‘parse calls’ for this statement, I issue one ‘parse call’ and 30 execute calls.

But, because of the on-going truncates from the other session, the execution plan I generated for the query on the ‘parse call’ (which parsed, optimised and held the cursor) was invalid on nearly every execution, so virtually every execute call I made had to optimise the query again. (If I showed you v$sql you would see that the child cursor had suffered about 30 invalidations and loads).

Summary:

parse count (total) – number of ‘parse calls’. If the statement has never been seen before, this will result in parsing and optimisation; if the statement has been seen before this will involve a search of the library cache (which is how you discover it’s been seen before) and may result in ‘cursor authentication’; if the statement has been seen and authenticated before and has a reference in the session cursor cache then the ‘parse call’ may do virtually nothing – because there isn’t even a need to search the library cache for the statement because it has been implicitly held.

parse count (hard) - number of optimisations that took place. Optimisation may be a consequence of a ‘parse call’ or an ‘execute call’.  Even a statement that is being held can have it’s plan invalidated or simply flushed from the library cache, which leads to optimisation on the next execution.

session cursor cache hits – number of times a ‘parse call’ has been virtually free because the statement has been (silently) held because the OCI layer has detected repeated use of the statement.

Addendum ( Jan 2008 )

There is another parse statistic that I haven’t mentioned. It doesn’t often figure highly in statspack reports but, for completeness, I probably ought to mention it here as it can confuse the issue.

If you try run a query which has a parse error built into it (such as ‘select ssdate from dual’) then the optimizer will return a suitable Oracle error message (ORA-00904 in the given example).  The statistic ‘parse count (failures)’ will go up by one, as will the statistic ‘parse count (hard)‘. 

Unfortunately ‘parse count (total)’ may not increase – it didn’t on my 9.2.0.8 system when I tried this test from SQL*Plus, although it did in 10.2.0.3 – so there’s another way of getting the statistics to show you more ‘hard parses’ than ‘parses’.

If you do see a lot of ‘parse count (failures)’, you will also see a corresponding number of waits for event ‘SQL*Net break/reset to client’ (or dblink, depending where the SQL is coming from).

Update Jan 2011

Randolf Geist has been looking at adaptive cursor sharing, and has noted that a parse call – including parse calls that go through the session cursor cache – seem to be the point in the code where adaptive cursor sharing can take place: in other words, it’s not an event that gets triggered or flagged by executions.

41 Comments »

  1. That’s an excellent illustration Jonathan. I’ll steal that one for later use ;-)

    Thanks.

    Comment by Doug Burns — July 4, 2007 @ 6:06 am GMT Jul 4,2007 | Reply

  2. So, should we just look at “parse count(hard)” to see how many times Oracle had to “optimize” a query ?
    That represents the calls that consumed CPU.

    Comment by Hemant K Chitale — July 5, 2007 @ 1:36 pm GMT Jul 5,2007 | Reply

  3. Excellent!!!

    Comment by Mirjana — July 6, 2007 @ 1:28 pm GMT Jul 6,2007 | Reply

  4. [...] Providing evidence further supporting Andrew’s earlier post, Jonathan Lewis illustrates the difference between parse counts,parse calls and “optimisations”. I know I’ve amassed a collection of downloaded scripts over the years, but, in another top [...]

    Pingback by Log buffer 52 - a carnival of the vanities for dbas « OraStory — July 6, 2007 @ 6:44 pm GMT Jul 6,2007 | Reply

  5. Hermant, that’s a reasonable guideline. “parse count(hard)” reports optimizer activity, which means CPU and increased activity on the library cache (etc.), shared pool latches and (possibly) row cache latches.

    “Parse count(total)” – “session cursor cache hit” will have caused some activity on the library cache (etc.) and shared pool latches, but usually a lot less, and much less CPU per call, than the ‘hard’ count.

    “session cursor cache hit” will have been about as gentle on the latches and CPU as anything could be.

    Of course, resource usage ultimately depends on frequency as well – I probably wouldn’t be focussing on 10,000 hard parses if I could see 50,000,000 hits on the session cursor cache.

    Comment by Jonathan Lewis — July 8, 2007 @ 10:41 am GMT Jul 8,2007 | Reply

  6. By the way, from where the statisctics session cursor cache hits,parse count(total),parse count (hard),execute count collected, through trace file or v$ view or tool you are developed?

    Comment by Car — August 3, 2007 @ 2:25 pm GMT Aug 3,2007 | Reply

  7. Car, these come from v$sysstat and v$sesstat.

    Comment by Jonathan Lewis — August 3, 2007 @ 5:11 pm GMT Aug 3,2007 | Reply

  8. Hi Jonathan,

    A very informative writeup.

    If, the value of session_cache_cursor parameter is increased, do I need to increase the size of the shared pool as well ? and session_cache_cursor keeps the frequently executed queries in the UGA of a user session, am I correct ?

    Regards

    Comment by Viveshar — August 11, 2007 @ 6:44 am GMT Aug 11,2007 | Reply

  9. Viveshar,
    It’s not possible to give a definitive answer to that question. Every individiual holding a cursor open has an entry in x$kgllk – which is in the SGA – and these entries seem to be 172 bytes long in 10g (152 in 9i). So, clearly, if you hold more cursors open, you will be using more memory for these structures.

    On the other hand, there may be other subtle effects that introduce benefits. For example, accidents happen, and you sometimes see multiple child cursors which should not have been created. These will be wasting resources – including memory – and some of them may not have appeared if the users had been holding more cursors.

    There will be a (fairly small) memory component relating to the session cached cursors in the UGA. I think Steve Adams made a comment once that this used to be an array in 8i but changed to a hash table in 9i – using more memory but less CPU.

    Comment by Jonathan Lewis — August 11, 2007 @ 7:33 am GMT Aug 11,2007 | Reply

  10. [...] to the “library cache latch”. This can be indicative of excessive optimization (aka “hard” parsing) – but since we aren’t doing much hard parsing we carry on down to the Dictionary Cache Stats [...]

    Pingback by Analysing Statspack(8) « Oracle Scratchpad — November 10, 2007 @ 5:05 pm GMT Nov 10,2007 | Reply

  11. [...] a long time before we get back onto the CPU. If you see a lot of ’soft parses’ though (i.e. parse calls that don’t result in an optimisation stage), your first thought shold be to check the effects of the session cursor [...]

    Pingback by Analysing Statspack (9) « Oracle Scratchpad — December 18, 2007 @ 7:21 pm GMT Dec 18,2007 | Reply

  12. Please correct my understanding sir,
    parse count (hard)-> It stands for the ‘hard parsing of the query as the query is never seen before or may be invalidated due to some object level DDL.So this wil account to go for optimization means the phases of
    1)Query Transformation
    2)Estimation
    3)Plan Generation

    parse count (total)->
    includes both the ‘soft’ and ‘hard’ parsing.So if the statement is not seen before,will include optimization ,if it did than may be a search in the Library Cache or may be not,if Session_cursor_cache is there.
    Is this correct sir?
    Wish you a very happy new year!
    Regards,
    Aman….

    Comment by Aman Sharma — January 3, 2008 @ 2:01 pm GMT Jan 3,2008 | Reply

  13. Aman,

    Some of the ‘hard’ parsing will not be recorded in the parse count(total) at all because it was performed during an execute call. The rest of your comments are reasonably accurate.

    Comment by Jonathan Lewis — January 3, 2008 @ 3:34 pm GMT Jan 3,2008 | Reply

  14. Sir,
    you mentioned,
    Some of the ‘hard’ parsing will not be recorded in the parse count(total) at all because it was performed during an execute call.
    I didnt understand this.How come the ‘hard parsing’ will happen at the ‘excute’ phase?
    Regards,
    Aman….

    Comment by Aman Sharma — January 4, 2008 @ 2:47 am GMT Jan 4,2008 | Reply

  15. Aman,
    The answer to your last question is exactly the point that the article addresses. Please read it again, especially the last six paragraphs.

    Comment by Jonathan Lewis — January 4, 2008 @ 7:29 am GMT Jan 4,2008 | Reply

  16. Just one thing to note. It seems that Oracle 9i and 10g caches pl/sql cursors in the area of session_cached_cursors, but Oracle 8i caches pl/sql cursors in the area of open_cursors. I belive this is the reason why we should always set session_cached_cursors bigger than 0.

    Comment by Dion Cho — January 23, 2008 @ 12:28 am GMT Jan 23,2008 | Reply

  17. Dion,
    Correct, but one minor detail, the change appeared in 9.2.0.5 (and is documented in the release note for 9.2.0.5). Until then the pl/sql cursor cache followed the value of open_cursors, which people often set reasonably high, after that it followed session_cached_cursors which I think may have defaulted to a rather modest 50 in that version of 9i – although that might have been the default for open_cursors – but seems to default to 20 in 10.2 and zero in the more recent versions of 9i.

    Comment by Jonathan Lewis — January 23, 2008 @ 10:58 pm GMT Jan 23,2008 | Reply

  18. Hi,

    I read your book. It is what I was searching.

    I am working as a Oracle Performance Tuning – SQL & PL SQL.

    Recently, I am facing a problem with Hard Parsed queries.

    The details are as follows:

    1. Queries written like :
    SELECT ……
    FROM …..
    WHERE
    c1 IN (1,2,3, ……230)
    2. Because of queries like above, Oracle parses 230 times.
    Instead of it I am trying to put this in as below:
    SELECT…
    ….
    IN (SELECT * FROM TABLE(obj_variable_collection);
    3. So that I am expecting IN query would will parse only once because of TABLE function.

    Could you provide me a solution for IN-list query – Hard Parsing.

    Thanks in advance,

    Oracle crazy.

    Comment by Oracle Crazy — March 27, 2008 @ 4:06 pm GMT Mar 27,2008 | Reply

  19. @Oracle Crazy
    Check this blog entry by Tom Kyte http://tkyte.blogspot.com/2006/06/varying-in-lists.html

    Comment by Óscar de la Torre — March 31, 2008 @ 7:51 am GMT Mar 31,2008 | Reply

  20. Oscar,

    Thanks for pointer that post out. I was planning to hunt it down and reference it myself.

    Oracle Crazy,

    I hope the reference helps. There is no really good solution to the variable inlist problem – it’s just one of those problems where you have to introduce some compromises between simplicity and efficiency.

    Comment by Jonathan Lewis — April 1, 2008 @ 4:48 pm GMT Apr 1,2008 | Reply

  21. Hi Jonathan,

    Can be session cursor cache hits > parse count (total)? Why?

    > @sid_parses 630
    
    CURSOR_CACHE_HITS PARSE_CALLS HARD_PARSE CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
    ----------------- ----------- ---------- ----------------- ----------- -----------
                46250       35158       8064           131.55%     -54.49%      22.94%
    
    1 fila seleccionada.
    
    > l
      1  select
      2    cursor_cache_hits,
      3    parse_calls,
      4    hard_parse,
      5    to_char(100 * cursor_cache_hits / parse_calls, '999999999990.00' ) || '%' cursor_cache_hits,
      6    to_char(100 * (parse_calls - cursor_cache_hits - hard_parse) / parse_calls, '999990.00' ) || '%' soft_parses,
      7    to_char(100 * hard_parse / parse_calls, '999990.00' ) || '%' hard_parses
      8  from
      9    ( select value parse_calls from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'parse count (total)' and sid=:p1 ),
     10    ( select value hard_parse  from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'parse count (hard)' and sid=:p1 ),
     11*  ( select value cursor_cache_hits  from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'session cursor cache hits' and sid=:p1)
     
     (:p1 = 630 )
    

    Is there anything wrong?

    I got the query from Metalink:

    SCRIPT – to Gauge the Impact of the SESSION_CACHED_CURSORS Parameter
    Doc ID: Note:208918.1

    Comment by Joaquín González — June 4, 2008 @ 5:03 pm GMT Jun 4,2008 | Reply

  22. Joaquín,

    Thanks for the note. I think you must be running 10.2. I hadn’t noticed it before but there seems to be a new code optimisation in 10.2 (possibly 10.1) that changes the way that some cursors can be cached.

    After seeing your comment, I ran a couple of quick tests that show very different behaviour between 9.2 and 10.2 – but I haven’t yet pinned down what type of action leaves the cache hits greater than the parse calls. (I did notice, though, that a simple “startup force” left the statistics for the starting session in exactly this state). I guess a detailed examination of a 10046 trace file might reveal more of what’s going on.

    This means, by the way, that comments I have made in other posts about substracting ‘session cursor cache hits’ from ‘parse count (total)’ are less useful 10g than they are for earlier versions.

    Comment by Jonathan Lewis — June 10, 2008 @ 10:37 am GMT Jun 10,2008 | Reply

  23. Jonathan, have you noticed that AWR reports, “SQL ordered by Parse Calls”, I’m unclear about the parse vs execution.

    for example (15 minute interval):

                                % Total
     Parse Calls  Executions     Parses    SQL Id
    ------------ ------------ --------- -------------
         261,245      261,245     13.15 8t8kxpg18kw92
    select min(time_manager_info) from "TAP_INFCE"."INTERFACE_XML_QUEUE"
    
         261,245      261,245     13.15 acqqp1jn0hq23
    select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no
    , local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exc
    eption_queue, retry_count, corrid, time_manager_info, sender_name, sender_addr
    ess, sender_protocol from "TAP_INFCE"."INTERFACE_XML_QUEUE" where time_manag
    
         261,196      261,196     13.15 cgb6kp2umq52a
    select t.schema, t.name, t.flags, q.name, t.timezone from system.aq$_queue_tabl
    es t, system.aq$_queues q where t.objno = :1 and q.table_objno = t.objno and q
    .usage = 0 and NOT ( t.name in ('DEF$_AQCALL' , 'DEF$_AQERROR')
    and t.schema = 'SYSTEM')
    
    
    SQL> select sql_text from v$sqlarea where sql_id='acqqp1jn0hq23';
    
    SQL_TEXT
    ------------------------------------------------------------------------------
    select q_name, state, delay, expiration, rowid, msgid,   dequeue_msgid, chain_
    no, local_order_no, enq_time, enq_tid, step_no,   priority, exception_qschema,
     exception_queue, retry_count, corrid,   time_manager_info, sender_name, sende
    r_address, sender_protocol   from "TAP_INFCE"."INTERFACE_XML_QUEUE"   where ti
    me_manager_info <= :1 and state not in (:2, :3)   for update skip locked
    

    Why are these statements getting parsed for every execution? Am I just confused on the definition of "parse". Also, note that the executions of the sql statements are all very close (if not equal) in number. Could this be due to these statements being called from within a PL/SQL package?

    Comment by RobH — January 22, 2009 @ 4:36 pm GMT Jan 22,2009 | Reply

    • RobH,

      These calls look like they’re coming from the use of advanced queues, which suggests that various objects and jobs have been created through the dbms_aq package. You often find in cases like this (where features are implemented through packages) that the underlying code is dynamically recreating the SQL for some statement then issuing parse calls for every execute. This need not be a problem, as the parse calls don’t often have to re-optimise the statement because (a) it’s using bind variables and (b) used so frequently that it’s always found in the library cache; you may also be able to reduce the cost of this library cache hits by making sure that your session cursor cache is large enough that Oracle handles the statement like a held cursor.

      Bottom line: a parse call may:

        a) Have to optimise the statement because it failed to find it after searching the library cache
        b) Find the statement after searching the library cache, and still have to optimise it because the text applies to different objects (cursor authentication).
        c) Find the statement after searching the library cache, and not have to optimise it
        d) operate through the session cursor cache or pl/sql cursor cache and use a short cut to the statement’s location in the library cache without having to search the cache.

      The thing being counted in this section of statspack is parse calls – it doesn’t tell you whether those calls turned into (a), (b), (c) or (d) … although there is a statistic that (sometimes) records cursor authentications.

      Comment by Jonathan Lewis — January 30, 2009 @ 6:12 pm GMT Jan 30,2009 | Reply

  24. Does anybody know of a method to easily find the parse failures on the db side (i.e. when the application is not reporting errors). It would be enough to see the actual failed sqltext.

    Comment by Bernd Eckenfels — July 10, 2009 @ 9:57 am GMT Jul 10,2009 | Reply

    • Look for statistic ‘parse count (failures)’.

      Comment by Timur Akhmadeev — July 10, 2009 @ 10:18 am GMT Jul 10,2009 | Reply

    • Bernd,

      I don’t think you can find the SQL text of a failed parse from the server side, I think you’d have to trap the errors from the client.

      In theory you could do something like setting a load of events (e.g. event 904) to dump the errorstack when a parse error occurs – but you’d have to cover all the possible parse errors, e.g. 904 is invalid identifier, 942 is table or view does not exist, 923 is “from” keyword not found. The same complication applies to firing a trigger “on servererror”.

      It’s possible that Tanel Poder may have some ideas – he’s just written a blog note about finding SQL in the SGA, so your question is a good follow-up to that note.

      Comment by Jonathan Lewis — July 10, 2009 @ 11:10 am GMT Jul 10,2009 | Reply

  25. [...] Just to confuse the issue, Oracle may also record a “parse count (hard)” without recording a “parse call”. [...]

    Pingback by Nutshell – 2 « Oracle Scratchpad — March 21, 2010 @ 8:17 pm GMT Mar 21,2010 | Reply

  26. Oracle中SQL解析的流程…

    Oracle中SQL解析的主要流程: 我们说的游标概念比较复杂,它可以是客户端程序中的游标,服务进程中的私有游标,以及服务器端共享池里的共享游标。假设一个游标被打开了,一般来说它的共享游标信息(包括执行计划,优化树等)总是会在SQL AREA里,无需再次软/硬解析。 SESSION_CACHED_CURSORS是Oracle中的一个初始化参数(修改必须重启实例),指定了每个会话缓存的游标上限(保留在PGA中);客户端程序中open cursor的要求仍会被传递给服务进程,服务进程首先扫描自身缓存的游…

    Trackback by Oracle Clinic — November 2, 2010 @ 11:08 am GMT Nov 2,2010 | Reply

  27. Hello Jonathan,

    Motivated by this article, I did some experiments. On Oracle 11g I noticed, that the “session cursor cache hits” value can be larger than the “parse count (total)” value (see the test case below). From what you write in the above summary (“if the statement has been seen and authenticated before and has a reference in the session cursor cache then the ‘parse call’ may do virtually nothing “) I understood, that “session cursor cache hits” should also be accounted for in “parse count (total)”. Also with respect to the “session cursor cache hits”, the Oracle Reference says “…Subtract this statistic from “parse count (total)” to determine the real number of parses that occurred”, from which I conclude that the “parse count (total)” value should be >= the “session cursor cache hits” value. Did I maybe misunderstand something concerning these statistics or do you have an explanation for my observation?

    thanks a lot for your help
    kind regards
    Martin

    Test scenario
    =============
    
    Preparation
    -----------
    
    create table mma_test(id number(9));
    
    
    
    Perform parses / session cursor cache lookups in Session 1
    -----------------------------------------------------------
    
    K.AF080C.AVALOQ> select distinct sid from v$mystat;
    
           SID
    ----------
           317
    
    1 row selected.
    
    K.AF080C.AVALOQ> begin
      2    for i in 1..4000 loop
      3      insert /*+ test_ins2 Schleife */ into mma_test values (33);
      4    end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    
    Monitor statistics in Session 2
    -------------------------------
    K.AF080C.AVALOQ>
    K.AF080C.AVALOQ>
    K.AF080C.AVALOQ>
    K.AF080C.AVALOQ> select name.name, stat.value
      2    from v$sesstat    stat
      3        ,v$statname   name
      4   where  stat.statistic# = name.statistic#
      5     and  stat.sid = 317
      6     and  name.name in ('parse count (hard)'
      7                       ,'parse count (total)'
      8                       ,'session cursor cache hits'
      9                       ,'session cursor cache count'
     10                       ,'opened cursors cumulative'
     11                       ,'opened cursors current'
     12                       );
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    opened cursors cumulative                                               191
    opened cursors current                                                    3
    session cursor cache hits                                                80
    session cursor cache count                                              108
    parse count (total)                                                     135
    parse count (hard)                                                        0
    
    6 rows selected.
    
    K.AF080C.AVALOQ> -- AFTER THE LOOP HAS BEEN EXECUTED IN SESSION 317
    K.AF080C.AVALOQ> /
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    opened cursors cumulative                                              4199
    opened cursors current                                                    3
    session cursor cache hits                                              4083
    session cursor cache count                                              109
    parse count (total)                                                     142
    parse count (hard)                                                        1
    
    6 rows selected.
    
    K.AF080C.AVALOQ>
    

    Comment by Martin Maletinsky — March 1, 2011 @ 4:28 pm GMT Mar 1,2011 | Reply

  28. Addon to my previous post:

    I just noticed that entry 22./23. address a similar topic (for Oracle 10.2). However, I am still not sure about which actions are accounted for in the statistics “session cursor cache hits” and “parse count (total)” and if there is an overlap (i.e. actions that increase both counters) or if the counters (for Oracle 10.2, 11g) refer to completely distinct actions.

    kind regards
    Martin

    Comment by Martin Maletinsky — March 1, 2011 @ 4:35 pm GMT Mar 1,2011 | Reply

    • Martin,

      Thanks for the post.

      The first thing to do, of course, is to run your test against 10g and see if similar results appear. I did a quick check with some similar code against a 10.2.0.3 instance and didn’t see the session cursor cache hits that you report.

      It’s possible, therefore, that the 11g code has done something to re-route the hits on the pl/sql cursor cache (which is what your code is using) through the code that handles the session cursor cache, or it may simply be counting pl/sql cursor cache hits as session cursor cache hits.

      It’s possible that the cause of the anomaly reported in comments 22 and 23 is now universal, it’s possible that there are two different factors at play. It’s something that will need further investigation.

      Comment by Jonathan Lewis — March 3, 2011 @ 10:02 am GMT Mar 3,2011 | Reply

  29. Hello Jonathan,

    I do not observe this behaviour with Oracle version 10.2. In fact with the test scenario described above I get the following output in the observing session:

    K.AF090D.AVALOQ> select name.name, stat.value
      2   from v$sesstat    stat
      3       ,v$statname   name
      4  where  stat.statistic# = name.statistic#
      5    and  stat.sid = 266
      6    and  name.name in ('parse count (hard)'
      7                      ,'parse count (total)'
      8                      ,'session cursor cache hits'
      9                      ,'session cursor cache count'
     10                      ,'opened cursors cumulative'
     11                      ,'opened cursors current'
     12                      );
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    opened cursors cumulative                                               138
    opened cursors current                                                   44
    session cursor cache hits                                                28
    session cursor cache count                                               39
    parse count (total)                                                     137
    parse count (hard)                                                        5
    
    6 rows selected.
    
    K.AF090D.AVALOQ>
    K.AF090D.AVALOQ> -- AFTER THE LOOP HAS BEEN EXECUTED IN SESSION 266
    K.AF090D.AVALOQ> /
    
    NAME                                                                  VALUE
    ---------------------------------------------------------------- ----------
    opened cursors cumulative                                               145
    opened cursors current                                                   45
    session cursor cache hits                                                29
    session cursor cache count                                               41
    parse count (total)                                                     142
    parse count (hard)                                                        7
    
    6 rows selected.
    
    K.AF090D.AVALOQ> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for HPUX: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    
    5 rows selected.
    
    K.AF090D.AVALOQ>
    

    Comment by Martin Maletinsky — March 3, 2011 @ 10:36 am GMT Mar 3,2011 | Reply

  30. [...] Blog article written by Jonathan Lewis, specifically this comment: “Every individiual holding a cursor open has an entry in x$kgllk – which is in the SGA – and these entries seem to be 172 bytes long in 10g (152 in 9i). So, clearly, if you hold more cursors open, you will be using more memory for these structures.” [...]

    Pingback by SESSION_CACHED_CURSORS – Possibly Interesting Details « Charles Hooper's Oracle Notes — July 21, 2011 @ 6:01 am GMT Jul 21,2011 | Reply

  31. [...] found in the PGA memory in dedicated server mode and in the SGA in shared server mode (reference1 reference2 from one of the references: “Every individiual holding a cursor open has an entry in x$kgllk – [...]

    Pingback by Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes — September 10, 2011 @ 9:14 pm GMT Sep 10,2011 | Reply

  32. [...] objects“相对比”library cache latch“要大。这可能是过度的优化的指示(a.k.a. “hard” parsing),但是因为没有太多的hard [...]

    Pingback by xpchild » analysing statspack 8 — December 16, 2011 @ 8:16 am GMT Dec 16,2011 | Reply

  33. [...] Jonathan Lewis在07年的一篇文章Parse Calls中详细介绍了parse相关的一些statistics. [...]

    Pingback by eagle’s home » 11203的parse count(total)中不包含softer soft parse — November 27, 2012 @ 6:30 am GMT Nov 27,2012 | Reply

  34. […] though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the […]

    Pingback by Striving for Optimal Performance – The Broken Statistics: “parse count (total)” and “session cursor cache hits” — August 14, 2013 @ 5:18 pm GMT Aug 14,2013 | Reply

  35. […] though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the […]

    Pingback by Striving for Optimal Performance – The Broken Statistics: "parse count (total)" and "session cursor cache hits" — March 1, 2014 @ 4:41 pm GMT Mar 1,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers