Archive

Tag Archives: oracle

All Oracle database professionals know the current versions of the Oracle database (12.2, 18, 19, 20 at the moment of writing), and we also know the pace Oracle corporation keeps is so high that a lot of companies are having a hard time keeping up with the current versions. A prominent one is Oracle corporation itself for their E-Business suite software, where Oracle extended the support for the database for version 12.1.0.2 and 11.2.0.4 for E-Business suite licenses only. But this blog isn’t about bitching about the pace of Oracle support and versions getting desupported.

What I do regularly encounter is that for all kinds of reasons a database version is not updated. Most of the time the versions that are encountered are 12.1.0.2 (the long term supported version of the 12.1 version of the database), 11.2.0.4 (the long term supported version of the 11.2 version of the database), and more and more seldom 11.2.0.3. If things truly have been left without updating you might encounter 11.2.0.2, and god forbid if you still have 11.2.0.1, that version had a lot if issues.

Now what if you encounter even older versions? Probably younger Oracle database consultants might never even have seen older versions than 11.2.0.1. But what if you need to work with a truly old version? Or are just interested in such an old version to see how it looks like, and what the state of the database was at that version?

For that, I created an automatic installation script to install either:
– Release 10.2 versions: 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5.
– Release 9.2 versions: 9.2.0.4, 9.2.0.5*, 9.2.0.6*, 9.2.0.7, 9.2.0.8.
(*=the patch install is fully scripted, but linking oracle throws an error)

Yes, this is extremely old, and if you must work with it, there has been neglect and somebody not paying attention at a quite massive scale. There are also licensing implications that do not work in your favour there.

There is a huge caveat too: the installation media for these Oracle database versions is not available for download anywhere as far as I know, and some of the patches are restricted downloads on My Oracle Support too. Since it’s Oracle proprietary software, the only way to obtain it is via Oracle.

Outside of these, if you must or want to use these ancient versions, and you got the required files, you can use:
https://gitlab.com/FritsHoogland/ol48_oracle92 for installing Oracle 9.2 on Oracle Linux 4.8 or
https://gitlab.com/FritsHoogland/ol511_oracle102 for installing Oracle 10.2 on oracle Linux 5.11

Clone the repository, put the required files in the files directory, edit the Vagrantfile to your liking and then build the database server by typing ‘vagrant up’.

In case you’re wondering how the operating system images are build, this is done using ‘packer’, I have a repository where you can see how these are build too: https://gitlab.com/FritsHoogland/packer-oracle-linux-museum

In case you’re wondering: there are even older versions; the first public version of the Oracle database on linux, as far as I know, is Oracle 8.0.5. However, the linux version to use with versions like 8.1.7, RHEL/Centos 2.1, is so old that it doesn’t play nicely with VirtualBox and packer, so in al reasonability, Oracle 9.2/oracle linux 4.8 is the earliest version that can be used without severe issues.

This post is about library cache SQL cursors, and how these are managed by the database instance.

Whenever an Oracle database parses a SQL, it follows the following sequence of events (events which are of importance to the topic of cursor children):
1. The SQL text is hashed to generate a hash value.
2. The hash value is used to inspect the session cursor cache of the session to find a cached child.
If a cached entry is found, the child is inspected for compatibility, and then executed.
3. The hash value is used to calculate the library cache hash bucket.
4. The hash bucket is inspected, and the pointer is followed to find the parent handle.
5. In the parent handle a pointer is followed to the parent’s heap 0.
6. In the parent heap 0 a pointer to the hash table with the cursor children followed.
7. The hash table is read from the most recent child down to the oldest child.
If a compatible child is found, that child is executed.
8. If no compatible child is found, a new child is created.

I guess this is reasonably well known.

However, there are some nuances that are interesting to understand.

The first one is that with pluggable databases, a child has the container-id in which the child is generated as a property. During the inspection of the child list, the container-id of the child must match the container-id of the executing session, otherwise the child is skipped. This prevents from getting blocked from sessions across containers. Please mind the library cache hash table and the parent handle are shared between containers, although waiting for these should be negligible. See Oracle multi-tenant and library cache isolation.

Children are inspected from most recently created to the oldest. This is logical, more recent children have the highest probability to have been optimised with the most current state of the data.

Whenever there is no compatible child found, a new child must be created.

Incomplete list insertion.
The first thing that is done to create a new child, is an entry is allocated into the child list for the new child. This is done using the function kkshinins(), and a cursor trace will show this as:

kkshinins insert child into incomplete list bi=0x7ea05290 cld=21 flg=25

This is noteworthy, because to do so, the hash table (child list) mutex must be taken in exclusive mode. If a session wants to inspect the child list, it will briefly take the hash table mutex in shared mode. If the session creating the new child does find such a session, it will wait for ‘cursor: mutex X’. The other way around: if a session wants to inspect the child list, but find it taken by a session in exclusive mode, it will wait for ‘cursor: mutex S’.

I am not sure how to read ‘incomplete list’, and whether this means that the current child list is containing a child not yet complete, or hinting at a separate list of incomplete cursors. What I do know, is that the function kkshinins() inserts the new at this point not yet created (“incomplete”) child as the very last entry of the child list.

This means that at this point, if another session needs to find a compatible child, it will scan the child list and if no children are found compatible, it will find the incomplete child as the last one. At this point once the session finds the incomplete child, it has to wait for it, and will wait for ‘kksfbc child completion’.

Child load.
The session that is creating a new child will continue to create the new child, for which the next step is to allocate the child’s heap 0. This is done in the function kksLoadChild(). After the child’s heap 0 is allocated, it will pin the child in exclusive mode. Sessions that were waiting for ‘kksfbc child completion’ now will wait for ‘cursor: pin S wait on X’. Any other sessions that scanned the child list and did not find any compatible child, will run into this (last) child and wait for ‘cursor: pin S wait on X’ too.

There is a good reason the sessions wait for the child creation. If a session would not wait for the child creation, it would not have found any suitable child, because the incomplete child is the last one on the list, and therefore also would start creating a child. The reason this doesn’t happen is that in such case, there is a fair chance that both sessions would create identical children.

At this point, after the child cursor is pinned in exclusive mode, the session will continue to create the child and perform all the steps it needs to do during parsing. In fact, child generation is the actual thing that is considered ‘hard parse’. This includes syntax checking, semantic checking, execution path selection, optimization, creation of the executable version of the cursor (in heap 6) etc.

Incomplete list removal.
When the child creation is finished, the just created child is taken off the incomplete list (function kkshindel()), and inserted into child list as first entry (function kkshhcins()). This requires exclusive access to the child list once again, so when there is high concurrency, this might yield ‘cursor: mutex X’ for the session creating the child, and/or ‘cursor: mutex S’ for the sessions willing to scan the child list. A cursor trace shows:

kkshindel remove child from incomplete list bi=0x7ea05290 cld=21 flg=30
kkshhcins insert child into hash table bi=0x7ea05290 cld=21 flg=38

I am not sure if it’s a change in the child’s status (perhaps the ‘flg’ field shown in the trace?) being taken off the incomplete list, but what does happen is that the child after being taken off the incomplete list is inserted as first entry on the list of children. The next step is that the exclusive pin of this child is changed to shared. Any sessions that were waiting for child creation in ‘cursor: pin S wait on X’ now will continue and can inspect the child to check compatibility, and if found compatible execute the newly created child. If the child it was waiting for was not compatible, it has run out of children to check and the session will start creating a new child.

This post is the result of a question that I got after presenting a session about Oracle database mutexes organised by ITOUG, as a response to the conference cancellations because of COVID-19. Thank Gianni Ceresa for asking me!

The library cache provides shared cursors and execution plans. Because they are shared, sessions can take advantage of the work of previous sessions of creating these. However, by having these shared, access needs to be regulated not to have sessions overwrite each other’s work. This is done by mutexes.

The question I got was (this is a paraphrased from my memory): ‘when using pluggable databases, could a session in one pluggable database influence performance of a session in another pluggable database’?

The answer I gave was that I didn’t test a multi-tenant scenario, but because the library cache is shared between the pluggable databases, it should be possible for a session in one pluggable database to block another session in another pluggable database.

So let’s test it!

I used an Oracle version 20.2 database, which automatically gives you a pluggable database. In fact, even when you don’t specify you want a multi-tenant database, it will create one. This is as expected and documented.

I created an instance called o202, and two pluggable databases, PDB1 and PDB2. Yes, I am a creative genius.

I logged in to PDB1 and executed a ‘select 8 from dual’.

SQL> show con_id

CON_ID
------------------------------
3

SQL> select 8 from dual;

         8
----------
         8

Now using another session in the root container, I dumped the library cache at level 16 to see how that looks like for the ‘select 8 from dual’ cursor:

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set events 'immediate trace name library_cache level 16';

Session altered.

(don’t do this on a live environment!)
These are snippets of certain parts of the dump:

Bucket: #=100089 Mutex=0x71f7f8d8(627065225216, 6, 0, 6)
  LibraryHandle:  Address=0x67bcca90 Hash=372d86f9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select 8 from dual
      FullHashValue=f18bf11763dd069341c61ce6372d86f9 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=925730553 OwnerIdn=9
...
    Concurrency:  DependencyMutex=0x67bccb40(0, 1, 0, 0) Mutex=0x67bccbe0(146, 22, 0, 6)
...
        Child:  childNum='0'
          LibraryHandle:  Address=0x6e6b3fd8 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=3
...
            Concurrency:  DependencyMutex=0x6e6b4088(0, 0, 0, 0) Mutex=0x67bccbe0(146, 22, 0, 6)

This is bucket 100089, with its mutex. The counter for this mutex is the second argument, which is 6.
The library cache handle on the next line is the parent handle. The sql text and the hash value sit with the parent handle.
I am not sure how to interpret the “ContainerId” here, it says 1 (indicating the root container), while this SQL is only executed in container 3, which is shown above.
After the first ‘…’, the two mutexes in the parent handle can be seen: the dependency mutex, and the parent handle mutex.
After the second ‘…’, child number 0 is visible. Here the container is specified from which this child was actually executed.
After the third ‘…’, the concurrency information for the child is shown. The important bit is the dependency mutex is independent/unique, whilst the regular mutex is the same/shared from the parent.

Now what would happen if I execute the exact same SQL (select 8 from dual) in another container; PDB2 alias container id 4?

SQL> show con_id

CON_ID
------------------------------
4

SQL> select 8 from dual;

         8
----------
         8

And look at bucket 100089 again (using the dump of the library cache at level 16):

Bucket: #=100089 Mutex=0x73f81e58(8589934592, 4, 0, 6)
  LibraryHandle:  Address=0x656e2638 Hash=372d86f9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select 8 from dual
      FullHashValue=f18bf11763dd069341c61ce6372d86f9 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=925730553 OwnerIdn=9
...
    Concurrency:  DependencyMutex=0x656e26e8(0, 2, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)
...
        Child:  childNum='0'
          LibraryHandle:  Address=0x656e0ed8 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=3
...
            Concurrency:  DependencyMutex=0x656e0f88(0, 0, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)
...
        Child:  childNum='1'
          LibraryHandle:  Address=0x655fba58 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
            Name:  Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=4
...
            Concurrency:  DependencyMutex=0x655fbb08(0, 0, 0, 0) Mutex=0x656e2788(2, 31, 0, 6)

Executing an identical SQL statement (which is a cursor object in the library cache) in another PDB means the same library cache hash bucket is used, and container id is one of the sharing criteria for a child. Because the statement in both cases has a different container id, the cursors can not be shared. This is very logical if you think about it: both containers essentially are logically isolated databases, and therefore there is no way to tell if the tables, access rights, data, really anything is the same, so the SQL is parsed again for the other container.

This has some implications. Because of identical SQL, it hashes to the same value, which mean both containers need access to the same bucket to find the pointer to the parent handle. Both containers also need access to the same parent and the same heap 0 to obtain the child list to find their child. Both the bucket and the handle have a single mutex that serializes access.

Access to the parent heap 0 is done shared, and remains shared if a compatible child can be found.

However, as soon as a new child needs to be created for the same SQL, it will create the new child handle pinned in exclusive mode and insert it into the hash table/child list in the parent’s heap 0. The new child is pinned in exclusive mode, because it still needs to allocate the child heaps and create the child information. This new child is the newest entry, and therefore is the first one any session would find if it scans for a compatible child.

At this point multi-tenancy makes a difference:
– If a session in the same container starts parsing and follows the procedure of hashing the SQL text, obtaining the bucket, finding the parent handle and then scan the child list for a compatible child, it will find the child in exclusive mode for this container, and waits for the child creation to finish, waiting on the event: ‘cursor: pin S wait on X’.
– If a session in ANOTHER container starts parsing and follows the procedure of hashing the SQL text, obtaining the bucket, finding the parent handle and then scan the child list for a compatible child, it will find the child in exclusive mode, but because it has a different container id, it will skip the child, and continue to scan the child list for compatible children. The scanning means pinning each child that potentially can be compatible, which means has the same container id, in shared mode, and by doing that either finds a compatible child, or if it doesn’t, create one itself.

So what does that mean; the conclusion:
The library cache hash table, the parent cursor and the child list/hash table are shared between all containers. These require unique (library cache hash table/handle) or shared (child list/hash table) access for each database process for usage, but the actual time it is held in these modes is so short that it is negligible.
The main reason for waiting for a cursor/library cache entry is a child pinned in exclusive mode during creation (‘cursor: pin S wait on X’).
When an identical statement is executed in another container, it will skip a child that is created or in the process of being created in another container, even when it’s pinned in exclusive mode.

This means that the multi-tenant option in my opinion does not significantly increase the risk of waiting because of library cache concurrency, specifically because it can skip child cursors pinned exclusively in another container.

Update april 4: I tested having a child from one container being pinned in X mode and running the same SQL in another container in 12.1.0.2.0 (base release of 12.1.0.2) and it works exactly the same as described above. So whilst the library cache hash table and parent handles are shared, the cursor children are specific to a container and do not lock each other out.

This blogpost is about the Oracle database row or dictionary cache. This is a separate cache that caches database metadata, like database object properties or user properties.

There is surprising little in-depth technical detail about the row cache. To some degree I understand: issues with the row cache are rare.

I noticed a column in V$ROWCACHE called ‘FASTGETS’. Whatever FASTGETS means, in my database it is being used:

SQL> select cache#, type, parameter, gets, fastgets, getmisses from v$rowcache;
    CACHE# TYPE        PARAMETER                              GETS   FASTGETS  GETMISSES
---------- ----------- -------------------------------- ---------- ---------- ----------
         3 PARENT      dc_rollback_segments                   1000          9         12
         1 PARENT      dc_free_extents                           0          0          0
         4 PARENT      dc_used_extents                           0          0          0
         2 PARENT      dc_segments                            4374       7308       2496
         0 PARENT      dc_tablespaces                         1232      14466        178
         5 PARENT      dc_tablespace_quotas                      0          0          0
         6 PARENT      dc_files                                  5          0          5
        10 PARENT      dc_users                                953      13473        196
         8 PARENT      dc_objects                            32545      13682       3769
        17 PARENT      dc_global_oids                          723          0        165
        12 PARENT      dc_constraints                            0          0          0
        13 PARENT      dc_sequences                              9          0          9
        16 PARENT      dc_histogram_defs                     60851          0      19613

If you look at the description of FASTGETS in the documentation (currently the 20c version), it says: ‘Reserved for internal use’. That’s not very helpful.

Luckily, there is a trace event to make Oracle print additional information regarding the row cache: event 10222.

Please mind using events is an unsupported action unless you have explicit blessing from Oracle support to use it. Even then, it should only be set if you are 100% sure you need it, and you should unset it as soon as you can. Also document the exact reason for using it, and evaluate set events during patching and version upgrades.

Using the event, I found that a normal dictionary lookup looks like this:

kqrpre: start hash=6d2c659c mode=S keyIndex=1 dur=CALL opt=FALSE
kqrpre: found cached object po=0x7e5da720 flg=2
kqrmpin : kqrpre: found po Pin po 0x7e5da720 cid=10 flg=2 hash=70b9dd3f
time=3663934334
kqrpre: pinned po=0x7e5da720 flg=2 pso=0x793472c8
kqrpre: done po=0x7e5da720 cid=10 flg=2 eq=0x7e616a20 pso=0x793472c8 dur=CALL
kqrpre: keyIndex=1 hash=70b9dd3f 6d2c659c 0
kqrpre: time=3663934346
kqrpre: returnVal: TRUE
.
kqrprl: eq=0x7e616a20 fpInUse=FALSE
ksedsts()+418<-kqrprl()+1208<-kkdlGetBaseUser()+248<-kzulgt1()+220<-kksLockUserSchema()+125<-kksLoadChild()+1983<-kxsGetRuntimeLock()+2049<-kksfbc()+17007<-kkspsc0()+2222<-kksParseCursor()+123<-opiosq0()+2266<-kpoopr
x()+420<-kpoal8()+838<-opiodr()+1244<-ttcpip()+1239
<-opitsk()+1943<-opiino()+957<-opiodr()+1244<-opidrv()+1091<-sou2o()+191<-opimai_real()+455<-ssthrdmain()+423<-main()+275<-__libc_start_main()+245.

This is a bit cryptic, but this is a row cache parent read (kqrpre) and release (kqrprl), for cache id (cid) 10 (dc_users, see above v$rowcache output), for which the needed entry was found (found cached object). It even contains a short stack format dump of the current location of invocation.

Now see the following lookup:

kqrpre: start hash=70b9dd3f mode=S keyIndex=0 dur=CALL opt=TRUE
kqrpre: optimistic lookup hash=70b9dd3f
kqrpre: KQR_READ_PD_VER: versionAddr=0x628f2a20 version=10
kqrpre: optimistic lookup: success
kqrpre: done po=0x7f651cbb1bf8 cid=10 flg=0 eq=(nil) pso=0x79360288 dur=CALL
kqrpre: keyIndex=0 hash=70b9dd3f 6d2c659c 0
kqrpre: time=3663936115
kqrpre: returnVal: TRUE
.
kqrprl: eq=0x7f651cbb1bf8 fpInUse=TRUE
ksedsts()+418<-kqrprl()+1208<-kkdlGetUserId()+587<-kqlhdlod()+641<-kqlCallback()+120<-kqllod()+667<-kglLoadOnLock()+1107<-kgllkal()+861<-kglLock()+1429<-kglget()+302<-kglgob()+328<-qcdlgbo()+609<-qcdlgob()+982<-qcsfgob()+278<-qcsprfro()+553<-qcsprfro_tree()+380
<-qcsprfro_tree()+150<-qcspafq()+246<-qcspqbDescendents()+281<-qcspqb()+272<-kkmdrv()+192<-opiSem()+1962<-opiDeferredSem()+447<-opitca()+436<-kksFullTypeCheck()+86<-rpiswu2()+583<-kksLoadChild()+7969<-kxsGetRuntimeLock()+2049<-kksfbc()+17007<-kkspsc0()+2222
<-kksParseCursor()+123<-opiosq0()+2266<-kpooprx()+420<-kpoal8()+838<-opiodr()+1244<-ttcpip()+1239<-opitsk()+1943<-opiino()+957<-opiodr()+1244<-opidrv()+1091<-sou2o()+191<-opimai_real()+455<-ssthrdmain()+423<-main()+275<-__libc_start_main()+245.

This is lookup for cid 10 (dc_users) too, but now “optimistic”, as can be seen on line 2. The first line shows “opt=TRUE”, which gives the indication that optimistic reading is probably not done dynamically, but an explicit setting. Could this “optimistic” read be what is shown as FASTGET in v$rowcache?

Let’s look at the actual C functions that are called for such an optimistic row cache read:

> kqrpre1(0xa, 0x7ffc48561380, ...)
| > kqrpre2(0xa, 0x7ffc48561380, ...)
| | > kqrhsh(0x62f471c8, 0x7ffc48561380, ...)
| | | > kgghash(0x7ffc48561380, 0x82, ...)
| | | < kgghash+0x0000000000d0 returns: 0xc34886c9
| | < kqrhsh+0x00000000007d returns: 0xc34886c9
| | > KQR_READ_PD_VER(0x62f471c8, 0x628c7670, ...)
| | < KQR_READ_PD_VER+0x000000000044 returns: 0x4a
| | > kqrFastPoRead(0x62f471c8, 0x7ffc48561380, ...)
| | | > _intel_fast_memcmp(0x7ffc48561380, 0x78fc6834, ...)
| | | < _intel_fast_memcmp+0x000000000053 returns: 0
| | | > kglInstanceIdn(0x7f1bc9f099a0, 0x78fc6800, ...)
| | | <> kglHandleIdn(0x7f1bc9f099a0, 0x7c736fb0, ...)
| | | < kglHandleIdn+0x000000000011 returns: 0x1
| | | > kglPdbInstanceIdn(0x7f1bc9f099a0, 0x7c736fb0, ...)
| | | < kglPdbInstanceIdn+0x000000000024 returns: 0x1
| | < kqrFastPoRead+0x0000000001ac returns: 0x1

Here we see kqrpre1 call kqrpre2, which calls kqrhsh to calculate a hash value to lookup the row cache entry. Then we see KQR_READ_PD_VER being called, which is unique to the optimistic lookup, and then kqrFastPoRead. That seems to be a reasonable definite indication this is doing the FASTGET.

The logical question to ask then is what is the actual difference between a fastget and a (regular) get?

Looking through the function calls of a fastget versus a regular get the thing that strikes me is the absence of concurrency checking and protection gets in the fastget alias optimistic call. A regular row cache get performs:
– obtains the row cache hash bucket mutex
– obtains the row cache parent object mutex
– pins the (row cache entry) memory
– frees the row cache hash bucket mutex
– locks the row cache entry (kqrget)
– frees the row cache parent object mutex

– the row cache data is used (this is the actual purpose of the row cache get)

– obtains the row cache parent object mutex
– unpins the (row cache entry) memory
– frees the row cache parent object mutex
– frees the lock (there is no explicit unlock call, I assume it’s done via the state object)

A fastget does simply not do a lot of the concurrency protection measures, it checks the version of the entry before and after getting the entry, and apparently that’s enough to be sure about the consistency of the row cache entry. It does lock the entry, and releases it after usage. Especially since the number of row cache lookups can easily get very high, this probably saves a lot of CPU cycles.

Looking in the bigger picture, the fastget seems to be done at times when the database executable has fetched the same entry in a regular way earlier, so it’s safe to assume it will still be there, and it’s safe to do it without all the concurrency control around it.

Is there anything you can do about it? Not that I know, this is just a blog to explain something about which there is no documentation.

In case you’re wondering when this appeared in Oracle, the answer is 11.2.0.4. The FASTGETS column appeared in that function, as well as the C function kqrFastPoRead.

There’s also the undocumented parameter “_kqr_optimistic_reads”, which is TRUE in my (Oracle 18) instance.

This post is about one of the fundamentally important properties of a database: how IO is done. The test case I studied is doing a simple full table scan of a single large table. In both Oracle and postgres the table doesn’t have any indexes or constraints, which is not a realistic example, but this doesn’t change the principal topic of the study: doing a table scan.

I used a publicly available dataset from the US bureau of transportation statistics called FAF4.5.1_database.zip
The zipped file is 347MB, unzipped size 1.7GB.

In both cases Oracle Linux 7.7 (64 bit) is used, running in VirtualBox, with the storage being a USB3 SSD. Number of CPUs is 4, memory size is 6G. Filesystem type: xfs.
The Oracle version used is Oracle 19.5, the Postgresql version used is 12.1.
For Postgresql, the postgresql.conf file is not changed, except for max_parallel_workers_per_gather which is set to 0 to make postgres use a single process.
For Oracle, the parameters that I think are important: filesystemio_options=’setall’. Oracle is used filesystem based (so no ASM).

This is the table definition for Oracle:

create table faf451 (
  fr_origin varchar2(3),
  dms_orig varchar2(3),
  dms_dest varchar2(3),
  fr_dest varchar2(3),
  fr_inmode varchar2(1),
  dms_mode varchar2(1),
  fr_outmode varchar2(1),
  sctg2 varchar2(2),
  trade_type varchar2(1),
  tons number,
  value number,
  tmiles number,
  curval number,
  wgt_dist number,
  year varchar2(4)
);

This is the table definition for Postgresql:

create table faf451 (
  fr_origin varchar(3),
  dms_orig varchar(3),
  dms_dest varchar(3),
  fr_dest varchar(3),
  fr_inmode varchar(1),
  dms_mode varchar(1),
  fr_outmode varchar(1),
  sctg2 varchar(2),
  trade_type varchar(1),
  tons double precision,
  value double precision,
  tmiles double precision,
  curval double precision,
  wgt_dist double precision,
  year varchar(4)
);

In order for the data to be easy loadable into postgres using copy from, I had to remove ‘””‘ (double double quotes) for the empty numeric fields. In oracle I could say “optionally enclosed by ‘”‘”. For Oracle I used an external table definition to load the data.

Now, before doing any benchmarks, I have an idea where this is going. Oracle is using direct IO (DIO) so linux page cache management and “double buffering” are avoided. Also, oracle will be doing asynchronous IO (AIO), which means submitting is separated from waiting for the notification that the submitted IOs are ready, and on top of that oracle will submit multiple IO requests at the same time. And again on top of that, oracle does multi-block IO, which means that instead of requesting each 8K database block individually, it will group adjacent blocks and request for these in one go, up to a size of combined blocks of 1MB, which means it can requests up to 128 8K blocks in one IO. Postgres will request every block synchronous, so 1 8K block at a time, and waiting for each request to finish. That makes me have a strong idea where this is going.

It should be noted that postgres explicitly is depending on the operating system page cache for buffering as a design principle. Because of DIO, blocks that are read by oracle are not cached in the operating system page cache.

I executed my benchmark in the following way:
– A run for every size is executed 5 times.
– At the start of every run for a certain size (so before every “batch” of 5 runs), the page cache is flushed: (echo 3 > /proc/sys/vm/drop_caches).
– Before each individual run, the database cache is flushed (systemctl restart postgresql-12 for postgres, alter system flush buffer_cache for oracle).

I started off with 2G from the dataset, and then simply performed a ‘copy from’ again to load the same dataset into the table in postgres. Oracle required a bit more of work. Oracle was able to save the same data in way less blocks; the size became 1.18G. In order to have both postgres and oracle scan the same amount of data, I calculated roughly how much rows I needed to add to the table to make it 2G, and copied that table to save it as a 2G table, so I could insert that table to increase the size of the test table by 2G. This way in both oracle and postgres I could test with a 2G table and add 2G at a time until I reached 20G.

These are the results. As you can see in the legenda: oracle is orange, postgres is blue.
postgres oracle scan results(click graph to load full picture)

What we see, is that postgres is a bit slower with the first run of 5 for the smaller dataset sizes, which becomes less visible with larger datasets.
Also, postgres is way faster if the dataset fits into the page cache and it has been read into it. This is logical because postgres explicitly uses the page cache as a secondary cache, and the test is the only activity on this server, so it hasn’t been flushed by other activity.

What was totally shocking to me, is postgres is performing alike oracle and both roughly are able to perform at the maximum IO speed of my disk: 300MB/s, especially when the dataset is bigger, alias beyond the page cache size.

It wasn’t shocking that oracle could reach the total bandwidth of the disk: oracle uses all the techniques to optimise IO for bandwidth. But how can postgres do the same, NOT deploying these techniques, reading 8K at a time??

The first thing to check is whether postgres is doing something else than I suspected. This can simply be checked with strace:

poll_wait(3, [{EPOLLIN, {u32=18818136, u64=18818136}}], 1, -1) = 1
recvfrom(11, "Q\0\0\0!select count(*) from faf451"..., 8192, 0, NULL, NULL) = 34
lseek(20, 0, SEEK_END)                  = 335740928
lseek(20, 0, SEEK_END)                  = 335740928
kill(1518, SIGUSR1)                     = 0
pread64(5, "\f\0\0\0\310ILc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846061568) = 8192
pread64(5, "\f\0\0\0HcLc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846069760) = 8192
pread64(5, "\f\0\0\0\260|Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846077952) = 8192
pread64(5, "\f\0\0\0000\226Lc\0\0\0\0D\1\210\1\0 \4 \0\0\0\0\230\237\312\0000\237\312\0"..., 8192, 846086144) = 8192
…etc…

The above strace output shows only 4 rows of pread64() calls, but this goes on. So no “secret” optimisation there.

Luckily, my VM has a new enough version of Linux for it to be able to use eBPF, so I can use biosnoop. Biosnoop is a tool to look at IO on one of the lower layers of the linux kernel, the block device interface (hence ‘bio’). This is the biosnoop output:

# /usr/share/bcc/tools/biosnoop
TIME(s)        COMM           PID    DISK    T  SECTOR    BYTES   LAT(ms)
0.000000000    postmaster     4143   sdb     R  66727776  708608     5.51
0.006419000    postmaster     4143   sdb     R  66731720  77824     11.06
0.006497000    postmaster     4143   sdb     R  66734432  786432    11.03
0.011550000    postmaster     4143   sdb     R  66731872  1310720   16.17
0.013470000    postmaster     4143   sdb     R  66729160  1310720   18.86
0.016439000    postmaster     4143   sdb     R  66735968  1310720   14.61
0.019220000    postmaster     4143   sdb     R  66738528  786432    15.20

Wow…so here it’s doing IOs of up to 1MB! So somewhere between postgres itself and the block device, the IOs magically grew to sizes up to 1MB…that’s weird. The only thing that sits between postgres and the block device is the linux kernel, which includes page cache management.

To get an insight into that, I ran ‘perf record -g -p PID’ during the scan, and then perf report to look at the recorded perf data. This is what is I found:

Samples: 21K of event 'cpu-clock', Event count (approx.): 5277000000
  Children      Self  Command     Shared Object       Symbol                                                                  ◆
-   41.84%     3.63%  postmaster  libpthread-2.17.so  [.] __pread_nocancel                                                    ▒
   - 38.20% __pread_nocancel                                                                                                  ▒
      - 38.08% entry_SYSCALL_64_after_hwframe                                                                                 ▒
         - 37.95% do_syscall_64                                                                                               ▒
            - 35.87% sys_pread64                                                                                              ▒
               - 35.51% vfs_read                                                                                              ▒
                  - 35.07% __vfs_read                                                                                         ▒
                     - 34.97% xfs_file_read_iter                                                                              ▒
                        - 34.69% __dta_xfs_file_buffered_aio_read_3293                                                        ▒
                           - 34.32% generic_file_read_iter                                                                    ▒
                              - 21.10% page_cache_async_readahead                                                             ▒
                                 - 21.04% ondemand_readahead                                                                  ▒
                                    - 20.99% __do_page_cache_readahead                                                        ▒
                                       + 14.14% __dta_xfs_vm_readpages_3179                                                   ▒
                                       + 5.07% __page_cache_alloc                                                             ▒
                                       + 0.97% radix_tree_lookup                                                              ▒
                                       + 0.54% blk_finish_plug                                                                ▒

If you look at rows 13-15 you see that the kernel is performing readahead. This is an automatic function in the linux kernel which looks if the requests are sequential of nature, and when that’s true performs readahead, so that the scan is made faster.

For the difference between Oracle database versions 12.2.0.1.191015 and 12.2.0.1.200114 this too follows the line of a low amount of differences.

There have been two spare parameters that have been changed to named undocumented parameters, and no data dictionary changes.

parameters unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME
--------------------------------------------------
_fifth_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter

parameters unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_enable_ptime_update_for_sys

On the C function side, there have been a group of AWR functions that have been removed and a group of SGA management functions, among other functions. There functions that have been added are random and diverse.

code symbol names unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
R_CR_entropy_resource_init                                   R_CR_entropy_resource_init                                   ??
kcbzdra                                                      (kcbz)dra                                                    kernel cache buffers subroutines for kcb ??
kdmsCreateSampleInvBlkList                                   (kdm)sCreateSampleInvBlkList                                 kernel data in-memory data layer ??
kdmsFillSampleList                                           (kdm)sFillSampleList                                         kernel data in-memory data layer ??
kewmfdms_flush_drmsum                                        (kewm)fdms_flush_drmsum                                      kernel event AWR metrics ??
kewmgaeidct                                                  (kewm)gaeidct                                                kernel event AWR metrics ??
kewmusmdb_update_smdbuf                                      (kewm)usmdb_update_smdbuf                                    kernel event AWR metrics ??
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??
kewrfosp2_fos_mdb_part2                                      (kewrf)osp2_fos_mdb_part2                                    kernel event AWR repository flush ??
kewrfosp3_fos_mdb_part3                                      (kewrf)osp3_fos_mdb_part3                                    kernel event AWR repository flush ??
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??
kewrggd_get_group_descriptor                                 (kewr)ggd_get_group_descriptor                               kernel event AWR repository ??
kewrggf_grp_get_flags                                        (kewr)ggf_grp_get_flags                                      kernel event AWR repository ??
kewrggh_grp_get_handle                                       (kewr)ggh_grp_get_handle                                     kernel event AWR repository ??
kewrggmc_grp_get_member_count                                (kewr)ggmc_grp_get_member_count                              kernel event AWR repository ??
kewrgltn_gen_lrgtest_tab_name                                (kewr)gltn_gen_lrgtest_tab_name                              kernel event AWR repository ??
kewrgvm_grp_valid_member                                     (kewr)gvm_grp_valid_member                                   kernel event AWR repository ??
kewrice_is_cache_enabled                                     (kewr)ice_is_cache_enabled                                   kernel event AWR repository ??
kewrmfp_map_flush_phase                                      (kewr)mfp_map_flush_phase                                    kernel event AWR repository ??
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??
kewrrc_release_cache                                         (kewr)rc_release_cache                                       kernel event AWR repository ??
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kkeutlCopyAllocatorState                                     (kke)utlCopyAllocatorState                                   kernel compile cost engine ??
kkeutlIsAllocStructureSame                                   (kke)utlIsAllocStructureSame                                 kernel compile cost engine ??
kmgs_check_uninited_comp                                     (kmgs)_check_uninited_comp                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_partial_inuse_list_comp                            (kmgs)_dump_partial_inuse_list_comp                          kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_quiesce_list                                       (kmgs)_dump_quiesce_list                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_resize_summary                                     (kmgs)_dump_resize_summary                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_fill_start_sizes                                        (kmgs)_fill_start_sizes                                      kernel multi threaded/mman manage (sga) space (?) ??
kmgs_get_min_cache_grans                                     (kmgs)_get_min_cache_grans                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_getgran_from_comp_pg                                    (kmgs)_getgran_from_comp_pg                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_init_sgapga_comps                                       (kmgs)_init_sgapga_comps                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_nvmksmid_2_kcbpoolid                                    (kmgs)_nvmksmid_2_kcbpoolid                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_recv_and_donor_are_caches                               (kmgs)_recv_and_donor_are_caches                             kernel multi threaded/mman manage (sga) space (?) ??
kmgs_shrink_gran                                             (kmgs)_shrink_gran                                           kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_param_manual_helper                              (kmgs)_update_param_manual_helper                            kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_resize_summary                                   (kmgs)_update_resize_summary                                 kernel multi threaded/mman manage (sga) space (?) ??
kmgsb_in_range                                               (kmgs)b_in_range                                             kernel multi threaded/mman manage (sga) space (?) ??
kmgsdpgl                                                     (kmgs)dpgl                                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgsset_timestamp                                            (kmgs)set_timestamp                                          kernel multi threaded/mman manage (sga) space (?) ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrte                                                      (krvx)rte                                                    kernel redo recovery extract ??
kslsesftcb_int                                               (ksl)sesftcb_int                                             kernel service  latching and post-wait ??
ksmg_estimate_sgamax                                         (ksm)g_estimate_sgamax                                       kernel service  memory ??
ktcxbFlgPrint                                                (ktc)xbFlgPrint                                              kernel transaction control component ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
kzekmdcw                                                     (kz)ekmdcw                                                   kernel security ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qksbgUnderOFE                                                (qksbg)UnderOFE                                              query kernel sql bind (variable) management(?) ??
ri_entcb_cmd_func                                            ri_entcb_cmd_func                                            ??
zt_yield_entropy_source_cb                                   (zt)_yield_entropy_source_cb                                 security encryption ??

code symbol names unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
apagwnrn                                                     (apa)gwnrn                                                   SQL Access Path Analysis ??
apagwnrnprd                                                  (apa)gwnrnprd                                                SQL Access Path Analysis ??
apatwnrn                                                     (apa)twnrn                                                   SQL Access Path Analysis ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbz_eff_bsz                                                 (kcbz)_eff_bsz                                               kernel cache buffers subroutines for kcb ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdsReadAheadSafe                                             (kds)ReadAheadSafe                                           kernel data seek/scan ??
kfdFreeReqs                                                  (kfd)FreeReqs                                                kernel automatic storage management disk ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kghunalo                                                     (kgh)unalo                                                   kernel generic heap manager ??
kjcts_syncseq_incident_dump                                  (kjc)ts_syncseq_incident_dump                                kernel lock management communication ??
kkfdIsXlate                                                  (kkfd)IsXlate                                                kernel compile fast dataflow (PQ DFO) ??
kkoRowNumLimit_Int                                           (kko)RowNumLimit_Int                                         kernel compile optimizer ??
kkoWnRowNumLimit                                             (kko)WnRowNumLimit                                           kernel compile optimizer ??
kkoarFreeStats                                               (kkoar)FreeStats                                             kernel compile optimizer automatic (sql) reoptimisation ??
kkqgbpValidPredCB                                            (kkqgbp)ValidPredCB                                          kernel compile query  group by placement ??
kkqoreApplyFKR                                               (kkqore)ApplyFKR                                             kernel compile query  or-expansion ??
kkqstIsOneToOneFunc                                          (kkq)stIsOneToOneFunc                                        kernel compile query  ??
kkquReplSCInMWithRefCB                                       (kkqu)ReplSCInMWithRefCB                                     kernel compile query  subquery unnesting ??
kkqvtOpnInView                                               (kkqvt)OpnInView                                             kernel compile query  vector transformation ??
kokujJsonSerialize                                           (kok)ujJsonSerialize                                         kernel objects kernel side ??
kpdbCheckCommonprofileCbk                                    (kpdb)CheckCommonprofileCbk                                  kernel programmatic interface pluggable database ??
kpdbSyncCreateProfile                                        (kpdbSync)CreateProfile                                      kernel programmatic interface pluggable database DBMS_PDB.KPDBSYNC SYNC_PDB ??
krvfptai_PutTxAuditInfo                                      (krv)fptai_PutTxAuditInfo                                    kernel redo recovery ??
krvtab                                                       (krvt)ab                                                     kernel redo recovery log miner viewer support ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
ksmg_estimate_nonimc_sga_size                                (ksm)g_estimate_nonimc_sga_size                              kernel service  memory ??
ktspFetchMeta1                                               (ktsp)FetchMeta1                                             kernel transaction segment management segment pagetable ??
kzekmckdcw                                                   (kz)ekmckdcw                                                 kernel security ??
kzekmckdcw_cbk                                               (kz)ekmckdcw_cbk                                             kernel security ??
opiBindReorderInfo                                           (opi)BindReorderInfo                                         oracle program interface ??
qcpiJsonSerialize                                            (qcpi)JsonSerialize                                          query compile parse interim ??
qcsSqnLegalCB                                                (qcs)SqnLegalCB                                              query compile semantic analysis (parser) ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qesblZero                                                    (qesbl)Zero                                                  query execute services bloom filter ??
qjsnIsDollarOnly                                             (qjsn)IsDollarOnly                                           query json ??
qjsnJsonCreatDom                                             (qjsn)JsonCreatDom                                           query json ??
qjsn_ferrh                                                   (qjsn)_ferrh                                                 query json ??
qkaGetClusteringFactor                                       (qka)GetClusteringFactor                                     query kernel allocation ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql compilter operand processing ??
qksqbCorrToNonParent                                         (qksqb)CorrToNonParent                                       query kernel sql Query compilation for query blocks ??
qksvcCloneHJPred                                             (qksvc)CloneHJPred                                           query kernel sql Virtual Column ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

For the difference between Oracle database versions 18.8 and 18.9 this too follows the line of a low amount of differences.

As always, there are some parameters that have changed from being undocumented spare to being undocumented with a name.

Also, the DBA and CDB table (DBA|CDB)_REGISTRY_BACKPORTS is back again. The disappearance of this table in 18.8 turned out to be a bug. There is a patch for 18.8 if you need this table.

parameters unique in version 18.8 versus 18.9

NAME
--------------------------------------------------
_eighth_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter
_second_spare_parameter

parameters unique in version 18.9 versus 18.8

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_enable_ptime_update_for_sys
_session_modp_list

dba tables unique to 18.8 versus 18.9

dba tables unique to 18.9 versus 18.8

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

cdb tables unique to 18.8 versus 18.9

cdb tables unique to 18.9 versus 18.8

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

On the C function side, it becomes apparent that this update is truly an update, roughly the number of functions that have gone and appeared are the same. A large portion of the functions that are removed in 18.9 are functions that have to do with AWR, and some other functions dealing with ASM and others.

The functions that have been added seem to be quite diverse,

code symbol names unique in version 18.8 versus 18.9

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
R_CR_entropy_resource_init                                   R_CR_entropy_resource_init                                   ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kds_update_turbo_scan_pivot_statistics                       (kds)_update_turbo_scan_pivot_statistics                     kernel data seek/scan ??
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??
kewrbtsg_build_topseg                                        (kewr)btsg_build_topseg                                      kernel event AWR repository ??
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??
kewrrdsi_rank_dstat_item                                     (kewr)rdsi_rank_dstat_item                                   kernel event AWR repository ??
kewrrtsq_rank_topsql                                         (kewr)rtsq_rank_topsql                                       kernel event AWR repository ??
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??
kfatknHsh                                                    (kfa)tknHsh                                                  kernel automatic storage management alias operations ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
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 ??
kkqtutlSelItemMatches                                        (kkqt)utlSelItemMatches                                      kernel compile query  table ??
kkquIsCorrTransInList                                        (kkqu)IsCorrTransInList                                      kernel compile query  subquery unnesting ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrsr_SkipRedo                                             (krvx)rsr_SkipRedo                                           kernel redo recovery extract ??
ksmg_estimate_sgamax                                         (ksm)g_estimate_sgamax                                       kernel service  memory ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
lxCharsetIsByteUnique                                        (l)xCharsetIsByteUnique                                      core library functions ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qkspmTravInit                                                (qkspm)TravInit                                              query kernel sql plan management ??
ri_entcb_cmd_func                                            ri_entcb_cmd_func                                            ??
zt_yield_entropy_source_cb                                   (zt)_yield_entropy_source_cb                                 security encryption ??

code symbol names unique in version 18.9 versus 18.8

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
apagwnrn                                                     (apa)gwnrn                                                   SQL Access Path Analysis ??
apagwnrnprd                                                  (apa)gwnrnprd                                                SQL Access Path Analysis ??
apatwnrn                                                     (apa)twnrn                                                   SQL Access Path Analysis ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbz_eff_bsz                                                 (kcbz)_eff_bsz                                               kernel cache buffers subroutines for kcb ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdsReadAheadSafe                                             (kds)ReadAheadSafe                                           kernel data seek/scan ??
kewrcc_bind_cb                                               (kewr)cc_bind_cb                                             kernel event AWR repository ??
kewrcc_check_columns                                         (kewr)cc_check_columns                                       kernel event AWR repository ??
kewrcc_fetch_cb                                              (kewr)cc_fetch_cb                                            kernel event AWR repository ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kfkIsAFDLoaded                                               (kfk)IsAFDLoaded                                             kernel automatic storage management KFK ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kjcts_syncseq_incident_dump                                  (kjc)ts_syncseq_incident_dump                                kernel lock management communication ??
kkoRowNumLimit_Int                                           (kko)RowNumLimit_Int                                         kernel compile optimizer ??
kkoWnRowNumLimit                                             (kko)WnRowNumLimit                                           kernel compile optimizer ??
kkqljpUpdateXplAnn                                           (kkq)ljpUpdateXplAnn                                         kernel compile query  ??
kkqoreAndDriver                                              (kkqore)AndDriver                                            kernel compile query  or-expansion ??
kpcxdrBindReorderInfo                                        (kp)cxdrBindReorderInfo                                      kernel programmatic interface ??
kpdbCheckCommonprofileCbk                                    (kpdb)CheckCommonprofileCbk                                  kernel programmatic interface pluggable database ??
kpdbSyncCreateProfile                                        (kpdbSync)CreateProfile                                      kernel programmatic interface pluggable database DBMS_PDB.KPDBSYNC SYNC_PDB ??
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 ??
kpussTemplateSend                                            (kpu)ssTemplateSend                                          kernel programmatic interface user ??
kpuxcSessionSignatureRecv                                    (kpuxc)SessionSignatureRecv                                  kernel programmatic interface user db replay? ??
kpuxcSessionTemplateSend                                     (kpuxc)SessionTemplateSend                                   kernel programmatic interface user db replay? ??
kpuxcSessionTemplatesFree                                    (kpuxc)SessionTemplatesFree                                  kernel programmatic interface user db replay? ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
ksmg_estimate_nonimc_sga_size                                (ksm)g_estimate_nonimc_sga_size                              kernel service  memory ??
ksp_init_modp_send                                           (ksp)_init_modp_send                                         kernel service  parameter ??
ksp_modp_get_enckeyvals                                      (ksp)_modp_get_enckeyvals                                    kernel service  parameter ??
ksp_modp_get_keyvals                                         (ksp)_modp_get_keyvals                                       kernel service  parameter ??
ksp_modp_set_enckeyvals                                      (ksp)_modp_set_enckeyvals                                    kernel service  parameter ??
ksp_modp_set_keyvals                                         (ksp)_modp_set_keyvals                                       kernel service  parameter ??
ksp_modp_update_sign                                         (ksp)_modp_update_sign                                       kernel service  parameter ??
kspdecbuf                                                    (ksp)decbuf                                                  kernel service  parameter ??
kspencbuf                                                    (ksp)encbuf                                                  kernel service  parameter ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qjsnIsDollarOnly                                             (qjsn)IsDollarOnly                                           query json ??
qjsnJsonSerialize_optim                                      (qjsn)JsonSerialize_optim                                    query json ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql operand processing ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

As expected, there aren’t any really drastic differences between Oracle database version 19.5 and 19.6. Now that I am doing these series on differences for all the versions every quarter the new release updates are coming out, there is a certain line, and this release does follow that.

As always, there are some parameters that have changed from being undocumented spare to being undocumented with a name. There is one documented parameter that was added: optimizer_session_type, which has gone official from “_optimizer_auto_index_allow”; see bug 29632611.

parameters unique in version 19.5 versus 19.6

NAME
--------------------------------------------------
_fifth_spare_parameter
_one-hundred-and-fifty-fifth_spare_parameter
_one-hundred-and-fifty-fourth_spare_parameter
_one-hundred-and-fifty-sixth_spare_parameter
_optimizer_auto_index_allow
_sixth_spare_parameter

parameters unique in version 19.6 versus 19.5

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_bug30224950_kjac_direct_path_enabled
_enable_ptime_update_for_sys
_path_prefix_create_dir
_smart_log_threshold_usec
optimizer_session_type

No data dictionary changes have been found.

On the C function side, it becomes apparent that there’s development taking place. For the functions that have been removed, there’s a bunch that deal with ASM (kfd), some with kubs which has to do with big data SQL and some XDB/XML stuff.

There’s way more functions that gotten into the newest release update. There’s a lot of unknowns, ASM, application continuity, lots of big data SQL, JSON, polymorphic table functions, XML, and something that looks like it has to do with SIMD processing (skfSIMD) for which I don’t know what the ‘f’ is there (functions?). There’s also a whole batch of functions starting with ‘rest_nhp’, I yet have to look into these.

code symbol names unique in version 19.5 versus 19.6

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
jskqJobQReadDiskCbk                                          (js)kqJobQReadDiskCbk                                        job scheduing ??
kcbo_incr_doc                                                (kcbo)_incr_doc                                              kernel cache buffers object queue ??
kcfis_clear_lob                                              (kcfis)_clear_lob                                            kernel cache file management intelligent storage ??
kcoclnup602                                                  (kco)clnup602                                                kernel cache operation ??
kewraxc_attribute_xcb                                        (kewr)axc_attribute_xcb                                      kernel event AWR repository ??
kfMaxParityHoles                                             (kf)MaxParityHoles                                           kernel automatic storage management ??
kfdDskComputeHashForRow                                      (kfd)DskComputeHashForRow                                    kernel automatic storage management disk ??
kfdDskTableCbInternal                                        (kfd)DskTableCbInternal                                      kernel automatic storage management disk ??
kfgComputeGrpProp                                            (kfg)ComputeGrpProp                                          kernel automatic storage management diskgroups ??
kfgGrpTableCbInternal                                        (kfg)GrpTableCbInternal                                      kernel automatic storage management diskgroups ??
kgds_dump_callers_sub                                        (kgds)_dump_callers_sub                                      kernel generic vos generic stack trace ??
kgfdDiscoverSubmit                                           (kgfd)DiscoverSubmit                                         kernel generic ASM io subsystem driver ??
kgh_size_sanity_check                                        (kgh)_size_sanity_check                                      kernel generic heap manager ??
kjccspbat                                                    (kjc)cspbat                                                  kernel lock management communication ??
kkeTbRowCPUCost                                              (kke)TbRowCPUCost                                            kernel compile cost engine ??
kkqjePatchCol                                                (kkqj)ePatchCol                                              kernel compile query  join analysis ??
krbbCountThreads                                             (krbb)CountThreads                                           kernel redo backup/restore creation of a backuppiece ??
ksadmb                                                       ksadmb                                                       kernel service  asynchronous messages deallocate message buffer
ksdhng_wdat_rem_dup                                          (ksdhng)_wdat_rem_dup                                        kernel service  debug internal errors hang analyze ??
kubsprqcrio_close                                            (kubsprq)crio_close                                          kernel utility big data sql parquet ??
kubsprqcrio_read                                             (kubsprq)crio_read                                           kernel utility big data sql parquet ??
kubsprqcrio_seek                                             (kubsprq)crio_seek                                           kernel utility big data sql parquet ??
kubsprqioAdvise                                              (kubsprq)ioAdvise                                            kernel utility big data sql parquet ??
kubsprqioClose                                               (kubsprq)ioClose                                             kernel utility big data sql parquet ??
kubsprqioOpen                                                (kubsprq)ioOpen                                              kernel utility big data sql parquet ??
kubsprqioRead                                                (kubsprq)ioRead                                              kernel utility big data sql parquet ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qjsnGetBinFromOSON                                           (qjsn)GetBinFromOSON                                         query json ??
qksptfOCIerr                                                 (qksptf)OCIerr                                               query kernel sql polymorphic table functions compilation ??
qmxgniCheckLOBSize                                           (qmx)gniCheckLOBSize                                         query XDB XML Objects ??
qmxgniHasNodeIdOrCSX                                         (qmx)gniHasNodeIdOrCSX                                       query XDB XML Objects ??
qmxgniReturnString                                           (qmx)gniReturnString                                         query XDB XML Objects ??
qmxgniUnderEvent                                             (qmx)gniUnderEvent                                           query XDB XML Objects ??
rwssid_andv                                                  (rws)sid_andv                                                row source ??

code symbol names unique in version 19.6 versus 19.5

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
CJPathConsumer2D_create                                      CJPathConsumer2D_create                                      ??
CJPathConsumer2D_staticFinalize                              CJPathConsumer2D_staticFinalize                              ??
CJPathConsumer2D_staticInitialize                            CJPathConsumer2D_staticInitialize                            ??
Java_sun_dc_pr_PathStroker_cInitialize2D                     Java_sun_dc_pr_PathStroker_cInitialize2D                     ??
PC2D__cleanup                                                PC2D__cleanup                                                ??
PC2D__enumCoObs                                              PC2D__enumCoObs                                              ??
PC2D_appendCubic                                             PC2D_appendCubic                                             ??
PC2D_appendLine                                              PC2D_appendLine                                              ??
PC2D_appendQuad                                              PC2D_appendQuad                                              ??
PC2D_beginPath                                               PC2D_beginPath                                               ??
PC2D_beginSubpath                                            PC2D_beginSubpath                                            ??
PC2D_className                                               PC2D_className                                               ??
PC2D_closedSubpath                                           PC2D_closedSubpath                                           ??
PC2D_copy                                                    PC2D_copy                                                    ??
PC2D_endPath                                                 PC2D_endPath                                                 ??
PC2D_useProxy                                                PC2D_useProxy                                                ??
crio_close                                                   crio_close                                                   ??
crio_read                                                    crio_read                                                    ??
crio_seek                                                    crio_seek                                                    ??
dbnest_etime_op                                              (dbnest)_etime_op                                            dbnest ??
dcpr_PCClosePath                                             dcpr_PCClosePath                                             ??
dcpr_PCCubicTo                                               dcpr_PCCubicTo                                               ??
dcpr_PCLineTo                                                dcpr_PCLineTo                                                ??
dcpr_PCMoveTo                                                dcpr_PCMoveTo                                                ??
dcpr_PCPathDone                                              dcpr_PCPathDone                                              ??
dcpr_PCQuadTo                                                dcpr_PCQuadTo                                                ??
dmqlDeserLogicalCheck                                        dmqlDeserLogicalCheck                                        ??
dmsarProdDump                                                dmsarProdDump                                                ??
dmsspKroneckerChk                                            dmsspKroneckerChk                                            ??
dmsspProdDump                                                dmsspProdDump                                                ??
dtbfti_find_tobedrp_ix                                       (dtb)fti_find_tobedrp_ix                                     drop table ??
jskqCountClassesUsingService                                 (js)kqCountClassesUsingService                               job scheduing ??
jskqJobQueuePDBReload                                        (js)kqJobQueuePDBReload                                      job scheduing ??
jskqUpdateQueueNextRunTime                                   (js)kqUpdateQueueNextRunTime                                 job scheduing ??
jznDecodeOSON4IMCJMetaD                                      (jzn)DecodeOSON4IMCJMetaD                                    json ??
jznOctDistFnmAllocSz                                         (jzn)OctDistFnmAllocSz                                       json ??
jznoctGetArraySizeI                                          (jzn)octGetArraySizeI                                        json ??
jznoctIsTopScalar                                            (jzn)octIsTopScalar                                          json ??
kciVARCHAR2sz                                                (kci)VARCHAR2sz                                              kernel cache oracle text ??
kcoclnup                                                     (kco)clnup                                                   kernel cache operation ??
kcrfw_alfs_sl_emulate                                        (kcrfw_alfs)_sl_emulate                                      kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_alfs_sl_save_redowrite_time                            (kcrfw_alfs)_sl_save_redowrite_time                          kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_alfs_sl_update_mode                                    (kcrfw_alfs)_sl_update_mode                                  kernel cache recovery file write/broadcast SCN adaptive log file sync ??
kcrfw_dax_is_smart_log                                       (kcrfw)_dax_is_smart_log                                     kernel cache recovery file write/broadcast SCN ??
kewrcc_bind_cb                                               (kewr)cc_bind_cb                                             kernel event AWR repository ??
kewrcc_check_columns                                         (kewr)cc_check_columns                                       kernel event AWR repository ??
kewrcc_fetch_cb                                              (kewr)cc_fetch_cb                                            kernel event AWR repository ??
kfGetParityFailureMask                                       (kf)GetParityFailureMask                                     kernel automatic storage management ??
kfParityCalP                                                 (kf)ParityCalP                                               kernel automatic storage management ??
kfParityCalP0                                                (kf)ParityCalP0                                              kernel automatic storage management ??
kfParityCalQ                                                 (kf)ParityCalQ                                               kernel automatic storage management ??
kfParityCalQ0                                                (kf)ParityCalQ0                                              kernel automatic storage management ??
kfdBlockContentCheck                                         (kfd)BlockContentCheck                                       kernel automatic storage management disk ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kfknodecFreeCb                                               (kfk)nodecFreeCb                                             kernel automatic storage management KFK ??
kfnASMBnRunning                                              (kfn)ASMBnRunning                                            kernel automatic storage management networking subsystem ??
kglLockWaitTime                                              (kglLock)WaitTime                                            kernel generic library cache management library cache lock ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kgwsm_reset                                                  (kg)wsm_reset                                                kernel generic ??
kjac_dp_alloc                                                (kjac)_dp_alloc                                              kernel lock management application continuity  ??
kjac_dp_bind_tim                                             (kjac)_dp_bind_tim                                           kernel lock management application continuity  ??
kjac_dp_bind_uin                                             (kjac)_dp_bind_uin                                           kernel lock management application continuity  ??
kjac_dp_comp_bin                                             (kjac)_dp_comp_bin                                           kernel lock management application continuity  ??
kjac_dp_comp_uin                                             (kjac)_dp_comp_uin                                           kernel lock management application continuity  ??
kjac_dp_enabled_cbk                                          (kjac)_dp_enabled_cbk                                        kernel lock management application continuity  ??
kjac_dp_free                                                 (kjac)_dp_free                                               kernel lock management application continuity  ??
kjac_dp_insert_init                                          (kjac)_dp_insert_init                                        kernel lock management application continuity  ??
kjac_dp_md_init                                              (kjac)_dp_md_init                                            kernel lock management application continuity  ??
kjac_dp_open                                                 (kjac)_dp_open                                               kernel lock management application continuity  ??
kjac_dp_recover                                              (kjac)_dp_recover                                            kernel lock management application continuity  ??
kjac_dp_update                                               (kjac)_dp_update                                             kernel lock management application continuity  ??
kjac_dp_update_init                                          (kjac)_dp_update_init                                        kernel lock management application continuity  ??
kjac_dp_update_state                                         (kjac)_dp_update_state                                       kernel lock management application continuity  ??
kjac_dpctx_alloc                                             (kjac)_dpctx_alloc                                           kernel lock management application continuity  ??
kjac_dpctx_free                                              (kjac)_dpctx_free                                            kernel lock management application continuity  ??
kjac_dpinfo_close                                            (kjac)_dpinfo_close                                          kernel lock management application continuity  ??
kjac_dpinfo_open                                             (kjac)_dpinfo_open                                           kernel lock management application continuity  ??
kjac_sql_ltxid_tabid                                         (kjac)_sql_ltxid_tabid                                       kernel lock management application continuity  ??
kjac_sql_materialize_part                                    (kjac)_sql_materialize_part                                  kernel lock management application continuity  ??
kjac_trace_event_cbk                                         (kjac)_trace_event_cbk                                       kernel lock management application continuity  ??
kjfclmdrsc                                                   (kj)fclmdrsc                                                 kernel lock management ??
kjfclmdsgvbi                                                 (kj)fclmdsgvbi                                               kernel lock management ??
kjgcr_GetTopCPU                                              (kj)gcr_GetTopCPU                                            kernel lock management ??
kjgcr_RunSyncTask                                            (kj)gcr_RunSyncTask                                          kernel lock management ??
kjgcr_SlaveReqGetSlot                                        (kj)gcr_SlaveReqGetSlot                                      kernel lock management ??
kkbati_add_tbd_ix                                            (kkb)ati_add_tbd_ix                                          kernel compile table ??
kkbdti_drop_tbd_ix                                           (kkb)dti_drop_tbd_ix                                         kernel compile table ??
kkdlVirtColCB                                                (kkdl)VirtColCB                                              kernel compile dictionary lookup ??
kkoUpdOrdTabWithOrdSubqHints                                 (kko)UpdOrdTabWithOrdSubqHints                               kernel compile optimizer ??
kkqjfCopyFroNoChn                                            (kkqjf)CopyFroNoChn                                          kernel compile query  join analysis join factorization ??
kpdbcCheckRedoApplyNeeded                                    (kpdb)cCheckRedoApplyNeeded                                  kernel programmatic interface pluggable database ??
kpoxcAppContPDBNotify                                        (kpo)xcAppContPDBNotify                                      kernel programmatic interface oracle ??
kpudpParquetInt96                                            (kpudp)ParquetInt96                                          kernel programmatic interface user DPAPI Load ??
ksfqsrfn                                                     (ksfq)srfn                                                   kernel service  functions sequential file io interface ??
kswsLdiToInt                                                 (ksws)LdiToInt                                               kernel service  workgroup services ??
ksws_alb_colocation_tag_cbk                                  (ksws)_alb_colocation_tag_cbk                                kernel service  workgroup services ??
ksws_alb_mark_stale_colocated_sessions                       (ksws)_alb_mark_stale_colocated_sessions                     kernel service  workgroup services ??
ksws_alb_set_session_colocation                              (ksws)_alb_set_session_colocation                            kernel service  workgroup services ??
kubsBUFioAdvise                                              (kubs)BUFioAdvise                                            kernel utility big data sql ??
kubsBUFioClose                                               (kubs)BUFioClose                                             kernel utility big data sql ??
kubsBUFioOpen                                                (kubs)BUFioOpen                                              kernel utility big data sql ??
kubsBUFioRead                                                (kubs)BUFioRead                                              kernel utility big data sql ??
kubsCRioODM_close                                            (kubs)CRioODM_close                                          kernel utility big data sql ??
kubsCRioODM_finish                                           (kubs)CRioODM_finish                                         kernel utility big data sql ??
kubsCRioODM_finishHelper                                     (kubs)CRioODM_finishHelper                                   kernel utility big data sql ??
kubsCRioODM_init                                             (kubs)CRioODM_init                                           kernel utility big data sql ??
kubsCRioODM_initHelper                                       (kubs)CRioODM_initHelper                                     kernel utility big data sql ??
kubsCRioODM_opcsetupgp                                       (kubs)CRioODM_opcsetupgp                                     kernel utility big data sql ??
kubsCRioODM_openHelper                                       (kubs)CRioODM_openHelper                                     kernel utility big data sql ??
kubsCRioODM_read                                             (kubs)CRioODM_read                                           kernel utility big data sql ??
kubsCRioODM_readHelper                                       (kubs)CRioODM_readHelper                                     kernel utility big data sql ??
kubsCRioREST_allocNextFromDSArray                            (kubs)CRioREST_allocNextFromDSArray                          kernel utility big data sql ??
kubsCRioREST_close                                           (kubs)CRioREST_close                                         kernel utility big data sql ??
kubsCRioREST_finish                                          (kubs)CRioREST_finish                                        kernel utility big data sql ??
kubsCRioREST_httpInit                                        (kubs)CRioREST_httpInit                                      kernel utility big data sql ??
kubsCRioREST_httpRequest                                     (kubs)CRioREST_httpRequest                                   kernel utility big data sql ??
kubsCRioREST_id                                              (kubs)CRioREST_id                                            kernel utility big data sql ??
kubsCRioREST_id_add                                          (kubs)CRioREST_id_add                                        kernel utility big data sql ??
kubsCRioREST_init                                            (kubs)CRioREST_init                                          kernel utility big data sql ??
kubsCRioREST_read                                            (kubs)CRioREST_read                                          kernel utility big data sql ??
kubsCRioREST_validate_url                                    (kubs)CRioREST_validate_url                                  kernel utility big data sql ??
kubsCRioSPescape                                             (kubs)CRioSPescape                                           kernel utility big data sql ??
kubsCRioURLencode                                            (kubs)CRioURLencode                                          kernel utility big data sql ??
kubsCRio_get_cell_wallet_path                                (kubs)CRio_get_cell_wallet_path                              kernel utility big data sql ??
kubsCRio_get_rdbms_wallet_path                               (kubs)CRio_get_rdbms_wallet_path                             kernel utility big data sql ??
kubsavroGetDecimalTpeProp                                    (kubsavro)GetDecimalTpeProp                                  kernel utility big data sql avro ??
kubsprqGetBoolProp                                           (kubsprq)GetBoolProp                                         kernel utility big data sql parquet ??
kubsprqtGetData                                              (kubsprq)tGetData                                            kernel utility big data sql parquet ??
kubsprqtGetDataByte                                          (kubsprq)tGetDataByte                                        kernel utility big data sql parquet ??
kubsprqtRead                                                 (kubsprq)tRead                                               kernel utility big data sql parquet ??
kubsprqtReadVarint                                           (kubsprq)tReadVarint                                         kernel utility big data sql parquet ??
kubsprquReadZVarint                                          (kubsprq)uReadZVarint                                        kernel utility big data sql parquet ??
kubsxGetConfigList                                           (kubs)xGetConfigList                                         kernel utility big data sql ??
kubsxiGetLocalCreds                                          (kubs)xiGetLocalCreds                                        kernel utility big data sql ??
kubsxiGetNextCell                                            (kubs)xiGetNextCell                                          kernel utility big data sql ??
kupaxisosf                                                   (kup)axisosf                                                 kernel utility datapump ??
kwqaexgcur                                                   (kwqa)exgcur                                                 kernel advanced queuing  administration ??
kwqaexgcurobc                                                (kwqa)exgcurobc                                              kernel advanced queuing  administration ??
kxes_sqlcomp_check_1                                         (kx)es_sqlcomp_check_1                                       kernel execution ??
kxes_sqlcomp_check_macro                                     (kx)es_sqlcomp_check_macro                                   kernel execution ??
kzvCheckGrantInRoot_cbk                                      (kz)vCheckGrantInRoot_cbk                                    kernel security ??
kzvGrantedCommonlyInRoot                                     (kz)vGrantedCommonlyInRoot                                   kernel security ??
ntt_pptlv                                                    (ntt)_pptlv                                                  network transport (drivers) tcp/ip ??
odm_apg                                                      (odm)_apg                                                    oracle disk manager ??
odm_fpg                                                      (odm)_fpg                                                    oracle disk manager ??
odm_get_creds                                                (odm)_get_creds                                              oracle disk manager ??
odm_get_proxy                                                (odm)_get_proxy                                              oracle disk manager ??
odm_oua                                                      (odm)_oua                                                    oracle disk manager ??
odm_pec                                                      (odm)_pec                                                    oracle disk manager ??
odm_printf                                                   (odm)_printf                                                 oracle disk manager ??
odm_wrf                                                      (odm)_wrf                                                    oracle disk manager ??
ph2osfm_setup_function_metadata                              (ph2)osfm_setup_function_metadata                            PLSQL phase 2 (semantic analyzer) ??
ph2scs_semantic_checks_for_sqm                               (ph2)scs_semantic_checks_for_sqm                             PLSQL phase 2 (semantic analyzer) ??
qctoj_obadi4DateTimeStuff                                    (qcto)j_obadi4DateTimeStuff                                  query compile type check operations operators ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qertrcGetEncMaxLen                                           (qer)trcGetEncMaxLen                                         query execute rowsource ??
qerupFindChiHC                                               (qerup)FindChiHC                                             query execute rowsource update ??
qerupUpdStatsCbk                                             (qerup)UpdStatsCbk                                           query execute rowsource update ??
qerxjConvertToBinaryNumber                                   (qerxj)ConvertToBinaryNumber                                 query execute rowsource json ??
qerxjConvertToInterval                                       (qerxj)ConvertToInterval                                     query execute rowsource json ??
qjsnJSON_DML_OptimOccurred                                   (qjsn)JSON_DML_OptimOccurred                                 query json ??
qjsnOKToApplySkipCpOSONOptim                                 (qjsn)OKToApplySkipCpOSONOptim                               query json ??
qjsnRetBinaryNumber                                          (qjsn)RetBinaryNumber                                        query json ??
qjsnRetInterval                                              (qjsn)RetInterval                                            query json ??
qjsn_can_skip_cpy_oson_bytes                                 (qjsn)_can_skip_cpy_oson_bytes                               query json ??
qjsn_can_skip_is_json_validation                             (qjsn)_can_skip_is_json_validation                           query json ??
qjsngGetBinFromOSON                                          (qjsn)gGetBinFromOSON                                        query json ??
qjsngRaiseSQLError                                           (qjsn)gRaiseSQLError                                         query json ??
qjsngStringToDateDty3                                        (qjsn)gStringToDateDty3                                      query json ??
qjsnlobCheckLobPatch                                         (qjsn)lobCheckLobPatch                                       query json ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql compilter operand processing ??
qksopFindQbByName                                            (qksop)FindQbByName                                          query kernel sql operand processing ??
qksopSplitPreds                                              (qksop)SplitPreds                                            query kernel sql operand processing ??
qksopVisitPredsInt                                           (qksop)VisitPredsInt                                         query kernel sql operand processing ??
qksopVisitPredsPostOrder                                     (qksop)VisitPredsPostOrder                                   query kernel sql compilter operand processing ??
qksptfContainNestedScalar                                    (qksptf)ContainNestedScalar                                  query kernel sql polymorphic table functions compilation ??
qksptfDescribePTF                                            (qksptf)DescribePTF                                          query kernel sql polymorphic table functions compilation ??
qksptfOCIerr_                                                (qksptf)OCIerr_                                              query kernel sql polymorphic table functions compilation ??
qksptfSQM_Check_Errors                                       (qksptf)SQM_Check_Errors                                     query kernel sql polymorphic table functions compilation ??
qksptfSQM_CleanupTDOs                                        (qksptf)SQM_CleanupTDOs                                      query kernel sql polymorphic table functions compilation ??
qksptfSQM_Describe                                           (qksptf)SQM_Describe                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_ExpRepl_                                           (qksptf)SQM_ExpRepl_                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_GetTxt                                             (qksptf)SQM_GetTxt                                           query kernel sql polymorphic table functions compilation ??
qksptfSQM_Init                                               (qksptf)SQM_Init                                             query kernel sql polymorphic table functions compilation ??
qksptfSQM_Laz_                                               (qksptf)SQM_Laz_                                             query kernel sql polymorphic table functions compilation ??
qksptfSQM_Model_Check                                        (qksptf)SQM_Model_Check                                      query kernel sql polymorphic table functions compilation ??
qksptfSQM_Parse                                              (qksptf)SQM_Parse                                            query kernel sql polymorphic table functions compilation ??
qksptfSQM_Parse_errors                                       (qksptf)SQM_Parse_errors                                     query kernel sql polymorphic table functions compilation ??
qksptfSQM_PstPrc                                             (qksptf)SQM_PstPrc                                           query kernel sql polymorphic table functions compilation ??
qksptfSQM_PstPrc1_                                           (qksptf)SQM_PstPrc1_                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_QbcRelExp_                                         (qksptf)SQM_QbcRelExp_                                       query kernel sql polymorphic table functions compilation ??
qksptfSQM_RplExpA                                            (qksptf)SQM_RplExpA                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_RplExpB                                            (qksptf)SQM_RplExpB                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_SetIQB_                                            (qksptf)SQM_SetIQB_                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_SetSQM_                                            (qksptf)SQM_SetSQM_                                          query kernel sql polymorphic table functions compilation ??
qksptfSQM_Template                                           (qksptf)SQM_Template                                         query kernel sql polymorphic table functions compilation ??
qksptfSQM_rm_vqb                                             (qksptf)SQM_rm_vqb                                           query kernel sql polymorphic table functions compilation ??
qksptfSubExprContainScalar                                   (qksptf)SubExprContainScalar                                 query kernel sql polymorphic table functions compilation ??
qmxtigCheckLOBSize                                           (qmxt)igCheckLOBSize                                         query XDB XML Objects XML ??
qmxtigCreXMLImgFromLob                                       (qmxt)igCreXMLImgFromLob                                     query XDB XML Objects XML ??
qmxtigGetXMLImgInfo                                          (qmxt)igGetXMLImgInfo                                        query XDB XML Objects XML ??
qmxtigImgNdsCnv                                              (qmxt)igImgNdsCnv                                            query XDB XML Objects XML ??
qmxtigReturnString                                           (qmxt)igReturnString                                         query XDB XML Objects XML ??
qmxtigUnderEvent                                             (qmxt)igUnderEvent                                           query XDB XML Objects XML ??
qmxtigUpdateReplayChecksum                                   (qmxt)igUpdateReplayChecksum                                 query XDB XML Objects XML ??
qmxtigValidateXmlImage                                       (qmxt)igValidateXmlImage                                     query XDB XML Objects XML ??
qmxtigValidateXmlImgBody                                     (qmxt)igValidateXmlImgBody                                   query XDB XML Objects XML ??
qmxtigValidateXmlImgHdr                                      (qmxt)igValidateXmlImgHdr                                    query XDB XML Objects XML ??
qxxqGetCredentials                                           (qx)xqGetCredentials                                         query query execution ??
qxxqGetProxy                                                 (qx)xqGetProxy                                               query query execution ??
qxxqGetSSLWallet                                             (qx)xqGetSSLWallet                                           query query execution ??
rest_nhpavail                                                rest_nhpavail                                                ??
rest_nhpconnect                                              rest_nhpconnect                                              ??
rest_nhpdisconnect                                           rest_nhpdisconnect                                           ??
rest_nhpflush                                                rest_nhpflush                                                ??
rest_nhpfree                                                 rest_nhpfree                                                 ??
rest_nhpmalloc                                               rest_nhpmalloc                                               ??
rest_nhprealloc                                              rest_nhprealloc                                              ??
rest_nhprecv                                                 rest_nhprecv                                                 ??
rest_nhprequestauth                                          rest_nhprequestauth                                          ??
rest_nhpsend                                                 rest_nhpsend                                                 ??
rest_nhptrace                                                rest_nhptrace                                                ??
rest_nhpwalletauth                                           rest_nhpwalletauth                                           ??
rwssecondtop                                                 (rws)secondtop                                               row source ??
setJPathConsumer2D                                           setJPathConsumer2D                                           ??
skfParityGDiv                                                (sk)fParityGDiv                                              operating system dependent kernel ??
skfParityGMul                                                (sk)fParityGMul                                              operating system dependent kernel ??
skfSIMDAvail                                                 (sk)fSIMDAvail                                               operating system dependent kernel ??
skfSIMDCalP                                                  (sk)fSIMDCalP                                                operating system dependent kernel ??
skfSIMDCalP0                                                 (sk)fSIMDCalP0                                               operating system dependent kernel ??
skfSIMDCalQ                                                  (sk)fSIMDCalQ                                                operating system dependent kernel ??
skfSIMDCalQ0                                                 (sk)fSIMDCalQ0                                               operating system dependent kernel ??
skfSIMDMultiplicationLookupTable                             (sk)fSIMDMultiplicationLookupTable                           operating system dependent kernel ??
skfSIMDRecoverOneDataFromQ                                   (sk)fSIMDRecoverOneDataFromQ                                 operating system dependent kernel ??
skfSIMDRecoverTwoDataFromPQ                                  (sk)fSIMDRecoverTwoDataFromPQ                                operating system dependent kernel ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)

This post is about how to use gdb, which is a debugger, so very simplistically put an aid for looking at C programs, as a profiler. I use gdb quite a lot for profiling because it’s the easiest way for profiling for me.

Lots of people which I know use other tools like perf, systemtap and dtrace for the same purpose and that’s fine. Each tools has its own advantages and disadvantages. One disadvantage of gdb is that it’s using ptrace to attach to a process, which makes it dead slow from a machine perspective, because everything it then does goes via another process, which is the debugger. That is how the debugger works.

Also lots of people use gdb like I do, and use basic functionality, which is breaking at functions, which makes it possible to find out the sequence of how functions are called, generating backtraces (stack traces) to understand the stack and maybe looking at functions arguments.

But there is more that you can do. Way more actually. These examples use the gdb that comes with RHEL7/OL7 via yum.

You can break conditionally at a function, which means that a break will be executed only if the condition is met. For example:

break sltrgatime64 if $caller_is("ksbabs")

This only breaks at a function called sltrgatime64 if it is called from a function that is called ksbabs.

But for what I wanted to do and investigate, I didn’t only want to do “something” when the sltrgatime64 function was called from ksbabs, I wanted be able to distinguish the two times this was called as the first one and the second one.

I knew that the “first time” was actually right after a function with the name of “dbktFlush” was called. So here’s what I did:

set $status = -1

break dbktFlush if $caller_is("ksbabs")
commands
 silent
 set $status = 0
 c
end

break sltrgatime64 if $caller_is("ksbabs")
commands
 silent
 if $status == 0
   set $status = 1
   printf "first invocation\n"
   c
 end
 if $status == 1
  set $status = 2
  printf "second invocation\n"
  c
 end
 if $status > 1
  printf "error. third or more invocations\n"
 end
end

This is much more advanced than using a single command, but I think for anyone who is able to program a few languages is reasonably simple to understand.

There’s two breaks, and both are conditional, the function from which the function to break on must be ksbabs.
In the break of dbktFlush the (convenience) variable $status is set to 0, indicating the the first invocation for the other break.

In the second break, there are 3 if statements. The first one picks up the variable $status set to 0, indicating the first invocation and then sets it to 1 for the second invocation and the second if picks up the second invocation indicated by the variable $status set to 1 and sets it to 2, so if I didn’t look carefully and there are more invocations of sltrgatime64, I will get notified.

If breaks would be activated when the second call to sltrgatime64 was executed, it would not come across dbktFlush, and thus the $status variable would (still) be set to -1, and the break would skip all ifs.

Of course this is a very specific case, you should tailor it for your own needs. However, this was the actual investigation I done to investigate the function of the above sltrgatime64 calls.

In fact, I gone a little deeper and wanted to understand what the timing of of these two reads of the wall clock time did. For that, I used the python interface in gdb, for which the support seems to be compiled in in the rpm based version.

You can use python, and use variables in python that remain state between gdb interactions. I used a dead simple interaction; calling time.time() to time the invocation of the sltrgatime64 executed right after dbktFlush was called:

python import time
python var_time=time.time()

break dbktFlush if $caller_is("ksbabs")
commands
 silent
 set $status = 0
 c
end

break sltrgatime64 if $caller_is("ksbabs")
commands
 silent
 if $status == 0
   set $status = 1
   python print time.time()-var_time
   python var_time=time.time()
   c
  else
   c
 end
end

If you set single logwriter to true and execute this against the logwriter, you will see that the logwriter tries to keep a 3 second pace by adjusting the sleep time on semtimedop().

My actual goal was to try to find out what is exactly timed for the message ‘Warning: log write elapsed time ????ms, size ?KB’ in the logwriter trace file, which Nenad Noveljic proved is not only about the actual IO time.

To be honest, I done it clumsy by going backward measuring all the calls that measured wall clock time and should have put a break on write and look at the backtrace. When I executed a backtrace of the write call that wrote the trace file entry, I could see this line was written in kcrfw_postprocess_write().

This is the gdb script with the timing:

python import time

break skgstmGetEpochTs if $caller_is("kcrfw_redo_write_driver")
commands
 silent
 python var_time=time.time()
 c
end

break kcrfw_do_write
 commands
 silent
 print $rip
 c
end

break skgstmGetEpochTs if $caller_is("kcrfw_postprocess_write")
commands
 silent
 shell sleep 1
 python print time.time()-var_time
 c
end

What does gdb script does, is always set the python variable var_time, which includes the idle cycles of the logwriter. If it goes into the function kcrfw_do_write it actually is going to write. That is also when potentially the line “Warning: log write elapsed time 1024ms, size 0KB” could be produced.
when the function skgstmGetEpochTs is called in the function kcrfw_postprocess_write, it executes a sleep for 1 second, and then prints the current time minus the time obtained when kcrfw_redo_write_driver was entered.

What this proofs, is that the timing for the log write time elapsed time warning is done from the start of the kcrfw_redo_write_driver function. That means that everything that the logwriter needs to do, excluding the the last bit of what the logwriter performs, posting the foregrounds, is timed, including dealing with the internal structures that keep track of the redo strands and the latching of it.

The Oracle database log writer is the process that fundamentally influences database change performance. Under normal circumstances the log writer must persist the changes made to the blocks before the actual change is committed. Therefore, it’s vitally important to understand what the log writer is exactly doing. This is widely known by the Oracle database community.

The traditional method for looking at log writer performance is looking at the wait event ‘log file parallel write’ and the CPU time, and comparing that to the ‘log file sync’ alias “commit wait time”. If ‘log file parallel write’ and ‘log file sync’ roughly match, a commit is waiting on the log writer IO latency, if it isn’t then it’s unclear, and things get vague.

Now we get to the gist of this blogpost: since Oracle 12, there are additional statistics in V$SESSTAT and v$SYSSTAT that describe where the log writer spending it’s time on. However, and sadly, these are not documented by Oracle. The statistics are:

– redo write gather time
– redo write schedule time
– redo write finish time
– redo write time (usec)
– redo write total time

Probably ‘redo write time (usec)’ doesn’t belong to these statistics because the timing is as good as the same as ‘redo write finish time’. Also the statistic ‘redo write time (usec)’ is updated by ‘kcrfw_postprocess_write’ right after the main writing function (kcrfw_do_write), whilst the other redo write timing statistics are updated/made visible by ksu_dispatch_tac outside of the actual writing functions, at the approximate rate of every 3 seconds.

All these statistics contain cumulative timing figure in microseconds (usec).

All these timings start at the start of the function kcrfw_redo_write_driver. This is the main function that handles all the redo writing, including investigating the fixed area structure (kcrfs_) and the shared pool structures (made visible using X$KCRFSTRAND) to see if there are changes.

1. redo write gather time.
This times reading the structures that control allocation of the redo structures, validating foreground processes are done writing the change vectors and applying these to the blocks, updating the LWN SCN. Part of this also happens when there is nothing to write, but then the time is not added to this statistic, only if it found something to write.

2. redo write schedule time.
This includes the time of 1, but now it found something to write, entered the writing function (kcrfw_do_write), and updated the ksfd IO related structures for writing.

3. redo write finish time.
This includes the time of 1 and 2, but this additionally times all the IO related functions for writing the changes it found in the public redo strands, as indicated by the shared pool and fixed area structures.

4. redo write time (usec).
This probably is not part of this group of statistics, but it works in exactly the same way: it’s a cumulative figure, it counts time since the start of the kcrfw_redo_write_driver and includes a tiny bit more code, it times until it has left the kcrfw_do_write function and enters the kcrfw_postprocess_write function.

5. redo write total time.
This includes the time of 1, 2 and 3, but additionally times the kcrfw_post function, which performs the signalling of the foreground processes that have committed and wait for a semop() signal.

This means that the Oracle database since Oracle 12 provides the timing information to see where the log writer is spending it’s time on. This can prevent a lot of guesswork.

%d bloggers like this: