v$fixed_view_definition

In one of my notes about excavating the x$ tables I said that there was a problem “hidden in plain sight” with the dynamic performance view (g)v$fixed_view_definition. If you haven’t previously spotted the problem here’s what the view looks like when you describe it:

SQL> desc gV$fixed_view_definition
 Name                                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 INST_ID                                            NUMBER
 VIEW_NAME                                          VARCHAR2(128)
 VIEW_DEFINITION                                    VARCHAR2(4000)
 CON_ID                                             NUMBER

and if that doesn’t make you suspicious, here’s a clue:

SQL> select  substr(view_definition,3950,50) tail_end
  2  from    gv$fixed_view_definition
  3  where   view_name = 'GV$SESSION'
  4  /

TAIL_END
--------------------------------------------------------------------------------
tand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bit

Once upon a time the dynamic performance views were much simpler and their definitions would all have fitted inside a varchar2(4000) – but those were the good old days and things have moved on since then.

The view gv$session is one of the biggest offenders as you can’t even get to the list of x$ structures in the from clause into the first 4,000 bytes. If you can’t see the entire definition it can be a little difficult to work out why a query against one of these views is performing badly; it is still possible, of course, but harder than it needs to be.

If you need to work around this issue one strategy would be ask Oracle where it’s hidden the view definition in shared memory. It’s not the easiest option but it may be the only one available to you. You start by modifying the defining query for gv$fixed_view_definition into something that will report the address of the row in x$kqfvt that holds the view text:

select 
        i.addr, t.addr 
from 
        x$kqfvi i, 
        x$kqfvt t
where 
        i.kqfvinam = 'GV$SESSION'
and     t.indx = i.indx
/

ADDR             ADDR
---------------- ----------------
00000000143490A0 00000000143660C0

If you use the x_rowsize.sql script I published a little while ago to see what it says about the lengths of x$kqfvt rows you’ll find that the row length is 32 bytes, though if you check the definition of x$kqfvt using the x_desc.sql script from the original “excavating x$” notes you find the following:

Column                      Ext Type   Int Type  Array Max   Len Size Len Offset   Col Size     Offset      Index    Idx Col
------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ADDR                              23          9          0          0          0          8          0          1          0
INDX                               2         11          0          0          0          4          0          2          0
INST_ID                            2         11          0          0          0          4          0          0          0
CON_ID                             2         11          0          0          0          2          0          0          0
KQFTPSEL                           1          6          0          0          0       4000          0          0          0

The only real column in the structure is KQFTPSEL and the “type” columns tell us that it is a pointer (8 bytes) to a string of 4000 bytes – which makes the 32 byte gap between rows a little odd, so let’s “peek” the 32 bytes starting at address 0x143660C0 to see what else (if anything) we can find in that row:

SQL> oradebug setmypid
Statement processed.

SQL> oradebug peek 0x143660C0 32 
[0143660C0, 0143660E0) = 14598D40 00000000 143754A0 00000000 00000000 00000000 00000000 00000000

That looks like 2 addresses (of 8 bytes each) and 16 null bytes which I can’t explain. The first address should get us to the view text, and I’m curious about the second address, so I’ll do a couple of large peeks for these two addresses, and dump the results into my session trace file by appending a ‘1’ to the command:

SQL> oradebug peek 0x14598D40 256 1
[014598D40, 014598E40) = 656C6573 73207463 736E692E 64695F74 612E732C 2C726464 6E692E73 732C7864 75736B2E 65736573 2E732C72 7575736B 73657364 6B2E732C ...

SQL> oradebug peek 0x143754A0 256 1
[0143754A0, 0143755A0) = 00000007 00000000 139779D0 00000000 00000005 00000000 1455A464 00000000 00000003 00000000 14BD2FAC 00000000 00000007 00000000 ...

The first peek looks promising – the hex values are in the “ASCII alphanumerics” range and reading each block of 4 bytes from right to left we get: ‘sele’ ‘ct t’ ‘.ins’ ‘t_id’ which, when we string it together starts us off with “select inst_id” which is the start of the view text.

The second peek looks as if it’s reporting pairs of (length of string, pointer to string) so I peeked at the first few addresses and found the strings: “INST_ID“, “SADDR“, “SID“, “SERIAL#” which looks like the first few names of the columns in gv$session.

Of course we haven’t yet seen anything telling us the length of the view text or the number of columns in the view – but maybe that’s in the x$kqfvi structure, and you’ll notice that I included the address (addr) of from that structure in my query. Here’s what I get from peekin the 80 bytes (the row size indicated by x_rowsize.sql) from that address:

0143490A0 0000000A 00000000 145528BC 00000000  [.........(U.....]
0143490B0 00000004 00000000 12F5D384 00000000  [................]
0143490C0 00000003 00000000 00000000 00000000  [................]
0143490D0 0000119F 00000000 00000068 00000000  [........h.......]
0143490E0 FFFFC15A 00000022 00000000 00000000  [Z..."...........]

The first line is a pointer to the character string “GV$SESSION” (0xA is the length), the second line is a point to the character string “NULL” (0x4 is the length) – but I don’t know why that’s there (yet). I don’t know what the third line is about. The fourth line holds the numbers 4511 (dec) and 104 (dec). Since the latter is the number of columns in gv$session would anyone cater to guess that the former is the length of the view text) The last line reports the kqfviobj and kqfviver as seen in the view definition.

The interesting question is whether we can find the full text of the view at the address we peeked because it’s possible that the presentation of the view definition has simply been stopped at the 4,000 bytes mark when it does actually continue in memory. So let’s do a bigger peek of the first address we found in x$kqfvt and check the trace file:

SQL> oradebug peek 0x14598D20 4600 1
[014598D20, 014599F18) = 74736E49 65636E61 00202927 00000000 00000000 00000000 00000000 00000000 656C6573 73207463 736E692E 64695F74 612E732C 2C726464 ...

SQL> ed or19_ora_28809.trc 

I’ve actually started my peek 32 (0x20) bytes earlier than the target address because of the way that Oracle fails to format the first few peeked bytes in the trace file. By peeking a little early I’ll get the whole section that I want to see formatted into readability:

Processing Oradebug command 'peek 0x14598D20 4600 1'
[014598D20, 014599F18) = 74736E49 65636E61 00202927 00000000 00000000 ...
Dump of memory from 0x014598D34 to 0x014599F18
014598D30          00000000 00000000 00000000      [............]
014598D40 656C6573 73207463 736E692E 64695F74  [select s.inst_id]
014598D50 612E732C 2C726464 6E692E73 732C7864  [,s.addr,s.indx,s]
014598D60 75736B2E 65736573 2E732C72 7575736B  [.ksuseser,s.ksuu]
014598D70 73657364 6B2E732C 65737573 2C6F7270  [dses,s.ksusepro,]
014598D80 736B2E73 6C647575 732C6975 75736B2E  [s.ksuudlui,s.ksu]
...
014599E50 20292745 6D6F7266 6B247820 65737573  [E') from x$ksuse]
014599E60 202C7320 736B2478 2064656C 78202C65  [ s, x$ksled e, x]
014599E70 6C736B24 77207477 65687720 62206572  [$kslwt w where b]
014599E80 6E617469 2E732864 7073736B 676C6661  [itand(s.ksspaflg]
014599E90 2129312C 6120303D 6220646E 6E617469  [,1)!=0 and bitan]
014599EA0 2E732864 7375736B 676C6665 2129312C  [d(s.ksuseflg,1)!]
014599EB0 6120303D 7320646E 646E692E 2E773D78  [=0 and s.indx=w.]
014599EC0 776C736B 64697374 646E6120 6B2E7720  [kslwtsid and w.k]
014599ED0 74776C73 3D747665 6E692E65 00007864  [slwtevt=e.indx..]
014599EE0 656C6573 20207463 44444153 202C2052  [select  SADDR , ]
014599EF0 20444953 4553202C 4C414952 202C2023  [SID , SERIAL# , ]
014599F00 53445541 2C204449 44415020 2C205244  [AUDSID , PADDR ,]
014599F10 45535520 2C202352                    [ USER# ,]

I got a little lucky with my guestimate of how much to peek and, as you can see I’ve got the whole of the view definition (which means I can now see all the tables in the from clause and all the predicates in the where clause).

I can now do some tedious work to edit the trace file to extract the full view and make it look pretty. On the other hand you may think that’s too much like hard work so, as an alternative, you might note that the view definition references x$ksuse, x$ksled and x$kslwt in that order, which means you might get lucky if you just search the executable to see if you can find those three words very close to each other:

[oracle@linux183 trace]$ cd $ORACLE_HOME/bin

[oracle@linux183 bin]$ strings -a oracle | grep -n "x\$ksuse.*x\$ksled.*x\$kslwt"  >temp.txt

[oracle@linux183 bin]$ wc temp.txt
   1  142 4519 temp.txt

We’ve found one line of 4,511 characters (plus 8 due to the -n option) matching our grep search criteria – and it’s the full text of the view.

If you want to find which other views lose the ends of their definitions a simple query will tell you:

set pagesize 60
set linesize 100
set trimspool on
set tab off

column view_name format a32
column view_definition format a60 wrap word

select  view_name, view_definition
from    v$fixed_view_definition
where   length(view_definition) = 4000
/

There were 18 of them in my 19.11.0.0 instance.

Summary

If you’re after the select statements defining any of the dynamic performance views they can be found in the view (g)v$fixed_view_definition. Unfortunately this will report only the first 4,000 bytes of the definition as will x$kqfvt, the x$ structure that exposes the defining text. (This doesn’t change, by the way, if you have set max_string_size to EXTENDED.)

The 4,000 byte limit is an artificial limit imposed by the limits on the external varchar2() type, and the information in the x$kqfvt structure actually points to a string holding the entire view definition. We can use oradebug to peek at the contents of a row in x$kqfvt using the addr column as the target address for peeking. The first 8 bytes will report the address where the view text is located, so we can use oradebug again to peek at that address (less 32 to get the whole string converted to character format) to tell Oracle to dump the entire string into the trace file.

Since it’s likely to be a tedious job to stitch the view text into a single piece from the 16 byte fragments dumped by Oracle you could use the strings o/s command on the oracle executable and search the result for a recognizable piece of text that will pick out just the view definition. If you can’t access the executable, though, the trace file may be the only option – and don’t forget that you may be able to read your trace file from v$diag_trace_file_contents through an SQL*Plus connection.