Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: v$kcbcbhx

Re: v$kcbcbhx

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 17 Nov 2006 10:20:01 -0800
Message-ID: <bf46380611171020m68c3ad77m3f99fb3102fc58bc@mail.gmail.com>


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.
>
> Wonder where this came from....

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 JUL

, 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
Received on Fri Nov 17 2006 - 12:20:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US