query on v$sqlarea

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 2 Nov 2001 17:36:19 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA72DBBA3@lnewton.leeds.lfs.co.uk>

Ed,

try this :

SQL> desc V$SQLTEXT_WITH_NEWLINES

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 ADDRESS                                            RAW(4)
 HASH_VALUE                                         NUMBER
 COMMAND_TYPE                                       NUMBER
 PIECE                                              NUMBER
 SQL_TEXT                                           VARCHAR2(64)

select sql_text
from v$sqltext_with_newlines
order by piece
(where address matches with the address column in V$SQLAREA as currently used).

HTH regards,
Norman.

---

---

Norman Dunbar			EMail:	Norman.Dunbar_at_LFS.co.uk
Database/Unix administrator	Phone:	0113 289 6265
				Fax:	0113 289 3146
Lynx Financial Systems Ltd.	URL:	http://www.Lynx-FS.com


------------------------------------------------------------------------

---

-----Original Message-----

From: Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) [mailto:Ed_Stevens_at_nospam.noway.nohow]
Posted At: Friday, November 02, 2001 4:48 PM Posted To: server
Conversation: query on v$sqlarea
Subject: query on v$sqlarea

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.)

Received on Fri Nov 02 2001 - 11:36:19 CST