You Asked
Hi Chris, Connor
As a part of daily archival process we:
1. Insert around 4.5 millions of records from Table T1 from APP_SCHEMA1 to ARCHIVAL_SCHEMA
2. One step# 1 completes, DELETE corresponding records from APP_SCHEMA1
This activity takes hours to complete.
Since T1 is daily partitioned in both APP_SCHEMA1 & ARCHIVAL_SCHEMA, I am tried exchange partitions technique on lower environment with limited no of rows in to get rid of native INSERT then DELETE approach --> This works perfectly fine but i have few queries like
1. How exactly exchange partition works in Oracle Architecture -- if you could share any link, blog
2. considering around 4.5 million of rows daily in production, will this impact any memory usages?
3. is there any was i can take a look of memory consumption.
and Connor said...
You can monitor PGA via the performance statistics, eg
select
st.sid,
s.name,
st.value
from v$statname s, v$sesstat st
where st.STATISTIC# = s.STATISTIC#
and s.name like 'session pga memory%'
but exchange can be a very efficient process, because we are simply manipulating the dictionary to point at different database segments.
Where exchange *can* get expensive is that we validate the data coming in to make sure it matches the appropriate partition definition. If you have a guarantee that the data is correct, you use the WITHOUT VALIDATION clause to avoid this.
More details in the docs
https://docs.oracle.com/database/121/VLDBG/GUID-E08650B4-06B1-43F9-91B0-FBF685A3B848.htm#VLDBG1156