How to find how many rows are inserted

Hi, I am inserting rows into table using " Insert into .....select * from table".
How do I know how many rows are inserted when still the insert statement is running.
I appriciate your help
Thanks

One way is:
Use PL/SQL with BULK COLLECTION option.
You come to know how many rows inserted by using dbms_output.put_line in a FORALL loop.

Tamil

dbms_output.putline() within FORALL loop will not display the output during insertion, it will show at the end of insertions, which is same as with insert into .. select *

Why does it matter that you know how many rows have been inserted while the insert is taking place?

Quote:

---

Originally posted by prince_jr
Hi, I am inserting rows into table using " Insert into .....select * from table".
How do I know how many rows are inserted when still the insert statement is running.
I appriciate your help
Thanks

---

V$Sqlarea

Quote:

---

Originally posted by abhaysk
V$Sqlarea

---

????? :confused:

The rows_processed column value in v$sqlarea is posted after insert operation completes its entire work.
This may not help to figure out how many rows are inserted so far when the sql is running.

Tamil

You might get something useful from V_$SESSION_LONGOPS

Quote:

---

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

---

Excellent...

Quote:

---

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

---

Yes, here is how:

Assume that before you start the "INSERT INTO TABLE SELECT ..." statement you check the number of rows you are supposed to insert, say 21411 (you run first select count(*) from table).

select round(SOFAR*21411/TOTALWORK)
from V$SESSION_LONGOPS
where username = 'SCOTT' and TIME_REMAINING > 0

Thanks All,
That was great.

I am not sure if v$session_longops works in all cases (in 8.1.7.4). Many times I have long process running and nothing appears in v$session_longops... AFIAK database process which takes more than 6 seconds they are considered long by Oracle and they are candidate to be seen in v$session_longops but it does not seem true in all cases... Anyone know why?

HI all,
Thanks a lot . That was great help and something new to learn/
Thanks

TO insert 4 million rows it took me 4 hours with all the indexes dropped and keys enabled. I am creating indexes. Is the time remaining out put from the above query V$SESSION_LONGOPS to create index is correct ? . Time to create one index shows more than 4 hours. How do I know how much time the index creation takes?
Thanks for all your help

4 million rows in 4 hours? Are you running on Commodore 64 or something?

Moving on ... the time remaining is pretty good, i've found, although it's subject to change due to changes in what other users are doing etc.

Yep, It took 4 hours to insert 4 million rows. Did disabled all triggers and dropped the indexes and inserted from temp table. I am using oracle 8.1.7 on Sun E 6500.

You should have minimized your amount of redo/undo generated with that insert-as-select by using direct path insert.

INSERT /*+ APPEND */ INTO .... AS SELECT * FROM ....

That should have greatly reduced the time to populate your table. Similary, for index creation you should use NOLOGGING option. That is of course if there is no explicit and very strong reason why you shouldn't use nologging operations, like if this database is part of dataguard configuration or something simmilar....

I am creating INDEX using CREATE INDEX "USER"."FF_TYP" ON "TABLE" ("TAR_ID" , "FF_TYP" ,"FF_COD" ) PCTFREE 15 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 488202240 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PYRAMID_IX012" NOLOGGING ;

I have to create 4 more indexes. Is there any way it can happen faster.
thanks

You can allways try PARALLEL option when creating indexes.

How do I use parallel option and what are the steps needed to run.

http://searchoracle.techtarget.com/t...910643,00.html

HTH
Ravinder

Now there's some advice that sucks.

Quote:

---

The point here is that "ALTER INDEX .... REBUILD" is faster than CREATE, since REBUILD uses Fast Full Scan (FFS) and CREATE uses Full Table Scan (FTS).

---

Rebuild a usable index (without specifying a statistics clause) and the old index will indeed be fast full scanned, but this guy seems to think that applies to unusable indexes, and that his proposed methodology avoids reading the table when creating the index.

What a crock.

Perhaps someone who has signed over all of their personal information to that site in exchange for an id would like to tell the author that he's an idiot.

I did use paralled option when creating index and was very fast. It created within 5 minutes.
Is there any issues if I use parallel option.
Thank you for all your help

Quote:

---

Originally posted by jmodic
????? :confused:

---

Quote:

---

Originally posted by tamilselvan
The rows_processed column value in v$sqlarea is posted after insert operation completes its entire work.
This may not help to figure out how many rows are inserted so far when the sql is running.

Tamil

---

Well, all i can say is I posted the reply in haste not even thinking what was really asked..

Quote:

---

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

---

I think i am late coming into this party :-)
Well to all who have aggreed that this is the method..

Not all Operations running long are captured in V$Session_Longops..
So for Index scan/Nested Joins any many other will not be populated in Session Longops, in which case again we are in fix...

V$Transaction -> Used_Urec will/may help in this regard to find number of rows inserted..Well it is one to one match if its a Single Row Insert..which presently is not..Present scenerio is Array Insert or rather Bulk Insert Type..for such Inserts one record is incremented in the Used_Urec Col for Every Block getting affected during insert...

So, knowing the number of blocks that will be returned by Select Query and substracting the value/2 in the Used_Urec will give an approx picture of how many more blocks needs to be inserted...

Hope i am correct this time...(Jurij/Slimdave/Tamil please yell at me if i am wrong :-)

Abhay.

=====

V$Transaction -> Used_Urec will/may help in this regard to find number of rows inserted..Well it is one to one match if its a Single Row Insert..which presently is not..Present scenerio is Array Insert or rather Bulk Insert Type..for such Inserts one record is incremented in the Used_Urec Col for Every Block getting affected during insert...

So, knowing the number of blocks that will be returned by Select Query and substracting the value/2 in the Used_Urec will give an approx picture of how many more blocks needs to be inserted...

=========

The above thoery does not hold good when I tested for "insert into table select * from table" in my system.

Tamil

Aren't you content with:

select round(SOFAR*21411/TOTALWORK)
from V$SESSION_LONGOPS
where username = 'SCOTT' and TIME_REMAINING > 0

once you verify that the original table has 21411 rows? This worked just fine for me.

Julian,

Here is my test:
1 created a table T1
2 First insert into t1 from dba_objects. Now I know how many rows are in T1.
3 insert into T1 as select * from T1. At the same time, in another session I run yr query. I did not get any result.

I repeated the test several times until I reach the number of rows in the table T1 was around 500,000. Every time I run I knew how many rows were in the table.

I did not get any result. Why ? I do not know.

Tamil

Did you analyze the table? How long did the process take? Did anything appear in V_$SESSION_LONGOPS for the process?

Table was analyzed.
I need only 40 seconds to insert 500,000 rows. The dev box has 16 CPUs and the storage array is EMC.

Tamil

that may be the question I was asking, not all processes are shown in v$session_longops

Maybe the answer is, that if it ain't identified as a long operation, you probably don't need to care about progress. If it's a four hour process then it would (probably) be a longop and then the method would work.

Tamil, just pull 15 of those cpu's out, and try it again.

I can't pull 15 cpu because the box is located in a data center 100 miles away from where I am working.

Tamil

Good job too -- i was just jokin' with ya.

Quote:

---

Originally posted by slimdave
Maybe the answer is, that if it ain't identified as a long operation, you probably don't need to care about progress. If it's a four hour process then it would (probably) be a longop and then the method would work.

---

Forget OCP, that's the kind of answer you need to be able to produce to graduate from the SOHK.

Quote:

---

Originally posted by DaPi
Forget OCP, that's the kind of answer you need to be able to produce to graduate from the SOHK.

---

Ah, quite so.

Lesson 1. Learn when to stop chasing irrelevant stuff.

Quote:

---

Originally posted by tamilselvan
=====

V$Transaction -> Used_Urec will/may help in this regard to find number of rows inserted..Well it is one to one match if its a Single Row Insert..which presently is not..Present scenerio is Array Insert or rather Bulk Insert Type..for such Inserts one record is incremented in the Used_Urec Col for Every Block getting affected during insert...

So, knowing the number of blocks that will be returned by Select Query and substracting the value/2 in the Used_Urec will give an approx picture of how many more blocks needs to be inserted...

=========

The above thoery does not hold good when I tested for "insert into table select * from table" in my system.

Tamil

---

Well I had made this statement based on what Jonathan Lewis had said...

See the link
http://metalink.oracle.com/metalink/..._id=280751.996

But When I posted recently in metalink, I got slightly diff or rather much clear answer from Jonathan Lewis himself...

http://metalink.oracle.com/metalink/...5&p_myThread=1

Now, I think we may have to do some tests..if this is correct.


So, with what i said previously will not give you exact figures..as i assumed the oracle will visit Empty Block twice to Insert data & header info..& as said by Jonathan Earlier that I undo rec will be generated for every Block that is affected during Array Insert.

Quote:

---

Originally posted by tamilselvan
Julian,

I did not get any result. Why ? I do not know.

Tamil

---

I beilive TABLE Scan(Since u used Select .. From .. with out any predicates and no indexes) should have been shown in V$Session_Longops..
I wonder if by any chance you are using 8.x? coz entries in the V$Session_Longops are not stable in this version IMHO it..

Where as in 9.x its clear..

Quote:

---

Originally posted by pando
that may be the question I was asking, not all processes are shown in v$session_longops

---

this is exactly what i asked in my earlier post..
and i belive index scan (apart from FFS) will not be listed in longops..if so than Select with any Where predicates using Index wont be listed even when its more that 6 secs or rather lemme be precise more that 10000 blocks visited by Oracle.

Or even with various Joins (execpt Hash) wont be listed.

PS Oracle Docs says long running query will be listed no matter what operation..but only with name Table Scan..but I havent yet seen things like what is said.

Thanks
Abhay.

Gee, what a looong and winding thread for such a dumb request as "How many records have been inserted so far while my insert (from select)is still taking place"!!!

1. The request itself is rediculous. What does it tell you? What's the use of it? To get the feeling of how long will it take to complete the insert? If so, simply use LONGOPS fixed view to observe the progress of work. If it's not showing in longops (for whatever reason) and you realy need an estimation of how long will it take to complete, why don't you first try inserting just a small fraction of all rows in a test run, measure the time and then extrapolate to the whole load? I'm sure the error of this prediction can't be much higher then digging some meaningfull answer from V$TRANSACTION.USED_UREC or something simmilar.

2. Converting the precentage of work done from v$longops into number of rows inserted?!?! IMHO, the ones who are doing that must be joking! Are you feeling much better if you get "234,216,318 rows out of 724,009,001 total rows have been inserted so far" instead of "32% of all rows have been inserted so far"?

3. If you realy need that information (why?) and the transactions doesn't want to show up in longops or longops are not yet supported with your release and you can't perform a test load to get a feeling of how long will it run, then you can use sequence to get an information of how far your insert is. Create a row trigger on a table your insert is going to populate. In a trigger, for every row inserted increase a dummy sequence. If you want to know how far you are, just check how far the sequence has been incremented. Not the most efficient method performance-wise, I know, but hey, if you wan't some extra information from the system, you have to pay for it somehow, don't you?

Quote:

---

Originally posted by tamilselvan
Julian,

Here is my test:
1 created a table T1
2 First insert into t1 from dba_objects. Now I know how many rows are in T1.
3 insert into T1 as select * from T1. At the same time, in another session I run yr query. I did not get any result.

I repeated the test several times until I reach the number of rows in the table T1 was around 500,000. Every time I run I knew how many rows were in the table.

I did not get any result. Why ? I do not know.

Tamil

---

What did my query return? For me it worked fine on 9.2.0.4.0

I am using 9.0.1.3

May be 9.2.0.3 or 9.2.0.4 would give correct result.

Tamil

Quote:

---

Originally posted by tamilselvan
I am using 9.0.1.3

May be 9.2.0.3 or 9.2.0.4 would give correct result.

Tamil

---

Perhaps it is also up to the version. I have the feeling after running the tests that even in 9.2.0.4.0 the information in that LONGOPS view is updated only every 5th or 6th second. I was clicking constatntly execute in iSQL*Plus and the number of rows increased but not "uniformly".