Re: Need ideas for "proof test"

On Thu, 26 Feb 2004 11:28:48 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>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,

Thanks for the code. This will give me a good framework to jump start several tests I'd like to try.

In the specific example you provided, I'm puzzled as to why there should be consistent performance preference of one over the other. Received on Fri Feb 27 2004 - 07:32:04 CST