Flexible sampling of any V$ or X$ view with sample.sql

Tanel Poder

2008-08-26

In recent past I’ve blogged few scripts which use specially crafted ordered nested loop for sampling contents of V$ and X$ views fast, with plain SQL.

If you haven’t read them yet, here are the links:

I wrote the above scripts having special purposes in mind (e.g. profile session waits or latching activity).

Now I introduce a simple but powerful sqlplus script for ad-hoc sampling of any V$ view. It’s called…. (drumroll) …. sample.sql :)

When you look into it, the script is actually very simple. It’s just using power of sqlplus substitution variables, I can pass the sampled column and table names and sampling conditions in to the script dynamically.

The basic syntax is:

@sample column_name table_name filter_condition num_samples

For example, let say I have a session which executes lots of SQL statements in a loop and I want to have a quick overview of what’s the TOP SQL statement for a session right now.

SQL> @sample  sql_id  v$session  sid=85  100000

SQL_ID             COUNT
------------- ----------
axwuqacjaxvfv      54658
4y1y43113gv8f      13850
8a1pvy4cy8hgv      13590
82hxvr8kxuzjq      13067
0000000000000       1924
0ybwd63u2any5       1276
cvhk2j2gymzhd        410
                     292
g1xapjmt4vm5c        207
330q95smuwnv9        194
cydgw456rs7z0        192
45bv71pwwj176         76
b5cr4hhndmbuf         76
dy0yxpyu2wcbv         36
gdzdu6ukq8vhs         34
8usapcs6k1b7f         20
20f6a85kwud5c         19
27xdav1rg5g14         18
c7g6u4yj5vsq8         16
2c5p3qrtz23rp         15
3kywng531fcxu         10
1yrpz7tw6d9vg          6
dmgp9whyw67x9          6
gsfnqdfcvy33q          4
5rxbazwmcdfaz          3
dwf9sc3v53hv5          1

26 rows selected.

SQL> select sql_text from v$sql where sql_id = 'axwuqacjaxvfv';

SQL_TEXT
---------------------------------------------------------------------
select /*+ no_parallel_index(t,"STATS$SYSTEM_EVENT_PK") dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_expand index(t,"STATS$SYSTEM_EVENT_PK") */ count(*)
as nrw,count(distinct sys_op_lbid(53892,'L',t.rowid)) as nlb,null as
ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from
"PERFSTAT"."STATS$SYSTEM_EVENT" t where "SNAP_ID" is not null or
"DBID" is not null or "INSTANCE_NUMBER" is not null or "EVENT" is not
null

During the sampling period most of the time the session 85 was executing SQL with id axwuqacjaxvfv. Using sql_id I can easily look up the statement text from V$SQL as I did above (I’m running dbms_stats.gather_database_stats in that demo session). Easy!

But sample.sql is much more flexible than that!

Let say I want to sample multiple columns and have multiple filter conditions. It’s doable, just make sure that you put multiple parameters between doublequotes that sqlplus wouldn’t misunderstand the parameter positions and meanings:

SQL> @sample sql_id,event v$session "sid=85 and state='WAITING'" 100000

SQL_ID        EVENT                               COUNT
------------- ------------------------------ ----------
0ybwd63u2any5 db file sequential read             10717
82hxvr8kxuzjq db file sequential read              8431
8xcd951zh9dtm db file scattered read               6467
31t5pdt54fxa7 db file scattered read               5775
82hxvr8kxuzjq row cache lock                       1420
82hxvr8kxuzjq DFS lock handle                       185
31t5pdt54fxa7 row cache lock                        169
82hxvr8kxuzjq library cache pin                     147
8xcd951zh9dtm db file sequential read                76
31t5pdt54fxa7 db file sequential read                73
82hxvr8kxuzjq rdbms ipc reply                        53
4y1y43113gv8f db file sequential read                39
8xcd951zh9dtm row cache lock                         15

13 rows selected.

The above command just reported what SQL ID’s waited on which events the most for SID 85. Note that the top waiter was waiting during 10717 samples of total 100000 samples (specified in 4th parameter to sample.sql), which is about 10.7% of total response time during that sampling period.

Starting from 10g we have some very nice new columns in V$SESSION, PLSQL_OBJECT_ID and PLSQL_SUBPROGRAM_ID which show in which PL/SQL object and subobject the execution currently is (of course, if that session is currently executing PL/SQL). Also there are two fields called PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID which show you the top level PL/SQL stored proc which was called (and potentially has called other PL/SQL subroutines…)

So, I could profile in which PL/SQL stored procs the execution currently is:

SQL> @sample plsql_object_id,plsql_subprogram_id,sql_id v$session sid=85 100000

PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID SQL_ID             COUNT
--------------- ------------------- ------------- ----------
                                    72kfm3yxd2d3h      50037
                                    ghzub02f1jmsr      30601
                                    acayttu2pgaz1       5693
           4345                  59 82hxvr8kxuzjq       3609
                                    0000000000000       2305
                                    0ybwd63u2any5       1794
                                    85b0rtskxucxj       1665
                                    5qtt0rzhzfkj9        564
           4345                 103 82hxvr8kxuzjq        543
           4345                 101 82hxvr8kxuzjq        448
           4345                  59 85b0rtskxucxj        433
           4365                  95 acayttu2pgaz1        299
           4365                  95 ghzub02f1jmsr        272
           4345                 109 82hxvr8kxuzjq        238
                                    7ng34ruy5awxq        198
           4258                   4 82hxvr8kxuzjq        146
           5357                  86 5qtt0rzhzfkj9        108
                                                          94
           5357                 134 82hxvr8kxuzjq         63

The rows where we see only SQL_ID but no PL/SQL object ID’s mean that Oracle is currently executing/fetching (recursive) SQL.

The rows where we see PL/SQL object IDs and SQL_IDs, tell me that the execution/fetch call of a cursor with that SQL_ID has completed and Oralce’s running PL/SQL code (and hasn’t cleared the SQL_ID yet).

You can go to DBA_OBJECTS or DBA_PROCEDURES to map these object IDs back to stored procedure names:

SQL> select owner, object_name, procedure_name
  2  from dba_procedures
  3  where object_id = 4345
  4  and   subprogram_id = 59;

OWNER      OBJECT_NAME                    PROCEDURE_NAME
---------- ------------------------------ ------------------------------
SYS        DBMS_STATS                     GATHER_DATABASE_STATS

SQL>

Note that historically Oracle has had quite many issues with setting/resetting the sql hash value / sql_id properly so I wouldn’t bet any of my “scientific” analysis of this data. Nevertheless this approach can be useful for getting an overview of which SQLs and PL/SQL subroutines are executed in a session as of now, via SQL.

When you look into the script you will also see that I temporarily set two parameters to false, _optimizer_sortmerge_join_enabled and hash_join_enabled. The reason is that as sample.sql can take any table/view name as a parameter, then I can’t hardcode the underlying X$ table name for USE_NL global hint as I’ve done for WaitProf and LatchProf. Thus with views like V$SESSION I didn’t get the nested loop happening in right order. I wanted it to happen against X$KSUSE, but instead a sort merge join was done, which doesn’t do sampling but scans through inner rowsource only once. So, that’s why I temporarily simply disable hash join and sort merge join in the session and all Oracle is left with is nested loop mechanism.

NB! I must warn you about sampling any view! While sampling views like V$SESSION, V$PROCESS and V$SESSION_WAIT is safe, there are some which you should not sample, well at least in production. X$KSMSP is first which comes into mind as even a single query against it can grab your shared pool latches for way long time, potentially halting all database activity. Another ones are V$SQL and V$OPEN_CURSOR and V$DB_OBJECT_CACHE and like as traversing through their underlying memory structures (library cache) requires quite a lot of library cache latching so normal user SQL execution could be heavily impacted.

However there’s an easy way to check whether an access to V$ view uses latches or not. You just need to sample some V$ view in your test environment and use instructions from my LatchProfX post for checking the latch usage from another session. Note that LatchProfX doesn’t show KGX mutex activity, so be aware that in Oracle 11g the V$SQL / X$KGLOB traversal is done under exclusive protection of Library Cache mutex, thus sampling those tables frequently is still not a good idea (and you don’t really get much out of sampling them thousands of times per second anyway)


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS