Oracle Scratchpad

February 27, 2012

Geek Stuff

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 5:50 pm BST Feb 27,2012

A recent post on the OTN database forum raises a problem with v$sql_shared_memory:

query to V$SQL_SHARED_MEMORY don’t return rows

please explain why ?

A follow-up posting then describes how the OP picked the view definition from v$fixed_view_definitions and used the text of that query instead of the view itself – and still got no rows returned:

SELECT * FROM
(select /*+use_nl(h,c)*/ c.inst_id,kglnaobj,kglfnobj, kglnahsh, kglobt03, kglobhd6, rtrim(substr(ksmchcom, 1, instr(ksmchcom, ‘:’, 1, 1) – 1)), ltrim(substr(ksmchcom, -(length(ksmchcom) – (instr(ksmchcom, ‘:’, 1, 1))), (length(ksmchcom) – (instr(ksmchcom, ‘:’, 1, 1)) + 1))), ksmchcom, ksmchptr, ksmchsiz, ksmchcls, ksmchtyp, ksmchpar from x$kglcursor c, x$ksmhp h where ksmchds = kglobhd6 and kglhdadr != kglhdpar)
WHERE ROWNUM < 3;

The answer is quite simple – but in two parts.

First, the developer who wrote the view definition doesn’t understand how to use hints. Secondly, x$ksmhp is (as far as I can tell) the result set from a call to a subroutine that takes a heap descriptor as an input and returns details of that subheap formatted to look like an ordinary row source.

The query is supposed to report sub-heap 6 of every child cursor Рbut to get any results you need to get the descriptor for the sub-heap from a child cursor before calling the subheap, which means the execution plan for the query has to do a nested loop join based on the predicate ksmchds = kglobhd6 and it has to visit x$kglcursor (which is one of the derived versions of x$kglob) before it visits x$ksmhp.

So here’s the execution plan from a copy of 10g that I have handy where the query select * from V$sql_shared_memory returns no rows, followed by the plan from a copy of 11g where the query returns thousands of rows. Spot the difference:

Execution Plan (10g)
----------------------------------------------------------
Plan hash value: 2632394999

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |  2604 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS     |             |     1 |  2604 |     0   (0)| 00:00:01 |
|   2 |   FIXED TABLE FULL| X$KSMHP     |     1 |    54 |            |          |
|*  3 |   FIXED TABLE FULL| X$KGLCURSOR |     1 |  2550 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C"."INST_ID"=USERENV('INSTANCE') AND
              "KGLHDADR"<>"KGLHDPAR" AND "KSMCHDS"="KGLOBHD6")

Execution Plan (11g)
----------------------------------------------------------
Plan hash value: 1141239260

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |  2604 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 |  2604 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL       | X$KGLCURSOR     |     1 |  2550 |     0   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) |     1 |    54 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C"."INST_ID"=USERENV('INSTANCE') AND "KGLHDADR"<>"KGLHDPAR")
   3 - filter("KSMCHDS"="KGLOBHD6")

In both cases the queries have followed the hint supplied – the plan is a nested loop, and that’s all that the hint requires. However, the 10g plan starts by scanning x$ksmsp without supplying a heap descriptor, so that part of the plan can’t return any rows, so the nested loop returns no rows. The 11g plan scans the x$kglcursor structure to find the heap descriptors, and calls x$ksmhp for each descriptor in turn.

If you want to work around the problem of no data, you could try hinting the query against v$sql_shared_memory (or create your own replacement view) which hints the join properly. (Note that the current hint on the view definition simply says: if h is the second table in the join use a nested loop, if c is the second table use a nested loop; the hint does NOT mean “use a nested loop with h as the first table and c as the second”). Here’s a possible hint set that works 10.2.0.3 – I identified the necessary query block name by using the ‘outline’ option of dbms_xplan to check the execution plan:


select
	/*+
		leading(@sel$5c160134 c@sel$3 h@sel$3 )
		use_nl( @sel$5c160134 h@sel$3)
	*/
	*
from
	v$sql_shared_memory
;

3 Comments »

  1. Hi Jonathan,

    Now that’s an odd one, I like that.

    If you were replacing the query would you be safe to simply place an ORDERED hint before the use_nl hint to ensure the table order and thus the working of the code.
    In fact, as it is so critical in this case one wonders why it is not stated in that way.

    Could the query block name change in a future version, rendering your hint invalid – but by using your own view you would be protected against such things? Mind you, if the query block name changes, that may well be because the view definition has changed so you would need to alter your hand-crafted view too…

    Martin

    Comment by mwidlake — February 27, 2012 @ 6:13 pm BST Feb 27,2012 | Reply

    • Martin,

      For any recent version of Oracle the leading() hint is more appropriate than the ordered() hint, but in principle hinting the underlying gv$sql_shared_memory view is the appropriate fix from Oracle.

      The GV$ view has been around since at least 8.1.7.4, so I suspect the developer who wrote it thought that the hint use_nl(h c) was specifying the order of the join – it’s only in very recent releases of the manuals that Oracle has stated explicitly that this form of the hint is a short-hand for /*+ use_nl(h) use_nl(c) */ and not a multi-table hint.

      I wouldn’t expect the query block name to change unless Oracle did something to introduce more (or differently named) query blocks in the GV$ view – and we hope they’d only do that if they fixed the thing to work properly anyway.

      Comment by Jonathan Lewis — February 27, 2012 @ 7:14 pm BST Feb 27,2012 | Reply

  2. Hello Mr. Jonathan,

    thank You for Yore expaination!

    Best regards,
    Paul

    Comment by Paul — February 28, 2012 @ 8:56 am BST Feb 28,2012 | 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers