Hi,
We have Query which takes 43 seconds to retrieve 650 records.We are on 10.2.0.4 version.Kindly Suggest me any changes is required.
SELECT InstrumentID, MEGroupID, MessageSequence FROM TIBEX_msgseqbyinstrumentbymeid WHERE MEGroupID = 'ME1';
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1364023912
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 1550 | 56585 (2)| 00:11:20 |
| 1 | HASH GROUP BY | | 25 | 1550 | 56585 (2)| 00:11:20 |
|* 2 | HASH JOIN | | 3272 | 198K| 56584 (2)| 00:11:20 |
|* 3 | TABLE ACCESS FULL | TIBEX_INSTRUMENT | 677 | 14894 | 18 (0)| 00:00:01 |
| 4 | VIEW | | 5689 | 222K| 56565 (2)| 00:11:19 |
| 5 | UNION-ALL | | | | | |
| 6 | HASH GROUP BY | | 614 | 11052 | 4587 (2)| 00:00:56 |
| 7 | TABLE ACCESS FULL | TIBEX_QUOTE | 455K| 8008K| 4564 (1)| 00:00:55 |
| 8 | HASH GROUP BY | | 108 | 1944 | 50283 (2)| 00:10:04 |
| 9 | TABLE ACCESS FULL | TIBEX_ORDER | 4926K| 84M| 50001 (1)| 00:10:01 |
| 10 | HASH GROUP BY | | 52 | 936 | 8 (13)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | TIBEX_EXECUTION | 307 | 5526 | 7 (0)| 00:00:01 |
| 12 | HASH GROUP BY | | 1 | 40 | 3 (34)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | TIBEX_TSTRADE | 1 | 40 | 2 (0)| 00:00:01 |
| 14 | HASH GROUP BY | | 396 | 7128 | 13 (8)| 00:00:01 |
| 15 | INDEX FAST FULL SCAN| IX_BESTEXREL | 3310 | 59580 | 12 (0)| 00:00:01 |
| 16 | HASH GROUP BY | | 1125 | 20250 | 12 (9)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | TIBEX_MERESUMEPRDTRANSITION | 1981 | 35658 | 11 (0)| 00:00:01 |
| 18 | HASH GROUP BY | | 1 | 17 | 4 (25)| 00:00:01 |
| 19 | TABLE ACCESS FULL | TIBEX_EDPUPDATEREJECT | 10 | 170 | 3 (0)| 00:00:01 |
| 20 | HASH GROUP BY | | 1126 | 32654 | 822 (1)| 00:00:10 |
| 21 | NESTED LOOPS | | 8640 | 244K| 821 (1)| 00:00:10 |
| 22 | TABLE ACCESS FULL | TIBEX_INSTRUMENTADMIN | 17280 | 421K| 820 (1)| 00:00:10 |
|* 23 | INDEX UNIQUE SCAN | XPKTIBEX_CONFIGMEGROUP | 1 | 4 | 0 (0)| 00:00:01 |
| 24 | HASH GROUP BY | | 17 | 306 | 70 (3)| 00:00:01 |
| 25 | TABLE ACCESS FULL | TIBEX_BESTEXECPRICELOG | 12671 | 222K| 68 (0)| 00:00:01 |
| 26 | HASH GROUP BY | | 1 | 40 | 3 (34)| 00:00:01 |
|* 27 | TABLE ACCESS FULL | TIBEX_AUCTIONPRICE | 1 | 40 | 2 (0)| 00:00:01 |
| 28 | HASH GROUP BY | | 1126 | 19142 | 618 (1)| 00:00:08 |
|* 29 | TABLE ACCESS FULL | TIBEX_ADMINACK | 18121 | 300K| 616 (1)| 00:00:08 |
| 30 | HASH GROUP BY | | 1122 | 20196 | 142 (2)| 00:00:02 |
| 31 | INDEX FAST FULL SCAN| INSTRUMENTSTATEMSGSEQ | 23588 | 414K| 140 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("INSTRUMENTID"="B"."INSTRUMENTID")
3 - filter("B"."MEGROUPID"='ME1')
11 - filter("INSTRUMENTID" IS NOT NULL)
13 - filter("INSTRUMENTID" IS NOT NULL)
17 - filter("INSTRUMENTID" IS NOT NULL)
23 - access("ADMINUSER"="MEGROUPID")
27 - filter("INSTRUMENTID" IS NOT NULL)
29 - filter("INSTRUMENTID" IS NOT NULL)
50 rows selected.
654 rows selected.
Elapsed: 00:00:43.67
CREATE OR REPLACE VIEW TIBEX_MSGSEQBYINSTRUMENTBYMEID
(INSTRUMENTID, MESSAGESEQUENCE, MEGROUPID)
AS
SELECT a.*, b.megroupid
FROM TIBEX_MSGSEQBYINSTRUMENT a
JOIN tibex_instrument b
ON a.instrumentid=b.instrumentid
/
CREATE OR REPLACE VIEW TIBEX_MSGSEQBYINSTRUMENT
(INSTRUMENTID, MESSAGESEQUENCE)
AS
SELECT instrumentID, NVL(max(MessageSequence),0) as MessageSequence
FROM (SELECT instrumentID, max(MessageSequence) as MessageSequence
FROM tibex_quote
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_order
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_execution
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_TsTrade
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_BestExRel
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_MeResumePrdTransition
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_EDPUpdateReject
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_INSTRUMENTADMIN
WHERE instrumentID IS NOT NULL
AND adminuser IN (
SELECT megroupID
FROM tibex_configMeGroup
)
GROUP by instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_BestExecPriceLog
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_auctionPrice
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(AckMessageSequence)
FROM tibex_adminAck
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
UNION ALL
SELECT instrumentID, max(MessageSequence)
FROM tibex_InstrumentState
WHERE instrumentID IS NOT NULL
GROUP BY instrumentID
)
GROUP BY instrumentID
/
Regards
Narasimha