RE: Using x$ tables in production
Date: Thu, 27 Aug 2015 13:07:42 -0400
Message-ID: <05ab01d0e0ea$e3f815c0$abe84140$_at_rsiz.com>
Clearly an excellent point. Even if you do create views on X$ within the limits of subsetting v$ view references, you would need to either check the patch or upgrade result’s possible changes to X$, then drop and (possibly) recreate any views referencing X$ changes before the upgrade of any non-sandbox database.
Packaging the queries up in PL/SQL or SQL scripts as Tanel recommended saves you from this administrative overhead. +1 on Jared’s note, too. Even though I’d call it a bug that any view cannot be dropped because of a change to an underlying object, it is still a problem you can avoid by policy.
Oracle changes or drops v$ views when underlying x$ references change. If you make one, you have to do the same. You might only be able to do that before the change. Enumerating your references in your specific code is a valid alternative, sort of like Cobol copy books for data definition.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Wednesday, August 26, 2015 9:54 PM To: tanel_at_tanelpoder.com; Mark W. Farnham Cc: rjoralist3_at_society.servebeer.com; Oracle-L Freelists Subject: RE: Using x$ tables in production
“One example from past: Jared Still once reported an ORA-600 caused by an user view on a X$ that went invalid after an upgrade (as the X$ structure had changed). This view couldn't be dropped after an upgrade (ORA-600):”
I ran into the same issue back in the 9i days after we upgraded to 10gR2. So, creating views on top of X$ is risky.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Wednesday, August 26, 2015 9:39 PM
To: Mark W. Farnham
Cc: rjoralist3_at_society.servebeer.com; Oracle-L Freelists
Subject: Re: Using x$ tables in production
Agreed with what others have said ... make sure you know what an X$ does (and what is it supposed to show you) before querying it in production. I wouldn't build it into regular applications (only performance & troubleshooting stuff when can't do without X$).
I would not create any "user" views on sys.X$ stuff either to not operate too far out at the edge of library cache dependency management world.
One example from past: Jared Still once reported an ORA-600 caused by an user view on a X$ that went invalid after an upgrade (as the X$ structure had changed). This view couldn't be dropped after an upgrade (ORA-600):
http://www.freelists.org/post/oracle-l/8174-upgrade-10g-or-11,4
I have written PL/SQL stored procedures in past for monitoring X$ for some very specific issue (like the shared pool monitor: http://blog.tanelpoder.com/files/scripts/tools/collectors/shared_pool_monitor/ ). You'd install the procedure as SYS (so it can query X$ without any magic views) and once enough diagnostic data is gathered (and problem solved) you'd drop the procedure.
Tanel.
On Tue, Aug 25, 2015 at 11:52 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
precisely. That is why I bothered to write:
If you go beyond view contents of existing v$ view you expose (or to users that currently don’t get v$ view access), then you have entered the “tread carefully and it is all your own fault” zone.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich J
Sent: Tuesday, August 25, 2015 2:26 PM
To: oracle-l_at_freelists.org
Subject: RE: Using x$ tables in production
On 2015/08/25 12:32, Mark W. Farnham wrote:
So, my advice is avoid this unless you need to for a reason, and my evidence that it does not necessarily cause harm per se is that x$ tables are referenced and exposed via some v$ views already.
Viewing V$ views is unlikely to cause the symptoms of an instance hang. X$ views are a different beast. Some, like X$KSMSP, have the ability to cause severe issues (info courtesy of Tanel's blog).
Tread very carefully.
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 27 2015 - 19:07:42 CEST