-
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.
-
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.
-
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.
-
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.
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..
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
I am using 9.0.1.3
May be 9.2.0.3 or 9.2.0.4 would give correct result.
Tamil
-
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".
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|