Re: serial#, last_call_et for long running queries

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_at_gmail.com> 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_at_sc.com> 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_at_gmail.com]
>> *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_at_sc.com> 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_at_gmail.com]
>> *Sent:* Wednesday, September 17, 2014 11:15 AM
>> *To:* Chitale, Hemant K
>> *Cc:* ax.mount_at_gmail.com; 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_at_sc.com> 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_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *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

--
http://www.freelists.org/webpage/oracle-l