Re: Need ideas for "proof test"

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 26 Feb 2004 11:28:48 -0800
Message-ID: <1077823690.467896@yasure>

Ed Stevens wrote:

> Once again we are having a "friendly discussion" within my workgroup
> over the performance impact of key positioning and row insert sequence
> and the efficacy of a db reorg. My workgroup consists of 2 DBAs that
> work on DB2, and myself and another Oracle DBA. I and the DB2 guys
> claim none of these things make any difference on performance. The
> other Oracle DBA is adamant that it is important (from a performance
> standpoint) where within a row a key column is placed, and that
> regular re-orgs improve performance by sequencing the rows in key
> order. We've talked till we're blue in the face, but this guy is
> adamant.
>
> I'd like to set up a test to settle this once and for all. I'm not
> asking anyone to do the work for me, but would appreciate some review
> of my plan.
>
> I think the easiest way to set it up would be to dump the table in
> question to a flat file, sort the file by a NON-key field (to
> introduce some randomness to the order), then sqlldr it into an empty
> table of the same structure.
>
> Next, run some unqalified SELECTS both with and without ORDER BY on
> the key field. Timing would be done by turning on a trace.
>
> Next, reorg the table by doing an export/import.
>
> Next, repeat the tests and timeing.
>
> Am I overlooking anything? I want to make sure the test plan and
> results stand up to scrutiny.
>
> And while I'm willing to do the work myself, if someone has some code
> or procedures laying around that they'd be willing to share . . .
> ;-)

CREATE TABLE pkfirst (
id NUMBER(10),

col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10));

CREATE TABLE pklast (

col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10),

id NUMBER(10));

ALTER TABLE pkfirst
ADD CONSTRAINT pk_pkfirst
PRIMARY KEY (id)
USING INDEX; ALTER TABLE pklast
ADD CONSTRAINT pk_pklast
PRIMARY KEY (id)
USING INDEX
TABLESPACE indx_sml;

BEGIN
   FOR i IN 1..200000
   LOOP

     INSERT INTO pkfirst
     VALUES
     (i, 'AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC', 'DDDDDDDDDD');

     INSERT INTO pklast
     VALUES
     ('AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC', 'DDDDDDDDDD', i);

   END LOOP;
   COMMIT;
END;
/

set serveroutput on

DECLARE
   i PLS_INTEGER;
   s pkfirst.col1%TYPE;
BEGIN
   i := dbms_utility.get_time();

   FOR i IN REVERSE 1 .. 200000
   LOOP

     SELECT col1
     INTO s
     FROM pkfirst
     WHERE id = i;

   END LOOP;    dbms_output.put_line('First: ' || TO_CHAR(dbms_utility.get_time()-i));

   i := dbms_utility.get_time();

   FOR i IN REVERSE 1 .. 200000
   LOOP

     SELECT col1
     INTO s
     FROM pklast
     WHERE id = i;

   END LOOP;    dbms_output.put_line('Last: ' || TO_CHAR(dbms_utility.get_time()-i)); END;
/

First: 3731
Last: 5366

The PK in the first column consistently performs better at both 20,000 and 200,000 rows.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Feb 26 2004 - 13:28:48 CST