Home » SQL & PL/SQL » SQL & PL/SQL » I have a table contining 20 million records.
I have a table contining 20 million records. [message #197535] Wed, 11 October 2006 12:22 Go to next message
Nirmala
Messages: 43
Registered: October 2004
Member
I have a table contining 20 million records.

create table test as
select level col1 from dual
connect by level < 20000000;


I am trying to insert into another table by selecting from this original table
using a small pl\sql block. (Actual proceedure involves more login in the pl\sql block)

declare 
cursor c_cur is 
select * from test;
l_counter number:=0;
begin
for r_cur in c_cur 
loop
insert into  test1 values (r_cur.col1);
l_counter:=l_counter+1;
end loop;
end ;


The execution of this block is taking a while and so i was monitoring what the session is doing using the following query.

 select a.sql_text
   from v$sqltext a , v$session b
  where a.address(+) = b.sql_address
    and b.sid =1903
  order by piece

From the output from this query from time to time i observe that the initial cursor(i.e select * from test)
is being executed again and again.
As per my understanding the execution flow of the above pl\sql block should be
1) Execute the cursor.
2) Open the cursor and loop through the cursor loop
3) Insert into the tabel test1
4) After the loop is complete close the cursor.
5) Exit the pl\sql block.

but what i observe when executing the pl\sql block is the following (1903 is the sid of the session in which the pl\sql blockis being executed.)


SQL>  select a.sql_text
  2     from v$sqltext a , v$session b
  3    where a.address(+) = b.sql_address
  4      and b.sid =1903
  5    order by piece;
  
SQL_TEXT
-------------------------------------------------------------

SELECT * FROM TEST

SQL> /
SQL_TEXT
-------------------------------------------------------------

INSERT INTO TEST1 VALUES (:B1)

SQL> /
SQL_TEXT
-------------------------------------------------------------

INSERT INTO TEST1 VALUES (:B1)

SQL> /

SQL_TEXT
-------------------------------------------------------------

SELECT * FROM TEST




From this it looks like this is the flow.
1) Execute the select and fetch some values
2) Insert those values
3) Execute the select again and fetch some more values and repeate this process till all the values are inserted.

My questions are
1) Is my understanding that the select query is getting executed multiple times during the execution process correct?
2) If so why? If not then why is the above query indicating that the session is doing a select query?
3) Is the above query the correct query to monitor what a particular session is doing? If not can you please provide
the queries that are normally used to monitor what a session is doing in tuning pl\sql programs.

Any help in this regard would be greately appreciated.

Thanks.
Re: I have a table contining 20 million records. [message #197538 is a reply to message #197535] Wed, 11 October 2006 12:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are waaaaay to slow to see what Oracle actually is doing. It can hit as high as thousands of statements per second. It is purely coincidental what you see there.
That being said, your way is about the slowest possible. Why don't you just insert as select?
Search AskTom for slow by slow for some more explanation.

Re: I have a table contining 20 million records. [message #197748 is a reply to message #197538] Thu, 12 October 2006 09:18 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
A problem almost exact to yours is discussed in asktom website at:

http://asktom.oracle.com/pls/ask/f?p=4950:8:14336724084828824943::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330

basically using sql instead of pl/sql, using parallel query etc is suggested...you will find out more by going through all of it
Re: I have a table contining 20 million records. [message #197883 is a reply to message #197535] Fri, 13 October 2006 01:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Is my understanding that the select query is getting executed multiple times during the execution process correct?

No, the SELECT gets executed once only. Look at your code - how could it get executed multiple times?

2) If so why? If not then why is the above query indicating that the session is doing a select query?

You are misuderstanding what you are seeing. Those instants where the 'Current Sql' shows the select represent the points where more rows were being fetched from the cursor.

3) Is the above query the correct query to monitor what a particular session is doing? If not can you please provide
the queries that are normally used to monitor what a session is doing in tuning pl\sql programs.

Your query will show what the session is doing at that point.
If you want an overview of the whole process, you could try tracing the session.
Previous Topic: lowest sal
Next Topic: ORA-01843: not a valid month -Error
Goto Forum:
  


Current Time: Fri Dec 02 14:16:52 CST 2016

Total time taken to generate the page: 0.07474 seconds