Oracle查询性能行为不一致 [英] Oracle inconsistent performance behaviour of query

问题描述

考虑以下查询:

SELECT * 
  FROM (
    SELECT ARRM.*, ROWNUM 
    FROM CRS_ARRANGEMENTS ARRM 
    WHERE 
       CONCAT(ARRM.NBR_ARRANGEMENT, ARRM.TYP_PRODUCT_ARRANGEMENT) > 
       CONCAT('0000000000000000', '0000') 
    ORDER BY 
      ARRM.NBR_ARRANGEMENT, 
      ARRM.TYP_PRODUCT_ARRANGEMENT, 
      ARRM.COD_CURRENCY) 
WHERE ROWNUM < 1000;

此查询在具有10000000条目的表上运行.从Oracle SQL Developer或我的应用程序运行查询时，需要4分钟才能运行！不幸的是，这也是我正在编写的应用程序内部的行为.将该值从1000更改为10完全没有影响，表明它正在执行全表扫描.

This query runs on a table that has 10 000 000 entries. When running the query from Oracle SQL Developer or my application it takes 4 minutes to run! Unfortunately that is also the behaviour inside the application that I'm writing. Changing the value from 1000 to 10 has no impact at all, suggesting that it is doing a full table scan.

但是，从SQuirreL运行时，查询将在几毫秒内返回 .那怎么可能?在SQuirreL中生成的解释计划给出:

However when running from SQuirreL the query returns within a few milliseconds. How is that possible? Explain plan generated in SQuirreL gives:

但是对于相同的查询，Oracle SQL Developer中生成了一个不同的解释计划:

But a different explain plan is generated in Oracle SQL Developer, for the same query:

有人知道这种行为上的差异是怎么可能的吗?我无法理解.我尝试使用JPA和原始JDBC.在应用程序中，我需要解析所有10000万条记录，并且此查询用于分页，因此等待4分钟不是一种选择(这需要27天).

Any idea how this difference in behaviour is possible? I can't get to understand it. I tried with JPA and raw JDBC. In the application I need to parse through all 10 000 000 records and this query is used for the paging, so waiting 4 minutes is not an option (that would take 27 days).

注意:我在SQuirreL和我的应用程序中使用了相同的Oracle jdbc驱动程序，所以这不是问题的根源.

Note: I'm using the same Oracle jdbc driver in SQuirreL and my application so that is not the source of the problem.

推荐答案

显然，National Language Support或NLS参数与它有关. Oracle SQL Developer将它们设置为"Dutch"，这是基于您的语言环境的默认设置，而SQuirreL则将其设置为BINARY.这种差异使优化器使用不同的路径来解决查询.为了在jdbc会话中使用正确的NLS_SORT参数，需要使用以下命令:

Apparently the National Language Support or NLS parameters had something to do with it. Oracle SQL Developer had them set to "Dutch", default setting based on your Locale, while SQuirreL has it set to BINARY. This difference made the optimizer use different paths to solve the query. In order to use the correct NLS_SORT parameter in the jdbc session the following command needs to be used:

ALTER SESSION SET NLS_SORT=BINARY

然后将在查询上使用正确的索引.

Then the correct indexes will be used on the query.

这篇关于Oracle查询性能行为不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！