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@xxxxxxxxxxx>
Sent: Monday, August 15, 2022 3:14 PM
To: Beckstrom, Jeffrey <jbeckstrom@xxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:jbeckstrom@xxxxxxxxx>> wrote:
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
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
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
