RE: Elapsed time of active running query

Hi Rumpi,

I am disabling parallel query in the session before running the "select *" and do not see the query running in parallel. The table is managed by VPD, but I am using a user that is exempted from the security policies.

Thanks

-----Original Message-----
From: Rumpi Gravenstein [mailto:rgravens_at_gmail.com] Sent: Friday, October 31, 2008 4:15 PM
To: Michael Schmitt
Cc: breitliw_at_centrexcc.com; ORACLE-L
Subject: Re: Elapsed time of active running query

is this running as a parallel query?

On 10/31/08, Michael Schmitt <mschmitt_at_uchicago.edu> wrote:
> Hi Wolfgang,
>
> That is what I expected from the documentation as well. However, that is
> not what I am seeing. The following is what I see in the database when I
> have the system user run "select * from <large_table>". The LAST_CALL_ET
> seems to be getting constantly reset and I only catch the STATUS as
> 'INACTIVE' when I know the session is still doing work. The query is still
> running when I received the following results, so I would expect the status
> to show 'ACTIVE' and the LAST_CALL_ET to increase. I randomly catch the
> LAST_CALL_ET sneak up a few seconds, but then it still drops down to 0 while
> the query is running. Once the query is actually completes, then the
> LAST_CALL_ET starts counting time as I would expect for an inactive session.
>
> Any ideas?
>
>
> select USERNAME,LAST_CALL_ET,COMMAND,STATUS,SQL_ID from v$session where
> username='SYSTEM'
>
> USERNAME LAST_CALL_ET COMMAND STATUS SQL_ID
> ------------------------------ ------------ ---------- --------
> -------------
> SYSTEM 0 3 INACTIVE
> apjk5mm7mshmx
>
> SQL> /
>
> USERNAME LAST_CALL_ET COMMAND STATUS SQL_ID
> ------------------------------ ------------ ---------- --------
> -------------
> SYSTEM 0 3 INACTIVE
> apjk5mm7mshmx
>
> SQL> /
>
> USERNAME LAST_CALL_ET COMMAND STATUS SQL_ID
> ------------------------------ ------------ ---------- --------
> -------------
> SYSTEM 0 3 INACTIVE
> apjk5mm7mshmx
>
>
>
> ________________________________
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Wolfgang Breitling
> Sent: Friday, October 31, 2008 9:46 AM
> To: Michael Schmitt
> Cc: 'ORACLE-L'
> Subject: Re: Elapsed time of active running query
>
> v$session.LAST_CALL_ET has the elapsed time of the current sql for active
> sessions and the elapsed time since it was last active for inactive
> sessions. For single sql this is what you want but if the user's session is
> executing many sql as part of the transaction then it doesn't tell you how
> long since the user kicked off the transaction.
>
> At 03:31 PM 10/30/2008, Michael Schmitt wrote:
>
>
> Hi All,
>
> This question is for a 10.2.0.3 database
>
> I am looking for help on writing a SQL statement that will tell me which
> sessions have an active running query that have run for 10 minutes or more
> from the user s perspective. For example, if a user executes select * from
> table A which holds 10 million rows, I would like this script to identify
> their session when the users stop watch is over 10 minutes (from the time
> they started their query).
>
> I was hoping last_call_et would give me this information; however it pretty
> much stays at 0 due to waits I guess (and status of inactive).
>
> I need this to be for statements while they are running.
>
> Thanks in advance
>
>
>
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> http://www.centrexcc.com<http://www.centrexcc.com/>
>

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