Home » SQL & PL/SQL » SQL & PL/SQL » performance compare -> insert select and insert select using cursor (oracle 10 unix)
performance compare -> insert select and insert select using cursor [message #445602] Tue, 02 March 2010 22:23 Go to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
Hi everyone.
I make insert into my_table_1 select <some calc> > from my_table_2
select consume a lot of resources and takes time.

1 th question-> performance will be improved if I use cursor?

2 nd question
if I use cursor and during insert my_table_2 will be
updated . my_table_1 will notice this change or it
depends where cursor was during cursor execution

Thanks
Re: performance compare -> insert select and insert select using cursor [message #445604 is a reply to message #445602] Tue, 02 March 2010 22:35 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Why don't you check it once on your own?
You have to just execute that INSERT...SELECT query once and your doubts will be solved.

regards,
Delna
Re: performance compare -> insert select and insert select using cursor [message #445605 is a reply to message #445602] Tue, 02 March 2010 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>select consume a lot of resources and takes time.

post EXPLAIN PLAN for this SQL
Re: performance compare -> insert select and insert select using cursor [message #445606 is a reply to message #445604] Tue, 02 March 2010 22:41 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
dalna.sexy
it is not easy to check out.
but I am more interesting to know.
When I create cursor ,server allocated memmory to keep all data or not.
Thanks
Re: performance compare -> insert select and insert select using cursor [message #445607 is a reply to message #445605] Tue, 02 March 2010 22:42 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
I am not ego to change sql, I need to find out difference between cursor and insert select without cursor
thanks
Re: performance compare -> insert select and insert select using cursor [message #445608 is a reply to message #445607] Tue, 02 March 2010 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I need to find out difference between cursor and insert select without cursor

Performance depends upon tables, data & indexes.

Please realize that we don't have your tables, we don't have your data, and we don't have your indexes.

Only YOU can measure the differences.
Re: performance compare -> insert select and insert select using cursor [message #445610 is a reply to message #445608] Tue, 02 March 2010 23:02 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.

About cursor. I am not sure. It is possible ( I do not know )
that cursor scaning real table, row after row. And after it finish work with particular row it reads next row from real table.


unix I am working with is overload. And I am interesting toknow what happend inside ORACLE in cursor cases.

Thanks

Re: performance compare -> insert select and insert select using cursor [message #445611 is a reply to message #445610] Tue, 02 March 2010 23:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>that cursor scaning real table, row after row.
ROW by ROW is SLOW by SLOW when compared to pure SQL.

SQL is faster than PL/SQL.

compare EXPLAIN PLAN to see for yourself.
Re: performance compare -> insert select and insert select using cursor [message #445612 is a reply to message #445611] Tue, 02 March 2010 23:13 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
Explain plan will work for cursors? I am doing insert inside cursor.

And I do not care how fast it runs. In my case I want other procedures that rauning on the same machine did not notice it.


another question. Cursor will allocate the same memmory as regular SQL?
Thanks
Re: performance compare -> insert select and insert select using cursor [message #445615 is a reply to message #445612] Tue, 02 March 2010 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: performance compare -> insert select and insert select using cursor [message #445618 is a reply to message #445615] Tue, 02 March 2010 23:23 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
Thank you
Re: performance compare -> insert select and insert select using cursor [message #445624 is a reply to message #445602] Wed, 03 March 2010 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Most of your questions are answered in Database Concepts, please read it.

Follow T. Kyte's advice:

Quote:
1. Do it in a single SQL statement if at all possible.
2. If you cannot, then do it in PL/SQL (as little PL/SQL as possible!).
3. If you cannot do it in PL/SQL, try a Java Stored Procedure (extremely rarely necessary with Oracle9i and above.)
4. If you cannot do it in Java, do it in a C external procedure. (when raw speed, or 3rd party API written in C is needed.)
5. If you cannot do it in a C external routine, you might want to seriously think


Regards
Michel
Re: performance compare -> insert select and insert select using cursor [message #445628 is a reply to message #445610] Wed, 03 March 2010 00:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
yurkoz wrote on Wed, 03 March 2010 06:02
black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.

I agree with Michel: you should read the concepts guide. Looks like you have a wrong idea on how stuff works.
Re: performance compare -> insert select and insert select using cursor [message #445650 is a reply to message #445610] Wed, 03 March 2010 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
black swan
as I know when sql run it takes all related data into the memory
from the table.
because if update happend on that data, sql did not notice it.
It bring data relly on memory data.


As has been pointed out, this is not what happens.

What actually happens is that Oracle takes a note of the state of all the tables involved in the SQL when it is executed. When the query returns rows, Oracle will internally reverse any changes committed to the tables after that point in order to return all the data to you as it was when you started the query.

Quote:
About cursor. I am not sure. It is possible ( I do not know )
that cursor scaning real table, row after row. And after it finish work with particular row it reads next row from real table.


Cursors are just pieces of SQL - the same rules apply. If your cursor is open for a long period, and there are lots of other transactions being committed, then it is possible to get an ORA-01555 error where Oracle loses the data neccessary to reconstruct the state for your query, but this is fairly rare.


Re: performance compare -> insert select and insert select using cursor [message #445717 is a reply to message #445650] Wed, 03 March 2010 09:43 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
JRowbottom Thank you for answer.
As I understand your message ,ther is no difference in execution
when we make insert -> select and insert select using cursor.

But what do you mean oracle makes note of state?
if some record has been update during sql execution , oracle
should find way to bring previous record before update happend.

Or update will not happen , before oracle save somewhere the record that should be updated by another update statement during execution


do you mean Server use UNDO tablespace?


[Updated on: Wed, 03 March 2010 09:50]

Report message to a moderator

Re: performance compare -> insert select and insert select using cursor [message #445719 is a reply to message #445717] Wed, 03 March 2010 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref602
Re: performance compare -> insert select and insert select using cursor [message #445720 is a reply to message #445717] Wed, 03 March 2010 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You should REALLY read the book I provided the link to; at least the first chapter.
You will never be able to guess what is a RDBMS behaviour you have to learn what it is and if you only rely on others to read it for you then you will always be unskilled.

Regards
Michel

[Updated on: Wed, 03 March 2010 10:27]

Report message to a moderator

Re: performance compare -> insert select and insert select using cursor [message #445723 is a reply to message #445720] Wed, 03 March 2010 10:04 Go to previous message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
Thank you Michel

Previous Topic: suitable set filter
Next Topic: Generating dates
Goto Forum:
  


Current Time: Tue Nov 12 22:49:15 CST 2024