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)
