RE: [EXTERNAL] Re: rman backup slow even after patching it
Date: Mon, 15 Aug 2022 19:16:33 +0000
Message-ID: <DM6PR09MB467765AE348DD6153FAC1088DF689_at_DM6PR09MB4677.namprd09.prod.outlook.com>
The most significant event is “:
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
---------- --------------------------- ----------- -------------- -----------
AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO EVENT_ID WAIT_CLASS_ID WAIT_CLASS#
------------ ---------- ----------------- ---------- ------------- -----------
WAIT_CLASS CON_ID TIME_WAITED_IN_SEC
-------------------- ---------- ------------------
38 SQL*Net message to client 383 0 0
0 0 424 2067390145 2000153315 7
Network 3 .000424
38 db file scattered read 35 0 31
.9 5 314295 506183215 1740759767 8
User I/O 3 .314295
38 db file single write 188 0 59
.31 2 585623 1307477558 1740759767 8
User I/O 3 .585623
38 db file sequential read 565 0 129
.23 2 1289658 2652584166 1740759767 8
User I/O 3 1.289658
38 control file parallel write 158 0 180
1.14 72 1803209 4078387448 4108307767 9
System I/O 3 1.803209
38 events in waitclass Other 514 1 299
.58 142 2988657 1736664284 1893977003 0
Other 3 2.988657
38 Disk file operations I/O 1672 0 485
.29 16 4851874 166678035 1740759767 8
User I/O 3 4.851874
38 control file sequential rea 5212 0 1643
d
.32 34 16433720 3213517201 4108307767 9
System I/O 3 16.43372
38 SQL*Net message from client 383 0 7017
18.32 1544 70169334 1421975091 2723168908 6
Idle 3 70.169334
38 RMAN backup & recovery I/O 125702 0 515954
4.1 178 5159535248 3107297351 4108307767 9
System I/O 3 5159.53525
From:
1 select sess_ev.*,
2 time_waited_micro/1000000 time_waited_in_sec
3 from v$session_event sess_ev
4 where sid=&1
5* order by time_waited
From: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Sent: Monday, August 15, 2022 3:14 PM
To: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
Cc: oracle-l_at_freelists.org
Subject: [EXTERNAL] Re: rman backup slow even after patching it
Try
Alter session set optimizer_mode=RULE;
Insider man run block…
Also what is the most significant wait event? Is it compute obsolete files?
On Aug 15, 2022, at 1:37 PM, Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>> wrote:
Pga_aggregate targe is set at the cdb to a value. At the pdb level it is 0.
Pga_aggregate_limit is not set anywhere.
Do I need to set the pga_aggregate limit?
Do I need to set both parameters at the PDB level?
Jeffrey Beckstrom
In 19.14, rman backup significantly slower than in 11g. This morning I put on some RMAN patches to no effect. In the rman trace file from a running backup I see:
Adjusting buffer count to honor available PGA; old bufcnt: 4, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 4, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 5, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 6, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 4, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 5, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 8, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Adjusting buffer count to honor available PGA; old bufcnt: 16, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 892876434, pga_available: 0
Backup envisage an unusual situation due to PGA memory availability: we are using only 2 read threads (instead of 9) for the current backup session
Adjusting buffer count to honor available PGA; old bufcnt: 4, new bufcnt: 2, pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 913692181, pga_available: 0
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 15 2022 - 21:16:33 CEST