Home » RDBMS Server » Performance Tuning » Tuning Distributed Queries (oracle 11g with windows server2003 64bit)
Tuning Distributed Queries [message #443547] Tue, 16 February 2010 03:34 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #443645 is a reply to message #443559] Tue, 16 February 2010 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>So we used Db_link but we are facing performance issue and not able to utilize all server at same time.
Your reality is that current "design" ensures non-scalable & slow SQL.

Your challenge is similar to the farmer who wants to teach his goat to fly.
You & the farmer will continue to be disappointed by all results.
Re: Tuning Distributed Queries [message #443680 is a reply to message #443645] Tue, 16 February 2010 23:25 Go to previous message
Adeelusman
Messages: 3
Registered: February 2010
Junior Member
Laughing 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

Previous Topic: High enqueue waits in statspack - lock contention on Primary key index showing 98% of db wait time
Next Topic: Replace Not Exists
Goto Forum:
  


Current Time: Sun Dec 11 04:14:03 CST 2016

Total time taken to generate the page: 0.14593 seconds