<\/code>SQL> set serveroutput on\nSQL> exec DBMS_RESULT_CACHE.Memory_Report();\nR e s u l t \u00a0 C a c h e \u00a0 M e m o r y \u00a0 R e p o r t\n[Parameters]Block Size \u00a0 \u00a0 \u00a0 \u00a0 \u00a0= 1K bytes\nMaximum Cache Size \u00a0= 4M bytes (4K blocks)\nMaximum Result Size = 204K bytes (204 blocks)\n[Memory]\nTotal Memory = 5180 bytes [0.004% of the Shared Pool]\n... Fixed Memory = 5180 bytes [0.004% of the Shared Pool]\n... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]\nPL\/SQL procedure successfully completed.<\/pre>\nBasic result cache tests<\/h3>\n
All tests will be run with Oracle sample schema SH. First, I try to execute twice the same statement without result cache (which is the default).<\/p>\n
<\/p>\n
SQL> set lines 150\nSQL> set timing on\nSQL> set autotrace traceonly explain\nSQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;\n620 rows selected.\nElapsed: 00:00:00.31\nSQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;\n620 rows selected.\nElapsed: 00:00:00.04\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1577413243\n--------------------------------------------------------------------------------\n| Id \u00a0| Operation \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| Name \u00a0 \u00a0 \u00a0| Rows \u00a0| Bytes | Cost (%CPU)| Time \u00a0 \u00a0 |\n--------------------------------------------------------------------------------\n| \u00a0 0 | SELECT STATEMENT \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 1 | \u00a0HASH GROUP BY \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 2 | \u00a0 TABLE ACCESS FULL| CUSTOMERS | 55500 | \u00a0 541K| \u00a0 405 \u00a0 (1)| 00:00:05 |\n--------------------------------------------------------------------------------<\/pre>\nHere, we see the effect of the existing caches, the second execution taking less time. As the two statements share the same execution plan, I have copied it only once.<\/p>\n
Now, result cache is enabled for this session and the same statement is executed two times again.<\/p>\n
<\/code>SQL> ALTER SESSION SET result_cache_mode = FORCE;\nSession altered.\nSQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;\n620 rows selected.\nElapsed: 00:00:00.12\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1577413243\n--------------------------------------------------------------------------------------------------\n| Id \u00a0| Operation \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Rows \u00a0| Bytes | Cost (%CPU)| Time \u00a0 \u00a0 |\n--------------------------------------------------------------------------------------------------\n| \u00a0 0 | SELECT STATEMENT \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 1 | \u00a0RESULT CACHE \u00a0 \u00a0 \u00a0 | dg3tccfff51dj1n3932f7wjypt | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| \u00a0 2 | \u00a0 HASH GROUP BY \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 3 | \u00a0 \u00a0TABLE ACCESS FULL| CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 55500 | \u00a0 541K| \u00a0 405 \u00a0 (1)| 00:00:05 |\n--------------------------------------------------------------------------------------------------\nResult Cache Information (identified by operation id):\n------------------------------------------------------\n1 - column-count=2; dependencies=(SH.CUSTOMERS); parameters=(nls); name=\"SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY\"\n\nSQL> SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;\n620 rows selected.\nElapsed: 00:00:00.00\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1577413243\n--------------------------------------------------------------------------------------------------\n| Id \u00a0| Operation \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Rows \u00a0| Bytes | Cost (%CPU)| Time \u00a0 \u00a0 |\n--------------------------------------------------------------------------------------------------\n| \u00a0 0 | SELECT STATEMENT \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 1 | \u00a0RESULT CACHE \u00a0 \u00a0 \u00a0 | dg3tccfff51dj1n3932f7wjypt | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| \u00a0 2 | \u00a0 HASH GROUP BY \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 3 | \u00a0 \u00a0TABLE ACCESS FULL| CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 55500 | \u00a0 541K| \u00a0 405 \u00a0 (1)| 00:00:05 |\n--------------------------------------------------------------------------------------------------\nResult Cache Information (identified by operation id):\n------------------------------------------------------\n1 - column-count=2; dependencies=(SH.CUSTOMERS); parameters=(nls); name=\"SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY\"<\/pre>\nA new operation named “RESULT CACHE” has been added in the execution plan. The two statements share the same name for result cache operation. The first execution is a little more longer than when cache is disabled but the second is immediate. The explanation seems simple, during the first execution Oracle puts the result in cache and for the second execution, the only operation is to read cache.
\nTo check cache allocation and verify the hypothesis, I check into some result cache views under SYS account.<\/p>\n
<\/code>SQL> SELECT id, name, cache_id, cache_key, type, status FROM v$result_cache_objects;\n ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- ---------\n\u00a0 0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dependency Published\n\u00a0 1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt \u00a0 \u00a0 9mxfx9vgpfzacd7c143887rgg1 \u00a0 \u00a0 Result \u00a0 \u00a0 Published\n\u00a0 \u00a0 S GROUP BY CUST_CITY\n2 objects have been created, a dependency object and a result object and both are published. The result object cache id corresponds to the result cache operation name in execution plan.\n\nSQL> select * from V$RESULT_CACHE_STATISTICS;\n ID \u00a0 \u00a0 \u00a0 \u00a0 NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VALUE\n---------- ---------------------------------------- ----------\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a01 Block Size (Bytes) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1024\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a02 Block Count Maximum \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a04096\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a03 Block Count Current \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a032\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a04 Result Size Maximum (Blocks) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 204\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a05 Create Count Success \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a06 Create Count Failure \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a07 Find Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a08 Invalidation Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a09 Delete Count Invalid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 \u00a0 \u00a0 10 Delete Count Valid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 \u00a0 \u00a0 11 Hash Chain Length \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01\n\u00a0 \u00a0 \u00a0 \u00a0 12 Find Copy Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00<\/pre>\n“Create Count Success” statistic shows that the first query creates the result object in cache and “Find Count” shows that the second execution has found and used the result object.<\/p>\n
To check how cache reacts when data is changed, I have updated the table customers.<\/p>\n
<\/code>-- SH session\nSQL> UPDATE CUSTOMERS SET cust_first_name = cust_first_name where rownum = 1;\n1 row updated.\nSQL> commit;\nCommit complete.\n-- SYS session\nSQL> SELECT id, name, cache_id, cache_key, type, status FROM v$result_cache_objects;\n\u00a0ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- ---------\n\u00a0 0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dependency Published\n\u00a0 1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt \u00a0 \u00a0 9mxfx9vgpfzacd7c143887rgg1 \u00a0 \u00a0 Result \u00a0 \u00a0 Invalid\n\u00a0 \u00a0 S GROUP BY CUST_CITY<\/pre>\nResult is not deleted from cache, result object became invalid and dependency object is still published. But a counter allows checking the number of invalidations that occurred on a dependency object. In fact, the next time the statement will be executed, the cache will be updated.<\/p>\n
I suppose the result set is not deleted because of its location in the shared pool. According to Oracle documentation, in general, any item in the shared pool remains there until it is flushed according to a modified LRU algorithm (see\u00a0Memory Architecture<\/a>).<\/p>\n-- The statement 'SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY' has been executed several times again in two different SH session with result cache\n-- SYS\nSQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;\n ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS \u00a0 \u00a0INVALIDATIONS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------\n\u00a0 0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dependency Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1\n\u00a010 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt \u00a0 \u00a0 9mxfx9vgpfzacd7c143887rgg1 \u00a0 \u00a0 Result \u00a0 \u00a0 Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 S GROUP BY CUST_CITY\n\u00a0 1 SELECT CUST_CITY, COUNT(*) FROM CUSTOMER dg3tccfff51dj1n3932f7wjypt \u00a0 \u00a0 9mxfx9vgpfzacd7c143887rgg1 \u00a0 \u00a0 Result \u00a0 \u00a0 Invalid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 S GROUP BY CUST_CITY\nSQL> select * from V$RESULT_CACHE_STATISTICS;\n\u00a0ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 VALUE\n--- ---------------------------------------- ----------\n\u00a0 1 Block Size (Bytes) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1024\n\u00a0 2 Block Count Maximum \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a04096\n\u00a0 3 Block Count Current \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a032\n\u00a0 4 Result Size Maximum (Blocks) \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 204\n\u00a0 5 Create Count Success \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2\n\u00a0 6 Create Count Failure \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 7 Find Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 14\n\u00a0 8 Invalidation Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1\n\u00a0 9 Delete Count Invalid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a010 Delete Count Valid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a011 Hash Chain Length \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a01\n\u00a012 Find Copy Count \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00<\/pre>\nA new updated result object has been created, a new object is present in v$result_cache_objects and “Create Count Success” is increased. Result cache memory can be consumed by invalid results which always need to be refreshed if the table is frequently updated. To check if the cache is efficient, check the ratio between “Create Count Success” and “Find Count” and the value of “Invalidation Count”.<\/p>\n
Result cache with views<\/h3>\n
Now, I would like to see result cache behaviour with views. I have created a view with the same statement whose result is already in the cache.<\/p>\n
-- SH\nSQL> CREATE VIEW customer_cities AS SELECT cust_city, count(*) as total_customers FROM sh.customers GROUP BY cust_city;\nView created.\nSQL> select * from customer_cities;\n620 rows selected.\nElapsed: 00:00:00.02\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1577413243\n--------------------------------------------------------------------------------------------------\n| Id \u00a0| Operation \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Rows \u00a0| Bytes | Cost (%CPU)| Time \u00a0 \u00a0 |\n--------------------------------------------------------------------------------------------------\n| \u00a0 0 | SELECT STATEMENT \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 1 | \u00a0RESULT CACHE \u00a0 \u00a0 \u00a0 | 3ysncryjpt7d11yrf9vs04ctmd | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|\n| \u00a0 2 | \u00a0 HASH GROUP BY \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| \u00a0 620 | \u00a06200 | \u00a0 407 \u00a0 (1)| 00:00:05 |\n| \u00a0 3 | \u00a0 \u00a0TABLE ACCESS FULL| CUSTOMERS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 55500 | \u00a0 541K| \u00a0 405 \u00a0 (1)| 00:00:05 |\n--------------------------------------------------------------------------------------------------\n-- SYS\nSQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;\n\u00a0ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS \u00a0 \u00a0INVALIDATIONS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------\n\u00a028 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dependency Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a029 select * from customer_cities \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a03ysncryjpt7d11yrf9vs04ctmd \u00a0 \u00a0 3yjy9v421rggpfdpw9b6tvwjky \u00a0 \u00a0 Result \u00a0 \u00a0 Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0<\/pre>\nOracle did not use the existing result cache although the query is the same. A new result cache and a new dependency object on the view have been created. The new dependency object is the view itself. Is Oracle capable of invalidating the cache if the table customers change?<\/p>\n
SQL> update sh.customers set cust_gender=upper(cust_gender) where rownum <=1;1 row updated.\nSQL> commit;\nCommit complete.\nSQL> SELECT id, name, cache_id, cache_key, type, status, invalidations FROM v$result_cache_objects;\n\u00a0ID NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS \u00a0 \u00a0INVALIDATIONS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------\n\u00a028 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SH.CUSTOMER_CITIES \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Dependency Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a029 select * from customer_cities \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a03ysncryjpt7d11yrf9vs04ctmd \u00a0 \u00a0 3yjy9v421rggpfdpw9b6tvwjky \u00a0 \u00a0 Result \u00a0 \u00a0 Invalid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0<\/pre>\nThe result set is now invalid. Oracle associated this result cache with dependency object SH.CUSTOMERS already present in the cache.<\/p>\n
PL\/SQL and binding variable<\/h3>\n
To test result cache behaviour within PL\/SQL, I have created a function that returns the full name of a customer passing the customer id. Then I have simulated a program which needs to print out the full name of many customers by looping on this function and I have checked result cache usage.<\/p>\n
-- SH session\nCREATE OR REPLACE FUNCTION getCustomerName(customer_id number)\nRETURN varchar2\nAS\nfirst_name customers.cust_first_name%TYPE;\nlast_name customers.cust_last_name%TYPE;\nBEGIN\nSELECT cust_first_name, cust_last_name into first_name, last_name FROM customers where cust_id = customer_id;\nRETURN first_name || ', ' || last_name;\nEND;\nSQL> DECLARE\u00a0 2 \u00a0cust_full_name varchar2(80);\n\u00a0 3 \u00a0BEGIN\n\u00a0 4 \u00a0FOR x IN 1500 .. 1999 LOOP\n\u00a0 5 \u00a0cust_full_name:=getCustomerName(x);\n\u00a0 6 \u00a0END LOOP;\n\u00a0 7 \u00a0END;\n\u00a0 8 \u00a0\/\n-- SYS\nID \u00a0NAME \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0CACHE_ID \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CACHE_KEY \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0TYPE \u00a0 \u00a0 \u00a0 STATUS \u00a0 \u00a0INVALIDATIONS\n--- ---------------------------------------- ------------------------------ ------------------------------ ---------- --------- -------------\n\u00a0xx SELECT CUST_FIRST_NAME, CUST_LAST_NAME F xxxxxxxxxxxxxxxxxxxxxxxxxx \u00a0 \u00a0 xxxxxxxxxxxxxxxxxxxxxxxxxx \u00a0 \u00a0 Result \u00a0 \u00a0 Published \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\n\u00a0 \u00a0 ROM CUSTOMERS WHERE CUST_ID = :B1<\/pre>\nWe notice two things, PL\/SQL access to the result cache and bind variable has been recognized. The parameter has been replaced in the SQL text but as each result had a different result, logically result cache created a result set for each value of the bind value.
\nIt seems that in this configuration, result cache can be efficient only if the statement will be re-executed with the same value for the bind variable because the first execution just creates the result object. As the result cache memory is limited, a single execution can replace old result object by new object that may be not used later.<\/p>\n
Conclusion<\/h3>\n
Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter’s table) or on complex statement consumming time in computing the same data.<\/p>\n
But if the “Created Count Success” is much larger than “Find count”, result cache could not be very efficient, i.e. created objects are not used before being replaced by new objects.<\/p>\n
So result cache can be a useful feature, but we need to pay attention on memory consumption and also on which statement offers the best benefit.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle has introduced three new caching features in its Oracle 11g release:<\/p>\n
\n- Query result cache<\/li>\n
- Client OCI result cache<\/li>\n
- PL\/SQL function result cache<\/li>\n<\/ul>\n
In this posting, I will present the query result cache feature.<\/p>\n","protected":false},"author":16,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[17,67,303],"type_dbi":[],"class_list":["post-2438","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-oracle-11g","tag-performance","tag-result-cache"],"acf":[],"yoast_head":"\n
'Query result cache' in Oracle 11g - dbi Blog<\/title>\n<meta name=\"description\" content=\"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"'Query result cache' in Oracle 11g\" \/>\n<meta property=\"og:description\" content=\"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-01-05T19:22:00+00:00\" \/>\n<meta name=\"author\" content=\"Nicolas Jardot\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nicolas Jardot\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\"},\"author\":{\"name\":\"Nicolas Jardot\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed\"},\"headline\":\"‘Query result cache’ in Oracle 11g\",\"datePublished\":\"2012-01-05T19:22:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\"},\"wordCount\":1216,\"commentCount\":0,\"keywords\":[\"Oracle 11g\",\"Performance\",\"Result cache\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\",\"name\":\"'Query result cache' in Oracle 11g - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2012-01-05T19:22:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed\"},\"description\":\"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"‘Query result cache’ in Oracle 11g\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed\",\"name\":\"Nicolas Jardot\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g\",\"caption\":\"Nicolas Jardot\"},\"description\":\"Nicolas Jardot is a senior consultant with more than nine years of experience in Oracle database infrastructure management and optimization. In addition to standard operations, he is specialized in the performance optimization and tuning of Oracle databases. He also has a strong knowledge of SQL language and has developed several PL\/SQL packages to simplify the administration of database applications. He also maintains the DMK_SQL package of dbi services\u2019 Database Management Kit. Nicolas is Oracle Certified Professional 11g\/12c and Oracle Certified Expert Performance Management and Tuning 12c and holds speeches around Oracle technologies in various conferences including Oracle OpenWorld and UKOUG. Over time, Nicolas has become increasingly interested in Cloud and automation technologies. He has been working for over two years on building and maintaining applications in AWS and operates a CI\/CD pipeline to provision and configure the infrastructure. Nicolas is also certified AWS Solution Architect Professional. Prior to dbi services, Nicolas Jardot was C++ developer on an application virtualization solution, which gave him a solid experience in virtualization and centralization of applications. Nicolas Jardot holds an Engineer\u2019s Degree in Computer Science from the University of Technology of Belfort-Montb\u00e9liard (F). His branch-related experience covers Pharma, Public Sector, Health, Real Estate, Automotive, etc.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/nicolas-jardot-762b9535\/\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nicolas-jardot\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"'Query result cache' in Oracle 11g - dbi Blog","description":"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/","og_locale":"en_US","og_type":"article","og_title":"'Query result cache' in Oracle 11g","og_description":"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.","og_url":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/","og_site_name":"dbi Blog","article_published_time":"2012-01-05T19:22:00+00:00","author":"Nicolas Jardot","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nicolas Jardot","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/"},"author":{"name":"Nicolas Jardot","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed"},"headline":"‘Query result cache’ in Oracle 11g","datePublished":"2012-01-05T19:22:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/"},"wordCount":1216,"commentCount":0,"keywords":["Oracle 11g","Performance","Result cache"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/","url":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/","name":"'Query result cache' in Oracle 11g - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2012-01-05T19:22:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed"},"description":"Query result cache may be a useful feature for SQL statement on relatively static tables (such as parameter's table) or on complex statement consumming time in computing the same data.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/query-result-cache-overview-in-oracle-11g\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"‘Query result cache’ in Oracle 11g"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0aa30f52275a132e9cc2c387708f84ed","name":"Nicolas Jardot","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/112c023c253239221e61a24d59db49d57b354e10da6e7c074cfff50e1b5a6dd8?s=96&d=mm&r=g","caption":"Nicolas Jardot"},"description":"Nicolas Jardot is a senior consultant with more than nine years of experience in Oracle database infrastructure management and optimization. In addition to standard operations, he is specialized in the performance optimization and tuning of Oracle databases. He also has a strong knowledge of SQL language and has developed several PL\/SQL packages to simplify the administration of database applications. He also maintains the DMK_SQL package of dbi services\u2019 Database Management Kit. Nicolas is Oracle Certified Professional 11g\/12c and Oracle Certified Expert Performance Management and Tuning 12c and holds speeches around Oracle technologies in various conferences including Oracle OpenWorld and UKOUG. Over time, Nicolas has become increasingly interested in Cloud and automation technologies. He has been working for over two years on building and maintaining applications in AWS and operates a CI\/CD pipeline to provision and configure the infrastructure. Nicolas is also certified AWS Solution Architect Professional. Prior to dbi services, Nicolas Jardot was C++ developer on an application virtualization solution, which gave him a solid experience in virtualization and centralization of applications. Nicolas Jardot holds an Engineer\u2019s Degree in Computer Science from the University of Technology of Belfort-Montb\u00e9liard (F). His branch-related experience covers Pharma, Public Sector, Health, Real Estate, Automotive, etc.","sameAs":["https:\/\/www.linkedin.com\/in\/nicolas-jardot-762b9535\/"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/nicolas-jardot\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2438","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2438"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2438\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2438"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2438"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}