Re: Another V$ question
From: <art_at_unsu.com>
Date: Fri, 29 May 2009 08:23:03 -0700 (PDT)
Message-ID: <e48ea432-5212-4af9-8b05-7afa8d6e173e_at_t10g2000vbg.googlegroups.com>
On May 29, 9:43 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 29, 9:38 am, a..._at_unsu.com wrote:
>
>
>
> > On May 28, 8:31 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > > On May 27, 7:23 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > > On May 27, 1:49 pm, a..._at_unsu.com wrote:
>
> > > > > Why is it so hard for Oracle to give proper documentation, instead of
> > > > > putting hundreds of columns into their V$ views that even they do not
> > > > > understand.
>
> > > > > I want to know 1 thing:
>
> > > > > 1 - How long a query has been running thus far. not from SQLPLUS
> > > > > using 'timing', but from a stored procedure that looks at the V$
> > > > > views.
>
> > > > > What do I have to work with? LAST_CALL_ET, SECONDS_IN_WAIT,
> > > > > WAIT_TIME, etc......
>
> > > > > So, the session is ACTIVE, how long has that session been ACTIVE? Why
> > > > > does Oracle make this so hard from a SQL Query standpoint?
>
> > > > We all laugh at Oracle, but that is mostly jealousy. They really do
> > > > have many tools that work well if you know how to use them. You
> > > > should look at ASH (if you are licensed), statspack, instrumenting
> > > > your own code (seehttp://tkyte.blogspot.com/2005/06/instrumentation.html
> > > > ), various scripts on metalink, tanel poder's site, jonathan lewis'
> > > > sites, all the ways you can use oradebug and traces, EM, many more
> > > > sites I'm forgetting about, etc.
>
> > > > It is by nature a hard problem, because Oracle is more concerned with
> > > > being able to give correct results for database queries as efficiently
> > > > as possible, than making it easy for you to answer a question that is
> > > > perhaps meaningless in relational terms. The v$ views are just ways
> > > > of looking at internal data, which does not necessarily follow ACID
> > > > rules. If something doesn't follow those rules, it is by definition
> > > > outside of the SQL Query space.
>
> > > > So I'll turn it around: show us the relational algebra that describes
> > > > how long a query takes.
>
> > > > There's an easy answer: If you have such a business requirement,
> > > > store the start time in your own table. That's what that
> > > > instrumentation stuff is all about.
>
> > > > jg
> > > > --
> > > > _at_home.com is bogus.http://www.wired.com/techbiz/it/multimedia/2008/03/gallery_one_wilshire
>
> > > a..., you really know how to whine about unimportant crap.
>
> > > If you need to know how long something runs then time it in the
> > > application.
>
> > > The Oracle trace facility will record the run time of nearly every
> > > operation performed in a session.
>
> > > When v$session.status = 'ACTIVE' then last_call_et is the run time of
> > > the current query. However, it may be followed immediately by another
> > > query without your noticing the status change to INACTIVE and back to
> > > ACTIVE. Really short (fast) queries may not show up in a query
> > > against v$session at all.
>
> > > Oracle is the most instrumented rdbms database on the market. No
> > > other product even comes close to providing access to the internal
> > > information that Oracle does. Oracle is a large, complex product so
> > > the statistics are also complex.
>
> > > But if you really want to know the time a query took, time it in the
> > > application because the only time that counts is the time it takes to
> > > submit a query and get a response in the application. Once you know
> > > that then you can do a break down of the total time to see where it is
> > > spent: query network transfer request time, db query run time, result
> > > set download transfer time, etc ...
>
> > > HTH -- Mark D Powell --
>
> > Thanks for the info all......the question I was looking to answer was
> > is there a way to tell how long the query has been executing 'so
> > far'. So if it is a long running query, and you want to know how long
> > it has been running..........- Hide quoted text -
>
> > - Show quoted text -
>
> The the v$session.status will be ACTIVE and in this case last_call_et
> is the run time for the current query.
> Also see v$session_longops which has a elapsed_seconds column.
>
> HTH -- Mark D Powell --
Date: Fri, 29 May 2009 08:23:03 -0700 (PDT)
Message-ID: <e48ea432-5212-4af9-8b05-7afa8d6e173e_at_t10g2000vbg.googlegroups.com>
On May 29, 9:43 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 29, 9:38 am, a..._at_unsu.com wrote:
>
>
>
> > On May 28, 8:31 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > > On May 27, 7:23 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > > On May 27, 1:49 pm, a..._at_unsu.com wrote:
>
> > > > > Why is it so hard for Oracle to give proper documentation, instead of
> > > > > putting hundreds of columns into their V$ views that even they do not
> > > > > understand.
>
> > > > > I want to know 1 thing:
>
> > > > > 1 - How long a query has been running thus far. not from SQLPLUS
> > > > > using 'timing', but from a stored procedure that looks at the V$
> > > > > views.
>
> > > > > What do I have to work with? LAST_CALL_ET, SECONDS_IN_WAIT,
> > > > > WAIT_TIME, etc......
>
> > > > > So, the session is ACTIVE, how long has that session been ACTIVE? Why
> > > > > does Oracle make this so hard from a SQL Query standpoint?
>
> > > > We all laugh at Oracle, but that is mostly jealousy. They really do
> > > > have many tools that work well if you know how to use them. You
> > > > should look at ASH (if you are licensed), statspack, instrumenting
> > > > your own code (seehttp://tkyte.blogspot.com/2005/06/instrumentation.html
> > > > ), various scripts on metalink, tanel poder's site, jonathan lewis'
> > > > sites, all the ways you can use oradebug and traces, EM, many more
> > > > sites I'm forgetting about, etc.
>
> > > > It is by nature a hard problem, because Oracle is more concerned with
> > > > being able to give correct results for database queries as efficiently
> > > > as possible, than making it easy for you to answer a question that is
> > > > perhaps meaningless in relational terms. The v$ views are just ways
> > > > of looking at internal data, which does not necessarily follow ACID
> > > > rules. If something doesn't follow those rules, it is by definition
> > > > outside of the SQL Query space.
>
> > > > So I'll turn it around: show us the relational algebra that describes
> > > > how long a query takes.
>
> > > > There's an easy answer: If you have such a business requirement,
> > > > store the start time in your own table. That's what that
> > > > instrumentation stuff is all about.
>
> > > > jg
> > > > --
> > > > _at_home.com is bogus.http://www.wired.com/techbiz/it/multimedia/2008/03/gallery_one_wilshire
>
> > > a..., you really know how to whine about unimportant crap.
>
> > > If you need to know how long something runs then time it in the
> > > application.
>
> > > The Oracle trace facility will record the run time of nearly every
> > > operation performed in a session.
>
> > > When v$session.status = 'ACTIVE' then last_call_et is the run time of
> > > the current query. However, it may be followed immediately by another
> > > query without your noticing the status change to INACTIVE and back to
> > > ACTIVE. Really short (fast) queries may not show up in a query
> > > against v$session at all.
>
> > > Oracle is the most instrumented rdbms database on the market. No
> > > other product even comes close to providing access to the internal
> > > information that Oracle does. Oracle is a large, complex product so
> > > the statistics are also complex.
>
> > > But if you really want to know the time a query took, time it in the
> > > application because the only time that counts is the time it takes to
> > > submit a query and get a response in the application. Once you know
> > > that then you can do a break down of the total time to see where it is
> > > spent: query network transfer request time, db query run time, result
> > > set download transfer time, etc ...
>
> > > HTH -- Mark D Powell --
>
> > Thanks for the info all......the question I was looking to answer was
> > is there a way to tell how long the query has been executing 'so
> > far'. So if it is a long running query, and you want to know how long
> > it has been running..........- Hide quoted text -
>
> > - Show quoted text -
>
> The the v$session.status will be ACTIVE and in this case last_call_et
> is the run time for the current query.
> Also see v$session_longops which has a elapsed_seconds column.
>
> HTH -- Mark D Powell --
Thanks for the info Mark. Not sure it works though. I ran a query with 3 tables in the FROM clause and no WHERE clause....a fairly long query. As soon as it started I ran the following:
select username, osuser, last_call_et from v$session where status = 'ACTIVE' and username IS NOT NULL;
Saw nothing with regards to that session......really strange.
At any rate, I'll keep hunting for an answer. Much thanks for your time. Received on Fri May 29 2009 - 10:23:03 CDT