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.
p.192	First paragraph, list points: ” We do the syntactic check and determine that it’s legal SQL. We hash the string to get a hash value, work out the relevant library cache hash bucket, and check the bucket to see if we can find a matching string.”  In fact the SQL may have been inserted into the library cache before the syntax check takes place; see this blog note and the comments it elicited from readers.

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