Re: Another V$ question

From: joel garry <joel-garry_at_home.com>
Date: Fri, 29 May 2009 14:28:03 -0700 (PDT)
Message-ID: <138b4b54-1b96-448e-8ecb-2768a3cf2ace_at_g19g2000yql.googlegroups.com>



On May 29, 8:23 am, a..._at_unsu.com wrote:
> 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.

See http://dioncho.wordpress.com/2009/05/02/how-long-is-my-query-being-executed/ (not to mention, Mark's first answer in this thread).

Like I said, it's a hard problem, and there's an easy answer. Two easy answers, I note now, not having noticed the 11g thing before just now, since I'm focused on 10g. Learn something new everyday, and remember something forgotten, too (Thanks Mark!).

I rarely use the v$ views for these types of things, as inevitably not much appears to be active unless something is wrong or some non-OLTP thing is running. The EM seems more appropriate for my practical purposes these days. Wasn't that long ago I'd never expect myself to say that! If you are the curious sort, you can trace what EM is doing, or use EM to look at cursors. And when EM messes up, you can laugh at Oracle some more.

jg

--
_at_home.com is bogus.
http://pblog.bna.com/techlaw/2009/05/judge-sotomayor-is-first-nominee-with-cyberlaw-record.html
Received on Fri May 29 2009 - 16:28:03 CDT

Original text of this message