Oracle Scratchpad

February 27, 2012

Geek Stuff

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

Jump to Feb 2021 update

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 the use_nl() hint.
  • 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 heap 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 supplie: the plan is a nested loop, which is all that the hint requires. However, the 10g plan starts by scanning x$ksmsp without supplying a heap descriptor, which means 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 a bad plan and 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 into h, IF c is the second table use a nested loop into c; 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.display() 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
;

 

Update (Feb 2021)

While revisiting this note I did a quick check on the v$sql_shared_memory view of 19.3.0.0, and then for a few earlier versions. Starting from 12.2.0.1 the hint had changed. Instead of /*+ use_nl(h,c) */ it has become /*+ use_nl_with_index(h 1) */ which isn’t an improvement.

On the plus side the hint does now restrict itself to demanding that access to h (x$ksmhp) should be by nested loop using an index. The hint is only valid, though, if the table is the second table of a join (which it might not be) and the list of indexes it specifies includes an index that can be used, or if it doesn’t specify any indexes and a suitable index is available.

In this case it appears that there is one index specified and it’s “name” is “1”. This strange choice of name probably reflects the fact that the x$ structures don’t have “real” indexes, and when a plan shows an index access path for an x$ structure the index is referenced only by number – for example, from the plan above:

--------------------------------------------------------------------------------------------
|*  3 |   FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) |     1 |    54 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

So what does Oracle think of this strange numeric index specification in the hint – for 19c we can just look at the hint report:


SQL> explain plan for select * from V$sql_shared_memory;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 1141239260

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 | 17011 |     0   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     1 | 17011 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL       | X$KGLCURSOR     |     1 | 16951 |     0   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KSMHP (ind:1) |     1 |    60 |     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")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
   0 -  SEL$3
         E -  use_nl_with_index(h


It’s a syntax error. You may get the right plan, but if you do it will be due to luck, not correct code.

 

 

7 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 GMT 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 GMT 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 GMT Feb 28,2012 | Reply

  3. […] once, and that was in an internal view definition where it had been used incorrectly (see this recent update to a note on one of the dynamic performance views that I wrote a few years […]

    Pingback by use_nl_with_index | Oracle Scratchpad — March 4, 2021 @ 4:00 pm GMT Mar 4,2021 | Reply

  4. Hi Jonathan,

    I still have a question about use_nl().

    It is clear (somehow) that the following query can generate a nestloop join plan (I am using the example codes in your post of 2017 https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/):

    select
            /*+ use_nl(a b) */
            a.v1, b.v1, c.v1, d.v1
    from
            a, b, c, d
    where
            d.n100 = 0
    and     a.n100 = d.id
    and     b.n100 = a.n2
    and     c.id   = a.id
    ;
    

    But why neither of the following code works ?(I mean that both of them cannot generate a plan contains a nestloop join ):

    query 1

    select
            /*+ use_nl(a) */
            a.v1, b.v1, c.v1, d.v1
    from
            a, b, c, d
    where
            d.n100 = 0
    and     a.n100 = d.id
    and     b.n100 = a.n2
    and     c.id   = a.id
    ;
    

    query 2

    select
            /*+ use_nl(b) */
            a.v1, b.v1, c.v1, d.v1
    from
            a, b, c, d
    where
            d.n100 = 0
    and     a.n100 = d.id
    and     b.n100 = a.n2
    and     c.id   = a.id
    ;
    

    If oracle generates a default join order before applying the hint. One of table a and table b could be an inner table in that order.

    Comment by winter — October 15, 2021 @ 8:34 am BST Oct 15,2021 | Reply

  5. […] question has just appeared on a note I wrote in 2017 about the incorrect use of the use_nl() hint in some sys-recursive SQL, linking back to an […]

    Pingback by use_nl redux | Oracle Scratchpad — October 15, 2021 @ 2:58 pm BST Oct 15,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: