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.
Frits Hoogland Weblog 