By Franck Pachot
.
The previous post partly answered to the original question (why an object link to V$SESSION is refreshed only every 30 seconds): recursive queries on shared=object views. Now let’s see what is different with fixed tables.
Disclaimer: this is research only. Don’t do that anywhere else than a lab. This is implicit when the title is ‘internals’.
Result cache
When query on a shared=object view is executed from a PDB, the session switches to the CDB to run a recursive query to get the rows. This query uses result cache by adding the following hint:
/*+ RESULT_CACHE (SYSOBJ=TRUE) */This enables result cache for the rows fetched by this query, and even for system object. The ‘SYSOBJ=TRUE’ is there because the “_rc_sys_obj_enabled” defaults to true.
Here is the result cache from the previous post (I flushed the result cache just before the second run because in 12c hard parsing is also using a lot the result cache):
16:34:00 SQL> select con_id,sql_id,rows_processed,plan_hash_value,executions,parse_calls,fetches,buffer_gets,sql_text from v$sql where plan_hash_value in (3598352655,3551671056) order by last_active_time;
CON_ID SQL_ID ROWS_PROCESSED PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FETCHES BUFFER_GETS SQL_TEXT
---------- ------------- -------------- --------------- ---------- ----------- ---------- ----------- --------------------------------------------------------------------------------
1 350gg6247sfa6 200 3598352655 2 2 2 26 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV
3 bynmh7xm4bf54 0 3598352655 0 5 0 51 SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
3 duy45bn72jr35 200 3551671056 2 2 16 269 select id from DEMOV where num column name format a120 trunc
16:34:00 SQL> select type,status,name,row_count from v$result_cache_objects order by row_count desc fetch first 10 rows only;
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 100
Dependency Published SYS.DEMOT 0
Dependency Published SYS.DEMOV 0
As with regular result cache, there is dependency tracking: as soon as the underlying table has some modification, the cache will be invalidated. So this query is always guaranteed to get fresh results.
Invalidation
I did the same when deleting half of the rows before the second execution in order to invalidate the result cache:
16:43:46 SQL> select con_id,sql_id,rows_processed,plan_hash_value,executions,parse_calls,fetches,buffer_gets,sql_text from v$sql where plan_hash_value in (3598352655,3551671056) order by last_active_time;
CON_ID SQL_ID ROWS_PROCESSED PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FETCHES BUFFER_GETS SQL_TEXT
---------- ------------- -------------- --------------- ---------- ----------- ---------- ----------- --------------------------------------------------------------------------------
1 350gg6247sfa6 150 3598352655 2 2 2 26 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV
3 bynmh7xm4bf54 0 3598352655 0 5 0 51 SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
3 duy45bn72jr35 150 3551671056 2 2 13 269 select id from DEMOV where num column name format a120 trunc
16:43:46 SQL> select type,status,name,row_count from v$result_cache_objects order by row_count desc fetch first 10 rows only;
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Invalid SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 100
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 50
Dependency Published SYS.DEMOT 0
Dependency Published SYS.DEMOV 0
I’ve 100 rows from the first run, invalidated, and them 50 rows from the second one.
Note that I’ve the same result when I set “_disable_cdb_view_rc_invalidation”=true. Sometimes undocumented parameters behavior cannot be guessed only from their name.
Fixed tables
I’ve run the same testcase but with the following definition of DEMOV:
create view DEMOV sharing=object as select saddr id, rownum num from V$SESSION;
Here is the trace of the recursive query run in CDB$ROOT, at first execution:
PARSING IN CURSOR #140436732146672 len=112 dep=1 uid=0 oct=3 lid=0 tim=769208810641 hv=3298783355 ad='108ee92f0' sqlid=' 10qf9kb29yw3v'
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
END OF STMT
PARSE #140436732146672:c=4000,e=10614,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=4,plh=350654732,tim=769208810640
EXEC #140436732146672:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208810701
FETCH #140436732146672:c=1000,e=961,p=0,cr=0,cu=0,mis=0,r=53,dep=1,og=4,plh=350654732,tim=769208811687
STAT #140436732146672 id=1 cnt=53 pid=0 pos=1 obj=0 op='RESULT CACHE byq3fbkawmkm34gtfk1csvwv52 (cr=0 pr=0 pw=0 time=877 us)'
STAT #140436732146672 id=2 cnt=53 pid=1 pos=1 obj=98258 op='VIEW DEMOV (cr=0 pr=0 pw=0 time=655 us cost=0 size=171 card=9)'
STAT #140436732146672 id=3 cnt=53 pid=2 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=652 us)'
STAT #140436732146672 id=4 cnt=53 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=597 us cost=0 size=306 card=9)'
STAT #140436732146672 id=5 cnt=53 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=436 us cost=0 size=270 card=9)'
STAT #140436732146672 id=6 cnt=53 pid=5 pos=1 obj=0 op='FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=219 us cost=0 size=352 card=44)'
STAT #140436732146672 id=7 cnt=53 pid=5 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=101 us cost=0 size=22 card=1)'
STAT #140436732146672 id=8 cnt=53 pid=4 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=70 us cost=0 size=4 card=1)'The difference here is that SHELFLIFE=30 has been added to the generated result cache hint.
The second run has very short parse time (c=0) because it’s a soft parse but you also see very short fetch time (c=0) because it’s a cache hit:
PARSING IN CURSOR #140436733602136 len=112 dep=1 uid=0 oct=3 lid=0 tim=769208821904 hv=3298783355 ad='108ee92f0' sqlid=' 10qf9kb29yw3v'
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
END OF STMT
PARSE #140436733602136:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208821904
EXEC #140436733602136:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208821955
FETCH #140436733602136:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=53,dep=1,og=4,plh=350654732,tim=769208821990When I look at the result cache, there were no invalidations:
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100 55
Dependency Published SYS.DEMOV 0When SHELFLIFE is set in a result cache hint, there is no dependency tracking. I’ve described RESULT_CACHE hint expiration options in a previous post.
The V$ views are on fixed tables, structures in memory, where there is no dependency tracking possibility. This is probably why the recursive query for sharing=object views use a SHELFLIFE instead.
This means that if you create an object link view on a fixed table the query will show same result for the next executions for 30 seconds.
“_cdb_view_rc_shelflife”
I’ve tested a shared=object view on V$SESSION as an answer to a previous blog comment. My query selects MAX(LAST_ET_CALL) which is supposed to increase every second for the inactive sessions. And we see that the result changes only every 30 seconds.
Those 30 seconds are parametered by “_cdb_view_rc_shelflife”. Here is the same test where I set “_cdb_view_rc_shelflife” to 5 seconds:
15:31:48 SQL> alter session set "_cdb_view_rc_shelflife"=5;
Session altered.
15:31:48 SQL> set serveroutput on
15:31:48 SQL> declare
15:31:48 2 x varchar2(100);
15:31:48 3 begin
15:31:48 4 for i in 1..60 loop
15:31:48 5 dbms_lock.sleep(1);
15:31:48 6 select to_char(current_timestamp)||' --> '||max(last_call_et) into x from DEMOV;
15:31:48 7 dbms_output.put_line(x);
15:31:48 8 end loop;
15:31:48 9 end;
15:31:48 10 /
07-AUG-16 03.31.49.852081 PM +00:00 --> 775144
07-AUG-16 03.31.50.863742 PM +00:00 --> 775144
07-AUG-16 03.31.51.863753 PM +00:00 --> 775144
07-AUG-16 03.31.52.864697 PM +00:00 --> 775144
07-AUG-16 03.31.53.864706 PM +00:00 --> 775144
07-AUG-16 03.31.54.864726 PM +00:00 --> 775144
07-AUG-16 03.31.55.864669 PM +00:00 --> 775150
07-AUG-16 03.31.56.864711 PM +00:00 --> 775150
07-AUG-16 03.31.57.864754 PM +00:00 --> 775150
07-AUG-16 03.31.58.864702 PM +00:00 --> 775150
07-AUG-16 03.31.59.864711 PM +00:00 --> 775150
07-AUG-16 03.32.00.864779 PM +00:00 --> 775150
07-AUG-16 03.32.01.865710 PM +00:00 --> 775156
07-AUG-16 03.32.02.866738 PM +00:00 --> 775156
07-AUG-16 03.32.03.866719 PM +00:00 --> 775156
07-AUG-16 03.32.04.866787 PM +00:00 --> 775156
07-AUG-16 03.32.05.866758 PM +00:00 --> 775156
07-AUG-16 03.32.06.866805 PM +00:00 --> 775156
07-AUG-16 03.32.07.867738 PM +00:00 --> 775162
07-AUG-16 03.32.08.868743 PM +00:00 --> 775162
07-AUG-16 03.32.09.868727 PM +00:00 --> 775162
07-AUG-16 03.32.10.868724 PM +00:00 --> 775162
07-AUG-16 03.32.11.868758 PM +00:00 --> 775162
07-AUG-16 03.32.12.869763 PM +00:00 --> 775167
07-AUG-16 03.32.13.870741 PM +00:00 --> 775167
07-AUG-16 03.32.14.870742 PM +00:00 --> 775167
07-AUG-16 03.32.15.870721 PM +00:00 --> 775167
07-AUG-16 03.32.16.870734 PM +00:00 --> 775167
07-AUG-16 03.32.17.870883 PM +00:00 --> 775167
07-AUG-16 03.32.18.872741 PM +00:00 --> 775173
07-AUG-16 03.32.19.873837 PM +00:00 --> 775173
And here is the same test after setting:
SQL> exec dbms_result_cache.bypass(true);
I’ve not tested, but I expect the same in Standard Edition where result cache is disabled
07-AUG-16 03.43.32.158741 PM +00:00 --> 775846
07-AUG-16 03.43.33.185793 PM +00:00 --> 775847
07-AUG-16 03.43.34.186633 PM +00:00 --> 775848
07-AUG-16 03.43.35.186738 PM +00:00 --> 775849
07-AUG-16 03.43.36.187696 PM +00:00 --> 775850
07-AUG-16 03.43.37.188684 PM +00:00 --> 775851
07-AUG-16 03.43.38.188692 PM +00:00 --> 775852
07-AUG-16 03.43.39.189755 PM +00:00 --> 775853
07-AUG-16 03.43.40.190697 PM +00:00 --> 775854
07-AUG-16 03.43.41.191763 PM +00:00 --> 775855
07-AUG-16 03.43.42.192706 PM +00:00 --> 775856
07-AUG-16 03.43.43.193736 PM +00:00 --> 775857
Conclusion
Don’t be afraid. There are very few sharing=object views in the dictionary, and only few of them have dependencies on fixed tables:
SQL> select owner,name,referenced_name from dba_dependencies
where (referenced_owner,referenced_name) in (select 'SYS',view_name from v$fixed_view_definition union select 'SYS',name from v$fixed_table)
and (owner,name,type) in (select owner,object_name,object_type from dba_objects where sharing='OBJECT LINK')
;
OWNER NAME REFERENCED_NAME
------------------------------ ------------------------------ ------------------------------
SYS INT$DBA_HIST_SQLSTAT X$MODACT_LENGTH
SYS INT$DBA_HIST_ACT_SESS_HISTORY X$MODACT_LENGTH
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTGSD
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTSD
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTOSD
SYS INT$DBA_ALERT_HISTORY X$KELTGSD
SYS INT$DBA_ALERT_HISTORY X$KELTSD
SYS INT$DBA_ALERT_HISTORY X$KELTOSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTGSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTOSD
SYS DEMOV GV$SESSION
6 rows selected.
I’ve described how AWR views are stacked onto each other in a previous post.
And don’t worry, you don’t need to have a fresh view of those X$ tables. As an example, behind DBA_HIST_ACTIVE_SES_HISTORY the fixed table X$MODACT_LENGTH holds only the length of module and action strings:
SQL> select * from X$MODACT_LENGTH;
ADDR INDX INST_ID CON_ID KSUMODLEN KSUACTLEN
---------------- ---------- ---------- ---------- ---------- ----------
00007FF2EF280920 0 1 0 48 32And the others (X$KELTSD, X$KELTGSD, X$KELTOSD) are the structures behind V$ALERT_TYPES that are not supposed to change.
So don’t panic. The multitenant architecture has some strange implementation stuff, but mostly harmless…
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)