David, See my response here : https://community.oracle.com/thread/1113767 and this this site by Gints Plivna: http://www.gplivna.eu/papers/v$session_longops.htm#_Toc155540263 Also see my posts : http://hemantoracledba.blogspot.sg/2009/01/when-not-to-use-vsessionlongops.html and http://hemantoracledba.blogspot.sg/2010/12/using-vrsessionlongops.html On Wed, Sep 17, 2014 at 11:41 AM, David Barbour <david.barbour1@xxxxxxxxx> wrote: > Okay, thanks. I'll check tomorrow and let you know what I find out. > > On Tue, Sep 16, 2014 at 10:37 PM, Chitale, Hemant K < > Hemant-K.Chitale@xxxxxx> wrote: > >> A simple select * from one_table would appear in v$session_longops. >> >> >> >> Joins that do nested loop and full table scans to fetch data do not seem >> to appear in v$session_longops if each loop is short-running. >> >> >> >> Hemant K Chitale >> >> >> >> >> >> *From:* David Barbour [mailto:david.barbour1@xxxxxxxxx] >> *Sent:* Wednesday, September 17, 2014 11:31 AM >> *To:* Chitale, Hemant K >> *Cc:* ORACLE-L >> >> *Subject:* Re: serial#, last_call_et for long running queries >> >> >> >> Hmmmm ..... that was why I suggested longops. I need to check that out. >> What do you mean by "a query that is 'pulling' data to the client"? If I >> had the world's biggest EMP table and I ran a query SELECT * FROM EMP ORDER >> BY LAST_NAME from either an application or SQL*Plus and it took over 6 >> seconds it's not going to show up? >> >> >> >> >> >> On Tue, Sep 16, 2014 at 10:19 PM, Chitale, Hemant K < >> Hemant-K.Chitale@xxxxxx> wrote: >> >> Not all SQL operations appear in longops. I don’t rely on it. For >> example, a query that is pulling data to the client won’t appear in >> v$session_longops as the op gets reset at every fetch (as would >> last_call_et get reset to 0). >> >> >> >> Hemant K Chitale >> >> >> >> >> >> *From:* David Barbour [mailto:david.barbour1@xxxxxxxxx] >> *Sent:* Wednesday, September 17, 2014 11:15 AM >> *To:* Chitale, Hemant K >> *Cc:* ax.mount@xxxxxxxxx; ORACLE-L >> *Subject:* Re: serial#, last_call_et for long running queries >> >> >> >> What about v$session_longops or gv$session_longops? >> >> >> >> On Tue, Sep 16, 2014 at 9:57 PM, Chitale, Hemant K < >> Hemant-K.Chitale@xxxxxx> wrote: >> >> SERIAL# goes together with SID as the unique identifier (within the >> instance) for a session. >> >> >> >> You need to look at SEQ# to see if the Wait Event is changing. >> >> >> >> Hemant K Chitale >> >> >> >> >> >> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: >> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *amonte >> *Sent:* Wednesday, September 17, 2014 1:51 AM >> *To:* Oracle-L Group >> *Subject:* serial#, last_call_et for long running queries >> >> >> >> Hello >> >> In order to identify long running queries I have been using last_call_et, >> if a session with a large last_call_et then it has not changed query. >> >> Is it more useful add serial#? Since serial# does not change in a long >> running query neither? >> >> Thank you >> >> Alex >> >> >> This email and any attachments are confidential and may also be >> privileged. If you are not the intended recipient, please delete all copies >> and notify the sender immediately. You may wish to refer to the >> incorporation details of Standard Chartered PLC, Standard Chartered Bank >> and their subsidiaries at >> https://www.sc.com/en/incorporation-details.html. >> >> >> >> >> This email and any attachments are confidential and may also be >> privileged. If you are not the intended recipient, please delete all copies >> and notify the sender immediately. You may wish to refer to the >> incorporation details of Standard Chartered PLC, Standard Chartered Bank >> and their subsidiaries at >> https://www.sc.com/en/incorporation-details.html. >> >> >> >> This email and any attachments are confidential and may also be >> privileged. If you are not the intended recipient, please delete all copies >> and notify the sender immediately. You may wish to refer to the >> incorporation details of Standard Chartered PLC, Standard Chartered Bank >> and their subsidiaries at >> https://www.sc.com/en/incorporation-details.html. >> > > -- Hemant K Chitale http://hemantoracledba.blogspot.com