Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Andre.

Asked: September 12, 2000 - 2:40 pm UTC

Last updated: April 13, 2010 - 7:52 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi again Tom,

I have tried to use the v$session_longops view to track database operations, but I´ve noticed that only some kinds of them really appear.

Besides, for example, the CREATE INDEX statement is split into multiple sub-operations (sort, merge, etc.)

I am aware I have to first analyze the object to be tracked and set TIMED_STATISTICS or SQL_TRACE, so I can use this feature.

The questions are:

- What operations are really "trackable" ?

- Why does some operations are split into sub-operations and how can I know to which operation these sub-operations belong ?

Regards.

and Tom said...

In Oracle 8.0, the view V$Session_longops was introduced to allow Server processes to indicate some idea of how far they had progressed (specifically RMAN).

In Oracle8i, this list was expanded to include:

Rman Backup and Restore
Parallel Query
Recovery ( Crash and Media )
Full Table scans **
Sorting

** Only considered a 'Longop' if the table exceeds 10,000 blocks
formatted blocks.


why are some operations broken into smaller chunks? I guess because it makes sense to. In a parallel index create -- there are lots of sorts going on and then we need to merge them all back together into 1 index.

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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
Parallel Query
Recovery ( Crash and Media )
Full Table scans **
Sorting

( 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

Wor, February 26, 2004 - 11:45 am UTC

Hi Tom,
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,
Wor


Tom Kyte
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

Hi Tom,

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;

SYSDATE
--------------------
15-FEB-2006 11:24:07

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
pjp

Tom Kyte
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:

NESTED LOOPS
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:

HASH JOIN
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) */
acct.poid,event.acct_obj,event.curr
FROM acct,event
WHERE acct.poid_id = event.acct_obj
AND acct.poid_id > 1
AND acct.poid_db01 > 0

Thanks
Chandan Singh

Tom Kyte
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

Dear Tom,

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.
SID|SERIAL#|OPNAME|TARGET|TARGET_DESC|START_TIME|
LAST_UPDATE_TIME|ELAPSED_SECONDS|SQL_ADDRESS|SQL_HASH_VALUE|
SQL_ID|QCSID
#1
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
#2
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.

Regards,
your fan.
Tom Kyte
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

Dear Tom,

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.


Regards,
JV
Tom Kyte
June 17, 2009 - 3:29 pm UTC

insufficient data to even begin to think about commenting.


for i in 1 .. 3
loop
   for x in (select * from t) 
   loop
      ...
   end loop;
end loop;



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?
Tom Kyte
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:

hash join
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
SELECT *
FROM y, z
WHERE y.id = z.id;


Tom Kyte
April 05, 2010 - 10:26 pm UTC

not really - if the plan is
hash join
   full y
   full z


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

   nested loops
       full y
       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...



v$session.program

yao, April 08, 2010 - 3:14 am UTC

Hi Tom
here is the question

CREATE OR REPLACE TRIGGER tri_logon
AFTER logOn on Database
declare
ls_sql varchar2(4000);
BEGIN
-- if ora_login_user = 'DLGDL' then
ls_sql := ' INSERT INTO logonoff$ddl
(sessionid,
userid,
username,
hostname,
INSTANCE,
machine,
terminal,
ip,
ISDBA,
OS_USER,
logon_time,
logoff_time,
AUTHENTICATION_TYPE,
lastprogram,
lastmodel,
sysevent)
VALUES
(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),
sysdate,
null,
translate(sys_context(''userenv'', ''AUTHENTICATION_TYPE'') using nchar_cs),
(select program
from v$session
where audsid =
translate(sys_context(''userenv'', ''sessionid'') using nchar_cs)),
(select module
from v$session
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;
END tri_logon;

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
please ,tks
Tom Kyte
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

Hi Tom
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
select session_id,db_user,object_schema,object_name,sql_text,sql_bind,statement_type,timestamp
from dba_fga_audit_trail
order by session_id desc,timestamp desc

so answer my second question ,please
Good luck for u and tks again
Tom Kyte
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)???


Guys

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.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.