| Tuning Distributed Queries [message #443547] |
Tue, 16 February 2010 03:34  |
Adeelusman
Messages: 3
Registered: February 2010
|
Junior Member |
|
|
Hello to all
I have new senior please help me out how to handle or what tool technique is appropriate for this senior.
My senior related to distributed quires. I want to tune this process. My working environment is windows server 2003 with 11g.
I run query form one server which involve db_link to extract data from two other databases. But I observe that query runs on first node and then 2nd node. But I want to run this process parallel. I want both servers utilize at same time but using db_link in query not solve the problem. Can oracle parallel server would be the solution or another. please help me which architecture would appropriate in this senior
my sample query is
select sum(store_code) over(partition by store_code)/sum(store_code) from PK0113_dg0@D2_9_2010634013255218206180
select sum(store_code) from
(
select * from PK0113_jt@D2_9_2010634013255218206180
inner join PK0113_dg0@D2_9_2010634013255218206180 on PK0113_dg0.dg0_id=PK0113_jt.dg0_id
where store_code=113
union all
select * from PK113S_jt@D2_12_2010634015728553161367
inner join PK113S_dg0@D2_12_2010634015728553161367 on PK113S_dg0.dg0_id=PK113S_jt.dg0_id
where store_code=102
) t
due to db_link it execution plan show remote access and its acces path is serial please help me out
|
|
|
|
| Re: Tuning Distributed Queries [message #443554 is a reply to message #443547] |
Tue, 16 February 2010 04:25  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Hi Adeelusman,
Welcome to the forum. If you don't mind could you spend some time in reading the forum guidelines on how to get quick and helpful replies.
If you don't post the pre-requisites as mentioned in the guidelines (sticky post in this forum section) we can only guess.
So my best guess will be if you could stage the data locally (which could be parallelised) and if you run the query against the staged tables locally it might help you.
Regards
Raj
[Updated on: Tue, 16 February 2010 04:26] Report message to a moderator |
|
|
|
| Re: Tuning Distributed Queries [message #443559 is a reply to message #443554] |
Tue, 16 February 2010 04:52 |
Adeelusman
Messages: 3
Registered: February 2010
|
Junior Member |
|
|
well i'm sorry i really did not read pre-requisites. i will read it now. i have first time posted question here. actually we have divided our data region wise on different servers. Our application deal with Data ware housing. currently we have only one star on single server now we are working on multiple star on multiple server. Now any Adhoc Query can access any server data. So we used Db_link but we are facing performance issue and not able to utilize all server at same time. i have posted my sample query will u guide me what is best way to do this i have read many article but my problem still there, following link explain the concept which i'm talking about above
|
|
|
|
|
|
| Re: Tuning Distributed Queries [message #443680 is a reply to message #443645] |
Tue, 16 February 2010 23:25 |
Adeelusman
Messages: 3
Registered: February 2010
|
Junior Member |
|
|
 well i did more RND on this topic and came to know Union all cant not be run parallelize . Oracle Process Union serially.But i'm surprised either my problem is unique or my approach is wrong. My application deal with DSS system. So AdhoC queries send to database. if user want a report which involve both region data (resided on two server) how i create a dynamic query? Union is last solution,i believe might be wrong. i request you all experts here help me out and suggest solution. i'm newbie in oracle. although i'm OCP but i have only one year experience. please correct my approach
looking for reply
[Updated on: Tue, 16 February 2010 23:27] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
