Oracle 12c – Optimizing Query Performance for 500 Million Records

oracleoracle-12cquery-performanceview

I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million records.

view:-

CREATE VIEW VZ.SERVICE_UTILIZATION
AS (
select T0base.SNO as SNO,
    T0base.SERVICE_CALL_DATE as SERVICE_CALL_DATE,
    T1.LOOKUP_VALUE as ORIG_SYS,
    T2.LOOKUP_VALUE as DEST_SYS,
    T0base.MSG_ID as MSG_ID,
    T7.LOOKUP_VALUE as SERVICE_NAMESPACE,
    T4.LOOKUP_VALUE as OPERATION_ACCESSED,
    T0base.RESPONSE_TIME as SERVICE_PROVIDER_TIME,
    T5.LOOKUP_VALUE as SERVICE_END_POINT,
    T0base.SLA_TIME as SLA_TIME,
    T0base.SUCCESS as SUCCESS,
    T8.LOOKUP_VALUE as PERSPECTIVE,
    T0base.CREATION_DTM as CREATION_DTM,
    T3.LOOKUP_VALUE as SERVICE_TYPE,
    T0base.PROCESS_ID as PROCESS_ID,
    T0base.ACTOR as ACTOR,
    T6.LOOKUP_VALUE as CLIENT_END_POINT,
    T0base.SERVICE_PROVIDER_TIME_TYPE as SERVICE_PROVIDER_TIME_TYPE,
    T0base.PARENT_PROCESS_ID as PARENT_PROCESS_ID,
    T0base.ORIG_SYS_NO as ORIG_SYS_NO,
    T0base.DEST_SYS_NO as DEST_SYS_NO,
    T0base.SERVICE_TYPE_NO as SERVICE_TYPE_NO,
    T0base.OPERATION_NO as OPERATION_ACCESSED_NO,
    T0base.SERVICE_END_POINT_NO as SERVICE_END_POINT_NO,
    T0base.CLIENT_END_POINT_NO as CLIENT_END_POINT_NO,
    T0base.NAMESPACE_NO as NAMESPACE_NO,
    T0base.SERVICEBUSPROCESSID as SERVICEBUSPROCESSID,
    T0base.TRANSACTIONID as TRANSACTIONID,
    T0base.PARTNERMACHINE as PARTNERMACHINE,
    T0base.PARTNERCONNECTIVITYTIME as PARTNERCONNECTIVITYTIME,
    T0base.DATASIZE as DATASIZE
from t_vz_service_utilization T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T3
    on (T0base.SERVICE_TYPE_NO = T3.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T5
    on (T0base.SERVICE_END_POINT_NO = T5.SNO )
  left join t_vz_lookup T6
    on (T0base.CLIENT_END_POINT_NO = T6.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
  left join t_vz_lookup T8
    on (T0base.PERSPECTIVE_NO = T8.SNO )
)

Now, I'm querying for the distinct values from this view

SELECT /*+ FULL(su1) PARALLEL(a, 8) */ 
               DISTINCT su1.orig_sys AS orig_sys, 
                        su1.dest_sys AS dest_sys, 
                        su1.operation_accessed AS operation, 
                        su1.service_namespace AS namespace 
          FROM service_utilization su1;

This query operation takes forever to run. Also, I'm assuming that the indexes on the underlying tables doesn't work when querying on the views. I'm looking for other alternative to speed up my query performance when dealing with views having 500+ million records.

Note:- Materialized view is not an option for me.

Edit:-

Indexes on table t_vz_service_utilization are SNO,MSG_ID,ORIG_SYS_NO, compound index(ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO)

Indexe on table t_vz_lookup is SNO

Explain Plan:-

enter image description here

Best Answer

select /*+ full(su) no_index(su) parallel(su 8) */
  distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
from
  t_vz_service_utilization su
;

There. Now you can use the lookup table on this, something like this. 

select /*+ no_merge(T0base) leading(T0base) */
  T1.LOOKUP_VALUE as ORIG_SYS,
  T2.LOOKUP_VALUE as DEST_SYS,
  T4.LOOKUP_VALUE as OPERATION_ACCESSED,
  T7.LOOKUP_VALUE as SERVICE_NAMESPACE
from
  (select /*+ full(su) no_index(su) parallel(su 8) */
      distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
    from
      t_vz_service_utilization su) T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
;

Of course this could be far from optimal, but you gave us nothing else than the query. We do not know your indexes (which columns, b-tree/bitmap?), constraints (FK/UK/PK), table structure (width, row migration/chained rows), init parameters (star transformation enabled?), table/index statistics, not even a plan...

Related Question