sqlmemh.sql sql_id or hash_value

[patrickjolliffe]

Help and code don't match up, the help says it's using sql_id, but it's actually using hash_value.

prompt Show shared pool memory usage of SQL statement with SQL_ID &1

FROM
v$sql
WHERE
hash_value = &1
/

(confused me for about 15 seconds, i'd fix it and do a Pull Request, but I don't know which it should be)

[tanelpoder]

Hello, I recall changing the sqlmem.sql to use SQL_ID (I wrote it many years ago before 10g adn SQL_IDs) and sqlmemh.sql is the same thing, but takes a hash value as an argument. (Pull the latest code if it's different for you and feel free to submit additions to help.sql :-)

[tanelpoder]

Aalso I see that the PROMPT code says "SQL_ID" in both scripts, feel free to send a PR for that too!

[patrickjolliffe]

OK, done. Fell totally down a rabbit hole in that on the version of the database I am working on (19.3) v$sql_shared_memory is broken, eg

1. direct query for equality on sql_id or hash_value returns no rows - doing "like <sql_id>%" seems to work though.
2. Bug 19340498 - CDB:NO ROWS RETURNED WHEN QUERYING V$SQL_SHARED_MEMORY INSIDE A PDB
3. Even in CDB direct query against v$sql_shared_memory seemed to return no rows, but weirdly with the hints in your query (maybe that's why they are there) it did...
   I didn't know how to do a pull request, but luckily chatgpt did, let me know if i've messed it up at all.

[tanelpoder]

Yep the v$sql_shared_memory / x$ksmhp only work when it's using the "fixed index" based access into a specific heap descriptor address (it doesn't walk through all the heaps that it can find and dump them). So the USE_NL hint (and in later versions the USE_NL_WITH_INDEX) in the v$sql_shared_memory view make optimizer come up with a NL plan that gets interesting heap addresses from x$kglcursor and then uses a nested loop to look up each heap descriptor with its exact memory address using the x$ksmhp "heap dump" x$view...

SQL> @xde x$ksmhp
Describe X$ tables and show indexed columns...

TABLE_NAME                COLUMN_NAME                    DATA_TYPE              KQFCOSIZ     OFFSET OFF_HEX    IDX
------------------------- ------------------------------ -------------------- ---------- ---------- --------- ----
X$KSMHP                   ADDR                           RAW(8)                        8          0      0x0
                          INDX                           NUMBER(4)                     4          0      0x0
                          INST_ID                        NUMBER(4)                     4          0      0x0
                          CON_ID                         NUMBER(2)                     2          0      0x0
                        **KSMCHDS                        RAW(8)                        8          0      0x0     1**
                          KSMCHCOM                       VARCHAR2(16)                 16         18     0x12
                          KSMCHPTR                       RAW(8)                        8         40     0x28
                          KSMCHSIZ                       NUMBER(8)                     8         48     0x30
                          KSMCHCLS                       VARCHAR2(8)                   8         56     0x38
                          KSMCHTYP                       NUMBER(2)                     2         64     0x40
                          KSMCHPAR                       RAW(8)                        8         72     0x48
                          KSMCHOWN                       RAW(8)                        8          8      0x8