Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

IOT Hash

It’s another of my double-entendre titles. The optimizer can turn a hash join involving an index-organized table into a real performance disaster (though you may have to help it along the way by using a silly definition for your primary key columns). This post was inspired by a question posted on the Oracle Developer Community forum recently so the table and column names I’ve used in my model reflect (almost, with a few corrections) the names used in the post.

We start with a simple requirement expressed through the following SQL:


rem
rem     Script:         iot_hash.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2019
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

insert
        /*+
                qb_name(insert)
        */
into t_iot(
        id, inst_id, nr_time,
        o_time, st, null_col, svname
)
select
        /*+
                qb_name(main)
                unnest(@subq)
                leading(@sel$a93afaed apar@main ob@subq)
                use_hash(@sel$a93afaed ob@subq)
                swap_join_inputs(@sel$a93afaed ob@subq)
                index_ss_asc(@sel$a93afaed ob@subq (t_iot.st t_iot.inst_id t_iot.svname))a
        */
        apar.id,
        'UP',
        to_date('2019-10-24','yyyy-mm-dd'),
        to_date('1969-12-31','yyyy-mm-dd'),
        'IDLE',
        null,
        'tkt007.jj.bb.com'
from
        t_base apar
where
        apar.id not in (
                select
                        /*+
                                qb_name(subq)
                        */
                        id
                from
                        t_iot ob
                where
                        inst_id = 'UP'
        )
and     nvl(apar.gp_nm,'UA') = 'UA'
and     rownum <= 5000
/

The requirement is simple – insert into table t_iot a set of values dictated by a subset of the rows in table t_base if they do not already exist in t_iot. To model the issue that appeared I’ve had to hint the SQL to get the following plan (which I pulled from memory after enabling rowsource execution stats):


---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |      1 |        |   296 (100)|      0 |00:00:00.03 |     788 |    148 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_IOT       |      1 |        |            |      0 |00:00:00.03 |     788 |    148 |       |       |          |
|*  2 |   COUNT STOPKEY          |             |      1 |        |            |    100 |00:00:00.03 |      99 |     91 |       |       |          |
|*  3 |    HASH JOIN RIGHT ANTI  |             |      1 |    100 |   296   (2)|    100 |00:00:00.03 |      99 |     91 |    14M|  1843K|   15M (0)|
|*  4 |     INDEX SKIP SCAN      | T_IOT_STATE |      1 |  12614 |   102   (0)|  10000 |00:00:00.01 |      92 |     91 |       |       |          |
|*  5 |     TABLE ACCESS FULL    | T_BASE      |      1 |    100 |     2   (0)|    100 |00:00:00.01 |       7 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=5000)
   3 - access("APAR"."ID"="ID")
   4 - access("INST_ID"='UP')
       filter("INST_ID"='UP')
   5 - filter(NVL("APAR"."GP_NM",'UA')='UA')

The optimizer has unnested (as hinted) the subquery and converted it to an anti-join using a right hash anti-join. Take a look at the Used-mem for the hash join – would it surprise you to learn that the total size of the (not compressed in any way) IOT, and all its indexes, and the t_base table together total less than 4 MB. Something dramatically awful has happened in the hash join to generated a requirement of 14MB. (In the case of the OP this appeared as an unexpected 5GB written to the temporary tablespace.)

Before I address the source of the high memory usage, take a close look at the Predicate Information, particularly operation 3, and ask yourself what the definition of index t_iot_state might be. The predicate joins t_base.id to t_iot.id, and here’s the code to create both tables and all the indexes.

create table t_iot (
        nr_time         timestamp,
        id              varchar2(1024),
        inst_id         varchar2(200),
        o_time          timestamp,
        st              varchar2(200),
        null_col        varchar2(100),
        svname          varchar2(200),
        constraint t_iot_pk primary key(nr_time, id, inst_id)
)
organization index
/

insert into t_iot
select
        sysdate,
        dbms_random.string('l',10),
        'UP',
        sysdate,
        'IDLE',
        null,
        rpad('x',25,'x')
from
        all_objects
where
        rownum <= 1e4 -- > hint to avoid wordpress format issue
/

create index t_iot_state on t_iot(st, inst_id, svname); 
create index idx2        on t_iot(id, inst_id, svname);

create table t_base(
        id              varchar2(400) not null,
        gp_nm           varchar2(200)
)
/

insert into t_base
select
        dbms_random.string('l',10),
        'UA'
from
        all_objects
where
        rownum <= 100 -- > hint to avoid wordpress format issue
;


begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 't_iot',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 't_base',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/


The index t_iot_state that Oracle has used in the hash join is defined on the columns (st, inst_id, svname) – so the predicate is doing a comparison with a column that’s not in the index! At least, it’s not visibly declared in the index; but this is a secondary index on an IOT, and IOTs don’t have “normal” rowids, the rowid in a secondary index is the value of the primary key (plus a “block guess”). So the columns in the index (even though not declared in the index) are: (st, inst_id, svname, {nr_time, id, inst_id, blockguess}). So this index does supply the required id column.

Side note: you’ll see in the list of columns above that inst_id appears twice. In fact (since Oracle 9, I think) the code to handle secondary indexes has been smart enough to avoid this duplication. If the secondary index contains columns from the primary key then the “rowid” doesn’t store those columns, the code knows how to construct the primaryh key value from the stored PK columns combined with the needed columns from the index entry. This can make IOTs a very nice choice of implementation for “intersection” tables that are used to represent many-to-many joins between two other tables.

Unfortunately this “rowid” is the explanation for the massive memory demand. Take a look at the “Column Projection Information” for my execution plan:


Column Projection Information (identified by operation id):
-----------------------------------------------------------
   2 - "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200], ROWNUM[8]
   3 - (#keys=1) "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200]
   4 - "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "ID"[VARCHAR2,1024], "INST_ID"[VARCHAR2,200], "OB".ROWID[ROWID,1249]
   5 - "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200]

The interesting line is operation 4. A hash join takes the rowsource from its first child (the build table) and creates an in-memory hash table (which may spill to disc, of course), so if I see an unreasonable memory allocation (or unexpected spill to disc) a good starting point is to look at what the first child is supplying. In this case the first child seems to be saying that it’s supplying (or allowing for) nearly 3,700 bytes to be passed up to the hash join.

On closer inspection we can see it’s reporting the “rowid” twice, and also reporting the three columns that make up that rowid. I think it’s reasonable to assume that it’s only supplying the rowid once, and maybe it’s not even supplying the other three columns because they are embedded in the rowid. Doing a quick arithmetic check, let’s multiply the size of the rowid by the value of A-rows: 1,249 * 10,000 = 12,490,000. That’s pretty close to the 14MB reported by the hash join in operation 3.

Hypothesis – to get at the id column, Oracle has used this index (actually a very bad choice of those available) to extract the rowid and then passed the rowid up to the parent in a (length padded) fixed format. Oracle has then created a hash table by extracting the id column from the rowid and building the hash table on it but also carrying the length-padded rowid into the hash table.  Possible variants on this theme are that some or all of the other columns in the Column Projection Information are also passed upwards so that the id doesn’t have to be extracted, but if they are they are not padded to their maximum length.

A simple test that this is broadly the right assumption is to re-run the model making the declared length of the rowid much larger to see what happens to the memory allocation. Changing the inst_id declaration from 200 bytes to 1000 bytes (note the stored value is only the 2 bytes needed for the value ‘UP’) the Used-mem jumps to 23 MB (which is an increment very similar to 800 * 10,000).  You’ll note that I chose to experiment with a column that wasn’t the column used in the join. It was a column in the secondary index definition, though, so another test would be to change the nr_time column from a timestamp (11 bytes) to a large varchar2, so I re-ran the test declaring the nr_time as a varchar2(1000) – reverting the inst_id to varchar2(200) – and the Used-mem increased to 25MB.

Preliminary Conclusion

If Oracle uses the contents of the rowid of a secondary index on an IOT in a join then it constructs a fixed format version for the rowid by padding every column in the primary key to its maximum length and concatenating the results. This can have catastrophic side effects on performance if you’ve declared some very long columns “just in case”. Any time you use index organized tables you should remember to check the Column Projection Information in any execution plans that use secondary indexes in case they are passing a large, padded, primary key through the plan to a point where a blocking operation (such as a hash join or merge join) has to accumulate a large number of rows.

Footnote

In my test case I had to hint the query heavily to force Oracle into the path I wanted to demonstrate.

It’s surprising that the optimizer should have chosen this path in the OP’s system, given that there’s another secondary index that contains the necessary columns in its definition. (So one thought is that there’s a statistics problem to address, or possibly the “good” index is subject to updates that make it become very inefficient (large) very quickly.)

Another oddity of the OP’s system was that Oracle should have chosen to do a right hash anti-join when it looked as if joining the tables in the opposite order would produce a much smaller memory demand and lower cost – there was an explict swap_join_inputs() hint in the Outline Information (so copying the outline into the query and changing that to no_swap_join_inputs() might have been abother viable workaround.) In the end the OP hinted the query to use a nested loop (anti-)join from t_base to t_iot – which is another way to avoid the hash table threat with padded rowids.

 

The Complexity Defense and Other DBA Crimes

No matter how far my career and role has shifted, I will always view myself as a database administrator.  I know this because when I fill out any form asking me what role I fulfill in IT, I still choose “DBA” from the list.  No matter what claims the media and leading sources state, DBAs are an important role, even as technology shifts and traditional database tasks move to the cloud.  Deeper relational database skills are still quite relevant when working in complex technical scenarios.

The Experts

Companies work to ensure they hire the right technical specialists.  They don’t understand database and other deep technology, so they hire someone who does.  The challenge arises when the technologist may fear responsibility for a problem or an incident, so decides to use the technical complexity to disqualify their technical area, such as a database, suspect.

The Problem

I’ve begun to experience this more often since joining Microsoft.  Its more often a scenario where I’m approached by exasperated peers who’ve been attempting to identify an issue in an environment that includes an Oracle database and as the Oracle DBA assumes the Microsoft folks won’t know Oracle, the DBA create a less than truthful explanation of why its not the Oracle database.  The answer they give is often incredibly complex, filled with proprietary jargon, so the Microsoft technologist(s) can do little more than thank the DBA for their time and move on to investigating other tiers in the environment.

The DBA may claim they’ve already cleared the database tier of any impact in the latency issue with their own research. They may refuse to investigate after stating a feature would make it impossible for the database to cause the issue or point fingers at  the application or network layer to distract everyone. Often latency is caused by a combination of issues, (application code vs. database design or database code vs. network bandwidth) but at the center of it all is the database and a mature RDBMS has tools to diagnose a great many issues, no matter the culprit.

Now we all know its no fun when the technology we’re responsible for is the source of contention, but it’s preferable to identify the issue and correct it than to insinuate its not the problem and later on down the road, after significant time wasted, discover that it actually was.  Even worse, what if the problem continues to grow and is never solved?

Not Worth It

The technologist that creates these situations rarely realizes they’ve become a liability to resolving the issue or that they’re the roadblock.  Believing they have saved their technology from being pinpointed, such as an Oracle database in an Azure environment, by making incorrect claims that prove the database’s innocence, really just delays the inevitable.  This is commonly where the Azure team locates someone like me at Microsoft who does know Oracle, (quite well) and has the skills to call the individual’s bluff.

So here’s the deal-  stop viewing the technology as a personal reflection on yourself and realize that it belongs to the company you work for.  Its not worth the loss in trust, time and respect.  I am undeniably persistent about performance data-  reports, traces, so on and so forth, until I can definitively state the database isn’t suspect.  Its only a matter of time before you’re going to have to explain yourself, play dumb or just look foolish.

 

Sincerely,

Your Friendly Azure Data Platform Architect

AKA Previous Oracle/SQL Server Optimization DBA



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [The Complexity Defense and Other DBA Crimes], All Right Reserved. 2019.

DML error logging redux

I posted a nice little feature of DML error logging recently, and a comment came in mentioned that caution is needed with DML error logging because the errors are logged persistently. Hence if you ran a load multiple times, or multiple sessions were utilizing the facility, then the error logging table can quickly become a soup of data that cannot be tracked back to the code your own session was running.

By default this is true, which we can see with a trivial demo. I’ll do the same load of bad rows twice and “forget” to clear out my error table. Here’s my setup – we have a target table called TGT which has rules on nulls and integers being positive, and a source table SRC which has some data that will violate those rules when it comes to loading the data.



SQL> create table tgt ( x int not null, y int, z int check ( z > 0 ));

Table created.

SQL> create table src ( sx int, sy int, sz int );

Table created.

SQL>
SQL> insert into src values (1,10,1);

1 row created.

SQL> insert into src values (2,11,1);

1 row created.

SQL> insert into src values (3,12,-2);

1 row created.

SQL> insert into src values (4,13,1);

1 row created.

SQL> insert into src values (5,14,1);

1 row created.

SQL> insert into src values (null,15,10);

1 row created.

SQL> exec dbms_errlog.create_error_log(dml_table_name=>'tgt')

PL/SQL procedure successfully completed.

SQL> insert into tgt
  2  select * from src
  3  log errors reject limit unlimited;

4 rows created.

SQL>
SQL> select * from err$_tgt
  2  @pr
==============================
ORA_ERR_NUMBER$               : 2290
ORA_ERR_MESG$                 : ORA-02290: check constraint (MCDONAC.SYS_C0086635) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             : 3
Y                             : 12
Z                             : -2
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("MCDONAC"."TGT"."X")

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             :
Y                             : 15
Z                             : 10

PL/SQL procedure successfully completed.

That is fine so far, but if I run the load from multiple sessions, then it is no longer apparent which session did what activities. When session 2 runs the same code, I have 4 errors but I cannot track the session information



SQL> insert into tgt
  2  select * from src
  3  log errors reject limit unlimited;

4 rows created.

SQL>
SQL> select * from err$_tgt
  2  @pr
==============================
ORA_ERR_NUMBER$               : 2290
ORA_ERR_MESG$                 : ORA-02290: check constraint (MCDONAC.SYS_C0086635) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             : 3
Y                             : 12
Z                             : -2
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("MCDONAC"."TGT"."X")

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             :
Y                             : 15
Z                             : 10
==============================
ORA_ERR_NUMBER$               : 2290
ORA_ERR_MESG$                 : ORA-02290: check constraint (MCDONAC.SYS_C0086635) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             : 3
Y                             : 12
Z                             : -2
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("MCDONAC"."TGT"."X")

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             :
Y                             : 15
Z                             : 10

PL/SQL procedure successfully completed.

However, the error logging is just a standard database table, so as long as you do not interfere with the columns required to support error logging, you can augment the table to capture readily available metadata.



SQL> delete err$_tgt;

4 rows deleted.

SQL> delete tgt;

8 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> alter table err$_tgt add session_id  int
  2    default sys_context('USERENV','SID');

Table altered.

SQL> alter table err$_tgt add client_host varchar2(100)
  2    default sys_context('USERENV','HOST');

Table altered.

SQL> alter table err$_tgt add osuser      varchar2(100)
  2    default sys_context('USERENV','OS_USER');

Table altered.

SQL>
SQL> delete tgt;

0 rows deleted.

SQL> insert into tgt
  2  select * from src
  3  log errors reject limit unlimited;

4 rows created.

SQL>
SQL> select * from err$_tgt
  2  @pr
==============================
ORA_ERR_NUMBER$               : 2290
ORA_ERR_MESG$                 : ORA-02290: check constraint (MCDONAC.SYS_C0086635) violated

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             : 3
Y                             : 12
Z                             : -2
SESSION_ID                    : 369
CLIENT_HOST                   : WORKGROUP\GTX
OSUSER                        : GTX\hamcdc
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("MCDONAC"."TGT"."X")

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
X                             :
Y                             : 15
Z                             : 10
SESSION_ID                    : 369
CLIENT_HOST                   : WORKGROUP\GTX
OSUSER                        : GTX\hamcdc

PL/SQL procedure successfully completed.

Of course the big question here is – is this approach supported? After all, typically we do not endorse people tinkering with internally/automatically created tables. As far as I can tell, you are “almost” covered here Smile. In the Database Administrators manual it states:

If the error logging table contains a column with a name that does not match a DML table column, the column is ignored.

So we allowed to add columns without that being a violation. However, whilst experimental observation suggests that the columns with the DEFAULT clause do indeed preserve their default values, this is not guaranteed by the documentation. For example, it would be possible one day in the future for the internal operation that populates the error logging table to force null into those columns. I’m not saying that will happen, but just that Oracle would be within its rights to do so. If you’re OK with sailing close to the wind in terms of the definition of “supported”, you can use the DEFAULT clause above, or Steven Feuerstein has put together some nice helper utilities for DML error logging. You can find them on LiveSQL here:

https://livesql.oracle.com/apex/livesql/file/content_CTMX9U1B173HA6PZZN9B6JK3T.html

https://livesql.oracle.com/apex/livesql/file/content_CTS18A4BCWHLNR9X1OC3DR6CF.html

If that is a concern, then an alternative that is covered in the docs is using a nominated tag when logging the error, ie:

LOG ERRORS INTO my_table  (‘some tag’)

and hence you could use a concatenation such as:


sys_context('USERENV','SID')    ||';'||
sys_context('USERENV','HOST')   ||';'||
sys_context('USERENV','OS_USER')||';'||

for your tag to capture the metadata you desire. In either instance, you can indeed track additional metadata to help debugging when using DML error logging.

(Shout out to Sergiusz Wolicki for providing extra information for this post)

What’s new with Oracle database 18.8 versus 18.7

The amount of changes between version 18.7 and 18.8 is truly minimal. There’s one spare parameter renamed to an underscore parameter, which seems to be a back port of functionality created version 19:

DATABASE_VERSION                                                                                     NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------
18.8                                                                                                 _ldap_password_oneway_auth
19.2                                                                                                 _ldap_password_oneway_auth
19.3                                                                                                 _ldap_password_oneway_auth
19.4                                                                                                 _ldap_password_oneway_auth
19.5                                                                                                 _ldap_password_oneway_auth

And it seems the DBA view DBA_REGISTRY_BACKPORTS is corrupted with 18.8, there only is a synonym with that name, which throws an ORA 1775: looping chain of synonyms when used. This is the reason it shows as gone in 18.8 (unique to version 18.7).

parameters unique in version 18.7 versus 18.8

NAME
--------------------------------------------------
_one-hundred-and-forty-sixth_spare_parameter

parameters unique in version 18.8 versus 18.7

NAME
--------------------------------------------------
_ldap_password_oneway_auth

parameter values changed isdefault between 18.7 versus 18.8

parameter values unique to 18.7 versus 18.8

parameter values unique to 18.8 versus 18.7

waitevents unique in version 18.7 versus 18.8

waitevents unique in version 18.8 versus 18.7

waitevents changed parameter description between 18.7 versus 18.8

x$ tables unique to 18.7 versus 18.8

x$ tables unique to 18.8 versus 18.7

x$ tables columns unique to 18.7 versus 18.8

x$ tables columns unique to 18.8 versus 18.7

v$ tables unique to 18.7 versus 18.8

v$ tables unique to 18.8 versus 18.7

v$ tables columns unique to 18.7 versus 18.8

v$ tables columns unique to 18.8 versus 18.7

gv$ tables unique to 18.7 versus 18.8

gv$ tables unique to 18.8 versus 18.7

gv$ tables columns unique to 18.7 versus 18.8

gv$ tables columns unique to 18.8 versus 18.7

sysstat statistics unique to 18.7 versus 18.8

sysstat statistics unique to 18.8 versus 18.7

sys_time_model statistics unique to 18.7 versus 18.8

sys_time_model statistics unique to 18.8 versus 18.7

dba tables unique to 18.7 versus 18.8

NAME
----------------------------------------------------------------------------------------------------
DBA_REGISTRY_BACKPORTS

dba tables unique to 18.8 versus 18.7

dba tables columns unique to 18.8 versus 18.7

dba tables columns unique to 18.7 versus 18.8

cdb tables unique to 18.7 versus 18.8

NAME
----------------------------------------------------------------------------------------------------
CDB_REGISTRY_BACKPORTS

cdb tables unique to 18.8 versus 18.7

cdb tables column unique to 18.7 versus 18.8

cdb tables column unique to 18.8 versus 18.7

And here are the differences in symbols (c functions).
The amount of functions added and removed is not huge this time (remember last time littecms was added).
The same java/domain functions as seen with version 19 have been removed, and the same PLSQL codegen layer functions, plus a singe DBMS_SODA function. There are some other functions that do not align with 19 removed.

The same batch of DBMS_SODA related functions are added, plus a lot of functions different than version 19.

code symbol names unique in version 18.7 versus 18.8

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
joncl_at_domains                                             (jon)cl_at_domains                                           java ??
joncl_domains                                                (jon)cl_domains                                              java ??
joncl_set_domains                                            (jon)cl_set_domains                                          java ??
joncl_snor_domains                                           (jon)cl_snor_domains                                         java ??
joniul_at_domains                                            (jon)iul_at_domains                                          java ??
joniul_domains                                               (jon)iul_domains                                             java ??
joniul_set_domains                                           (jon)iul_set_domains                                         java ??
joniul_snor_domains                                          (jon)iul_snor_domains                                        java ??
jonndl_at_domains                                            (jon)ndl_at_domains                                          java ??
jonndl_domains                                               (jon)ndl_domains                                             java ??
jonndl_set_domains                                           (jon)ndl_set_domains                                         java ??
jonndl_snor_domains                                          (jon)ndl_snor_domains                                        java ??
jonoce_at_domains                                            (jon)oce_at_domains                                          java ??
jonoce_domains                                               (jon)oce_domains                                             java ??
jonoce_set_domains                                           (jon)oce_set_domains                                         java ??
jonoce_snor_domains                                          (jon)oce_snor_domains                                        java ??
jonosd_at_domains                                            (jon)osd_at_domains                                          java ??
jonosd_domains                                               (jon)osd_domains                                             java ??
jonosd_set_domains                                           (jon)osd_set_domains                                         java ??
jonosd_snor_domains                                          (jon)osd_snor_domains                                        java ??
jonvcl_at_domains                                            (jon)vcl_at_domains                                          java ??
jonvcl_domains                                               (jon)vcl_domains                                             java ??
jonvcl_set_domains                                           (jon)vcl_set_domains                                         java ??
jonvcl_snor_domains                                          (jon)vcl_snor_domains                                        java ??
kestsInitTCB                                                 (kest)sInitTCB                                               kernel event SQL manageability tokenize (?) ??
kglsim_get_bucket_info                                       (kglsim)_get_bucket_info                                     kernel generic library cache management simulator (library cache advise)  ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
pdzdM60_Make_RBST                                            (pdz)dM60_Make_RBST                                          PLSQL code execution codegen infrastructure ??
pdzdM66_Insert_At_Root                                       (pdz)dM66_Insert_At_Root                                     PLSQL code execution codegen infrastructure ??
qcsoPreNameres_ProcOpt                                       (qcso)PreNameres_ProcOpt                                     query compile semantic analysis (parser) OBJECTS ??
qsodasqlGetInsertOneSQL                                      (qsoda)sqlGetInsertOneSQL                                    query system DBMS_SODA ??

code symbol names unique in version 18.8 versus 18.7

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
audIsSenCol                                                  (aud)IsSenCol                                                audit ??
audOptTab                                                    (aud)OptTab                                                  audit ??
audstwlk                                                     (aud)stwlk                                                   audit ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kclcsb                                                       (kcl)csb                                                     kernel cache lock manager/buffer cache ??
kds_update_turbo_scan_pivot_statistics                       (kds)_update_turbo_scan_pivot_statistics                     kernel data seek/scan ??
kesdtcbResetDiagEvent                                        (kes)dtcbResetDiagEvent                                      kernel event SQL manageability ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
kfdp_chkSufOlnFGAvl                                          (kfdp)_chkSufOlnFGAvl                                        kernel automatic storage management disk PST ??
kfioSrMsgBuf_ack                                             (kfio)SrMsgBuf_ack                                           kernel automatic storage management translation I/O layer ??
kfkcrRefresh                                                 (kfk)crRefresh                                               kernel automatic storage management KFK ??
kfnFreeKfnpnmMem                                             (kfn)FreeKfnpnmMem                                           kernel automatic storage management networking subsystem ??
kgfvTermSkvol                                                (kgf)vTermSkvol                                              kernel generic ASM ??
kjznhcdn                                                     (kjzn)hcdn                                                   kernel lock management DIAG node layer ??
kjznhcds                                                     (kjzn)hcds                                                   kernel lock management DIAG node layer ??
kkopinlpInt                                                  (kko)pinlpInt                                                kernel compile optimizer ??
kkquIsCorrTransInList                                        (kkqu)IsCorrTransInList                                      kernel compile query  subquery unnesting ??
knlpmaxfmsgsize                                              (knl)pmaxfmsgsize                                            kernel replication streams replication ??
kpuxsoCurPrefetchFree                                        (kpu)xsoCurPrefetchFree                                      kernel programmatic interface user ??
kpuxsoDocContentSet                                          (kpu)xsoDocContentSet                                        kernel programmatic interface user ??
kpuxsoOperInit                                               (kpu)xsoOperInit                                             kernel programmatic interface user ??
krb_devtype_chk                                              (krb)_devtype_chk                                            kernel redo backup/restore ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrsr_SkipRedo                                             (krvx)rsr_SkipRedo                                           kernel redo recovery extract ??
kzan_set_ddl_lock_timeout                                    (kza)n_set_ddl_lock_timeout                                  kernel security audit  ??
kzckmWalletInArchive                                         (kzck)mWalletInArchive                                       kernel security Code-Based Access Control encryption support ??
kzckmrOpenRootOnPlug                                         (kzck)mrOpenRootOnPlug                                       kernel security Code-Based Access Control encryption support ??
kzckmrXmlVldTDEKeys                                          (kzck)mrXmlVldTDEKeys                                        kernel security Code-Based Access Control encryption support ??
kzckmrXmlVldTDEKeysFile                                      (kzck)mrXmlVldTDEKeysFile                                    kernel security Code-Based Access Control encryption support ??
kzckmrXmlVldTDEMetaHSM                                       (kzck)mrXmlVldTDEMetaHSM                                     kernel security Code-Based Access Control encryption support ??
pfrcrt                                                       (pfr)crt                                                     PLSQL Code Execution runtime ??
qsodasqlGetContentDefine                                     (qsoda)sqlGetContentDefine                                   query system DBMS_SODA ??
qsodasqlGetCreatedOnDefine                                   (qsoda)sqlGetCreatedOnDefine                                 query system DBMS_SODA ??
qsodasqlGetInsertSQL                                         (qsoda)sqlGetInsertSQL                                       query system DBMS_SODA ??
qsodasqlGetKeyDefine                                         (qsoda)sqlGetKeyDefine                                       query system DBMS_SODA ??
qsodasqlGetLastModDefine                                     (qsoda)sqlGetLastModDefine                                   query system DBMS_SODA ??
qsodasqlGetMediaDefine                                       (qsoda)sqlGetMediaDefine                                     query system DBMS_SODA ??
qsodasqlGetVersionDefine                                     (qsoda)sqlGetVersionDefine                                   query system DBMS_SODA ??
qsodasqlPrefetchPrepare                                      (qsoda)sqlPrefetchPrepare                                    query system DBMS_SODA ??
qsodasqlSelectDocFieldsSQL                                   (qsoda)sqlSelectDocFieldsSQL                                 query system DBMS_SODA ??
qsodaxArrayFetchDocuments                                    (qsoda)xArrayFetchDocuments                                  query system DBMS_SODA ??
qsodaxDefineDocumentFields                                   (qsoda)xDefineDocumentFields                                 query system DBMS_SODA ??
qsodaxGetPrefetchDocument                                    (qsoda)xGetPrefetchDocument                                  query system DBMS_SODA ??
slmeset_s                                                    (sl)meset_s                                                  operating system (dependent) library ??

Strange Estimates.

A question came up on the Oracle-L list server a couple of days ago expressing some surprise at the following execution plan:


--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |       |       |   845 (100)|          |
|   1 |  SORT AGGREGATE                      |                 |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ANY_TABLE       | 84827 |   579K|   845   (1)| 00:00:01 |
|   3 |    SORT CLUSTER BY ROWID             |                 | 68418 |       |    76   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                 | ANY_INDEX       | 68418 |       |    76   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"."ANY_COLUMN1"='J')
   4 - access("X"."ANY_COLUMN2"=89155)

You’ll notice that this is a very simple query accessing a table by index, yet the estimated table rows found exceeds the estimated number of index entries used to probe the table. How can this happen. The answer (most frequently) is that there’s a mismatch between the table (or, more commonly, column) statistics and the index statistics. This seems to happen very frequently when you start mixing partitioned tables with global (or globally partitioned) indexes but it can happen in very simple cases, especially since a call to gather_table_stats() with cascade set to true and using the auto_sample_size will take a small sample from the index while using a 100% “sample” from the table.

Here’s an example I engineered very quickly to demonstrate the point. There’s no particular reason for the choice of DML I’ve used on the data beyond a rough idea of setting up a percentage of nulls and deleting a non-uniform pattern of rows.


rem
rem     Script:         table_index_mismatch.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2019
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem
create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        mod(rownum,1000)                n1,
        mod(rownum,1000)                n2,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1, for columns (n1,n2) size 1'
        );
end;
/

create index t1_i1 on t1(n1);

delete from t1 where mod(trunc(sqrt(n1)),7) = 0;
update t1 set n1 = null where mod(n1,10) = 0;
delete from t1 where mod(n1,10) = trunc(dbms_random.value(0,10));

execute dbms_stats.gather_table_stats(user,'t1',estimate_percent=>1)
execute dbms_stats.gather_index_stats(null,'t1_i1',estimate_percent=> 0.01)

Of course you’re not supposed to collect stats with arbitrary samples in any recent version of Oracle, so going for a 1% and 0.01% sample seems a little daft but I’m just doing that to demonstrate the problem with a very small data set.

After generating the data and gathering the stats I ran a few queries to pick out some critical numbers.


select
        table_name, sample_size, num_rows
from
        user_tables
where
        table_name = 'T1'
/

select 
        index_name, sample_size, num_rows, distinct_keys
from
        user_indexes
where
        table_name = 'T1'
and     index_name = 'T1_I1'
/

select
        column_name, sample_size, num_nulls, num_distinct
from
        user_tab_cols
where
        table_name = 'T1'
and     (
            column_name = 'N1'
         or virtual_column = 'YES'
        )
order by
        column_name
/

You’ll notice that I’ve only picked one of my original columns and any virtual columns. My gather_table_stats() call had a method_opt that included the creation of extended stats for the column group (n1, n2) and I want to report the stats on the resulting virtual column.


TABLE_NAME           SAMPLE_SIZE   NUM_ROWS
-------------------- ----------- ----------
T1                          7865     786500


INDEX_NAME           SAMPLE_SIZE   NUM_ROWS DISTINCT_KEYS
-------------------- ----------- ---------- -------------
T1_I1                     385779     713292           714


COLUMN_NAME                      SAMPLE_SIZE  NUM_NULLS NUM_DISTINCT
-------------------------------- ----------- ---------- ------------
N1                                      7012      85300          771
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS          7865          0          855

A couple of observations on the stats

  • the table sample size is, as expected, 1% of the reported num_rows (the actual count is 778,154).
  • The index sample size is much bigger than expected – but that’s probably related to the normal “select 1,100 leaf blocks strategy”. Because of the skew in the pattern of deleted values it’s possible for the sample size in this model to vary between 694,154 and something in the region of 380,000.
  • The n1 sample size is about 10% smaller than the table sample size – but that’s because I set 10% of the column to null.
  • The column group sample size matches the table sample size because column group hash values are never null, even if an underlying column is null.

So let’s check the execution plan for a very simple query:


set autotrace on explain
select id from t1 where n1 = 140 and n2 = 140;
set autotrace off


---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |   920 | 11960 |   918   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |   920 | 11960 |   918   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |   909 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N2"=140)
   2 - access("N1"=140)

The estimate for relevant index rowids is smaller than the estimate for the number of table rows! The difference is not as extreme as the case reported on Oracle-l, but I’m only trying to demonstrate a principle, not reproduce the exact results.

There are several ways in which contradictory results like this can appear – but in this case we can see the following:

  • For the table access table.num_rows/column.num_distinct = 786,500 / 855 = 919.88  (using the column group num_distinct)
  • For the index range scan: (table.num_rows – column.num_nulls) / column.num_distinct = (786500 – 85300) / 771 = 909.47 (using the n1 statistics)

So the change in strategy as it becomes possible for the optimizer to take advantage of the column group means the index and table have been using incompatible sets of stats (in particular there’s that loss of information about NULLs) as their cardinalities are calculated. The question, then, is “how much is that likely to matter”, and the follow-up if it can matter is “in what circumstancs could the effect be large enough to cause problems”. But that’s a topic for another day.

Update / Footnote

In the case of the Oracle-l example, there was no column group, and in some cases the optimizer would produce a plan where the table estimate was much smaller than the index estimate, and in other cases (like the opening plan above) the table estimate was signficantly greater than the index estimate. This was a side effect of adaptive statistics: the low table estimate was due to the basic “multiply separate selectivities”; but the with adaptive statistics enabled Oracle started sampling the table to check the correlation between the two tables, and then produced an SQL Plan Directive to do so and got to the higher (and correct) result.

 

 

Changing Your Bash Shell Prompt and Colors

I enjoyed the opportunity to test out my PASS Summit shell scripting session at SQL Saturday Denver.  It was the first run on it and Glenn Berry sat in my session.  Afterwards, this wise and long-standing member of the community had a very valuable piece of constructive criticism-  change the background on my bash terminal for Azure Cloud Shell, as the black background and colors could be troublesome for some attendees.

https://dbakevlar.com/wp-content/uploads/2019/10/cloudshell1-300x238.png 300w, https://dbakevlar.com/wp-content/uploads/2019/10/cloudshell1-768x610.png 768w, https://dbakevlar.com/wp-content/uploads/2019/10/cloudshell1.png 1216w" sizes="(max-width: 800px) 100vw, 800px" />

I agree with him-  you never know what the projector quality will be, the lighting in the room, color-blind attendees or other factors that could impact the readability of  the demonstration when you have a black background and colored text.  I realized, as the Azure Cloud Shell is a service, we have less control of the terminal offered to us, so it was important to tell people how to update their Azure Cloud Shell to change the execution prompt to not be in color and highlight the background in white, with black text bolded for easier reading.

Changing the Azure Cloud Shell Run Commands

Log into your Azure Cloud Shell using the BASH shell interface, (not Powershell, this isn’t the session for that! :))

We’ll need to edit the bash run commands file, also known as .bashrc.

view .bashrc

Using VIM commands on your keyboard, use the “j” key to go down till you see the following lines and use the “#” sign to comment out the lines you see I’ve commented out below.  Replace the current PS1 line, (alias for your execution line) with the one I’ve added, which will update the following:

  • Change the prompt to be your username
  • Change the background of the text on that line to white
  • Remove color and change the text to BOLDED black text for prompt
#if [ -n "$force_color_prompt" ]; then
#    if [ -x /usr/bin/tput ] && tput setaf 1 >&/dev/null; then
        # We have color support; assume it's compliant with Ecma-48
        # (ISO/IEC-6429). (Lack of such support is extremely rare, and such
        # a case would tend to support setf rather than setaf.)
#       color_prompt=yes
#    else
#       color_prompt=
#    fi
#fi

#if [ "$color_prompt" = yes ]; then
#    PS1='${debian_chroot:+($debian_chroot)}\[\033[01;32m\]\u@\h\[\033[00m\]:\[\033[01;34m\]\w\[\033[00m
\]\$ '
#else
#    PS1='${debian_chroot:+($debian_chroot)}\u@\h:\w\$ '
#    Want to have it bold instead
     PS1="\[\033[47m\]\[\033[1;30m\]\u:\w\$ "
#fi
#unset color_prompt force_color_prompt
Save the file, (ESC :wq) and then when you type in the terminal, you’ll notice that the text is now inside a white background and is black, bolded text and should look like the following:

Create a Profile

If you only want to change this for when you’re presenting, you could create a .profile_p file, add the following:
export PS1="\[\033[47m\]\[\033[1;30m\]\u:\w\$ "
Save the file and execute it after logging in:
~/.profile_p
If you’re trying to decipher what is in the command, there is the jist:
  • PS1= the first prompt line
  • 33= textsize
  • 47m= white background
  • 1;30m=bold; black text, (and the 1 is not bold for every Linux distribution, so if you experience something different, lucky you, you get to look up what it is for your “flavor”!)
  • u=username
You could also add this to a profile for a particular demo script that is run as part of a presentation or for a set profile, but you get the idea.  This will then change it just for the session instead of every time you log in.
Enjoy!



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Changing Your Bash Shell Prompt and Colors], All Right Reserved. 2019.

What’s new with Oracle database 19.5 versus 19.4

It seems that the most eye-catching difference between Oracle database versions 19.5 and 19.4 is three underscore parameters spare parameters being changed to specifically named underscore parameters, two of them have a bug number in them which is not publicly visible.

In v$sysstat/v$sesstat, a group of statistics are renamed from ‘OS %’ to ‘Server %’. All these statistics are about networking. One changed parameter directly points to networking (tcpinfo). These statistics are added in version 19.

One DBA view was added: DBA_DV_COMMON_OPERATION_STATUS. This view is specific and owned by to DVSYS.

NAME
--------------------------------------------------
_fourth_spare_parameter
_one-hundred-and-fifty-third_spare_parameter
_one-hundred-and-forty-sixth_spare_parameter

parameters unique in version 19.5 versus 19.4

NAME
--------------------------------------------------
_bug29302220_tcpinfo_statistics_save_atexit
_bug29903454_ksws_enable_alb
_session_modp_list

parameter values changed isdefault between 19.4 versus 19.5

parameter values unique to 19.4 versus 19.5

parameter values unique to 19.5 versus 19.4

waitevents unique in version 19.4 versus 19.5

waitevents unique in version 19.5 versus 19.4

waitevents changed parameter description between 19.4 versus 19.5

x$ tables unique to 19.4 versus 19.5

x$ tables unique to 19.5 versus 19.4

x$ tables columns unique to 19.4 versus 19.5

x$ tables columns unique to 19.5 versus 19.4

v$ tables unique to 19.4 versus 19.5

v$ tables unique to 19.5 versus 19.4

v$ tables columns unique to 19.4 versus 19.5

v$ tables columns unique to 19.5 versus 19.4

gv$ tables unique to 19.4 versus 19.5

gv$ tables unique to 19.5 versus 19.4

gv$ tables columns unique to 19.4 versus 19.5

gv$ tables columns unique to 19.5 versus 19.4

sysstat statistics unique to 19.4 versus 19.5

NAME
----------------------------------------------------------------------------------------------------
OS Advertised Receive Window
OS Advertised Send Window
OS Data Segments In
OS Data Segments Out
OS Lost Packets
OS Path Maximum Transmission Unit(MTU)
OS Send Congestion Window
OS Time (usec) Busy Sending Data
OS Time (usec) Busy Sending Data under Congestion
OS Time (usec) Last Ack Received
OS Time (usec) Last Ack Sent
OS Time (usec) Last Data Received
OS Time (usec) Last Data Sent
OS Time (usec) Limited by Receive Window
OS Time (usec) Limited by Send Buffer
OS Time (usec) Round Trip Time
OS Time (usec) Round Trip Time Variance
OS Total Bytes Acked
OS Total Bytes Received
OS Total Number of Retransmitted Packets

sysstat statistics unique to 19.5 versus 19.4

NAME
----------------------------------------------------------------------------------------------------
Server Advertised Receive Window
Server Advertised Send Window
Server Data Segments In
Server Data Segments Out
Server Elapsed Time (msec) Last Ack Received
Server Elapsed Time (msec) Last Ack Sent
Server Elapsed Time (msec) Last Data Received
Server Elapsed Time (msec) Last Data Sent
Server Lost Packets
Server Path Maximum Transmission Unit(MTU)
Server Send Congestion Window
Server Time (usec) Approximate Elapsed Time from the packet dro
Server Time (usec) Busy Sending Data
Server Time (usec) Limited by Receive Window
Server Time (usec) Limited by Send Buffer
Server Time (usec) Round Trip Time
Server Time (usec) Round Trip Time Variance
Server Total Bytes Acked
Server Total Bytes Received
Server Total Number of Retransmitted Packets

sys_time_model statistics unique to 19.4 versus 19.5

sys_time_model statistics unique to 19.5 versus 19.4

dba tables unique to 19.4 versus 19.5

dba tables unique to 19.5 versus 19.4

NAME
----------------------------------------------------------------------------------------------------
DBA_DV_COMMON_OPERATION_STATUS

dba tables columns unique to 19.5 versus 19.4

dba tables columns unique to 19.4 versus 19.5

cdb tables unique to 19.4 versus 19.5

cdb tables unique to 19.5 versus 19.4

cdb tables column unique to 19.4 versus 19.5

cdb tables column unique to 19.5 versus 19.4

And here are the differences in symbols (c functions).
The functions unique to 19.4 versus 19.5 (alias removed) are partly jon thus java related functions, which all have ‘domain’ in the name. Plus some diverse functions.

There is a significant amount number of functions added. The first ones starting with ‘aux_’ and ‘decode_krb5’ might have something to do with ‘krb5’, alias Kerberos version 5.

There is a ‘dbnest_dir_check’ function in between ‘aux’ and ‘decode’. It seems ‘dbnest’ is an entire layer in the database code at least, however I do not know if it relates to something that is public knowledge.

Among all kinds of singular added functions, what further is noticeable, is a high number of ASM related functions being added, of which a lot of them refer to ‘oss’ (oracle storage server?).

Additionally, a bunch of ‘ksp_modp’ functions are added which might have something to do with the ‘_session_modp_list’ parameter, and a bunch of parameters starting with ‘ksws_alb’ which might have something to do with the ‘_bug29903454_ksws_enable_alb’ parameter.

Further a group of functions starting with ‘soda’, which might be related to DBMS_SODA: a package implementing Simple Oracle Document Access (SODA), for managing document collections using PL/SQL.

code symbol names unique in version 19.4 versus 19.5

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
drexogidx                                                    (drex)ogidx                                                  text index query ??
joncl_at_domains                                             (jon)cl_at_domains                                           java ??
joncl_domains                                                (jon)cl_domains                                              java ??
joncl_set_domains                                            (jon)cl_set_domains                                          java ??
joncl_snor_domains                                           (jon)cl_snor_domains                                         java ??
joniul_at_domains                                            (jon)iul_at_domains                                          java ??
joniul_domains                                               (jon)iul_domains                                             java ??
joniul_set_domains                                           (jon)iul_set_domains                                         java ??
joniul_snor_domains                                          (jon)iul_snor_domains                                        java ??
jonndl_at_domains                                            (jon)ndl_at_domains                                          java ??
jonndl_domains                                               (jon)ndl_domains                                             java ??
jonndl_set_domains                                           (jon)ndl_set_domains                                         java ??
jonndl_snor_domains                                          (jon)ndl_snor_domains                                        java ??
jonoce_at_domains                                            (jon)oce_at_domains                                          java ??
jonoce_domains                                               (jon)oce_domains                                             java ??
jonoce_set_domains                                           (jon)oce_set_domains                                         java ??
jonoce_snor_domains                                          (jon)oce_snor_domains                                        java ??
jonosd_at_domains                                            (jon)osd_at_domains                                          java ??
jonosd_domains                                               (jon)osd_domains                                             java ??
jonosd_set_domains                                           (jon)osd_set_domains                                         java ??
jonosd_snor_domains                                          (jon)osd_snor_domains                                        java ??
jonvcl_at_domains                                            (jon)vcl_at_domains                                          java ??
jonvcl_domains                                               (jon)vcl_domains                                             java ??
jonvcl_set_domains                                           (jon)vcl_set_domains                                         java ??
jonvcl_snor_domains                                          (jon)vcl_snor_domains                                        java ??
jsv_try_scan_3choice                                         (js)v_try_scan_3choice                                       job scheduing ??
kcbtse_raise_tbs_datafile_enc_mismatch_error                 (kcbtse)_raise_tbs_datafile_enc_mismatch_error               kernel cache buffers tablespace encryption ??
kffgAttrMaxPxCb                                              (kffg)AttrMaxPxCb                                            kernel automatic storage management KFF file group ??
kffgVolClientCheck                                           (kffg)VolClientCheck                                         kernel automatic storage management KFF file group ??
kgiflini                                                     (kgi)flini                                                   kernel generic instantiation manager ??
kgiflpop                                                     (kgi)flpop                                                   kernel generic instantiation manager ??
kjgcr_GetTopCPU                                              (kj)gcr_GetTopCPU                                            kernel lock management ??
kjgcr_RunSyncTask                                            (kj)gcr_RunSyncTask                                          kernel lock management ??
kjgcr_SlaveReqGetSlot                                        (kj)gcr_SlaveReqGetSlot                                      kernel lock management ??
kkdlpGetObjn                                                 (kkdl)pGetObjn                                               kernel compile dictionary lookup ??
kkjcjchk                                                     (kkj)cjchk                                                   kernel compile job queue ??
kkoarFreeCtx                                                 (kkoar)FreeCtx                                               kernel compile optimizer automatic (sql) reoptimisation ??
kkoarFreeStats                                               (kkoar)FreeStats                                             kernel compile optimizer automatic (sql) reoptimisation ??
kkqojeFroNoRid                                               (kkqoj)eFroNoRid                                             kernel compile query  outer join ??
kkquIsCorrTransInList                                        (kkqu)IsCorrTransInList                                      kernel compile query  subquery unnesting ??
kslsesftcb_int                                               (ksl)sesftcb_int                                             kernel service (VOS) latching and post-wait ??
ktcsnaInitMinActScnInEnv                                     (ktc)snaInitMinActScnInEnv                                   kernel transaction control component ??
nsmplxhash                                                   (ns)mplxhash                                                 network session ??
pdy7F89_Create_Frame_Descriptor                              (pdy)7F89_Create_Frame_Descriptor                            PLSQL code execution codegen MCode gen ??
pdzdM60_Make_RBST                                            (pdz)dM60_Make_RBST                                          PLSQL code execution codegen infrastructure ??
pdzdM66_Insert_At_Root                                       (pdz)dM66_Insert_At_Root                                     PLSQL code execution codegen infrastructure ??
pdzdM67_Create                                               (pdz)dM67_Create                                             PLSQL code execution codegen infrastructure ??
qerocnStart                                                  (qeroc)nStart                                                query execute rowsource object collection iterator ??
qesmmCUpdProfile                                             (qesmm)CUpdProfile                                           query execute services memory manager (pga) ??
qosdMonNonCGCol                                              (qosd)MonNonCGCol                                            query optimizer statistics sql plan directive ??
qsodasqlGetInsertOneSQL                                      (qsoda)sqlGetInsertOneSQL                                    query system DBMS_SODA ??

code symbol names unique in version 19.5 versus 19.4

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
aux_loadptr_external_principal_identifier_ptr                aux_loadptr_external_principal_identifier_ptr                ??
aux_loadptr_pkinit_krb5_principal_name                       aux_loadptr_pkinit_krb5_principal_name                       ??
aux_loadptr_ptr_kdf_alg_id                                   aux_loadptr_ptr_kdf_alg_id                                   ??
aux_loadptr_ptr_seqof_external_principal_identifier          aux_loadptr_ptr_seqof_external_principal_identifier          ??
aux_loadptr_ptr_supported_kdfs                               aux_loadptr_ptr_supported_kdfs                               ??
aux_loadptr_subject_pk_info_ptr                              aux_loadptr_subject_pk_info_ptr                              ??
aux_present_opt_ptr_kdf_alg_id                               aux_present_opt_ptr_kdf_alg_id                               ??
aux_present_opt_ptr_seqof_external_principal_identifier      aux_present_opt_ptr_seqof_external_principal_identifier      ??
aux_present_opt_ptr_supported_kdfs                           aux_present_opt_ptr_supported_kdfs                           ??
aux_present_opt_subject_pk_info_ptr                          aux_present_opt_subject_pk_info_ptr                          ??
aux_storeptr_external_principal_identifier_ptr               aux_storeptr_external_principal_identifier_ptr               ??
aux_storeptr_pkinit_krb5_principal_name                      aux_storeptr_pkinit_krb5_principal_name                      ??
aux_storeptr_ptr_kdf_alg_id                                  aux_storeptr_ptr_kdf_alg_id                                  ??
aux_storeptr_ptr_seqof_external_principal_identifier         aux_storeptr_ptr_seqof_external_principal_identifier         ??
aux_storeptr_ptr_supported_kdfs                              aux_storeptr_ptr_supported_kdfs                              ??
aux_storeptr_subject_pk_info_ptr                             aux_storeptr_subject_pk_info_ptr                             ??
dbnest_dir_check                                             dbnest_dir_check                                             ??
decode_krb5_auth_pack                                        decode_krb5_auth_pack                                        ??
decode_krb5_auth_pack_draft9                                 decode_krb5_auth_pack_draft9                                 ??
decode_krb5_kdc_dh_key_info                                  decode_krb5_kdc_dh_key_info                                  ??
decode_krb5_pa_pk_as_rep                                     decode_krb5_pa_pk_as_rep                                     ??
decode_krb5_pa_pk_as_req                                     decode_krb5_pa_pk_as_req                                     ??
decode_krb5_pa_pk_as_req_draft9                              decode_krb5_pa_pk_as_req_draft9                              ??
decode_krb5_principal_name                                   decode_krb5_principal_name                                   ??
decode_krb5_reply_key_pack                                   decode_krb5_reply_key_pack                                   ??
decode_krb5_reply_key_pack_draft9                            decode_krb5_reply_key_pack_draft9                            ??
decode_krb5_td_dh_parameters                                 decode_krb5_td_dh_parameters                                 ??
decode_krb5_td_trusted_certifiers                            decode_krb5_td_trusted_certifiers                            ??
encode_krb5_auth_pack                                        encode_krb5_auth_pack                                        ??
encode_krb5_auth_pack_draft9                                 encode_krb5_auth_pack_draft9                                 ??
encode_krb5_kdc_dh_key_info                                  encode_krb5_kdc_dh_key_info                                  ??
encode_krb5_pa_pk_as_rep                                     encode_krb5_pa_pk_as_rep                                     ??
encode_krb5_pa_pk_as_rep_draft9                              encode_krb5_pa_pk_as_rep_draft9                              ??
encode_krb5_pa_pk_as_req                                     encode_krb5_pa_pk_as_req                                     ??
encode_krb5_pa_pk_as_req_draft9                              encode_krb5_pa_pk_as_req_draft9                              ??
encode_krb5_reply_key_pack                                   encode_krb5_reply_key_pack                                   ??
encode_krb5_reply_key_pack_draft9                            encode_krb5_reply_key_pack_draft9                            ??
encode_krb5_td_dh_parameters                                 encode_krb5_td_dh_parameters                                 ??
encode_krb5_td_trusted_certifiers                            encode_krb5_td_trusted_certifiers                            ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbflc_reap_internal                                         (kcb)flc_reap_internal                                       kernel cache buffers ??
kcbo_incr_doc                                                (kcbo)_incr_doc                                              kernel cache buffers object queue ??
kcbtse_raise_tbs_datafile_enc_mismatch                       (kcbtse)_raise_tbs_datafile_enc_mismatch                     kernel cache buffers tablespace encryption ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kcslcu8                                                      (kcs)lcu8                                                    kernel cache scn management ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdiss_optimize                                               (kdiss)_optimize                                             kernel data index split skip scan ??
kdlfForcedType                                               (kdl)fForcedType                                             kernel data lob ??
kdmrIMCULoadAvg                                              (kdmr)IMCULoadAvg                                            kernel data in-memory data layer background ??
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??
kewxgenstbcb                                                 (kewx)genstbcb                                               kernel event AWR sysaux? ??
kewxgsc_generic_space_cbf                                    (kewx)gsc_generic_space_cbf                                  kernel event AWR sysaux? ??
kfIsZdlra                                                    (kf)IsZdlra                                                  kernel automatic storage management ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
kfdDskTableCbInternal                                        (kfd)DskTableCbInternal                                      kernel automatic storage management disk ??
kffgAddChildBlk                                              (kffg)AddChildBlk                                            kernel automatic storage management KFF file group ??
kffgAddDefault                                               (kffg)AddDefault                                             kernel automatic storage management KFF file group ??
kffgAttrMaxParCb                                             (kffg)AttrMaxParCb                                           kernel automatic storage management KFF file group ??
kffgAttrParSchmOvrdCb                                        (kffg)AttrParSchmOvrdCb                                      kernel automatic storage management KFF file group ??
kffgAuthenticate                                             (kffg)Authenticate                                           kernel automatic storage management KFF file group ??
kffgCalDeltaRedChg                                           (kffg)CalDeltaRedChg                                         kernel automatic storage management KFF file group ??
kffgCalRealQuota                                             (kffg)CalRealQuota                                           kernel automatic storage management KFF file group ??
kffgCalTargetRedun                                           (kffg)CalTargetRedun                                         kernel automatic storage management KFF file group ??
kffgCmp                                                      (kffg)Cmp                                                    kernel automatic storage management KFF file group ??
kffgFillFg                                                   (kffg)FillFg                                                 kernel automatic storage management KFF file group ??
kffgFindFree                                                 (kffg)FindFree                                               kernel automatic storage management KFF file group ??
kffgGetParOvrd                                               (kffg)GetParOvrd                                             kernel automatic storage management KFF file group ??
kffgIsParitySupported                                        (kffg)IsParitySupported                                      kernel automatic storage management KFF file group ??
kffgIsRedunAvailable                                         (kffg)IsRedunAvailable                                       kernel automatic storage management KFF file group ??
kffgIsUnsplit                                                (kffg)IsUnsplit                                              kernel automatic storage management KFF file group ??
kffgMakeName                                                 (kffg)MakeName                                               kernel automatic storage management KFF file group ??
kffgParseParOvrd                                             (kffg)ParseParOvrd                                           kernel automatic storage management KFF file group ??
kffgVerifyRedunChg                                           (kffg)VerifyRedunChg                                         kernel automatic storage management KFF file group ??
kffgcSyncDBLazy                                              (kffg)cSyncDBLazy                                            kernel automatic storage management KFF file group ??
kffgcSyncEnd                                                 (kffg)cSyncEnd                                               kernel automatic storage management KFF file group ??
kffgftMap                                                    (kffg)ftMap                                                  kernel automatic storage management KFF file group ??
kffgptAddDefault                                             (kffg)ptAddDefault                                           kernel automatic storage management KFF file group ??
kffgptFixRedun                                               (kffg)ptFixRedun                                             kernel automatic storage management KFF file group ??
kffgptGetStrVal                                              (kffg)ptGetStrVal                                            kernel automatic storage management KFF file group ??
kffgptLookup                                                 (kffg)ptLookup                                               kernel automatic storage management KFF file group ??
kffgptLookupDump                                             (kffg)ptLookupDump                                           kernel automatic storage management KFF file group ??
kffgptParseNum                                               (kffg)ptParseNum                                             kernel automatic storage management KFF file group ??
kfgComputeGrpProp                                            (kfg)ComputeGrpProp                                          kernel automatic storage management diskgroups ??
kfgGrpTableCbInternal                                        (kfg)GrpTableCbInternal                                      kernel automatic storage management diskgroups ??
kfiQuantize2                                                 (kf)iQuantize2                                               kernel automatic storage management ??
kfioComputeStripeIndex                                       (kfio)ComputeStripeIndex                                     kernel automatic storage management translation I/O layer ??
kfioGetXSetSize                                              (kfio)GetXSetSize                                            kernel automatic storage management translation I/O layer ??
kfioLoadParity                                               (kfio)LoadParity                                             kernel automatic storage management translation I/O layer ??
kfioParityWriteBegin                                         (kfio)ParityWriteBegin                                       kernel automatic storage management translation I/O layer ??
kfk_alloc_kfkio_freelist                                     (kfk)_alloc_kfkio_freelist                                   kernel automatic storage management KFK ??
kfk_asm_ioerror                                              (kfk)_asm_ioerror                                            kernel automatic storage management KFK ??
kfk_bld_sbmtptr_arry                                         (kfk)_bld_sbmtptr_arry                                       kernel automatic storage management KFK ??
kfk_bld_waitptr_arry                                         (kfk)_bld_waitptr_arry                                       kernel automatic storage management KFK ??
kfk_block_asmlib                                             (kfk)_block_asmlib                                           kernel automatic storage management KFK ??
kfk_block_oss                                                (kfk)_block_oss                                              kernel automatic storage management KFK ??
kfk_block_ufs                                                (kfk)_block_ufs                                              kernel automatic storage management KFK ??
kfk_build_osdstruct                                          (kfk)_build_osdstruct                                        kernel automatic storage management KFK ??
kfk_chk_xor_valid                                            (kfk)_chk_xor_valid                                          kernel automatic storage management KFK ??
kfk_do_abn_xor_chk                                           (kfk)_do_abn_xor_chk                                         kernel automatic storage management KFK ??
kfk_error_emulate                                            (kfk)_error_emulate                                          kernel automatic storage management KFK ??
kfk_fill_ksfdrq                                              (kfk)_fill_ksfdrq                                            kernel automatic storage management KFK ??
kfk_find_io_subsys                                           (kfk)_find_io_subsys                                         kernel automatic storage management KFK ??
kfk_get_asmioc_oper                                          (kfk)_get_asmioc_oper                                        kernel automatic storage management KFK ??
kfk_get_free_kfkrq                                           (kfk)_get_free_kfkrq                                         kernel automatic storage management KFK ??
kfk_get_unreaped_ios                                         (kfk)_get_unreaped_ios                                       kernel automatic storage management KFK ??
kfk_hard_abn_set                                             (kfk)_hard_abn_set                                           kernel automatic storage management KFK ??
kfk_hard_getoffs                                             (kfk)_hard_getoffs                                           kernel automatic storage management KFK ??
kfk_iodone_merge_slave                                       (kfk)_iodone_merge_slave                                     kernel automatic storage management KFK ??
kfk_lib_abn_check                                            (kfk)_lib_abn_check                                          kernel automatic storage management KFK ??
kfk_lib_xor_check                                            (kfk)_lib_xor_check                                          kernel automatic storage management KFK ??
kfk_oss_cell_timedout                                        (kfk)_oss_cell_timedout                                      kernel automatic storage management KFK ??
kfk_oss_dump_orq                                             (kfk)_oss_dump_orq                                           kernel automatic storage management KFK ??
kfk_oss_get_orq                                              (kfk)_oss_get_orq                                            kernel automatic storage management KFK ??
kfk_oss_offload_fill_orq                                     (kfk)_oss_offload_fill_orq                                   kernel automatic storage management KFK ??
kfk_oss_offload_submit                                       (kfk)_oss_offload_submit                                     kernel automatic storage management KFK ??
kfk_oss_post_wait                                            (kfk)_oss_post_wait                                          kernel automatic storage management KFK ??
kfk_oss_put_orq                                              (kfk)_oss_put_orq                                            kernel automatic storage management KFK ??
kfk_parse_disk_path                                          (kfk)_parse_disk_path                                        kernel automatic storage management KFK ??
kfk_proc_submitted_lib_reqs                                  (kfk)_proc_submitted_lib_reqs                                kernel automatic storage management KFK ??
kfk_process_an_ioq                                           (kfk)_process_an_ioq                                         kernel automatic storage management KFK ??
kfk_put_free_kfkios                                          (kfk)_put_free_kfkios                                        kernel automatic storage management KFK ??
kfk_reap_io_from_kfks                                        (kfk)_reap_io_from_kfks                                      kernel automatic storage management KFK ??
kfk_reap_lib_async_io                                        (kfk)_reap_lib_async_io                                      kernel automatic storage management KFK ??
kfk_reap_oss_async_io                                        (kfk)_reap_oss_async_io                                      kernel automatic storage management KFK ??
kfk_reap_ufs_async_io                                        (kfk)_reap_ufs_async_io                                      kernel automatic storage management KFK ??
kfk_rebuild_kfdrqs                                           (kfk)_rebuild_kfdrqs                                         kernel automatic storage management KFK ??
kfk_retry_submit_oss_io                                      (kfk)_retry_submit_oss_io                                    kernel automatic storage management KFK ??
kfk_submit_lib_io                                            (kfk)_submit_lib_io                                          kernel automatic storage management KFK ??
kfk_submit_oss_io                                            (kfk)_submit_oss_io                                          kernel automatic storage management KFK ??
kfk_submit_oss_io_no_wtevt                                   (kfk)_submit_oss_io_no_wtevt                                 kernel automatic storage management KFK ??
kfk_submit_oss_io_wtevt                                      (kfk)_submit_oss_io_wtevt                                    kernel automatic storage management KFK ??
kfk_submit_ufs_io                                            (kfk)_submit_ufs_io                                          kernel automatic storage management KFK ??
kfk_transit_fetchIO                                          (kfk)_transit_fetchIO                                        kernel automatic storage management KFK ??
kfk_transit_waitIO                                           (kfk)_transit_waitIO                                         kernel automatic storage management KFK ??
kfk_update_iodone_stats                                      (kfk)_update_iodone_stats                                    kernel automatic storage management KFK ??
kfk_zero_reap_array                                          (kfk)_zero_reap_array                                        kernel automatic storage management KFK ??
kfmdIsDGMountedCluster                                       (kfmd)IsDGMountedCluster                                     kernel automatic storage management node monitor interface implementation layer
                                                                                                                          for diskgroup registration ??

kfmsGetGrpMemCnt                                             (kfms)GetGrpMemCnt                                           kernel automatic storage management node monitor interface implementation layers
                                                                                                                           support function interface ??

kgfdDiscoverSubmit                                           (kgfd)DiscoverSubmit                                         kernel generic ASM io subsystem driver ??
kgh_size_sanity_check                                        (kgh)_size_sanity_check                                      kernel generic heap manager ??
kjac_allow_tg                                                (kjac)_allow_tg                                              kernel lock management application continuity  ??
kjccspbat                                                    (kjc)cspbat                                                  kernel lock management communication ??
kkocfbFindOldActual                                          (kkocfb)FindOldActual                                        kernel compile optimizer cardinality feedback ??
kkopinlpInt                                                  (kko)pinlpInt                                                kernel compile optimizer ??
kkscsGetPQSharingParameters                                  (kkscs)GetPQSharingParameters                                kernel compile shared objects (cursor) child cursor ??
kkscsGetSessionLimitDOP                                      (kkscs)GetSessionLimitDOP                                    kernel compile shared objects (cursor) child cursor ??
kokegAddOneGarbageItem                                       (koke)gAddOneGarbageItem                                     kernel objects kernel side OBJECTS images ??
kokegRepinLob                                                (koke)gRepinLob                                              kernel objects kernel side OBJECTS images ??
kpochalts                                                    (kpo)chalts                                                  kernel programmatic interface oracle ??
kpossGetEncNonTemplateOverflow                               (kpo)ssGetEncNonTemplateOverflow                             kernel programmatic interface oracle ??
kpossSetEncNonTemplateOverflow                               (kpo)ssSetEncNonTemplateOverflow                             kernel programmatic interface oracle ??
kpossTemplateMatch_                                          (kpo)ssTemplateMatch_                                        kernel programmatic interface oracle ??
kpossTemplateSet                                             (kpo)ssTemplateSet                                           kernel programmatic interface oracle ??
kpueONSTraceWrite                                            (kpu)eONSTraceWrite                                          kernel programmatic interface user ??
kpueONSTrcCb                                                 (kpu)eONSTrcCb                                               kernel programmatic interface user ??
kpussTemplateSend                                            (kpu)ssTemplateSend                                          kernel programmatic interface user ??
kpuxcOpenCurCntDecr                                          (kpu)xcOpenCurCntDecr                                        kernel programmatic interface user ??
kpuxcOpenCurCntIncr                                          (kpu)xcOpenCurCntIncr                                        kernel programmatic interface user ??
kpuxcRefCurDfnMarkOpen                                       (kpu)xcRefCurDfnMarkOpen                                     kernel programmatic interface user ??
kpuxcRefCurOutBndMarkOpen                                    (kpu)xcRefCurOutBndMarkOpen                                  kernel programmatic interface user ??
kpuxcSessionSignatureRecv                                    (kpu)xcSessionSignatureRecv                                  kernel programmatic interface user ??
kpuxcSessionTemplateSend                                     (kpu)xcSessionTemplateSend                                   kernel programmatic interface user ??
kpuxcSessionTemplatesFree                                    (kpu)xcSessionTemplatesFree                                  kernel programmatic interface user ??
kpuxcTACService                                              (kpu)xcTACService                                            kernel programmatic interface user ??
kpuxsoCurPrefetchFree                                        (kpu)xsoCurPrefetchFree                                      kernel programmatic interface user ??
kpuxsoOperInit                                               (kpu)xsoOperInit                                             kernel programmatic interface user ??
krbbCountThreads                                             (krbb)CountThreads                                           kernel redo backup/restore creation of a backuppiece ??
ksdhng_wdat_rem_dup                                          (ksdhng)_wdat_rem_dup                                        kernel service (VOS) debug internal errors hang analyze ??
ksfdafGetXsetSize                                            (ksfdaf)GetXsetSize                                          kernel service (VOS) functions disk IO asm files ??
ksfdafParityWrite                                            (ksfdaf)ParityWrite                                          kernel service (VOS) functions disk IO asm files ??
ksfdafParityWriteAbort                                       (ksfdaf)ParityWriteAbort                                     kernel service (VOS) functions disk IO asm files ??
ksfdafParityWriteBegin                                       (ksfdaf)ParityWriteBegin                                     kernel service (VOS) functions disk IO asm files ??
ksfdafParityWriteEnd                                         (ksfdaf)ParityWriteEnd                                       kernel service (VOS) functions disk IO asm files ??
kslwt_event_stats                                            (kslwt)_event_stats                                          kernel service (VOS) latching and post-wait wait interface ??
kslwt_push_stats                                             (kslwt)_push_stats                                           kernel service (VOS) latching and post-wait wait interface ??
ksmsq_spendingi                                              (ksmsq)_spendingi                                            kernel service (VOS) memory sga heap message queue services ??
ksp_init_modp_send                                           (ksp)_init_modp_send                                         kernel service (VOS) parameter ??
ksp_modp_get_enckeyvals                                      (ksp)_modp_get_enckeyvals                                    kernel service (VOS) parameter ??
ksp_modp_get_keyvals                                         (ksp)_modp_get_keyvals                                       kernel service (VOS) parameter ??
ksp_modp_set_enckeyvals                                      (ksp)_modp_set_enckeyvals                                    kernel service (VOS) parameter ??
ksp_modp_set_keyvals                                         (ksp)_modp_set_keyvals                                       kernel service (VOS) parameter ??
ksp_modp_update_sign                                         (ksp)_modp_update_sign                                       kernel service (VOS) parameter ??
kspdecbuf                                                    (ksp)decbuf                                                  kernel service (VOS) parameter ??
kspencbuf                                                    (ksp)encbuf                                                  kernel service (VOS) parameter ??
ksucln_expired_cleanup_timer                                 (ksucln)_expired_cleanup_timer                               kernel service (VOS) user cleanup detached process ??
kswrListDelete                                               (ksw)rListDelete                                             kernel service (VOS) workgroup ??
ksws_alb_alloc_pga_memory                                    (ksws)_alb_alloc_pga_memory                                  kernel service (VOS) workgroup services ??
ksws_alb_block_service                                       (ksws)_alb_block_service                                     kernel service (VOS) workgroup services ??
ksws_alb_check_sessions_to_mark                              (ksws)_alb_check_sessions_to_mark                            kernel service (VOS) workgroup services ??
ksws_alb_check_threshold                                     (ksws)_alb_check_threshold                                   kernel service (VOS) workgroup services ??
ksws_alb_clear_mark_drain_session                            (ksws)_alb_clear_mark_drain_session                          kernel service (VOS) workgroup services ??
ksws_alb_dump_service                                        (ksws)_alb_dump_service                                      kernel service (VOS) workgroup services ??
ksws_alb_dump_session                                        (ksws)_alb_dump_session                                      kernel service (VOS) workgroup services ??
ksws_alb_filter_session                                      (ksws)_alb_filter_session                                    kernel service (VOS) workgroup services ??
ksws_alb_mark_target_sessions                                (ksws)_alb_mark_target_sessions                              kernel service (VOS) workgroup services ??
ksws_alb_process_directives                                  (ksws)_alb_process_directives                                kernel service (VOS) workgroup services ??
ksws_alb_run_gv_sql                                          (ksws)_alb_run_gv_sql                                        kernel service (VOS) workgroup services ??
ksws_alb_safe_session                                        (ksws)_alb_safe_session                                      kernel service (VOS) workgroup services ??
ksws_alb_safe_to_block_service                               (ksws)_alb_safe_to_block_service                             kernel service (VOS) workgroup services ??
ksws_alb_selection_bias_apply                                (ksws)_alb_selection_bias_apply                              kernel service (VOS) workgroup services ??
ksws_alb_set_mark_drain_session                              (ksws)_alb_set_mark_drain_session                            kernel service (VOS) workgroup services ??
ksws_alb_unblock_fix_delta_services                          (ksws)_alb_unblock_fix_delta_services                        kernel service (VOS) workgroup services ??
ksws_alb_unblock_service                                     (ksws)_alb_unblock_service                                   kernel service (VOS) workgroup services ??
ksws_alb_update_service_session_metrics                      (ksws)_alb_update_service_session_metrics                    kernel service (VOS) workgroup services ??
ksws_alb_update_threshold                                    (ksws)_alb_update_threshold                                  kernel service (VOS) workgroup services ??
ksws_alb_valid_session                                       (ksws)_alb_valid_session                                     kernel service (VOS) workgroup services ??
kwraChkOptdef                                                (k)wraChkOptdef                                              kernel ??
kwrachkopnTree                                               (k)wrachkopnTree                                             kernel ??
kwraplschkLogdef                                             (k)wraplschkLogdef                                           kernel ??
kxccXRkxck                                                   (kxcc)XRkxck                                                 kernel execution constraints catch all ??
kzctxgIsGlobal                                               (kzctxg)IsGlobal                                             kernel security Code-Based Access Control context routines global_context ??
kzrtGetAndCheckTriggerCache                                  (kzrt)GetAndCheckTriggerCache                                kernel security virtual private database privileges ??
kzrtTriggerPresent                                           (kzrt)TriggerPresent                                         kernel security virtual private database privileges ??
kzvdveCommOpBypass                                           (kzvd)veCommOpBypass                                         kernel security data vault ??
kzvdveIsAppCommonUserId                                      (kzvd)veIsAppCommonUserId                                    kernel security data vault ??
kzvradmAllowCommOp                                           (kz)vradmAllowCommOp                                         kernel security ??
kzvradmAllowCommOpCB                                         (kz)vradmAllowCommOpCB                                       kernel security ??
kzvradmAllowCommOp_int                                       (kz)vradmAllowCommOp_int                                     kernel security ??
kzvutilGetACOStatus                                          (kz)vutilGetACOStatus                                        kernel security ??
kzvutilGetACOStatus0                                         (kz)vutilGetACOStatus0                                       kernel security ??
psdvcn                                                       (psd)vcn                                                     PLSQL infrastructure programmatic interfaces to/from  ??
qesrcTol_New                                                 (qesrc)Tol_New                                               query execute services result cache ??
qsodasqlGetContentDefine                                     (qsoda)sqlGetContentDefine                                   query system DBMS_SODA ??
qsodasqlGetCreatedOnDefine                                   (qsoda)sqlGetCreatedOnDefine                                 query system DBMS_SODA ??
qsodasqlGetInsertSQL                                         (qsoda)sqlGetInsertSQL                                       query system DBMS_SODA ??
qsodasqlGetKeyDefine                                         (qsoda)sqlGetKeyDefine                                       query system DBMS_SODA ??
qsodasqlGetLastModDefine                                     (qsoda)sqlGetLastModDefine                                   query system DBMS_SODA ??
qsodasqlGetMediaDefine                                       (qsoda)sqlGetMediaDefine                                     query system DBMS_SODA ??
qsodasqlGetVersionDefine                                     (qsoda)sqlGetVersionDefine                                   query system DBMS_SODA ??
qsodasqlPrefetchPrepare                                      (qsoda)sqlPrefetchPrepare                                    query system DBMS_SODA ??
qsodasqlSelectDocFieldsSQL                                   (qsoda)sqlSelectDocFieldsSQL                                 query system DBMS_SODA ??
qsodaxArrayFetchDocuments                                    (qsoda)xArrayFetchDocuments                                  query system DBMS_SODA ??
qsodaxDefineDocumentFields                                   (qsoda)xDefineDocumentFields                                 query system DBMS_SODA ??
qsodaxGetPrefetchDocument                                    (qsoda)xGetPrefetchDocument                                  query system DBMS_SODA ??
sskgds_dw_is_context_valid                                   (sskg)ds_dw_is_context_valid                                 operating system dependent system kernel generic ??
sskgds_dw_is_pccontext_valid                                 (sskg)ds_dw_is_pccontext_valid                               operating system dependent system kernel generic ??

improving performance with stored procedures — a pgbench example.

improving performance with stored procedures — a pgbench example.

In a previous post I mentioned that I do not use pgbench to benchmark the platform. But when it comes to measuring client/server application, pgbench fully makes makes sense.

I initialize the pgbench schema with small data:

pgbench - initialize - init-steps=dtgvpf -h localhost -p 5432 -U postgres franck

And I run the pgbench builtin workload with does something like a TPC-B

tpcb-like builtin

pgbench --builtin tpcb-like --transactions 30000 --protocol=prepared --jobs=10 --client=10 -h localhost -p 5432 -U postgres franck

I run 30000 transactions there, from 10 threads. It runs for more than 4 minutes:

The rate is 1097 transactions per second with an average of 9 milliseconds per transaction.

That’s my baseline. What the builtin transaction runs is easy to get from the source:

postgres/postgres

As pgbench can also run custom workloads, I’ll run exactly the same workload by copying those statements in a file.

tpcb-like as a custom file

Here is the file containing the same as the builtin found in pgbench.c:

cat > /tmp/tpcb-like <<'CAT'
-- tpcb-like
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
END;
CAT

Now running the workload from this file, still 30000 transactions from 10 threads.

pgbench --file /tmp/tpcb-like --transactions 30000 --protocol=prepared --jobs=10 --client=10 -h localhost -p 5432 -U postgres franck

The result is very similar:

The rate is 1095 transactions per second with an average of 9 milliseconds per transaction. Same statements and same throughput.

tpcb-like as a procedure + select

I create a stored procedure with all INSERT/UPDATE statements:

create procedure P_TPCB_LIKE
(p_aid integer, p_bid integer, p_tid integer, p_delta integer) AS $$
BEGIN
UPDATE pgbench_accounts SET abalance = abalance + p_delta WHERE aid = p_aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
UPDATE pgbench_tellers SET tbalance = tbalance + p_delta WHERE tid = p_tid;
UPDATE pgbench_branches SET bbalance = bbalance + p_delta WHERE bid = p_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
END;
$$ language plpgsql;

Now, the custom file will only call that procedure for the modifications, and run the select:

cat > /tmp/simple-update-p <<'CAT'
-- tpcb-like
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
call P_SIMPLE_UPDATE(:aid, :bif, :tid, :delta);
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
END;
CAT

This is functionally equivalent and I run again the same number of transactions from the same number of threads:

pgbench --file /tmp/tpcb-like-p --transactions 30000 --protocol=prepared --jobs=10 --client=10 -h localhost -p 5432 -U postgres franck

Now I have a huge gain here as the throughput is 3 times higher:

The rate is 3024 transactions per second with an average of 3 milliseconds per transaction. Same statements but stored on the server.

tpcb-like as a function with refcursor

Ideally, each client/server call should be only one statement. And then I must include the SELECT part in my stored procedure. That is possible with a function that returns a refcursor:

create function F_TPCB_LIKE
(p_aid integer, p_bid integer, p_tid integer, p_delta integer) returns table(abalance integer) AS $$
DECLARE
c refcursor;
BEGIN
UPDATE pgbench_accounts SET abalance = pgbench_accounts.abalance + p_delta WHERE aid = p_aid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
UPDATE pgbench_tellers SET tbalance = tbalance + p_delta WHERE tid = p_tid;
UPDATE pgbench_branches SET bbalance = bbalance + p_delta WHERE bid = p_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (p_tid, p_bid, p_aid, p_delta, CURRENT_TIMESTAMP);
return query SELECT pgbench_accounts.abalance FROM pgbench_accounts WHERE aid = p_aid;
END;
$$ language plpgsql;

You note that I didn’t even change the variable names and for this reason, I prefixed abalance in the update statement.

Here is my simple call for the transaction, which does all the DML and returns a cursor:

cat > /tmp/tpcb-like-f <<'CAT'
-- tpcb-like
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
SELECT abalance from F_TPCB_LIKE(:aid, :bif, :tid, :delta);
END;
CAT

Running it with the same configuration:

pgbench --file /tmp/tpcb-like-f --transactions 30000 --protocol=prepared --jobs=10 --client=10 -h localhost -p 5432 -U postgres franck

the performance is even better:

The rate is 4167 transactions per second with an average of 2 milliseconds per transaction. Obviously this is better. The more you do in the database the better you can scale. But there’s even more: all the benefits from encapsulation. I’ll mention 3 main benefits:

  1. Here the API between the application and the database is independent of the SQL syntax. And the SQL statements that are tightly coupled with the data model (and the database system/version) are all encapsulated in the procedure, within this database.
  2. It is also a strong security advantage: no SQL injection possible as inputs go through the API which admits only a procedure name and typed parameters. And you can audit the procedure calls.
  3. When the junior architect tells you that your application is an ugly old monolith, add the following comment in front of each call to the stored procedures and you are trendy again:
--- calling the 'command' CQRS microservice
CALL my_app_procedure(...);
--- calling the 'query' CQRS microservice
SELECT my_app_function(...);

Video : Oracle REST Data Services (ORDS) : First Party (Basic) Authentication on Tomcat

In today’s video we demonstrate first party cookie-based authentication, or basic authentication, for Oracle REST Data Services when run on Tomcat.

For those of you that prefer to read, this is one of the authentication and authorization methods discussed in this article.

You can get more information about ORDS here.

The star of today’s video is Patrick Barel, who is somehow managing to remain out of focus when everything else in the video is in focus. I’m not sure how I managed that… </p />
</p></div>

    	  	<div class=

match_recognize()

A couple of days ago I posted a note with some code (from Stew Ashton) that derived the clustering_factor you would get for an index when you had set a value for the table_cached_blocks preference but had not yet created the index. In the note I said that I had produced a simple and elegant (though massively CPU-intensive) query using match_recognize() that seemed to produce the right answer, only to be informed by Stew that my interpretation of how Oracle calculated the clustering_factor was wrong and that the query was irrelevant.  (The fact that it happened to produce the right answer in all my tests was an accidental side effect of the way I had been generating test data. With Stew’s explanation of what Oracle was doing it was easy to construct a data set that proved my query was doing the wrong thing.)

The first comment I got on the posting was a request to publish my match_recognize() query – even though it was irrelevant to the problem in hand – simply because it might be a useful lesson in what could be done with the feature; so here’s some code that doesn’t do anything useful but does demonstrate a couple of points about match_recognize(). I have to stress, though, that I’m not an expert on match_recognize() so there may be a more efficient way of using it to acheieve the same result. There is certainly a simple and more efficient way to get the same result using some fairly straightforward PL/SQL code.

Requirement

I had assumed that if you set the table_cached_blocks preference to N then Oracle would keep track of the previous N rowids as it walked an index to gather stats and only increment the “clustering factor counter” if it failed to find a match for the block address of the current rowid in the block addresses extracted from the previous N rowids. My target was to emulate a way of doing this counting.

Strategy

Rather than writing code that “looked backwards” as it walked the index, I decided to take advantage of the symmetry of the situation and write code that looked forwards (you could think of this as viewing the index in descending order and looking backwards along the descending index). I also decided that I could count the number of times I did find a match in the trail of rowids, and subtract that from the total number of index entries.

So my target was to look for patterns where I start with the block address from the current rowid, then find the same block address after zero to N-1 failures to find the block address. Since the index doesn’t exist I’ll need to emulate its existence by selecting the columns that I want in the index along with the rowid, ordering the data in index order. Then I can walk this “in memory” index looking for the desired pattern.

Here’s some code to create a table to test against:


rem
rem     Script:         clustering_factor_est_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2019
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem 

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                                  id,
        cast(rownum as varchar2(10))            v1,
        trunc(dbms_random.value(0,10000))       rand,
        rpad('x',100,'x')                       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
/

My table has 1M rows, and there’s a column called rand which has 10,000 distinct values. This is generated through Oracle’s dbms_random package and the procedure I’ve used will give me roughly 100 occurrences for each value scattered uniformly across the table. An index on this column might be quite useful but it’s probably going to have a very high clustering_factor because, on average, any two rows of a particular value are likely to be separated by 10,000 rows, and even if I look for rows with a pair of consecutive values any two rows are likely to be separated by a few hundred other rows.

Here’s the code that I was using to get an estimate of (my erroneous concept of) the clustering_factor with table_cached_blocks set to 32. For debugging purposes it reports the first row of the pattern for each time my pattern is matched, so in this version of the code I’d  have to check the number of rows returned and subtract that from the number of rows in the table (where rand is not null).


select
        first_rand, first_file_id, first_block_id, first_row_id, step_size
from    (
                select
                        rand, 
                        dbms_rowid.rowid_relative_fno(rowid) file_id,
                        dbms_rowid.rowid_block_number(rowid) block_id,
                        dbms_rowid.rowid_row_number(rowid)   row_id
                from
                        t1
                where
                        rand is not null
        )
match_recognize(
        order by rand, file_id, block_id, row_id
        measures
                count(*) as step_size,
                first(strt.rand)        as first_rand,
                first(strt.file_id)     as first_file_id,
                first(strt.block_id)    as first_block_id,
                first(strt.row_id)      as first_row_id
        one row per match
        after match skip to next row
        pattern (strt miss{0, 31} hit)
        define 
                miss as (block_id != strt.block_id or  file_id != strt.file_id),
                hit  as (block_id  = strt.block_id and file_id  = strt.file_id)
)
order by 
        first_rand, first_file_id, first_block_id, first_row_id
;

The first point to note is that the inline view is the thing I use to model an index on column rand. It simply selects the value and rowid from each row. However I’ve used the dbms_rowid package to break the rowid down into the file_id, block_id and row_id within block. Technically (to deal with partitioned tables and indexes) I also ought to be thinking about the object_id which is the first component of the full rowid. I haven’t actually ordered the rows in the in-line view as I’m going to let the match_recognize() operation handle that.

A warning as we start looking at the match_recognize() section of the query: I’ll be jumping around the clause a little bit, rather than working through it in order from start to finish.

First I need the raw data sorted in order so that any results I get will be deterministic. I have an order by clause that sorts my data by rand and the three components I’ve extracted from the rowid. (It is also possible to have a partition clause – similar to the partition clause of an analytic function – but I don’t need one for this model.)

Then I need to define the “columns” that I’m planning to output in the final query. This is the set of measures and in the list of measures you can see a count(*) (which doesn’t quite mean what it usually does) and a number of calls to a function first() which takes an aliased column name as it’s input and, although the column names look familiar, the alias (strt) seems to have sprung from nowhere.

At this point I want to jump down to the define clause because that’s where the meaning of strt could have been defined.  The define clause is a list of “rules”, which are also treated as “variables”, that tell us how to classify a row. We are looking for patterns, and pattern is a set of rows that follows a set of rules in a particular order, so one of the things I need to do is create a rule that tells Oracle how to identify a row that qualifies as the starting point for a pattern – so I’ve defined a rule called strt to do this – except I haven’t defined it explicitly, it’s not visible in the define list, so Oracle has assumed that the rule is “1 = 1”, in other words every row I’m going to look at could be classified as a strt row.

Now that I have a definition for what strt means I could go back to the measures – but I’ll postpone doing that for a moment and look at the other rules in my define list. I have a rule called miss which says “if either of these comparisons evaluates to true” then the row is a miss;  but the predicates includes a reference to strt which means we are doing comparisons with the most recent row that was classified as a strt row. So a miss means we’ve found a starting row and we’re now looking at other rows comparing the block_id and file_id for each row to check that they don’t match the block_id and file_id of the starting row.

Similarly we have a hit rule which says a hit means we’ve previously found a starting row and we’re now looking at other rows checking for rows where the current block_id and file_id match the starting block_id and file_id.

Once we’ve got a set of rules explaining how to classify rows we can specify a pattern (which means going back up the match_recognize() clause one section). Our pattern reads:  “find a strt row, followed by zero and 31 miss rows, followed by a hit row”. And that’s just a description of my back-to-front way of saying “remember the last 32  rowids and check if the current block address matches the block address in one of those rowids”.

The last two clauses that need to be explained before I revisit the measures clause are the “one row per match” and “after match skip to next row”.

If I find a sequence of rows that matches my pattern there are several things I could do with that set of rows – for example I could report every row in that pattern along with the classification of strt/miss/hit (which would be useful if I’m looking for a few matches to a small pattern in a large data set), or (as I’ve done here) I could report just one row from the pattern and give Oracle some instruction about which one row I want reported.

Then, after I’ve found (and possibly reported) a pattern, what should I do next. Again there are several possibilities – the two most obvious ones, perhaps are: “just keep going” i.e. look at the row after the end of the pattern to see if it’s another strt row, and “go to the row after the start of the pattern you’ve just reported”. These translate into: “after match skip past last row” (which is the default if you don’t specify an “after match” clause) and “after match skip to next row” (which is what I’ve specified).

Finally we get back to the measures clause – I’ve defined four “columns” with names like ‘first_xxx’ and a step_size. The step_size is defined as count(*) which – in this context – means “count the number of rows in the current matched pattern”. The other measures are defined using the first() function, referencing strt alias which tells Oracle I want to retain the value from the first row that met the strt rule in the current matched pattern.

In summary, then my match_recognize() clause tells Oracle to

  • Sort the data by rand, file_id, block_id, row_id
  • For each row in turn
    • extract the file_id and block_id
    • take up to 32 steps down the list looking for a matching file_id and block_id
    • If you find a match pass a row to the parent operation that consists of: the number of rows between strt to hit inclusive, and the values of rand, file_id, block_id, and row_id of the strt row.

Before you try testing the code, you might like to know about some results.

As it stands my laptop with a virtual machine running 12.2.0.1 took 1 minute and 5 seconds to complete with “miss{0, 31}” in the pattern. In fact the first version of the code had the file_id and block_id tests in the define clause in the opposite order viz:

        define 
                miss as (file_id != strt.file_id or  block_id != strt.block_id),
                hit  as (file_id  = strt.file_id and block_id  = strt.block_id)

Since the file_id for my test is the same for every row in my table (it’s a single file tablespace), this was wasting a surprising amount of CPU, leading to a run time of 1 minute 17 seconds! Neither time looks particularly good when compared to the time required to create the index, set the table_cached_blocks to 32, and gather stats on the index – in a total time of less than 5 seconds. The larger the value I chose for the pattern match, the worse the workload became, until at “miss{0, 199}” – emulating a table_cached_blocks of 200 – the time to run was about 434 seconds of CPU!

A major portion of the problem is the way that Oracle is over-optimistic (or “greedy”, to use the technical term) with its pattern matching, combined with the nature of the data which (in this example) isn’t going to offer much opportunity for matching, combined with the fact that Oracle cannot determine that a row that is not a “miss” has to be a “hit”.  In this context “greedy” means Oracle will try to find as many consecutive occurrences of the first rule in the pattern before it tries to find and occurrence of the second rule – and when it fails to match a pattern it will “backtrack” one step and have another go, being slightly less greedy. So, for our example, the greedy algorithm will operate as follows:

  • find 31 rows that match miss, then discover the 32nd row does not match hit
  • go back to the strt and find 30 rows that match miss, then discover the 31st row does not match hit
  • go back to the strt and find 29 rows that match miss, then discover the 30th row does not match hit
  • … repeat until
  • go back to the strt and find 0 rows that match miss, then discover the 1st does not match hit
  • go to the next row, call it strt, and repeat the above … 1M times.

From a human perspective this is a pretty stupid strategy for this specific problem – but that’s because we happen to know that “hit” = “not(miss)” (ignoring nulls, of which there are none) while Oracle has to assume that there is no relationship between “hit” and “miss”.

There is hope, though, because you can tell Oracle that it should be “reluctant” rather than “greedy” which means Oracle will consume the smallest possible number of occurrences of the first rule before testing the second rule, and so on. All you have to do is append a “?” to the count qualifier:

        pattern (strt miss{0, 31 }? hit)

Unfortunately this seemed to have very little effect on execution time (and CPU usage) in our case. Again this may be due to the nature of the data etc., but it may also be a limitation in the way that the back tracking works. I had expected a response time that would more closely mirror the human expectation, but a few timed tests suggest the code uses exactly the same type of strategy for the reluctant strategy as it does for the greedy one, viz:

  • find 0 rows that match miss, then discover the next row does not match hit
  • go back to the strt and find 1 row that matches miss, then discover the 2nd row does not match hit
  • go back to the strt and find 2 rows that match miss, then discover the 3rd row does not match hit
  • … repeat until
  • go back to the strt and find 31 rows that match miss, then discover the 32nd does not match hit
  • go to the next row, call it strt, and repeat the above … 1M times.

Since there are relatively few cases in our data where a pattern match will occur both the reluctant and the greedy strategies will usually end up doing all 32 steps. I was hoping for a more human-like algorithm that would recognise that Oracle would recognise that if it’s just missed on the first X rows then it need only check the X+1th and not go back to the beginning (strt) – but my requirement makes it easy to see (from a human perspective) that that makes sense; in a generic case (with more complex patterns and without the benefit of having two mutially exclusive rules) the strategy of “start all over again” is probably a much safer option to code.

Plan B

Plan B was to send the code to Stew Ashton and ask if he had any thoughts on making it more efficient – I wasn’t expecting him to send a PL/SQL solution my return of post, but that’s what I got and published in the previous post.

Plan C

It occurred to me that I don’t really mind if the predicted clustering_factor is a little inaccurate, and that the “backtracking” introduced by the variable qualifer {0,31} was the source of a huge amount of the work done, so I took a different approach which simply said: “check that the next 32 (or preferred value of N) rows are all misses”.  This required two changes – eliminate one of the defines (the “hit”) and modify the pattern definition as follows:

         pattern (strt  miss{32} )
         define
                 miss as (block_id != strt.block_id or  file_id != strt.file_id)

The change in strategy means that the result is going to be (my version of) the clustering_factor rather than the number to subtract from num_rows to derive the clustering_factor. And I’ve introduced a small error which shows up towards the end of the data set – I’ve demanded that a pattern should include exactly 32 misses; but when you’re 32 rows from the end of the data set there aren’t enough rows left to match the pattern. So the result produced by the modified code could be as much as 32 short of the expected result.  However, when I showed the code to Stew Ashton he pointed out that I could include “alternatives” in the pattern, so all I had to do was add in to the pattern something which said “and if there aren’t 32 rows left, getting to the end of the data set is good enough” (technically that should be end of the current partition, but we have only one partition).

         pattern (strt  ( miss{32} | ( miss* $) ) )

The “miss” part of the pattern now reads:  “32 misses” or “zero or more misses and then the end of file/partition/dataset”.

It’s still not great – but the time to process the 1M rows with a table_cached_blocks of 32 came down to 31 seconds

Finally

I’ll close with one important thought. There’s a significant difference in the execution plans for the two strategies – which I’m showing as outputs from the SQL Monitor report using a version of the code that does a simple count(*) rather than listing any rows:


pattern (strt miss{0, 31} hit)
==========================================================================================================================================
| Id |         Operation          | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                            |       | (Estim) |       | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
==========================================================================================================================================
|  0 | SELECT STATEMENT           |       |         |       |        54 |    +14 |     1 |        1 |     . |          |                 |
|  1 |   SORT AGGREGATE           |       |       1 |       |        54 |    +14 |     1 |        1 |     . |          |                 |
|  2 |    VIEW                    |       |      1M | 12147 |        54 |    +14 |     1 |     2782 |     . |          |                 |
|  3 |     MATCH RECOGNIZE SORT   |       |      1M | 12147 |        60 |     +8 |     1 |     2782 |  34MB |          |                 |
|  4 |      VIEW                  |       |      1M |   325 |        14 |     +1 |     1 |       1M |     . |          |                 |
|  5 |       INDEX FAST FULL SCAN | T1_I1 |      1M |   325 |         7 |     +8 |     1 |       1M |     . |          |                 |
==========================================================================================================================================

pattern (strt  miss{32} )
==================================================================================================================================================================
| Id |                     Operation                      | Name  |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                                    |       | (Estim) |       | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
==================================================================================================================================================================
|  0 | SELECT STATEMENT                                   |       |         |       |        15 |    +16 |     1 |        1 |     . |          |                 |
|  1 |   SORT AGGREGATE                                   |       |       1 |       |        15 |    +16 |     1 |        1 |     . |          |                 |
|  2 |    VIEW                                            |       |      1M | 12147 |        15 |    +16 |     1 |     997K |     . |          |                 |
|  3 |     MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTO |       |      1M | 12147 |        29 |     +2 |     1 |     997K |  34MB |          |                 |
|  4 |      VIEW                                          |       |      1M |   325 |        16 |     +1 |     1 |       1M |     . |          |                 |
|  5 |       INDEX FAST FULL SCAN                         | T1_I1 |      1M |   325 |         9 |     +8 |     1 |       1M |     . |          |                 |
==================================================================================================================================================================

The significant line to note is operation 3 in both cases. The query with the pattern that’s going to induce back-tracking reports only “MATCH RECOGNIZE SORT”. The query with the “fixed” pattern reports “MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTO” Oracle can implement a finite state machine with a fixed worst case run-time. When you write some code that uses match_recognize() the three magic words you want to see in the plan are “deterministic finite auto” – if you don’t then, in principle, your query might be one of those that could (theoretically) run forever.

Addendum

Congratulations if you’ve got this far – but please remember that I’ve had very little practice using match_recognize; this was a little fun and learning experience for me and there may be many more things you could usefully know about the technology before you use it in production; there may also be details in what I’ve written which are best forgotten about. That being the case I’d be more than happy for anyone who wants to enhance or correct my code, descriptions and observations to comment below.