RE: serial#, last_call_et for long running queries

  • From: "Chitale, Hemant K" <Hemant-K.Chitale@xxxxxx>
  • To: "David Barbour" <david.barbour1@xxxxxxxxx>
  • Date: Wed, 17 Sep 2014 11:37:50 +0800

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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2014