Re: Another V$ question
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 29 May 2009 07:43:18 -0700 (PDT)
Message-ID: <580b23cf-516b-4f3c-b724-1dfab2640f9c_at_r34g2000vba.googlegroups.com>
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 -
Date: Fri, 29 May 2009 07:43:18 -0700 (PDT)
Message-ID: <580b23cf-516b-4f3c-b724-1dfab2640f9c_at_r34g2000vba.googlegroups.com>
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 -- Received on Fri May 29 2009 - 09:43:18 CDT