Oracle Database Enterprise Edition 12.2.0.1.0, running on an Oracle Database Appliance X7-2-HA in an active-active configuration.
We have a query of the following form, which is only ever run by one session at a time:
insert into GLOBAL_TEMPORARY_TABLE
select max(x), max(y)
from STAGING_TABLE s1 join STAGING_TABLE s2 on (…)
The performance seems to deteriorate as the number of records increases (3 minutes for 15k; 3+ hours for 100k; 24+ hours for 300k), but it will very occasionally complete large batches in seconds. We have not been able to find any pattern to the periods of good performance.
Monitoring GV$SESSION while a long query is ongoing shows huge amounts of time spent in “buffer busy waits,” “gc cr block 2-way,” “db file sequential read,” and “latch: cache buffers chains;” the objects affected by these wait events are STAGING_TABLE and its indexes, as well as the UNDO tablespace.
We are still gathering detailed info as instructed in the SQL FAQ (will make a new thread when ready), but I am curious about the observed behavior - as only one session at a time ever uses this table, why is so much time consumed in wait events?