Search

Top 60 Oracle Blogs

Recent comments

Oracle rowcache fastgets

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.