10 April 2014

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.

07 February 2014

RAC ASM - Resizing online redo log files

It could be somewhat confusing to try to resize the redolog files when you are new on RAC, especially with ASM. Moreover, the procedures I've seen are not clear enough for new "RACers". So let's simplify this task as much as possible:

  # We must drop the redolog files and create new ones with different size

  # But before dropping the redolog files, we must create "temporary" ones, because we just can't let the DB without redolog files

  # Check the redolog groups and their members:

SQL> set linesize 200 pagesize 999
SQL> col member format a80
SQL> select * from v$logfile order by 1, 4;

    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  +DATA/racdb/onlinelog/group_1.261.794500523                                      NO
         1         ONLINE  +FRA/racdb/onlinelog/group_1.257.794500523                                       YES
         2         ONLINE  +DATA/racdb/onlinelog/group_2.262.794500523                                      NO
         2         ONLINE  +FRA/racdb/onlinelog/group_2.258.794500523                                       YES
         3         ONLINE  +DATA/racdb/onlinelog/group_3.265.794500567                                      NO
         3         ONLINE  +FRA/racdb/onlinelog/group_3.259.794500567                                       YES
         4         ONLINE  +DATA/racdb/onlinelog/group_4.266.794500567                                      NO
         4         ONLINE  +FRA/racdb/onlinelog/group_4.260.794500569                                       YES
         5         ONLINE  +DATA/racdb/onlinelog/group_5.314.827942215                                      NO
         5         ONLINE  +FRA/racdb/onlinelog/group_5.295.827942215                                       NO
         6         ONLINE  +DATA/racdb/onlinelog/group_6.315.827942215                                      NO
         6         ONLINE  +FRA/racdb/onlinelog/group_6.396.827942215                                       NO

  # Ok, we have 6 groups with 2 members each (one in diskgroup +DATA, the other in +FRA).

  # Now let's see the thread (instance) of each group, and the status of each group:

SQL> select * from v$log order by 1, 2;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
         1          1     227683   52428800        512          2 YES ACTIVE              9923568924 05/02/2014   9923760849 05/02/2014
         2          1     227684   52428800        512          2 NO  CURRENT             9923760849 05/02/2014   2,8147E+14
         3          2     143283   52428800        512          2 YES INACTIVE            9923488956 05/02/2014   9923620049 05/02/2014
         4          2     143284   52428800        512          2 NO  CURRENT             9923620049 05/02/2014   2,8147E+14
         5          3      34875   52428800        512          2 YES INACTIVE            9923271031 05/02/2014   9923515094 05/02/2014
         6          3      34876   52428800        512          2 NO  CURRENT             9923515094 05/02/2014   2,8147E+14

  # Great, groups 1 and 2 belong to thread 1, groups 2 and 3 to thread 2, and so on.

  # As you can see, each thread has one of its groups with status CURRENT, and the other one with status ACTIVE or INACTIVE.

  # As seen in the previous query, the names of the members seemed to have been created automatically. Let's confirm this:

SQL> select name, value from v$parameter where name in ('db_create_file_dest', 'db_recovery_file_dest');

NAME                                 VALUE
------------------------------------ ------------------------------
db_create_file_dest                  +DATA
db_recovery_file_dest                +FRA

  # Ok, then we will also let Oracle choose the names of the redolog files we are going to create

  # We have 3 threads (3 instances), so we will need 3 commands to create the redolog groups

  # Create the "temporary" redolog groups, using numbers 10, 20, 30, 40, 50 and 60 (because 1, 2, 3, 4, 5 and 6 already exist)

SQL> alter database add logfile thread 1 group 10 size 150M, group 20 size 150M;
SQL> alter database add logfile thread 2 group 30 size 150M, group 40 size 150M;
SQL> alter database add logfile thread 3 group 50 size 150M, group 60 size 150M;

  # Switch logfiles in all instances until none of the old groups are 'CURRENT' (execute in all the instances as many times as needed):

SQL> alter system switch logfile;

  # The old groups must be 'INACTIVE'. If any of them is 'ACTIVE', create a checkpoint (execute once, in only one instance):

SQL> alter system checkpoint;

  # Good, now all the old groups are 'INACTIVE', so we can drop them:

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
SQL> alter database drop logfile group 5;
SQL> alter database drop logfile group 6;

    # Note: We don't need to drop the files manually since ASM already did this for us (otherwise, use "ALTER DISKGROUP... DROP FILE...;")

  # Let's check the redolog groups we have:

SQL> select * from v$log order by 1, 2;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
        10          1     227689  157286400        512          2 NO  CURRENT             9924015408 05/02/2014   2,8147E+14
        20          1     227686  157286400        512          2 YES INACTIVE            9923967008 05/02/2014   9923982967 05/02/2014
        30          2     143286  157286400        512          2 YES INACTIVE            9923894563 05/02/2014   9923983496 05/02/2014
        40          2     143287  157286400        512          2 NO  CURRENT             9923983496 05/02/2014   2,8147E+14
        50          3      34878  157286400        512          2 YES INACTIVE            9923983073 05/02/2014   9923998604 05/02/2014
        60          3      34879  157286400        512          2 NO  CURRENT             9923998604 05/02/2014   2,8147E+14

  # Now we can recreate the redolog files with the new size, and drop the "temporary" ones:

SQL> alter database add logfile thread 1 group 1 size 150M, group 2 size 150M;
SQL> alter database add logfile thread 2 group 3 size 150M, group 4 size 150M;
SQL> alter database add logfile thread 3 group 5 size 150M, group 6 size 150M;

  # Switch logfiles in all instances until none of the "temporary" groups are 'CURRENT' (execute in all the instances as many times as needed):

SQL> alter system switch logfile;

  # The "temporary" groups must be 'INACTIVE'. If any of them is 'ACTIVE', create a checkpoint (execute once, in only one instance):

SQL> alter system checkpoint;

  # Good, now all the "temporary" groups are 'INACTIVE', so we can drop them:

SQL> alter database drop logfile group 10;
SQL> alter database drop logfile group 20;
SQL> alter database drop logfile group 30;
SQL> alter database drop logfile group 40;
SQL> alter database drop logfile group 50;
SQL> alter database drop logfile group 60;

  # Let's check the redolog groups we have now:

SQL> select * from v$log order by 1, 2;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
         1          1     227690  157286400        512          2 YES INACTIVE            9924177798 05/02/2014   9924189055 05/02/2014
         2          1     227691  157286400        512          2 NO  CURRENT             9924189055 05/02/2014   2,8147E+14
         3          2     143288  157286400        512          2 YES INACTIVE            9924178388 05/02/2014   9924202862 05/02/2014
         4          2     143289  157286400        512          2 NO  CURRENT             9924202862 05/02/2014   2,8147E+14
         5          3      34880  157286400        512          2 YES INACTIVE            9924178491 05/02/2014   9924217395 05/02/2014
         6          3      34881  157286400        512          2 NO  CURRENT             9924217395 05/02/2014   2,8147E+14

    # Note: We don't need to drop the files manually since ASM already did this for us (otherwise, use "ALTER DISKGROUP... DROP FILE...;")

  # The end.

I hope my procedure is the simplest and clearer method you've found :)

11gR2 - Using asmcmd with a non-grid user

I was created an OS user to admin both ASM and the RAC database.

In order to be able to connect to the ASM instance, firstly I requested to add my OS user to the asmdba and asmadmin groups.

Now I was able to connect to the ASM instance, but I wasn't able to connect with asmcmd:

$ asmcmd
/u01/app/11.2.0/grid/bin/asmcmd: line 123: /u01/app/11.2.0/grid/perl/bin/perl: Permission denied
/u01/app/11.2.0/grid/bin/asmcmd: line 146: /u01/app/11.2.0/grid/perl/bin/perl: Permission denied
/u01/app/11.2.0/grid/bin/asmcmd: line 146: exec: /u01/app/11.2.0/grid/perl/bin/perl: cannot execute: Permission denied

Then I took a glance at the permissions on that file:

$ ls -ltr /u01/app/11.2.0/grid/perl/bin/perl
-rwx------ 1 grid oinstall 1424555 Jul 21  2011 /u01/app/11.2.0/grid/perl/bin/perl

Oh, that file doesn't have group permissions. Moreover, neither the other executable files in there have!

Is this an expected behavior? Can't I use my OS user to use asmcmd? Did I really have to manually change the permissions?

To answer these questions, I did some research, but there was nothing that satisfied me, until I found Doc ID 1295851.1:

From 11.2.0.1 to 12.1.0.1, this IS an expected behavior. Only grid user should use asmcmd. For security reasons, even though I can, I SHOULDN'T change the permissions. So, either:

1) Request grid's password
2) Change the permissions:

$ cd <GI HOME>/perl
$ chmod -R 750 bin lib man (Original permissions are 700)
$ cd <GI HOME>/lib
$ chmod 750 libexpat.so.1 (Original permissions are 600)

If this was useful to you, or have anything to comment, please go ahead! :)