Change the prompt in "sh"

I set my prompt in .profile to

PS1="%m %! %c %# "; export PS1

which gives me host – command number – last part of path – # or % depending on root or not…

Reduce Disk Consumption

Get rid of all non-english files in application bundles

find / ! -name "English.lproj" -name "*.lproj" -type d -exec rm -r -- true ;

System Speedups

These things are all one-liners – watch out for the wrap in your browser.

to make window resizing and application load time faster :

sudo renice -20 -p `ps -ax | grep WindowServer | cut -c1-5 | sort | head -1`
pre-bind files in os x (optimize/defragment) :

sudo update_prebinding -root / 

Selective IMPORT from RBR file

From Ian Smith of Oracle:

If you go the EXPORT and IMPORT route then may I recommend the following:

rmu/extract/item=import

It already generates an IMPORT script.

Modify the IMPORT command to redefine any indices you want to change. IMPORT will replace the definitions in the RBR file with those on the command line. BUT make sure they are defined inside the IMPORT command (i.e. Only one semicolon)

In V7.1 you can RMU/LOAD from the RBR file (V7.1.0.2 and later documented next release 🙂

RMU/LOAD/MATCH_NAME personnel employees pers.rbr

You can then submit batch jobs to load from the RBR after creating the database using IMPORT … NO DATA.

Rdb Datatypes Decoded

-- Copyright 2002 - Oracle Corporation
-- Example CASE expression to translate Rdb system table
-- type information to SQL text
--

select
  RDB$FIELD_NAME as Column_Name,    !as "Column Name"
  case RDB$FIELD_TYPE
    when 6  then          -- Byte Integer
      'TINYINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 7  then          -- Word Integer
      'SMALLINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 8  then          -- Longword Integer
      'INTEGER' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 9  then          -- Quadword Integer
      'BIGINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 10 then 'REAL'       -- F_floating
    when 27 then 'DOUBLE PRECISION' -- G_floating
    when 14 then          -- Character-coded text
      'CHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 37 then          -- Varying Character-coded Text
      'VARCHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 21 then 'DECIMAL'      -- Packed decimal string
    when 261 then
      'LIST OF BYTE VARYING (' ||
      CAST(RDB$SEGMENT_LENGTH AS VARCHAR(5)) || ')'
    when 35 then
      -- Absolute Date and Time
      case RDB$FIELD_SUB_TYPE
        when 0  then
          'DATE VMS'
        when 56 then
          'TIME (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        when 7  then
          'DATE ANSI'
        when 63 then
          'TIMESTAMP (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        else 'INTERVAL ' ||
          case RDB$FIELD_SUB_TYPE
            when 513 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 515 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MONTH'
            when 514 then
              'MONTH(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 516 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 524 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO HOUR'
            when 540 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 572 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 520 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 536 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 568 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 528 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 560 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 544 then
              'SECOND(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ',' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            else
              '(unknown interval)'
          end
      end
    else
      '(unknown data type)'
  end as Data_Type          ! as "Data Type"
from RDB$FIELDS
where RDB$FIELD_NAME not starting with 'RDB';

RMU/Repair – Caveat Emptor

Don’t EVER type a control-Y at an “RMU/REPAIR/ABM dbname” command where you have forgotten the /AREA qualifier.

Bad things happen to the RDB$SYSTEM area.

Hopefully completing a full RMU/REPAIR/ABM command will fix the damage.

Logical Area Number

Logical Area Number – I couldn’t remember this exactly… larea is logical area

select rdb$relation_name from rdb$relations
where rdb$storage_id = :larea_no
union
select rdb$index_name from rdb$indices
where rdb$index_id = :larea_no
union
select rdbvms$map_name from rdbvms$storage_map_areas
where rdb$storage_id = :larea_no;

If have received ABMBITERR messages you will probably be wanting to do this.

ABMBITERR, inconsistency between spam page and bit in
area bitmap in larea
page

Explanation: Either an ABM page contains a bit set for a spam page
that does not manage the logical area of the ABM page
or an ABM page does not contain a bit set for a spam
page that does manage the logical area of the ABM page.
The

User Action: Use RMU REPAIR to rebuild the ABM pages.

$ rmu/close/cluster DB

$ rmu/repair/abm DB /area=the-area-name-from-the-select
Continue reading “Logical Area Number”

Speeding up Queries

yesterday’s activity – get the slow query to go faster. Two things

1. $ ASSIGN 4000 RDM$BIND_BUFFERS

changed the size of the database window from 200 buffers by a factor of 20. This allowed very large portions of the thrashing index area to reside in memory (60 I/O per second down to 1)

2. $ SET VOLUME DISK$SCRATCH /EXTENSION=16000

changed the file extend size from 5 – huge increase. Went from 3 extends per second to .03

Result? Approximately 12 hour query down to about 1 hour with zero code changes!

I like that!

Finding a Logical Area

rmu/analyze/opt=debug/end=1 DBname/out=logical_areas.dat

select rdbvms$map_name, rdbvms$area_name, rdb$storage_id, rdb$index_id from rdbvms$storage_map_areas

select rdb$relation_name, rdb$storage_id from rdb$relations order by 2

System Index Compression

3.9.4 System Index Compression

When you create a database, you can specify that Oracle Rdb compress the system indexes. To do so, use the SYSTEM INDEX COMPRESSION IS ENABLED clause of the CREATE DATABASE statement. To enable system index compression for an existing database, you must export and import the database and specify the SYSTEM INDEX COMPRESSION IS ENABLED clause with the IMPORT statement.

For system indexes, Oracle Rdb uses run-length compression, which compresses a sequence of space characters from text data types and binary zeros (also called ASCII NUL) from nontext data types. It compresses any sequences of two or more spaces for text data types or two or more binary zeros for nontext data types. See Section 3.9.3.4 for more information on run-length compression.

Compressing system indexes results in reduced storage and improved I/O. Unless your applications often perform data definition concurrently, you should use compressed system indexes.