代码之家 › 专栏 › 技术社区 › Ismael

如何使用SELECT DISTINCT提高Oracle中的性能

ofbiz oracle performance

Ismael · 技术社区 · 17 年前

我目前正在部署基于OFBiz的ERP

最大的问题之一是一些oracle性能问题,通过分析ofbiz日志和以下查询:

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

PK字段orderId和几乎所有其他字段都有索引

带有DISTINCT的解释计划是:

SELECT STATEMENT () (null)
 SORT (ORDER BY)    (null)
  HASH (UNIQUE) (null)
   TABLE ACCESS (FULL)  ORDER_HEADER

如果没有区别,则是:

SELECT STATEMENT () (null)
 SORT (ORDER BY)    (null)
  TABLE ACCESS (FULL)   ORDER_HEADER

关于调优oracle以提高此类查询的性能,您有什么想法吗? 因此,我认为解决方案是调整oracle

提前谢谢

编辑:根据Rob van Wijk和haffax的建议,我使用tkprof分析了查询,结果如下

********************************************************************************

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

因此,问题似乎是“db文件分散读取”,有没有办法调整oracle以减少此事件中的等待时间?

跟进新tkprof结果,这次结束会话:

********************************************************************************

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

6 回复 | 直到 17 年前

Taryn Frank Pearson 14 年前

尝试禁用哈希聚合:

select /*+ no_use_hash_aggregation*/ distinct ...

http://oracle-randolf.blogspot.com/2011/01/hash-aggregation.html

haffax 17 年前

因为您是根据 order_date 在该字段上有一个降序索引是很重要的。还要告诉oracle您希望使用此索引。放置 订单日期 字段,并使用如下提示

SELECT /*+ index(HEADERS IDX_ORDER_DATE_DESC) */ ... 
FROM ERP.ORDER_HEADER HEADERS
WHERE ...
ORDER BY ORDER_DATE DESC

与其说是拥有索引,不如说是告诉oracle使用它们。甲骨文对索引非常挑剔。当您根据最重要的查询选择索引时,将获得最佳结果。如有疑问,, trace a query . 通过这种方式,您可以看到oracle在查询的哪个部分花费的时间最多,以及您的索引是否被实际提取。在解决性能问题时,跟踪是非常宝贵的。

Tony Andrews 17 年前

ORDER_ID是否使用主键约束声明为PK?因为如果是这样的话,我希望乐观主义者认识到,在这个查询中,DISTINCT是多余的,并对其进行优化。如果没有约束,它将不会知道这是多余的,因此将花费不必要的大量精力来“消除”结果。

Rob van Wijk 17 年前

如果这两个查询之间的差异很大,那将是令人惊讶的。您提到,不带DISTINCT的查询大约需要30秒。使用DISTINCT的查询需要多长时间?

在使用“alter session set events'10046 trace name context forever,level 8'”跟踪会话并在查询完成后断开连接后,是否可以使用DISTINCT显示查询的tkprof输出?通过这种方式,我们可以看到时间实际花在了哪里,以及它是否在等待什么(“直接路径读取温度”可能?)

发布tkprof文件后的后续行动:

我看到您成功地获得了tkprof输出,但不幸的是,您在创建tkprof文件之前没有断开会话。现在,光标处于打开状态,无法将统计行写入跟踪文件。这就是tkprof文件中没有计划/行源操作的原因。如果你能重复这个过程,如果下面的建议被证明是垃圾,那就太好了。

我这边有一点猜测:我认为DISTINCT几乎是一个不可用的选项,因为您选择了太多的列。如果这是真的,那么您的谓词“WHERE STATUS_ID='ORDER_COMPLETED'”是非常有选择性的,您将受益于此列上的索引。创建索引后,请确保对其进行了正确的分析,如果数据值有偏差,甚至可以使用直方图。最终结果将是此查询的不同计划,首先是索引范围扫描,然后是ROWID对表的访问,这将导致非常快速的查询。

创建索引后,可以使用以下语句重新分析表,包括直方图:

exec dbms_stats.gather_table_stats([owner],[table_name],cascade=>true,method_opt=>'FOR ALL INDEXED COLUMNS SIZE')

当做

dpbradley 17 年前

在对无法控制SQL的应用程序进行故障排除时,我发现dbms_sqltune包节省了大量时间。看见 http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm ,是的,很遗憾,您应该获得使用许可。

当然,这个工具不应该代替理解应用程序及其数据结构,但是阅读详细说明更好的计划(如果找到)的执行路径的输出是有教育意义的。

-1

Aurelio Martin Massoni 17 年前

每次运行查询时,Oracle都会访问整个表(表访问(完整))。在STATUS\u ID和ORDER\u TYPE\u ID列上创建索引

CREATE INDEX ERP.ORDER_HEADER_I1 ON ERP.ORDER_HEADER ( STATUS_ID, ORDER_TYPE_ID );