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)
NOT IN (SELECT DISTINCT ...). Skip theDISTINCT, because you don't need any sort and distinct values.dynamic statistics used: dynamic sampling (level=2)implies there is still a table without statistics. Run thegather_table_statscommand on every table involved in the query and then investigate why the statistics are missing. Oracle has a daily autotask that gathers stats for all tables without any stats. Ask your DBA to figure out why that task is not running. Also, keep in mind that full table scans are more efficient than index scans when you are reading a large percentage of data, which seems to be true based on the relevant!=andIS NOT NULLpredicates.COLUMN != 'VALUE'usually return a large percentage of rows. Btw, the use ofuas a national character symbol is not documented in the manual. It works fine in current versions as far as I know, but to be safe, I recommend changing theuton. Also,IS NOT NULLconditions usually return a large percentage of data. (Unless you have a very sparse table.) There's no precise number, but as an example, 5% is often a "large" percentage.