Printable View
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.
Forget OCP, that's the kind of answer you need to be able to produce to graduate from the SOHK.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.
Ah, quite so.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.
Lesson 1. Learn when to stop chasing irrelevant stuff.
Well I had made this statement based on what Jonathan Lewis had said...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
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.
I beilive TABLE Scan(Since u used Select .. From .. with out any predicates and no indexes) should have been shown in V$Session_Longops..Quote:
Originally posted by tamilselvan
Julian,
I did not get any result. Why ? I do not know.
Tamil
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..
this is exactly what i asked in my earlier post..Quote:
Originally posted by pando
that may be the question I was asking, not all processes are shown in v$session_longops
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?
What did my query return? For me it worked fine on 9.2.0.4.0Quote:
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
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".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