Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query on v$sqlarea

Re: query on v$sqlarea

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 2 Nov 2001 18:38:55 +0100
Message-ID: <tu5n0160m8ihf8@corp.supernews.com>

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3be2ce12.64548495_at_ausnews.austin.ibm.com...
> I'm trying to assist one of our developers to identify and tune his
> heavy hitting SQL. To identify the major culprits, I ran the
> following:
>
> select b.username username,
> a.disk_reads reads,
> a.executions exec,
> a.disk_reads / decode (a.executions, 0,
> 1,a.executions) rds_exec_ratio,
> a.command_type,
> a.sql_text Statement
> from v$sqlarea a,
> dba_users b
> where a.parsing_user_id = b.user_id AND
> a.disk_reads > 50000
> order by a.disk_reads desc;
>
> I also run a variant of the SELECT, changing the WHERE to select on
> execution count instead of disk reads. The problem I'm having is that
> the column SQL_TEXT in V$SQLAREA is "only" 1000 characters,
> and a lot of these application queries are running larger than that.
> Thus, the queries are being truncated in the report. This makes it
> impossible to run an EXPLAIN on them. I know the complete text of
> the query has to be in the shared pool. Any ideas on how I can get my
> hands on it?
>
> TIA
>
> - Ed Stevens
>
>
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

v$sqltext
or
v$sqltext_with_new_lines

join by address and hash_value with v$sqlarea

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address
Received on Fri Nov 02 2001 - 11:38:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US