1

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.

  • Could one table have AIF_SESSION_ID as a PK? Exactly how are you doing the partitioning? – Michael Kutz Jan 14 at 11:31
  • AIF_SESSION_ID is PK in the r_session table. The other table has no PK. Partitioning is done based on the r_session.session_end and the r_step.timestamp columns, as a partition range: PARTITION BY RANGE ("TIMESTAMP") (PARTITION "AIFRSTEP15Q3" VALUES LESS THAN TIMESTAMP' 2015-10-01 00:00:00') TABLESPACE "AIFR15Q3". – Gábor Major Jan 14 at 12:53
  • Is there and actual PK constraint on R_SESSION? – Michael Kutz Jan 14 at 14:10
  • Yes. PK_R_SESSION, primary key on the AIF_SESSION_ID column. – Gábor Major Jan 14 at 14:25
  • Have you thought about creating a materialized view on each of the tables with the filters and function separately, then refresh this materialized view. You might need to let it refresh over a weekend regardless of how long it takes to refresh. – Gandolf989 Jan 14 at 14:32
1

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

  • the CBO will consider (RELY) when it builds a plan
  • the database doesn't enforce (DIABLED)
  • the database doesn't validate the current data at time of creation (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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.