Oracle Scratchpad

March 21, 2022

X$ row sizes

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 10:59 am GMT Mar 21,2022

Here’s a script I’ve used from time to time to help me get some ideas about how to hack through some x$ tables. It’s obviously something that can only be run by the SYS schema and it’s not something to apply arbitrarily to every x$ table as the side effects could be nasty but, on a sandbox instance with enough CPUs and a large enough SGA, it might give you some interesting clues about how Oracle is defining or accessing some of its internal structures.

The first query simply walks through the specified x$ table in index (indx) order comparing the address (addr) of the preceding row with that of the current row and reporting the difference. Some of the x$ tables are likely to give you a random collection of large numbers, which means there is no well-structured row involved. Others will give very regular patterns – though not necessarily a constant value as you move from row to row (take a look at x$ksuru, for example).

The second query takes the lowest and highest addresses of rows in the table, finds the difference and divides by one less than the number of rows in the table (i.e. allowing for the fact that the highest address is the start of a final row, not the end of a row). If the average size value matches the value reported by every other row in the output that’s a fairly strong clue that the x$ table may be a simple array of fixed sized rows.

rem
rem     Script:         x_rowsize.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2002 (probably)
rem
rem     Usage:
rem             Connect as SYS (due to x$)
rem             start x_rowsize {x tablename} {number of rows to list}
rem

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

prompt  ================================================
prompt  Usage:  @x_rowsize {table_name} {number_of_rows}
prompt  ================================================
pause   Press return to continue

define m_xtab  = &1
define m_rowct = &2

column addr_dec         format 999,999,999,999,999
column lag_addr_dec     format 999,999,999,999,999
column row_size         format 999,999,999,999,999

spool x_rowsize

select 
        addr,
        to_number(addr,'XXXXXXXXXXXXXXXX') addr_dec, 
        to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') lag_addr_dec, 
        to_char(
                to_number(addr,'XXXXXXXXXXXXXXXX') -
                        to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx'),
                        'xxxxxxxxxxxxxxxx'
        ) row_size_hex,
        to_number(addr,'XXXXXXXXXXXXXXXX') -
                to_number(lag(addr,1) over (order by indx),'xxxxxxxxxxxxxxxx') row_size,
        indx 
from    &m_xtab
where   indx <= &m_rowct
order by 
        indx
/

prompt  ========================================================
prompt  Average row size will (almost certainly) not match the
prompt  row gap listed in the above if the x$ table is segmented
prompt  ========================================================

select
        min(addr), max(addr), count(*),
        (to_number(max(addr),'xxxxxxxxxxxxxxxx') -
                to_number(min(addr),'xxxxxxxxxxxxxxxx')
        ) / (count(*) - 1) avg_row_size
from    &m_xtab
/

spool off

It’s a good idea to start with a small sample size in case a complete scan of a structure crashes the session or even the instance- so here’s the result of calling the script with the parameters x$ksuse 10

===============================================
Usage:  @x_rowsize {table_name} {numbe_of_rows}
===============================================
Press return to continue


ADDR                         ADDR_DEC         LAG_ADDR_DEC ROW_SIZE_HEX                  ROW_SIZE       INDX
---------------- -------------------- -------------------- ----------------- -------------------- ----------
000000008E713678        2,389,784,184                                                                      1
000000008E715E60        2,389,794,400        2,389,784,184              27e8               10,216          2
000000008E718648        2,389,804,616        2,389,794,400              27e8               10,216          3
000000008E71AE30        2,389,814,832        2,389,804,616              27e8               10,216          4
000000008E71D618        2,389,825,048        2,389,814,832              27e8               10,216          5
000000008E71FE00        2,389,835,264        2,389,825,048              27e8               10,216          6
000000008E7225E8        2,389,845,480        2,389,835,264              27e8               10,216          7
000000008E724DD0        2,389,855,696        2,389,845,480              27e8               10,216          8
000000008E7275B8        2,389,865,912        2,389,855,696              27e8               10,216          9
000000008E729DA0        2,389,876,128        2,389,865,912              27e8               10,216         10

10 rows selected.

========================================================
Average row size will (almost certainly) not match the
row gap listed in the above if the x$ table is segmented
========================================================

MIN(ADDR)        MAX(ADDR)          COUNT(*) AVG_ROW_SIZE
---------------- ---------------- ---------- ------------
000000008E713678 000000008EDEDD70        704   10222.1451

1 row selected.

At first sight it looks as if the rowsize for an x$ksuse (v$session) rows is 10,216 bytes – but then the average rowsize turns out to be different, so there’s some inconsistency somewhere in x$ksuse that we can’t see in the first few rows.

After running the report for the whole x$ksuse structure I realised that a quick and easy edit would report just the interesting bits. I folded the original query into an inline view and selected only the rows where “row_size != 10216”. Here’s the result:

ADDR                         ADDR_DEC         LAG_ADDR_DEC ROW_SIZE_HEX                  ROW_SIZE       INDX
---------------- -------------------- -------------------- ----------------- -------------------- ----------
000000008E8CAB98        2,391,583,640        2,391,571,984              2d88               11,656        177
000000008EA820B8        2,393,383,096        2,393,371,440              2d88               11,656        353
000000008EC395D8        2,395,182,552        2,395,170,896              2d88               11,656        529

That looks like an interesting pattern – especially if you’re good at mental arithmetic (or have a calculator handy) and I tell you that there are 704 rows in x$ksuse.

The way I’ve written my code the row size reported for indx = n is the length of row n-1, so out of the 704 rows in x$ksuse, the “interesting” rows are 176, 352, and 528 and when you check the arithmetic you realise:

  • 176 = 704 / 4,
  • 352 = 704 / 2,
  • 528 = 704 * 3/4

Maybe it’s not a coincidence that this instance is running on 4 CPUs; moreover, with that thought in mind, if I look at v$latch_children to see if there are any latches with 4 children I find (amongst others) the following latch names:

client/application info
session allocation
session idle bit
session switching

There may be other latches that are also relevant but these 4 latches have names that suggest you might want to acquire one of them if you wanted to modify the contents of a session-related row – in other words if you wanted to modify the contents of a row in x$ksuse. So maybe Oracle has checked the cpu_count on startup and created the v$session/x$ksuse array in equal sized pieces, one for each CPU, so that it can minimise contention when try to modify data in x$ksuse. And following that thought I’ll just add that I had set sessions = 702 in my parameter file, and Oracle had rounded that up to a multiple of 4.

The hypothesis is easy to check – just bounce the database a few times, using a pfile rather than an spfile, change the cpu_count and sessions parameter each time and see if Oracle always rounds sessions up (if needed) to a multiple of cpu_count and then splits the array into cpu_count equal pieces.

And that certainly seemed to be the case on my little machine. (Obviously I’m not in a position to see what would happen on a machine with 256 CPU and sessions set to 16,000 – so there might be some sanity checks for extreme cases.)

Jumping to conclusions

Going back to the output from my initial setup, it looks as if each row in x$ksuse is going to demand 10,216 bytes, and that there may be 1,440 (viz: 11,656 – 10,216) bytes needed as some sort of “header” for each segment of the array, but what sort of header could need 1,440 bytes? I can’t help noticing that if you wanted to store 176 pointers of 8 bytes each that’s a total of 1,408 bytes – so maybe the “header” is mostly a list of pointers to the rows – just like the row directory in a data block!

It’s a thought that might be heading in the right direction, but perhaps it’s a little premature. Fortunately on one of my restarts one of the gaps showed up as roughly minus 30,000,000. So I need to find an explanation that covers this case as well – but that’s okay because I soon came up with a hypothesis (that I haven’t actually confirmed with a reproducible model yet).

I know that my memory granule size is 16MB (select * from V$sgainfo). What if Oracle had allocated the first few sections of x$ksuse and then found that it didn’t have enough room left in the current granule for the next piece of the array? It would have to start using another granule. What if the granules were attached to the shared pool “from the top downwards” – the next granule would have a starting address that was 16MB lower than the current granule – so the step from the end of the current granule to the beginning of the next granule would be around 32MB backwards. Allowing for the fact that I was experimenting with numbers that produced sizes of about 2MB (200 rows @ 10,216 bytes) for each piece of the array the hypothesis is reasonably consistent with the step size of minus 30 million.

Further experimentation is left as an exercise for the interested reader, but if I wanted to test the granule hypothesis I could probably experiment with carefully selected values for sessions, maybe starting the instance with a reduced granule size (parameter _ksmg_granule_size) to see if I could get two or more backwards jumps in the array. But I think I’ve pursued x$ksuse far enough for the moment. Except I have one more (generic) problem that it highlights.

It’s not that simple

If I query x$kqfta – the list of x$ tables, I see the following for x$ksuse:

SQL> execute print_table('select * from x$kqfta where kqftanam = ''X$KSUSE''')
ADDR                           : 00000000143D1DD0
INDX                           : 43
INST_ID                        : 1
CON_ID                         : 0
KQFTAOBJ                       : 4294951004
KQFTAVER                       : 42
KQFTANAM                       : X$KSUSE
KQFTATYP                       : 2
KQFTAFLG                       : 1
KQFTARSZ                       : 8
KQFTACOC                       : 113

-----------------

1 row(s) selected

The column kqftarsz is the “Row SiZe” and 8 is a long way off the 10,216 that I’ve derived from the addresses of consecutive rows in the table. So how does an 8 byte column turn into a 10,216 byte row? (I wonder if it’s describing the rows in the table of pointers to the rows, not the length of the rows themselves). But (apparently) it’s neither 8 nor 10,216 anyway because if I look at x$kqfco (the list of x$ columns) for x$ksuse I see the following):

select
        co.indx,
        co.kqfconam     column_name,
        co.kqfcosiz     column_size,
        co.kqfcooff     column_offset
from
        x$kqfta ta,
        x$kqfco co
where
        co.kqfcotab = ta.indx
and     ta.kqftanam = 'X$KSUSE'
order by
        co.kqfcooff,
        co.indx
/

      INDX COLUMN_NAME                      COLUMN_SIZE COLUMN_OFFSET
---------- -------------------------------- ----------- -------------
       503 ADDR                                       8             0
       504 INDX                                       4             0
       505 INST_ID                                    4             0
       506 CON_ID                                     2             0
       507 KSSPAFLG                                   4             0
       508 KSSPAOWN                                   8             0
...
       603 KSUSELTXIDFLG                              4          9636
       604 KSUSELTXID                                 8          9648
       615 KSUSEFLG3                                  4         10000

According to this the last column of the row starts at byte 10,000 and ends at 10,003 giving us a third possible length for the row and a gap of 212 bytes to explain; and even though there are several x$ tables starting at the same address (x$ksuse, x$ksusio, x$lsusecst, x$ksuru, x$ksusm) on the same internal structure none of them has a column definition explaining the gap.

It looks as if any further investigation (of x$ksuse at least) will have to depend on dumping memory memory to trace files and chasing pointers.

Summary

When you try to find out how big a “row” in an x$ structure is you may find several pieces of contradictory information:

  1. The rowsize (kqftarsz) reported in x$kqfta.
  2. The offset + length of the final column in the description in x$kqfco.
  3. The “gap” between consecutive rows when you query addr in the object itself

Using oradebug to peek a couple of rows may help you uncover the truth. But you may end up chasing pointers to pointers to pointers to get to the ultimate source.

March 20, 2022

ANSI catalogue

Filed under: Oracle — Jonathan Lewis @ 8:19 am GMT Mar 20,2022

This is a list of articles I’ve written that pick up some detail about using features of the ANSI syntax that are (or were) less commonly used in traditional Oracle SQL.Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

March 18, 2022

v$ v_$ v$

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 5:33 pm GMT Mar 18,2022

One of the “curiosity” questions that comes up from time to time on the various forums is the one about the cascade of names from synonym to view to object that Oracle uses to provide access to the dynamic performance views. Typically we see the pattern:

  • v$XXX is a public synonym for a stored view v_$XXX
  • v_$XXX is defined as ‘select {list of columns} from v$XXX

So, after two steps, we seem to be back where we started. Why has Oracle done this? I came up with the hypothesis a long time ago that there were two parts to the answer; the first is a privilege requirement, the second is for convenience.

SYS is a very special user (as many people have found when running SQL in the sys schema and finding that it doesn’t behave in exactly the same way it does in every other schema) and access to the dynamic performance views is one of the most exotic features of the SYS account. Underneath the respectable veneer of the dynamic performance (v$) views lies the murky horror that is the x$ layer where an “table” may turn out to be a function call, some form of memory array, a linked list, or a hideously complex structure that needs to be filtered, fragmented, reassembled and sorted to yield something that looks like a row containing useful information – an “object” may even be a function that reads an array of pointers to linked lists of hideously complex structures.

So the dynamic performance views hide nasty things and SYS is the only schema allowed to execute the Oracle kernel code to interpret and display those things. But the developers of tools like EM may find it highly desirable to get access the contents of the dynamic performance views – so Oracle creates “real” (i.e. stored) views in the SYS schema to expose the contents of the dynamic performance views and may grant select privileges on those stored views to ordinary users. Of course, since the views are created by SYS the stored queries defining the view contents operate with the privileges of SYS , which means the views can execute the kernel code and return the correct results.

Everyone happy so far?

So now a developer writes a really terrific query that they test very carefully against the dynamic performance view to make sure it’s producing the right results. And when it’s working they have to write a new version of the query using the names of the stored view rather than the names of the dynamic performance views because they also want to include their query in an Enterprise Manager / Grid Control monitor screen.

But why have two versions of a query when, by creating a suitable set of synonyms, a single version of the query will work. Choose your synonym names carefull and when the super query is run by SYS the parser will interpret names as direct references to the dynamic performance views and when it’s run by any other user (who’s had select granted on the stored views) it will translate names of synonyms to names of stored views and onwards to the names of dynamic perfermance views.

So: the stored views make it possible for non-SYS users to run SQL with the privileges of SYS; the cunning use of synonyms means the same SQL text can be run by SYS and non-SYS users and mean the same thing.

Lagniappe

There’s one dynamic performance view that breaks the general pattern, the one about sequences. Try running the following as SYS – the first triplet demonstrates the general pattern – the second doesn’t:

select 'dynamic' object_type, kqfvinam     object_name from x$kqfvi       where kqfvinam     like 'V%SQLAREA'
union all
select 'real view',           view_name                from dba_views     where view_name    like 'V%SQLAREA'
union all
select 'synonym',             synonym_name             from dba_synonyms  where synonym_name like 'V%SQLAREA';

select 'dynamic' object_type, kqfvinam     object_name from x$kqfvi       where kqfvinam     like 'V%SEQUENCES'
union all
select 'real view',           view_name                from dba_views     where view_name    like 'V%SEQUENCES'
union all
select 'synonym',             synonym_name             from dba_synonyms  where synonym_name like 'V%SEQUENCES';

The dynamic performance view v$_sequences doesn’t follow the pattern. There is no associated stored view, hence no possibility of a working synonym. (The dynamic performance view is also unusual in having an underscore after the v$, maybe that’s why some automatic code to generate the view and synonym didn’t work for it ;)

You could argue that v$_lock and v$_lock1 also break the pattern, but they’re just Oracle flailing around frantically but deliberately invisibly under v$lock, which does follow the pattern.

You might also point out that the one pair of dynamic performance views that start with GO$/O$ also breaks the pattern as the stored view and synonym change the ‘O’s to ‘V’s.

March 16, 2022

Excavating x$

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 11:13 pm GMT Mar 16,2022

If you ever feel the need to dig down into the “dynamic performance views (v$)”, and then go a little deeper to get to the “internal structures (x$)” there are 5 objects in the list of x$ that you will eventually want to know about.

  • x$kqfta – headline information about the x$ structures – name, size, column count.
  • x$kqfvi – a list of the names of the dynamic performance views (mostly pairs of v$/gv$)
  • x$kqfvt – a list of the select statements that define the views in x$kqfvi
  • x$kqfdt – a list of “derived tables”: a cross between synonyms and views of a few of the structures in x$kqfta
  • x$kqfco – a list of the “columns” in each of the tables in x$kqfta (but not x$kqfvi and x$kqfdt)

I’ve listed the tables in this order because that’s the way they’re indexed (0 – 4) in x$kqfta, but your journey is likely to start from the view v$fixed_table, or the view v$fixed_view_definition. From a session connected as SYS to a cdb$root in 19.11.0.0:

SQL> describe v$fixed_view_definition
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                          VARCHAR2(128)
 VIEW_DEFINITION                                    VARCHAR2(4000)
 CON_ID                                             NUMBER

SQL> select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'V$FIXED_VIEW_DEFINITION'
  4  /

VIEW_DEFINITION
--------------------------------------------------------------------------------
select  VIEW_NAME , VIEW_DEFINITION, CON_ID from GV$FIXED_VIEW_DEFINITION where
inst_id = USERENV('Instance')


1 row selected.

SQL> select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'GV$FIXED_VIEW_DEFINITION'
  4  /

VIEW_DEFINITION
--------------------------------------------------------------------------------
select i.inst_id,kqfvinam,kqftpsel, i.con_id   from x$kqfvi i, x$kqfvt t where i
.indx = t.indx

There’s a fairly common pattern in the fixed view definitions – they often come in pairs, one starting GV$ the other starting V$, and the V$ is usually just “select most of the columns from the gv$ for this instance”. The GV$ views are the “Global” views relevant to RAC, the V$ views are local to the current instance. (Just pretend you haven’t noticed that Oracle uses the deprecated userenv() function to find the instance id rather than the sys_context(‘userenv’,{variable}) function.)

If you are running RAC and query one of the GV$ views you’ll find that every other instance starts a Parallel Execution process to create it’s local results for the calling instance. This PX process doesn’t come from (and will ignore the limit on) the normal pool of PX processes and will have a name like PZnn rather than the traditional Pnnn.

You’ll note in the result from the last query that gv$fixed_view_definition is a simple join of x$kqfvi and x$kqfvt on the indx column. There is a problem with this view – hidden in plain sight – but I’ll have to come back to that later.

We can go through a similar set of queries to find the definition of v$fixed_table:

SQL> select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'V$FIXED_TABLE'
  4  /

VIEW_DEFINITION
--------------------------------------------------------------------------------
select  NAME , OBJECT_ID , TYPE , TABLE_NUM, CON_ID from GV$FIXED_TABLE where in
st_id = USERENV('Instance')


1 row selected.

SQL> select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'GV$FIXED_TABLE'
  4  /

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,kqftanam, kqftaobj, 'TABLE', indx, con_id from x$kqfta union all
select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537, con_id from x$kqfvi union all
select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537, con_id from x$kqfdt


1 row selected.

So the “fixed tables” are really the combination of the basic structures, views on the structures, and the “derived tables”. The objects in the UNION ALL each have a unique object_id in a range close to power(2,32) and (for the tables in x$kqfta) if you make a call to dbms_stats.gather_table_stats() that object_id will be used to store the statistics in the data dictionary.

So what are the “derived tables”. There are two ways to address this question – query it, or find a place where one is used. Here’s a full listing of x$kqfdt (still 19.11.0.0:

SQL> select * from x$kqfdt;

ADDR                   INDX    INST_ID     CON_ID   KQFDTOBJ KQFDTNAM                         KQFDTEQU
---------------- ---------- ---------- ---------- ---------- -------------------------------- ---------------------------
000000001454E6C0          0          1          0 4294952735 X$KSLLTR_CHILDREN                X$KSLLTR
000000001454E6E8          1          1          0 4294952736 X$KSLLTR_PARENT                  X$KSLLTR
000000001454E710          2          1          0 4294956013 X$KSLLTR_OSP                     X$KSLLTR
000000001454E738          3          1          0 4294956014 X$KSLWSC_OSP                     X$KSLWSC
000000001454E760          4          1          0 4294951073 X$KCVFHONL                       X$KCVFH
000000001454E788          5          1          0 4294951074 X$KCVFHMRR                       X$KCVFH
000000001454E7B0          6          1          0 4294951440 X$KCVFHALL                       X$KCVFH
000000001454E7D8          7          1          0 4294951056 X$KGLTABLE                       X$KGLOB
000000001454E800          8          1          0 4294951057 X$KGLBODY                        X$KGLOB
000000001454E828          9          1          0 4294951058 X$KGLTRIGGER                     X$KGLOB
000000001454E850         10          1          0 4294951059 X$KGLINDEX                       X$KGLOB
000000001454E878         11          1          0 4294951060 X$KGLCLUSTER                     X$KGLOB
000000001454E8A0         12          1          0 4294951061 X$KGLCURSOR                      X$KGLOB
000000001454E8C8         13          1          0 4294952684 X$KGLCURSOR_CHILD_SQLID          X$KGLOB
000000001454E8F0         14          1          0 4294952680 X$KGLCURSOR_CHILD_SQLIDPH        X$KGLOB
000000001454E918         15          1          0 4294952683 X$KGLCURSOR_CHILD                X$KGLOB
000000001454E940         16          1          0 4294953372 X$KGLCURSOR_PARENT               X$KGLOB
000000001454E968         17          1          0 4294953759 X$KGLSQLTXL                      X$KGLOB
000000001454E990         18          1          0 4294956135 X$ALL_KQLFXPL                    X$KQLFXPL
000000001454E9B8         19          1          0 4294953124 X$KKSSQLSTAT_PLAN_HASH           X$KKSSQLSTAT
000000001454E9E0         20          1          0 4294953231 X$ZASAXTD1                       X$ZASAXTAB
000000001454EA08         21          1          0 4294953232 X$ZASAXTD2                       X$ZASAXTAB
000000001454EA30         22          1          0 4294953233 X$ZASAXTD3                       X$ZASAXTAB
000000001454EA58         23          1          0 4294951597 X$JOXFS                          X$JOXFT
000000001454EA80         24          1          0 4294951598 X$JOXFC                          X$JOXFT
000000001454EAA8         25          1          0 4294951599 X$JOXFR                          X$JOXFT
000000001454EAD0         26          1          0 4294951621 X$JOXFD                          X$JOXFT
000000001454EAF8         27          1          0 4294952364 X$JOXOBJ                         X$JOXFT
000000001454EB20         28          1          0 4294952365 X$JOXSCD                         X$JOXFT
000000001454EB48         29          1          0 4294952366 X$JOXRSV                         X$JOXFT
000000001454EB70         30          1          0 4294952367 X$JOXREF                         X$JOXFT
000000001454EB98         31          1          0 4294952368 X$JOXDRC                         X$JOXFT
000000001454EBC0         32          1          0 4294952369 X$JOXDRR                         X$JOXFT
000000001454EBE8         33          1          0 4294952370 X$JOXMOB                         X$JOXFM
000000001454EC10         34          1          0 4294952371 X$JOXMIF                         X$JOXFM
000000001454EC38         35          1          0 4294952372 X$JOXMIC                         X$JOXFM
000000001454EC60         36          1          0 4294952373 X$JOXMFD                         X$JOXFM
000000001454EC88         37          1          0 4294952374 X$JOXMMD                         X$JOXFM
000000001454ECB0         38          1          0 4294952375 X$JOXMAG                         X$JOXFM
000000001454ECD8         39          1          0 4294952376 X$JOXMEX                         X$JOXFM
000000001454ED00         40          1          0 4294956138 X$ALL_ASH                        X$ASH
000000001454ED28         41          1          0 4294956123 X$ALL_KESWXMON                   X$KESWXMON
000000001454ED50         42          1          0 4294956126 X$ALL_KESWXMON_PLAN              X$KESWXMON_PLAN

43 rows selected.

What it comes down to is that there are several different ways of addressing some of the base objects. x$kglob is the nicest example of this with 11 “equivalences”. I don’t know why Oracle has taken this approach – but it may reflect the fact that some of the underlying structures don’t look anything like “rows” and different derived tables expose different components of the same underlying in mutually incomaptible ways. Here’s an example showing one of these derived tables making an appearance:

SQL> explain plan for
  2  select  child_number, plan_hash_value, sql_text 
  3  from V$sql where sql_id = '7m239n32gftgh';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1346707802

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

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

   1 - filter("KGLOBT03"='7m239n32gftgh' AND "INST_ID"=USERENV('INSTANCE'))

13 rows selected.

When you try to find x$kglcursor_child in the fixed tables (x$kqfta) it’s not there – but a check against x$kqfdt tells us that it’s just another way of addressing x$kglob.

This plan also shows an interesting feature in the table access Operation, which is reported as “Fixed table fixed index” with “ind:2” appended when we check the Name column of the plan.

In this context “index” has a non-standard meaning – x$kglob is a memory structure helping to describe the contents of the Library Cache, so we shouldn’t expect to see anything that looks like a traditional index. In this context this just means that Oracle has a particularly efficient access path if your predicate is of the right type. We could guess in this case that it’s the predicate “kglobt03 =’7m239n32gftgh'” that Oracle is using, but it would be nice to check, and nice to know what other “indexed” access paths there are into the structure (this is, after all, “ind:2”, so there ought to be an “ind:1”). Conveniently Oracle has a dynamic performance view called v$index_fixed_columns that we can query:

SQL> select * from v$indexed_fixed_column where table_name = 'X$KGLOB';

TABLE_NAME                INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION     CON_ID
------------------------- ------------ -------------------- --------------- ----------
X$KGLOB                              1 KGLNAHSH                           0          0
X$KGLOB                              2 KGLOBT03                           0          0

2 rows selected.

Note that the Index_Number column tells use that x$kglobt03 is defined as the 2nd index on the table – which is consistent with the “ind:2” in the plan. Of course, it’s not immediately obvious, and can take a bit of effort to work out, that kgnahsh corresponds to v$sql.hash_value and kglobt03 corresponds to v$sql.sql_id; but it’s nice to know that we can find all the efficent access paths if we need to tune a bit of “expert” SQL that we’ve discovered on the internet.

Even more exciting – we can go back to v$fixed_view_definition and find out what structure(s) are sitting underneath v$indexed_fixed_column:

SQL> select view_definition
  2  from v$fixed_view_definition
  3  where view_name = 'GV$INDEXED_FIXED_COLUMN'
  4  /

VIEW_DEFINITION
--------------------------------------------------------------------------------
select c.inst_id,kqftanam, kqfcoidx, kqfconam, kqfcoipo, c.con_id  from x$kqfco
c, x$kqfta t where t.indx = c.kqfcotab and kqfcoidx != 0

It’s a join between x$kqfta and x$kqfco – the fifth item on our list: the complete list of x$ columns. And you just can’t help wondering what results you would get if you removed the filter on column kqfcoidx. So here’s a little script I wrote a long time ago to do just that:

rem
rem     Script:         x_desc.sql
rem     Author:         Jonathan Lewis
rem     Dated:          September 2002
rem     Purpose:        Describe the X$ objects
rem
rem     Notes:
rem     This script can only be run by SYS
rem

set newpage 0
set pagesize 60
set linesize 132
set trimspoon on
set tab off

break on kqftanam skip page
column kqftanam new_value m_table noprint

column  kqfconam        heading "Column" format a34
column  kqfcodty        heading "Ext Type" 
column  kqfcotyp        heading "Int Type"
column  kqfcomax        heading "Array Max"
column  kqfcolsz        heading "Len Size"
column  kqfcolof        heading "Len Offset"
column  kqfcosiz        heading "Col Size"
column  kqfcooff        heading "Offset"
column  kqfcoidx        heading "Index"
column  kqfcoipo        heading "Idx Col"

ttitle -
        skip 1 -
        center m_table -
        skip 2

spool x_desc

select
        ta.kqftanam,
        co.kqfconam,
        co.kqfcodty,
        co.kqfcotyp,
        co.kqfcomax,
        co.kqfcolsz,
        co.kqfcolof,
        co.kqfcosiz,
        co.kqfcooff,
        co.kqfcoidx,
        co.kqfcoipo
from
        x$kqfta ta,
        x$kqfco co
where
        co.kqfcotab = ta.indx
order by
        ta.kqftanam,
        co.kqfcooff,
        co.indx
;

spool off

And here’s the result for just one “table” in 19.11.0.0 – x$ksled (corresponding to v$event_name) together with a query to report its column count and row size from x$kqfta.

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
KSLEDNAM                            1          6          0          0          0         64          0          0          0
KSLEDCLASSID                        2          7          0          0          0          4          0          0          0
KSLEDCLASS                          1          7          0          0          0         64          0          0          0
KSLEDP1                             1          6          0          0          0         64          8          0          0
KSLEDP2                             1          6          0          0          0         64         16          0          0
KSLEDP3                             1          6          0          0          0         64         24          0          0
KSLEDFLG                            2         11          0          0          0          4         32          0          0
KSLEDCLASS#                         2          0          0          0          0          2         36          0          0
KSLEDDSP                            1          6          0          0          0         64         40          0          0
KSLEDHASH                           2         11          0          0          0          4         52          0          0

SQL> select kqftarsz, kqftacoc from X$kqfta where kqftanam = 'X$KSLED';

  KQFTARSZ   KQFTACOC
---------- ----------
        56         14

In this particular case you can see that the column count (14) matches – I haven’t checked if this is always true, but I suspect it is – and in this case we can see from a quick check of the highest offset (52) plus its column size (4) that the row size (56) matches as well, but that’s not always the case as we shall see in other examples.

There are, inevitably, some puzzles in this output.

  • How can we have 7 columns all starting at the same zero offset?
  • How can you have a column with an offset of 16 (ksledp2) when the previous column has an offset of 8 and a length of 64?
  • Might there be an interesting 2 bytes of information between ksledclass# (offset 36 length 2) and ksleddsp (offset 40)?

I won’t attempt to unravel the mystery any further at this point, but I will just point out that there are two “indexes” on this “table”. To access a row efficiently you can either query by addr (its 64-bit address) or by indx (which suggests it’s stored in memory as a simple non-segmented, array – which is another detail I’ll explain at a later date).

Coming soon

  • The problem with v$fixed_view_definition – and how to circumvent it: now published
  • Three ways of checking row sizes – which may give different results: now published
  • Notes on interpreting the columns in x$kqfco.

I will leave you with one last thought – the X$ “tables” fall into 4 different categories and you can identify which category that is by checking the address (addr) of the first row of each table (provided the table has some data in it, of course). And here’s a silly little detail – I think every x$ table starts at indx = 1, except x$ksupr which starts at indx = 0.

March 11, 2022

Session count

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 12:38 pm GMT Mar 11,2022

This note is an extended answer to a question the Kyle Hailey raised on the oracle-l list server a couple of days ago. Rather than repeat the question immediately, I’ll start with a little scripts (to be run by a suitably privileged account – which eventually will have to be SYS) and then telll you the question.

rem
rem     Script:         xksuse.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2022
rem     Purpose:        
rem
rem     Last tested 
rem             21.3.0.0
rem             19.11.0.0
rem
rem     Notes:
rem     Has to be run by sys in the cdb$root
rem
rem     See also:
rem     https://www.freelists.org/post/oracle-l/sysmetric-Session-Count-Count-from-vsession
rem

select value from v$sysmetric where metric_name = 'Session Count';

select current_utilization from v$resource_limit where resource_name = 'sessions';

select count(*) from v$session;

Here’s a set of results taken from a test instance of 19.11.0.0

     VALUE
----------
	78

CURRENT_UTILIZATION
-------------------
		 78

  COUNT(*)
----------
	54

The question on oracle-l was: “why doesn’t v$sysmetric agree with the count of v$session?”

This raises an internesting question about licencing. I don’t really pay much attention to licensing since I don’t run a production Oracle database any more, but I have a vague memory that for some versions there were things like “named user” licenses and “concurrent sessions” licenses – and there are a couple of parameters you can set to avoid the risk of breaching the license agreement:

Parameter                       Description   
-------------------------------------------------------------------------------------------------
license_max_sessions            maximum number of non-system user sessions allowed
license_max_users               maximum number of named users that can be created in the database

Do these license agreements still exist? Has anyone signed up for them?

I haven’t set either of these parameters in my database so they’ve defaulted to show zero, but by running up several extra sessions and checking the v$sysmetic entry “Session Limit %” I could infer that the default value for license_max_sessions was 1,000 (for my instance on my machine, possibly affected by other parameter settings), and that the limit percentage was based on the v$sysmetric value not a count from v$session. (And if it had been based on the v$session count that would have been almost as bad since the description of the parameter is “non-system user sessions” – and of the 54 sessions reported above only 2 of them were of type “USER”.)

The Geek Bit

Before Kyle mentioned the license issue I was only interested in finding out why there was a difference in the counts. The starting point for this was to look at the x$ structure underlying v$session – and the critical one is x$ksuse although for some time the view includes a join to two other x$ structures: x$kslwt (v$session_wait) and x$ksled (v$event_name).

If you want to see the definition of v$session (or, rather, gv$session) you will run into a problem if you query v$fixed_view_definition because the definition of v$session is longer than the varchar2(4000) limit.

A workaround to this problem is to extract the full definition from the oracle executable which, for Unix systems you can do with a command like:

strings -a oracle | grep -inT "x\$ksuse" | fold >temp.txt

You’ll have to search the (fairly small) text file to find the right definition, of course as there are several dynamic performance views that include x$ksuse or x$ things with names that start the same way. When you”ve identified the correct definition you’ll note that there are two predicates applied to the structure to generate v$session (and these have not changed since at least 8.1.7.4 – which is the oldest listing of the views I have stored):

where
        bitand(s.ksspaflg,1)!=0 
and     bitand(s.ksuseflg,1)!=0

Based on these predicates I wrote a simple script to report sessions (x$ entries) that matched each of the predicates separately:

select 'U' flag, count(*) from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, count(*) from x$ksuse where bitand(ksspaflg,1)!=0
/

F   COUNT(*)
- ----------
U         61
S         78

The 78 S types matches (maybe coincidentally) the v$resource_limit and v$sysmetric figures, but there’s an interesting excess built into the U types when compared to the count of v$session. So the next step is to drill into the detail a bit.

To drill down I wrote a query to list the program column (ksusepnm) with a count of how many rows there were for that program name in the union all. For many program names we could expect to see the answer 2, one row of type U and one of type S, but there are likely to be many end user sessions showing showing some format of connection mechanism such as ((TNS V1-V3). Here’s the script with a predicate showing only the “interesting” rows (i.e. the ones where the result is not 2):

set null n/a

column ksusepnm format a40

select ksusepnm, count(*) from (
select 'U' flag, ksusepnm from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, ksusepnm from x$ksuse where bitand(ksspaflg,1)!=0
)
group by ksusepnm
having count(*) != 2
order by count(*)
/

KSUSEPNM                                   COUNT(*)
---------------------------------------- ----------
oracle@linux183.localdomain (P001)                1
oracle@linux183.localdomain (P002)                1
oracle@linux183.localdomain (J00B)                1
oracle@linux183.localdomain (P000)                1
oracle@linux183.localdomain (MZ00)                1
oracle@linux183.localdomain (MZ01)                1
oracle@linux183.localdomain (P003)                1
oracle@linux183.localdomain (SCMN)                4
sqlplus@linux183.localdomain (TNS V1-V3)          4
n/a                                              24

10 rows selected.

We can ignore the sqlplus rows, they correspond to the two sessions (reported twice each) that are visible in v$session. Similarly we can ignore the SCMN rows, of which there are two in my v$session at present, but we have 24 rows with no program, and 7 rows that appear only in one of the two classes.

If we take the 7 rows where the count is only 1 we note that they are all named for “slave” processes: Jnnn are job queue slaves, Pnnn are parallel execution slaves, and MZnn are manageability monitor (MMON) slaves which, the operating system tells me, don’t currently exist.

The interesting thing is the set of 24 rows that have no associated program; and to look at those I’m just going list the whole dataset rather than counting it and (since the volume is small in my case) examine the results by eye.

select 'U' flag, ksusepnm from x$ksuse where bitand(ksuseflg,1)!=0
union all
select 'S' flag, ksusepnm from x$ksuse where bitand(ksspaflg,1)!=0
order by 1,2
/

I won’t reproduce the results but the key detail was that all the rows where the program name was null were from the S class (and that’s not too surprising, really).

The next (and final, so far) check was to add a few columns to the simple query and change the sort order to see if anything stood out:

break on flag 
compute count of ksspatyp on flag

select 
        'U' flag, indx, ksuseser, ksspatyp, ksusepnm, bitand(ksspaflg,1) spaflg, bitand(ksuseflg,1) useflag, con_id, ksusepro 
from    x$ksuse 
where   bitand(ksuseflg,1)!=0
union all
select 
        'S' flag, indx, ksuseser, ksspatyp, ksusepnm, bitand(ksspaflg,1) spaflg, bitand(ksuseflg,1) useflag, con_id, ksusepro 
from    x$ksuse 
where   bitand(ksspaflg,1)!=0
order by 
        flag, ksusepro, ksusepnm
/

The break/compute commands allow me to check that the totals for the S class and U class still matched with the original 78 and 61 – dynamic structures can change very quickly, and some of the oddities were about background processes. The interesting rows in the U class were as follows:

F       INDX   KSUSESER   KSSPATYP KSUSEPNM                                     SPAFLG    USEFLAG     CON_ID KSUSEPRO
- ---------- ---------- ---------- ---------------------------------------- ---------- ---------- ---------- ----------------
U        533          0          0 oracle@linux183.localdomain (J00B)                0          1          1 00
         546          0          0 oracle@linux183.localdomain (MZ00)                0          1          0 00
         539          0          0 oracle@linux183.localdomain (MZ01)                0          1          0 00
         537          0          0 oracle@linux183.localdomain (P000)                0          1          0 00
         525          0          0 oracle@linux183.localdomain (P001)                0          1          0 00
         544          0          0 oracle@linux183.localdomain (P002)                0          1          0 00
          49          0          0 oracle@linux183.localdomain (P003)                0          1          0 00

Basically this looks like a set of processes which used to be “user” processes, which have terminated and shutdown, but their entries in x$ksuse have not been completely “cleaned” out. The numbers are nice because we have 61 rows in this class, 7 rows show “no serial#, no process” and v$session is reporting 54 sessions which is the result of 61 minus 7.

So what about the S class rows. I’ve cunningly sorted by ksusepro which, if you hadn’t guessed, is the paddr for the process supporting the session. So do we have any cases where multiple sessions seem to be supported by a single process – and this is where I viewed the output and noticed I could show the interesting detail with a simple call to grep plus a little cosmetic editing:

grep -nT -B+1 "                                " xksuse.lst >temp.txt

                 INDX   KSUSESER   KSSPATYP KSUSEPNM                                     SPAFLG    USEFLAG     CON_ID KSUSEPRO
                 ----   --------   -------- -------------------------------------------- ------    -------     ------ ----------------
 270    -           3      62786          1 oracle@linux183.localdomain (GEN0)                1          1          0 000000008E567480
 271    :          26       4581          1                                                   1          0          0 000000008E567480
 274    -         504       2420          1 oracle@linux183.localdomain (GEN1)                1          1          0 000000008E56B4D0
 275    :         519      53839          1                                                   1          0          0 000000008E56B4D0
 278    -           6      44715          1 oracle@linux183.localdomain (OFSD)                1          1          0 000000008E56F520
 279    :          27      60151          1                                                   1          0          0 000000008E56F520
 281    -           4      65197          1 oracle@linux183.localdomain (DBRM)                1          1          0 000000008E572000
 282    :          25      41180          1                                                   1          0          0 000000008E572000
 294    -          13      25844          1 oracle@linux183.localdomain (W000)                1          1          0 000000008E582140
 295    :          14      42325          1                                                   1          0          0 000000008E582140
 296    -         513      33860          1 oracle@linux183.localdomain (LREG)                1          1          0 000000008E5836B0
 297    :         520      56123          1                                                   1          0          0 000000008E5836B0
 298    -          28       5509          1 oracle@linux183.localdomain (W005)                1          1          0 000000008E584C20
 299    :          29      49967          1                                                   1          0          0 000000008E584C20
 301    -         515      19909          1 oracle@linux183.localdomain (MMON)                1          1          0 000000008E588C70
 302    :         516       3981          1                                                   1          0          0 000000008E588C70
 303    -          17       5100          1 oracle@linux183.localdomain (MMNL)                1          1          0 000000008E58A1E0
 304    :          32      46728          1                                                   1          0          0 000000008E58A1E0
 306    -          37      30525          1 oracle@linux183.localdomain (M000)                1          1          0 000000008E58F7A0
 307    :          31      18290          1                                                   1          0          0 000000008E58F7A0
 308    -         523       2817          1 oracle@linux183.localdomain (M002)                1          1          0 000000008E590D10
 309    :         524      58038          1                                                   1          0          0 000000008E590D10
 310    -          19      30164          1 oracle@linux183.localdomain (M001)                1          1          0 000000008E592280
 311    :          35      25427          1                                                   1          0          0 000000008E592280
 315    -         518      20043          1 oracle@linux183.localdomain (AQPC)                1          1          0 000000008E598DB0
 316    :         530      24619          1                                                   1          0          0 000000008E598DB0
 317    -          15      27947          1 oracle@linux183.localdomain (M004)                1          1          0 000000008E59A320
 318    :          45      37304          1                                                   1          0          0 000000008E59A320
 319    -         521      58123          1 oracle@linux183.localdomain (CJQ0)                1          1          0 000000008E59E370
 320    :         522      32179          1                                                   1          0          0 000000008E59E370
 321    -          23      17837          1 oracle@linux183.localdomain (W003)                1          1          0 000000008E59F8E0
 322    :          30      24152          1                                                   1          0          0 000000008E59F8E0
 326    -         529      54638          1 oracle@linux183.localdomain (M003)                1          1          0 000000008E5A0E50
 327    :         526       9248          1                                                   1          0          0 000000008E5A0E50
 328    -          16      23626          1 oracle@linux183.localdomain (W001)                1          1          0 000000008E5A23C0
 329    :          20      52434          1                                                   1          0          0 000000008E5A23C0
 332    -         531      31198          1 oracle@linux183.localdomain (QM02)                1          1          0 000000008E5A6410
 333    :         532      53916          1                                                   1          0          0 000000008E5A6410
 334    -          38      28119          1 oracle@linux183.localdomain (W006)                1          1          0 000000008E5A7980
 335    :          39      55963          1                                                   1          0          0 000000008E5A7980
 336    -          42      46149          1 oracle@linux183.localdomain (W007)                1          1          0 000000008E5AA460
 337    :          43      45287          1                                                   1          0          0 000000008E5AA460
 341    -          47      36878          1 oracle@linux183.localdomain (W002)                1          1          0 000000008E5BD080
 342    :          48      26536          1                                                   1          0          0 000000008E5BD080
 343    -         538      50713          1 oracle@linux183.localdomain (Q002)                1          1          0 000000008E5BE5F0
 344    :         517      40400          1                                                   1          0          0 000000008E5BE5F0
 345    -          34      27163          1 oracle@linux183.localdomain (W004)                1          1          0 000000008E5C5120
 346    :          44      19000          1                                                   1          0          0 000000008E5C5120

Every single one of the S class with a blank program name is “sharing” a process with one of the background processes. In many of the pairs the row with a named program is a slave process (Mnnn, Wnnn etc.) but that’s far from a uniform pattern. I could imagine an argument that says Oracle wants to “pre-allocate” a spare session linked to an existing process in some special cases so that a program can switch to the alternate session (memory structures) if the first session gets corrupted in some way – but I can’t really see that as relevant to any slave processes, while I can think of a couple more programs where this strategy would be more important.

Adding a little weight to the “shadow” idea (or maybe just highlighting a bug) it’s interesting to note that a number of the pairs show consecutive values for the indx (SID) – as if Oracle has created two sessions at the same time.

All I can say at the moment, though, is that we have a consistent pattern for all the excess “invisible” sessions, and the strange entries make up the difference between the v$session count and the v$sysmetric report.

Summing up:

v$session shows a count of 54 but digging into x$ksuse for “user sessions” I can see a further 7 sessions; but the pattern for these sessions is simply “memory structure doesn’t need to be cleaned out after process terminated” so they have nothing to do with any counting anomaly.

v$resource_limit and v$sysmetric report a value that is larger than the v$session count by 24; and we can find 24 rows in x$ksuse which (a) “mirror” 24 background sessions and (b) are linked to processes (v$process.addr) but don’t have a program name (v$session.program) and aren’t flagged as user processes. So it looks as if we can (partially) explain why the two views are over-reporting.

There are some indications that if you are licensing by sessions (if that’s still possible) then the number used for checking the limit is not consistent with the description of the relevant parameter. A large number of background sessions is included in the v$sysmetric figure that seems to be the reference record.

Footnote:

I have a little oddity to pursue in 21.3.0.0, where my v$sysmetric.value was one less than the v$resource_limit current_utilization. The same “shadow” process strategy was visible though, and v$session count plus the “shadow” count agreed with v$resource_limit.

March 7, 2022

SYSAUX Occupants

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 11:32 am GMT Mar 7,2022

This is a draft note that’s been hanging around a very long time for a final edit and print. I was prompted to rediscover it today when I noticed a question on the Oracle Developers’ forum asking about reducing the size of a SYSAUX tablespace that currently included 400GB of free space.

Obviously the usual problem of used extents at the top end of a file can apply even for SYSAUX, and it’s highly likely that it will be necessary to move a few objects around (possibly within the tablespace, possibly out of the tablespace and back) but it’s easy to forget that some of the objects in SYSAUX are sufficiently important that they should only be moved using a procedure that has been supplied by Oracle Corp.

The list of contents of SYSAUX and, where they exist, the relevant procedure to move them are listed in view v$sysaux_occupants, so it’s always worth a quick check of the view before you do anything else – hence this little script:

rem
rem     Script:         sysaux_list.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2012
rem 


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

spool sysaux_list.lst

-- execute print_table('select * from v$sysaux_occupants order by space_usage_kbytes')

column  schema_name             format a32
column  occupant_name           format a32
column  move_procedure          format a45
column  space_usage_kbytes      format 999,999,999

select 
        schema_name, occupant_name, move_procedure, space_usage_kbytes 
from 
        v$sysaux_occupants
order by 
        space_usage_kbytes
;

spool off

I don’t think there’s anything in the script that needs comment, apart from the optional line I’ve put in that uses Tom Kyte’s print_table() procedure to output rows in a tabular layout that produces a convenient full dump of the view. It’s a standard bit of defensive programming that helps me (sometimes) to avoid missing changes to view contents as versions are upgrade.

Here’s a small extract from the two sections of the output from a little VM sandbox of 19.11:

OCCUPANT_NAME                  : LOGMNR
OCCUPANT_DESC                  : LogMiner
SCHEMA_NAME                    : SYSTEM
MOVE_PROCEDURE                 : SYS.DBMS_LOGMNR_D.SET_TABLESPACE
MOVE_PROCEDURE_DESC            : Move Procedure for LogMiner
SPACE_USAGE_KBYTES             : 0
CON_ID                         : 3

-----------------

...

OCCUPANT_NAME                  : SM/OTHER
OCCUPANT_DESC                  : Server Manageability - Other Components
SCHEMA_NAME                    : SYS
MOVE_PROCEDURE                 :
MOVE_PROCEDURE_DESC            : *** MOVE PROCEDURE NOT APPLICABLE ***
SPACE_USAGE_KBYTES             : 72896
CON_ID                         : 3

-----------------

OCCUPANT_NAME                  : SDO
OCCUPANT_DESC                  : Oracle Spatial
SCHEMA_NAME                    : MDSYS
MOVE_PROCEDURE                 : MDSYS.MOVE_SDO
MOVE_PROCEDURE_DESC            : Move Procedure for Oracle Spatial
SPACE_USAGE_KBYTES             : 199552
CON_ID                         : 3

-----------------

32 row(s) selected



SCHEMA_NAME                      OCCUPANT_NAME                    MOVE_PROCEDURE                                SPACE_USAGE_KBYTES
-------------------------------- -------------------------------- --------------------------------------------- ------------------
SYSTEM                           LOGMNR                           SYS.DBMS_LOGMNR_D.SET_TABLESPACE                               0
...
AUDSYS                           AUDSYS                           DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION                  39,488
SYS                              AO                               DBMS_AW.MOVE_AWMETA                                       45,696
XDB                              XDB                              XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE                     62,144
SYS                              SM/OPTSTAT                                                                                 68,288
SYS                              SM/OTHER                                                                                   72,896
MDSYS                            SDO                              MDSYS.MOVE_SDO                                           199,552

32 rows selected.

Addendum

It’s a long time since I had to help someone clean load of garbage from sysaux – typically from excess space usage by the audit table, the histogram history table, or the partition synopsis table – so I thought I’d take a quick look around the internet and MOS for any notes that might be good starting points for doing the job in a recent version of Oracle. The biggest unknown for me was the need to mess around inside a PDB, so this addendum is largely a pointer to get me started if I do need to do something with a PDB in the future.

Searching MOS for “v$sysaux_occupants” I noted a number of documents could offer helpful comments, in particular I picked on “Doc ID 1499542.1: Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace.” which made reference to the dbms_pdb package and the need to use dbms_pdb.exec_as_oracle_script() to move objects in a PDB’s sysaux tablespace, and the slightly more generic Doc ID 2798505.1 How To Move Sys Table in PDB that made a similar point and highlighted Oracle error ORA-65040: operation not allowed from within a pluggable database.

Following this detail with a further search on google I then found a note on Marco Mischke’s blog with the title: “Shrink SYSAUX of a Pluggable Database”. The note is 6 years old, so shouldn’t be followed thoughtlessly – things change with time – but it’s a good starting point.

v$sql_hint

Filed under: Hints,Oracle — Jonathan Lewis @ 9:50 am GMT Mar 7,2022

Here’s a quick and dirty script I thought I’d published a very long time ago. It has to be run as SYS and queries v$sql_hint to produce a list of all the things which (at least in theory) could be used as hints somewhere in the code.

I’ve ordered the list by the columns version then version_outline. I believe the version is supposed to be the version in which the hint first appeared (even if it wasn’t documented) and the version_outline is the version at which the hint could appear as part of a query’s “Outline Information” (which can then be used to generate a Stored Outline or SQL Plan Baseline – or SQL Patch). You’ll notice that a lot of the hints don’t have a version_outline.

One of the conveniences of this report is the appearance of the inverse column. For many hints there is an “opposite” hint. Hints often come in pairs (especially in recent versions of Oracle) following the pattern XXXX / NO_XXXX though there are many hints that don’t have an inverse and a few that don’t follow the pattern (e.g. use_concat / no_expand).

Perhaps one of the most useful parts of the report comes from the target_level which is a bitmap that can be decoded to show the query level(s) that the hint can apply to. There are 4 levels (corresponding to bits 0 to 3): statement, query block, object and join and that’s a useful piece of information to know when you’re trying to make best use of hints. For example, the cardinality hint is one that is not well-known and most of the people who knew anything about it seemed to assume that it could apply only to a table when in fact it can also be used for a query block (which can be extremely useful with subquery factoring) or a multi-table join (although that option is quite hard to use effectively except in the simplest cases).

rem
rem     Script:         sql_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2007
rem

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

column  name            format a32
column  inverse         format a32
column  sql_feature     format a30
column  version         format a8
column  outline         format a8
column  class           format a35
column  target          format 999
column  prop            format 999

column  bit0            format a6
column  bit1            format a6
column  bit2            format a6
column  bit3            format a6

column  bit4            format a3
column  bit5            format a3
column  bit6            format a3
column  bit7            format a3
column  bit8            format a3

break on version skip 1
compute count of version on version

spool sql_hints

select
        version,
        version_outline         outline,
        name,
        inverse,
        sql_feature,
        class,
        decode(bitand(target_level,1),1,'State')        bit0,
        decode(bitand(target_level,2),2,'QBlock')       bit1,
        decode(bitand(target_level,4),4,'Object')       bit2,
        decode(bitand(target_level,8),8,'Join')         bit3,
        target_level                                    target,
        decode(bitand(property,16),16,'16')     bit4,
        decode(bitand(property,32),32,'32')     bit5,
        decode(bitand(property,64),64,'64')     bit6,
        decode(bitand(property,128),128,'128')  bit7,
        decode(bitand(property,256),256,'256')  bit8,
        property                                prop
from
        v$sql_hint
order by
        to_number(substr(version,1,3)),
        version,
        to_number(substr(version_outline,1,3)) nulls last,
        version_outline nulls last,
        name
;

spool off

Footnote

There’s also a property column, which seems to be another bitmap but (at present) only using bits 4 to 8 of “something”. That points to the possibility that the level and property might be derived from the same couple of bytes but a quick check on x$qksht (the x$ structure underneath gv$sql_hint) shows two different columns (level_qkshtsyrow, props_qkshtsyrow) – with a 4 byte difference in their offsets. (This may be a fake, of course, since the address (addr) of every single “row” in the structure is the same, which means that when you’re looking at the x$ it’s been dynamically constructed from somewhere else.)

I haven’t yet worked out a consistent pattern for the bits in the property column, even though there are a number “coincidences” that look almost convincing, but I keep reporting the property – broken down by bits as well as the actual value – in this report in case one day I spot some clear correlation between the bits and the function of the hints.

If you’re interested in the results and don’t have access to v$sql_hint, here’s the output from 21.3.0.0. Of the 388 hints listed only about 120 are (officially) documented anywhere – there are a lot of hints you’re not supposed to use:

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
8.0.0    8.1.7    CLUSTER                                                           QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         8.1.7    INDEX                            NO_INDEX                         QKSFM_INDEX                    ACCESS                                            Object             4 16  32          256  304
         8.1.7    ROWID                                                             QKSFM_CBO                      ACCESS                                            Object             4 16              256  272
         10.1.0   NO_MERGE                         MERGE                            QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
                  EXPR_CORR_CHECK                                                   QKSFM_CBO                      EXPR_CORR_CHECK                     State                            1                        0
                  MERGE_CONST_ON                                                    QKSFM_CBO                      MERGE_CONST_ON                      State                            1                        0
                  NL_AJ                                                             QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
                  NL_SJ                                                             QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
                  NO_MONITORING                                                     QKSFM_ALL                      NO_MONITORING                       State                            1                        0
                  NO_ORDER_ROLLUPS                                                  QKSFM_TRANSFORMATION           NO_ORDER_ROLLUPS                           QBlock                    2                        0
                  NO_STATS_GSETS                                                    QKSFM_ALL                      NO_STATS_GSETS                             QBlock                    2                        0
                  ORDERED_PREDICATES                                                QKSFM_CBO                      ORDERED_PREDICATES                         QBlock                    2 16                    16
                  QUEUE_CURR                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
                  QUEUE_ROWP                                                        QKSFM_CBO                      ACCESS                                            Object             4                 256  256
--------
      14

8.1.0    8.1.5    BITMAP                                                            QKSFM_CBO                      BITMAP                                     QBlock                    2                 256  256
         8.1.5    NO_PUSH_PRED                     PUSH_PRED                        QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    PUSH_PRED                        NO_PUSH_PRED                     QKSFM_FILTER_PUSH_PRED         PUSH_PRED                                  QBlock Object             6 16                    16
         8.1.5    RULE                                                              QKSFM_RBO                      MODE                                State                            1 16                    16
         8.1.7    AND_EQUAL                                                         QKSFM_AND_EQUAL                ACCESS                                            Object             4 16  32          256  304
         8.1.7    DRIVING_SITE                                                      QKSFM_ALL                      DRIVING_SITE                                      Object             4                 256  256
         8.1.7    FACT                             NO_FACT                          QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    FULL                                                              QKSFM_FULL                     ACCESS                                            Object             4 16              256  272
         8.1.7    HASH                                                              QKSFM_ALL                      ACCESS                                            Object             4 16              256  272
         8.1.7    HASH_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    HASH_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    INDEX_COMBINE                                                     QKSFM_INDEX_COMBINE            ACCESS                                            Object             4 16  32      128 256  432
         8.1.7    INDEX_DESC                       NO_INDEX                         QKSFM_INDEX_DESC               ACCESS                                            Object             4 16  32          256  304
         8.1.7    INDEX_FFS                                                         QKSFM_INDEX_FFS                ACCESS                                            Object             4 16  32          256  304
         8.1.7    MERGE_AJ                                                          QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         8.1.7    MERGE_SJ                                                          QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         8.1.7    NO_EXPAND                        USE_CONCAT                       QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    NO_FACT                          FACT                             QKSFM_STAR_TRANS               FACT                                              Object             4 16              256  272
         8.1.7    ORDERED                                                           QKSFM_CBO                      ORDERED                                    QBlock                    2 16                    16
         8.1.7    SEMIJOIN_DRIVER                                                   QKSFM_CBO                      SEMIJOIN_DRIVER                            QBlock                    2 16                    16
         8.1.7    STAR_TRANSFORMATION              NO_STAR_TRANSFORMATION           QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         8.1.7    SWAP_JOIN_INPUTS                 NO_SWAP_JOIN_INPUTS              QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         8.1.7    USE_CONCAT                       NO_EXPAND                        QKSFM_USE_CONCAT               OR_EXPAND                                  QBlock                    2 16                    16
         8.1.7    USE_HASH                         NO_USE_HASH                      QKSFM_USE_HASH                 JOIN                                              Object             4 16      64  128 256  464
         8.1.7    USE_MERGE                        NO_USE_MERGE                     QKSFM_USE_MERGE                JOIN                                              Object             4 16      64      256  336
         8.1.7    USE_NL                           NO_USE_NL                        QKSFM_USE_NL                   JOIN                                              Object             4 16      64      256  336
         10.1.0   MERGE                            NO_MERGE                         QKSFM_CVM                      MERGE                                      QBlock Object             6 16                    16
         10.2.0.1 ALL_ROWS                                                          QKSFM_ALL_ROWS                 MODE                                State                            1 16                    16
         10.2.0.1 FIRST_ROWS                                                        QKSFM_FIRST_ROWS               MODE                                State                            1 16                    16
         10.2.0.5 PUSH_SUBQ                        NO_PUSH_SUBQ                     QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
                  APPEND                           NOAPPEND                         QKSFM_CBO                      APPEND                              State                            1                        0
                  CACHE                            NOCACHE                          QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  CHOOSE                                                            QKSFM_CHOOSE                   MODE                                State                            1 16                    16
                  DEREF_NO_REWRITE                                                  QKSFM_ALL                      DEREF_NO_REWRITE                    State                            1                        0
                  INDEX_ASC                        NO_INDEX                         QKSFM_INDEX_ASC                ACCESS                                            Object             4 16  32          256  304
                  NESTED_TABLE_GET_REFS                                             QKSFM_ALL                      NESTED_TABLE_GET_REFS               State                            1                        0
                  NOAPPEND                         APPEND                           QKSFM_CBO                      APPEND                              State                            1                        0
                  NOCACHE                          CACHE                            QKSFM_EXECUTION                CACHE                                             Object             4                 256  256
                  NOPARALLEL                       SHARED                           QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  NO_PARALLEL_INDEX                PARALLEL_INDEX                   QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PARALLEL_INDEX                   NO_PARALLEL_INDEX                QKSFM_PQ                       PARALLEL_INDEX                                    Object             4     32          256  288
                  PIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  PIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  REMOTE_MAPPED                                                     QKSFM_ALL                      REMOTE_MAPPED                              QBlock                    2 16              256  272
                  SHARED                           NO_PARALLEL                      QKSFM_PARALLEL                 SHARED                              State         Object             5                 256  256
                  STAR                                                              QKSFM_STAR_TRANS               STAR                                       QBlock                    2 16                    16
                  TIV_GB                                                            QKSFM_ALL                      PIV_GB                                     QBlock                    2                        0
                  TIV_SSF                                                           QKSFM_ALL                      PIV_SSF                                    QBlock                    2                        0
                  USE_ANTI                                                          QKSFM_CBO                      USE_ANTI                                          Object             4 16              256  272
                  USE_SEMI                                                          QKSFM_CBO                      USE_SEMI                                          Object             4 16              256  272
--------
      50

8.1.5    8.1.7    NO_ACCESS                                                         QKSFM_ALL                      NO_ACCESS                                         Object             4                 256  256
         8.1.7    NO_INDEX                         INDEX                            QKSFM_INDEX                    NO_INDEX                                          Object             4 16  32          256  304
         8.1.7    NO_REWRITE                       REWRITE                          QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         8.1.7    PQ_DISTRIBUTE                                                     QKSFM_PQ_DISTRIBUTE            PQ_DISTRIBUTE                                     Object             4 16              256  272
         8.1.7    REWRITE                          NO_REWRITE                       QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 INDEX_JOIN                                                        QKSFM_INDEX_JOIN               ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 DOMAIN_INDEX_NO_SORT             DOMAIN_INDEX_SORT                QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
         10.2.0.1 DOMAIN_INDEX_SORT                DOMAIN_INDEX_NO_SORT             QKSFM_CBO                      DOMAIN_INDEX_SORT                          QBlock                    2                        0
                  BUFFER                           NO_BUFFER                        QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  BYPASS_UJVC                                                       QKSFM_CBO                      BYPASS_UJVC                                QBlock                    2                        0
                  CACHE_CB                         NOCACHE                          QKSFM_CBO                      CACHE_CB                                          Object             4                 256  256
                  CUBE_GB                                                           QKSFM_CBO                      CUBE_GB                                    QBlock                    2                        0
                  NESTED_TABLE_SET_SETID                                            QKSFM_ALL                      NESTED_TABLE_SET_SETID              State                            1                        0
                  NO_BUFFER                        BUFFER                           QKSFM_CBO                      BUFFER                                     QBlock                    2                        0
                  RESTORE_AS_INTERVALS                                              QKSFM_CBO                      RESTORE_AS_INTERVALS                       QBlock                    2                        0
                  SAVE_AS_INTERVALS                                                 QKSFM_CBO                      SAVE_AS_INTERVALS                          QBlock                    2                        0
                  SCN_ASCENDING                                                     QKSFM_ALL                      SCN_ASCENDING                       State                            1                        0
--------
      17

8.1.6    10.1.0   NO_UNNEST                        UNNEST                           QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0   UNNEST                           NO_UNNEST                        QKSFM_UNNEST                   UNNEST                                     QBlock                    2 16                    16
         10.1.0.3 LEADING                                                           QKSFM_JOIN_ORDER               LEADING                                                  Join        8 16              256  272
                  SYS_PARALLEL_TXN                                                  QKSFM_CBO                      SYS_PARALLEL_TXN                           QBlock                    2                        0
--------
       4


VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
9.0.0    10.2.0.1 INDEX_SS                         NO_INDEX_SS                      QKSFM_INDEX_SS                 ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 INDEX_SS_DESC                    NO_INDEX_SS                      QKSFM_INDEX_SS_DESC            ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 PQ_MAP                           PQ_NOMAP                         QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         10.2.0.1 PQ_NOMAP                         PQ_MAP                           QKSFM_PQ_MAP                   PQ_MAP                                            Object             4 16              256  272
         18.1.0   INLINE                           MATERIALIZE                      QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
         18.1.0   MATERIALIZE                      INLINE                           QKSFM_TRANSFORMATION           INLINE                                     QBlock                    2 16                    16
                  ANTIJOIN                                                          QKSFM_TRANSFORMATION           ANTIJOIN                                   QBlock                    2 16                    16
                  BYPASS_RECURSIVE_CHECK                                            QKSFM_ALL                      BYPASS_RECURSIVE_CHECK                     QBlock                    2                        0
                  CARDINALITY                                                       QKSFM_STATS                    CARDINALITY                                QBlock Object Join       14 16              256  272
                  CPU_COSTING                      NO_CPU_COSTING                   QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  CURSOR_SHARING_EXACT                                              QKSFM_CBO                      CURSOR_SHARING_EXACT                       QBlock                    2                        0
                  DML_UPDATE                                                        QKSFM_CBO                      DML_UPDATE                          State                            1                        0
                  GBY_CONC_ROLLUP                                                   QKSFM_TRANSFORMATION           GBY_CONC_ROLLUP                            QBlock                    2                        0
                  HWM_BROKERED                                                      QKSFM_CBO                      HWM_BROKERED                               QBlock                    2                        0
                  INDEX_RRS                                                         QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
                  INDEX_SS_ASC                     NO_INDEX_SS                      QKSFM_INDEX_SS_ASC             ACCESS                                            Object             4 16  32          256  304
                  LOCAL_INDEXES                                                     QKSFM_CBO                      LOCAL_INDEXES                              QBlock                    2                        0
                  MV_MERGE                                                          QKSFM_TRANSFORMATION           MV_MERGE                                   QBlock                    2                        0
                  NO_CPU_COSTING                   CPU_COSTING                      QKSFM_CPU_COSTING              CPU_COSTING                                QBlock                    2 16                    16
                  NO_PRUNE_GSETS                                                    QKSFM_TRANSFORMATION           NO_PRUNE_GSETS                             QBlock                    2                        0
                  NO_SEMIJOIN                      SEMIJOIN                         QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  OVERFLOW_NOMOVE                                                   QKSFM_CBO                      OVERFLOW_NOMOVE                            QBlock                    2                        0
                  SEMIJOIN                         NO_SEMIJOIN                      QKSFM_TRANSFORMATION           SEMIJOIN                                   QBlock                    2 16                    16
                  SKIP_EXT_OPTIMIZER                                                QKSFM_CBO                      SKIP_EXT_OPTIMIZER                         QBlock                    2 16                    16
                  SQLLDR                                                            QKSFM_CBO                      SQLLDR                              State                            1                        0
                  USE_TTT_FOR_GSETS                                                 QKSFM_TRANSFORMATION           USE_TTT_FOR_GSETS                          QBlock                    2                        0
--------
      26

9.2.0    10.1.0   EXPAND_GSET_TO_UNION             NO_EXPAND_GSET_TO_UNION          QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.1.0   NO_EXPAND_GSET_TO_UNION          EXPAND_GSET_TO_UNION             QKSFM_TRANSFORMATION           EXPAND_GSET_TO_UNION                       QBlock                    2                        0
         10.2.0.5 NO_PUSH_SUBQ                     PUSH_SUBQ                        QKSFM_TRANSFORMATION           PUSH_SUBQ                                  QBlock                    2 16                    16
         11.1.0.6 FORCE_XML_QUERY_REWRITE          NO_XML_QUERY_REWRITE             QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
         11.1.0.6 NO_XML_QUERY_REWRITE             FORCE_XML_QUERY_REWRITE          QKSFM_XML_REWRITE              FORCE_XML_QUERY_REWRITE             State                            1                        0
                  DYNAMIC_SAMPLING                                                  QKSFM_DYNAMIC_SAMPLING         DYNAMIC_SAMPLING                           QBlock Object             6 16              256  272
                  DYNAMIC_SAMPLING_EST_CDN                                          QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN                          Object             4 16              256  272
                  IGNORE_WHERE_CLAUSE                                               QKSFM_ALL                      IGNORE_WHERE_CLAUSE                 State                            1                        0
                  NO_QKN_BUFF                                                       QKSFM_CBO                      NO_QKN_BUFF                                QBlock                    2                        0
                  NO_REF_CASCADE                   REF_CASCADE_CURSOR               QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  REF_CASCADE_CURSOR               NO_REF_CASCADE                   QKSFM_CBO                      REF_CASCADE_CURSOR                  State                            1                        0
                  SYS_DL_CURSOR                                                     QKSFM_CBO                      SYS_DL_CURSOR                       State                            1                        0
                  SYS_RID_ORDER                                                     QKSFM_ALL                      SYS_RID_ORDER                              QBlock                    2                        0
--------
      13

10.1.0.3 10.1.0.3 NO_BASETABLE_MULTIMV_REWRITE     REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_INDEX_FFS                     INDEX_FFS                        QKSFM_INDEX_FFS                NO_INDEX_FFS                                      Object             4 16  32          256  304
         10.1.0.3 NO_INDEX_SS                      INDEX_SS                         QKSFM_INDEX_SS                 NO_INDEX_SS                                       Object             4 16  32          256  304
         10.1.0.3 NO_MULTIMV_REWRITE               REWRITE                          QKSFM_ALL                      REWRITE                                    QBlock                    2 16                    16
         10.1.0.3 NO_SET_TO_JOIN                   SET_TO_JOIN                      QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.1.0.3 NO_STAR_TRANSFORMATION           STAR_TRANSFORMATION              QKSFM_STAR_TRANS               STAR_TRANSFORMATION                        QBlock Object             6 16                    16
         10.1.0.3 NO_SWAP_JOIN_INPUTS              SWAP_JOIN_INPUTS                 QKSFM_CBO                      SWAP_JOIN_INPUTS                                  Object             4 16              256  272
         10.1.0.3 NO_USE_HASH                      USE_HASH                         QKSFM_USE_HASH                 NO_USE_HASH                                       Object             4 16      64      256  336
         10.1.0.3 NO_USE_MERGE                     USE_MERGE                        QKSFM_USE_MERGE                NO_USE_MERGE                                      Object             4 16      64      256  336
         10.1.0.3 NO_USE_NL                        USE_NL                           QKSFM_USE_NL                   NO_USE_NL                                         Object             4 16      64      256  336
         10.1.0.3 SET_TO_JOIN                      NO_SET_TO_JOIN                   QKSFM_SET_TO_JOIN              SET_TO_JOIN                                QBlock                    2 16                    16
         10.2.0.1 IGNORE_OPTIM_EMBEDDED_HINTS                                       QKSFM_ALL                      IGNORE_OPTIM_EMBEDDED_HINTS         State                            1                        0
         10.2.0.1 OPTIMIZER_FEATURES_ENABLE                                         QKSFM_ALL                      OPTIMIZER_FEATURES_ENABLE           State                            1 16              256  272
                  COLUMN_STATS                                                      QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  FBTSCAN                                                           QKSFM_CBO                      FBTSCAN                             State                            1                        0
                  GATHER_PLAN_STATISTICS                                            QKSFM_GATHER_PLAN_STATISTICS   GATHER_PLAN_STATISTICS              State                            1                        0
                  INCLUDE_VERSION                                                   QKSFM_ALL                      INCLUDE_VERSION                     State                            1                        0
                  INDEX_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  MODEL_DONTVERIFY_UNIQUENESS                                       QKSFM_TRANSFORMATION           MODEL_DONTVERIFY_UNIQUENESS                QBlock                    2                        0
                  MODEL_MIN_ANALYSIS                                                QKSFM_TRANSFORMATION           MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_NO_ANALYSIS                                                 QKSFM_ALL                      MODEL_MIN_ANALYSIS                         QBlock                    2                        0
                  MODEL_PUSH_REF                   NO_MODEL_PUSH_REF                QKSFM_TRANSFORMATION           MODEL_PUSH_REF                             QBlock                    2                        0
                  NESTED_TABLE_FAST_INSERT                                          QKSFM_ALL                      NESTED_TABLE_FAST_INSERT            State                            1                        0
                  NO_MODEL_PUSH_REF                MODEL_PUSH_REF                   QKSFM_ALL                      MODEL_PUSH_REF                             QBlock                    2                        0
                  NO_PARALLEL                      SHARED                           QKSFM_CBO                      SHARED                              State         Object             5                 256  256
                  NO_PARTIAL_COMMIT                                                 QKSFM_CBO                      NO_PARTIAL_COMMIT                   State                            1                        0
                  NO_QUERY_TRANSFORMATION                                           QKSFM_TRANSFORMATION           NO_QUERY_TRANSFORMATION             State                            1 16                    16
                  OPAQUE_TRANSFORM                                                  QKSFM_TRANSFORMATION           OPAQUE_TRANSFORM                    State                            1                        0
                  OPAQUE_XCANONICAL                                                 QKSFM_TRANSFORMATION           OPAQUE_XCANONICAL                   State                            1                        0
                  OPT_ESTIMATE                                                      QKSFM_OPT_ESTIMATE             OPT_ESTIMATE                               QBlock Object Join       14 16              256  272
                  QB_NAME                                                           QKSFM_ALL                      QB_NAME                                    QBlock                    2                 256  256
                  RESTRICT_ALL_REF_CONS                                             QKSFM_ALL                      RESTRICT_ALL_REF_CONS               State                            1                        0
                  REWRITE_OR_ERROR                                                  QKSFM_TRANSFORMATION           REWRITE                                    QBlock                    2                        0
                  SKIP_UNQ_UNUSABLE_IDX                                             QKSFM_CBO                      SKIP_UNQ_UNUSABLE_IDX               State                            1                        0
                  STREAMS                                                           QKSFM_CBO                      STREAMS                             State                            1                        0
                  TABLE_STATS                                                       QKSFM_STATS                    TABLE_STATS                         State                            1 16              256  272
                  TRACING                                                           QKSFM_EXECUTION                TRACING                             State                            1                        0
                  USE_NL_WITH_INDEX                NO_USE_NL                        QKSFM_USE_NL_WITH_INDEX        USE_NL_WITH_INDEX                                 Object             4 16  32          256  304
                  USE_WEAK_NAME_RESL                                                QKSFM_ALL                      USE_WEAK_NAME_RESL                  State                            1                        0
                  VECTOR_READ                                                       QKSFM_CBO                      VECTOR_READ                         State                            1                        0
                  VECTOR_READ_TRACE                                                 QKSFM_CBO                      VECTOR_READ_TRACE                   State                            1                        0
                  X_DYN_PRUNE                                                       QKSFM_CBO                      X_DYN_PRUNE                                QBlock                    2                        0
--------
      42

10.2.0.1 10.2.0.1 BITMAP_TREE                                                       QKSFM_BITMAP_TREE              ACCESS                                            Object             4 16  32          256  304
         10.2.0.1 ELIMINATE_JOIN                   NO_ELIMINATE_JOIN                QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 ELIMINATE_OBY                    NO_ELIMINATE_OBY                 QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_ELIMINATE_JOIN                ELIMINATE_JOIN                   QKSFM_TABLE_ELIM               ELIMINATE_JOIN                                    Object             4 16                    16
         10.2.0.1 NO_ELIMINATE_OBY                 ELIMINATE_OBY                    QKSFM_OBYE                     ELIMINATE_OBY                              QBlock                    2 16                    16
         10.2.0.1 NO_PULL_PRED                     PULL_PRED                        QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 OLD_PUSH_PRED                                                     QKSFM_OLD_PUSH_PRED            OLD_PUSH_PRED                              QBlock Object             6 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
10.2.0.1 10.2.0.1 OPT_PARAM                                                         QKSFM_ALL                      OPT_PARAM                           State                            1 16              256  272
         10.2.0.1 OUTLINE                                                           QKSFM_ALL                      OUTLINE                                    QBlock                    2                        0
         10.2.0.1 OUTLINE_LEAF                                                      QKSFM_ALL                      OUTLINE_LEAF                               QBlock                    2                        0
         10.2.0.1 PULL_PRED                        NO_PULL_PRED                     QKSFM_PULL_PRED                PULL_PRED                                         Object             4 16                    16
         10.2.0.1 RBO_OUTLINE                                                       QKSFM_RBO                      RBO_OUTLINE                         State                            1                        0
         10.2.0.5 NO_USE_HASH_AGGREGATION          USE_HASH_AGGREGATION             QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         10.2.0.5 USE_HASH_AGGREGATION             NO_USE_HASH_AGGREGATION          QKSFM_ALL                      USE_HASH_AGGREGATION                       QBlock                    2                        0
         11.1.0.6 NO_PX_JOIN_FILTER                PX_JOIN_FILTER                   QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
         11.1.0.6 NO_XML_DML_REWRITE                                                QKSFM_XML_REWRITE              NO_XML_DML_REWRITE                  State                            1                        0
         11.1.0.6 PX_JOIN_FILTER                   NO_PX_JOIN_FILTER                QKSFM_PX_JOIN_FILTER           PX_JOIN_FILTER                                    Object             4 16      64      256  336
                  DBMS_STATS                                                        QKSFM_DBMS_STATS               DBMS_STATS                          State                            1                        0
                  INLINE_XMLTYPE_NT                                                 QKSFM_ALL                      INLINE_XMLTYPE_NT                   State                            1                        0
                  MODEL_COMPILE_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_COMPILE_SUBQUERY                     QBlock                    2                        0
                  MODEL_DYNAMIC_SUBQUERY                                            QKSFM_TRANSFORMATION           MODEL_DYNAMIC_SUBQUERY                     QBlock                    2                        0
                  NO_CARTESIAN                                                      QKSFM_ALL                      NO_CARTESIAN                                      Object             4 16      64      256  336
                  NO_SQL_TUNE                                                       QKSFM_ALL                      NO_SQL_TUNE                         State                            1                        0
                  PRECOMPUTE_SUBQUERY                                               QKSFM_TRANSFORMATION           PRECOMPUTE_SUBQUERY                        QBlock                    2                        0
                  PRESERVE_OID                                                      QKSFM_ALL                      PRESERVE_OID                        State                            1                        0
--------
      25

10.2.0.2 10.2.0.2 CONNECT_BY_COST_BASED            NO_CONNECT_BY_COST_BASED         QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 CONNECT_BY_FILTERING             NO_CONNECT_BY_FILTERING          QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_COST_BASED         CONNECT_BY_COST_BASED            QKSFM_TRANSFORMATION           CONNECT_BY_COST_BASED                      QBlock                    2 16                    16
         10.2.0.2 NO_CONNECT_BY_FILTERING          CONNECT_BY_FILTERING             QKSFM_ALL                      CONNECT_BY_FILTERING                       QBlock                    2 16                    16
--------
       4

10.2.0.3 10.2.0.3 NATIVE_FULL_OUTER_JOIN           NO_NATIVE_FULL_OUTER_JOIN        QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN        NATIVE_FULL_OUTER_JOIN           QKSFM_ALL                      NATIVE_FULL_OUTER_JOIN                     QBlock                    2 16                    16
         10.2.0.3 NUM_INDEX_KEYS                                                    QKSFM_CBO                      ACCESS                                            Object             4 16  32          256  304
--------
       3

10.2.0.4 10.2.0.4 CONNECT_BY_COMBINE_SW            NO_CONNECT_BY_COMBINE_SW         QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
         10.2.0.4 NO_CONNECT_BY_COMBINE_SW         CONNECT_BY_COMBINE_SW            QKSFM_ALL                      CONNECT_BY_COMBINE_SW                      QBlock                    2 16                    16
--------
       2

10.2.0.5 10.2.0.5 CONNECT_BY_CB_WHR_ONLY           NO_CONNECT_BY_CB_WHR_ONLY        QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 GBY_PUSHDOWN                     NO_GBY_PUSHDOWN                  QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
         10.2.0.5 NO_CONNECT_BY_CB_WHR_ONLY        CONNECT_BY_CB_WHR_ONLY           QKSFM_TRANSFORMATION           CONNECT_BY_CB_WHR_ONLY                     QBlock                    2 16                    16
         10.2.0.5 NO_GBY_PUSHDOWN                  GBY_PUSHDOWN                     QKSFM_ALL                      GBY_PUSHDOWN                               QBlock                    2 16                    16
--------
       4

11.1.0.6 11.1.0.6 COST_XML_QUERY_REWRITE           NO_COST_XML_QUERY_REWRITE        QKSFM_COST_XML_QUERY_REWRITE   COST_XML_QUERY_REWRITE              State                            1                        0
         11.1.0.6 DB_VERSION                                                        QKSFM_ALL                      DB_VERSION                          State                            1 16              256  272
         11.1.0.6 DOMAIN_INDEX_FILTER              NO_DOMAIN_INDEX_FILTER           QKSFM_CBO                      DOMAIN_INDEX_FILTER                               Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_ASC                                                      QKSFM_INDEX_RS_ASC             ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 INDEX_RS_DESC                                                     QKSFM_INDEX_RS_DESC            ACCESS                                            Object             4 16  32          256  304
         11.1.0.6 NLJ_BATCHING                     NO_NLJ_BATCHING                  QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NLJ_PREFETCH                     NO_NLJ_PREFETCH                  QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_COST_XML_QUERY_REWRITE        COST_XML_QUERY_REWRITE           QKSFM_COST_XML_QUERY_REWRITE   NO_COST_XML_QUERY_REWRITE           State                            1                        0
         11.1.0.6 NO_DOMAIN_INDEX_FILTER           DOMAIN_INDEX_FILTER              QKSFM_CBO                      NO_DOMAIN_INDEX_FILTER                            Object             4 16  32          256  304
         11.1.0.6 NO_NLJ_BATCHING                  NLJ_BATCHING                     QKSFM_EXECUTION                ACCESS                                            Object             4 16              256  272
         11.1.0.6 NO_NLJ_PREFETCH                  NLJ_PREFETCH                     QKSFM_EXECUTION                NLJ_PREFETCH                                      Object             4 16              256  272
         11.1.0.6 NO_OUTER_JOIN_TO_INNER           OUTER_JOIN_TO_INNER              QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 NO_PLACE_GROUP_BY                PLACE_GROUP_BY                   QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 NO_SUBQUERY_PRUNING              SUBQUERY_PRUNING                 QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 NO_USE_INVISIBLE_INDEXES         USE_INVISIBLE_INDEXES            QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE              XMLINDEX_REWRITE                 QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 NO_XMLINDEX_REWRITE_IN_SELECT    XMLINDEX_REWRITE_IN_SELECT       QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 OUTER_JOIN_TO_INNER              NO_OUTER_JOIN_TO_INNER           QKSFM_OUTER_JOIN_TO_INNER      OUTER_JOIN_TO_INNER                        QBlock Object             6 16                    16
         11.1.0.6 PLACE_GROUP_BY                   NO_PLACE_GROUP_BY                QKSFM_PLACE_GROUP_BY           PLACE_GROUP_BY                             QBlock                    2 16                    16
         11.1.0.6 SUBQUERY_PRUNING                 NO_SUBQUERY_PRUNING              QKSFM_CBO                      SUBQUERY_PRUNING                                  Object             4 16              256  272
         11.1.0.6 USE_INVISIBLE_INDEXES            NO_USE_INVISIBLE_INDEXES         QKSFM_INDEX                    USE_INVISIBLE_INDEXES               State                            1                        0
         11.1.0.6 USE_MERGE_CARTESIAN                                               QKSFM_USE_MERGE_CARTESIAN      JOIN                                              Object             4 16      64      256  336
         11.1.0.6 XMLINDEX_REWRITE                 NO_XMLINDEX_REWRITE              QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XMLINDEX_REWRITE_IN_SELECT       NO_XMLINDEX_REWRITE_IN_SELECT    QKSFM_XMLINDEX_REWRITE         XMLINDEX_REWRITE                    State                            1                        0
         11.1.0.6 XML_DML_RWT_STMT                                                  QKSFM_XML_REWRITE              XML_DML_RWT_STMT                    State                            1                        0
                  CHECK_ACL_REWRITE                NO_CHECK_ACL_REWRITE             QKSFM_CHECK_ACL_REWRITE        CHECK_ACL_REWRITE                   State                            1                        0
                  MONITOR                          NO_MONITOR                       QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_CHECK_ACL_REWRITE             CHECK_ACL_REWRITE                QKSFM_CHECK_ACL_REWRITE        NO_CHECK_ACL_REWRITE                State                            1                        0
                  NO_LOAD                                                           QKSFM_EXECUTION                NO_LOAD                             State                            1                        0
                  NO_MONITOR                       MONITOR                          QKSFM_ALL                      MONITOR                             State                            1                        0
                  NO_RESULT_CACHE                  RESULT_CACHE                     QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
                  RESULT_CACHE                     NO_RESULT_CACHE                  QKSFM_EXECUTION                RESULT_CACHE                               QBlock                    2                        0
--------
      32

11.1.0.7          BIND_AWARE                       NO_BIND_AWARE                    QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  CHANGE_DUPKEY_ERROR_INDEX                                         QKSFM_DML                      CHANGE_DUPKEY_ERROR_INDEX                         Object             4     32          256  288
                  IGNORE_ROW_ON_DUPKEY_INDEX                                        QKSFM_DML                      IGNORE_ROW_ON_DUPKEY_INDEX                        Object             4     32          256  288
                  NO_BIND_AWARE                    BIND_AWARE                       QKSFM_CURSOR_SHARING           BIND_AWARE                          State                            1                        0
                  RETRY_ON_ROW_CHANGE                                               QKSFM_DML                      RETRY_ON_ROW_CHANGE                 State                            1                        0
--------
       5

11.2.0.1 11.2.0.1 COALESCE_SQ                      NO_COALESCE_SQ                   QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 CONNECT_BY_ELIM_DUPS             NO_CONNECT_BY_ELIM_DUPS          QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 EXPAND_TABLE                     NO_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 FACTORIZE_JOIN                   NO_FACTORIZE_JOIN                QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16
         11.2.0.1 NO_COALESCE_SQ                   COALESCE_SQ                      QKSFM_COALESCE_SQ              COALESCE_SQ                                QBlock                    2 16                    16
         11.2.0.1 NO_CONNECT_BY_ELIM_DUPS          CONNECT_BY_ELIM_DUPS             QKSFM_ALL                      CONNECT_BY_ELIM_DUPS                       QBlock                    2 16                    16
         11.2.0.1 NO_EXPAND_TABLE                  EXPAND_TABLE                     QKSFM_TABLE_EXPANSION          EXPAND_TABLE                                      Object             4 16                    16
         11.2.0.1 NO_FACTORIZE_JOIN                FACTORIZE_JOIN                   QKSFM_JOINFAC                  FACTORIZE_JOIN                             QBlock                    2 16                    16

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
11.2.0.1 11.2.0.1 NO_PLACE_DISTINCT                PLACE_DISTINCT                   QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 NO_TRANSFORM_DISTINCT_AGG        TRANSFORM_DISTINCT_AGG           QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         11.2.0.1 PLACE_DISTINCT                   NO_PLACE_DISTINCT                QKSFM_DIST_PLCMT               PLACE_DISTINCT                             QBlock                    2 16                    16
         11.2.0.1 TRANSFORM_DISTINCT_AGG           NO_TRANSFORM_DISTINCT_AGG        QKSFM_TRANSFORMATION           TRANSFORM_DISTINCT_AGG                     QBlock                    2                        0
         21.1.0.1 NO_DST_UPGRADE_INSERT_CONV       DST_UPGRADE_INSERT_CONV          QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  APPEND_VALUES                    NOAPPEND                         QKSFM_CBO                      APPEND_VALUES                       State                            1                        0
                  DST_UPGRADE_INSERT_CONV          NO_DST_UPGRADE_INSERT_CONV       QKSFM_ALL                      DST_UPGRADE_INSERT_CONV             State                            1                        0
                  NO_STATEMENT_QUEUING             STATEMENT_QUEUING                QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  NO_SUBSTRB_PAD                                                    QKSFM_EXECUTION                NO_SUBSTRB_PAD                      State                            1                        0
                  STATEMENT_QUEUING                NO_STATEMENT_QUEUING             QKSFM_PARALLEL                 STATEMENT_QUEUING                   State                            1                        0
                  XMLINDEX_SEL_IDX_TBL                                              QKSFM_ALL                      XMLINDEX_SEL_IDX_TBL                State                            1                        0
--------
      19

11.2.0.2 11.2.0.2 NO_TABLE_LOOKUP_BY_NL            TABLE_LOOKUP_BY_NL               QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 NO_USE_HASH_GBY_FOR_PUSHDOWN     USE_HASH_GBY_FOR_PUSHDOWN        QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
         11.2.0.2 TABLE_LOOKUP_BY_NL               NO_TABLE_LOOKUP_BY_NL            QKSFM_TABLE_LOOKUP_BY_NL       TABLE_LOOKUP_BY_NL                                Object             4 16                    16
         11.2.0.2 USE_HASH_GBY_FOR_PUSHDOWN        NO_USE_HASH_GBY_FOR_PUSHDOWN     QKSFM_ALL                      USE_HASH_GBY_FOR_PUSHDOWN                  QBlock                    2                        0
                  NO_XDB_FASTPATH_INSERT           XDB_FASTPATH_INSERT              QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
                  XDB_FASTPATH_INSERT              NO_XDB_FASTPATH_INSERT           QKSFM_ALL                      XDB_FASTPATH_INSERT                 State                            1                        0
--------
       6

11.2.0.3 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER         NO_FULL_OUTER_JOIN_TO_OUTER      QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER      FULL_OUTER_JOIN_TO_OUTER         QKSFM_CBO                      FULL_OUTER_JOIN_TO_OUTER                          Object             4 16              256  272
         11.2.0.3 NO_OUTER_JOIN_TO_ANTI            OUTER_JOIN_TO_ANTI               QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 NO_SEMI_TO_INNER                 SEMI_TO_INNER                    QKSFM_CBO                      NO_SEMI_TO_INNER                                  Object             4 16              256  272
         11.2.0.3 OUTER_JOIN_TO_ANTI               NO_OUTER_JOIN_TO_ANTI            QKSFM_CBO                      OUTER_JOIN_TO_ANTI                                Object             4 16              256  272
         11.2.0.3 SEMI_TO_INNER                    NO_SEMI_TO_INNER                 QKSFM_CBO                      SEMI_TO_INNER                                     Object             4 16              256  272
--------
       6

11.2.0.4          DISABLE_PARALLEL_DML             ENABLE_PARALLEL_DML              QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
                  ENABLE_PARALLEL_DML              DISABLE_PARALLEL_DML             QKSFM_DML                      ENABLE_PARALLEL_DML                 State                            1                        0
--------
       2

12.1.0.1 12.1.0.1 BATCH_TABLE_ACCESS_BY_ROWID      NO_BATCH_TABLE_ACCESS_BY_ROWID   QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 BITMAP_AND                                                        QKSFM_BITMAP_TREE              BITMAP_AND                                        Object             4 16  32                48
         12.1.0.1 CLUSTERING                       NO_CLUSTERING                    QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 CLUSTER_BY_ROWID                 NO_CLUSTER_BY_ROWID              QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 CUBE_AJ                                                           QKSFM_JOIN_METHOD              ANTIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 CUBE_SJ                                                           QKSFM_JOIN_METHOD              SEMIJOIN                                   QBlock                    2 16                    16
         12.1.0.1 DATA_SECURITY_REWRITE_LIMIT      NO_DATA_SECURITY_REWRITE         QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 DECORRELATE                      NO_DECORRELATE                   QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_BATCH_TABLE_ACCESS_BY_ROWID   BATCH_TABLE_ACCESS_BY_ROWID      QKSFM_EXECUTION                BATCH_TABLE_ACCESS_BY_ROWID                       Object             4 16              256  272
         12.1.0.1 NO_CLUSTERING                    CLUSTERING                       QKSFM_CLUSTERING               CLUSTERING                          State                            1                        0
         12.1.0.1 NO_CLUSTER_BY_ROWID              CLUSTER_BY_ROWID                 QKSFM_CBO                      CLUSTER_BY_ROWID                                  Object             4 16              256  272
         12.1.0.1 NO_DATA_SECURITY_REWRITE         DATA_SECURITY_REWRITE_LIMIT      QKSFM_DATA_SECURITY_REWRITE    DATA_SECURITY_REWRITE_LIMIT         State                            1                        0
         12.1.0.1 NO_DECORRELATE                   DECORRELATE                      QKSFM_DECORRELATE              DECORRELATE                                QBlock                    2 16                    16
         12.1.0.1 NO_PARTIAL_JOIN                  PARTIAL_JOIN                     QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 NO_PARTIAL_ROLLUP_PUSHDOWN       PARTIAL_ROLLUP_PUSHDOWN          QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 NO_PQ_CONCURRENT_UNION           PQ_CONCURRENT_UNION              QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 NO_PQ_REPLICATE                  PQ_REPLICATE                     QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 NO_PQ_SKEW                       PQ_SKEW                          QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 NO_PX_FAULT_TOLERANCE            PX_FAULT_TOLERANCE               QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 NO_USE_CUBE                      USE_CUBE                         QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 NO_ZONEMAP                       ZONEMAP                          QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
         12.1.0.1 PARTIAL_JOIN                     NO_PARTIAL_JOIN                  QKSFM_PARTIAL_JOIN             PARTIAL_JOIN                                      Object             4 16              256  272
         12.1.0.1 PARTIAL_ROLLUP_PUSHDOWN          NO_PARTIAL_ROLLUP_PUSHDOWN       QKSFM_PQ                       PARTIAL_ROLLUP_PUSHDOWN                    QBlock                    2 16                    16
         12.1.0.1 PQ_CONCURRENT_UNION              NO_PQ_CONCURRENT_UNION           QKSFM_PQ                       PQ_CONCURRENT_UNION                 State  QBlock                    3                        0
         12.1.0.1 PQ_DISTRIBUTE_WINDOW                                              QKSFM_PQ                       PQ_DISTRIBUTE_WINDOW                       QBlock                    2 16                    16
         12.1.0.1 PQ_FILTER                                                         QKSFM_PQ                       PQ_FILTER                                  QBlock                    2                        0
         12.1.0.1 PQ_REPLICATE                     NO_PQ_REPLICATE                  QKSFM_PQ_REPLICATE             PQ_REPLICATE                                      Object             4 16              256  272
         12.1.0.1 PQ_SKEW                          NO_PQ_SKEW                       QKSFM_PQ                       PQ_SKEW                                           Object             4 16              256  272
         12.1.0.1 PX_FAULT_TOLERANCE               NO_PX_FAULT_TOLERANCE            QKSFM_PQ                       PX_FAULT_TOLERANCE                  State                            1                        0
         12.1.0.1 USE_CUBE                         NO_USE_CUBE                      QKSFM_USE_CUBE                 JOIN                                              Object             4 16      64      256  336
         12.1.0.1 ZONEMAP                          NO_ZONEMAP                       QKSFM_ZONEMAP                  ZONEMAP                                           Object             4                 256  256
                  AUTO_REOPTIMIZE                  NO_AUTO_REOPTIMIZE               QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  GATHER_OPTIMIZER_STATISTICS      NO_GATHER_OPTIMIZER_STATISTICS   QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  NO_AUTO_REOPTIMIZE               AUTO_REOPTIMIZE                  QKSFM_AUTO_REOPT               AUTO_REOPTIMIZE                     State                            1                        0
                  NO_GATHER_OPTIMIZER_STATISTICS   GATHER_OPTIMIZER_STATISTICS      QKSFM_DBMS_STATS               GATHER_OPTIMIZER_STATISTICS         State                            1                        0
                  USE_HIDDEN_PARTITIONS                                             QKSFM_PARTITION                USE_HIDDEN_PARTITIONS                      QBlock                    2                        0
                  WITH_PLSQL                                                        QKSFM_ALL                      WITH_PLSQL                          State                            1                        0
--------
      37

12.1.0.2 12.1.0.2 ADAPTIVE_PLAN                    NO_ADAPTIVE_PLAN                 QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 ANSI_REARCH                      NO_ANSI_REARCH                   QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 ELIM_GROUPBY                     NO_ELIM_GROUPBY                  QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 INMEMORY                         NO_INMEMORY                      QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 INMEMORY_PRUNING                 NO_INMEMORY_PRUNING              QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_ADAPTIVE_PLAN                 ADAPTIVE_PLAN                    QKSFM_ADAPTIVE_PLAN            ADAPTIVE_PLAN                       State                            1 16                    16
         12.1.0.2 NO_ANSI_REARCH                   ANSI_REARCH                      QKSFM_ANSI_REARCH              ANSI_REARCH                                QBlock                    2 16                    16
         12.1.0.2 NO_ELIM_GROUPBY                  ELIM_GROUPBY                     QKSFM_TRANSFORMATION           ELIM_GROUPBY                               QBlock                    2 16                    16
         12.1.0.2 NO_INMEMORY                      INMEMORY                         QKSFM_EXECUTION                INMEMORY                                   QBlock Object             6         64            64
         12.1.0.2 NO_INMEMORY_PRUNING              INMEMORY_PRUNING                 QKSFM_EXECUTION                INMEMORY_PRUNING                           QBlock Object             6         64            64
         12.1.0.2 NO_USE_VECTOR_AGGREGATION        USE_VECTOR_AGGREGATION           QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM              VECTOR_TRANSFORM                 QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 NO_VECTOR_TRANSFORM_DIMS         VECTOR_TRANSFORM_DIMS            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 NO_VECTOR_TRANSFORM_FACT         VECTOR_TRANSFORM_FACT            QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
         12.1.0.2 USE_VECTOR_AGGREGATION           NO_USE_VECTOR_AGGREGATION        QKSFM_VECTOR_AGG               USE_VECTOR_AGGREGATION                     QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM                 NO_VECTOR_TRANSFORM              QKSFM_VECTOR_AGG               VECTOR_TRANSFORM                           QBlock                    2 16                    16
         12.1.0.2 VECTOR_TRANSFORM_DIMS            NO_VECTOR_TRANSFORM_DIMS         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_DIMS                             Object             4 16      64            80
         12.1.0.2 VECTOR_TRANSFORM_FACT            NO_VECTOR_TRANSFORM_FACT         QKSFM_VECTOR_AGG               VECTOR_TRANSFORM_FACT                             Object             4 16      64            80
                  RESERVOIR_SAMPLING                                                QKSFM_EXECUTION                RESERVOIR_SAMPLING                  State                            1                        0
--------

VERSION  OUTLINE  NAME                             INVERSE                          SQL_FEATURE                    CLASS                               BIT0   BIT1   BIT2   BIT3   TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
      19

12.2.0.1 12.2.0.1 BUSHY_JOIN                       NO_BUSHY_JOIN                    QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 CONTAINERS                                                        QKSFM_ALL                      CONTAINERS                          State                            1                        0
         12.2.0.1 DIST_AGG_PROLLUP_PUSHDOWN        NO_DIST_AGG_PROLLUP_PUSHDOWN     QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 ELIMINATE_SQ                     NO_ELIMINATE_SQ                  QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_BUSHY_JOIN                    BUSHY_JOIN                       QKSFM_BUSHY_JOIN               BUSHY_JOIN                                 QBlock                    2 16                    16
         12.2.0.1 NO_DIST_AGG_PROLLUP_PUSHDOWN     DIST_AGG_PROLLUP_PUSHDOWN        QKSFM_PQ                       DIST_AGG_PROLLUP_PUSHDOWN                  QBlock                    2 16                    16
         12.2.0.1 NO_ELIMINATE_SQ                  ELIMINATE_SQ                     QKSFM_ELIMINATE_SQ             ELIMINATE_SQ                               QBlock                    2 16                    16
         12.2.0.1 NO_OR_EXPAND                     OR_EXPAND                        QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 NO_USE_DAGG_UNION_ALL_GSETS      USE_DAGG_UNION_ALL_GSETS         QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 NO_USE_HASH_GBY_FOR_DAGGPSHD     USE_HASH_GBY_FOR_DAGGPSHD        QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_DISTINCT   USE_PARTITION_WISE_DISTINCT      QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 NO_USE_PARTITION_WISE_GBY        USE_PARTITION_WISE_GBY           QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
         12.2.0.1 ORDER_SUBQ                                                        QKSFM_TRANSFORMATION           ORDER_SUBQ                                 QBlock                    2 16                    16
         12.2.0.1 OR_EXPAND                        NO_OR_EXPAND                     QKSFM_CBQT_OR_EXPANSION        OR_EXPAND                                  QBlock                    2 16                    16
         12.2.0.1 USE_DAGG_UNION_ALL_GSETS         NO_USE_DAGG_UNION_ALL_GSETS      QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         12.2.0.1 USE_HASH_GBY_FOR_DAGGPSHD        NO_USE_HASH_GBY_FOR_DAGGPSHD     QKSFM_ALL                      USE_HASH_GBY_FOR_DAGGPSHD                  QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_DISTINCT      NO_USE_PARTITION_WISE_DISTINCT   QKSFM_PARTITION                USE_PARTITION_WISE_DISTINCT                QBlock                    2                        0
         12.2.0.1 USE_PARTITION_WISE_GBY           NO_USE_PARTITION_WISE_GBY        QKSFM_PARTITION                USE_PARTITION_WISE_GBY                     QBlock                    2                        0
                  DATA_VALIDATE                                                     QKSFM_EXECUTION                DATA_VALIDATE                       State                            1                        0
                  FRESH_MV                                                          QKSFM_MVIEWS                   FRESH_MV                            State                            1                        0
                  SQL_SCOPE                                                         QKSFM_COMPILATION              SQL_SCOPE                           State                            1                        0
                  XMLTSET_DML_ENABLE                                                QKSFM_ALL                      XMLTSET_DML_ENABLE                  State                            1                        0
--------
      22

18.1.0   18.1.0   ANSWER_QUERY_USING_STATS         NO_ANSWER_QUERY_USING_STATS      QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_ANSWER_QUERY_USING_STATS      ANSWER_QUERY_USING_STATS         QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS                   QBlock                    2 16                    16
         18.1.0   NO_PUSH_HAVING_TO_GBY            PUSH_HAVING_TO_GBY               QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   NO_REORDER_WIF                   REORDER_WIF                      QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   NO_USE_PARTITION_WISE_WIF        USE_PARTITION_WISE_WIF           QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
         18.1.0   PUSH_HAVING_TO_GBY               NO_PUSH_HAVING_TO_GBY            QKSFM_EXECUTION                PUSH_HAVING_TO_GBY                         QBlock                    2                        0
         18.1.0   REORDER_WIF                      NO_REORDER_WIF                   QKSFM_PARTITION                REORDER_WIF                                QBlock                    2                        0
         18.1.0   USE_PARTITION_WISE_WIF           NO_USE_PARTITION_WISE_WIF        QKSFM_PARTITION                USE_PARTITION_WISE_WIF                     QBlock                    2                        0
                  AV_CACHE                                                          QKSFM_EXECUTION                AV_CACHE                                   QBlock                    2                        0
                  CURRENT_INSTANCE                                                  QKSFM_ALL                      CURRENT_INSTANCE                    State                            1                        0
                  MEMOPTIMIZE_WRITE                                                 QKSFM_EXECUTION                MEMOPTIMIZE_WRITE                   State                            1                        0
                  PDB_LOCAL_ONLY                                                    QKSFM_DML                      PDB_LOCAL_ONLY                      State                            1                        0
                  SKIP_PROXY                                                        QKSFM_ALL                      SKIP_PROXY                          State                            1                        0
                  SUPPRESS_LOAD                                                     QKSFM_DDL                      SUPPRESS_LOAD                       State                            1                        0
                  SYSTEM_STATS                                                      QKSFM_ALL                      SYSTEM_STATS                        State                            1 16              256  272
--------
      15

19.1.0   19.1.0   NO_PQ_EXPAND_TABLE               PQ_EXPAND_TABLE                  QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   NO_USE_SCALABLE_GBY_INVDIST      USE_SCALABLE_GBY_INVDIST         QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
         19.1.0   PQ_EXPAND_TABLE                  NO_PQ_EXPAND_TABLE               QKSFM_TABLE_EXPANSION          PQ_EXPAND_TABLE                                   Object             4 16                    16
         19.1.0   USE_SCALABLE_GBY_INVDIST         NO_USE_SCALABLE_GBY_INVDIST      QKSFM_PQ                       USE_SCALABLE_GBY_INVDIST                   QBlock                    2                        0
                  JSON_LENGTH                                                       QKSFM_EXECUTION                JSON_LENGTH                         State                            1                        0
                  QUARANTINE                                                        QKSFM_EXECUTION                QUARANTINE                          State                            1                        0
--------
       6

20.1.0   20.1.0   FORCE_JSON_TABLE_TRANSFORM       NO_JSON_TABLE_TRANSFORM          QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_JSON_TABLE_TRANSFORM          FORCE_JSON_TABLE_TRANSFORM       QKSFM_JSON_REWRITE             FORCE_JSON_TABLE_TRANSFORM          State                            1                        0
         20.1.0   NO_SET_GBY_PUSHDOWN              SET_GBY_PUSHDOWN                 QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
         20.1.0   SET_GBY_PUSHDOWN                 NO_SET_GBY_PUSHDOWN              QKSFM_ALL                      SET_GBY_PUSHDOWN                           QBlock                    2 16                    16
                  ANALYTIC_VIEW_SQL                                                 QKSFM_COMPILATION              ANALYTIC_VIEW_SQL                          QBlock                    2                        0
                  DENORM_AV                                                         QKSFM_COMPILATION              DENORM_AV                                  QBlock                    2                        0
--------
       6

21.1.0   21.1.0   DAGG_OPTIM_GSETS                 NO_DAGG_OPTIM_GSETS              QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   HASHSET_BUILD                                                     QKSFM_EXECUTION                HASHSET_BUILD                              QBlock                    2 16                    16
         21.1.0   NO_DAGG_OPTIM_GSETS              DAGG_OPTIM_GSETS                 QKSFM_GROUPING_SET_XFORM       DAGG_OPTIM_GSETS                           QBlock                    2                        0
         21.1.0   NO_OBY_GBYPD_SEPARATE            OBY_GBYPD_SEPARATE               QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   NO_PQ_NONLEAF_SKEW               PQ_NONLEAF_SKEW                  QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
         21.1.0   OBY_GBYPD_SEPARATE               NO_OBY_GBYPD_SEPARATE            QKSFM_PQ                       OBY_GBYPD_SEPARATE                         QBlock                    2 16                    16
         21.1.0   ORDER_KEY_VECTOR_USE                                              QKSFM_VECTOR_AGG               ORDER_KEY_VECTOR_USE                       QBlock                    2 16              256  272
         21.1.0   OSON_GET_CONTENT                                                  QKSFM_JSON                     OSON_GET_CONTENT                    State                            1                        0
         21.1.0   PQ_NONLEAF_SKEW                  NO_PQ_NONLEAF_SKEW               QKSFM_PQ                       PQ_NONLEAF_SKEW                                   Object             4 16              256  272
--------
       9


388 rows selected

March 2, 2022

Generated Predicates

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 11:25 am GMT Mar 2,2022

A question arrived on the MOS Community forum yesterday (needs an account if you want to see the original) that reported a couple of fragments of a CBO trace (10053) file:

----- Current SQL Statement for this session (sql_id=4c85twjpdg8g9) -----
select /*+ 123456 */ count(*) from gl_detail where prepareddatev='2022-01-22 15:00:00'

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "NC63"."GL_DETAIL" "GL_DETAIL" 
WHERE "GL_DETAIL"."PREPAREDDATEV"='2022-01-22 15:00:00' 
AND SUBSTR("GL_DETAIL"."PREPAREDDATEV",1,10)='2022-01-22'

The question was:

Why after transformations ,oracle add condition SUBSTR(“GL_DETAIL”.”PREPAREDDATEV”,1,10)=’2022-01-22′

Mark Powell asked for the execution plan and information about indexes (normal and function-based) and histograms, as well as asking for the Oracle version. I asked about constraints and virtual columns and, in particular, the possibility of a virtual column being used as a partition key.

We didn’t get explicit answers to all our questions, but we did get “no constraints, no virtual columns, no partitioning”, and we also got the full 10053 trace file which, given the simplicity of the query, was mercifully short .. a mere 95KB and 2,800 lines.

The key aid to reading 10053 trace files is knowing what you’re expecting to see before you start looking. And with a generated predicate there was likely to be something that would tell me about about the “column” that caused the predicate to appear and the arithmetic that was the consequence of that predicate coming into existence. So I started with the section headed “SINGLE TABLE ACCESS PATH” where the cardinality estimate (for each individual table) would be calculated. This showed two columns being considered for the single table in the query:

  Column (#77): 
    NewDensity:0.000000, OldDensity:0.000035 BktCnt:75, PopBktCnt:11, PopValCnt:1, NDV:8314506
  Column (#77): PREPAREDDATEV(
 
  Column (#88): 
    NewDensity:0.000188, OldDensity:0.000583 BktCnt:75, PopBktCnt:11, PopValCnt:1, NDV:4551
  Column (#88): SYS_NC00088$(

Check the name of column #88 – sys_nc00088$ – that’s an internally generated virtual column which may well be to be associated with a function-based index, so let’s back up a bit to the “BASIC STATISTICAL INFORMATION” and (thirteen sets of) index stats for the table where we find:

  Index: I_GL_DETAIL_7  Col#: 88
    LVLS: 3  #LB: 433301  #DK: 4551  LB/K: 95.00  DB/K: 5922.00  CLUF: 26953639.00

The obvious first guess is that column #88 is the invisible virtual column underpinning an index that has been created on substr(prepareddatev,1,10) and here’s a quick and dirty test script to demonstrate that this could be the correct guess.

create table t1 (v1 varchar2(20), v2 varchar2(1));
create index t1_i1 on t1(substr(v1,1,10));

select column_name, virtual_column, hidden_column from user_tab_cols where table_name = 'T1';
select * from user_ind_expressions where table_name = 'T1';

insert into t1 values('2022-03-02 09:01:00', 'x');
commit;

execute dbms_stats.gather_table_stats(user,'t1')

set autotrace traceonly explain

select /*+ full(t1) */  * from t1 where v1 = '2022-03-02 09:01:00';

set autotrace off

And here’s the output cut and pasted from an SQL*Plus session running 11.2.0.4 (which is the version the CBO trace file came from).

Table created.


Index created.


COLUMN_NAME          VIR HID
-------------------- --- ---
V1                   NO  NO
V2                   NO  NO
SYS_NC00003$         YES YES

3 rows selected.


INDEX_NAME           TABLE_NAME                COLUMN_EXPRESSION                        COLUMN_POSITION
-------------------- ------------------------- ---------------------------------------- ---------------
T1_I1                T1                        SUBSTR("V1",1,10)                                      1

1 row selected.


1 row created.


Commit complete.


PL/SQL procedure successfully completed.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    33 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    33 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("V1"='2022-03-02 09:01:00' AND
              SUBSTR("V1",1,10)='2022-03-02')

We see the “extra” predicate and a column with a name of the form sys_ncXXXXX$. The results from more recent versions of Oracle should be the same. I think there’s a pretty good chance that if the OP runs suitable queries against XXX_tab_cols and XXX_ind_expressions they’ll see similar results that explain the predicate that surprised them.

Footnote

There are various notes on the blog about constraints and transitive closure generating extra predicates, and how the optimizer can use function-based indexes that have definitions that are “good enough” though not perfect matches for user-supplied predicates. This is just another little detail in how the optimizer tries to find as much helpful information as it can from the data dictionary. The earliest note I can find on my blog about this at present is about partition elimination and generated predicates – which prompted various comments about function-based indexes and predicate generation.

February 21, 2022

“Ignoring Hints” Catalogue

Filed under: Oracle — Jonathan Lewis @ 9:44 am GMT Feb 21,2022

This is a list of all articles I’ve written to examine claims that Oracle has “ignored a hint”, including articles where other aspects of hinting (such as SQL Plan Baslines) don’t work as expected.

Each entry has a date stamp and a short note of the contents. Some of these will also appear in the generic “Hints” catalogue. The articles are generally listed most-recent first.

  • Use_nl() revisited (Oct 2021) – a detailed examination of how a simple hint has been used by the optimizer but still produce a plan that appears to have ignored it.
  • Index hints – pt. 1 (Jan 2021) – a voyage of discovery as index hints don’t do exactly what you expect.
    • Index hints – pt. 2 (Jan 2021) – continuing the voyage of discovery with index skip scans and range scans.
  • Hinting errors (Jan 2021) – there are many reasons why your hints might be wrong; this is a list (probably incomplete) of possible reasons that I extracted from the Oracle executable.
  • Faking Baselines (Feb 2020) – an example of an SQL Plan Baseline that doesn’t reproduce the plan it’s supposed to.
  • Ignoring hints by choice (June 2019) – changes to the “_optimizer_ignore_hints” (previously hidden) parameter in 18g onwards. It’s official, and has a companion.
  • Avoiding APPEND (March 2019) – ideas for making Oracle ignore an APPEND hint in a 3rd party application.
  • Parallelism (May 2017) – the parallel(N) hint does not mean “make this query run parallel with degree N”.
  • What does use_nl(a,b) mean (Jan 2017) – a common misconception explained; this hint does not mean “do a nested loop from a to b”.
  • Hinting across upgrades (March 2016) – updates can introduce new transformations that make your previously working set of hints for a query incomplete: an example.
  • Ignore this hint – please (May 2014) – Oracle doesn’t “ignore” hints, but sometimes you might wish it did.

CTE Enhancement

Filed under: 12c,Oracle,Performance,Transformations — Jonathan Lewis @ 9:24 am GMT Feb 21,2022

For many years I’ve told people that when you materialize a CTE (common table expression / “with” subquery) the result set will be written to the temporary tablespace using direct path writes and will be read back using cached reads. This stopped being an accurate description in 12c.

There is a clue about this in the way that the corresponding execution plans and I’ll be pointing that out later. The key difference between earlier versions of Oracle and newer versions is that the GTT (global temporary table) that holds the materialized result set is not necessarily written to disc, and may even avoid allocating a temporary segment.

I started looking at this feature a couple of days ago after remembering that someone, somewhere, had mentioned some details about a temporary object being kept in the PGA rather than being written to disc if the size wasn’t too big. I couldn’t remember if this was GTTs or temporary LOBs (or something completely different) and I only had a vague memory that there was a critical size that might have been 256KB; so I started experimenting with materializing CTEs.

Here’s the script I used to establish a baseline in 11g. I took a fairly arbitrary starting guess that if there was a PGA/Temp split is might be around 64KB.

rem
rem     Script:         cte_writes.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2022
rem     Purpose:        
rem
rem     Last tested 
rem             19.11.0.0
rem             12.2.0.1
rem             11.2.0.4
rem

alter session set events '10046 trace name context forever, level 8';

prompt  ==============================
prompt  First sample - just under 64KB
prompt  ==============================

set arraysize  35
set pagesize  35

set pause Waiting...
set pause on

execute snap_ts.start_snap
execute snap_my_stats.start_snap

with g1 as (
        select  /*+ materialize */
                lpad(rownum,1024)       v1
        from
                dual
        connect by
                level <= 63
)
select
        substr(v1,-10)
from
        g1
;

alter session set events '10046 trace name context off';

execute snap_my_stats.end_snap
execute snap_ts.end_snap

prompt  ==============================
prompt  Second sample - just over 64KB
prompt  ==============================

execute snap_ts.start_snap
execute snap_my_stats.start_snap

with g1 as (
        select  /*+ materialize */
                lpad(rownum,1024)       v1
        from
                dual
        connect by
                level <= 64
)
select
        substr(v1,-10)
from
        g1
;

execute snap_my_stats.end_snap
execute snap_ts.end_snap

set pause off

The calls to the package snap_my_stats are the usual ones I use (very old source at this Wayback URL) to report the session’s activity (v$mystat) between start and end snapshot and the calls in the snap_ts package do the same for the I/O per tablespace, summing bu tablespace across v$filestat and v$tempstat.

This script basically materializes and reports a GTT with a single column of 1,024 bytes, and in this case either 63 or 64 rows. You’ll note that I’ve built another assumption into the code that the CTE (if kept in memory) won’t follow all the details of a “real” table block, but will be a simple array with a tiny overhead per row – I’ve also assumed that the optimizer isn’t smart enough (or foolhardy enough) to push the substr() call inside the CTE.

I’ve set pause on and set both the arraysize and pagesize to a value less than the total number of rows I’ll be fetching so that I can check a couple of dynamic performance views (in particular v$sort_usage) from another session while the queries are running.

As a starting point, here are some critical values I’ve selected from the various outputs for just the 63 row case when running 11.2.04:

-------------
Session stats
-------------
Name                                                 Value
----                                                 -----
physical reads                                           9
physical reads cache                                     9
physical writes                                          9
physical writes direct                                   9
physical writes direct temporary tablespace              9

---------
T/S Stats 
---------
TS#        Reads      Blocks   Avg Csecs    Max      Writes      Blocks   Avg Csecs    Max Tablespace
----       -----      ------   ---------    ---      ------      ------   ---------    --- -------------------
   3           1           9        .000      1           1           9        .000      0 TEMP


Since I've enabled extended tracing at level 8 (waits) I can also show you the I/O waits reported for the reads and writes:
WAIT #140185232180256: nam='direct path write temp' ela= 180 file number=201 first dba=35073 block cnt=9 obj#=-1 tim=1645178184267768
...
WAIT #140185232180256: nam='db file scattered read' ela= 183 file#=201 block#=35073 blocks=9 obj#=-40016369 tim=1645178184268342

A dump of the 9 blocks of the temporary file (the “file number-201” translates to tempfile 1 since I have db_files = 200) starting at block 35073 looks like an ordinary table with pctfree 10 (which is one of the annoying details of GTTs – you can’t adjust it), 3 ITL slots (which is normal for CTAS) and 7 rows per block.

So, for 11g, we conclude that the effect of materializing a CTE is simply to create a GTT in the temporary tablespace, write it out using direct path writes, then read it back into the buffer cache using db file scattered reads. (You might want to confirm that this always happens, even if the CTE holds only one row.)

If you take advantage of the pause to issue “alter system flush buffer_cache” from another session you can also dump the segment header block (35072 in my case) to see that it’s a normal table segment header block – using freelist management, not ASSM because that’s the way temporary tablespaces have to be declared. The segment header block didn’t get written to disc in the normal course of the test.

12c Enhancement

This is the moment where the second query, and the pause that allows me to query v$sort_usage, becomes significant. When I started 12.2.0.1 with the 63 row query I saw:

  • No I/O on the temporary tablespace
  • No entry in v$sort_usage

To my great satisfaction the 64 row query did report I/O to the temporary tablespace (10 blocks this time – needing one extra block to cater for the 64th row) with v$sort_usage reporting a segment being created on my behalf. Obviously I re-ran the test a couple of times, flushing the buffer cache and shared pool, and connecting to a new session each time. The results were totally consistent: 63 rows => no GTT, 64 rows => GTT.

If you’re feeling a little suspicious at this point, bear with me.

This is the point where I switched to 19.11.0.0 – and both queries ran in memory with no sign of a GTT being created. Luckily I had cloned the query several times in the script generating different pairs of numbers of rows: 127/128, 255/256, 511/512, 1023/1024, and when I hit 1024 (and 1023) my session produced a GTT.

Somewhere between 512 and 1023 rows I was hitting a critical breakpoint – so I nearly started working through a binary chop to find the actually breakpoint; luckily, though, I had a little inspiration: if the overhead per row was 3 bytes (as it would be for a normal table column of more than 254 bytes) then 1023 rows would have an overhead of about 3KB – so I should test 1021 rows if I wanted to test a memory of just under 1MB.

Sure enough, at 1021 rows the GTT didn’t appear, at 1022 rows it did – time after time after time.

But …

My tests seemed to be totally repeatable. Again, I connected to a new session a few times, I flushed the buffer cache, I flushed the shared pool, I checked v$sort_usage. Consistently the results seemed to say:

  • 12.2 uses the PGA up to 64KB then dumps to a GTT
  • 19.11.0.0 uses the PGA up to 1MB then dumps to a GTT

Except – that night I had to shut down the two virtual machines because sometimes, for no obvious reason, I can’t hibernate my laptop while the VMs are running; and when I started everything up again the following morning and started re-running the tests as I wrote up the results something had changed. In fact my 19.11 instance didn’t dump to a GTT until I had reached nearly 10MB of data and my 12.2 wasn’t even dumping at 1MB; and I have no idea why a complete restart made such a difference.

After spending a little time trying to figure out what had changed – and I think it may have been that I’d been running the previous day’s tests after doing a lot of heavy work with temporary LOBs trying to pin down an anomaly with the handling of the temporary tablespace – I finally tried a google search using keywords that might be relevant and found this article that Keith Laker wrote about 5 years ago.

The feature is known as In-memory “cursor-duration” temporary table. I mentioned a clue in the execution plans at the start of this note: materialization shows up with a “temp table transformation” operation followed, in 11g, by with a child operation of “load as select”; but in 12.2 the child operation is “load as select (cursor duration memory)”. I really should have started my invesigation by putting the entire text of that operation into a search engine.

Summary

(Basically the highlights from Keith’s article):

  • The “in-memory cursor-duration”temporary table” change appeared in 12.2
  • It can be used in a number of transformations that the optimizer does
  • It’s not possible to force the use of the feature for a given query, it’s down to an internal algorithm
  • The mechanism uses memory that is “essentially” PGA
  • Despite the name this feature does not require you to licence the In-Memory option
  • If you’re still using an older version of Oracle this could be a good reason for upgrading as it can reduce the I/O load particularly for “analytic” types of query at a cost of using extra memory.

All the work I had done trying to find a break-point where Oracle switched from using PGA to using a GTT had been a waste of time – and the apparently consistent results on the first day had been an “accident” dictated (possibly) by some PGA-related activity that had taken place before I started running my tests .

Footnotes and geeky things

Five years on from the publication date of Keith’s article we shouldn’t be surprised to see some changes. Keith notes that the mechanism will apply only to serial queries that do more than one pass over the table – but there are two points to raise there:

  • possibly the two-pass thing is because it usually takes two passes over a CTE before Oracle will materialize a CTE automatically; my example shows the in-memory effect on a single pass – but that was a forced materialization.
  • the restrictions on parallelism may have been relaxed by now – if you check for hidden parameters you will find: _in_memory_cdt_maxpx, default value 4, described as “Max Parallelizers allowed for IMCDT”.

Interestingly there are more “cdt” parameters in 12.2 than there are in 19.11, and there are clear indications of some changes in algorithm and mechanism:

12c parameters like '%cdt%
Parameter                                  System Value         Description
--------------------------------------------------------- -------------------- ---------------------------------
_cdt_shared_memory_limit                   0                    Shared Memory limit for CDT
_cdt_shared_memory_query_percent           20                   Per Query Percentage from CDT pool
_imcdt_use_mga                             ON                   MGA for In Memory CDT
_in_memory_cdt                             LIMITED              In Memory CDT
_in_memory_cdt_maxpx                       4                    Max Parallelizers allowed for IMCDT

19g parameters like '%cdt%'
Parameter                                  System Value         Description
--------------------------------------------------------- -------------------- ---------------------------------
_hcs_enable_in_mem_cdt_hint                FALSE                add hint opt_param('_in_memory_cdt', 'off')
_in_memory_cdt                             LIMITED              In Memory CDT
_in_memory_cdt_maxpx                       4                    Max Parallelizers allowed for IMCDT

The parameter “_in_memory_cdt” can take the values ON, LIMITED, or OFF – which tells you that even if you can’t force a query to use in-memory CDTs you can (if you really want to) stop a query from using the feature. There are a few notes about this parameter and its significance to RAC and parallel execution (for 12.2) on MOS – if you have an account – Doc ID 2388236.1 What is _in_memory_cdt Parameter?

The reference to MGA (the “managed global area”) in 12.2 is also quite interesting. This is documented as a Solaris feature using OSM to share memory between processes. For more general details you can review MOS Doc ID 2638904.1 MGA (Managed Global Area) Reference Note (again, only if you have an account).

The “new” oradebug mechanism shows (from 18c) a couple of relevant components under SQL compilation and execution that you could trace if you want to investigate further.

Components in library RDBMS:
--------------------------
  SQL_Compiler                 SQL Compiler ((null))
    ICDT_Compile               In Memory CDT Compilation (qks3t)
  SQL_Execution                SQL Execution (qer, qes, kx, qee)
    ICDT_Exec                  In Memory CDT Execution (qes3t, kxtt)

February 19, 2022

Infrastructure Catalogue

Filed under: Oracle — Jonathan Lewis @ 10:41 am GMT Feb 19,2022

This is a list of articles I’ve written that get a little heavily into technical details and won’t necessarily be very helpful to the typical DBA or developer. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

  • Why “lag() ignore nulls” is slow (May 2022): and how to work around the problem in some cases.
  • dbms_redefinition defect (May 2022): using copy_table_dependencies() can produce metadata inconsistencies.
  • Long v$ definitions (Mar 2022): Some definitions in v$fixed_view_definition are too long for the view. A workaround.
  • X$ row sizes (Mar 2022): Simple script to get some clue about rows in x$ structures
  • V$ cascade (Mar 2022): Why does is v$ a synonym for v_$ which is a view on v$
  • Digging into x$ (Mar 2022): preliminary investigation of x$ information
  • Session Count issue (Mar 2022): v$session, v$resource_limit and v$sysmetric don’t agree – digging in to x$
  • Querying v$session (Oct 2019): a performance threat for large systems
  • What’s my SID (Oct 2019): several ways of getting the SID (and some for the serial#)
  • Dumping redo (June 2019): includes examples of how to be selective in dumping (online and archived) redo log records.
  • Redo Op Codes (July 2017): my personal reference for translating redo op codes.
  • v$lock efficiency (May 2013): the benefit of collecting stats on individual x$ structures
  • Hint error (Feb 2012): why is v$sql_shared_memory returning no data?
  • Lock Modes (June 2010): my personal reference for translating numeric lock modes.
  • Oracle Pipes (Jan 2009): What’s currently inside a pipe created by dbms_pipe.

Lock Catalogue

Filed under: Oracle — Jonathan Lewis @ 10:34 am GMT Feb 19,2022

This is a list of all articles I’ve written about locks (or enqueues). Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (including one specifically on deadlocks), but otherwise are generally listed most-recent first.

General

  • Lock Modes (June 2010): my personal reference for translating numeric lock modes.
  • Locking SQL (April 2009): there is no explicit path in the v$ views that you can take to get from a locked row to the SQL that locked it

Deadlocks

  • Index ITLs (Feb 2022): an extreme demonstration of self-deadlocking deliberately engineered by overuse of autonomous transactions.
  • Materialized view oddity (Jan 2018): a strange (possibly transient) deadlock when refreshing nested materialized views.
  • Referential Integrity (May 2016): including a discussion of deadlocks due to concurrent processes mixing their inserts of parent and child rows.
  • Deadlock trace (Oct 2014): an explanation of how you can find out more details about the source of a deadlock from the deadlock trace file.
  • An unusual deadlock (Sept 2014): between a library cache lock and a normal DML (TX) lock. (Includes a link to an example of a deadlock between a rowcache lock and a library cache lock.)
  • Shrink Space bug (Mar 2014): a bug, fixed in 12.2, demonstrating that Oracle does not “resolve” deadlocks, it just makes it possible for the application to resolve the deadlock.
  • Deadlock Graph (Nov 2013): look carefully at the deadlock graph – it may not be about TX / TM locks.
  • Rows waited on (April 2013): be a little cautious about “rows waited on” information following a deadlock graph; the figures may be held over from a previous operation.
  • Lock bug (March 2013): an unexpected (and incorrect) deadlock error; possibly due to RAC effects, and probably fixed by late 11.2
  • Deadlock Detection (Feb 2013): Oracle does not resolve deadlocks, it merely detects them and makes it possible for the application to resolve them.
  • A deadlock variation (Aug 2011): many deadlocks are TX deadlocks in mode 6 (X) – but other modes are possible.
  • An IOT deadlock (April 2011): a reason why the deadlock graph may look a little different from the commonest type.

February 18, 2022

Index ITL Limit

Filed under: deadlocks,Indexing,Infrastructure,Locks,Oracle,trace files — Jonathan Lewis @ 5:02 pm GMT Feb 18,2022

Here’s a little script that I wrote more than 10 years ago when investigating some undesirable behaviour with indexes. I’ve just rediscovered it after seeing it mentioned in a comment to an old article that I had been prompted to revisit. This isn’t going to help you solve any specific problem, but it might give you some feel for how much work Oracle has to do to cater for efficient index maintenance.

The script is just a dirty little hack to crash a session by calling a procedure recursively until something breaks – in this case when all the ITL slots of an index leaf block are full and the block doesn’t split for the next insert (which was a little surprising in its own right).

rem
rem     Script:         itl_limit.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2010
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem             11.1.0.6
rem  

create table itl_limit(n1 number) pctfree 0;
create unique index il_01 on itl_limit(n1) pctfree 0;

create or replace procedure recursive_itl(i_tx_count number)
as
        pragma autonomous_transaction;
begin
        if i_tx_count != 0 then
                insert into itl_limit values(200 - i_tx_count);
                recursive_itl(i_tx_count - 1);
                commit;
        end if;
end;
/

alter session set events '10046 trace name context forever, level 8';

execute recursive_itl(200);

alter system checkpoint;

alter session set events '10046 trace name context off';

prompt  ==========================================
prompt  If there is no index on the table then you
prompt  should see 169 rows in one block and 31 in
prompt  the other. But if there is an index there
prompt  should be no rows thanks to the rollback
prompt  caused by the error.
prompt  ==========================================

select 
        dbms_rowid.rowid_block_number(rowid), count(*) 
from 
        itl_limit
group by 
        dbms_rowid.rowid_block_number(rowid)
;

prompt  =================================
prompt  Try for a tree dump on the index
prompt  after which you can dump the root
prompt  block to see the ITL entries
prompt  =================================

column object_id new_value m_object_id

select  object_id, object_type, object_name
from    user_objects
where   object_name = 'IL_01'
/

alter session set events 'immediate trace name treedump level &m_object_id ';

If you comment out the creation of the index il_01 then the script completes very quickly (complaining, of course, about the attempt to do a treedump with a null level (ORA-49100: Failed to process event statement). Assuming you’re using an 8KB block size the rowid count query will show that you’ve got 169 rows in one block and 31 rows in the other – and if you dump the block with 169 rows you will find that (a) there’s loads of empty space in the block, and (b) the number of ITL entries has reached 169, and that’s the limiting factor that restricted the number of rows we could insert.

If you create the index then you’ll have to wait roughly 142 seconds for the procedure call to fail (with an ORA-00060: deadlock detected error) and the script to complete. And when you generate the tkprof output from the trace file you’ll find that most of the time is spent in the following statement:

INSERT INTO ITL_LIMIT
VALUES
(200 - :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    169      0.96     143.69         11      56324       4939         168
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      170      0.96     143.69         11      56324       4939         168

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 138     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  ITL_LIMIT (cr=5 pr=0 pw=0 time=192 us starts=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        11        0.00          0.00
  enq: TX - allocate ITL entry                   48        5.01        142.49
********************************************************************************

We’ve execute 169 times, but reported only 168 rows, which suggests something went wrong on one insert. The 48 waits for “enq:TX – allocate ITL entry” invite further investigation, of course – so let’s get back to the raw trace file and find them.

grep -T -n  "WAIT.*alloc" or19_ora_15879.trc

 571   :WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 1000682 name|mode=1415053316 usn<<16 | slot=10420232 sequence=2 obj#=94939 tim=5285776687
 574   :WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 1016171 name|mode=1415053316 usn<<16 | slot=1310726 sequence=6209 obj#=94939 tim=5286793280
 577   :WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 1001580 name|mode=1415053316 usn<<16 | slot=10223624 sequence=2 obj#=94939 tim=5287795235
...
 709   :WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 4999483 name|mode=1415053316 usn<<16 | slot=11468804 sequence=2 obj#=94939 tim=5423905081
42381  :WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 2999710 name|mode=1415053316 usn<<16 | slot=9633800 sequence=2 obj#=94939 tim=5426905028

All 48 waits occur after the 169th attempt to insert a row. Oracle rotates through 12 ITL slots waiting one second on each, then goes round the loop again waiting 2 seconds on each, then 4 seconds, then 5 seconds – except it doesn’t wait on the 12th ITL on the final loop, instead it reports “DEADLOCK DETECTED (ORA-00060)” in the trace file and dumps a Deadlock graph of the form:

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00930008-00000002-0039DED3-00000000         44      49     X        18979      44      49           S  18979

Note the self-deadlock – the holder and waiter are the same session. After the deadlock graph we get the usual stack dump and after 48,000 lines of trace we see the message “Attempting to break deadlock by signaling ORA-00060” after which the session waits on the 12th ITL for 3 seconds and then the whole stack of autonomous transactions rolls back:

WAIT #139861052053576: nam='enq: TX - allocate ITL entry' ela= 2999710 name|mode=1415053316 usn<<16 | slot=9633800 sequence=2 obj#=94939 tim=5426905028
EXEC #139861052053576:c=429678,e=142567211,p=0,cr=504,cu=11,mis=0,r=0,dep=1,og=1,plh=0,tim=5427342894
ERROR #139861052053576:err=60 tim=5427342914
CLOSE #139861052053576:c=1,e=1,dep=1,type=3,tim=5427343141
XCTEND rlbk=1, rd_only=0, tim=5427343240
XCTEND rlbk=1, rd_only=0, tim=5427343336
...

My test script reports the object_id (not the data_object_id) of the index and does a treedump of it (which should show just a root block (which is also a leaf block) with no entries. The root block address lets you do a treedump, which will show something like:

----- begin tree dump
leaf: 0x400008b 67109003 (0: row:0.0 avs:3988)
----- end tree dump

Notice how the available space (avs) in this root/leaf block is only 3988 bytes rather than roughly 8,000 for an 8KB block size. That’s because the ITL area has taken advantage of its ability to grow to half the size of the block, and once it has grown it doesn’t shrink (Note: there is a hard limit of 255 which will only become apparent with larger block sizes – and if you want to test that you’ll have to edit my script to change both occurrences of the constant 200 to (at least) 256).

The data block address (DBA) of the root/leaf block is given in both hexadecimal and decimal; but if you can’t work out which file and block number this represents (I happen to know it’s file 16, and I can convert 0x8b to 139 decimal in my head) then you can check for the segment header block and add one to the block number; or use calls to the dbms_utility package to translate the DBA before doing a block dump:

SQL> select
  2          dbms_utility.data_block_address_file(67109003) file#,
  3          dbms_utility.data_block_address_block(67109003) block#
  4  from
  5          dual
  6  /

     FILE#     BLOCK#
---------- ----------
        16        139

1 row selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 16 block 139;

System altered.

Here’s the section of the resulting trace that shows you how bad things had got before the

 seg/obj: 0x172dd  csc:  0x0000000001311be1  itc: 169  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x4000088 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000001311be1
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0xa8   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0xa9   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
Leaf block dump

A final comment: if you’re wondering why the table (without an index) can get to 169 rows while the index achieves 168 rows and fails on the 169th – there’s one ITL in the index ITL area that is reserved for the “service ITL” (see answer 2), the one that Oracle uses to register and hold the block on an “index node split”.

Footnote

One of the entertaining things, about poking around with Oracle is the way that you discover unexpected details – some of which are side-effects that you realise you should have predicted, some of which are just a little bizarre. In this case the easter egg in the trace file was the following statement (reproduced from the tkprof output, and made highly visible because I used the sort=execnt option.)

update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,
  undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13
where
 us#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      336      0.00       0.00          0          0          0           0
Execute    336      0.02       0.02          3        336        531         336
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      672      0.03       0.03          3        336        531         336

The predictable side effect was that Oracle was going to create a number of new undo segments: as a standard detail of minimising contention Oracle tries to give every concurrent transaction (which means every one of my autonomous transactions) its own undo segment.

The surprising detail was the /*+ rule */ hint – still in 19.11.0.0. I guess that that was to ensure that Oracle executed the update through an index access – but possibly a suitable /*+ index() */ hint would be more appropriate to almost every version of Oracle.

There were a number of other segment/undo related statements that operated 140+ times in the course of this test – which did make me wonder if a very busy OLTP system (doing lots of tiny, concurrent, transacation) with a lot of undo segments could spend a significant amount of its time managing undo segments – but that’s a thought for another day. Another “look at that some time” thing that appeared was the large number of selects and inserts to a table call undohist$.

February 16, 2022

Materialized Views catalogue

Filed under: Oracle — Jonathan Lewis @ 6:36 pm GMT Feb 16,2022

This is a list of articles I’ve written about materialized views. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

You may also find some useful ideas in the list of MOS articles (needs an account) in Doc ID 1353040.1: Primary Note for Materialized View (MVIEW) though the usual “trust” warning applies – it has a very recent “last update” date but some of the notes referenced are very old.

  • MVs and Hacking 3rd parties (Dec 2021) – a very old article (10g) very recently published presenting an idea of working around 3rd party performance issues
  • Oracle’ aid to query rewrite (Aug 2020) – an introduction to the explain_mview() and explain_rewrite() procedures that help you discover why a matieralized view isn’t doing what you hoped for.
  • Drop unused columns bug (Jan 2020) – fixed in 21.1, patches needed prior to 19.10. This bug may affect you if you have materialized view logs on tables where you’ve marked columns unused then drop them.
  • Nested MVs (Jan 2018) – when written this demonstrated a strange self-deadlock error with nested MVs in 12.2. The deadlock no longer appears on my 12.2 system, but the note is a nice story about how to set up nested MVs anyway.
  • A Distributed Query Trap (Nov 2016/Sept 2021) – Materialized Views came into existence as “snapshots” pulling data from remote databases; when you do this you need to remember that some things can turn a remote query into a distributed query.
  • Union All MV (July 2016) – an example of how to create a materialized view of a UNION ALL view.
  • 12c Out of place refresh (March 2015) – notes on the mechanism introduced in 12.1
  • A bigfile bug for MVs (April 2014) – up to 11.2.0.4 a materialized view log “with rowid” of a table in a bigfile tablespace would cause a crash on refresh. Fixed in 12.2 with a backport to 12.1
  • rman duplicates and MVs (March 2014) – a surprising requirement for “skip tablespaces” (that may have been removed by now).
  • Out of place MV refresh – 11g style (Sept 2013) – Meeting the client’s requirements for refreshing an MV before 12c “out of place” refresh existed.
  • MVs that crash dbms_stats (Aug 2013) – and are still doing it even in 19.11.0.0 and 21.3.0.0

February 7, 2022

Sequence catalogue

Filed under: Oracle — Jonathan Lewis @ 11:17 am GMT Feb 7,2022

This is a list of all articles I’ve written about sequences. Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (mainly because I hadn’t previously created an index for a short series I wrote for Simpletalk), but otherwise are generally listed most-recent first.

Miscellaneous

  • Sequence Accelerator (Aug 2021) – a follow-up to a note by Connor McDonald about a recent (19.10) enhancement to internal sequence handling that aims to reduce pressure on the seq$ table. (With a threat warning in as the first comment).
  • Distributed Sequences pt.1 (March 2021) – a critical point about inserting into a local table with a local sequence and a fully remote query.
  • Distributed Sequences pt.2 (June 2021) – a little follow-up to the previous note.
  • Sequence Costs (March 2020) – a bug introduced in 19.3 (fixed by 19.11) when the audit_trail parameter is set.
  • Sequences (March 2007)“how do you tell which column is using this sequence?” That’s not the way it works in Oracle (until several years later and 12c “identities”) but …

A series written for Simpletalk

« Previous PageNext Page »

Website Powered by WordPress.com.