Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Cursor Performance

Re: PL/SQL Cursor Performance

From: Eric Albacete <ealbacete_at_home.net>
Date: Sat, 29 May 1999 22:51:25 GMT
Message-ID: <375116f8.29880632@news>


Options 1 and 3 are true depending on how your query is written.

As for option 1. The cursor will return all of the rows before any other processing occurs(I.E. the insert) if you are performing a group or order operation. These two operations require that all of the data be processed before the results can be returned. For example, if you add an 'ORDER BY a' to your query, oracle will not be able to return all of the rows until all rows are read and sorted.

Option 3 can be accomplished by using the FIRST_ROWS hint. The FIRST_ROWS hint instructs the optimizer to return a block of data as quick as possible. In theory, the FIRST_ROWS hint could cause the overall runtime of the query to be longer but return blocks of data to the cursor faster. It also has the potential to utilize more system resources in order to get the results back quicker. So... You can cause oracle to be reading additional blocks while the cursor loop in your PL/SQL is performing the insert on the returned rows.

My experience has been that FIRST_ROWS is a good "catch all" to insure that your query is using all of the indexes necessary. But the only way to know if your query is using all of the indexes it needs is to run it through explain plan.

Another thing to note is that the FIRST_ROWS hint requires the Cost-Based Optimizer(CBO). Therefore the tables and indexes accessed in your cursor must be analyzed.

I home this helps.

Eric Albacete
Sr. Consultant
Computer Systems Authority
www.csac.com

On Thu, 27 May 1999 19:16:32 GMT, salman_aziz_at_my-deja.com wrote:

>Hi
>I have a performance question on PL/SQL cursors. Please have a look at
>following piece of code.
>
>for crs in
>(select a, b, c
>from x_table )
>loop
> insert into y_table(a, b, c)
> values
> (crs.a, crs.b, crs.c);
>
>end loop;
>
>Questions:
>1. Does the cursor get all the rows in one go from the database?
>OR
>2. It is selects a row and inserts it and then gets the next row for
>next insertion?
>
>If there are 1 million rows in source table x_table then according to 1
>the select is performed once fetching all rows before inserting into the
>target table y_table.
>But if 2 is right then insert of each row into target table is preceded
>by a select form the database.
>Or there is third possibility that a set of rows is selected and placed
>in some kind of buffer of a set size. Once buffer is full the rows are
>inserted and then another SELECT is performed to get the next set for
>the insert.
>
>3. How is the following insert different from the one above.
> insert into y_table (a,b,c)
> select * from x_table;
>
>4. How does the indexes on the source table will be helpful in terms of
>performing the insert in both scenarios.
>
>I will appreciate your comments and suggestion on further reading
>material.
>
>Thanks
>
>Salman
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Sat May 29 1999 - 17:51:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US