Addenda and Errata for Oracle Core Chapter 7 Parsing and Optimising
|p.169||Middle of page: “… when I ran the same query against an instance of 220.127.116.11 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
Moreover, in heap dumps we see the expression “desc=” fairly often, e.g.:
HEAP DUMP heap name="sga heap" desc=05977BA0
|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:
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.|
|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.|