Re: Another V$ question
Date: Thu, 28 May 2009 06:31:41 -0700 (PDT)
Message-ID: <064d00bb-f8d7-4d84-b389-8efe00e9ebf7_at_t10g2000vbg.googlegroups.com>
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 -- Received on Thu May 28 2009 - 08:31:41 CDT