Hi.
12.1 SE
I have a fairly intensive Materialised view that I do a complete refresh daily. I run it overnight,
I'm using the following syntax.
CREATE Materialized View DA_DEBTORS_ACTIVE_MV
BUILD IMMEDIATE
USING INDEX
REFRESH
FORCE
ON DEMAND
START WITH sysdate + 0
NEXT round(sysdate + 1)+ 01.75 / 24
DISABLE QUERY REWRITE AS
SELECT
*
FROM
da_debtors_active_view;
I notice when I run a refresh manually in SQL Developer, the main application on the database slows down quite considerably (i.e., anywhere between 17 seconds to over a minute on an APEX report to refresh vs 1 sec when the refresh isn't running, (which is why I run it at 1.45am.)
However, I've noticed that the Application has been running very slowly for the last few days during the day time. When I investigated further, I see that the SQL Query to refresh the MV is still running continuously.
for reference the SQL I see running is the following:
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "VRS"."DA_DEBTORS_ACTIVE_MV"
SELECT * FROM da_debtors_active_view
So, if It starts at 1.45am and takes 2hrs, then it should be complete at approx 3.45am. Why is this query sitting around all day killing the performance of the application? And what can I do to stop it doing this?
Thanks