V$ views in transactions

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Tue, 12 Oct 2004 13:57:58 +0530
Message-ID: <MHEAIPLKCACENJKNJIALIENICHAA.joshic@mahindrabt.com>

Dear all,

Version 9.2.0.3
OS: Solaris 8

I have written a tiny stored procedure which does a SELECT from v$sql and v$sql_workarea and INSERTs into local tables. The reason for doing this is that STATSPACK does not take snapshot of v$sql_workarea table, and I thought this information would be useful to accurately calculate values for SORT_AREA_SIZE and HASH_AREA_SIZE.

But I remembered having been told by a DBA never to include v$ tables as part of any transaction. This makes sense as well. The question is:

Is it absolutely unwise and not recommended to create such a procedure as I have? Is there any workaround?
How does STATSPACK collect data from V$ tables, if not through INSERT..SELECT.. COMMIT like transaction?

Many thanks & regards,
Charu.

Disclaimer:

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.

Visit us at http://www.mahindrabt.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 12 2004 - 03:25:04 CDT