Below is the select query which takes 1515 seconds to finish. Can some one help me improve the query performance by looking into the execution plan provided below.
As mentioned in the execution plan, I created index on event_type column in the USAGETYPE table. But execution plan still showing TABLE FULL SCAN. Wondering why?.
Also what is the BUFFER SORT in the execution plan? Is it something to be avoided?.
Note: We have Oracle 19c version.
SELECT boi.documentid,
boi.device_id ,
bie.end_t ,
bie.vfqbalance_LOCATION ,
bie.vfqbalance_CALLED_TO ,
bie.vfqbalance_DURATION ,
CEIL(bie.vfqbalance_DURATION / 60.0),
bie.vfqbalance_LOC_AREA_CODE ,
NVL(bie.gsminfoNUMBER, 0) ,
NVL(bie.evttotal_amount, 0) ,
bid.vfBucketId,
bie.vfqbalance_AMOUNT ,
0,
bie.vfqbalance_CALLING_NUMBER,
bie.crbtinfo_CHANNEL ,
bie.crbtinfo_PRODUCT_NAME ,
usageTypeId, usageName, usageType, usageCategory,
CASE ut.impact_category
WHEN u'VAS CONTENT' THEN COALESCE(bie.crbtinfo_CHANNEL, u' ') || COALESCE(u' ' || bie.crbtinfo_PRODUCT_NAME, u' ')
--WHEN u'VAS CRBT' THEN ut.usageDetail || bie.event_type
ELSE usageDetail
END as usageDetail,
usageDst,
bie.vfqbalance_RECORD_TYPE,
100,100,
bie.vfqbalance_TYPE ,
bie.vfqbalance_OPTIONS ,
boi.TYPE_STR ,
bie.vfqbalance_purchase_start_t ,
bie.vfqbalance_resource_name ,
boi.AAC_ACCESS,0,0,0
FROM (SELECT * FROM invoice_items WHERE ( ITEMTYPE = 'AR_ITEMS' OR ITEMTYPE = 'SUB_ITEMS')) boi
JOIN invoice_items_events bie ON boi.item_obj_3 = bie.item_obj_3 AND boi.ITEMTYPE = bie.ITEMTYPE AND boi.DOCUMENTID = bie.documentid
JOIN USAGETYPE ut ON bie.event_obj_2 = ut.event_obj and
( bie.telcoinfo_USAGE_CLASS = ut.USAGE_CLASS OR bie.telcoinfo_USAGE_CLASS=u'Non-roaming' or ut.usage_class IS NULL ) AND
( bie.vfqbalance_RECORD_TYPE = ut.record_type or ut.record_type IS NULL ) AND
(
--bie.event_type = ut.event_type or
ut.event_type IS NULL
-- OR (ut.event_type = u'!'
-- AND bie.event_type NOT IN (
-- SELECT distinct u.event_type
-- FROM USAGETYPE u
-- WHERE u.event_type is not null
-- AND u.event_type != u'!')
-- )
) AND
( bie.impact_category = ut.impact_category or ut.impact_category IS NULL ) AND
( bie.vfqbalance_CALLED_TO = ut.called_to or ut.called_to IS NULL
OR (ut.called_to = u'!'
And bie.vfqbalance_CALLED_TO NOT IN (
SELECT DISTINCT u.called_to
FROM USAGETYPE u
WHERE u.called_to IS NOT NULL
AND u.called_to != u'!')
)
)
LEFT JOIN VFQBALANCEBUCKET bid on bid.vfBucketId = bie.vfqbalance_VFQ_BUCKET_ID
Where bie.vfqbalance_RECORD_TYPE is not null or ut.usageType in (u'vas',u'video')
AND CASE WHEN ut.usageType = u'voice' AND bie.vfqbalance_DURATION = u'0' AND bie.evttotal_amount = 0 THEN 0 ELSE 1 END = 1;
Below is the execution plan for the select query
Plan hash value: 194770266
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5809 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 5809 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 5787 | 5 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 2068 | 5 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| INVOICE_ITEMS | 1 | 1313 | 0 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | INVOICE_ITEMS_ITEMTYPE | 1 | | 0 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 390 | 287K| 5 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | USAGETYPE | 390 | 287K| 5 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | INVOICE_ITEMS_EVENTS | 1 | 3719 | 0 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | INVOICE_ITEMS_EVENTS_DOCID | 1 | | 0 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | VFQBALANCEBUCKET | 1 | 22 | 0 (0)| 00:00:01 |
|* 13 | INDEX FAST FULL SCAN | USAGETYPE_CALLED_TO | 342 | 6156 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("UT"."CALLED_TO" IS NULL OR "BIE"."VFQBALANCE_CALLED_TO"="UT"."CALLED_TO" OR
"UT"."CALLED_TO"=U'!' AND NOT EXISTS (SELECT 0 FROM "USAGETYPE" "U" WHERE "U"."CALLED_TO" IS NOT NULL AND
"U"."CALLED_TO"<>U'!' AND LNNVL("U"."CALLED_TO"<>:B1)))
7 - access("ITEMTYPE"='AR_ITEMS' OR "ITEMTYPE"='SUB_ITEMS')
9 - filter("UT"."EVENT_TYPE" IS NULL)
10 - filter("BIE"."EVENT_OBJ_2"="UT"."EVENT_OBJ" AND "INVOICE_ITEMS"."ITEM_OBJ_3"="BIE"."ITEM_OBJ_3" AND
"INVOICE_ITEMS"."ITEMTYPE"="BIE"."ITEMTYPE" AND ("BIE"."VFQBALANCE_RECORD_TYPE"="UT"."RECORD_TYPE" OR
"UT"."RECORD_TYPE" IS NULL) AND ("BIE"."IMPACT_CATEGORY"="UT"."IMPACT_CATEGORY" OR "UT"."IMPACT_CATEGORY" IS
NULL) AND ("BIE"."VFQBALANCE_RECORD_TYPE" IS NOT NULL OR ("UT"."USAGETYPE"=U'vas' OR
"UT"."USAGETYPE"=U'video') AND CASE WHEN ("UT"."USAGETYPE"=U'voice' AND "BIE"."VFQBALANCE_DURATION"=U'0' AND
"BIE"."EVTTOTAL_AMOUNT"=0) THEN 0 ELSE 1 END =1) AND ("BIE"."ITEMTYPE"='AR_ITEMS' OR
"BIE"."ITEMTYPE"='SUB_ITEMS') AND ("BIE"."TELCOINFO_USAGE_CLASS"="UT"."USAGE_CLASS" OR
"BIE"."TELCOINFO_USAGE_CLASS"=U'Non-roaming' OR "UT"."USAGE_CLASS" IS NULL))
11 - access("INVOICE_ITEMS"."DOCUMENTID"="BIE"."DOCUMENTID")
12 - access("BID"."VFBUCKETID"(+)="BIE"."VFQBALANCE_VFQ_BUCKET_ID")
13 - filter("U"."CALLED_TO" IS NOT NULL AND "U"."CALLED_TO"<>U'!' AND LNNVL("U"."CALLED_TO"<>:B1))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)