Geek Stuff

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 use 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
;