问题描述
环境: RHEL 6.9 udev asmdisk DB 11.2.0.4
场景A: MEMORY_TARGET = 4gb PGA_TARGET = 1gb，默认 _ 备份 _ 磁盘/文件 _ bufsz/cnt值
恢复17TB完全完成42小时，每个通道使用大约21mb PGA。
场景B: 只需使用以下自定义值调整默认的 _ backup_disk/file_bufsz/cnt值:
恢复相同的17tb完整完成40小时，每个通道使用大约500mb PGA。
所以我的问题是，为什么两个场景的恢复性能仅在小5% 时有所不同，而PGA_USED_MEM使用的两个场景的每个通道都有10〜25倍的差异。
场景A: MEMORY_TARGET = 4gb PGA_TARGET = 1gb，默认 _ 备份 _ 磁盘/文件 _ bufsz/cnt值
恢复17TB完全完成42小时，每个通道使用大约21mb PGA。
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>
场景B: 只需使用以下自定义值调整默认的 _ backup_disk/file_bufsz/cnt值:
恢复相同的17tb完整完成40小时，每个通道使用大约500mb PGA。
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> 所以我的问题是，为什么两个场景的恢复性能仅在小5% 时有所不同，而PGA_USED_MEM使用的两个场景的每个通道都有10〜25倍的差异。
专家解答
我对各种 “bufsz” 的理解是，它们仅用于文件系统活动，而不是ASM。
因此，在这种情况下，您指定了它们，我们 * 使用了 * 它们 (因此使用了较大的PGA)，但这些参数将决定ASM的整体性能，因为没有文件系统需要关注。
MOS注解1072545.1对此有较深入的研讨。
因此，在这种情况下，您指定了它们，我们 * 使用了 * 它们 (因此使用了较大的PGA)，但这些参数将决定ASM的整体性能，因为没有文件系统需要关注。
MOS注解1072545.1对此有较深入的研讨。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
