We had a materialized view over two bae tables, which was set to refresh on demand, and we had an hourly job which fast refreshed the mview after a linux script inserted new records to the base tables. No update or delete of any sort happened on the base tables. For some unknown reason, this hourly refresh job was disabled for several months, and after that Oracle is refusing the fast refresh on the mview. Probably too many changes since the last fast refresh. We are trying the full refresh, but the problem is that the base query is way too slow, because the base tables have too much data: one table (r_session) has 90 686 250 records, the other one (r_step) has around 4 billion records (!), count was not finished in 30 minutes, but we have information from few months ago (3.7 billion at that time).
Base tables:
CREATE TABLE R_SESSION
(
AIF_SESSION_ID NUMBER NOT NULL,
SESSION_START DATE,
SESSION_END DATE NOT NULL,
TREE_INSTANCE_ID NUMBER,
ANI VARCHAR2(25 BYTE),
CONTACT_ID VARCHAR2(32 BYTE),
DURATION NUMBER,
STEPS NUMBER,
OUTCOME CHAR(1 BYTE),
VP_SESSION_ID VARCHAR2(50 BYTE),
DESTINATION VARCHAR2(15 BYTE),
LASTCHECKPOINTNODE NUMBER,
IMPORTED_TS DATE DEFAULT SYSDATE
);
CREATE TABLE R_STEP
(
STEP NUMBER,
TREE_INSTANCE_ID NUMBER,
NODE_ID NUMBER,
AIF_SESSION_ID NUMBER,
TIMESTAMP TIMESTAMP (3),
AUTHLEVEL CHAR(1 BYTE),
DURATION NUMBER,
LANGAUGE_ID NUMBER,
BAD_TRIES NUMBER(*,0),
INPUT VARCHAR2(15 BYTE),
FIRST_VISIT NUMBER(1,0),
SUBTREE_INSTANCE_ID NUMBER,
AUX_INFO VARCHAR2(200 BYTE),
IMPORTED_TS DATE DEFAULT SYSDATE
);
AIF_SESSION_ID is a common key in the tables, but there is no foreign key to speed up the import process from the linux scripts, and also to avoid any problems if some import step fails for some reason (e.g. too long value for a column).
Here is the query for the mview:
SELECT
trunc(s2.session_end, 'fmhh24') AS day_and_hour,
floor(s1.tree_instance_id / 10000) AS tree_id,
s1.node_id,
COUNT(1) AS used,
SUM(s1.first_visit) AS used_in_calls,
MIN(s1.duration) AS min_duration,
MAX(s1.duration) AS max_duration,
SUM(s1.duration) AS total_duration
FROM
r_step s1,
r_session s2
WHERE
s1.aif_session_id = s2.aif_session_id
GROUP BY
trunc (s2.session_end, 'fmhh24'),
floor(s1.tree_instance_id / 10000),
s1.node_id;
Both tables are partitioned daily. We created the following indexes on them for the above query (they weren't needed while the hourly job was running):
-
r_session: IDX_SESS_END_AIFSID
- aif_session_id
- trunc(session_end, 'fmhh24')
-
r_step: IDX_AIFSID_DUR_TIID_NID_FV
- aif_session_id
- duration
- floor(tree_instance_id / 10000)
- node_id
- first_visit
Both indexes are non-unique, and partitioned to same tablespaces as the tables. Oracle generates the following execution plan:
----------------------------------------------------------------------------------------------------------------------------------------------------
| OPERATION | OBJECT_NAME | CARDINALITY | COST | PARTITION_START | PARTITION_STOP |
------------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| SELECT STATEMENT | | 4352264137 | 91054346 | | |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| HASH (GROUP BY) | | 4352264137 | 91054346 | | |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| HASH JOIN | | 4352264137 | 16104026 | | |
| Access Predicates | | | | | |
| S1.AIF_SESSION_ID=S2.AIF_SESSION_ID | | | | | |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| PARTITION RANGE (ALL) | | 86330332 | 118572 | | |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| INDEX (FAST FULL SCAN) | IDX_SESS_END_AIFSID | 86330332 | 118572 | 1 | 1103 |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| PARTITION RANGE (ALL) | | 4410755963 | 7022348 | | |
|-----------------------------------------------|------------------------------|---------------|------------|-------------------|------------------|
| INDEX (FAST FULL SCAN) | IDX_AIFSID_DUR_TIID_NID_FV | 4410755963 | 7022348 | 1 | 1103 |
----------------------------------------------------------------------------------------------------------------------------------------------------
We think that the reason for the fast full index scan is that all of the records are required from both tables for the aggregation. Is this a correct assumption? Is there any chance to run this query as it is now? Alternatively, can you suggest another approach to solve this problem? E.g. to process the query in several parts, maybe with some helper tables or CTE, so that we can get the mview up to date again.
Best Answer
The plan
It looks like the CBO wants to make a 90 M X 4 B matrix, THEN do the filtering.
You need to convince the CBO to "don't do that".
Ideas
I suggest you research the following ides. From there, you can try (combinations) of the various suggestions
As I understand FAST REFRESH Materilaized Views with COUNT(*), SUM() need Materialized View Logs. There may be other limitations.
You can create a constraint such that
RELY) when it builds a planDIABLED)NOVALIDATE)DBMS_PARALLEL_EXECUTE(DPE) can be used to break down large DML operations into smaller ones. I've only used it to limit UNDO/TEMPSPACE usage. Beyond that, I prefer to use Parallel DML statements.The combination of DPE and
/*+ parallel */queries can take a lot of resources (more than you actually have)