Home » RDBMS Server » Performance Tuning » Access Remote Data Slow
 Access Remote Data Slow [message #624927] |
Sun, 28 September 2014 21:15  |
 |
oasisin2014
Messages: 8
Registered: September 2014
|
Junior Member |
|
|
I have a query running slow, and the query and the analyzing are as the following.
The most important part is from the remote view. The remote view slows down the speed. This remote view consist of 300 tables which uses 300 "union all" from different users.
The operation analyzing shows that the view domains the loading time(from step 20 ).
It gets 2921 rows and takes Elapsed time of more than 10 minutes.
What could do to speed up the speed of the query? Thanks for all possible help!
SELECT
2 fView.usN AS usN,
3 fView.idS as idS,
4 fView.idL as idL,
5 fView.usT as usT,
6 fView.psDT as psDT,
7 fView.exDT as exDT,
fView.exFR as exFR,
fView.exRB as exRB,
8 fView.exRE as exRE,
11 fView.cbDT as cbDT,
12 fView.ceDT as ceDT,
13 fView.cs as cs,
14 fView.dbdt as dbdt,
15 fView.dedt AS dedt,
16 fView.ds AS ds,
17 LIB.numLIB AS numLIB,
18 LIB.numLIBST as numLIBRT
FROM
(select
20 HS.usN as usN,
22 hs.idS as idS,
23 HSS.idL AS idL,
24 HSS.HS_DOH_IND as usT,
25 d.date_dt as psDT,
26 ps.exDT as exDT,
27 fml1.file_id as exFR,
28 ps.exRB as exRB,
29 ps.exRE as exRE,
30 HR.BEG_DT_TM as cbDT,
31 HR.END_DT_TM as ceDT,
32 HR.STATUS_FLG as cs,
33 HR2.END_DT_TM as dedt,
35 HR2.STATUS_FLG as ds
36 from hsysR hs
37 cross join hd d
38 left join hr HR on HS.usN = HR.usN
39 LEFT JOIN hr HR2 ON HR.HF_RUN_GROUP_ID = HR2.HF_RUN_GROUP_ID
40 INNER JOIN hss2 HSS ON HS.idS = HSS.idS
41 left join synDASH ps on ps.idL = hss.idL
42 left join fLo fl1 on hss.idL = fl1.idL
43 left join cnfVal cv1 on hss.idL = cv1.idL
45 where
46 HS.ACTIVE_IND = 1
49 GROUP BY HS.usN, HS.idS, HSS.idL) fView
LEFT JOIN
52 (SELECT
53 hs2.idS as idS,
54 D2.DATE_DT AS psDT,
55 FL2.FILE_ID AS numLIB,
56 FML2.FILE_ID as numLIBST
57 FROM hsysR hs2
58 CROSS JOIN hd D2
60 INNER JOIN hss2 HSS2 ON HS2.idS = HSS2.idS
61 left join fLo fl2 on hss2.idL = fl2.idL and
62 left join cnfVal CV2 on HSS2.idL = CV2.idL
64 where
65 HS2.ACTIVE_IND = 1
68 GROUP BY HS2.idS, D2.DATE_DT) LIB
ON
70 fView.idS = LIB.idS;
The analyzing is:
2921 rows selected.
Elapsed: 00:11:09.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3972478313
--------------------------------------------------------------------------------
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39160 | 7571K| | 11778 (3)| 00:04:24 | | |
|* 1 | HASH JOIN RIGHT OUTER | | 39160 | 7571K| | 11778 (3)| 00:04:24 | | |
| 2 | VIEW | | 2268 | 106K| | 2123 (2)| 00:00:48 | | |
| 3 | SORT GROUP BY | | 2268 | 294K| | 2123 (2)| 00:00:48 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 2268 | 294K| | 2122 (2)| 00:00:48 | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | cnfVal| 3 | 150 | | 1 (0)| 00:00:01 | | |
|* 6 | INDEX RANGE SCAN | Ind_wocv | 3 | | | 1 (0)| 00:00:01 | | |
| 7 | VIEW | | 2268 | 183K| | 2120 (2)| 00:00:48 | | |
| 8 | NESTED LOOPS OUTER | | 2268 | 205K| | 2120 (2)| 00:00:48 | | |
|* 9 | HASH JOIN OUTER | | 500 | 41500 | | 1970 (2)| 00:00:45 | | |
| 10 | VIEW | | 500 | 30500 | | 5 (20)| 00:00:01 | | |
|* 11 | FILTER | | | | | | | | |
|* 12 | HASH JOIN | | 500 | 19000 | | 5 (20)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | Ind_hsysR| 42 | 798 | | 1 (0)| 00:00:01 | | |
| 14 | MERGE JOIN CARTESIAN || 1002 | 19038 | | 3 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | Ind_hd| 12 | 96 | | 1 (0)| 00:00:01 | | |
| 16 | BUFFER SORT || 84 | 924 | | 2 (0)| 00:00:01 | | |
|* 17 | INDEX FAST FULL SCAN | Ind_wohsr| 84 | 924 | | 0 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS FULL | fLo| 10470 | 224K| | 1965 (2)| 00:00:44 | | |
|* 19 | INDEX RANGE SCAN | Ind_wflog| 5 | 50 | | 1 (0)| 00:00:01 | | |
| 20 | VIEW || 39160 | 5736K| | 9655 (3)| 00:03:36 | | |
| 21 | HASH GROUP BY || 39160 | 4665K| 6152K| 9655 (3)| 00:03:36 | | |
| 22 | VIEW | VW_DAG_0| 39160 | 4665K| | 8762 (3)| 00:03:16 | | |
| 23 | HASH GROUP BY || 39160 | 6577K| 7128K| 8762 (3)| 00:03:16 | | |
|* 24 | HASH JOIN RIGHT OUTER || 39160 | 6577K| | 7534 (4)| 00:02:49 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | cnfVal| 3 | 150 | | 1 (0)| 00:00:01 | | |
|* 26 | INDEX RANGE SCAN | Ind_wocv| 3 | | | 1 (0)| 00:00:01 | | |
| 27 | VIEW || 39160 | 4665K| | 7532 (4)| 00:02:49 | | |
|* 28 | HASH JOIN OUTER || 39160 | 8642K| | 7532 (4)| 00:02:49 | | |
|* 29 | HASH JOIN OUTER || 8634 | 1821K| | 5920 (4)| 00:02:13 | | |
| 30 | VIEW || 8331 | 1578K| | 3967 (5)| 00:01:29 | | |
|* 31 | HASH JOIN OUTER || 8331 | 1700K| | 3967 (5)| 00:01:29 | | |
| 32 | VIEW || 328 | 52480 | | 307 (1)| 00:00:07 | | |
| 33 | NESTED LOOPS OUTER || 328 | 48544 | | 307 (1)| 00:00:07 | | |
| 34 | NESTED LOOPS OUTER || 247 | 30628 | | 159 (1)| 00:00:04 | | |
|* 35 | HASH JOIN || 247 | 22724 | | 11 (10)| 00:00:01 | | |
| 36 | JOIN FILTER CREATE | :BF0000| 84 | 1848 | | 3 (0)| 00:00:01 | | |
|* 37 | TABLE ACCESS FULL | hss2| 84 | 1848 | | 3 (0)| 00:00:01 | | |
| 38 | VIEW || 1002 | 70140 | | 7 (0)| 00:00:01 | | |
|* 39 | FILTER || | | | | | | |
| 40 | JOIN FILTER USE | :BF0000| 1002 | 27054 | | 7 (0)| 00:00:01 | | |
| 41 | MERGE JOIN CARTESIAN || 1002 | 27054 | | 7 (0)| 00:00:01 | | |
|* 42 | INDEX RANGE SCAN | Ind_hd| 12 | 96 | | 1 (0)| 00:00:01 | | |
| 43 | BUFFER SORT || 84 | 1596 | | 6 (0)| 00:00:01 | | |
|* 44 | TABLE ACCESS FULL | hsysR| 84 | 1596 | | 1 (0)| 00:00:01 | | |
|* 45 | TABLE ACCESS BY INDEX ROWID| hr| 1 | 32 | | 1 (0)| 00:00:01 | | |
|* 46 | INDEX RANGE SCAN | Ind_run| 1 | | | 1 (0)| 00:00:01 | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | hr| 1 | 24 | | 1 (0)| 00:00:01 | | |
|* 48 | INDEX RANGE SCAN | Ind_run| 2 | | | 1 (0)| 00:00:01 | | |
| 49 | REMOTE | proVIEW| 12M| 561M| | 3610 (4)| 00:01:21 |
|* 50 | TABLE ACCESS FULL | fLo| 107K| 2313K| | 1952 (1)| 00:00:44 | | |
--------------------------------------------------------------------------------
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("fView"."psDT"="LIB"."psDT"(+) AND "fView"."idS"="LIB"."idS"(+))
Remote SQL Information (identified by operation id):
----------------------------------------------------
49 - SELECT "idL","exDT","exRB","exRE","pDT" FROM "proVIEW"
synDASH is a synonym
CREATE OR REPLACE SYNONYM "synDASH" FOR "proVIEW";
proVIEW is view for the same table bTab on 300 schema
CREATE OR REPLACE FORCE VIEW "proVIEW" ("idL", "exDT", "exRB", "exRE", "pDT") AS
select distinct idL, exDT, exRB, exRE, pDT from schema1.bTab
union all
select distinct idL, exDT, exRB, exRE, pDT from schema2.bTab
...........
300 union all
|
|
|
|
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #624943 is a reply to message #624930] |
Mon, 29 September 2014 03:13  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
oasisin2014 wrote on Mon, 29 September 2014 04:13The reason to build the remote view is that all the rest tables in the query are in one schema, and the view has to be in another schema.
Is there a way to re-optimize the union all in the view, then speed up? Thanks.
Union all is pretty un-optimizable - it's highly efficient so there's nothing you can do to make it better, apart from possibly using parallel processing. The fundamental problem is that it's searching through 300 tables. The only way to significantly speed it up is to get it to not search through 300 tables.
The best bet from a performance point of view (and I realize this will probably require a lot of code changes) is to combine the 300 tables into 1 table.
|
|
|
|
| Re: Access Remote Data Slow [message #624944 is a reply to message #624943] |
Mon, 29 September 2014 03:17 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
cookiemonster wrote on Mon, 29 September 2014 09:13The best bet from a performance point of view (and I realize this will probably require a lot of code changes) is to combine the 300 tables into 1 table.
Depending on call frequency, might be worth making an MV with the table outputs and using a synonym or similar for the thing jumping across the link.
It really depends, but that at least would mitigate some of the dev time.
|
|
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #624963 is a reply to message #624960] |
Mon, 29 September 2014 08:24 |
|
oasisin2014
Messages: 8
Registered: September 2014
|
Junior Member |
|
|
cookiemonster wrote on Mon, 29 September 2014 08:04Maybe. It's hard to tell since I can't actually work out what the view is in the original query, proview doesn't appear to be mentioned.
In the original query, synDASH is used as a synonym for the view proview. And this view proview is built with 300 identical base table 'bTab' from 300 users. For the convenience, the code for this view is listed below again:
CREATE OR REPLACE FORCE VIEW "proVIEW" ("idL", "exDT", "exRB", "exRE", "pDT") AS
select distinct idL, exDT, exRB, exRE, pDT from schema1.bTab
union all
select distinct idL, exDT, exRB, exRE, pDT from schema2.bTab
...........
300 union all
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #624966 is a reply to message #624956] |
Mon, 29 September 2014 08:49 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
oasisin2014 wrote on Mon, 29 September 2014 13:45But the analyzing showed that the optimizer would not pick up any query index for this view(even indexes are used for the base table). If we create indexes for the MV, maybe that would speed up it?
What analysis are we talking about here? There is after all a big difference between querying a view that unions 300 tables and querying a single table (and an mv is a table for this purpose).
Try it with an MV, then if you still have an issue try adding an index on idL.
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #624968 is a reply to message #624967] |
Mon, 29 September 2014 09:51 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So the analysis is the explain plan against the existing query?
That's pretty much irrelevant at this point. Plus the bit you've highligted has nothing to do with the remote and view and it shows index usage, so I'm really not sure what you think it proves.
Try the MV, see what happens
|
|
|
|
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #625003 is a reply to message #624977] |
Tue, 30 September 2014 03:39 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you mean the indexes on the tables the MW query is based on aren't used?
They couldn't be. The whole point of an MV is that it creates a seperate table with the results of the view query.
So any indexes on the base tables are irrelevant when querying it.
You can put indexes on the MV itself.
|
|
|
|
| Re: Access Remote Data Slow [message #625005 is a reply to message #624927] |
Tue, 30 September 2014 04:03 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your query includes many outer joins. These cripple the optimizer, because they force the join order. The query has aggregations. This also cripples the optimizer, because they force Oracle to stop what is doing and materialize the in-line view.
Often programmers throw in outer joins when they are unnecessary (why? Perhaps because they are afraid of losing rows) and aggregations when they are unnecessary (why? Perhaps because they are afraid of getting duplicates.) It is less common that I see BOTH these structures used.
Why do you have those constructs in the query? Are they really needed? If you have proper constraints, they possible aren't. Don't just say that you need those joins and aggregations. Do some work on them. You need to understand your data before you query it.
I wouldn't worry about the efficiency of UNION ALL on many tables, that is (in effect) no different from a full scan of a partitioned table.
[Updated on: Tue, 30 September 2014 04:03] Report message to a moderator |
|
|
|
| Re: Access Remote Data Slow [message #625008 is a reply to message #625005] |
Tue, 30 September 2014 04:38 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Tue, 30 September 2014 10:03This also cripples the optimizer, because they force Oracle to stop what is doing and materialize the in-line view.
Actually looking at the query the group by doesn't marry up to the select list at all, so I can only assume the query is edited.
John Watson wrote on Tue, 30 September 2014 10:03
I wouldn't worry about the efficiency of UNION ALL on many tables, that is (in effect) no different from a full scan of a partitioned table.
I'm not convinced that's true. I used to have 30 tables with identical structures and a union all view over the top, queries against the view were horribly slow. Combining the tables into 1 gave a marked increase in performance even for queries that didn't use indexes on the resulting table. Now admittedly I didn't use partitioning, cause I didn't need it, but I have to assume a partitioned table wouldn't be worse than a non-partitioned one in that regard. I'd test but I don't have an EE DB handy.
Of course non of this proves there's a problem with union-all, there isn't, it's as efficient as it can be, the problem is having related data spread across lots of tables.
|
|
|
|
|
|
| Re: Access Remote Data Slow [message #625030 is a reply to message #625008] |
Tue, 30 September 2014 08:12 |
|
oasisin2014
Messages: 8
Registered: September 2014
|
Junior Member |
|
|
Quote:
I'm not convinced that's true. I used to have 30 tables with identical structures and a union all view over the top, queries against the view were horribly slow. Combining the tables into 1 gave a marked increase in performance even for queries that didn't use indexes on the resulting table. Now admittedly I didn't use partitioning, cause I didn't need it, but I have to assume a partitioned table wouldn't be worse than a non-partitioned one in that regard. I'd test but I don't have an EE DB handy.
Of course non of this proves there's a problem with union-all, there isn't, it's as efficient as it can be, the problem is having related data spread across lots of tables.
+1, all the union all tables are in 1 MV, and it speeds up.
[Updated on: Tue, 30 September 2014 08:13] Report message to a moderator |
|
|
|
| Re: Access Remote Data Slow [message #625033 is a reply to message #625030] |
Tue, 30 September 2014 08:31 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Well Field3_ID, dt1 and dt2 aren't mentioned anywhere in the original sql so I have no idea how they're involved. If you want suggestions you'll need to supply the correct query plus the MV structure and also tell us how many rows are in the MV and how many match the where clause of your query.
|
|
|
|
| Re: Access Remote Data Slow [message #625055 is a reply to message #625033] |
Tue, 30 September 2014 13:56 |
 |
oasisinsky
Messages: 3
Registered: September 2014
|
Junior Member |
|
|
Sorry about the fields in the query. They are just 3 fields.
Another import thing is that the base table in the MV needs to have the very current data, since new data are imported to the base table very often, maybe every hour.
But now the thing is the MV could not have those updated data after creating. We need to refresh it very very often. But in this way, the loading could get delayed again.
Is there a good/efficient way to refresh? like Oracle scheduler?
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 18:32:21 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
