title: How to improve performance in Oracle using SELECT DISTINCT
body: <p>I'm currently working in the deployment of an OFBiz based ERP
  The database being used is Oracle 10g Enterprise</p>
  
  <p>One of the biggest issues is some oracle performance problems, analyzing the ofbiz logs, the following query:</p>
  
  <pre><code>SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
   SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY, 
   FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID, 
   ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID, 
   AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE, 
   REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, 
  CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM 
  ERP.ORDER_HEADER WHERE ((STATUS_ID = :v0 OR STATUS_ID = :v1 OR STATUS_ID = :v2) AND 
  (ORDER_TYPE_ID = :v3)) ORDER BY ORDER_DATE DESC
  </code></pre>
  
  <p>is very slow. We've tested executing the query without the DISTINCT and it takes about 30 seconds. There are 4.000.000+ registers in the table.
  There are index for the PK field orderId and almost every other field</p>
  
  <p>The EXPLAIN PLAN with DISTINCT is:</p>
  
  <pre><code>SELECT STATEMENT () (null)
   SORT (ORDER BY)    (null)
    HASH (UNIQUE) (null)
     TABLE ACCESS (FULL)  ORDER_HEADER
  </code></pre>
  
  <p>and without the DISTINCT is:</p>
  
  <pre><code>SELECT STATEMENT () (null)
   SORT (ORDER BY)    (null)
    TABLE ACCESS (FULL)   ORDER_HEADER
  </code></pre>
  
  <p>any ideas about tuning oracle to improve the performance of this kind of queries?
  It's very difficult to rewrite the query because is automatically generated by ofbiz
  so I think the solution is about tuning oracle</p>
  
  <p>thanks in advance</p>
  
  <p>EDIT: I analyzed the query using tkprof ,as suggested by Rob van Wijk and haffax,and the result is the following</p>
  
  <pre><code>********************************************************************************
  
  SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
   SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY, 
   FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID, 
   ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID, 
   AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE, 
   REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, 
  CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM 
  ERP.ORDER_HEADER WHERE STATUS_ID = 'ORDER_COMPLETED' ORDER BY ORDER_DATE DESC
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.03       0.01          0          0          0           0
  Execute      1      0.00       0.00          0          0          0           0
  Fetch        1      9.10     160.81      66729      65203         37          50
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      9.14     160.83      66729      65203         37          50
  
  Misses in library cache during parse: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 58  
  
  Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    SQL*Net message to client                       1        0.00          0.00
    db file scattered read                       8178        0.28        146.55
    direct path write temp                       2200        0.04          4.22
    direct path read temp                          36        0.14          2.01
    SQL*Net more data to client                     3        0.00          0.00
    SQL*Net message from client                     1        3.36          3.36
  ********************************************************************************
  </code></pre>
  
  <p>So it seems the problem is the 'db file scattered read', any ideas to how to tune oracle in order to reduce the wait in this event?</p>
  
  <p>Follow up with the new tkprof result, this time closing the session:</p>
  
  <pre><code>********************************************************************************
  
  SELECT DISTINCT ORDER_ID, ORDER_TYPE_ID, ORDER_NAME, EXTERNAL_ID,
   SALES_CHANNEL_ENUM_ID, ORDER_DATE, ENTRY_DATE, VISIT_ID, STATUS_ID, CREATED_BY,
   FIRST_ATTEMPT_ORDER_ID, CURRENCY_UOM, SYNC_STATUS_ID, BILLING_ACCOUNT_ID,
   ORIGIN_FACILITY_ID, WEB_SITE_ID, PRODUCT_STORE_ID, TERMINAL_ID, TRANSACTION_ID,
   AUTO_ORDER_SHOPPING_LIST_ID, NEEDS_INVENTORY_ISSUANCE, IS_RUSH_ORDER, INTERNAL_CODE,
   REMAINING_SUB_TOTAL, GRAND_TOTAL, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
  CREATED_TX_STAMP, RECIBIR_BODEGAL, RECEPCIONADA_BODEGAL, FECHA_RECEPCION_BODEGAL FROM
  ERP.ORDER_HEADER WHERE STATUS_ID = 'ORDER_COMPLETED' ORDER BY ORDER_DATE DESC
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.03       0.01          0          0          0           0
  Execute      2      0.00       0.00          0          0          0           0
  Fetch        1      8.23      47.66      66576      65203         31          50
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        4      8.26      47.68      66576      65203         31          50
  
  Misses in library cache during parse: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 58 
  
  Rows     Row Source Operation
  -------  ---------------------------------------------------
       50  SORT ORDER BY (cr=65203 pr=66576 pw=75025 time=47666679 us)
  3456659   TABLE ACCESS FULL ORDER_HEADER (cr=65203 pr=65188 pw=0 time=20757300 us)
  
  
  Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    SQL*Net message to client                       1        0.00          0.00
    db file scattered read                       8179        0.14         34.96
    direct path write temp                       2230        0.00          3.91
    direct path read temp                          52        0.14          0.84
    SQL*Net more data to client                     3        0.00          0.00
    SQL*Net message from client                     1     1510.62       1510.62
  ********************************************************************************
  </code></pre>