View is taking more time and consuming 100% cpu utilization

Occasional Visitor

We are using views on tables having huge records to display some information in our application but it is consuming 100% CPU utilization and taking more time to process the records.

 

SELECT COUNT(*) FROM FRA_ITEM_FIELD_DEF--44

SELECT COUNT(*) FROM FRA_STAT_ITEM--59635520(59M)

SELECT COUNT(*) FROM FRA_STAT_FIELD--536719680(536M)

 

Available Indexes:

FRA_STAT_ITEM:

CREATE UNIQUE INDEX FRA_STITM_U1
ON FRA_STAT_ITEM (ID_ORIGINAL,ITEM_TYPE,SEQUENCE_NO)
GO

CREATE INDEX FRA_STITM_U2 ON FRA_STAT_ITEM (REFERENCE)
GO

FRA_STAT_FIELD:

CREATE INDEX FRA_STFLD_OPT1 ON FRA_STAT_FIELD
(ID_STAT_ITEM,ID_FIELD_DEF,TEXT_VALUE,DATETIME_VALUE,NUMBER_VALUE,DECIMAL_VALUE)
GO

FRA_STAT_FIELD:

CREATE INDEX FRA_STFLD_OPT2 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,DATETIME_VALUE)
GO

CREATE INDEX FRA_STFLD_OPT3 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,TEXT_VALUE)
GO

 

Reported View:

CREATE OR ALTER VIEW FRAUD_ALERT_STAT_HIT_VIEW
AS
SELECT DISTINCT STAT.ID AS ID, STAT.COMPANY AS COMPANY,
STAT.TEXT_VALUE AS ACCOUNT, STAT.ITEM_TYPE AS FRAUD_ITEM_TYPE,
STAT.DATETIME_VALUE AS VALUE_DATE, STAT.CREATION_TIME AS CREATION_TIME,
STAT.PAYLOAD AS PAYLOAD, STAT.REFERENCE AS REFERENCE,
STAT.SEQUENCE_NO AS SEQUENCE_NO, STAT.ID_MANDATOR AS ID_MANDATOR,
STAT.ALERTED AS ALERTED, STAT.STATUS AS STATUS
FROM (
SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE,
FSI2.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME,
FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO,
FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS
FROM FRA_STAT_ITEM FSI
CROSS JOIN FRA_STAT_FIELD FSI1
CROSS JOIN FRA_STAT_FIELD FSI2
WHERE FSI1.ID_STAT_ITEM=FSI.ID
AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate')
AND FSI2.ID_STAT_ITEM=FSI.ID
AND FSI2.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='debitAccount')
UNION
SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE,
FSI3.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME,
FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO,
FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS
FROM FRA_STAT_ITEM FSI
CROSS JOIN FRA_STAT_FIELD FSI1
CROSS JOIN FRA_STAT_FIELD FSI3
WHERE FSI1.ID_STAT_ITEM=FSI.ID
AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate')
AND FSI3.ID_STAT_ITEM=FSI.ID
AND FSI3.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='creditAccount')
) STAT
GO

 

Query to fetch and display record in GUI:

WITH query AS
(select ROW_NUMBER() OVER (order by fraudstats0_.id desc) as __hibernate_row_nr__, fraudstats0_.id as id64_, fraudstats0_.version as version64_, fraudstats0_.alert_type as alert3_64_, fraudstats0_.benef_cust_id as benef4_64_, fraudstats0_.blocked as blocked64_, fraudstats0_.company as company64_, fraudstats0_.creation_time as creation7_64_, fraudstats0_.id_mandator as id8_64_, fraudstats0_.ordering_cust_id as ordering9_64_, fraudstats0_.id_original as id10_64_, fraudstats0_.reference as reference64_, fraudstats0_.sequence_no as sequence12_64_, fraudstats0_.item_type as item13_64_, fraudstats0_.alerted as alerted64_, fraudstats0_.last_evaluation as last15_64_, fraudstats0_.last_eval_time as last16_64_, fraudstats0_.last_eval_user as last17_64_, fraudstats0_.id_live_item as id18_64_, fraudstats0_.payload as payload64_, fraudstats0_.status as status64_
from fra_stat_item fraudstats0_
where fraudstats0_.id in (select fraudstats1_.id from fraud_alert_stat_hit_view fraudstats1_
where fraudstats1_.fraud_item_type='profile.batch.trn.alert' and fraudstats1_.value_date>=@P0 and (fraudstats1_.account in (@P1 , @P2 , @P3) or fraudstats1_.account in (@P4 , @P5 , @P6))) ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN @P7 AND @P8

 

At present, Archiving/Purging the table is not possible by structure. 

 

Could anyone share some suggestion on fine tuning the view? 

 

0 Replies