Co-worker found note RMAN Backup is Slow after 19.11 Release Update ( Doc ID
2846457.1<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-30379120441&id=2846457.1>
).
I set pga_aggregate_limit in the CDB and PDB.
I set pga_aggregate_target in the PDB to the same as in the CDB.
Trace file shows:
Adjusting buffer count to honor available PGA; old bufcnt: 4, new bufcnt: 2,
pga_agg_limit: 0, pga_agg_target: 0, pga_agg_used: 881709295, 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: 881709295, 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: 881709295, 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: 881709295, 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: 881709295, 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: 881709295, 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: 881709295, 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: 881709295, 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
[dbsorat06:/oracle/db/diag/rdbms/cdb01t06/cdb01t06/trace:] $
You will noitice in the above pga_agg_limit and target are showing as 0!!!!!
This is an EBS database, as such, the PDB name is LOWER CASE. could that be
causing the problem?
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Mark W. Farnham
Sent: Tuesday, August 16, 2022 9:03 AM
To: gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: [EXTERNAL] Re: rman backup slow even after patching it
In a rare factual error my friend Mladen missed that the list is ascending.
Still probably a network issue. Smirk. Unless that is backup & recovery read
time (The key thing that *might* tell you a *lot* would be separation of I/O
into I and O). I cannot remember their detailed description of this event and
I'm too lazy to too it up. But I did re-arrange the output for that one line
into "What" and useful "How much" columns.
Notice that the "also ran" second place was only about a minute of total wait.
So this one bundled line for I and O is pretty much everything. So 41
milliseconds per I/O and I have no idea what a raw system level I/O between
your source system and backup system is, or what size RMAN's I/O unit is (min,
max, average) as configured. If you have some idea of the total bytes written,
you could do a system to system level I/O of that size (maybe a bundle of 100
or 1000 repeats, so any possible begin and end process time error is minimized)
and get a clue whether I/O time between systems is the problem, rather than
something more complex inside Oracle.
Without measuring a damn thing, the first thing I would do is check whether the
"mount" strings for your destination are different between 11 and 19.
If that's not it, the system to system i/o speed check is next up in figuring
out what is next. (My smirk is that your new 19 system probably has a network
consumption limiter on some Quality of Service protected Virtual network that
someone forgot is 100% for you. That's also worth a quick non-scientific ask if
your relationship with the network team is good. IF they got it wrong, the
reactions I've seen range from "oops, sorry, we'll try not to do that again" to
"Grr. I'll fix it, but I'll get you back sooner or later. (in thought) while
saying "YOU should have told us you needed a non-standard QOS configuration."
Converting warring camps (sysadmin, storage admin, network admin, DBAs) into
teams was both the most challenging part of my career and when occasionally
successful the best feeling.
Good luck.
mwf
What: 38 RMAN backup & recovery I/O System I/O 3
How much: total waits timeouts ave wait max wait time waited in seconds
time waited in hours
125702 0 4.1 (cs) 178
5159.53525 1.42
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: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala
Sent: Monday, August 15, 2022 11:46 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: rman backup slow even after patching it
On 8/15/22 15:16, Beckstrom, Jeffrey wrote:
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
Jeff, I wonder how is it that an experienced DBA like you still hasn't learned
one of the basic rules of database administration: whenever it is unclear
what's going on, it's a network problem. Network engineers have to prove their
innocence, over and over again. You can sweeten the things up by saying that it
used to work until yesterday and then by asking them what did they do
yesterday? Being a network engineer entails large dose of masochism and equally
large dose of sadism. Network engineers are blamed for everything, including
the climate change, but they also can prevent any progress in your projects by
being strict enough with the firewall.
Your most waited on event is SQL Net message to client. That means that one of
your clients cannot receive data as fast as Oracle can deliver. Sounds rare,
and it is, at least to my knowledge. This very probably is a network problem.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com<https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=05%7C01%7Cjbeckstrom%40gcrta.org%7C32e3e421c1d54b96bd8908da7f87cf31%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637962518529465299%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=Ioj8t%2BmwW7bWzhkjrB4kiYzjhYsEGicOkrJKCPltixw%3D&reserved=0>
--
//www.freelists.org/webpage/oracle-l<https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=05%7C01%7Cjbeckstrom%40gcrta.org%7C32e3e421c1d54b96bd8908da7f87cf31%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637962518529465299%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=fd8VvGXv7QA4rzzNelkIyGxByrG3dJxcs%2BSrprspmyA%3D&reserved=0>