11gR2 - The Result Cache Background Process (RCBG)
There's lack of information about the Result Cache background process (RCBG) out there. Could be due to the fact that normally it causes no problems, then few people write about it.
I've faced a situation were several sessions opened by RCBG were using the default temporary tablespace. When I had to downsize this tablespace, I found a problem: These sessions won't evict themselves from the tablespace, thus the tablespace can't be dropped.
Can I disconnect/kill these sessions? Some people suggest yes, some suggest no. Let's find the correct answer!
# First, check the related parameterization
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 512M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
# "result_cache_mode" is "MANUAL", the default config. It means it will be used when specified via the RESULT_CACHE hint, or via table annotation.
# "result_cache_max_size" is "512M". It means it won't use more than 512M. It takes memory from the shared pool.
# "result_cache_max_result" is "5". It means only 5% of the total 512M will be used per result set.
# Check which is the OS process for RCBG
SQL> !ps -ef|grep rcbg
oracle 9162 1 0 10:22 ? 00:00:00 ora_rcbg_cvtst1
oracle 14514 14502 0 11:02 pts/0 00:00:00 /bin/bash -c ps -ef|grep rcbg
oracle 14516 14514 0 11:02 pts/0 00:00:00 grep rcbg
# Flush the Result Cache
SQL> exec dbms_result_cache.flush;
Procedimiento PL/SQL terminado correctamente.
# Check the statistics of Result Cache
SQL> col name format a30
SQL> col value format a20
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 0
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# "Block Size" is 1024 bytes. It's Result Cache specific and mustn't be confused with the db block size.
# "Block Count Maximum" is the result_cache_max_size (524288*1024=512M).
# "Result Size Maximum (Blocks)" is the result_cache_max_result (26214*1024=26M, which is 5% of the result_cache_max_size)
# The other indicators are 0 because we had flushed the Result Cache
# Create a report of the Result Cache
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 12768 bytes [0.003% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
Procedimiento PL/SQL terminado correctamente.
# The reason of these values is we had flushed the Result Cache.
# Use the Result Cache feature by using the RESULT_CACHE hint (use a table from your db)
SQL> set timing on
SQL> select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:13.07
SQL> set timing off
# Check the statistics of Result Cache (again)
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 13664
Result Size Maximum (Blocks) 26214
Create Count Success 1
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 1
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note that the "Block Count Current" increased from 0 to 13664. This means we have cached 13664 blocks.
# If we multiply the cached blocks (13664) with the block size (1024 bytes), we realize we have cached 13,3M.
# "Create Count Success" is 1, which is the number of cache results successfully created
# "Hash Chain Length" is also 1, which is the average length of items in the hash chain
# Execute again the query and check in the execution plan if the RESULT_CACHE hint was used successfully (it should have taken the blocks from the cache)
SQL> set autotrace on
SQL> set timing on
select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:11.07
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3011663700
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 247K| 12M| 567 (1)| 00:00:07 |
| 1 | RESULT CACHE | 0rtbq1v9uk4890hubjsac6s92q | | | | |
| 2 | TABLE ACCESS FULL| TRK_TRACKING_EMBED | 247K| 12M| 567 (1)| 00:00:07 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=4; dependencies=(APP_ESDC81.TRK_TRACKING_EMBED); name="select /*+ RESULT_CACHE */ * from
APP_ESDC81.TRK_TRACKING_EMBED"
EstadÃsticas
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
14989919 bytes sent via SQL*Net to client
182002 bytes received via SQL*Net from client
16500 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
247475 rows processed
# Note that the time spent has decreased
# Note that the RESULT_CACHE hint was successfully used.
# Note that the values of "consistent gets" and "physical reads" are cero - no time was spent here!
# Check status of Result Cache (again)
SQL> set timing off
SQL> set autotrace off
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 13664
Result Size Maximum (Blocks) 26214
Create Count Success 1
Create Count Failure 0
Find Count 1
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 1
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note the "Find Count" increased to 1. It means that a result set was found on the Result Cache once (if we execute again the same query, we will see this indicator increase)
# Create a report of the Result Cache (again)
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 14294392 bytes [3.408% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 14281624 bytes [3.405% of the Shared Pool]
....... Overhead = 289688 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 128K bytes (16K slots)
........... Chunk Maps = 64K bytes
........... Miscellaneous = 27544 bytes
....... Cache Memory = 13664K bytes (13664 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 13664 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 13663 blocks
................... SQL = 13663 blocks (1 count)
Procedimiento PL/SQL terminado correctamente.
# Note that the "Total Memory" has increased to 14294392 bytes, which is 3.4% of the Shared Pool.
# Note that there are several indicators that have changed.
# Now we are ready to see what happens if we disconnect the sessions being used by RCBG and retry!
# First, find the sessions being used by RCBG:
SQL> select inst_id, sid, serial#, program, status, last_call_et from gv$session where program like '%RCBG%' order by 1,2,3,4;
INST_ID SID SERIAL# PROGRAM STATUS LAST_CALL_ET
---------- ---------- ---------- ------------------------------------------------ -------- ------------
1 225 13 oracle@bat-cvracdb01 (RCBG) ACTIVE 2496
2 646 1 oracle@bat-cvracdb02 (RCBG) ACTIVE 3051
# Disconnect these sessions
SQL> alter system disconnect session '225,13,@1' immediate;
SQL> alter system disconnect session '646,1,@2' immediate;
# Confirm they were disconnected from the database
SQL> select inst_id, sid, serial#, program, status, last_call_et from gv$session where program like '%RCBG%' order by 1,2,3,4;
ninguna fila seleccionada
# Confirm RCBG has disappeared from the OS
SQL> !ps -ef|grep rcbg
oracle 863 736 0 16:08 pts/0 00:00:00 /bin/bash -c ps -ef|grep rcbg
oracle 865 863 0 16:08 pts/0 00:00:00 grep rcbg
# Check on the other nodes, if they exist
SQL> !ssh bat-cvracdb02 ps -ef|grep rcbg
# Now that there's no RCBG process running, let's see what happens with the Result Cache
# Flush the Result Cache (again)
SQL> exec dbms_result_cache.flush;
Procedimiento PL/SQL terminado correctamente.
# Check status of Result Cache
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 0
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note that the values were reset
# Create a report of the Result Cache (again)
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 12768 bytes [0.003% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
Procedimiento PL/SQL terminado correctamente.
# Remember we had flushed the Result Cache
# Now try to use the Result Cache feature by using the RESULT_CACHE hint
SQL> set timing on
SQL> select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:13.80
# Check status of Result Cache (again)
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 32
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 1
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# This is interesting. This time, only 32 blocks were used, but one attempt to cache a result set was unsuccessful! (Create Count Failure=1)
With this, we can conclude that killing the Result Cache Background Process (RCBG) the database won't crash, even though some people say it will.
But keep in mind you won't be able to use the Result Cache until you restart the database and the RCBG processes start as well, therefore, only kill RCBG if you are not using Result Cache. In case you prefer to disable it completely, set result_cache_max_size=0 and restart the database.
I've faced a situation were several sessions opened by RCBG were using the default temporary tablespace. When I had to downsize this tablespace, I found a problem: These sessions won't evict themselves from the tablespace, thus the tablespace can't be dropped.
Can I disconnect/kill these sessions? Some people suggest yes, some suggest no. Let's find the correct answer!
# First, check the related parameterization
SQL> show parameter result
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 512M
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
# "result_cache_mode" is "MANUAL", the default config. It means it will be used when specified via the RESULT_CACHE hint, or via table annotation.
# "result_cache_max_size" is "512M". It means it won't use more than 512M. It takes memory from the shared pool.
# "result_cache_max_result" is "5". It means only 5% of the total 512M will be used per result set.
# Check which is the OS process for RCBG
SQL> !ps -ef|grep rcbg
oracle 9162 1 0 10:22 ? 00:00:00 ora_rcbg_cvtst1
oracle 14514 14502 0 11:02 pts/0 00:00:00 /bin/bash -c ps -ef|grep rcbg
oracle 14516 14514 0 11:02 pts/0 00:00:00 grep rcbg
# Flush the Result Cache
SQL> exec dbms_result_cache.flush;
Procedimiento PL/SQL terminado correctamente.
# Check the statistics of Result Cache
SQL> col name format a30
SQL> col value format a20
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 0
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# "Block Size" is 1024 bytes. It's Result Cache specific and mustn't be confused with the db block size.
# "Block Count Maximum" is the result_cache_max_size (524288*1024=512M).
# "Result Size Maximum (Blocks)" is the result_cache_max_result (26214*1024=26M, which is 5% of the result_cache_max_size)
# The other indicators are 0 because we had flushed the Result Cache
# Create a report of the Result Cache
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 12768 bytes [0.003% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
Procedimiento PL/SQL terminado correctamente.
# The reason of these values is we had flushed the Result Cache.
# Use the Result Cache feature by using the RESULT_CACHE hint (use a table from your db)
SQL> set timing on
SQL> select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:13.07
SQL> set timing off
# Check the statistics of Result Cache (again)
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 13664
Result Size Maximum (Blocks) 26214
Create Count Success 1
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 1
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note that the "Block Count Current" increased from 0 to 13664. This means we have cached 13664 blocks.
# If we multiply the cached blocks (13664) with the block size (1024 bytes), we realize we have cached 13,3M.
# "Create Count Success" is 1, which is the number of cache results successfully created
# "Hash Chain Length" is also 1, which is the average length of items in the hash chain
# Execute again the query and check in the execution plan if the RESULT_CACHE hint was used successfully (it should have taken the blocks from the cache)
SQL> set autotrace on
SQL> set timing on
select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:11.07
Plan de Ejecución
----------------------------------------------------------
Plan hash value: 3011663700
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 247K| 12M| 567 (1)| 00:00:07 |
| 1 | RESULT CACHE | 0rtbq1v9uk4890hubjsac6s92q | | | | |
| 2 | TABLE ACCESS FULL| TRK_TRACKING_EMBED | 247K| 12M| 567 (1)| 00:00:07 |
-------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=4; dependencies=(APP_ESDC81.TRK_TRACKING_EMBED); name="select /*+ RESULT_CACHE */ * from
APP_ESDC81.TRK_TRACKING_EMBED"
EstadÃsticas
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
14989919 bytes sent via SQL*Net to client
182002 bytes received via SQL*Net from client
16500 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
247475 rows processed
# Note that the time spent has decreased
# Note that the RESULT_CACHE hint was successfully used.
# Note that the values of "consistent gets" and "physical reads" are cero - no time was spent here!
# Check status of Result Cache (again)
SQL> set timing off
SQL> set autotrace off
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 13664
Result Size Maximum (Blocks) 26214
Create Count Success 1
Create Count Failure 0
Find Count 1
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 1
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note the "Find Count" increased to 1. It means that a result set was found on the Result Cache once (if we execute again the same query, we will see this indicator increase)
# Create a report of the Result Cache (again)
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 14294392 bytes [3.408% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 14281624 bytes [3.405% of the Shared Pool]
....... Overhead = 289688 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 128K bytes (16K slots)
........... Chunk Maps = 64K bytes
........... Miscellaneous = 27544 bytes
....... Cache Memory = 13664K bytes (13664 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 13664 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 13663 blocks
................... SQL = 13663 blocks (1 count)
Procedimiento PL/SQL terminado correctamente.
# Note that the "Total Memory" has increased to 14294392 bytes, which is 3.4% of the Shared Pool.
# Note that there are several indicators that have changed.
# Now we are ready to see what happens if we disconnect the sessions being used by RCBG and retry!
# First, find the sessions being used by RCBG:
SQL> select inst_id, sid, serial#, program, status, last_call_et from gv$session where program like '%RCBG%' order by 1,2,3,4;
INST_ID SID SERIAL# PROGRAM STATUS LAST_CALL_ET
---------- ---------- ---------- ------------------------------------------------ -------- ------------
1 225 13 oracle@bat-cvracdb01 (RCBG) ACTIVE 2496
2 646 1 oracle@bat-cvracdb02 (RCBG) ACTIVE 3051
# Disconnect these sessions
SQL> alter system disconnect session '225,13,@1' immediate;
SQL> alter system disconnect session '646,1,@2' immediate;
# Confirm they were disconnected from the database
SQL> select inst_id, sid, serial#, program, status, last_call_et from gv$session where program like '%RCBG%' order by 1,2,3,4;
ninguna fila seleccionada
# Confirm RCBG has disappeared from the OS
SQL> !ps -ef|grep rcbg
oracle 863 736 0 16:08 pts/0 00:00:00 /bin/bash -c ps -ef|grep rcbg
oracle 865 863 0 16:08 pts/0 00:00:00 grep rcbg
# Check on the other nodes, if they exist
SQL> !ssh bat-cvracdb02 ps -ef|grep rcbg
# Now that there's no RCBG process running, let's see what happens with the Result Cache
# Flush the Result Cache (again)
SQL> exec dbms_result_cache.flush;
Procedimiento PL/SQL terminado correctamente.
# Check status of Result Cache
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 0
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 0
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# Note that the values were reset
# Create a report of the Result Cache (again)
SQL> SET SERVEROUTPUT ON;
SQL> execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 512M bytes (512K blocks)
Maximum Result Size = 26214K bytes (26214 blocks)
[Memory]
Total Memory = 12768 bytes [0.003% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.003% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... = 2008 bytes
....... Bloom Fltr = 2K bytes
....... RAC Cbk = 5408 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
Procedimiento PL/SQL terminado correctamente.
# Remember we had flushed the Result Cache
# Now try to use the Result Cache feature by using the RESULT_CACHE hint
SQL> set timing on
SQL> select /*+ RESULT_CACHE */ * from APP_ESDC81.TRK_TRACKING_EMBED;
(...)
247475 filas seleccionadas.
Transcurrido: 00:00:13.80
# Check status of Result Cache (again)
SQL> select name, value from v$result_cache_statistics order by id;
NAME VALUE
------------------------------ --------------------
Block Size (Bytes) 1024
Block Count Maximum 524288
Block Count Current 32
Result Size Maximum (Blocks) 26214
Create Count Success 0
Create Count Failure 1
Find Count 0
Invalidation Count 0
Delete Count Invalid 0
Delete Count Valid 0
Hash Chain Length 0
Find Copy Count 0
Global Hit Count 0
Global Miss Count 0
14 filas seleccionadas.
# This is interesting. This time, only 32 blocks were used, but one attempt to cache a result set was unsuccessful! (Create Count Failure=1)
With this, we can conclude that killing the Result Cache Background Process (RCBG) the database won't crash, even though some people say it will.
But keep in mind you won't be able to use the Result Cache until you restart the database and the RCBG processes start as well, therefore, only kill RCBG if you are not using Result Cache. In case you prefer to disable it completely, set result_cache_max_size=0 and restart the database.
No comments:
Post a Comment