Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    NW England
    Posts
    2

    Unanswered: Poor performance on join queries

    Hi,

    I'm having some problems with a join query that performs extremely badly. Syntax looks a little like this:

    Select a.column1, a.column2, ......, a.column5, b.column1, b.column2, ....., b.column5, c.column1, c.column2, ......, c.column5
    from table1 a, table2 b, table3 c
    where a.column1 = b.column3
    and b.column2 = c.column4

    I'm going to run through some basic checks tomorrow (i.e. are the joined fields indexed etc), but the problem is that the SQL itself is written to Oracle by a second application (Unica Affinium Campaign), so I have very little control over the syntax of the statement.

    Two quick questions for now:
    1) Is performance likely to be any better if the statement were written with an expicit INNER JOIN?
    2) Is there any benefit in writing an ORDERED hint with only three tables? (Oracle is configured to use CBO rather than RBO).

    Any help appreciated!

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Please post the EXPLAIN PLAN for the query

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Ensure that there are indeed indexes for what you are trying to join. Find the table and will limit the number of rows the quickest and use that as the DRIVING table. Ensure that your statistics are updated on table and indexes. Do the indexes have a true representation of the data or is the data skewed on one side of the index or the other? (If so, analyze the table for all indexed columns). Try setting autotrace on. alter session set optimizer_goal ... choose, first_row, all_rows, rule.... and see what you like better.... As mentioned above, post your explain plan with the number of rows from the tables and indexes that "should" be involved.

    Gregg

  4. #4
    Join Date
    Jan 2004
    Location
    NW England
    Posts
    2

    Re: Poor performance on join queries

    Execution Plan is below.

    It would appear there are NO indexes on the join fields(!) and one of them is a VARCHAR2 to VARCHAR2 join.

    Any other suggestions for improving?

    Thanks so far....


    Execution Plan:
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9133 Card=647084 Byt
    es=125534296)

    1 0 HASH JOIN* (Cost=9133 Card=647084 Bytes=125534296) :Q777505
    006

    2 1 HASH JOIN* (Cost=5471 Card=405190 Bytes=48217610) :Q777505
    005

    3 2 HASH JOIN* (Cost=4128 Card=760087 Bytes=52446003) :Q777505
    004

    4 3 HASH JOIN* (Cost=2342 Card=760087 Bytes=33443828) :Q777505
    002

    5 4 TABLE ACCESS* (FULL) OF 'TEST_TABLE' (Cost=54 Card :Q777505
    =748193 Bytes=10474702) 000

    6 4 PARTITION HASH* (ALL) :Q777505
    002

    7 6 TABLE ACCESS* (FULL) OF 'DM_STORECARD' (Cost=189 :Q777505
    2 Card=6825805 Bytes=204774150) 001

    8 3 TABLE ACCESS* (FULL) OF 'SM_CUSTOMER' (Cost=1617 Car :Q777505
    d=10395396 Bytes=259884900) 003

    9 2 PARTITION HASH* (ALL) :Q777505
    005

    10 9 TABLE ACCESS* (FULL) OF 'DM_CREDIT_CARD' (Cost=1222 :Q777505
    Card=2768465 Bytes=138423250) 005

    11 1 PARTITION HASH* (ALL) :Q777505
    006

    12 11 TABLE ACCESS* (FULL) OF 'DM_CUSTOMER_ADDRESS' (Cost=32 :Q777505
    83 Card=8292385 Bytes=621928875) 006



    1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
    A2.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5

    2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
    A1.C0 C0,A2.C3 C1,A1.C1 C2,A1.C2 C3

    3 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
    A1.C0 C0,A2.C2 C1,A2.C0 C2,A1.C1 C3

    4 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
    A2.C1 C0,A2.C0 C1,A2.C2 C2 FROM :Q7

    5 PARALLEL_FROM_SERIAL
    6 PARALLEL_COMBINED_WITH_PARENT
    7 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."SC_ACC
    OUNT_NUM" C0,A1."CLIENTID" C1,A1."SC

    8 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."CLIENT
    ID" C0,A1."OCCUPATIONCODE" C1,A1."DO

    9 PARALLEL_COMBINED_WITH_PARENT
    10 PARALLEL_COMBINED_WITH_PARENT
    11 PARALLEL_COMBINED_WITH_PARENT
    12 PARALLEL_COMBINED_WITH_PARENT

    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •