Re: Another V$ question
From: <art_at_unsu.com>
Date: Fri, 29 May 2009 06:38:37 -0700 (PDT)
Message-ID: <cde92833-14d6-4dc2-a0f6-dee72a90c854_at_z7g2000vbh.googlegroups.com>
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 --
Date: Fri, 29 May 2009 06:38:37 -0700 (PDT)
Message-ID: <cde92833-14d6-4dc2-a0f6-dee72a90c854_at_z7g2000vbh.googlegroups.com>
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.......... Received on Fri May 29 2009 - 08:38:37 CDT