Skip to Main Content
  • Questions

  • "proper" way to join v$session and v$sqlarea

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 10, 2025 - 11:17 am UTC

Last updated: May 14, 2025 - 6:09 am UTC

Version: 19

Viewed 100+ times

You Asked

Hello, a few questions:

1. All of the scripts I have written years ago use both address and hash_value to join v$session and v$sqlarea.

What I don't quite understand is why it's not sufficient to just join on hash_value (or sql_id).

Why was/is address also needed?

Doc ID 43716.1 seems to indicate both should be included when doing the join.

SQL_ADDRESS RAW(4|8) Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed.
----> Joins to <<View:V$SQL>> . ADDRESS / HASH_VALUE

SQL_HASH_VALUE NUMBER Used with SQL_ADDRESS to identify the SQL statement that is currently being executed.
----> Joins to <<View:V$SQL>> . ADDRESS / HASH_VALUE

2. If I wanted to instead use v$sqlstats, it seems the best way to join v$session to that view is to use sql_id?

Note my use-case for doing these joins in the above two questions is to get the full SQL text.

3. Why was Bug 27760729 (for SQL_FULLTEXT in v$sqlstats not actually showing the full SQL text) closed out as not a bug?!?

and Connor said...

The SQL_ID is just a different representation of the hash value.

The key thing is that we have efficient access into the V$ structures. Even though they are memory tables, they have "indexes". You can see this with an EXPLAIN PLAN

SQL> set autotrace traceonly explain
SQL> select * from v$sqlstats
  2  where sql_id = '123123';

Execution Plan
----------------------------------------------------------
Plan hash value: 853367051

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                      |     1 | 33346 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KKSSQLSTAT (ind:1) |     1 | 33346 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


You can see that using SQL_ID gets us efficiently into the structure.

Bug 27760729 is due to the way v$sqlstats is structured. To make it fast, its a fixed size object which means we need to cap the size of each "row" (which a CLOB does not allow).

Rating

  (1 rating)

Comments

A reader, May 13, 2025 - 9:03 am UTC

Thank you. What about joining to v$sqlarea? Is the proper way to do such a join to use hash_value (or sql_id) and address together? Or is just hash_value (or sql_id) alone sufficient?

Doc ID 43716.1 seems to indicate both should be included when doing the join.
Connor McDonald
May 14, 2025 - 6:09 am UTC

One or the other or both will be fine

SQL> set autotrace traceonly explain
SQL> select * from v$sqlarea where sql_id = 'blah';

Execution Plan
----------------------------------------------------------
Plan hash value: 2878698642

-----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                                 |     1 | 20164 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD_SQLID (ind:2) |     1 | 20164 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - filter("KGLOBT03"='blah' AND "KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE'))

SQL> select * from v$sqlarea where hash_value = 123123;

Execution Plan
----------------------------------------------------------
Plan hash value: 1696808874

-----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                                 |     1 | 20164 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD_SQLID (ind:1) |     1 | 20164 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - filter("KGLNAHSH"=123123 AND "KGLOBT02"<>0 AND "INST_ID"=USERENV('INSTANCE'))


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here