Skip to Main Content
  • Questions

  • RMAN restore performance based in ASM

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, yulin.

Asked: November 05, 2020 - 12:26 pm UTC

Last updated: February 10, 2021 - 4:54 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Enviroment: RHEL 6.9 udev asmdisk DB 11.2.0.4

Scene A: MEMORY_TARGET=4GB PGA_TARGET=1GB with default _backup_disk/file_bufsz/cnt values
restore 17TB full completed for 42 Hrs with the approximate 21MB PGA used each channel.

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE TYPE
---------------------------------------------------------------- ------------ ---------- ------------------- -------------
shared pool                                                               656        528                   0 GROW
large pool                                                                416        416                   0 STATIC
java pool                                                                  64         64                   0 STATIC
streams pool                                                                0          0                   0 STATIC
SGA Target                                                               3072       3072                   0 STATIC
DEFAULT buffer cache                                                     1888       1888                   0 SHRINK
KEEP buffer cache                                                           0          0                   0 STATIC
RECYCLE buffer cache                                                        0          0                   0 STATIC
DEFAULT 2K buffer cache                                                     0          0                   0 STATIC
DEFAULT 4K buffer cache                                                     0          0                   0 STATIC
DEFAULT 8K buffer cache                                                     0          0                   0 STATIC
DEFAULT 16K buffer cache                                                    0          0                   0 STATIC
DEFAULT 32K buffer cache                                                    0          0                   0 STATIC
Shared IO Pool                                                              0          0                   0 STATIC
PGA Target                                                               1024       1024                1024 STATIC
ASM Buffer Cache                                                            0          0                   0 STATIC

16 rows selected.



PARAMETER             VALUE              DESCRIPTION                                        Default?
--------------------- ------------------ -------------------------------------------------- ----------
_pga_max_size         209715200          Maximum size of the PGA memory for one process     TRUE
_smm_max_size         102400             maximum work area size in auto mode (serial)       TRUE
_smm_px_max_size      524288             maximum work area size in auto mode (global)       TRUE
pga_aggregate_target  1073741824         Target size for the aggregate PGA memory consumed  FALSE
                                         by the instance
                                         
KSPPINM                        KSPPSTVL        KSPPDESC
------------------------------ --------------- -------------------------------------------------------
_backup_disk_io_slaves         0               BACKUP Disk I/O slaves
_backup_ksfq_bufcnt_max        64              maximum number of buffers used for backup/restore
_backup_ksfq_bufsz             0               size of buffers used for backup/restore
_backup_ksfq_bufcnt            0               number of buffers used for backup/restore
_backup_disk_bufsz             0               size of buffers used for DISK channels
_backup_disk_bufcnt            0               number of buffers used for DISK channels
_backup_file_bufsz             0               size of buffers used for file access
_backup_file_bufcnt            0               number of buffers used for file access

8 rows selected.

SQL> 
SQL>  
   
   
                                         
SPID                     PROGRAM                              EVENT                          PGA_USED_MEM PGA_MAX_MEM
------------------------ ------------------------------------ ------------------------------ ------------ -----------
133295                   rman@JCHXRST (TNS V1-V3)             SQL*Net message from client       106519950   113586358
133308                   rman@JCHXRST (TNS V1-V3)             RMAN backup & recovery I/O         21421923    24588470
133306                   rman@JCHXRST (TNS V1-V3)             RMAN backup & recovery I/O         21421539    24588470
133310                   rman@JCHXRST (TNS V1-V3)             RMAN backup & recovery I/O         21421499    24588470
133305                   rman@JCHXRST (TNS V1-V3)             RMAN backup & recovery I/O         20685411    23998646
133302                   rman@JCHXRST (TNS V1-V3)             SQL*Net message from client          980323     1913014     


SQL> 
SQL> 
SQL> 
SQL> SELECT   SUM(pga_used_mem),   SUM(pga_alloc_mem),    SUM(pga_max_mem) FROM v$process p;

SUM(PGA_USED_MEM) SUM(PGA_ALLOC_MEM) SUM(PGA_MAX_MEM)
----------------- ------------------ ----------------
         76895603           83603020         90222156

SQL> 
SQL>



Scene B: just only adjust the default _backup_disk/file_bufsz/cnt values with the following custom values:
restore the same 17TB full completed for 40 Hrs with the approximate 500MB PGA used each channel.

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE TYPE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- -------------
shared pool                                                               656        528        656                   0 GROW
large pool                                                                416        416        416                   0 STATIC
java pool                                                                  64         64         64                   0 STATIC
streams pool                                                                0          0          0                   0 STATIC
SGA Target                                                               3072       3072       3072                   0 STATIC
DEFAULT buffer cache                                                     1888       1888       1920                   0 SHRINK
KEEP buffer cache                                                           0          0          0                   0 STATIC
RECYCLE buffer cache                                                        0          0          0                   0 STATIC
DEFAULT 2K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 4K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 8K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 16K buffer cache                                                    0          0          0                   0 STATIC
DEFAULT 32K buffer cache                                                    0          0          0                   0 STATIC
Shared IO Pool                                                              0          0          0                   0 STATIC
PGA Target                                                               1024       1024       1024                1024 STATIC
ASM Buffer Cache                                                            0          0          0                   0 STATIC

16 rows selected.

SQL> 
SQL> 

KSPPINM                        KSPPSTVL        KSPPDESC
------------------------------ --------------- -------------------------------------------------------
_backup_disk_io_slaves         0               BACKUP Disk I/O slaves
_backup_ksfq_bufcnt_max        64              maximum number of buffers used for backup/restore
_backup_ksfq_bufsz             0               size of buffers used for backup/restore
_backup_ksfq_bufcnt            0               number of buffers used for backup/restore
_backup_disk_bufsz             4194304         size of buffers used for DISK channels
_backup_disk_bufcnt            16              number of buffers used for DISK channels
_backup_file_bufsz             4194304         size of buffers used for file access
_backup_file_bufcnt            16              number of buffers used for file access

8 rows selected.

SQL> 
SQL> 

SPID                     PROGRAM                                          EVENT                             PGA_USED_MEM PGA_MAX_MEM
------------------------ ------------------------------------------------ --------------------------------- ------------ -----------
427743                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        541512974   549973526
427750                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        541474342   549793974
427745                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        541397894   549793974
427747                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        541364902   549793974
427735                   rman@JCHXRST (TNS V1-V3)                         SQL*Net message from client          106519950   113586358
427742                   rman@JCHXRST (TNS V1-V3)                         SQL*Net message from client            1129766     1454262

6 rows selected.

SQL> 
SQL> 
SQL> 
SQL> SELECT   SUM(pga_used_mem),   SUM(pga_alloc_mem),    SUM(pga_max_mem) FROM v$process p;

SUM(PGA_USED_MEM) SUM(PGA_ALLOC_MEM) SUM(PGA_MAX_MEM)
----------------- ------------------ ----------------
       2350909654         2396434428       2396434428

SQL> 
SQL>



So my question is why the restore performance of the two scenes differ just only with small 5% while the PGA_USED_MEM used each channel of the two scences differ with 10~25 multiples.



and Connor said...

My understanding of the the various "bufsz" is that they are solely intended for file-system activities and not ASM.

So in this case, you specified them and we *used* them (hence the larger PGA), but these are not parameters which will determine overall performance for ASM because there is no file system to be concerned about.

MOS note 1072545.1 has more in-depth discussion on this.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 06, 2020 - 12:56 pm UTC

The following contents are abstracted from the oracle white paper <RMAN performance tuning best practices>:

Note that with Oracle Database 11g Release 2, no buffer parameter changes should be needed when backing up from or to ASM, as all disk buffers are automatically sized for optimal performance.
However, if the desired EFFECTIVE_BYTES_PER_SECOND is not achieved, then the buffers can still be manually adjusted.

Notes: you can see that the EPS of disk_async_io is just too slow about 8MBps .

SQL> 
SQL> 
SQL> select avg(EFFECTIVE_BYTES_PER_SECOND) from v$backup_async_io;

AVG(EFFECTIVE_BYTES_PER_SECOND)
-------------------------------
                     8373334.09

SQL> 
SQL>


Connor McDonald
November 09, 2020 - 3:41 am UTC

Have you gone through all the performance tuning recommendations for RMAN ?

A nice presentation with steps here

http://docshare04.docshare.tips/files/21043/210437276.pdf

In particular, it talks about identifying what the raw performance of your disk infrastructure is. If that's slow, then RMAN can't magically make it better.

If its not slow *and* you've been through *al* of the recommendations, then its time to talk to Support

A reader, November 09, 2020 - 4:29 am UTC


Is it possible to manually adjust & optimize ASM I/O buffers

A reader, November 10, 2020 - 3:32 am UTC

I think that the problem should foucs on the bottleneck of the ASM I/O performance with the RMAN！

So is there any way to manually adjust & optimize the equivalent ASM disks I/O buffer size or cnts？
Connor McDonald
November 11, 2020 - 6:41 am UTC

You *can* adjust those buffers with ASM, I just don't think you'll see much benefit or improvement.

It seems hat the too slow restore problem had nothing to do with the rman itself

A reader, February 10, 2021 - 2:16 am UTC

Finally , we tested the 3rd Scene via increasing the pga_target size from 2GB to 4GB.

Thus the average EBPS nearly doubled from 8MB to 15MB, and meanwhile the ratio of long_waits/io_counts all decreased below 10%。

So at least the problem at DB internal layer could be basically eliminated, but in fact the rman restore still kept almost the same 40Hrs ！
Connor McDonald
February 10, 2021 - 4:54 am UTC

ouch
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.