OraFAQ Forum: Performance Tuning » Tuning query (insert using select)

Home » RDBMS Server » Performance Tuning » Tuning query (insert using select)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning query (insert using select) [message #271391]

Mon, 01 October 2007 08:58

etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland

Member

Hi All,

i am trying to insert some records using select query from other database where its been running for hours here is the example:

insert /+append*/ into insert_table
(
col1,
col2
)
select /+STAR*/
col1,
col2
from src_tbl1@dblink A, src_tbl2@dblink B
where A.col1=b.col2

i am using hints to get performance still in vain.

Oracle Version :9.2.2

Thanks in advance,,

Report message to a moderator

Re: Tuning query (insert using select) [message #271509 is a reply to message #271391]

Mon, 01 October 2007 15:43

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

I would load the remote tables into local temp tables first, then join them.

Also, consider creating indexes on the local temp tables before the final join phase.

Report message to a moderator

Re: Tuning query (insert using select) [message #271513 is a reply to message #271509]

Mon, 01 October 2007 16:30

OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico

Junior Member

That is not necessary, you may instruct Oracle to execute the query at the remote DB using the DRIVING_SITE hint.

Regards
Ignacio

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:41] by Moderator

Report message to a moderator

Re: Tuning query (insert using select) [message #271539 is a reply to message #271391]

Mon, 01 October 2007 23:39

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Your query is syntaxically wrong
2/ Why do you try to use a STAR hint and what do you think it means?

Regards
Michel

Report message to a moderator

Re: Tuning query (insert using select) [message #271665 is a reply to message #271539]

Tue, 02 October 2007 07:46

etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland

Member

My meaning of using STAR hint is just to give chance to the optimiser to execute the heavy joins last,, i am not that much familier with the HINT's..

Please suggest any better way of using hints..

Thanks in advance

Report message to a moderator

Re: Tuning query (insert using select) [message #271668 is a reply to message #271665]

Tue, 02 October 2007 07:57

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ STAR hint does not exist
2/ "using STAR hint is just to give chance to the optimiser to execute the heavy joins last", there is only 1 join in your query which one can be first or last but this one?
3/ "i am not that much familier with the HINT's" So why are you using them? It is far better to not use them in this case.

First read How to Identify Performance Problem and Bottleneck , especially the third part.

Regards
Michel

Report message to a moderator

Re: Tuning query (insert using select) [message #271673 is a reply to message #271391]

Tue, 02 October 2007 08:11

etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland

Member

Thanks for the quick reply just want to make clear to you that

i have total 8 tables out of this two tables has more than 2 cores of records, thats the reason i have opted hints as per the definition and the usage i have particularly selected STAR hint..

Ur's
Maruthi Narra

Report message to a moderator

Re: Tuning query (insert using select) [message #271676 is a reply to message #271673]

Tue, 02 October 2007 08:23

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So what you posted is not what you want to optimize but you want us to help you to optimize something you do not post with the help of something you posted but is not what you want to optimize but it is the sole information you want to give us and which is not what actually is.
Am I clearly understand your point?

Regards
Michel

Report message to a moderator

Re: Tuning query (insert using select) [message #271681 is a reply to message #271391]

Tue, 02 October 2007 08:37

etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland

Member

I have given example on what i am using

Here i am expecting different kind of options to tuning this kind of bottllenecks,

i have problem with only inserting the data not on selecting or while joining my select query has been working in just 1 to 2 min of time while i am using this to insert its just keep on executing for hours so i have opted this group to share with this to gurus to close this problem.

Anyways Thanks for your quick reply next time i will give much information as i can..

Report message to a moderator

Re: Tuning query (insert using select) [message #271745 is a reply to message #271391]

Tue, 02 October 2007 14:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Here i am expecting different kind of options to tuning this kind of bottllenecks,
I guess I missed it. Exactly which bottleneck is "this kind of bottllenecks"?

Essentially, you have said, "My query is slow. Tell me how to make faster."

Why do you expect a solution, when you've posted nothing of substance?

Please read & follow posting guidelines as found in the #1 STICKY post at the TOP of this forum.

Report message to a moderator

Previous Topic:	open cursors
Next Topic:	Outer Join killing Performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 20:39:14 CDT 2024