Re: Another V$ question
Date: Wed, 27 May 2009 16:23:59 -0700 (PDT)
Message-ID: <4b03c053-fbe3-4cbc-adbf-e449173b5c0e_at_w9g2000pro.googlegroups.com>
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 (see http://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_wilshireReceived on Wed May 27 2009 - 18:23:59 CDT