Re: Oracle Performance

Bill Roney (bill.roney_at_mailport.delta-air.com) wrote:
: I'm having a problem with a 7.2.3 server. It is running on an NCR system with 8 P133s and 3GB of
: physical memory (sorry, I don't know the model number) running AT&T UNIX.
 

: I have 2 tables that form a master detail relationship, the general structure of the tables are as
: follows:
 

: table T1
: (
: fielda char(5) not null,
: fieldb date not null,
: fieldc number not null,
: ...
: ) (row with is around 250 bytes)
: table T2
: (
: fielda char(5) not null,
: fieldb date not null,
: fieldc number not null,
: fieldd char(1) not null,
: ...
: ) (row with is around 150 bytes)
 

: The sizes of these tables are 2.5 million rows in T1, and 6 million rows in T2.
 

: These 2 tables are joined with the following where and order by clauses
: where T1.fielda = T2.fielda
: and T1.fieldb = T2.fieldb
: and T1.fieldc = T2.fieldc
: and T1.fieldb >= '[a start date]'
: and T1.fieldb <= '[an end date]'
: order by T2.fielda, T2.fieldb, T2.fieldc, T2.fieldd
 

: The result set returns 20 minutes later, with the sar report during the period reporting 0% idle
: time.
 

: I have a requirement that the result set be built < 5 minutes. Is this possible based upon anyone's
: experience? I have been told that the requirement is not possible.
 

: On a side note, selecting just the rows from T1 (no join) takes 3 minutes. This would imply that I
: may be able to perform the join on the client faster than the join being done by the RDBMS?!?
 

: Any help or insight would be greatly appreciated.
 

: Thanks in advance.
: Bill

Well, I wont go into any design issues... except to say that a single column join might be alot better...

Also, you should ensure that you have an index on ( fieldb, fieldc ) for the where clause, and one on ( fielda, fieldb ) for the order by. (you could also include c and d if these are sufficiently non-unique within fieldb.)

depending on which column (a b or c ) is the most unique, you might do better to place the most unique column last in the where clause. and possibly save yourself the b,c index above.

Are you using Cost or Rule based optimization? Have you run explain plan or something similar? tkprof? Do you have enough SGA? Are the tables on different disks? how about the rollbacks? I bet you can hit your numbers.

Randy :)

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I