Home » RDBMS Server » Server Administration » Is it safe to query X$ ?
Is it safe to query X$ ? [message #308628] Tue, 25 March 2008 01:54 Go to next message
Serg.K.
Messages: 7
Registered: March 2008
Junior Member

Hi all!

As far as i know X$ tables are not documented, and there are issues when a single select against x$ table can hung the db.

Is it safe to query x$ tables (x$bh especially) regularly?

Report message to a moderator

Re: Is it safe to query X$ ? [message #308640 is a reply to message #308628] Tue, 25 March 2008 02:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is safe, as far as i know.
Unless you are going to write a really nasty sql or do updates Smile.

Report message to a moderator

Re: Is it safe to query X$ ? [message #308641 is a reply to message #308628] Tue, 25 March 2008 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your meaning of safe.
x$ tables are not tables (neither disk nor memory ones), they are call to procedures.
So accessing x$ tables may lead to unpredictible result and side effects on your instance.
Don't use them unless you perfectly know what they do.

Why to want to access x$bh regurlarly? First why not v$bh? And secondly simply why, what is the reason?

Regards
Michel

Report message to a moderator

Re: Is it safe to query X$ ? [message #308652 is a reply to message #308628] Tue, 25 March 2008 02:34 Go to previous messageGo to next message
Serg.K.
Messages: 7
Registered: March 2008
Junior Member
We are going to install new monitoring system (BMC monitoring for oracle. see bmc.com) .

I've installed it on my Test server, and i noticed that is uses x$bh . To be more accurate, it creates new view called p$bh based on x$bh.
It work O.K. on my test server... But, i have production servers of different versions and on different operation systems...

Report message to a moderator

Re: Is it safe to query X$ ? [message #308657 is a reply to message #308652] Tue, 25 March 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Accessing x$bh will take latch so this will lead others to wait.
In addition, with big cache, it is an expensive query.

Regards
Michel

Report message to a moderator

Re: Is it safe to query X$ ? [message #308664 is a reply to message #308628] Tue, 25 March 2008 02:50 Go to previous messageGo to next message
Serg.K.
Messages: 7
Registered: March 2008
Junior Member
Michel, Thanks for your answers.

Any query against x$bh will take latch ?

Is there any official documentation where i can read about this??

Report message to a moderator

Re: Is it safe to query X$ ? [message #308666 is a reply to message #308664] Tue, 25 March 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any query against x$bh will take latch ?

Yes, the header you access should not be modified during the time you access it.

Quote:
Is there any official documentation where i can read about this??

Of course not.
X$ tables don't exist for you. Your only legal access is V$.

Regards
Michel

Report message to a moderator

Re: Is it safe to query X$ ? [message #308740 is a reply to message #308628] Tue, 25 March 2008 05:48 Go to previous messageGo to next message
Serg.K.
Messages: 7
Registered: March 2008
Junior Member
Quote:
Yes, the header you access should not be modified during the time you access it.


If i understand you right :
All x$ tables are latch affected. V$ views based on X$ tables are not?




Report message to a moderator

Re: Is it safe to query X$ ? [message #308753 is a reply to message #308740] Tue, 25 March 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is why you should use them from time to time and not every 15 seconds (for instance).

Regards
Michel

Report message to a moderator

Re: Is it safe to query X$ ? [message #308899 is a reply to message #308628] Tue, 25 March 2008 21:34 Go to previous messageGo to next message
Serg.K.
Messages: 7
Registered: March 2008
Junior Member
Thanks for the explanation.

So, V$ system views are not like user created views. If I'll create new view based on any X$ table it it will be latch affected?

But, why? How does it works?

Report message to a moderator

Re: Is it safe to query X$ ? [message #308961 is a reply to message #308899] Wed, 26 March 2008 01:38 Go to previous message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because:
Quote:
x$ tables are not tables (neither disk nor memory ones), they are call to procedures.


Regards
Michel

Report message to a moderator

Previous Topic: enable auditing ,startup hang
Next Topic: One of my tablespaces is not listed in dba_free_spaces
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 18 18:21:44 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.