Home » RDBMS Server » Performance Tuning » Performance issue (10.2.0.5.0)
Performance issue [message #619769] Fri, 25 July 2014 00:25 Go to next message
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 #619773 is a reply to message #619769] Fri, 25 July 2014 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove the hint.

Re: Performance issue [message #619774 is a reply to message #619769] Fri, 25 July 2014 00:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
My car goes slow.
I have given for servicing also.
Still it goes slow.

Please read the following link on how to post performance tuning questions.
Re: Performance issue [message #619776 is a reply to message #619773] Fri, 25 July 2014 00:37 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
with out hint also its taking time
Re: Performance issue [message #619778 is a reply to message #619776] Fri, 25 July 2014 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So remove the data from the table.

Re: Performance issue [message #619779 is a reply to message #619778] Fri, 25 July 2014 00:46 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
Good idea then we don't have perf issue
Re: Performance issue [message #619784 is a reply to message #619779] Fri, 25 July 2014 01:13 Go to previous messageGo to next message
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 #619788 is a reply to message #619784] Fri, 25 July 2014 01:43 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
HI Ross,


Thanks for your suggestion.
My remote table has 43061174 records.My local tables also have huge data.

What should i do .

Thanks,
Ramana.
Re: Performance issue [message #619792 is a reply to message #619788] Fri, 25 July 2014 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


Re: Performance issue [message #619807 is a reply to message #619769] Fri, 25 July 2014 02:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ramanaapps wrote on Fri, 25 July 2014 06:25
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.
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 #619816 is a reply to message #619807] Fri, 25 July 2014 03:36 Go to previous messageGo to next message
ramanaapps
Messages: 27
Registered: April 2013
Location: HYDERABAD
Junior Member
no i n query i gave + symbol
Re: Performance issue [message #619827 is a reply to message #619816] Fri, 25 July 2014 05:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
As John suggested, look Materialized View
Re: Performance issue [message #619922 is a reply to message #619827] Sat, 26 July 2014 02:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #619940 is a reply to message #619827] Sat, 26 July 2014 07:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Lalit Kumar B wrote on Fri, 25 July 2014 16:02
As John suggested, look Materialized View


Sorry I quoted incorrectly in previous reply, it was not John, it was Ross. Sorry again.
Re: Performance issue [message #619947 is a reply to message #619940] Sat, 26 July 2014 08:44 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: A question about SPM
Next Topic: Partitioning on a VARCHAR2 column
Goto Forum:
  


Current Time: Thu Mar 28 15:40:43 CDT 2024