Oracle Scratchpad

OC 7 Parsing and Optimising

Addenda and Errata for Oracle Core Chapter 7 Parsing and Optimising

Back to Index

Addenda

p.169 Middle of page: “… when I ran the same query against an instance of 11.2.0.2 there was no entry for KQR ENQ …” I’ve been sent an email that shows that the rowcache enqueues can (along with the kgl locks and pins be found as a subheap to heaps of type KKSSP if you use the “dump heapdump_addr” command:

                    Processing Oradebug command 'DUMP HEAPDUMP_ADDR 1 0x7000000a29e8998'
                    ******************************************************
                    HEAP DUMP heap name="KKSSP^192"  desc=7000000a29e8998
                    EXTENT 0 addr=7000000a04e00c8
                      Chunk  7000000a04e02e0 sz=       40    freeable  "kglseshtSegs   "
                      Chunk  7000000a04e0308 sz=      256    freeable  "KQR ENQ        "
                      Chunk  7000000a04e0708 sz=      256    freeable  "kglpn          "
                    EXTENT 1 addr=7000000a04eaf90
                      Chunk  7000000a04eafa0 sz=       48    freeable  "kglseshtSegs   "
                      Chunk  7000000a04eb068 sz=      256    freeable  "KQR ENQ        "
                      Chunk  7000000a04eb168 sz=      256    freeable  "kgllk          "
                      Chunk  7000000a04eb268 sz=      256    freeable  "KQR ENQ        "
p.186   First paragraph, second sentence: “… you will see a ds (data segment) label …”. I’ve received an email pointing out that ds may be short for “descriptor” or even “descriptor of subheap” given the following notes in the documentation of the dynamic performance views:_MEMORY and V$SQL, we can see:

V$SQL_SHARED_MEMORY.SUBHEAP_DESC: If the parent heap of this context heap is itself a subheap, then this is the address of the descriptor of the parent heap   
V$SQL.TYPE_CHK_HEAP: Descriptor of the type check heap for this child cursor.

Moreover, in heap dumps we see the expression “desc=” fairly often, e.g.:

HEAP DUMP heap name="sga heap"  desc=05977BA0

Errata

p.195Second paragraph, fifth line: “… cursor_spare_for_time …” should be cursor_space_for_time.Note: The text in the book says that this parameter “is deprecated in 11g”, in fact MOS note 565424.1, published July 2008, says that it is deprecated in 10.2.0.5 and 11.1.0.7

p.166 Final paragraph: “If we check x$qrst (the X$ structure underlying v$rowcache), we find that it contains an interesting column, as follows” – the correct name of the structure is x$kqrst.
p.174 The note says the following:

Note The sequence of activity in parsing is a little more complicated than my original suggestion. When you pass a piece of text to Oracle it will do a syntax check to decide if it is legal, then it will search the library cache for a matching text (using a hash value computed from the text). If it finds a textual match Oracle starts the semantic check—checking to see if the new text actually means the same as the existing text (same objects, same privileges, etc.); this is known as cursor authentication. If everything matches, then the session need not optimize the statement again.

This is incorrect, the semantic check (what does the statement mean) takes place before the search of the library cache. If the search finds a matching text and some existing child cursors Oracle has to do a further check to see if the objects (and optimizer environment etc.) referenced in any of the child cursors are the same; in other words, the cursor authentication is not a “semantic check”, it’s checking to see if the semantics (etc.) of an existing child cursor match. If there is a match then the session doesn’t have to create a new child cursor, it can use the existing one.

p.176 Fourth paragraph, second line: “… increasing the session_cache_cursor parameter …” should be “… increasing the session_cached_cursors parameter …”.
p.179 Figure 7-2: I’ve drawn the freelists as singly linked lists. In fact they’re doubly linked lists. I don’t say anything about whether or not the free lists are linked directly to x$kghlu – it’s quite likely that they’re not and that there’s a pointer from each row in x$kghlu (or its equivalent) to an array of 255 elements which are the end points of the 255 linked lists.
p.186 First paragraph penultimate sentence: “The address given by the ds line is in the middle of a freeable chunk commented as PCursor (parent cursor)further down the heap dump…” I didn’t point out that the extract on the previous page didn’t include the extra few lines needed to show this parent cursor, so here is a version of the extact that is just a few lines longer, showing the relevent PCursor on the penultimate line:

EXTENT 0 addr=1A800000
  Chunk 1a800038 sz=       24  R-freeable  "reserved stoppe"
  Chunk 1a800050 sz=   212888  R-free      "               "
  Chunk 1a833fe8 sz=       24  R-freeable  "reserved stoppe"
  Chunk 1a834000 sz=   969800    perm      "perm           "  alo=35876
  Chunk 1a920c48 sz=    16068    free      "               "
  Chunk 1a924b0c sz=     1072    recreate  "Heap0: KGL     "  latch=00000000
     ds 1b3e5d60 sz=     1072 ct=        1
  Chunk 1a924f3c sz=     2056    freeable  "parameter handl"
  Chunk 1a925744 sz=     4096    freeable  "sql area       "  ds=1A93000C
  Chunk 1a926744 sz=     4096    freeable  "sql area       "  ds=1A93000C
  Chunk 1a927744 sz=     1072    freeable  "CCursor        "  ds=1A9B9E44
  Chunk 1a927b74 sz=     4096    recreate  "sql area       "  latch=00000000
     ds 1a93000c sz=    12288 ct=        3
        1a925744 sz=     4096
        1a926744 sz=     4096
  Chunk 1a928b74 sz=     1072    freeable  "CCursor        "  ds=1A9B9E44
  Chunk 1a928fa4 sz=     1072    recreate  "CCursor        "  latch=00000000
     ds 1a9b9e44 sz=     3216 ct=        3
        1a927744 sz=     1072
        1a928b74 sz=     1072
  Chunk 1a9293d4 sz=    14672    free      "               "
  Chunk 1a92cd24 sz=     4096    freeable  "sql area       "  ds=1A970DD4
  Chunk 1a92dd24 sz=     4096    freeable  "sql area       "  ds=1A992E48
  Chunk 1a92ed24 sz=     4096    freeable  "sql area       "  ds=1A970DD4
  Chunk 1a92fd24 sz=       40    free      "               "
  Chunk 1a92fd4c sz=      504    freeable  "parameter table"
  Chunk 1a92ff44 sz=     1072    freeable  "PCursor        "  ds=1A948F68
  Chunk 1a930374 sz=    18864    free      "               "

p.187 Penultimate paragraph: “The first 176 ( 0 to 175) increment the chunk size by 4 bytes at a time;the next few increment by 12 bytes at a time,” Bucket 175 is the last bucket used to hold free chunks of a single size, and that’s why the increment to bucket 176 is 4; bucket 176 can hold two different sizes of free chunk, 724 and 728 bytes and that’s why its increment is 8 (as are the next few, rather than the 12 stated in the book).

Moreover, these figures are for 32-bit Oracle. For 64-bit Oracle bucket 0 holds chunks of 32 bytes, the basic increment is 8 bytes and the last “single chunk size” bucket is bucket 178 (holding chunks of 1456 bytes).

p.190 First paragraph: ” – the LRU lists are associated with sub-pools, not with sub-sub-pools” I suspect that each duration of each sub-pool has it’s own LRU list even though x$kghlu seems to report only one row per sub-pool rather than 4. (I’ve repeated the error on page 191 by raising the possibility of side effects of having the LRU list spanning durations.) Examination of the heapdump trace file shows that each duration has its own free lists, but only the first duration showed any items on its ‘UNPINNED RECREATABLE CHUNKS” list – and that list seemed to include all the recreatable chunks from the other durations.

Typos/Grammar/Style/Punctuation

p.178 First paragraph, last sentence: “Counter-intuitively it is possible to get see a value of …” should be “Counter-intuitively it is possible to get a value of …”  
p.191 Second complete paragraph, fifth line: “… can’t possibly help.” should end with a question mark.
p.197 First paragraph, third line: “… as the statement and it’s cursor …” has an apostrophe that shouldn’t be there.

Back to Index

21 Comments »

  1. Hi Jonathan,

    Chapter 7 : Page no: 174 –
    You mentioned -as part of defining ‘cursor authentication’

    1.syntax check
    2.check in Library cache for exact text
    3.semantic check

    but is not this way ?

    1.syntax check
    2.semantic check
    3.check in library cache

    Just wanted to confirm with you if I am wrong …

    2.a)PL/SQL ‘caches’ cursors OPEN though we ‘CLOSE’ them (static SQL)
    b) Session ‘caches’ cursors (after 3 times the cursor is executued- you said its not quite correct though)

    Sir
    Could you please tell me EXACTLY the internals behind ‘cursor cache’in both the above cases?
    How Pl/SQL keeps OPEN though we CLOSE it ? how session ‘cache’ happens?

    3.When we use bind variables – Does the client not make parse call to the database for subsequent input values for binds?
    I understand it makes ‘parse call’ but database makes it ‘soft parse’

    Many thanks for your time

    Once again thanks for your EXCELLENT BOOK !!!!

    Comment by Bix — November 13, 2011 @ 7:11 pm BST Nov 13,2011 | Reply

    • Bix,
      1) The note is wrong – and I’ve written a correction in the Errata above. The semantic check happens before the library cache search, although each child cursor in the library cache is then checked to see if it’s semantics match the new statement.

      2)
      a) correct, until the pl/sql cursor closes, and it’s not just static SQL that this applies to (though effect vary with version of Oracle)
      b) Not quite correct, but a reasonable first approximation, as explained on page 176

      Cursor cache needs three things – something to “fix” part of the child cursor in the library cache, a “state object” in the session’s UGA on the server side, and an array somewhere that knows about the state object and can therefore trust the child cursor portion to be pinned. The code that populates and maintains the array varies with the version and environment – to the extent that one of the JDBC drivers will (I believe) keep such an array at the client library level, holding a cursor open even when the client java code has issued a call to close it. It’s possible (though I’m not certain) that with the session cursor cache (server side caching) that the array need not exist, but could be implicit in some detail of the state objects.

      3) It is entirely up to the client, and the libraries used by the client as to whether a “parse call” is sent across SQL*Net to the server. If a parse call arrives at the server then the server code can decide how to treat that parse call – which may be result in a “soft parse”.

      Thanks for the comment about the book.

      Comment by Jonathan Lewis — November 22, 2011 @ 2:26 pm BST Nov 22,2011 | Reply

  2. Hi Jonathan ,

    Could you please correct if my understanding is wrong – sorry to trouble you – Many thanks for your time .

    When we use literal in SQL (select id from t where owner=’IT’) , the client
    1.makes parse call to the database with the SQL text and database will do ‘syntax’ , ‘semantic’ check and also prepare the optimal execution plan (if the same SQL not found in Library cache) as the literal is known at parse time itself
    2.When the client says ‘execute’- then the database will execute as per the ‘plan’ prepared in the parse call.
    *Here – the execution plan is prepared by the engine at ‘PARSE CALL’

    When we use bind variable in SQL (select id from t where owner=:owner) – the client will
    1.make parse call to the database with the SQL text and database will do ‘syntax’ and ‘semantic’ check
    2.when the client says ‘execute’- then the database will peek the first bind value and prepare optimal execution plan
    *Here – the execution plan is prepared by the engine at ‘EXECUTE CALL’
    3.the SQL is marked as ‘bind sensitive’ if there are RANGE operators / histograms on the column
    4.if the WHERE clause contain RANGE operators/histograms – then the subsequent execution will mark the SQL as bind aware and On 3rd execution of the SQL -Adaptive cursor sharing takes place.

    Even with bind variables – the client makes PARSE call to the database for every SQL statement but the database makes it ‘soft parse’
    If the same SQL was already executed once.

    PL/SQL Cache (client side caching) -> it keeps the cursor OPEN (=the parameter session_cursor_cache), it means
    When a client keeps the cursor ‘OPEN’ means – what happens internally ?

    The pointer in PGA still points to the SQL text (parent cursor) and PLAN (child cursor) and
    The memory allocated for the cursor still exists in PGA
    So in this case – the client (pl/sql) directly sends (SQL, the cursor pointer) to the database with EXECUTE call
    No PARSE call .

    Server Caching (session_cursor_cache)
    The memory allocated for the cursor in the PGA is ‘de-allocated’
    The only thing is ‘the pointer’ from PGA still points to the SQL and plan in the SGA.

    Comment by Bix — November 14, 2011 @ 10:40 am BST Nov 14,2011 | Reply

    • Thanks Jonathan for your reply – My understanding mentioned above is bit more high level
      When you have 2 minutes time – can you please look at that
      I mentioned in 2 CASES:

      Case 1: When we use literal in SQL (select id from t where owner=’IT’)
      CASE 2: When we use bind variable in SQL (select id from t where owner=:owner)

      Before going through your book on ‘Parsing and Optimization’ – if we have this understanding of how SQL is executed (parse calls/execute calls) as I mentioned above in 2 cases – I feel it is bit easy to understand the concepts you explained NICELY easily in your EXCELLENT book .

      Once again Many thanks for your time !!

      Comment by Bix — November 22, 2011 @ 3:09 pm BST Nov 22,2011 | Reply

    • I think the first part (literal SQL is fine).

      The second part (SQL with binds) may vary with version of Oracle and working environment (choice of programming language, Oracle library etc.) and it’s worth pointing out that whatever the program code looks like the libraries invoked may bundle database calls and maintain some state under the covers.

      If you check the OCI manuals you will find that you can go through steps like: parse, define_column, bind, execute, fetch.

      If your code manages to separate the parse and bind, then you will be sending a statement with undefined (unknown type and value) bind variables to Oracle, and find that it will do the syntax and semantic checks, and put the text into the library cache with a parent and child cursor – although the child won’t have a plan attached. You will then see that (as you say) the query is re-optimized on the execute call – and a second child cursor (with a plan this time) goes into the library cache. (You could, for example, used the dbms_sql package to examine this behaviour).

      Adaptive cursor sharing – also triggered by bind variable that reference partition keys. I am not aware of any significance of a “3rd execution” – as far as I know the cursor could be marked as invalid and Oracle start generating new child cursors markes as bind-aware at any time after the first execution.

      As far as the details of the different caching mechanisms are concerned, I’m really not going to try to work out exactly what happens in every version of Oracle – and I’ve never looked at exactly where and when PGA memory might be released and reallocated. To me, the most significant thing about the pl/sql and session caches was how effective they were in reducing latch activity (and that kept changing with versions of Oracle – and we’ve now got rid of a load of latches for mutexes anyway), and the new detail about which of them allows adaptive cursor sharing to appear and which one blocks it.

      Comment by Jonathan Lewis — November 22, 2011 @ 3:46 pm BST Nov 22,2011 | Reply

  3. On page 195, the second sentence in the first paragraph states, “In 10.2 they introduced code to allow KGL pins to be cached by sessions (hidden parameter _session_kept_cursor_pins) with similar intent…” The _SESSION_KEPT_CURSOR_PINS hidden parameter does not exist in Oracle Database 11.2.0.2 (I did not check 10.2.0.x). Is it possible that this hidden parameter was removed in 11.2.0.x?

    Comment by Charles Hooper — December 27, 2011 @ 11:24 am BST Dec 27,2011 | Reply

    • Charles,

      The parameters appeared in 10.2.0.1 and survived until 11.1.0.7, disappearing from 11.2.0.1.

      When I wrote the text the intention was that it would imply that the 11g mechanism made the 10g one redundant. This demonstrates two errors (a) you don’t always say exactly what you mean, and (b) I failed to check the version between the two I was thinking about. The implication of the parameters is that 11.1 uses the 10.2 mechanism and the new page-based approach only appeared in 11.2 – but I haven’t checked that hypothesis.

      Comment by Jonathan Lewis — December 28, 2011 @ 8:58 pm BST Dec 28,2011 | Reply

  4. On page 194, the middle of the last paragraph states, “However, you may recall all those latches relating to the library cache that appeared in 10g—like the library cache pin allocation latch…”. That particular latch appears to have been removed in Oracle Database 11.1.0.6 (http://blog.tanelpoder.com/2008/08/03/library-cache-latches-gone-in-oracle-11g/ ), however it is not clear if this section of the book is only describing behavior prior to Oracle Database 11.1.

    Comment by Charles Hooper — December 27, 2011 @ 11:28 am BST Dec 27,2011 | Reply

    • Charles,

      I can see some scope for rewriting that paragraph and the next one to make the intent clearer. I was trying to outline (very concisely) the evolution from the early 10g approach through to the 11.2 changes that made the 10g latches redundant – the bit you quote is setting the scene for why the intial “lots of latches” approach still wasn’t good enough, allowing the next paragraph to go on to the caching, and then paging strategies.

      That specific latch had, indeed, disappeared by 11.1.0.6 – unfortunately when I listed the various library cache latches (ch. 4, p.76) I included only the tereminal (as at time of writingh) releases of 8i, 9i, 10g, and 11g – so 11.1.0.x wasn’t in the list.

      Comment by Jonathan Lewis — December 28, 2011 @ 9:13 pm BST Dec 28,2011 | Reply

  5. On page 171, at the end of the third paragraph from the bottom of the page, the book states, “(If you needed an argument why you should select only the columns you need in a query, rather than using select *, the extra cost of accessing dc_histogram_defs should be enough to convince you.)” This sentence immediately follows a sentence that described how adding a second predicate in the WHERE clause referencing a second column would double the number of gets (increasing 2,000 gets to 4,000 gets) from the dictionary cache; thus it seems that the comment about the threat of “select *” causing more visits to dc_histogram_defs is only significant if those columns are also specified in the WHERE clause.

    Comment by Charles Hooper — December 27, 2011 @ 11:34 am BST Dec 27,2011 | Reply

    • Charles,

      The script (core_dc_activity_01.sql) goes through four variations of query, ending with:

      select n1, v1
      from t1
      where padding = rpad(‘x’,100)
      and id = {constant}

      Note that the columns in the select list are not the ones used in the where clause.

      The four variants tested by the script were:

      one column selected, one predicate
      one column selected, two predicates
      two columns selected, one predicate,
      two columns selected, two predicates

      The number of gets on the dictionary cache entry dc_histogram_defs (in 10.2.0.3) were

      2,000
      3,000
      3,000
      4,000

      suggesting that every column referenced in the query – whether in the select list or the where clause – requires dictionary access.

      Perhaps if I had included multiple sets of the results my claim would have been clearer, but I was concerned that this would result in a fairly large section of repetitious text for a small increase in information content.

      Comment by Jonathan Lewis — December 28, 2011 @ 9:33 pm BST Dec 28,2011 | Reply

      • Jonathan,

        Thank you for providing the output summary for your core_dc_activity_01.sql script. I examined the script while reading the book and noticed that there was a single result (likely one column selected, one predicate) for 10.2.0.3 and 11g included in the script file. Prior to posting this potential errata item, I had not created your snap_latch_child and snap_rowcache packages in a test database, so I did not execute the test script to confirm that the number of dc_histogram_defs gets was dependent on the number of columns in the SELECT clause. Logic reasoning suggested that there should be some form of increase in the dictionary cache gets to obtain the column definition and average row lengths of the selected columns, but it did not seem reasonable that dc_histogram_defs would show an increase in the number of gets.

        After seeing your comment, I created the snap_latch_child and snap_rowcache packages in a test database and experimented a bit with the core_dc_activity_01.sql script. I am able to reproduce the 2,000, 3,000, 3,000, and 4,000 results that you provided, so the statement in the book is correct.

        I made several slight adjustments to your script. Below is output (modified to show just the snap_rowcache.end_snap output) when selecting all four columns with either two columns listed in the WHERE clause or a single column in the WHERE clause. In both cases there were 4,000 gets of dc_histogram_defs (only when hard parses were required):

        SQL> prompt	select four columns with two predicates
        SQL> declare
          2  	m_n	number;
          3  	m_v	varchar2(10);
          4          m_m2    varchar2(100);
          5          m_id    number;
          6  begin
          7  	for i in 1..1000 loop
          8  		execute immediate
          9  			'select n1, v1, padding, id from t1 where padding = rpad(''x'',100) and id = ' || i
         10  			into m_n, m_v, m_m2, m_id;
         11  	end loop;
         12  end;
         13  /
         
        PL/SQL procedure successfully completed.
         
        SQL> execute snap_rowcache.end_snap
        ---------------------------------
        Dictionary Cache - 28-Dec 22:39:52
        Interval:-      0 seconds
        ---------------------------------
        Parameter                 Usage Fixed    Gets  Misses   Scans  Misses    Comp    Mods Flushes
        ---------                 ----- -----    ----  ------   -----  --------------    ---- -------
        dc_segments                   0     0   4,000       0       0       0       0       0       0
        dc_users                      0     0   3,000       0       0       0       0       0       0
        dc_objects                    0     0   4,013       0       0       0       0       0       0
        dc_global_oids                0     0      12       0       0       0       0       0       0
        dc_histogram_defs             0     0   4,000       0       0       0       0       0       0
        dc_object_grants              0     0      20       0       0       0       0       0       0
         
        PL/SQL procedure successfully completed.
        
        SQL> prompt	select four columns with one predicate
        SQL> declare
          2  	m_n	number;
          3  	m_v	varchar2(10);
          4          m_m2    varchar2(100);
          5          m_id    number;
          6  begin
          7  	for i in 1..1000 loop
          8  		execute immediate
          9  			'select n1, v1, padding, id from t1 where id = ' || i
         10  			into m_n, m_v, m_m2, m_id;
         11  	end loop;
         12  end;
         13  /
         
        PL/SQL procedure successfully completed.
         
        SQL> execute snap_rowcache.end_snap
        ---------------------------------
        Dictionary Cache - 28-Dec 22:39:53
        Interval:-      0 seconds
        ---------------------------------
        Parameter                 Usage Fixed    Gets  Misses   Scans  Misses    Comp    Mods Flushes
        ---------                 ----- -----    ----  ------   -----  --------------    ---- -------
        dc_segments                   0     0   4,000       0       0       0       0       0       0
        dc_users                      0     0   3,000       0       0       0       0       0       0
        dc_objects                    0     0   4,012       0       0       0       0       0       0
        dc_global_oids                0     0      12       0       0       0       0       0       0
        dc_histogram_defs             0     0   4,000       0       0       0       0       0       0
        global database name          0     0       2       0       0       0       0       0       0
         
        PL/SQL procedure successfully completed.
        

        Comment by Charles Hooper — December 29, 2011 @ 2:26 pm BST Dec 29,2011 | Reply

  6. Hi “Sir” Jonathan

    Looks like in 11.2.0.3 , session_cached_cursors behave differently than in the earlier patchsets/versions.
    Here is a small test case.

    ###
    
    Create table test (a number);
    Insert into test values(1);
    commit;
    
    -- Session Cached Cursor is set to 100.
    
    -- Run this SELECT 100 times.
    SELECT * FROM TEST WHERE A=1 ;
    
    SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
    
    Output
    =======
    100 
    
    (This is expected)
    

    Hold on , don’t exit this session. Now flush the shared pool

    ALTER SYSTEM FLUSH SHARED_POOL;
    
    SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
    
    Output
    =======
    
    No rows selected 
    

    (This is expected)

    Now , run the same select statement just once.

    SELECT * FROM TEST WHERE A=1 ;
    
    SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT='SELECT * FROM TEST WHERE A=1';
    
    Output
    =======
    101 
    

    V$SQLAREA now shows 101 executions. But the fact of the matter is that the previous 100 executions
    have been flushed out of the shared pool. So , this count should be 1 .
    I notice this behavior only in 11.2.0.3 and not in the earlier patchsets/versions.

    Further , if I set session_cached_cursor=0 ,
    I don’t get the same issue. I correctly see the execution count as 1

    Best Regards,
    Vishal

    Comment by Vishal — February 17, 2012 @ 4:03 pm BST Feb 17,2012 | Reply

    • Vishal,

      I can’t reproduce your results. You had “alter session flush shared_pool”, by the way, but I assume this was a typo, so I’ve corrected it.

      After the flush shared pool, I found that v$sql and v$sqlarea had been flushed, but v$sqlstats still showed the statement with 100 executions. After running the statement one more time, all three of them showed one execution. (This was 11.2.0.3 on Windows XP Pro 32-bit.)

      Comment by Jonathan Lewis — February 26, 2012 @ 4:06 pm BST Feb 26,2012 | Reply

  7. Hello,

    I have managed to repeat this behaviour only in EE 9.2.0.8 while on EE 10.2.0.4 and 11.2.0.3 was working as expected ie to show one executions after flushing shared pool.

    Regards,
    Sasa

    Comment by Sasa Petkovic — August 23, 2012 @ 8:52 am BST Aug 23,2012 | Reply

  8. Hello Jonathan,

    On page 197 you mention “vanish” as one of the possibilities what a parse call can do. Does this refer to other possibilities than the PL/SQL cursor cache (where cursors are held open imlicitly). This might be inferred from the phrasing “particlularly from pl/sql code” (which seems to suggest that there are other possibilities in addition to the PL/SQL cursor cache). If so, could you please tell me what possibilities you were thinking of? Otherwise this first point would be the same as the fourth point where you mention the PL/SQL cache explicitly?

    thank you
    kind regards
    Martin

    Comment by Martin Maletinsky — April 22, 2013 @ 8:18 pm BST Apr 22,2013 | Reply

  9. Hello Jonathan,

    I have a question regarding the free-lists which you describe starting on page 186. Is there one such list per sub-sub pool (which I don’t think is written explicitly in the book, but I might also have skipped it when reading through the chapter). If so, are the different free lists just shown in order in the heapdump and is there any labelling telling what sub-sub pool the list belongs to? If this is not the case (i.e. one single free-list for the entire shared pool), how can Oracle determine to which sub-sub pool a chunk belongs that is found in the free-list and therefore decide if the chunk may be used to serve the corresponding memory request?

    thank you for clarification
    kind regards
    Martin

    Comment by Martin Maletinsky — April 22, 2013 @ 8:40 pm BST Apr 22,2013 | Reply

  10. Hello Jonathan,

    On page 183 you write that the sub-pool concept used to cause ORA-04031 in earlier releases. Is this no longer the case – and if so since which release and how did Oracle fix the problem (the most obvious approach would be to have a session try each of the sub-pools in turn in case it’s allocation request to the first sub-pool fails). On page 191 (second paragraph) however you mention that the scenario of no sufficiently contiguous memory in one sub-pool can cause an ORA-04031, without referring to old versions.

    Also on page 183 you mention that one of the reasons to introduce sub-pools was to combat ORA-04031. In what way do the sub-pools address the ORA-04031? Starting on that page 183 you list three damage limitation strategies, none of which seems to be directly related to the concept of sub-pools (the third is related to the sub-sub pools, i.e. durations, which could however be implemented independently of the sub-pools as well)

    thank you
    kind regards
    Martin

    Comment by Martin Maletinsky — April 23, 2013 @ 7:54 pm BST Apr 23,2013 | Reply

  11. Hello Jonathan,

    Is my understanding correct that the strategy to hold cursors open which you describe starting on page 176 is the same that is done (automatically) by the PL/SQL interpreter when the PL/SQL cursor cache comes in action? Is that what you are saying by “the Oracle pre-compiler allows you to generate code that holds cursors without having to do any special coding”?
    Or phrased differently is my understanding correct that the code on page 177 would behave identically (performance-wise) if you moved the calls to dbms_sql.open_cursor, dbms_sql.parse and dbms_sql.close_cursor inside the loop, as the PL/SQL cursor cache would still keep the cursor open?
    If so, what would be the benefit to implement this in PL/SQL code (as the optimization is done automatically by PL/SQL anyway) – or did you just write the code to demonstrate how the PL/SQL cursor cache works?

    thank you
    kind regards
    Martin

    Comment by Martin Maletinsky — April 23, 2013 @ 8:05 pm BST Apr 23,2013 | Reply

  12. Hello Jonathan,

    Just after I finished reading chapter 7 I faced an ORA-04020 on a client’s production system. The theory I learnt in chapter 7 helped me a lot to understand the context of the problem. However, when I look at the tracefile that was produced, I still have a question (although the problem at the client’s site has been resolved). I include an excerpt of the tracefile below:

    [...]
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning option
    [...]
    System name: AIX
    Node name: [...]
    Release: 1
    Version: 7
    [...]
    Redo thread mounted by this instance: 1
    Oracle process number: 70
    Unix process pid: 11927750, image: oracle@[...] (J003)

    *** 2013-04-25 19:39:54.145
    *** SESSION ID:(471.65) 2013-04-25 19:39:54.145
    *** CLIENT ID:() 2013-04-25 19:39:54.145
    *** SERVICE NAME:(SYS$USERS) 2013-04-25 19:39:54.145
    *** MODULE NAME:(DBMS_SCHEDULER) 2013-04-25 19:39:54.145
    *** ACTION NAME:(AVQ$ISLRM0_42100) 2013-04-25 19:39:54.145

    A deadlock among DDL and parse locks is detected.
    This deadlock is usually due to user errors in
    the design of an application or from issuing a set
    of concurrent statements which can cause a deadlock.
    [...]
    ORA-04020: deadlock detected while trying to lock object K.DEF_SEC_ENV
    ——————————————————–
    object waiting waiting blocking blocking
    handle session lock mode session lock mode
    ——– ——– ——– —- ——– ——– —-
    700000297c255b0 7000002a889eb68 700000285b96050 X 7000002a889eb68 700000285c0a3f0 S
    700000298c42b90 7000002aaa29498 7000002826d79d0 X 7000002a889eb68 700000285c0b700 S
    ——————————————————–
    [...]

    From how I read the deadlock graph, the session identified by 7000002a889eb68 was locking itself as it was holding a shared lock on library cache object 700000297c255b0 and wanted to get an exclusive lock on that object. Moreover this session was holding a shared lock on library cache object 700000298c42b90, which was requested to be locked in exclusive mode by a session identified by 7000002aaa29498.

    The two library cache objects were PL/SQL packages. The scenario was that the client was installing a software upgrade, and forgot to stop all application processes connecting to the database (as would be required), so the solution was to repeat the installation after properly terminating all processes connected to the database.

    The surviving session (7000002a889eb68) was the application process the client forgot to stop, while the session that terminated with an ORA-04020 was one of the processes doing the actual installation.

    The point I don’t understand, is why the above scenario is considered a deadlock. Couldn’t session 7000002a889eb68 just convert it’s shared lock to an exclusive lock (if there were other sessions holding a shared lock on the object it obviously couldn’t, but then I’d expect to see those sessions in the deadlock graph and to be listed as the session blocking session 7000002a889eb68). Also, terminating session 7000002aaa29498 as Oracle did doesn’t resolve the locking graph – and in fact ORA-04020 occurred two more times throughout the faulty installation, each time with session 7000002a889eb68 in the same place in the corresponding deadlock graph, locked on the same object (700000297c255b0) and with two other installation processes being blocked by this session and terminating with an ORA-04020.

    I just add the remainder of the tracefile below in case it contains any information that is relevant to understand the problem.

    Thank you
    kind regards
    Martin

    [...]
    ———- DUMP OF WAITING AND BLOCKING LOCKS ———-
    ——————————————————–
    ————- WAITING LOCK ————-
    —————————————-
    SO: 0x700000285b96050, type: 78, owner: 0x7000002a3ea7fb0, flag: INIT/-/-/0×00 if: 0×3 c: 0×3
    proc=0x7000002aa7250e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

    LibraryObjectLock: Address=700000285b96050 Handle=700000297c255b0 RequestMode=X CanBeBrokenCount=1 Incarnation=2 ExecutionCount=0

    User=7000002a889eb68 Session=7000002ac94e810 ReferenceCount=0 Flags=[0000] SavepointNum=152b0
    LibraryHandle: Address=700000297c255b0 Hash=14f3dcb0 LockMode=S PinMode=S LoadLockMode=0 Status=INVL
    ObjectName: Name=K.BASE_PAR#
    FullHashValue=a198494a548895777a38651614f3dcb0 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) Identifier=274315 OwnerIdn=29
    Statistics: InvalidationCount=1 ExecutionCount=4 LoadCount=6 ActiveLocks=1 TotalLockCount=128 TotalPinCount=145
    Counters: BrokenCount=1 RevocablePointer=2 KeepDependency=0 BucketInUse=306 HandleInUse=306 HandleReferenceCount=0
    Concurrency: DependencyMutex=700000297c25660(0, 600, 0, 0) Mutex=700000297c256e0(0, 1961, 0, 0)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
    Lock=700000297c25640[700000285b960c0,700000285b960c0]
    Pin=700000297c25620[700000297c25620,700000297c25620]
    LoadLock=700000297c25698[700000297c25698,700000297c25698]
    Timestamp: Current=10-13-2012 01:55:44
    HandleReference: Address=700000297c25758 Handle=700000299dc22a8 Flags=OWN[200]
    LibraryObject: Address=70000028f5c0ca8 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=/SWR[0008]
    DataBlocks:
    Block: #=’0′ name=KGLH0^14f3dcb0 pins=0 Change=NONE
    Heap=70000028f5c1c60 Pointer=70000028f5c0d48 Extent=70000028f5c0c28 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=1.773438 Size=3.976562 LoadTime=59819567764
    Block: #=’2′ name=PLDIA^14f3dcb0 pins=1 Change=NONE
    Heap=70000028f5c1110 Pointer=70000028f5bfca8 Extent=70000028f5bfc28 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=54.085938 Size=56.000000 LoadTime=59819567764
    Block: #=’4′ name=PLMCD^14f3dcb0 pins=0 Change=NONE
    Heap=70000028f5c11e8 Pointer=70000028f583278 Extent=70000028f5831f8 Flags=I/-/-/A/-/-
    FreedLocation=0 Alloc=6.960938 Size=8.031250 LoadTime=59819568229 ————- BLOCKING LOCK ————
    —————————————-
    SO: 0x700000285c0a3f0, type: 78, owner: 0x7000002a3f836e0, flag: INIT/-/-/0×00 if: 0×3 c: 0×3
    proc=0x7000002aa7250e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

    LibraryObjectLock: Address=700000285c0a3f0 Handle=700000297c255b0 Mode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

    User=7000002a889eb68 Session=7000002ac94e810 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=3254
    LibraryHandle: Address=700000297c255b0 Hash=14f3dcb0 LockMode=S PinMode=S LoadLockMode=0 Status=INVL
    ObjectName: Name=K.BASE_PAR#
    FullHashValue=a198494a548895777a38651614f3dcb0 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) Identifier=274315 OwnerIdn=29
    Statistics: InvalidationCount=1 ExecutionCount=4 LoadCount=6 ActiveLocks=1 TotalLockCount=128 TotalPinCount=145
    Counters: BrokenCount=1 RevocablePointer=2 KeepDependency=0 BucketInUse=306 HandleInUse=306 HandleReferenceCount=0
    Concurrency: DependencyMutex=700000297c25660(0, 600, 0, 0) Mutex=700000297c256e0(0, 1961, 0, 0)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
    Lock=700000297c25640[700000285b960c0,700000285b960c0]
    Pin=700000297c25620[700000297c25620,700000297c25620]
    LoadLock=700000297c25698[700000297c25698,700000297c25698]
    Timestamp: Current=10-13-2012 01:55:44
    HandleReference: Address=700000297c25758 Handle=700000299dc22a8 Flags=OWN[200]
    LibraryObject: Address=70000028f5c0ca8 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=/SWR[0008]
    DataBlocks:
    Block: #=’0′ name=KGLH0^14f3dcb0 pins=0 Change=NONE
    Heap=70000028f5c1c60 Pointer=70000028f5c0d48 Extent=70000028f5c0c28 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=1.773438 Size=3.976562 LoadTime=59819567764
    Block: #=’2′ name=PLDIA^14f3dcb0 pins=1 Change=NONE
    Heap=70000028f5c1110 Pointer=70000028f5bfca8 Extent=70000028f5bfc28 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=54.085938 Size=56.000000 LoadTime=59819567764
    Block: #=’4′ name=PLMCD^14f3dcb0 pins=0 Change=NONE
    Heap=70000028f5c11e8 Pointer=70000028f583278 Extent=70000028f5831f8 Flags=I/-/-/A/-/-
    FreedLocation=0 Alloc=6.960938 Size=8.031250 LoadTime=59819568229 ————- WAITING LOCK ————-
    —————————————-
    SO: 0x7000002826d79d0, type: 78, owner: 0x7000002a3f13810, flag: INIT/-/-/0×00 if: 0×3 c: 0×3
    proc=0x7000002aa71bb48, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

    LibraryObjectLock: Address=7000002826d79d0 Handle=700000298c42b90 RequestMode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

    User=7000002aaa29498 Session=7000002aaa29498 ReferenceCount=0 Flags=[0100] SavepointNum=126f08
    LibraryHandle: Address=700000298c42b90 Hash=be6d0403 LockMode=S PinMode=S LoadLockMode=0 Status=VALD
    ObjectName: Name=K.DEF_SEC_ENV
    FullHashValue=71ccc043dbfdfb6ee394b13dbe6d0403 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) Identifier=58571 OwnerIdn=29
    Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
    Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=4 HandleInUse=4 HandleReferenceCount=0
    Concurrency: DependencyMutex=700000298c42c40(0, 3, 0, 0) Mutex=700000298c42cc0(0, 175, 0, 0)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
    Lock=700000298c42c20[7000002826d7a40,7000002826d7a40]
    Pin=700000298c42c00[700000298c42c00,700000298c42c00]
    LoadLock=700000298c42c78[700000298c42c78,700000298c42c78]
    Timestamp: Current=11-16-2012 19:10:57
    HandleReference: Address=700000298c42d38 Handle=700000299dc22a8 Flags=OWN[200]
    LibraryObject: Address=700000282c65820 HeapMask=0000-0005-0005-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=NST[0001]
    DataBlocks:
    Block: #=’0′ name=KGLH0^be6d0403 pins=0 Change=NONE
    Heap=7000002984ce330 Pointer=700000282c65910 Extent=700000282c657a0 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=0.882812 Size=4.000000 LoadTime=59819579077
    Block: #=’2′ name=PLDIA^be6d0403 pins=1 Change=NONE
    Heap=700000282c65a68 Pointer=700000282c64820 Extent=700000282c647a0 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=29.679688 Size=32.000000 LoadTime=59819579077 ————- BLOCKING LOCK ————
    —————————————-
    SO: 0x700000285c0b700, type: 78, owner: 0x7000002a3f836e0, flag: INIT/-/-/0×00 if: 0×3 c: 0×3
    proc=0x7000002aa7250e8, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8548 ID:, pg=0

    LibraryObjectLock: Address=700000285c0b700 Handle=700000298c42b90 Mode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

    User=7000002a889eb68 Session=7000002ac94e810 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=375b
    LibraryHandle: Address=700000298c42b90 Hash=be6d0403 LockMode=S PinMode=S LoadLockMode=0 Status=VALD
    ObjectName: Name=K.DEF_SEC_ENV
    FullHashValue=71ccc043dbfdfb6ee394b13dbe6d0403 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) Identifier=58571 OwnerIdn=29
    Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1
    Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=4 HandleInUse=4 HandleReferenceCount=0
    Concurrency: DependencyMutex=700000298c42c40(0, 3, 0, 0) Mutex=700000298c42cc0(0, 175, 0, 0)
    Flags=PIN/TIM/[00002801]
    WaitersLists:
    Lock=700000298c42c20[7000002826d7a40,7000002826d7a40]
    Pin=700000298c42c00[700000298c42c00,700000298c42c00]
    LoadLock=700000298c42c78[700000298c42c78,700000298c42c78]
    Timestamp: Current=11-16-2012 19:10:57
    HandleReference: Address=700000298c42d38 Handle=700000299dc22a8 Flags=OWN[200]
    LibraryObject: Address=700000282c65820 HeapMask=0000-0005-0005-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=NST[0001]
    DataBlocks:
    Block: #=’0′ name=KGLH0^be6d0403 pins=0 Change=NONE
    Heap=7000002984ce330 Pointer=700000282c65910 Extent=700000282c657a0 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=0.882812 Size=4.000000 LoadTime=59819579077
    Block: #=’2′ name=PLDIA^be6d0403 pins=1 Change=NONE
    Heap=700000282c65a68 Pointer=700000282c64820 Extent=700000282c647a0 Flags=I/-/P/A/-/-
    FreedLocation=0 Alloc=29.679688 Size=32.000000 LoadTime=59819579077 ——————————————————–
    This lock request was aborted.

    Comment by Martin Maletinsky — May 1, 2013 @ 5:43 pm BST May 1,2013 | Reply

  13. Hi Jonathan,

    I have couple of small generic queries on Shared pool. Here it goes;

    1. Are V$ROWCACHE and V$DB_OBJECT_CACHE related by any chance? If Yes then how are these related.

    2. I am confused about the way Oracle caches Object related meta-data in Shared pool. My understanding about the fetching of object related metadata which comes from data-dictionary is as below;
    => Oracle pulls data blocks from SYSTEM tablespace in the DB_BUFFER_CACHE.
    => Then Oracle pulls row-by-row data in the ROWCACHE in SHARED_POOL. This is seen in V$ROWCACHE view, say under dc_objects TYPE.
    => Oracle creates exclusive heap for the object in LIBRARY CACHE and this object is shared among all the CURSORS (I guess through pointers from heap0) coming in the LIBRARY CACHE. This heap details can be seen under V$DB_OBJECT_CACHE.
    So I assume that, say a particular TABLE related metadata would be present both in Dictionary cache as well as Library cache although in different structural forms.
    Please correct me if I am wrong

    I would be glad if you could find sometime and throw some light on this please.

    Thanks,
    Vineet

    Comment by Vineet Ranjan — June 21, 2013 @ 7:38 am BST Jun 21,2013 | Reply


RSS feed for comments on this post.

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers