Pretoria Manual


Table of Contents

  1. What is Pretoria ?
    1. Pretoria internals
    2. Why Threads ?
    3. Why Java ?
  2. What is an Oracle indexfile ?
    1. Example
    2. How to create an indexfile ?
  3. How to create your storage parameter file ?
    1. Syntax
  4. Running Pretoria
  5. Running the generated scripts
  6. Reorganizing your DB
  7. Beat our record !
  8. Bugs
  9. Limitations
  10. About the author

What is Pretoria ?

Pretoria is a tool for manipulating Oracle indexfiles.  Basically, Pretoria parses the indexfile and performs a search and replace on the storage parameters - it then separates table create statements and index create statements. These files can then be used to pre-create all database segments, basically reorganizing your database. Pretoria can also be used as a pretty printer ( hence the name, pretty oracle -> pretora -> pretoria ) for indexfiles. 

Pretoria Internals

 

So how does Pretoria work ?

Pretoria does not use parse trees nor does it make use of the railroad diagrams found in SQL reference books. This is also the reason why the pretty printing stuff is pretty lame. Also - not lexical parsing is done !

Why threads ?

1. As an Oracle DBA/Consultant I don't do a lot of programming - except for the usual Ksh scripting and PL/SQL stuff. So when I program something it has to be something fun or I lose interest very easily ( I guess my ex colleagues can confirm this ? :-) ). That's the main reason for using threads and FIFO's as inter-thread communication - it's fun !

2. Depending on your hardware and platform, performance - can - increase using threads. If you run multi-threaded programs on an SMP box (multiple processors), your OS will run multiple threads concurrently on multiple processors. This all depends on the implementation of the native thread library. However several operating systems implement the two-level thread model - for example IBM AIX, HP-UX, Solaris, Digital Unix (Tru64).

Why Java ?

Pretoria is written using JDK1.3. However, all code is 1.1.x compliant.

I used Java in general because it's platform independent. We had a Ksh script (written by my good friend Kugendran Naidoo) using kick ass awk/sed/grep text manipulations doing exactly the same (well almost) thing however :

So, using Java these aren't issues anymore !

Another reason is - it's a language I pretty much know by heart. I've been using Java since version 1.0.1 and up for several projects as a software engineer.

Back to Top

What is an Oracle indexfile ?

As said before - it's a file containing create statements for tables, indexes and clusters.

Example

REM CREATE TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ("ID" 
REM NUMBER(*,0), "TEMPLATE_SCHEMA" VARCHAR2(30) NOT NULL ENABLE, 
REM "TEMPLATE_NAME" VARCHAR2(50) NOT NULL ENABLE, "TEMPLATE_TYPE" 
REM VARCHAR2(50) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(30), "CREATED_ON" 
REM DATE, "UPDATED_BY" VARCHAR2(30), "UPDATED_ON" DATE) PCTFREE 10 
REM PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 
REM 24576 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST 
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" ;
CREATE UNIQUE INDEX "PORTAL30"."WWV_SYS_APP_TEMPLS$_PK" ON 
"WWV_SYS_APPLICATION_TEMPLATES$" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 
255 STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "PORTAL" LOGGING ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ADD 
REM CONSTRAINT "WWV_SYS_APP_TEMPLS$_PK" PRIMARY KEY ("ID") USING INDEX 
REM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 40960 NEXT 40960 
REM MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST 
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" ENABLE ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ADD 
REM CONSTRAINT "WWV_SYS_APP_TEMPLS$_CK1" CHECK (template_type in 
REM ('STRUCTURED', 'UNSTRUCTURED')) ENABLE NOVALIDATE ;
REM CREATE TABLE "PORTAL30"."WWV_SYS_APP_TEMPLATE_DETAILS$" 
REM ("APP_TEMPLATE_ID" NUMBER(*,0), "ELEMENT" VARCHAR2(50), "THE_VALUE" 
REM LONG) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 
REM STORAGE(INITIAL 65536 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
REM TABLESPACE "PORTAL" ;
CREATE INDEX "PORTAL30"."WWV_SYS_APP_TMPL_DET$_IDX" ON 
"WWV_SYS_APP_TEMPLATE_DETAILS$" ("APP_TEMPLATE_ID" ) PCTFREE 10 INITRANS 2 
MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 24576 MINEXTENTS 1 MAXEXTENTS 505 
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "PORTAL" LOGGING ;
REM CREATE TABLE "PORTAL30"."WWV_SYS_BUILDER_DETAILS$" ("ID" NUMBER(*,0) 
REM NOT NULL ENABLE, "NAME" VARCHAR2(40) NOT NULL ENABLE, "ARRAY_GROUP" 
REM VARCHAR2(40), "TYPE_ID" NUMBER(*,0) NOT NULL ENABLE, "SEQ" NUMBER NOT 
REM NULL ENABLE, "SECTION" NUMBER NOT NULL ENABLE, "INPUT_TYPE" 
REM VARCHAR2(10) NOT NULL ENABLE, "INPUT_WIDTH" NUMBER NOT NULL ENABLE, 
REM "INPUT_HEIGHT" NUMBER NOT NULL ENABLE, "COL_SPAN" NUMBER, "ATTRIBUTE" 
REM VARCHAR2(1), "REQUIRED" VARCHAR2(1), "DEF_ALLOWED" VARCHAR2(1), 
REM "IMPACT" VARCHAR2(1), "ARRAY" VARCHAR2(1), "LONG_TYPE" VARCHAR2(1), 
REM "LOVNAME" VARCHAR2(4000), "PRE_FUNCTION" VARCHAR2(500), 
REM "POST_FUNCTION" VARCHAR2(500), "PROMPT" VARCHAR2(200), 
REM "DEFAULT_VALUE" VARCHAR2(4000), "PROMPT_ALIGN" VARCHAR2(10), 
REM "UPPERCASE" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 
REM 255 LOGGING STORAGE(INITIAL 573440 NEXT 147456 MINEXTENTS 1 
REM MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 
REM BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_BUILDER_DETAILS$" MODIFY 
REM ("INPUT_WIDTH" DEFAULT 1 ) ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_BUILDER_DETAILS$" MODIFY 
REM ("INPUT_HEIGHT" DEFAULT 1 ) ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_BUILDER_DETAILS$" MODIFY ("UPPERCASE" 
REM DEFAULT 'N') ;
CREATE UNIQUE INDEX "PORTAL30"."WWV_SYS_BUILDER_DETAILS$_PK" ON 
"WWV_SYS_BUILDER_DETAILS$" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 122880 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 
50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" 
LOGGING ;

As you can see - it's crap - but very useful crap !!! Only the create index statements are uncommented ! Now you could start hacking away on the indexfile using your favorite text editor (vi) orrrrr you could use Pretoria !

How to create an indexfile ?

An indexfile is created using Oracle's export/import utilities. 

  1. create an Oracle export dump file using exp

>> exp system/manager FULL=Y ROWS=N FILE=export.dmp

No need to export data here - just a logical export will do.

  1. use imp to create the indexfile from the export dump

>> imp system/manager FILE=export.dmp FULL=Y INDEXFILE=index.sql

Don't worry - nothing will be imported into your database ! It'll just create the indexfile !

Note : on NT/Win2000 platforms the export/import executables are named differently - they always have stupid version numbers or something in their name : for example exp23 or exp8 ... It always confuses me too (but who runs a real database on NT anyway ?)

Back to Top

How to create your storage parameter file ?

The storage parameter file can be created in different ways. But one thing is for sure - YOU will have to do it using creating storage rules YOU find appropriate. One could use PRO*C, PRO*COBOL, JDBC (Java)  or whatever. For me the fasted way (and most platform independent) was ordinary PL/SQL using UTL_FILE package. 

I've included a sample script called storage_rules.sql. It basically is going to query the size of all tables, indexes and table partitions and recalculated initial extent, next extent, pctincrease and parallel degree. I've made decisions based on the segment's SIZE found in DBA_SEGMENTS. This can be incorrect as it is based on the number of allocated extents - and not the actual data. A better way to do it is :

  1. in the script - analyze your segment first if you haven't already(using dynamic or native SQL)
  2. after statistics are available, query AVERAGE_ROW_LEN and NUMROWS in DBA_TABLES -> real size of segment ( I think DBMS_SPACE should also do the trick - just use your imagination)
  3. depending on optimizer mode - delete statistics (using dynamic or native SQL)

Although I already have a script like this, I did not include it as it's not necessary for explaining the principles. (And if you're a DBA it should be a piece of cake to write such a script - sometimes you have to work for your money :-) )

Syntax

Quite handy is the fact that the storage parameter file can contain database users passwords. If you look at an indexfile you'll notice CONNECT <<user>> statements. However, the indexfile doesn't add the passwords. This would normally mean walking through the whole file using your favorite text editor and adding user passwords (or else suffer 'No longer connected to Oracle' warnings!). In other words - you can list users password using following syntax :

CONNECT <<username>>  <<password>> 

CONNECT SYSTEM  MANAGER

Only use whitespaces and/or tabs as separators.

Listing storage parameters :

"OWNER"."SEGMENTNAME"."PARTITIONNAME"  <<storage parameter 1>> <<value 1>>  <<storage parameter n>> <<value n>>

The first token on a line should always be owner + segmentname + partitionname (optional) as it'll be used as a hashkey. Always use double quotes (") and dots (.) to separate owner, segmentname and partitionname (optional). 

<<storage parameter>> is one of the following :

All storage parameters have to be listed in UPPERCASE. Two parameters are currently not supported - buffer pool. This parameter can also be set using an ALTER TABLE command and should only be changed after a database tuning and splitting up your buffer cache. The other parameter is INSTANCES - this parameters is usefull for Oracle Parallel Server. It defines the number of instances in an OPS setup that will take part of running a query (inter-instance parallelism). Modify this parameter only when you're migrating from OPS to Single Instance or vice versa (after tuning).

You do not have to list the storage parameters in any particular order !

A lot of people have asked me to add the ability to work with wildcards. Starting from Pretoria 3a I've added the next best thing : DEFAULT values. This feature was partially implemented by Stéphane Duprat - I just optimised the code a bit and made it work for all 3 object types (TABLE, INDEX, CONSTRAINT).This feature introduced some extra keywords :

These 3 new keywords are quite self-explaining. If Pretoria can not find an entry for a particular object (table, index, constraint), it'll look for DEFAULT storage parameters of that object's owner.

If Pretoria can not find an entry for a particular object, and also no entry for the DEFAULT value for that object's owner, Pretoria will look for an overall DEFAULT value for that object's type.

Example :

"DWH"."CUSTOMERS" INITIAL 100M NEXT 100M PCTFREE 5 TABLESPACE CUST

DEFAULT_TABLE("DWH") INITIAL 1M NEXT 1M TABLESPACE DWH_DATA

DEFAULT_TABLE ("DWHADMIN") INITIAL 512K NEXT 512K TABLESPACE DWH_ADMIN_DATA

DEFAULT_TABLE INTIAL 128K NEXT 128K TABLESPACE DATA

"DWH"."CUSTOMERS_IDX" INITIAL 20M NEXT 20M TABLESPACE CUST_IDX

DEFAULT_INDEX("DWH") TABLESPACE INDEX DWH_INDX

DEFAULT_INDEX TABLESPACE INDX

Using the above as a storagefile for Pretoria, the following things will happen :

Back to Top


Running Pretoria

If you downloaded the source code (Pretoria.java), you first have to compile it. You can do this by invoking javac :

javac Pretoria.java

javac is not part of JRE (java runtime environment). You need a JDK (java development kit) to compile.

Pretoria 1x

java -classpath Pretoria.jar Pretoria <indexfile> <storagefile> <table output file> <index output file>

java -classpath Pretoria.jar Pretoria -p <indexfile> <table output file> <index output file>

Using -p, Pretoria will only perform its pretty printing functionality - no storage file is needed.

Pretoria 2x

Pretoria 2a introduces new output modes - and thus, new command line switches :

Examples :

If you're using JRE (Java Runtime Environment) instead of JDK (Java Development Kit) it's possible that you'll have to invoke jre instead of java.

Back to Top

Running the generated scripts

Because the output threads add platform specific line separators, you can get into trouble using SQL*Plus. I would recommend running the table & index create script as SYSTEM in Server Manager (svrmgrl / svrmgr23 / svrmgr30) on pre 8i version. From 8.1.5 on you can use SQL*Plus but perform  'set SQLBLANK on' first !!!

Back to Top

Reorganizing your DB

In short - following these steps :

  1. Export your DB 
  2. Create an indexfile
  3. Make your storage rules - remember - there is no sky !
  4. Run Pretoria 
  5. Use the table output script to pre create your tables
  6. Import your data (INDEX=N & CONSTRAINTS=N ... referential key constraints :-) )
  7. Run the index output script
  8. Import your data (ROWS=N INDEX=N CONSTRAINTS=Y)

This is quite simple - If you want further assistance you can always contact me...

Back to Top

Beat our record !!!

Our current record for reorganizing a 

is 10 hours !!!

We used our own proprietary tool (massive parallel export/import) for exporting and importing the data. The 10hours do not include database monitoring to define our storage rules ! We also improved our proprietary export/import tool (thanks again to Kugendran Naidoo) so it should even go faster now.

The reorganization was only a part of an OPS (12 nodes) migration to Single Instance Oracle setup. We first moved the warehouse from an IBM RS6000 SP2 using EMC storage and raw devices to the IBM S80/ESS on JFS - this phase took only 5hours for the database to start up. Afterwards, we recreated the warehouse using bigger db blocks, going from 8.0.4 32bit to 8.1.6 64bit, full export/import. The warehouse was down for less than a weekend ! Performance gains were unbelievable - some reports that took 3days to finish now ran within one minute :-) You do the math !

Anyone beating our record - let me know and I'll send you a real belgian beer !

People interested in our services can always contact me. 

Oh - this was back in the magical year 2000 !!!

Back to Top


Bugs

This program was written during 2 days - I had the flue ... and got bored - so I wrote this small program. I've only tested it briefly on MS Win, Linux, IBM AIX, Compaq Tru64 and HPUX11 using JDK 1.1.5, 1.1.8, 1.2 and 1.3 and indexfiles coming from Oracle RDBMS 7.3.4, 8.0.4, 8.1.6 and 8.1.7 (32 & 64 bit), 9i (64bit). So make no illusions - I'm pretty sure it'll contain bugs as I'm a DBA and thus inherently lazy . If you find one, or if you have a request or a great idea - let me know ! My email address is kurt_van_meerbeeck@axi.be 

Back to Top

Limitations

Pretoria is free of charge !!!

Back to Top


About the author

My personal webpage is http://zap.to/knal !

Cheers

Kurt.

Back to Top

Kurt Van Meerbeeck / kurt_van_meerbeeck@axi.be