Creating a Cross Tab Report

I see quite a few questions about creating a crosstab table. While working with a good friend, Gary Withrow, who works for Santa Cruz County, California, he showed me a great technique for doing crosstabs that he has given me permission to share. Essentially it involves using the capability of embedding expressions into the COUNT or other functions available in Oracle.

In 9i and above, of particular use in generating crosstabs is the CASE structure, for example:

-- Crosstab of owner and object_type
--
col owner format a10
set numwidth 8
set lines 132
set pages 50
select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(case when a.object_type = 'TABLE' then 1 else null end) "Tables",
count(case when a.object_type = 'INDEX' then 1 else null end) "Indexes",
count(case when a.object_type = 'PACKAGE' then 1 else null end) "Packages",
count(case when a.object_type = 'SEQUENCE' then 1 else null end) "Sequences",
count(case when a.object_type = 'TRIGGER' then 1 else null end) "Triggers",
count(case when a.object_type not in
('PACKAGE','TABLE','INDEX','SEQUENCE','TRIGGER') then 1 else null end) "Other",
count(case when 1 = 1 then 1 else null end) "Total"
from dba_objects a
group by rollup(a.owner)
/

The above SQL will generate a crosstab report on Object owner verses counts of the various type of objects they own as well as totals across all columns:


SQL Result

As you can see, this is a very useful technique.

In older versions of Oracle where the CASE is not available use DECODE instead:

select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(decode( a.object_type,'TABLE',1,null)) "Tables",
count(decode( a.object_type,'INDEX' ,1,null)) "Indexes",
count(decode( a.object_type,'PACKAGE',1,null)) "Packages",
count(decode( a.object_type,'SEQUENCE',1,null)) "Sequences",
count(decode( a.object_type,'TRIGGER',1,null)) "Triggers",
count(decode( a.object_type,'PACKAGE',null,'TABLE',null,'INDEX',null,'SEQUENCE',null,'TRIGGER',null, 1)) "Other",
count(1) "Total"
from dba_objects a
group by rollup(a.owner)
/

I have tested this in 8i, 9i and 10g and it works in all versions.

Thanks Gary for finding it and sharing it!

Using a HASH Signature in Data Warehouses

On my current assignment I am dealing with slowly changing dimensions, I found an interesting technique I would like to share.

In data warehouses one item that can be difficult to deal with are slowly changing dimensions. Slowly changing dimensions are dimensions whose values may alter over time such as addresses, deployments, etc. Often it is a desire of the data warehouse manager to maintain a running history of these changes over time, this makes the dimension a type 2 dimension. The reason these are difficult to deal with is because determining what has changed becomes difficult if there are more than a few fields in the dimension. The data warehouse manager has the choice of a difficult, field by field verification of each field in the record or using some other form of field verification to determine if a record has changed in a dimension thus requiring a new record insertion. When the new record is inserted into a slowly changing dimension the old record is marked as invalid either by a flag or via a date field or set of date fields allowing for a bi-temporal dimension.

One method for finding out if a record has a changed field is to generate a CRC or a hash for the entire pertinent sections of the record, store this signature with the record and then on each record that is to be inserted, a signature is calculated for the important sections of the record. This new signature is compared to the stored signature and if the signatures match, the record is tossed, if the signature is changed, the new record is inserted, the old is marked as invalid and the new marked as the current record, if the new record matches none of the existing signatures, the record is inserted but no existing records are altered.

However, calculating a CRC or HASH can be difficult, unless you know something about what Oracle provides in the various packages contained in Oracle. For example the OUTLN_EDIT_PKG contains the GENERATE_SIGNATURE package. The GENERATE_SIGNATURE package creates a 32 byte hash signature for up to a 32k-1 sized string value passed to it either internally or via a procedure call. If the pass-in is from a procedure call from SQL then the length would be limited to 4000 bytes. Providing your critical columns are less than 4000 bytes concatenated length you can generate a signature that can be used to compare records, in this scenario, each date or number field should be converted using the appropriate function to a character equivalent.

To use the OUTLN_EDIT_PKG.GENERATE_SIGNATURE package the first thing to do is make sure your user that owns the schema for the data warehouse (DWH) has EXECUTE privilege on the OUTLN_EDIT_PKG:

From the owner of the OUTLN_EDIT_PKG, the user SYS, grant the proper privilege:

GRANT EXECUTE ON outln_edit_pkg TO ;

In order to use the GENERATE_SIGNATURE package most effectively, I suggest wrapping it into a deterministic function, for example:

From the DWH user you could create a function similar to:

CREATE OR REPLACE FUNCTION get_sign (text VARCHAR2)
RETURN RAW
DETERMINISTIC
AS
signature RAW(32);
text2 VARCHAR2(32767);
BEGIN
text2:=text;
IF text2 IS NULL THEN
text2:='1';
END IF;
sys.outln_edit_pkg.generate_signature(text2,signature);
RETURN signature;
END;
/

Note that this is just a bare-bones function, exception checking should be added.

Notice I use a default value of '1' if the input value is null, because the function generates a hash signature you cannot pass it a null value, so in the case of a null value we change it to a 1, you of course could use any string value you desire, just be sure to document it!

Once you have a deterministic function you can either use it as an INSERT into a table column, or, use it to generate a function based index. By using a function based index and query re-write you can eliminate the need to have a column in the table. This allows you to regenerate the matching columns without having to update the complete dimension each time there may be columns added to the dimension. Also, there are performance benefits to using a function based index or an internal column over doing either a column by column match. Let's look at some examples:

First, we have our test table TEST, in this case I created it from the DBA_OBJECTS view using a simple CTAS.

SQL> DESC test
Name Null? Type
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> SELECT COUNT(*) FROM test;

COUNT(*)
----------
32359

Once we have our test table, we create our function to call the GENERATE_SIGNATURE and then use it to create a function based index:

SQL> CREATE INDEX test_fbi ON test(get_sign(ownerobject_namesubobject_name));

Index created.

Before we can use query rewrite we must analyze the table and index as well as set the proper initialization parameters, (query_rewrite_enabled = true and query_rewrite_integrity = trusted).

SQL> ANALYZE TABLE test COMPUTE STATISTICS;

Table analyzed.

Let's look at a query that will use this index and see what the performance is like.

SQL> SELECT a.object_id FROM test a
2 WHERE
3 get_sign('SYSTEM''TEST''') = get_sign(a.ownera.object_namea.subobject_name);

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=324
Bytes=11016)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=324
Bytes=11016)
2 1 INDEX (RANGE SCAN) OF 'TEST_FBI' (NON-UNIQUE) (Cost=2
Card=129)

Statistics
----------------------------------------------------------
74 recursive calls
0 db block gets
14 consistent gets
1 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1
Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'TEST_FBI' (NON-UNIQUE) (Cost=2
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1
Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'TEST_FBI' (NON-UNIQUE) (Cost=2
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now let’s drop the index and re-run our query to check performance without it:

SQL> DROP INDEX test_fbi;

Index dropped.

SQL> SELECT a.object_id FROM test a
2 WHERE
3 get_sign('SYSTEM''TEST''') = get_sign(a.ownera.object_namea.subobject_name);

OBJECT_ID
----------
35567

Elapsed: 00:00:01.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=138 Card=324
Bytes=11016)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=138 Card=324
Bytes=11016)

Statistics
----------------------------------------------------------
243 recursive calls
0 db block gets
526 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=138 Card=324
Bytes=11016)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=138 Card=324
Bytes=11016)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
486 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SELECT a.object_id FROM test a
2 WHERE
3 get_sign('SYSTEM''TEST''') = get_sign(a.ownera.object_namea.subobject_name);

OBJECT_ID
----------
35567

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=132 Card=324
Bytes=11016)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=132 Card=324
Bytes=11016)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
446 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

As you can see the simple SELECT statement using the function based index performs in too small a time interval for Oracle's timing command to measure (the test was run several times, with identical results), while the SELECT not able to use the index takes a consistent time just over 1 second (the tests were run several times, and the times averaged).

As you can see even when the table is read from memory (physical reads = 0) the consistent gets (446) are much higher than those from the index based selects ( consistent gets 4) and the index based query performs better even though it has 74 recursive calls. The recursive calls were only present on the first run, after that the recursive calls were 0.

In situations where the call is repeated many times, such as for loading dimension tables, then the performance improvement should be dramatic.

In other tests, using an added column SIGNATURE, a RAW(32), and an index on the column we can get identical performance to a function based index and table combination:

First, using a modified SELECT to go against the SIGNATURE column and no index:

SQL> alter table test add signature raw(32);

Table altered.

SQL> update test a set a.signature=get_sign(a.ownera.object_namea.subobject_name);

32359 rows updated.

SQL> commit;

Commit complete.

SQL> set autotrace on
SQL> set timing on
SQL> SELECT a.object_id FROM test a
2 WHERE
3 get_sign('SYSTEM''TEST''') = a.signature;

OBJECT_ID
----------
35567

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=324
Bytes=7128)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=116 Card=324
Bytes=7128)

Statistics
----------------------------------------------------------
277 recursive calls
0 db block gets
534 consistent gets
3 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=324
Bytes=7128)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=116 Card=324
Bytes=7128)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
486 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

So without an index the use of a special SIGNATURE column performs worse than the table and function based index combination. However, when we add an index, the performance is the same from the timing and the statistics.

SQL> create index test_ui on test(signature);

Index created.

Elapsed: 00:00:00.07
SQL> analyze table test compute statistics;

Table analyzed.

Elapsed: 00:00:02.05
SQL> SELECT a.object_id FROM test a
2 WHERE
3 get_sign('SYSTEM''TEST''') = a.signature;

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1
Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'TEST_UI' (NON-UNIQUE) (Cost=2
Card=1)

Statistics
----------------------------------------------------------
26 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1
Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'TEST_UI' (NON-UNIQUE) (Cost=2
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> /

OBJECT_ID
----------
35567

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1
Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'TEST_UI' (NON-UNIQUE) (Cost=2
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
382 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

However, if there is a chance that columns will be added or removed using a column inside the dimension will require the entire dimension to be updated a row at a time. In low row-count dimensions this may be acceptable, in large dimensions with thousands of rows it may be better to use the table and function based index solution.

It was the best of times...

I had a bit of a start the other day. I had a call from my brother in Florida telling me my Father was in the hospital for some "tests". Turns out his heart, which has been beating fiercely for nearly 70 years, has decided to take a rest every now and then. Probably he'll need a pace-maker, no big deal. No big deal, right, it gave me pause to think.

Over my life I've taken antibiotics, had inoculations (more than I care to remember courtesy of Uncle Sam), been in the hospital for stitches, an occasional kidney stone, etc. Even 80 years ago I might be dead instead of writing this. I know for a fact both my daughters and my wife, the central facets of my life, would be dead. Various infections, a blocked bowel requiring an emergency re-section, other things that we would take no notice of, polio, small pox, a plethora of deadlies would have stolen them from me, if not visa-versa.

People fantasize about simpler times. They can keep them. My Mother (god rest her) lost a brother, my Uncle, lost a son, my wife's paternal grandmother lost two children. Good old days, right. My father-in-law, a retired doctor, told the story of a patient who had come in complaining of shortness of breath, a check showed walking pneumonia, a quick prescription and poof! no more problem, 40 years earlier, it would have been a death sentence more than likely.

Perhaps we live in an age of information overload, where our lives are filled with stress and we run hither and yon after things our grandparents couldn't have conceived of. My Great grandmother, who passed away at 105 years of age, saw from wagon trains to space shuttles, all-in-all an incredible thing.

So many people seem to be longing for this age to end, whether it be through the "rapture" or whatever religious apocalyptic vision they have. They seem to fear how far we've come and how far we are likely to go. Many have forgotten history, how many-many times in the past the world has been a much more evil place than it is right now. Now we see the evil, in full color and sound, while before it could require generations for it to be seen.

I for one would not trade the current time for any I could think of in history. That we are standing on the brink of average life-times greater than 100 years is wonderful, maybe if we have longer lives we won't be so greedy with our time, maybe we will take the time to appreciate the good things instead of spending so much energy fleeing the bad. Maybe we will take the time to truly teach our children what they need to live, not just what they need to get by.

Anyway, just a few stray thoughts on this Friday in spring.

On the Demise of DBAs

Much is being said recently about how much the DBA job is changing. How soon there won't be a real need for hard-core DBAs. In a recent keynote (I was unfortunately called to a client site and it was given in my stead by David Scott, the President of the GOUSER (Georgia Oracle User) group) I addressed this issue. In an analysis of the changing DBA role I was able to identify that we have gone from the basic 13 items listed in the Version 6 Oracle Administrators guide for a description of the DBA job, to at least 2 dozen responsibilities as of 10g. The original list looked like so:

1. Installing and upgrading Oracle server and application tools.
2. Allocating system storage and planning future storage requirements for the database.
3. Creating primary database storage structures once developers have designed an application.
4. Creating primary database objects (tables, views, indexes) once the application designers have designed an application.
5. Modifying the database structure as necessary, from information given by application developers.
6. Enrolling users and maintaining system security.
7. Ensuring compliance with Oracle licensing agreements.
8. Controlling and monitoring user access to the database.
9. Monitoring and optimizing the performance of the database.
10. Planning for backup and recovery of database information.
11. Maintaining archived data on appropriate storage devices.
12. Backing up and restoring the database.
13. Contacting Oracle Corporation for technical support

The revised list:

1. Installing and upgrading Oracle server and application tools.
2. Allocating system storage and planning future storage requirements for the database.
3. Creating primary database storage structures once developers have designed an application.
4. Creating primary database objects (tables, views, indexes) once the application designers have designed an application.
5. Modifying the database structure as necessary, from information given by application developers.
6. Enrolling users and maintaining system security.
7. Ensuring compliance with Oracle licensing agreements.
8. Controlling and monitoring user access to the database.
9. Planning for backup and recovery of database information.
10. Maintaining archived data on appropriate storage devices.
11. Contacting Oracle Corporation for technical support.
12. Management of object related features.
13. Determination of LOB storage options.
14. Assistance with RAID configuration.
15. Determination of proper index strategy (normal, reverse, IOT, bitmapped)
16. Education of Developers in Oracle features and their use.
17. Management of distributed environments.
19. Management of parallel server and parallel query.
20. Determine and manage partitions and sub-partitions.
21. Determine proper use of outlines and SQL profiles
22. Create, manage and maintain resource groups,
23. Create manage and maintain global temporary tables.
24. Create and manage materialized views, summaries and snapshots.
25. Monitoring and managing the automatic and dynamic sizing parameters.
26. Monitoring and managing the automated UNDO (it isn’t set and forget)
27. Monitoring and tuning RAC environments, especially the cluster interconnect.
28. Manage and maintain fine grained auditing (HIPPA/SOX requirements)
29. Manage and maintain row level security28. Manage and maintain fine grained access controls.

Add to the above: Monitor and maintain application servers, web servers, connection managers, LDAP and other servers as well as the entire client to database environment in many shops the DBA does it all.

Now there may be arguments on both sides about some of the above rolling into some of the original listed general categories, but when some commands require multiple pages to just describe (CREATE TABLE for example), let alone show meaningful examples for, well, they need to be broken out as a responsibility.

However, as the features are improved I have no doubt they will automate the complete management of SQL, tables and indexes and tablespaces as well as some memory and tuning parameters. So the DBA will give up on items 12, 13, 24 and 25. Gee, how will the other 25 (26 if you include the final one added above) items fill our time? The death of the DBA has been greatly overstated.

A case in point. I am involved in producing some roll ups for use in feeding the Oracle Warehouse builder for a 10g DWH project. The roll ups are used to pe-flatten and tune the feeds, tuning is difficult if you use 100% OWB to do the flattening. Anyway during verification that the flattening was being effective I decided to test the Enterprise Manager SQL Tuning Advisor. Before tuning, the particular view being examined required 10 minutes to produce a count of its records, I put the SQL Tuning Advisor onto it and 8 minutes later it came up with its recommendations. I implemented them and then tried the count again. 39 minutes later it came back with the count. It didn't exactly produce the desired performance improvement. To be fair, on the one I ran through before this, it cut a 4 minute run to a 2 minute run on a different view. Could I have done a better job tunng it manually? We shall see (as soon as I figure out how to get the "SQL Profile" it created removed!)

So, needless to say, I feel safe in saying the imminent demise of the DBA though much sought after by Oracle Corporate (I kid you not, I was at a ECO conference and sat through a keynote by a VP of Oracle who obviously didn't realize he was talking to a room full of DBAS, which with great relish described how Oracle was attempting to do away with the job of DBA) is still a long way off. It seems for each job they "take away" they give us three more.

More Random Data

I have been catching a load of grief over my random data post. Folks didn't like my procedure for loading the data, and folks didn't like that I said the EXCEPTIONS INTO seemed to be exhibiting anomalous behavior.

As to the first point, was I attempting to create a picture perfect, bullet proof general purpose data loader that was elegant, fully self documenting and blessed by all and sundry Oracle deities? No. Was I attempting to solve an immediate problem as quickly as possible? Yes. Did the procedure work for that? Yes.

As to the EXCEPTIONS clause, my understanding of an exception is that you get a ordinal value, a "zero" case, then, when duplicates (in the case of a primary key) are found, they are exceptions however, as has been shown to me, Oracle treats all occurrences, ordinal or not, as exceptions. So, Oracle is working as programmed. Do I agree with that? No. However, it has been that way for years. And darned of they will change it for me, imagine that!

In the SQL Manual they have a terse:

"exceptions_clause - Specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS. The exceptions table must be on your local database."

If you don't have an ordinal row, how can subsequent ones violate it?

In the Administrator's guide they provide a quick overview of the EXCEPTIONS clause with a small example. The small example (a single violation) does show two rows being mapped in the exceptions table. Taking some snippets from there:

“All rows that violate a constraint must be either updated or deleted from the table containing the constraint.” Which is incorrect. What if one of the rows is the proper row? Do I delete or update it? In the situation where there are multiple identical rows, except for a bogus artificial key, this statement would lead someone to believe all the rows needed to be deleted. The section only confuses the matter more if someone comes in with a misunderstanding about how the exception table is populated. A second line: “When managing exceptions, the goal is to eliminate all exceptions in your exception report table.”
Also seems to indicate that every line in the exceptions table is an exception, when there are clear cases where the line will be a valid row, not an exception. It all ties back to the definition of exception.

Here is the complete reference for those that doubt my veracity:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/general.htm#13282

If I ask, in the following series of numbers how many unique, single digit values are there?
1,1,1,2,2,2,3,3,3,4
The answer is 4 (1,2,3,4) and 6 duplications of the numbers, not 1 (4) and 7 exceptions as Oracle’s exception processing would answer.

If you go into it with my initial understanding of what an exception is, then the documentation seems to confirm that, if you go into it with Oracle's understanding of an exception it proves that. I have asked that a simple definition of what Oracle defines an exception to be to be added if possible to the manuals (SQL and Aministrators guide) however I am meeting with extreme resistance.

So, the behavior noted in my previous blog is not a bug, just the way Oracle works. However, you need to take this into account when dealing with Oracle's EXCEPTIONS INTO clause. I would hate for some one working on critical data to use the same definition of exception I did and delete some very important data. When using the EXCEPTIONS INTO clause, be sure to test on a test platform with non-critical data any UPDATE or DELETE activities planned and completely understand the outcome before applying the usage to production data.

Oh, and for those that want to de-dup tables, accoring to Tom Kyte, here is the fastest method known to man:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1224636375004#29270521597750

Random Data

Had an interesting bit of work come my way yesterday. For testing we needed a 600 row table with four fields. The first field had X possible entries, the second had Y and the third had Z with the last entry being a simulated count, one third of which had to be zero.

My thought was to use varray types and populate them with the various possible values, then use dbms_random.value to generate the index values for the various varrays. The count column was just a truncated call to dbms_random in the range of 1 to 600. All of this was of course placed into a procedure with the ability to give it the number of required values.

Essentially:

create or replace procedure load_random_data(cnt in number) as
define varray types;
define varrays using types;
declare loop interator;
begin
initialize varrays with allowed values;
start loop 1 to cnt times
set dbms_rabdom seed to loop interator;
insert using calls to dbms_random.value(1-n) as the indice for the various varrays;
end loop;
commit;
end;


This generated the rows and then using a primary key constraint with the exceptions clause I would then be able to delete the duplicates leaving my good rows.

create exceptions table using $ORACLE_HOME/rdbms/admin/utlexcpt.sql

alter table tab_z add constraint primary key (x,y,z) exceptions into exceptions;

delete tab_z where rowid in (select row_id from exceptions);

Well, the procedure worked as planned. However, when I attempted to use the exceptions clause on a concatenated key it did not work after the first run where it left 99 out of 600 rows it ended up rejecting all of the rows in the table on all subsequent runs! I tried it several times, each time it rejected every row in the table, even though I could clearly see by examining the first hundred or so rows that they weren't all duplicates.

The left me the old fallback of using the selection of the rowid values and then deleteing the min(rowid) values:

delete from tab_z Z
where z.rowid > (
select min(z2.rowid) from tab_z Z2
where Z.X = Z2.X and
Z.Y=Z2.Y and
Z.Z=Z2.Z);

This worked as expected. Of course you have to generate X(n1)*Z(n2)*Y(n3)*tab(n4) rows it seems to get the n4 number of rows where n1-3 are the count of the number of unique values for the given column and n4 is the number of rows in the table. For example, for my 600 rows I ended up needing to generate 30000 rows to get 600 non-dupes on the three index values. In the 30000 rows I ended up with 600 sets of duplicates in the range of 9-80 duplicates per entry. I re-seeded the random function using the loop interator with each pass through the loop.

Of course after all the work with dbms_random I figured out that it would have been easier just to cycle through the possible values for the varrays in a nested looping structure. Of course that would have led to a non-random ordered table. For the work we were doing it would have been ok to do it that way, but I can see other situations where the random technique would be beneficial.

Ad Nauseum

Ad Nauseum, an interesting term. Usually used to indicate that a subject has continued beyond its useful lifetime. Several experts seem to want to continue on such subjects as index rebuilding, clustering factor discussions and updating data dictionary tables ad nauseum. I for one find it odd they have so much free time. Perhaps I should be complemented they feel this drive to snap at my heals so much.

Note to Oracle: Perhaps you should find more for your experts to do, they seem to be caught in a rut.

I have stated my opinions on the subjects of indexes, clustering factors and data dictionary tables. I see no need to reiterate them. I have recanted incorrect advice I have given in the past. It is time to move on. Aren't there more interesting topics? Or perhaps they find, rather like Pro Wrestling draws a number of viewers, a good bash fest draws in more readers. I guess that is what happens when there is no technical content they can present as their own.

Learning is an iterative process, and during that time mistakes can and will be made. However if every time someone makes a misstatement or an error we bash them about the head and shoulders and then remind them of their past mistakes at every opportunity, they will soon cease learning altogether. Makes me wonder how these other experts treat their co-workers and clients, if they berate them at every opportunity it is no wonder they have so much free time.

As for me, I have too much to do to worry overmuch about what tripe they are currently dishing up, in fact, I plan not to respond to them any longer as I am reminded of an old country saying:

"It does no good to get down in the mud and wrestle with pigs, you can't win and the pigs enjoy it."

So I shall spend my time working for my clients, researching and documenting Oracle behaviors, tips and techniques and acquiring new skills. Perhaps if these other experts would spend as much energy on putting out new material as they do circling the same issues over and over we would all benefit.

Standing By

In several posts at various Oracle related forums I have been taken to task for standing by Don Burleson. In the past I was also taken to task for standing by others such as Rich Niemiec. I have been repeatedly asked why I don't attack, belittle or otherwise "take on" many other experts.

Well, first, I believe people are entitled to their opinions.

Second, both of the above I consider to be friends. Where I come from you stand by your friends in public. Now in private I may tell them they are incorrect and suggest they alter their viewpoints, postings or papers, but not in a public forum where it could humiliate or embarrass them.

Others seem to enjoy laying about with wild abandon when someone is mistaken, falling all over each other like the spider creatures eating their wounded in the remake of "Lost In Space". They seem to enjoy laying scorn and abuse on others. This behavior is counter productive and leads to outright fear on the part of many neophytes about writing papers, posting answers or helping anyone anywhere online.

Of course the very folks that attack with guns blazing seem most upset when you fight back, rather like a school yard bully. They expect you to roll over and show your soft spots like a cur giving into a stronger dog. Sorry, can't do that, never did it, won't do it.

I will admit mistakes, and correct them when possible. I have done this in the past and will do it in the future. However I am a strong believer in positive feedback being more beneficial than negative feedback. I rarely listen to feedback that begins something like "You worthless git, your book/paper/presentation/tip is all bloody trash". However, I always open to "Mike, the tip is interesting, but here is a problem with what you have said", I may argue a bit, but I will listen and try to improve my knowledge and abilities. Be that as it may, you attack me in a public forum, I have no compunction about taking you to task over it, also in a public forum.

Anyway, enough rant.

Going Solid

(Note: This is a blog on a specific technical topic, solid state disk drives, any responses not germane to this topic will be deleted.)

Back in my days as a Nuclear Machinest Mate/ELT on board submarines in the USN, there was a condition known as "going solid" which referred to a controlled process where by the bubble of steam in the reactor coolant loop pressurizer was slowly compressed to force it back into water thus making the entire reactor loop solid with water. Under controlled conditions this was done for various reasons, if it happened by accident it was not good as you would have lost pressure control for the reactor cooling loop. So going solid in those days was both good (if done as a controlled exercise) and possibly very bad (if allowed to happen accidentally.) But that is not what I am referring to in this blog as going solid.

What I am referring to is the new trend (well, actually it has been around since the 90's) to move databases to solid state drives. In almost all cases this form of going solid leads to measurable performance improvements by elimination of the various latencies involved with the standard computer disk drive assemblies. Of course the removal of disk rotational and arm latencies is just one of the things that solid state drives bring to the table, the other is access to your data at near-memory speeds.

What solid state drives mean to an application is that if your application is currently suffering from IO problems some if not all of them are relieved. However, the benefits are a bit lop sided depending on the predominant type of IO wait your system is seeing. If your IO waits are due to contention during the retrieval of information (SELECT statements) then you can expect a dramatic increase in performance. In tests against standard disk arrays using the TPC-H benchmark I was able to achieve a 176 times performance increase for primarily retrieval based transactions. For what I refer to as IUD transactions (INSERT, UPDATE and DELETE) only a 30-60% performance improvement was shown.

Remember that retrieval requires no additional processing once the data location is returned from the search process, while IUD involves data verification via indexes, constraints and other internal processes that tend to slow down the process thus reducing the performance gains. Does solid state disk (SSD) technology eliminate waits? Of course not, what it does is reduce their duration. For example, looking at load times:


Load Graph Using SSD

You can from the graph that load times for standard disks was on the average about 30% slower than the load times for an equivalent SSD drive array.

However looking at query response time, we see an entirely different picture:



SSD Queries

When we compare the SSD times with the same queries, same OS settings and same layout on standard drives, we see a dramatic difference (notice the scale on this next graph is logarithmic, not linear):


ATA Queries

The second graph really drives home the point, I had to make it logarithmic because of the great differences in time duration for the various queries when going to disks. What required a couple of hours (running the 20+ base queries) on SSD required 3 days on the average for the ATA drives.

Of course we don't remove the IO waits by using SSD, their duration is simply reduced to the point they are not a big factor anymore. Of course, for scalability and such you still will need to tune SQL.

Some how, I see going solid in our future.

For a complete copy of the testing look at:

http://www.rampant-books.com/book_2005_1_ssd.htm

For the latest in Solid State Drives look at:

http://www.texmemsys.com/

Why No Comments

(Note: I have modified this blog, I stated in the first verison that the SQL execution plans didn't change, when actually we can't be sure, but from the bug reports and other statistics we gathered I had inferred this, sorry if I misled anyone.)

I have received a couple of rejoiners about not allowing comments on my last blog. I did this because I found that the people who were responding to other posts spent more time being negative than positive, more time tearing down then building up and more time to offer vindictive rather than constructive comments.

This entry, as the last, will not be allowing comments because I want it to stand by itself. When I make the next blog after this one, I will open it back up for comments. I indulged a bit of vindictive myself with my last blog, maybe it was justfied, maybe not. I just get tired of the self righteous experts out there that believe their way is the only way. I try to embrace whatever technologies will help my clients.

Let me explain how a typical client visit goes:

1. We get the call and issue a contract for the visit, let's say for a Database Healthcheck

2. The contract is assigned to a Senior DBA and it is determined to be either an onsite or remote job.

3. If onsite we go to the client and first review what problems they perceive they are having. This helps establish the parameters of the initial monitoring.

4. We then establish connectivity to the instance(s) to be monitored.

5. Using Oracle provided and self generated monitoring scripts the database is analyzed, usually for (not a complete list):
A. Wait events
B. IO spread and timing
C. Log events
D. Memory statistics and parameters
E. SQL usage
F. SQL statements which require too many resources
G. Access statistics if in 9i and 10g, otherwise major problem SQL is analyzed.
H. Parameter settings

6. The server is monitored for:
A. Disk IO
B. CPU usage statistics
C. Memory usage statistics

7. The system is reviewed for security and backup and recovery processes.

Usually one or more of the above causes the need for additional monitoring or analysis in a specific area, as time allows, this is done.

Using all of the above data, a detailed list of recommendations is prepared with actionable statements giving specific fixes and time estimates for the fixes. The "low hanging fruit" such as improvements to settings, server settings, addition of needed indexes, etc, are done, in a test environment if possible and the results analyzed (again if time and site procedures allow). The results of the analysis of the changes and the original data is used to prepare a final report with the actions suggested, actions taken, the results of the actions and the list of recommendations that still need to be implemented. Usually we include a whitepaper or two about the various techniques we may recommend.

We never go onsite and start issuing recommendations without analysis. The last time I saw this technique used was from Oracle support three days ago. Without looking at detailed statistics or analysis they suggested increasing the shared pool (even though all of it wasn't being used), increasing the database cache size (even though 30% of the buffers were free in V$BH), turn off MTS (we had already tried this and it made no difference) and several other Oracle "Silver Bullets", none of which made the slightest difference in the problem. It turned out the problem was that 10g has a default value of TRUE for the parameter _B_TREE_BITMAP_PLANS which, even though the application had been running for months, suddenly started making a difference. Note there were no bitmaps in the database other than those that Oracle had in their schemas. They offered no proofs, no explanations, just a sigh of relief that this Oracle Silver Bullet fixed the problem.

This is a classic example of why "simple", "repeatable" proofs can be hazardous. By all of the indications there was nothing wrong at the database level. No waits or events showed the problem, even plans for the SQL appaerently didn't change, since it was a systemic problem (any SQL causes the issue not just specific SQL) we didn't check plans before the fix was provided by Oracle support. Any SQL activity resulted in CPU spiking of up to 70-80%. However, the setting of this undocumented parameter had a profound effect on the optimizer and overall database performance. If an Oracle analyst hadn't "seen this before" and remembered this "Silver Bullet" the client would still be having problems. The troubled queries dropped form taking several minutes to 6 seconds.

When going from 7 to 8 to 8i to 9i to 10g Oracle has been notorious for resetting their undocumented parameters from true to false, false to true, adding new undocumented and documented parameters and of course varying the various number based parameters to get the right "mix". You have to understand this in order to trouble shoot Oracle performance problems. Even within the same release some of these change based on platform.

This is the point I am trying to make about single user, small database proofs, they are great for documenting Oracle behavior on single user, small databases. They would have been completely useless in this case.

Unless the "test" environment reached whatever trigger size caused the parameter to take precedence you could type in various proofs until your fingers were bloody and they would not have helped one iota.

In order to fully perceive a problem you have to look at it carefully, from both an atomic level and a holistic level or you will miss the tru solution in many cases. In others, you may know the solution (the application needs to be re-written) but it may be impossible to implement, either because of time or resource limits or because of support limits from vendors. In this case you must be prepared to treat symptoms to allow the database to function at an acceptable level.

That is the long and short of it.

In a Galaxy Far, Far Away...

I read with amusement many of the “experts” posts, both as comments here and at many other websites. In my mind I imagine a conversation between these experts and my typical client (note, SATIRE SWITCH ON (I have never been present at a conversation between these experts and a client))

Client: Welcome to Acme! We have been looking forward to your visit! We really have some problems we want help with!

Expert: Good, as you know I am an expert and am here to help.

Client: Ok, where do you want to setup? Do you need connection to the database?

Expert: The CIO office would be nice.

Client: Well, errr, we have this cubical we set up for you.

(Looking down their nose) Expert: If I must.

(Looking relieved) Client: Great, it is right over here.

Client: How do you want to start?

Expert: I will have to look at all you data, its relationships to each other and how you use it.

(Looking concerned)Client: I’ll have to get that approved. How long do you think it will take?

Expert: Not longer than 2-3 weeks tops.

Client: 2-3 Weeks! At your rate that’s (getting out calculator) $54,000.00!

Expert: I am an expert and work directly for XXX. Is there a problem? (looking intimidating)

Client: Well, we had hoped to get it sooner, I will see about getting approval.

Expert: After that I will need to study the physical relationships of your entire database, how all the indexes are built, how all the tables are configured, and do root cause analysis of all waits and events.

(Looking impressed) Client: Wow! How long will that take?

Expert: Oh, not long, maybe another 3 weeks if I work overtime.

(Looking really concerned now)Client: That would mean more than $108,000.00…

Expert: Do you want this database to perform or not?

Client: Of course we want it to perform, that is why we brought you in. There may be some issues though…

Expert: How so?

Client: According to HIPPA and SOX, we can’t grant you access to our data and we are running a third-part application and can’t touch the code.

(Picking up laptop case) Expert: Sorry, I can’t help you, what do you think I am? Burleson Consulting? I can’t be expected to tune without changing the code, I don’t believe in silver-bullets that can be applied globally and if I don’t completely understand your data, structures, indexes and tables I simply can’t make any recommendations. You’ll get my bill.

Client: What was that name you mentioned?

(SATIRE SWITCH OFF)

Rumor Control

It seems numerous rumors and comments are being posted in the comments sections about things I may have once said, but upon research found were incorrect and retracted. Let me reiterate:

1. I have not found a case were an index rebuild, without re-arrangement of columns or change to the index structure, changes the clustering factor. Any prior advice I gave (nearly 2 years ago or so) to the contrary is incorrect.

2. I completely understand that the multiple block size feature in Oracle has limitations. I recommend them where the limitations are not a factor. That I haven't written a multi-page report on this fact with several more pages of complex proof, simply to prove what others have already done and is well documented in Oracle documentation I apologize for, I simply don't have the time to waste reinventing wheels.

3. The simple rules I mentioned in previous posts referred to the rules for database physical, PL/SQL and SQL design that are put forth in many of the Oak Table books, Oracle Press books and Rampant Books. Unfortunately, people don't follow these rules, the rules were made after the applications were designed, the people bought third party solutions that never follow any rules (good or bad), the people can't/won't change the code or a myriad set of other reasons that the good practices are not put in place. This is the real-world I dwell in and have to deal with.

4. I have never claimed that my advice on rebuilding indexes only applied to bitmaps, never have, never will. There are several situations where rebuilding or coalescing of indexes is beneficial and when I see a need for it, I will recommend it.

5. Why have I not attacked Don Burleson. Well, why have Howard and Tom never attacked Oracle for the many stupid things Oracle has done in the design and implementation of the Oracle product? For Oracle’s handling of support issues? For Oracle supports use of their own set of silver bullets? Also, Don has not attacked me. I am not confrontational by nature, I tend to let others have their opinions. When I can, I take Don to task in private for things which may be inaccurate, it is up to Don to change if he agrees with me. If I don't agree with everything Don advises, well, that is my opinion. It is rather like the TV, if you don't like what is being said, switch to another channel.

6. As to my opinions about asktom, yes, there are many good things there, there are also bad things as with any public forum. My major problem is that while it is supported by Oracle Corp. and supposedly moderated, I see things there that violate Oracles rules of conduct and should not be allowed on a moderated site. The examples are in the article, you can go to those links and see for yourself. Tom is excellent at providing proofs for his environment. But he rarely states "This proof should only be taken for proof in a similar environment to mine, your mileage may differ" and the very neofites you fear Don's and my advice will mislead, will be just as easily misled by correct proofs for an improper environment.

I attempt to avoid name-calling, bashing of others, and in general clouding issues with emotion charged clap-trap. In specific cases I have fought back when attacked by others and will continue to do so.

Oracle users tend to be a vocal lot. If advice causes problems it is generally well shouted from the roof tops. I task you Howard in presenting these shouts, if you cannot then you are but another chicken little proclaiming the sky is falling.

Any advice, when used incorrectly or without consideration is bad advice. Anyone who blindly follows suggestions, no matter what the source, without testing it in a test environment, deserves what they get.

If an automotive expert suggested I put water in my gas tank, I would question that advice, not blindly follow it. Compared to virtually all auto experts I am a neophyte in that area but I know not to blindly follow advice. When I go to a Doctor, an area I am definitely not trained in, if the advice sounds absurd I go to a second Doctor for their advice. There is no shortage of advice on Oracle, a quick check of Metalink, OTN and Google will find a plethora of opinions, demos, and advice on virtually any facet of Oracle administration you can imagine.

As I have said before, I have real work to do, I need to consolidate a list of 32 findings into something that can be accomplished with maximum return in 5 days. Not 2 weeks, not a month of study and testing, 5 days. Again, this is the real world.

Lack of Compassion?

I recently read an article in Utne magazine (online at http://www.utne.com/) entitles "Dying for Lobster" that, as a diver left me horrified. For those of you who aren't divers, when you dive and breath air under pressure, more than normal amounts of nitrogen are forced into your blood, as long as you dive within proper limits and follow proper safety, this nitrogen comes back out without causing problems. However, if you push the limits, don't follow safety rules (for example, staying down to long and ascending to fast) then the nitrogen comes out too fast, like bubbles of gas in a soda can. The gas is likely to come out at joints and as a rash at the surface of the skin. Oh, it also comes out in the brain and spinal cord, causing stroke like symptoms and paralysis. This is called decompression illness, you may have heard it called "the bends." If you are rushed to a decompression chamber the symptoms in many cases can be reversed, although there can be residual side effects.

Anyway, the article talked about Nicaraguan divers who dive for lobster from lobster company boats. These divers dive with poor equipment, no depth or pressure gage, no dive computers. They may dive 15 or more times a day to depths of 120 feet or more. I am sure they are encourged to bring up as many lobsters as they can each day. This means they ignore bottom time limits (if they are even taught them) and ignore safe ascent rates (again if they are even taght them.) As a result many of them "get bent" and unfortunately, since these boats are out for 2-3 weeks, don't get the benefit of getting taken to a deco chamber until it is too late. This results in many being crippled for life and many die.

On a Scuba board I suggested setting up a donation center for used dive equipment that is still servicable. Most of this equipment moulders away in closets, or is sold on ebay for a fraction of its worth. Since there is already a tax-exempt group set up to provide deco chambers for the divers I thought we could probably set up with them to provide tax receipts (at least to USA divers) for donated equipment that they could then use for reducing their taxes. The Nicaraguan divers win, we win. We would also provide training manuals and training to help these divers understand how to avoid the injuries from bends and how to use the more complex dive computers.

I understand that this is not correcting the problem, the exploitation of the workers and a government that doesn't care, but we really can't do much about that. We can make these divers safe divers and ease or prevent their suffering. To a response, all responses on the Scuba board were negative. No one saw the benefit of helping the divers, no one offered spare equipment.

Several of the more vocal respondents were from Canada, and I thought Canadians were a warm, friendly people who wanted to help others. Most were from the USA. I thought there would be more compassion for the plight of these divers, I thought there would be more generosity. I was wrong.

One diver said Red Lobster was the number one purchaser of the lobsters, I haven't confirmed that, nor do I know how to confirm it, if someone could I would appreciate it. Anyway, would a boycott help? The main reason the divers there dive is for the money. For 2-3 weeks of 15 dives a day and risking their health and lives they make $300.00 and room an board. Now for their economy this is good money, right up to the point where it kills them. In a boycott, if it was 100% successful, would it help these divers? It might eliminate their livelihood and throw them back into abject poverty, I am sure they would thank us. Through education and the provision of better equipment we would improve the quality of their lives, and as a side effect, reduce the pressure on the lobster population as limiting bottom time would reduce the number taken.

I hate to think the divers may have been motivated by the thought that they could get more money for the used equipment by selling it rather than donating it. I also hate to think they are so naive as to think we could change the government there and make it more compassionate (haven't we spent billions to do that with no visible affect?.) I also hate to think they care more about lobsters than they do men.

Stray Thoughts

It is amazing the things that come to mind just as you are drifting off to sleep. Old memories, new thoughts, the answer to that annoying question. I guess it shows that the mind is continuously at work processing things that we see and hear, sometimes long after we have stopped thinking about them on the surface.

Last night for example religion for some reason was paramount in my mind as I started to drift off. I remembered such adages as "God helps them that help themselves", and the parable of the servants and the money which goes something like "A master gave three servants each some money and instructed them to take care of it while he was gone. One went out and invested it, another used it to buy grain and plant it, while the third buried it. Upon the masters return each was called into accounting for the money they had been given, the first gave back two fold, the second was unable to give back crops because of a blight, the third gave back exactly what he was given. The master praised the two who had invested and put the money to work and rewarded them, even though the second had lost the money, the third, who only hid the money to return the exact amount, was punished." forgive any paraphrasing...

It seems to me that many of the fundamentalist beliefs are rather like the third servant. They take the coin of heaven and attempt to return it without changing it, improvement or loss. They feel that to question, challenge or add to the amount given (I am not talking about how many you add to the numbers of the church, I am talking about knowledge) is in some way forbidden, as if the holy book sprung, not from the labor of hundreds of people over thousands of years, but whole and perfect from the hand of God. When there are errors in translation, errors in usage, errors in understanding, they accept this all as whole cloth, not weaving more, not patching the holes, and not washing the stains away.

This happens of course not only in religion, but in life as well. In technical areas we have our own version of the GI-GO syndrome (garbage in - garbage out) known as garbage-in-gospel-out (I talked about this in an article back in the late 80's), that portends if it issues from a computer it must be gospel. In law we base everything on precedents, as if those in the past were smarter, more enlightened and knew more about the current situation than we do.

I believe that each person has the responsibility to learn as much as they can. We are all our own product. Many times people have accused me of being, shall I say eclectic? To which a say thanks. Having read at last estimate over 3000 or so books in my lifetime (give or take 100 or so) and hoping to read at least another 3000 before I pass into my reward, I find it gives me quite a platform on which to stand while I reach for more. Reach for more understanding. more knowledge and more insight that is. My reading reaches from technical and religious books to science fiction, historical fiction and the occasional horror or action adventure. Each book has something to teach, if you limit your reading on any topic to just one author, your horizon will be limited.

In a recent conversation someone dear to me expressed horror that I would read certain books by authors who disagreed with that persons basic belief structure. Words are only given power by the reader. I read what gives me more understanding, for it is only in understanding something can we elevate or overcome it.

Well, enough rambling for one day, there is work to be done!

Be Careful In What You Prove

I have recently been researching scuba diving sites within the USA for a possible series of articles for a scuba magazine. Believe it or not, I do have interests outside of Oracle! One of the sites, Rock Lake, Wisconsin is of particular interest for what lies beneath its turgid waters. But if I reveal too much now it might dilute the future article. What it has brought to light is the fact that you must be careful not only in what you believe to be true but in what you prove to be true. The tales of what lay beneath Rock Lake vary from lost Atlantis to only legends. I am sure if the two camps were brought together, bloodshed would ensue as each defended their point of view. To each of the camps, what they believe is the truth.

Many people believe that truth is immutable, that what is true now is always true. However, times change, technology changes, even (especially) theology. It was not so long ago that respected scientists believed that a Doctor washing his hands was not professional, that travelling at speeds in excess of 30 miles per hour would do irreperable damage to the human frame, that travelling faster than the speed of sound would result in a crash much like hitting a brick wall, or that setting off an atomic bomb would ignite the atmosphere and annilalate mankind.

Of course all of the above has been proven incorrect. Now there are those that propose that the charished notion that the speed of light is absolute is also a myth that will be dispelled with time (believe it or not, the "warp" drive of Star Trek fame has a basis in theoretical physics, of course it requires more energy than we can currently produce, but in medevil times wouldn't all of our current technology give us a one way ticket to the witches bonfire? ) however, as much energy as we can currently produce is perhaps a far cry from as much energy as we can produce. Some go as far as to claim that some super-novas are proof that some technologies lost control of zero-point energy.

So also must we view "proofs" given of things that are acceptable incurrent technology, in Oracle and in other technologies, as only transient in nature. Watch out that the "proofs" of today don't become tomorrows old wives tales.

For example, it wasn't too long ago that separation of indexes and tables in databases was a good and accepted method for improving performance. Of course this was because otherwise they would be on the same disk platter if they weren't seperate and would conflict. Now of course with the almost universal implementation of RAID techology (rendundent arrays of independent/inexpensive disks) this becomes unneeded (sometimes, depending on the amount of data stored on each disk in the array). It could be demonstrated, when they shared a single disk, that moving indexes away from tables improved performance. Based on that proof, no longer valid, some will say that moving indexes away from tables always improves performance.

Likewise the old saw that moving to RAW devices in UNIX (raw meaning the application was responsible for IO) would result in large improvements in performance. This of course was based on the premise that all of the buffering in the OS for non-raw filesystems caused delays. Now modern file systems can be set to eliminate this buffering and journalling delays and the performance gains from raw have deminished to nearly null. Of course rebuilding the objects within the databases involved, (tables and indexes) restoring them to proper parameters that they may have exceeded through years of neglect also helped.

So now we have a new crop of experts providing proofs (sometimes limited to a single-user, small database on a laptop) that their methods are the best and no doubt their proofs will be sited long after they are useful or meaningful and their expert advice will fade into old DBA tales as new technologies and methods become the rage. This is as it should be.

I guess I am trying to say, in a rambling way, that todays sage advice becomes tomorrows old tale. We must all be aware of what the current methods are, realize when the old methods no longer apply, and gracefully accept new ways if we are to grow and prosper. However, we must also recognize when the "proper" method evolves and mutates into a "new" method, leaving the old ways to die away.

So be careful in what you prove and how you prove it. What seems clever today may come back to haunt you. You may be aplogizing several years down the road for what is right now, but not in the future. The net is an amazing place. No doubt you can find numerous places where I may have exponded on the virtues of separation of indexes and tables, on rebuilding indexes frequently or other items that have since been proved, for current versions, old DBA tales. I like others before, am not immune to time and neither is my advice.

If any paper you read is older than a year or two, I suggest you take its advice with a big grain of salt as it may be applicable only to history and not current events.

Critics love to dig up old papers, presentations and advice given and use it to bludgeon people into believing they are the only authority. Take this type of advice with a grain of salt as well. In time, their advice will be referenced, out of date and out of style.

Call me Abused

Just finished doing a RAC class on Oracle9i RAC. During the class we installed Oracle RAC. Let me tell you, installing RAC on Oracle9i, version 9.2.0.6 is not the funnest thing I have done in a long time. Feel like a horse that was rode hard and put down wet. When I first started working with 9i and RAC the install was a bit convoluted. However, it has gotten much worse! Between missing directories, missing links, needing to install the universal installer, having the universal installer mess up the JRE, well, you ge the picture.

Compared to this "terminal" 9i release, 10g RAC install is a snap. But enough about that. Some comments on classrooms. Always teach from the front, it allows eye contact and allows you to see the expressions on the classes faces. I have had to teach three times from the back of a room, in each case I left feeling that I didn't do a good job, usually, so did the class. Eye contact and being able to see if the class understands key points is critical to good teaching.

Also, if the class uses software, such as RAC, make sure it is up and running before the class begins (unless the major part of the class is the install). The other times I have felt that the class got less from the class then they should have when I had to wrestle with a balky install. No matter why the software doesn't install, be it poor documentation, bugs or just bad karma, this will rub off on the classes opinion of your expertise.

A final observation, try to reduce any powerpoint slides to just talking points and needed diagrams. If you have a whiteboard, use it. Classes like the hands-on approach. As tempting as it is sometimes, especially when facing outside issues such as a balky install, don't just read the slides! I find myself falling into the trap when I have not had sufficient time to prepare for the class (it usually takes 4-6 hours per hour of class time to prepare materials.) Usually this means I put too much on the slides. By just putting bullet points for the topics on the slides, you can avoid this trap.

Well, that is all for now, talk to you later.

It is Totally Amazing...

I will begin blogging with a few thoughts on the time and energy other "experts" put into questioning everything said. I find it amazing the folks such as Howard J. Rogers can spend so much time and energy beating dead topics to death. Most of us have moved beyond arguments about index rebuilding and coalescing. I suggest them when it is indicated that it will help, it is one tool in a tuning experts arsenal. When I suggest a rebuild or a coalesce of an index in Oracle database systems it is only after a complete analysis points to the index as a problem.

Business must be slow down under for Howard to have so much free time. I have to grab a few minutes between running for the plane, working with customers all across the country and trying to keep up with my remote monitoring and tuning committments to swat at this annoying gadfly.

As to the whole debate (well, at least from Howards viewpoint it is a debate, for me it is just a tool to use when indicated) about reordering a column order in a concatenated index, again, Howard and others seem to feel that once an index is created it is sacred and can't be touched. Obviously they have never worked with a poorly crafted, poorly indexed third-party or custom generated application, for that matter, they must have never worked with Oracle's own Applications. I have seen applications where a table had 29 indexes. If I followed Howards way of thinking each would be a sacred object that I shouldn't touch because obviously the need for each was carefully considered and it would bring down the entire application to change it. Hog wash.

It merely shows that Howard spends too much time with theory and not enough in real-world tuning situations. I have been at over 24 client sites in the last 6 months doing tuning, optimizing and database evaluations. I find things that work and apply them as needed. My job is to help an ailing database back to health and to improve peformance of client applications. If this means that I drop, rebuild or otherwise change an index, then that is what it means. Heaven forbid, I even suggest adding an index occasionally!