X Dollors !!!!!!!!

Hello,
I would like to query the x$ tables ( ex: x$kcbrbh ), I logged in as "SYS", i couldnt query the table. What could be the cause ???.

Any way to get the acceess...

Thanks in advance;
Madhu

you might not be having the sysdba privileges.

SQL>connect sys as sysdba


Sam

this could be stupid question but I am not clear. What is the difference between SYS and SYSDBA ??? How do I log in as SYSDBA ???

Could someone give me the full syntax since I tried what Sam advise on the above message and it's not working

SYS is a user whereas SYSDBA is a special role.

Ashley, have you created the password file? Which Oracle version are you using?

[Edited by Raminder on 02-28-2002 at 05:29 AM]

Quote:

---

SYS is a user whereas SYSDBA is a special role.

---

SYSDBA is not a "special role". SYSDBA is a special privilege, one among the two main administrative privileges in Oracle.

Guys,
I have connected as INTERNAL, even then I can see the following errors:

from sys.x$kcbrbh
*
ORA-00942: table or view does not exist
from sys.x$kcbrbh
*
ORA-00942: table or view does not exist

Pl suggest me what i have to do ?????

Thanks,
Madhu

This object is ** NOT ** created by default in 8i databases :

SQL*Plus: Release 3.3.4.0.0 - Production on Thu Feb 28
15:06:15 2002

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Conectado a:
Personal Oracle7 Release 7.3.4.0.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.0.0 - Production

sys@po7:SQL> desc sys.x$kcbrbh
Name Null? Type
------------------------------- -------- ----
ADDR RAW(4)
INDX NUMBER
COUNT NUMBER
............

SQL*Plus: Release 8.1.7.0.0 - Production on Qui Fev 28 15:06:35 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Conectado a:
Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

sys@po8i:SQL> desc sys.x$kcbrbh
ERROR:
ORA-04043: o objeto sys.x$kcbrbh não existe

--->> EXACTLY what you want to do here ?

I really want to query this x$kcbrbh , And I ma unable to find it, as you said it might not be created by default, So which script to run to create this in my instance.

Will it have any ide effects ???

I donno, Pl let me know.

Thanks again,
Madhu

Check your sys.v$fixed_table view - if "x$kcbrbh" table exists in the "name" col then you will find it otherwise ..............

-amar

Any GURU can look into this plz ????

You want a definitive answer eh?? ;)

These views were obsoleted in Oracle8i along with the init.ora parameters db_block_lru_statistics and db_block_lru_extended_statistics.

-amar

p.s. I have no claims to be a GURU mind you :D

Thanks AMAR ( if not GURU , may be PANDIT ),

Well I am trying to find out the Buffer Cache Hit Ratios using
X$KCBRBH table. I queried V$FIXED_TABLE too , I have seen a lot of X Dollors , but not this one.

I do not have any idea about the init.ora parameters , which you are talking about. And How they are related to my question ???

Sorry to Bug you again and your help will be very much appreciated,

Madhu

# X$KCBCBH and X$KCBRBH are accessible only to the SYS user

# They are used for tuning the buffer cache

# They are used along with init.ora parameters, db_block_lru_statistics and db_block_lru_extended_statistics. These parameters became obsolete from Oracle 8.1(as amar rightly opined)

# If you set db_block_lru_statistics to TRUE, statistics gets collected in X$KCBCBH. The number of rows in this table is equal to the number of buffers in your buffer cache. The COUNT gives number of hits caused by that buffer.

# If you set db_block_lru_extended_statistics to a number, which will be the number of buffers you want to add to your buffer cache. Then, by querying X$KCBRBH table you will know how many additional hits you are going to get.

# Setting these parameters will cause performance overhead

VJ

Thanks a bunch VJ,
I understood that these parameters are obsoleted, And in order to test this buffre cache hit performance we ( used to )rely on these two parameters .

My only wish is to get the right statistics on buffer cache, so would u suggest me any better way of getting the stats thru
scripts.

Thanks again for your wonderful explanation,
Madhu

You can get the Buffer cache hit ratio form V$SYSSTAT.

Select Name, Value from V$SYSSTAT where Name in ('db block gets', 'consistent gets', 'physical reads');

Buffer Cache Hit Ratio = (db block gets + consistent gets -physical reads)/ (db block gets + consistent gets)


All the best.