6 seconds or 2 seconds
Nag, November 02, 2001 - 1:46 pm UTC
To be sure:
The conditions for a operation to appear in v$session_longops, what we require is
1.the table exceed 10,000 blocks
2.The operation should be more than 6 seconds.
3.The operation should be one of
Rman Backup and Restore
Recovery ( Crash and Media )
Full Table scans **
( is there a list of other server process which are covered by v$session_longops, can you give the link to the specific page which contains all the above mentioned information about v$session_longops.)
Wor, February 26, 2004 - 11:45 am UTC
We are suddenly experiencing extreme DB slowness.
Users are accesing the site on real time basis and running reports too.
How do I know which SQL's are taking time ( basically longer time ) or maybe indexes not used etc..
I need to know the exact SQL's which are taking longer time in sorted order.
Is there any way ?
Thanks in advance,
February 26, 2004 - 2:19 pm UTC
statspack, its all about statspack.
just run it, wait 10 minutes, run it again. you'll see what you ask for.
v$transaction and v$session_longops
Parag J Patankar, February 15, 2006 - 2:12 am UTC
I found in test database 9.2 one update is running for more than hour, I tried to see the process in v$session_longops it is not showing anything but it is showing in v$transaction
alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';
select sysdate from dual;
select addr, status, start_time from v$transaction
ADDR STATUS START_TIME
---------------- ---------------- --------------------
0700000007AD7BA8 ACTIVE 02/15/06 10:18:50
select * from v$session_longops where time_remaining > 0;
no rows selected
Will you pl tell me why it is so ?
thanks & regards
February 15, 2006 - 9:21 am UTC
because v$session_longops only reports long running "things"
consider this query:
select * from t1, t2 where t1.key = t2.key and t1.key > 0;
say there are indexes on t1.key and t2.key.
The RBO would likely generate:
INDEX SCAN ON T1 to find t1.key > 0 plus table access by index rowid
--> so we can INDEX SCAN ON T2 to find that KEY plus table access
So, there are lots of teeny tiny operations
scan t1 idx, find rowid, goto t1, get row, goto t2 index, scan it, goto t2, get row, output row, REPEAT over and over....
there is no single "long running THING". There would be no one thing that runs for 3 or more seconds - just billions of tiny things.
The CBO on the other hand, noticing that t1.key is always > 0 would likely:
TABLE ACCESS FULL T1
TABLE ACCESS FULL T2
the full scan of T1 could be a long running operation
the full scan of T2 could be a long running operation
here would be two long running opertions - v$session_longops would tell us about the progress of each full scan (assuming they took a while of course) as they happened.
So, I would presume your update query plan is a "plan of many teeny tiny steps, not a big bulk operation", hence it'll never show up in v$session_longops since it doesn't do any single thing for a really long time.
v$session_longops shows one table for scan and does not show for table when use hint
Chandan Singh, April 26, 2006 - 1:57 am UTC
I have this query and its taking very long time. Table acct contains 7 Million records and event table(33 partitions) contains 650 million records. Index I_EVENT_ACCTOBJ is on column acct_obj.
When i run this query v$session_longops to track how much time it will take it shows table scan for acct and when this scan is completed after that it does not show anything. Actually it should show table scan for event table also but its not showing anything & query is still running. When i remove the hint then table scan on event table comes up in v$session_longops.
There is index on column poid_id but there is no index on poid_id.
1) Why this is so? when i add hint in query its does not come in v$session_longops and when i remove the hint it comes up?
2) How i can improve the performance of this query? It will take up 4 hrs easily to get me the result.
SELECT /*+ index(event I_EVENT__ACCTOBJ) */
WHERE acct.poid_id = event.acct_obj
AND acct.poid_id > 1
AND acct.poid_db01 > 0
April 26, 2006 - 7:59 am UTC
lose your hint, that is the likely cause of massive performance issues.
1) when you add the hint, you are doing millions of tiny operations (nested loop join), when you leave the hint out - you are doing nice big juicy full scans and hash joins.
2) you give no information as to how many rows this produces, how many rows such that poid_id >1 and poid_db01 > 0
so, no way to say.
some help regarding row appearing for operation in v$session_longops
reader, July 29, 2008 - 8:58 am UTC
good day to you as always, if you can please throw some light on the below.
I executed a query which is updating a table having 15705 blocks, it's meant to be updating all the rows in table approx .25 million records. I was observing the data in v$session_longops at the same time, i found that after completion of table scan once one more row appeared for the same operation. I am not able to understand if the operation was completed why should the row appear again for same operation in v$session_longops. Below is the data from v$session_longops. It will be a great help if you can please shed some light on this.
148|1652|Table Scan|ABC.CUSTOMER|null|29/07/2008 9:40:26 AM|
29/07/2008 9:40:50 AM|24|2FCE6FE4|296869345|g0hct3w8v3rg1|0
148|1652|Table Scan|ABC.CUSTOMER|null|29/07/2008 9:40:50 AM|
29/07/2008 9:40:58 AM|8|2FCE6FE4|296869345|g0hct3w8v3rg1|0
I have removes some columns from the output.
Thanks a lot for your time and help on this.
July 29, 2008 - 4:02 pm UTC
what query were you using to query v$session longops
did you have a "where time_remaining > 0"
some more help!!!!
JV, June 17, 2009 - 2:33 pm UTC
we faced a weird problem today and if you can shed some light. I am working on the test case to show you what we faced but here's what we observed.
We made some changes to one of our stored procedures and executed the same from sqlplus with tracing on event 10046 level 8, while the procedure was executing I was constantly querying v$session_longops using the sid of executing session and time_remaining >0. I observed that one of our tables was full scanned thrice but in the tkprof the rowsource has this table scan once only.
We are using subquery factoring to fetch the data from this table in question once and use the data set multiple times in other queries with union all. Have you ever faced such a situation or can you please suggest some way to have the trace generated to show the table scan the number of times it was performed
I am not sure why the trace is not highlighting the full scan of table thrice but I did saw the row for full scan appearing in v$session_longops thrice with block read progressing in each output.
It will be a great help if you can give some directions on this.
June 17, 2009 - 3:29 pm UTC
insufficient data to even begin to think about commenting.
for i in 1 .. 3
for x in (select * from t)
that would do it in 11g - the row source presented is the last row source in the tkprof only.
I don't know what you are looking at, how your are interpreting the data - nothing.
v$session_longops and progress meter
A reader, April 02, 2010 - 10:49 am UTC
Suppose I have some long running jobs and I'd like to create a progress meter around these jobs so users could see where each of the jobs are. Is there any way I can use the information provided by v$session_longops for this purpose? It seems not possible since there is no way for me to know ahead of time how many operations there are going to be (hash join, sort output, etc) for each job. Can you please provide some pointers on how to create a progress meter for tracking long-running jobs?
April 05, 2010 - 1:15 pm UTC
only if you write the long running job yourself. then you know how many steps there will be (you tell us!)
but for a multi-step query, we only know the number of steps for a given piece of the query at a time. For example, if you have:
full scan t1
full scan t2
we know how many steps full scan t2 will be and report that. AND THEN we know how many for t1 and report that. But we cannot (do not) report the number of steps for t1 and t2 together - we can't really - the full scan of t2 is really a full scan and create hash lookup, possibly swapping to disk. whereas the full scan of t2 is a full scan and hash probe - setting rows off to the side if they are mapped to a segment in temp - to be come back to later, and includes probably lots of sqlnet message from client waits that the first full scan won't have and so on. they are not comparable steps in the work they perform.
A reader, April 05, 2010 - 3:03 pm UTC
So there's no way to develop a progress meter for even a simple statement like:
INSERT INTO x
FROM y, z
WHERE y.id = z.id;
April 05, 2010 - 10:26 pm UTC
not really - if the plan is
you'll get two different long running operations (probably, not assured of that, one of them or both might never appear if they are deemed short enough). They would be back to back but independent of each other.
(there is a query monitor now as part of enterprise manager performance pack - not an end user tool, a DBA tool see: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-misc.html
Real-time SQL Monitoring)
if the plan was
index range scan z_idx
table access by index rowid z
you would never see anything in longops - since there is no single long running operation - we do a split instant of full scan on Y, then a split instant of index, then a split instant of reading z by rowid, then y, then z_idx, then z, then y, then z_idx, then z and so on...
yao, April 08, 2010 - 3:14 am UTC
here is the question
CREATE OR REPLACE TRIGGER tri_logon
AFTER logOn on Database
-- if ora_login_user = 'DLGDL' then
ls_sql := ' INSERT INTO logonoff$ddl
(translate(sys_context(''userenv'', ''sessionid'') using nchar_cs),
translate(uid using nchar_cs),
translate(ora_login_user using nchar_cs),
translate(sys_context(''userenv'', ''host'') using nchar_cs),
translate(sys_context(''userenv'', ''instance'') using nchar_cs),
translate(sys_context(''userenv'', ''machine'') using nchar_cs),
translate(sys_context(''userenv'', ''terminal'') using nchar_cs),
translate(sys_context(''userenv'', ''ip_address'') using nchar_cs),
translate(sys_context(''userenv'', ''isdba'') using nchar_cs),
translate(sys_context(''userenv'', ''os_user'') using nchar_cs),
translate(sys_context(''userenv'', ''AUTHENTICATION_TYPE'') using nchar_cs),
where audsid =
translate(sys_context(''userenv'', ''sessionid'') using nchar_cs)),
where audsid =
translate(sys_context(''userenv'', ''sessionid'') using nchar_cs)),
translate(ora_sysevent using nchar_cs))';
--execute the sql
execute immediate ls_sql;
-- end if;
i want to get the "program" property of v$session but during the pl/sql code oracle show ora-00942:table or view not exists ,so tom ,how i can get the v$session.program ?
and my another question is:
how i can get IE ipaddress with browse/server style application just like using sys_context('userenv','ip_address') oracle's function? you know i don't to write the code in the applications
April 13, 2010 - 7:50 am UTC
why are you using dynamic sql???? stop it, you are just making things slower and complex, use static SQL - drop the dynamic - you must do that. And then you'll discover at COMPILE time your code doesn't work. It'll be faster and more reliable.
you would query v$session to get a column from v$session.
In order to query it in a compiled stored object with definers rights - the owner of the object needs to have direct access to the object
grant select on v_$session to triggr_owner;
that would let them get access to the object that v$session points to.
To get the ip address of the web browser, the MIDDLE TIER would have to tell us that - can your middle tier tell us that? If it cannot, you have no way of knowing in the database. The middle tier software would have to participate. You do not say what you are using there - if it is APEX, we can get it easily (owa_util.get_cgi_env), if it is something else - you'd have to refer to the documentation of whatever you are using.
yao, April 08, 2010 - 3:47 am UTC
first so sorry that i have accessed my prior question with the help http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:114412348062
; and i use the same function 'grant select on dba_fga_audit_trail to public' to access the question :
create or replace view v_dba_fga_audit_trail_dml as
order by session_id desc,timestamp desc
so answer my second question ,please
Good luck for u and tks again
April 13, 2010 - 7:52 am UTC
"U" isn't available, "U" is dead as far as I know. Look it up, it is true. http://en.wikipedia.org/wiki/U_of_Goryeo
and what does tks again refer to? I've never been to Tokushima Airport, Japan (Only Narita in Tokyo)???
Ryuzaki, June 19, 2015 - 11:10 am UTC
Do you realise this is a 15 year old post right now?
It is history of 15 years. Amazing, its like we talk through time.