hi, i'm working on a 12c EE Extreme Perf Release 12.2.0.1.0 instance.
I've a table TABLE1 with about 1 millions of records and a procedure, executed on request by a job, that deletes and inserts about 300k of that table's records in about 10 minutes.
Than the SP executes about 10 millions of insert, for about other 90 minutes, in another table TABLE2 with a FK to TABLE1.
I don't have any explicit transaction management within the SP.
I have an external app that must frequently execute this query "select max(COLUMN1) from TABLE1",
the query usually is immediate but, when the SP is running, it has always his duration increased up to 30/60 seconds that isn't acceptable for my requirements.
Note: it hasn't any lock, i can execute it multiple times while SP is running and it uses the default read-commit behavior so it is correctly returning the value of the data without the SP updates.
I'm pretty new to oracle so i don't know if i can do something to make it faster, i cannot understand why it's impacted by a concurrent not-committed transaction.
thanks for your suggestions