Re: v$kcbcbhx
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: knecht.stefan@xxxxxxxxx
- Date: Fri, 17 Nov 2006 10:20:01 -0800
On 11/17/06, Stefan Knecht <knecht.stefan@xxxxxxxxx> 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
Other related posts:
