Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2006
    Posts
    43

    Question Unanswered: Query optimization

    Hi guys,

    We're trying to optimize a query that's taking forever to run:

    select
    sku.sku_id skuId, u.id udacId, u.code udacCode, d.dir_id, d.dir_ypg_no, d.dir_ypa_no, h.heading_id, h.code headingCode, sku.validity_start_date, sku.validity_end_date,
    acts.type actType, acts.active actActive, act.code reservCode, acts.expiration_date, act.effective_start_date, acts.status, act.account_number, acts.rec_updt_userid
    from stock_keeping_unit sku
    inner join ad_directory d on (sku.directory_id = d.dir_id)
    inner join ad_heading h on (sku.heading_id = h.heading_id)
    inner join udac u on (sku.udac_id = u.id)
    left outer join sku_activity skuact on (sku.sku_id = skuact.sku_id)
    left outer join activity act on (skuact.activity_id = act.id)
    left outer join activity_status acts on (act.id = acts.activity_id)
    where rownum >= ? and rownum <= ?
    order by u.code, d.dir_ypg_no, h.code, sku.validity_start_date, sku.validity_end_date, sku.sku_id, act.id, acts.id, acts.rec_updt_dt;

    where:

    . stock_keeping_unit has 10 million rows
    . ad_directory has 2,000 rows
    . ad_heading has 42,000 rows
    . udac has 18,000 rows
    . sku_activity has 1,000 rows
    . activity has 5,000 rows
    . activity_status has 15,000 rows

    Running an explain plan in SQL Developer shows that query cost is over 17,000; full table scan on the stock_keeping_unit table accounts 99% of that cost. Running the query takes around 200 seconds.

    Any tips on optimizing this?

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gbilodeau View Post

    Any tips on optimizing this?
    Yes. Don't select all 10 million rows every time.
    ---
    "It does not work" is not a valid problem statement.

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

    Code:
    SELECT   sku.sku_id  skuid,
             u.ID        udacid,
             u.code      udaccode,
             d.dir_id,
             d.dir_ypg_no,
             d.dir_ypa_no,
             h.heading_id,
             h.code      headingcode,
             sku.validity_start_date,
             sku.validity_end_date,
             acts.TYPE   acttype,
             acts.active actactive,
             act.code    reservcode,
             acts.expiration_date,
             act.effective_start_date,
             acts.status,
             act.account_number,
             acts.rec_updt_userid
    FROM     stock_keeping_unit sku
             INNER JOIN ad_directory d
               ON (sku.directory_id = d.dir_id)
             INNER JOIN ad_heading h
               ON (sku.heading_id = h.heading_id)
             INNER JOIN udac u
               ON (sku.udac_id = u.ID)
             LEFT OUTER JOIN sku_activity skuact
               ON (sku.sku_id = skuact.sku_id)
             LEFT OUTER JOIN activity act
               ON (skuact.activity_id = act.ID)
             LEFT OUTER JOIN activity_status acts
               ON (act.ID = acts.activity_id)
    WHERE    ROWNUM >= ?
             AND ROWNUM <= ?
    ORDER BY u.code,
             d.dir_ypg_no,
             h.code,
             sku.validity_start_date,
             sku.validity_end_date,
             sku.sku_id,
             act.ID,
             acts.ID,
             acts.rec_updt_dt;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2006
    Posts
    43
    Sorry about the code tags. The query is actually as follows:

    Code:
    SELECT *
    FROM
      (SELECT *
      FROM
        ( SELECT DISTINCT sku.SKU_ID sku_SKU_ID,
          sku.VALIDITY_START_DATE sku_VALIDITY_START_DATE,
          udac.CODE udac_CODE,
          dir.DIR_YPG_NO dir_DIR_YPG_NO,
          dir.DIR_YPA_NO dir_DIR_YPA_NO,
          dir.DIR_ENNAME dir_DIR_ENNAME,
          dir.DIR_FRNAME dir_DIR_FRNAME,
          dir.PROVINCE_CODE dir_PROVINCE_CODE,
          heading.CODE heading_CODE,
          heading.HEADING_ENDESC heading_HEADING_ENDESC,
          heading.HEADING_SHORT_ENDESC heading_HEADING_SHORT_ENDESC,
          heading.HEADING_FRDESC heading_HEADING_FRDESC,
          heading.HEADING_SHORT_FRDESC heading_HEADING_SHORT_FRDESC,
          dir.ISSUE_DATE dir_ISSUE_DATE,
          sku.VALIDITY_END_DATE sku_VALIDITY_END_DATE,
          sku.REC_UPDT_USERID sku_REC_UPDT_USERID,
          reserve.reserveCode reserveCode,
          reserve.lastChanged reserveLastChanged,
          reserve.lastChangedDate reserveLastChangedDate,
          sale.saleCode saleCode,
          sale.lastChanged saleLastChanged,
          sale.lastChangedDate saleLastChangedDate,
          CASE
            WHEN (sale.statusSale = 4)
            THEN 2
            WHEN (reserve.expirationDate > ?
            AND (reserve.statusReserve   = 1
            OR reserve.statusReserve     = 2))
            THEN 1
            ELSE 0
          END status,
          CASE
            WHEN reserve.statusReserve IS NULL
            THEN ''
            WHEN sale.statusSale IS NULL
            THEN reserve.lastChanged
            WHEN reserve.lastChanged > sale.lastChanged
            THEN reserve.lastChanged
            ELSE sale.lastChanged
          END statusLastChanged,
          CASE
            WHEN (sale.statusSale = 4)
            THEN reserve.expirationDate
            WHEN (reserve.expirationDate > ?
            AND (reserve.statusReserve   = 1
            OR reserve.statusReserve     = 2))
            THEN reserve.expirationDate
            ELSE NULL
          END expirationDate,
          CASE
            WHEN (sale.statusSale = 4)
            THEN sale.saleEffectiveStartDate
            WHEN (reserve.expirationDate > ?
            AND (reserve.statusReserve   = 1
            OR reserve.statusReserve     = 2))
            THEN reserve.reserveEffectiveStartDate
            ELSE NULL
          END effectiveStartDate,
          CASE
            WHEN (sale.statusSale = 4)
            THEN sku.VALIDITY_END_DATE
            WHEN (reserve.expirationDate > ?
            AND (reserve.statusReserve   = 1
            OR reserve.statusReserve     = 2))
            THEN sku.VALIDITY_END_DATE
            ELSE NULL
          END effectiveEndDate,
          CASE
            WHEN (sale.statusSale = 4)
            THEN (sku.VALIDITY_END_DATE - sale.saleEffectiveStartDate)
            WHEN (reserve.expirationDate > ?
            AND (reserve.statusReserve   = 1
            OR reserve.statusReserve     = 2))
            THEN (sku.VALIDITY_END_DATE - reserve.reserveEffectiveStartDate)
            ELSE                        -1
          END effectivePeriodDuration
        FROM STOCK_KEEPING_UNIT sku
        INNER JOIN AD_DIRECTORY dir
        ON sku.Directory_ID = dir.DIR_ID
        INNER JOIN AD_HEADING heading
        ON sku.HEADING_ID = heading.HEADING_ID
        INNER JOIN UDAC udac
        ON sku.UDAC_ID = udac.ID
        LEFT OUTER JOIN
          ( SELECT DISTINCT sku_id,
            as_reserve.status                     AS statusReserve,
            as_reserve.expiration_date            AS expirationDate,
            as_reserve.rec_updt_userid            AS lastChanged,
            as_reserve.rec_updt_dt                AS lastChangedDate,
            activity_reserve.code                 AS reserveCode,
            activity_reserve.effective_start_date AS reserveEffectiveStartDate
          FROM SKU_ACTIVITY skuAC_reserve
          LEFT OUTER JOIN ACTIVITY activity_reserve
          ON (activity_reserve.ID = skuAC_reserve.ACTIVITY_ID)
          LEFT OUTER JOIN ACTIVITY_STATUS as_reserve
          ON (as_reserve.ACTIVITY_ID  = activity_reserve.ID
          AND as_reserve.type         = 0)
          WHERE as_reserve.ACTIVE     = 1
          ) reserve ON reserve.sku_id = sku.SKU_ID
        LEFT OUTER JOIN
          ( SELECT DISTINCT sku_id,
            as_sale.status                     AS statusSale,
            as_sale.expiration_date            AS expirationDate,
            as_sale.rec_updt_userid            AS lastChanged,
            as_sale.rec_updt_dt                AS lastChangedDate,
            activity_sale.code                 AS saleCode,
            activity_sale.effective_start_date AS saleEffectiveStartDate
          FROM SKU_ACTIVITY skuAC_sale
          LEFT OUTER JOIN ACTIVITY activity_sale
          ON (activity_sale.ID = skuAC_sale.ACTIVITY_ID)
          LEFT OUTER JOIN ACTIVITY_STATUS as_sale
          ON (as_sale.ACTIVITY_ID = activity_sale.ID
          AND as_sale.type        = 1)
          WHERE as_sale.ACTIVE    = 1
          ) sale ON sale.sku_id   = sku.sku_id
        ) searchQuery
      WHERE searchQuery.sku_VALIDITY_START_DATE >= ?
      AND searchQuery.sku_VALIDITY_START_DATE   <= ?
      AND ( STATUS                               = 0
      OR STATUS                                  = 1
      OR STATUS                                  = 2 )
      ORDER BY searchQuery.dir_ISSUE_DATE ,
        searchQuery.dir_DIR_YPG_NO ,
        searchQuery.dir_DIR_ENNAME ,
        searchQuery.heading_CODE ,
        searchQuery.heading_HEADING_ENDESC ,
        searchQuery.udac_CODE ,
        searchQuery.status
      )
    WHERE rownum <= ?
    Query takes ~75 seconds to run. Explain plan is:

    Code:
     
    -------------------------------------------------------------------------------------------------
    | Id  | Operation                                 |  Name               | Rows  | Bytes | Cost  |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                     |     1 |   681 |   823 |
    |*  1 |  COUNT STOPKEY                            |                     |       |       |       |
    |   2 |   VIEW                                    |                     |     1 |   681 |   823 |
    |*  3 |    SORT ORDER BY STOPKEY                  |                     |     1 |   681 |   823 |
    |   4 |     VIEW                                  |                     |     1 |   681 |   822 |
    |   5 |      SORT UNIQUE                          |                     |     1 |   493 |   822 |
    |*  6 |       FILTER                              |                     |       |       |       |
    |*  7 |        HASH JOIN OUTER                    |                     |       |       |       |
    |*  8 |         HASH JOIN OUTER                   |                     |     1 |   336 |   815 |
    |   9 |          NESTED LOOPS                     |                     |     1 |   188 |   809 |
    |  10 |           NESTED LOOPS                    |                     |     1 |   144 |   809 |
    |  11 |            NESTED LOOPS                   |                     |     1 |    60 |   809 |
    |* 12 |             TABLE ACCESS FULL             | STOCK_KEEPING_UNIT  |   810K|    37M|   809 |
    |  13 |             TABLE ACCESS BY INDEX ROWID   | UDAC                |     1 |    11 |       |
    |* 14 |              INDEX UNIQUE SCAN            | UDAC                |     1 |       |       |
    |  15 |            TABLE ACCESS BY INDEX ROWID    | AD_HEADING          |     1 |    84 |       |
    |* 16 |             INDEX UNIQUE SCAN             | AD_HEADING          |     1 |       |       |
    |  17 |           TABLE ACCESS BY INDEX ROWID     | AD_DIRECTORY        |     1 |    44 |       |
    |* 18 |            INDEX UNIQUE SCAN              | AD_DIRECTORY        |     1 |       |       |
    |  19 |          VIEW                             |                     |     1 |   148 |     5 |
    |  20 |           SORT UNIQUE                     |                     |     1 |   222 |     5 |
    |* 21 |            FILTER                         |                     |       |       |       |
    |  22 |             NESTED LOOPS OUTER            |                     |       |       |       |
    |  23 |              MERGE JOIN CARTESIAN         |                     |     1 |   148 |     2 |
    |  24 |               TABLE ACCESS FULL           | SKU_ACTIVITY        |   164 |  4264 |     2 |
    |  25 |               BUFFER SORT                 |                     |     1 |   122 |       |
    |  26 |                TABLE ACCESS BY INDEX ROWID| ACTIVITY_STATUS     |     1 |   122 |       |
    |* 27 |                 INDEX RANGE SCAN          | IDX_AST_TYPE        |     1 |       |       |
    |  28 |              TABLE ACCESS BY INDEX ROWID  | ACTIVITY            |     1 |    74 |     1 |
    |* 29 |               INDEX UNIQUE SCAN           | ACTIVITY            |     1 |       |       |
    |  30 |         VIEW                              |                     |     1 |   157 |     5 |
    |  31 |          SORT UNIQUE                      |                     |     1 |   222 |     5 |
    |* 32 |           FILTER                          |                     |       |       |       |
    |  33 |            NESTED LOOPS OUTER             |                     |       |       |       |
    |  34 |             MERGE JOIN CARTESIAN          |                     |     1 |   148 |     2 |
    |  35 |              TABLE ACCESS FULL            | SKU_ACTIVITY        |   164 |  4264 |     2 |
    |  36 |              BUFFER SORT                  |                     |     1 |   122 |       |
    |  37 |               TABLE ACCESS BY INDEX ROWID | ACTIVITY_STATUS     |     1 |   122 |       |
    |* 38 |                INDEX RANGE SCAN           | IDX_AST_TYPE        |     1 |       |       |
    |  39 |             TABLE ACCESS BY INDEX ROWID   | ACTIVITY            |     1 |    74 |     1 |
    |* 40 |              INDEX UNIQUE SCAN            | ACTIVITY            |     1 |       |       |
    Thanks,
    GB
    Last edited by gbilodeau; 03-15-10 at 15:26.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    trim out as much SKU data as you can.
    put your search query for the SKU table at the source.

    so this:
    Code:
      WHERE searchQuery.sku_VALIDITY_START_DATE >= ?
      AND searchQuery.sku_VALIDITY_START_DATE   <= ?
    should be somewhere close to the inner join like so:
    Code:
        FROM STOCK_KEEPING_UNIT sku
      WHERE sku.VALIDITY_START_DATE >= ?
      AND sku.VALIDITY_START_DATE   <= ?
    ) ... [rest of outer joins]
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Dec 2006
    Posts
    43
    Thank you all for your ideas. We settled on denormalizing some data, duplicating some data in the STOCK_KEEPING_UNIT table so that the final query only uses this table.

    The funny thing, in our development environment (Oracle 10g) this query now runs in under 0,03 seconds, but in our QA environment (Oracle 9i) this query runs in just under 30 seconds. Granted, the dev table contains 3.5 million records while the QA table contains 5.1 million records, but this surely does not warrant an execution time that is 1000x higher.

    Query now goes like this:

    Code:
    SELECT * FROM ( 
    SELECT DISTINCT 
    sku.sku_id sku_SKU_ID, 
    sku.validity_start_date sku_VALIDITY_START_DATE, 
    sku.c_udac_code udac_CODE, 
    sku.c_dir_ypg_no dir_DIR_YPG_NO, 
    sku.c_dir_ypa_no dir_DIR_YPA_NO, 
    sku.c_dir_ypg_en_name dir_DIR_ENNAME, 
    sku.c_dir_ypg_fr_name dir_DIR_FRNAME, 
    sku.c_dir_province_code dir_PROVINCE_CODE, 
    sku.c_heading_code heading_CODE, 
    sku.c_heading_en_desc heading_HEADING_ENDESC, 
    sku.c_heading_en_desc_short heading_HEADING_SHORT_ENDESC, 
    sku.c_heading_fr_desc heading_HEADING_FRDESC, 
    sku.c_heading_fr_desc_short heading_HEADING_SHORT_FRDESC, 
    sku.c_dir_issue_date dir_ISSUE_DATE, 
    sku.validity_end_date sku_VALIDITY_END_DATE, 
    sku.rec_updt_userid sku_REC_UPDT_USERID, 
    sku.c_reserv_code reserveCode, 
    sku.c_reserv_last_updt_userid reserveLastChanged, 
    sku.c_reserv_last_updt_dt reserveLastChangedDate, 
    sku.c_sale_code saleCode, 
    sku.c_sale_last_updt_userid saleLastChanged, 
    sku.c_sale_last_updt_dt saleLastChangedDate, 
    CASE 
    WHEN (sku.c_sale_status = 4) THEN 2
    WHEN (sku.c_reserv_expiration_date > ?0 AND (sku.c_reserv_status = 1 OR sku.c_reserv_status = 2)) THEN 1
    ELSE 0
    END status,
    CASE 
    WHEN sku.c_reserv_status IS NULL THEN '' 
    WHEN sku.c_sale_status IS NULL THEN sku.c_reserv_last_updt_userid 
    WHEN sku.c_reserv_last_updt_dt > sku.c_sale_last_updt_dt THEN sku.c_reserv_last_updt_userid 
    ELSE sku.c_sale_last_updt_userid 
    END statusLastChanged, 
    CASE 
    WHEN (sku.c_sale_status = 4) THEN sku.c_reserv_expiration_date 
    WHEN (sku.c_reserv_expiration_date > ?0 AND (sku.c_reserv_status = 1 OR sku.c_reserv_status = 2)) THEN sku.c_reserv_expiration_date 
    ELSE NULL 
    END expirationDate, 
    CASE 
    WHEN (sku.c_sale_status = 4) THEN sku.c_sale_effective_start_date 
    WHEN (sku.c_reserv_expiration_date > ?0 AND (sku.c_reserv_status = 1 OR sku.c_reserv_status = 2)) THEN sku.c_reserv_effective_start_date 
    ELSE NULL 
    END effectiveStartDate, 
    CASE 
    WHEN (sku.c_sale_status = 4) THEN sku.validity_end_date 
    WHEN (sku.c_reserv_expiration_date > ?0 AND (sku.c_reserv_status = 1 OR sku.c_reserv_status = 2)) THEN sku.validity_end_date 
    ELSE NULL 
    END effectiveEndDate, 
    CASE 
    WHEN (sku.c_sale_status = 4) THEN (sku.VALIDITY_END_DATE - sku.c_sale_effective_start_date) 
    WHEN (sku.c_reserv_expiration_date > ?0 AND (sku.c_reserv_status = 1 OR sku.c_reserv_status = 2)) THEN (sku.validity_end_date - sku.c_reserv_effective_start_date) 
    ELSE -1 
    END effectivePeriodDuration 
    FROM 
    STOCK_KEEPING_UNIT sku 
    ) searchQuery
    WHERE rownum <= ?;
    Explain plan in dev is as follows:

    Code:
    -----------------------------------------------------------------------------------------
    | Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                    |    20 |  3700 |     2   (0)| 00:00:01 |
    |*  1 |  COUNT STOPKEY     |                    |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| STOCK_KEEPING_UNIT |    20 |  3700 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    Explain plan in QA is as follows:

    Code:
    -------------------------------------------------------------------------------------
    | Id  | Operation             |  Name               | Rows  | Bytes |TempSpc| Cost  |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                     |    20 | 13720 |       |   208K|
    |*  1 |  COUNT STOPKEY        |                     |       |       |       |       |
    |   2 |   VIEW                |                     |  5121K|  3350M|       |   208K|
    |*  3 |    SORT UNIQUE STOPKEY|                     |  5121K|   928M|  2462M|   208K|
    |   4 |     TABLE ACCESS FULL | STOCK_KEEPING_UNIT  |  5121K|   928M|       |  2600 |
    -------------------------------------------------------------------------------------
    The explain plans are completely different and I don't know why. Stats have been gathered in both environments.

    Thanks!

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When was the last time you gathered statistics in the Q/A database?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'm pretty sure your problem with the original query was due to turning a left outer join into an inner join after you have left joined it (see section in bold below). Seen this quite a few times over the years, try turning that LEFT OUTER to an INNER and I bet performance improves quite a bit.
    Dave

    Code:
       LEFT OUTER JOIN
          ( SELECT DISTINCT sku_id,
            as_sale.status                     AS statusSale,
            as_sale.expiration_date            AS expirationDate,
            as_sale.rec_updt_userid            AS lastChanged,
            as_sale.rec_updt_dt                AS lastChangedDate,
            activity_sale.code                 AS saleCode,
            activity_sale.effective_start_date AS saleEffectiveStartDate
          FROM SKU_ACTIVITY skuAC_sale
          LEFT OUTER JOIN ACTIVITY activity_sale
          ON (activity_sale.ID = skuAC_sale.ACTIVITY_ID)
          LEFT OUTER JOIN ACTIVITY_STATUS as_sale
          ON (as_sale.ACTIVITY_ID = activity_sale.ID
          AND as_sale.type        = 1)
          WHERE as_sale.ACTIVE    = 1
          ) sale ON sale.sku_id   = sku.sku_id
        ) searchQuery
      WHERE searchQuery.sku_VALIDITY_START_DATE >= ?
      AND searchQuery.sku_VALIDITY_START_DATE   <= ?

  9. #9
    Join Date
    Dec 2006
    Posts
    43
    Quote Originally Posted by beilstwh View Post
    When was the last time you gathered statistics in the Q/A database?
    As far as I know, I gathered stats 2 minutes before launching the query. This is what I executed:

    Code:
    execute dbms_stats.gather_table_stats('schema_name', 'stock_keeping_unit');
    The result is still the same, any idea why?

  10. #10
    Join Date
    Dec 2006
    Posts
    43
    The problem seems to be coming from the interpretation of the "select distinct" statement. The distinct isn't necessary so I've removed it, the query is now blazing fast. Woohoo!

  11. #11
    Join Date
    Dec 2006
    Posts
    43
    Ok, maybe I celebrated too quickly. After adding an ORDER BY clause, the query becomes 500x slower (17,5 s vs 0,03 s). I've tried playing with the "sort_area_size" parameter but it doesn't seem to be having any effect. This is running on Oracle 9i.

    The same query running on Oracle 10g takes 3 seconds.

    Any ideas how to improve performance with the ORDER BY clause?

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by gbilodeau View Post
    Ok, maybe I celebrated too quickly. After adding an ORDER BY clause, the query becomes 500x slower (17,5 s vs 0,03 s). I've tried playing with the "sort_area_size" parameter but it doesn't seem to be having any effect. This is running on Oracle 9i.
    Are you sure the query returns all rows in 0.03s and not only the first ones? The queries you provided differ between "TOP N" and "firstly fetched N" variants. The second is significantly faster, as it does not both fetch all rows nor sorts them. So, before trying another query modification, you could clearly specify the requirements.
    Quote Originally Posted by gbilodeau View Post
    The same query running on Oracle 10g takes 3 seconds.
    Still 100 times slower than the query without ORDER BY. Are you sure you have same data on both systems?
    Quote Originally Posted by gbilodeau View Post
    Any ideas how to improve performance with the ORDER BY clause?
    For the closer description of pagination queries, have a look at these threads on AskTom:
    http://asktom.oracle.com/pls/apex/f?...D:127412348064
    http://asktom.oracle.com/pls/apex/f?...ID:76812348057
    http://asktom.oracle.com/pls/apex/f?...77300346084930

  13. #13
    Join Date
    Dec 2006
    Posts
    43
    Quote Originally Posted by flyboy View Post
    Are you sure the query returns all rows in 0.03s and not only the first ones? The queries you provided differ between "TOP N" and "firstly fetched N" variants. The second is significantly faster, as it does not both fetch all rows nor sorts them. So, before trying another query modification, you could clearly specify the requirements.
    I'm trying to compare equal things. I run all queries using SQL Developer, so yes not all data is loaded at a given time, but this is the same for all queries. So the 17.5 seconds (with order by) vs 0.03 seconds (without order by) is calculated using the same method.

    Still 100 times slower than the query without ORDER BY. Are you sure you have same data on both systems?
    The 2 systems now use the same data sets, namely ~5.1 million records. Updated stats are now:

    Oracle 9i without order by: 0.03s
    Oracle 9i with order by: 17.5s
    Oracle 10g without order by: 0.02s
    Oracle 10g with order by: 4.5s

    Granted, the Oracle 10g database is more powerful and has more memory. Still, I'm trying to push the Oracle 9i configuration as hard as I can. Are there parameters other than sort_area_size that could have an impact on sort performance?

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    SQLDEVELOPER?!?!?!?! WTF?

    You can't verify anything using sqldeveloper.
    you mention the first rows sqldeveloper algorithm and you just dismiss it.

    If you want to measure anything then you run a full run through sqlplus and trace it.
    tkprof the trace and now you are looking at actuals.

    also, why even bother with comparing an inferior box and lower version of oracle against a higher version on a box with more resources?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Dec 2006
    Posts
    43
    We're getting the same results using SqlPlus - which makes sense, since the query is only returning the first 20 rows.

    Sorry, I meant to say that the Oracle 10g box has less resources than the 9i box. So this is even more baffling.

Posting Permissions

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