Data Block Cache Header Format Changes (Oracle8 Physical layout)

Oracle8 has introduced a change with the data block cache header format.  The
basic idea is that incarnation and sequence numbers stored in the cache header
have been replaced with an SCN number and sequence number.  The size of the
cache header has remained 20 bytes.  The size of the block trailer is still 4
bytes.  Only the format of the cache header and the trailer has changed.
Oracle7 Implementation
~~~~~~~~~~~~~~~~~~~~~~
The current Oracle7 implementation stores the incarnation and sequence number
in the cache header of each data block to determine the current version of the
block.  A compressed version of the incarnation/sequence is maintained at the
end of the data block.  The incarnation and sequence numbers are each 4 byte
values.  The low-order 2 bytes of each value are stored as the last 4 bytes of
the data block.  This information is used to detect media corruption when
reading the block for normal operations or during recovery, or when validating
the block.  It allows for a consistency check to ensure the top of the block
is in sync with the bottom of the block.
See [NOTE:33242.1] for more details on the Oracle7 format.
Oracle8 Implementation
~~~~~~~~~~~~~~~~~~~~~~
The basic idea is to store the current redo generating SCN in the cache header
when making a change to a block.  Since multiple changes to a block can be
made at the same SCN, a sequence number is also stored in the cache header to
differentiate between different changes at the same SCN.  The sequence number
is increased each time a change is made at the same SCN.  The sequence number
is reset to 1 when making a change at a higher SCN than the SCN currently in
the block.
Oracle8 data block layout
-------------------------
|   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |10 |11 |12 |13 |14 |15 |
+-------+-------+---------------+---------------+---------------+
|Typ|Fmt|Filler |     RDBA      |    SCNBase    |SCNWrap|Seq|Flg|
|-------+-------+---------------+---------------+---------------|
|ChkVal |Filler | <------                                       |
|-------+-------+                                               |
:                       Body of                                 :
:                        Data Block                             :
:                                                               :
|                                               +---------------|
|                                    ---------> |     Tail      |
+---------------------------------------------------------------+
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | B | C | D | E | F |
|   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
The bytes in the header are defined as:
Typ     Block Type (defined in k.h).
Fmt     Block format.  In Oracle8 this is 2, converted on-the-fly.
Filler  Not used currently.
RDBA    Relative database address of the block.
SCNBase SCN Base
SCNWrap SCN Wrap
Seq     Sequence number.  Incremented for every change made to the
block at the same SCN.
Flg     Flag (defined in kcbh.h)
ChkVal  Optional check value for the block.  Set so that an xor of all
the ub2 values in the block equals zero.  This may require the
value to be zero (unlike Oracle7).  ChkVal will be verified
only if KCBHFCKV is set in the Flg field.
- always for file header blocks
- if the init.ora <parameter:DB_BLOCK_CHECKSUM> is set.
Tail    Consistency information used to verify the beginning and the
end of the block are of the same version.  Lower order 2 bytes
of SCNBase, plus block Type, plus SCN Seq number.
Migration to the New Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Data block cache headers will be upgraded to the new Oracle8 format on-the-fly
when the block is read into the cache.  Oracle8 will do this automatically for
every block that is read into the cache during normal operation. The on-disk
copy is not updated unless the block becomes dirty.
Soft Corrupt in Oracle8
~~~~~~~~~~~~~~~~~~~~~~~
Soft corrupt blocks in Oracle8 have:
Seq# is 0xff
flg  is 0x00

SCRIPT – to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

Script:

select
'session_cached_cursors'  parameter,
lpad(value, 5)  value,
decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
( select
max(s.value)  used
from
v$statname  n,
v$sesstat  s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
v$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value,  '990') || '%'
from
( select
max(sum(s.value))  used
from
v$statname  n,
v$sesstat  s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
v$parameter
where
name = 'open_cursors'
)
/

Sample Output:

PARAMETER              VALUE      USAGE
---------------------- ---------- -----
session_cached_cursors    20       100%
open_cursors             300        16%
select
to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
( select sum(value) calls from v$sysstat
where name in ('parse count (total)', 'parse count (failures)') ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/
select
to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
( select sum(value) calls from v$mystat m, v$statname s
where m.statistic# = s.statistic# and
name in ('parse count (total)', 'parse count (failures)') ),
( select value hard from v$mystat m, v$statname s
where m.statistic# = s.statistic# and name = 'parse count (hard)' ),
( select value sess from v$mystat m, v$statname s
where m.statistic# = s.statistic# and name = 'session cursor cache hits' )
/

SCRIPT TO GENERATE SQL*LOADER CONTROL FILE

This script prepares a SQL*Loader control file for a table already existing in the database. The script accepts
the table name and automatically creates a file with the table name and extension ‘ctl’.  This is specially
useful if you have the DDL statement to create a particular table and have a free-format ASCII-delimited file but
have not yet created a SQL*Loader control file for the loading operation.

Default choices for the file are as follows (alter to your needs):

Delimiter:              comma (‘,’)
INFILE file extension:  .dat
DATE format:            ‘MM/DD/YY’

You may define the Loader Data Types of the other Data Types by revising the DECODE function pertaining
to them.

Please note:
The name of the table to be unloaded needs to be provided when the script is executed as follows:

Script:

set echo off
set heading off
set verify off
set feedback off
set show off
set trim off
set pages 0
set concat on
set lines 300
set trimspool on
set trimout on
spool &1..ctl
select 'LOAD DATA'||chr (10)||
'INFILE '''||lower (table_name)||'.dat'''||chr (10)||
'INTO TABLE '||table_name||chr (10)||
'FIELDS TERMINATED BY '','''||chr (10)||
'TRAILING NULLCOLS'||chr (10)||'('
from   all_tables
where  table_name = upper ('&1');
select decode (rownum, 1, '   ', ' , ')||
rpad (column_name, 33, ' ')||
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER',   decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('||column_name||
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('||
column_name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
column_name||'=BLANKS)')),
'DATE',     'DATE "MM/DD/YY"  NULLIF ('||column_name||'=BLANKS)',
null)
from   user_tab_columns
where  table_name = upper ('&1')
order  by column_id;
select ')'
from sys.dual;
spool off

Sample Output:

LOAD DATA
INFILE 'tv.dat'
INTO TABLE TV
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(  T1                               INTEGER EXTERNAL NULLIF (T1=BLANKS)
, T2                               CHAR NULLIF (T2=BLANKS)
, T3                               CHAR NULLIF (T3=BLANKS)
)

沪ICP备14014813号

沪公网安备 31010802001379号