Performance issue [message #619769] |
Fri, 25 July 2014 00:25 |
|
ramanaapps
Messages: 27 Registered: April 2013 Location: HYDERABAD
|
Junior Member |
|
|
Hi Team,
I am joining a table with remote table.
I have taken /* driving_site */ hint also.
Still its taking huge time.
Can you suggest me any other options.
Thanks in advance..
Ramana.
|
|
|
|
|
|
|
|
Re: Performance issue [message #619784 is a reply to message #619779] |
Fri, 25 July 2014 01:13 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
http://www.orafaq.com/tuningguide/remote%20table.html
Not using the DRIVING_SITE hint (executes locally) would be appropriate if the remote table is small.
Using the DRIVING_SITE is appropriate if you have a small number of rows on the local table and you want to do indexed lookups on a large remote table.
If neither of these is true, and both the local and remote tables are large (or the result sets are large after having applied constant filters), then replicating the remote table locally is about your only choice. Materialized Views are probably the easiest way to achieve this.
Ross Leishman
|
|
|
|
|
Re: Performance issue [message #619807 is a reply to message #619769] |
Fri, 25 July 2014 02:25 |
John Watson
Messages: 8950 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ramanaapps wrote on Fri, 25 July 2014 06:25Hi Team,
I am joining a table with remote table.
I have taken /* driving_site */ hint also.
Still its taking huge time.
Can you suggest me any other options.
Thanks in advance..
Ramana. Just one point: in your post you do not have a hint, because you have missed out the "+" symbol. So all you have is a comment. Was it the same in your query?
|
|
|
|
|
Re: Performance issue [message #619922 is a reply to message #619827] |
Sat, 26 July 2014 02:31 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
To start solving your problem first step back and look at it.
In order to join two tables, rows from both tables must be in the same database. If each table is in a different database, then Oracle must take rows from one of the tables and move them to the other database so that rows from both tables are together. Additionally, when you are done, the answer must go to the database that initially made the request. If your query moved rows to some database other than this one then you have to move the answer set back to the originating database, which if it is big can also be costly. Let us look at some examples.
You have two choices.
1. take remote rows and bring them local, join rows to build the answer, give the answer to the app
2. take local rows and move them remote, join rows to build the answer, move the answer back to local, give the answer to the app
For distributed queries, the most common answer to maximum performance is usually: to move the least amount of data across the network as possible.
Looking at your choices, you can see easily where data needs to be moved for each choice. It is really simple math in the end. If remote data "AFTER FILTERING AND PROJECTION" is less than (local data "AFTER FILTERING AND PROJECTION" + the answer), then do #1, else do #2.
If you are at a point where you are trying to use the driving site hint, then you need to do some math first to make sure you got the right idea in mind. Normally Oracle would figure this stuff out for you. But like most things it is not perfect which may be why you are having problems with a distributed query.
Keep in mind that the variables in these equations are the sizes of the data pieces after partial processing. These sizes are not generally known before hand and so Oracle must estimate them. This is the same estimating you will be doing tomorrow when you take the math noted above and apply it to your problem. Only difference is, with a little smarts you can get an almost exact answer for each variable and thus know the right thing to do.
Good luck. Kevin
|
|
|
Re: Performance issue [message #619935 is a reply to message #619922] |
Sat, 26 July 2014 05:46 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
But Kevin, don't you think instead of moving the data across the network while processing, it would be better to replicate the data in the local DB itself, for which Oracle provides MV. The performance gain would be in magnitudes doing so compared to join two tables, one local another remote.
|
|
|
|
Re: Performance issue [message #619947 is a reply to message #619940] |
Sat, 26 July 2014 08:44 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
It is quite possible that a materialized view would be a good solution. But we try to take things in steps until we get really good at them since making a wrong turn means traveling some distance before you figure out you went the wrong way and then backtracking to where the mistake was made so we can try again.
I want the OP to have a deeper grasp of the basic problem before venturing forth into the more advanced solutions.
I would also point out that a materialized view (or a manually managed version of same (a simple copy of the table)) is not without its own drawbacks. MVIEWS come in several flavors, so the OP will need to understand the differences between all the options in order to select the right flavor for the purpose. The flavor may require additional logic in the database (hidden perhaps but still there) which has its own performance cost. A MVIEW does in a sense move the cost of queries around. Using an MVIEW lets you take the cost of some joins and aggregation and pre-do it (maybe) so that you push that workload into an earlier part of your process. This time shift of cost should not be ignored. It may also be more expensive to do since depending upon the MVIEW options you choose, the database may need to do some significant monitoring of the data to keep it current. Which means an MVIEW solution overall may be more expensive not less expensive when considering the total picture. But that could still be OK if the need is to make the reporting piece much faster and you achieved that with the MVIEW solution and are willing to accept this tradeoff in costs. Also consider that unless you do FAST REFRESH ON COMMIT, your MVIEW's data can be out-of-date so you have to be OK with some tolerance to staleness. Using ON COMMIT can be very costly in its own right for though it keeps the MVIEW data fresh, it breaks the building of the MVIEW down into transactional pieces which means you loose any benefit you might have gained by a bulk-build of the MVIEW. It also makes your OTLP transactions take even longer than one of the simpler MVIEW solutions. And then there is the problem of getting that damned things to behave with automatic query rewrite which never seems to work first time out the gate. Not saying it is buggy, only that MVIEW QUERY REWRITE is a sophisticated technology and you have to observe all the details.
So sure, you are right, Materialized Views may indeed be in th OP's future. But I don't know how skilled they are so that is not the first thing I would teach them.
Kevin
[Updated on: Sat, 26 July 2014 08:51] Report message to a moderator
|
|
|
Re: Performance issue [message #619948 is a reply to message #619947] |
Sat, 26 July 2014 08:55 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
As an unrelated comment, has anyone else noted the aggressiveness of advertising on the internet lately. Only a few hours ago I was looking up mulch options for gardening. Now, even on this website, I see adds for mulch. Geesh.
Kevin
|
|
|