Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: v$kcbcbhx
On 11/17/06, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
> > Reason I ask is that we've got a 9.2.0.6 database (AIX) where we still got > the v_$ view, and the according public synonym for v$kcbcbhx. > > > Story's just gotten funnier... Opened an SR about this - and Oracle says > this "object" was never part of any Release, from 7.x up to 10.2 - even > the symbol kcbcbhx didn't return any match inside the Oracle code. Shrug.>
Really?
I wonder how they explain this script.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist em Rem Copyright (C) 1989 by ORACLE Corporation (UK) Ltd. Rem Rem FILE NAME: Rem stat7nap.sql Rem Rem CREATED: Rem Chris Ellis 15-Nov-89 Rem Rem NOTES. Rem SQL*PLUS command file to enter start values Rem of "snapshot" statistical information Rem Rem MODIFIED: Rem Dave Ensor 15-Mar-90 Rem - (summary) LRU and Extended LRU Stats recorded Rem - Order By placed on list of Snapshots Rem Rem Chris Ellis 23-Mar-90 Rem - Remove stats$files statistics Rem Rem Chris Ellis 16-Jun-90 Rem - Set termout off for body of command file Rem - Display only the latest snap_id and time Rem Rem Chris Ellis 21-Mar-91 Rem - Filename changed for Unix compatibility Rem Rem Dave Ensor 23-Sep-92 Rem - Updated for Oracle 7 Rem - Changed column names in V$LATCH Rem - Obselete column names removed from V$WAITSTAT & data now Rem captured unconditionally (only 10 rows per snap) Rem - Additional columns in V$ROLLSTAT Rem - Added capture of V$LIBRARYCACHE Rem - Added capture of instance number & startup on snap Rem Rem Dave Ensor 05-Oct-92 Rem - SGA Statistics captured Rem Rem Graham Wood 07-Oct-92 Rem - modified SGA stats capture Rem - added lock activity capture Rem Rem Graham Wood 12-Oct-92 Rem - added in collection of STATS$SGASTATXS set echo off feedback off verify off termout off Rem Rem Create new session in snapshot control table Rem and establish snap_id for begin snap Rem undefine INSTANCE undefine STARTED col INSTANCE new_value INSTANCE select nvl(max(value),0) INSTANCE from v$parameter where name = 'instance_number'; col STARTED new_value STARTED select to_date(JUL.VALUE, 'J') || to_char(SEC.VALUE/3600, '09') || to_char(mod(SEC.VALUE/60, 60), '09') || to_char(mod(SEC.VALUE, 60), '09') STARTED from V$INSTANCEX JULReceived on Fri Nov 17 2006 - 12:20:01 CST
, V$INSTANCEX SEC
where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%'; Rem set flag indicating if we have already recorded the stats for Rem the SGA since instance startup Rem undef NEW_SGA col NEW_SGA new_value NEW_SGA select count(1) NEW_SGA from sys.dual where exists (select 1 from stats$sgastats where instance_start = to_date('&STARTED', 'dd-mon-yy hh24 mi ss')); insert into stats$snapshots ( snap_id , snap_time , snap_instance , instance_start ) values ( snapshot_id.nextval , sysdate , &INSTANCE , to_date('&STARTED', 'dd-Mon-yy hh24 mi ss') ); commit work; Rem Rem Generate snapshot entries Rem insert into stats$librarycaches select snapshot_id.currval
, NAMESPACE
, GETS
, GETHITS
, PINS
, PINHITS
, RELOADS
, INVALIDATIONS
from V$LIBRARYCACHE; insert into stats$latches select snapshot_id.currval ,names.name ,stats.level# ,stats.gets ,stats.misses ,stats.sleeps ,stats.immediate_gets ,stats.immediate_misses from v$latch stats ,v$latchname names where stats.latch# = names.latch#; rem We'd like 20 groups reported, so calculate how many buffers rem should be in each group. The last group may be shorter! rem Can't use .CURRVAL with a GROUP BY col snapid noprint new_value snapid col granule noprint new_value granule col maxbuf noprint new_value maxbuf select snapshot_id.currval snapid ,ceil(value / 20) granule ,value maxbuf from v$parameter where name = 'db_block_buffers'; insert into stats$lrustats select &snapid ,'C' ,&granule * trunc(indx / &granule) + 1 ,least(&granule * (trunc(indx / &granule) + 1), &maxbuf) ,sum(count) from v$*kcbcbhx* where indx > 0 group by trunc(indx / &granule); select ceil(value / 20) granule ,value maxbuf from v$parameter where name = 'db_block_lru_extended_statistics'; insert into stats$lrustats select &snapid ,'R' ,&granule * trunc(indx / &granule) + 1 ,least(&granule * (trunc(indx / &granule) + 1), &maxbuf) ,sum(count) from v$kcbrbhx where indx > 0 group by trunc(indx / &granule); insert into stats$rollstats select snapshot_id.currval ,usn ,extents ,rssize ,writes ,xacts ,gets ,waits ,optsize ,hwmsize ,shrinks ,wraps ,extends ,aveshrink ,aveactive from v$rollstat; insert into stats$rowcaches select &snapid ,cache# ,parameter ,sum(count) ,sum(usage) ,sum(gets) ,sum(getmisses) ,sum(scans) ,sum(scanmisses) ,sum(scancompletes) ,sum(modifications) ,sum(flushes) from v$rowcache group by cache#, parameter; insert into stats$sgastats ( instance
, instance_start
, stat_type
, name
, c_value
) select &INSTANCE ,to_date('&STARTED', 'dd-Mon-yy hh24 mi ss')
, 1
, name
, value
from v$parameter where &NEW_SGA = 0; insert into stats$sgastats ( instance
, instance_start
, stat_type
, name
, n_value
) select &INSTANCE ,to_date('&STARTED', 'dd-Mon-yy hh24 mi ss')
, 2
, name
, value
from v$sga where &NEW_SGA = 0; insert into stats$sgastatxs ( snap_id
, name
, entry_no
, value
) select snapshot_id.currval
, name
, rownum
, bytes
from v$sgastatx; insert into stats$sysstats select snapshot_id.currval ,name ,value from v$sysstat; insert into stats$waitstats select snapshot_id.currval ,class ,count ,time from v$waitstatx; insert into stats$lockactivity select snapshot_id.currval ,indx ,"count" from v$le_statx; commit work; set termout on col snap_time format a20 heading 'Snapshot start time' col snap_instance heading 'Instance' col startup format a20 heading 'Instance start time' select snap_id ,to_char(snap_time, 'dd-Mon-yyyy hh24:mi:ss') snap_time ,snap_instance ,to_char(instance_start, 'dd-Mon-yyyy hh24:mi:ss') startup from stats$snapshots where snap_id= &snapid; Rem End of stat7nap.sq -- http://www.freelists.org/webpage/oracle-l
![]() |
![]() |