RE: Using x$ tables in production
Date: Thu, 27 Aug 2015 01:54:14 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED1FE0D4E4_at_USA7109MB012.na.xerox.net>
“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.
http://blog.tanelpoder.com
On Tue, Aug 25, 2015 at 11:52 AM, Mark W. Farnham <mwf_at_rsiz.com<mailto: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> [mailto: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<mailto: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 - 03:54:14 CEST