Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query on v$sqlarea
"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 addressReceived on Fri Nov 02 2001 - 11:38:55 CST