oracle 如何避免这种非常繁重的查询会减慢应用程序的速度？

How to avoid this very heavy query that slows down the application?

提问by Juan Paredes

We have a web application running in a production enviroment and at some point the client complained about how slow the application got.

我们有一个在生产环境中运行的 Web 应用程序，有时客户抱怨应用程序变得如此缓慢。

When we checked what was going on with the application and the database we discover this "precious" query that was being executed by several users at the same time (thus inflicting an extremely high load on the database server):

当我们检查应用程序和数据库的情况时，我们发现这个“宝贵的”查询同时由多个用户执行（因此对数据库服务器造成了极高的负载）：

SELECT   NULL AS table_cat,
         o.owner AS table_schem,
         o.object_name AS table_name,
         o.object_type AS table_type,
         NULL AS remarks
FROM     all_objects o
WHERE    o.owner LIKE :1 ESCAPE :"SYS_B_0" AND
         o.object_name LIKE :2 ESCAPE :"SYS_B_1" AND
         o.object_type IN(:"SYS_B_2", :"SYS_B_3")
ORDER BY table_type, table_schem, table_name

Our application does not execute this query, I believe it is an Hibernate internal query. I've found little information on why Hibernate does this extremely heavy query, so any help in how to avoid it very much appreciated!

我们的应用程序不执行这个查询，我相信它是一个 Hibernate 内部查询。我几乎没有找到关于 Hibernate 为什么执行这个极其繁重的查询的信息，因此非常感谢有关如何避免它的任何帮助！

The production enviroment information: Red Hat Enterprise Linux 5.3 (Tikanga), JDK 1.5, web container OC4J (whitin Oracle Application Server), Oracle Database 10.1.0.4, JDBC Driver for JDK 1.2 and 1.3, Hibernate version 3.2.6.ga, connection pool library C3P0 version 0.9.1.

生产环境信息：Red Hat Enterprise Linux 5.3 (Tikanga), JDK 1.5, web container OC4J (whitin Oracle Application Server), Oracle Database 10.1.0.4, JDBC Driver for JDK 1.2 and 1.3, Hibernate version 3.2.6.ga, connection池库 C3P0 版本 0.9.1。

UPDATE: Thanks to @BalusC for claryfing that indeed it is Hibernate that executes the query, now I have a better idea about what's going on. I'll explain the way we handle the hibernate session (it's very rudimentary yes, if you have suggestions about how to handle it better they are more than welcome!)

更新：感谢@BalusC 澄清确实是 Hibernate 执行查询，现在我对正在发生的事情有了更好的了解。我将解释我们处理休眠会话的方式（这是非常基本的，是的，如果您有关于如何更好地处理它的建议，我们非常欢迎！）

We have a filter (implements javax.servlet.Filter) that when it's starts (init method) it constructs the session factory (supossedly this happens only once). Then every HttpRequest that goes to the application goes through the filter and obtains a newsession and it starts a transaction. When the process it's over, it comes back through the filter, makes the commit of the transaction, killsthe hibernate session, then continue to the forward page (we don't store the hibernate session in the Http session because it never worked well in our tests).

我们有一个过滤器（实现 javax.servlet.Filter），当它启动时（init 方法）它构造会话工厂（假设这只发生一次）。然后进入应用程序的每个 HttpRequest 都通过过滤器并获得一个新会话并启动一个事务。当这个过程结束时，它通过过滤器返回，提交事务，终止休眠会话，然后继续前进页面（我们不将休眠会话存储在 Http 会话中，因为它在我们的测试）。

Now here comes the part where I think the problem is. In our development enviroment we deploy our apps in Tomcat 5.5, and when we start the service all filters start inmediately and only once. In the production enviroment with OC4J doesn't seem to work that way. We deploy the application and only when the first request arrives, OC4J instantiates the filters.

现在到了我认为问题所在的部分。在我们的开发环境中，我们在 Tomcat 5.5 中部署我们的应用程序，当我们启动服务时，所有过滤器都会立即启动，并且只启动一次。在 OC4J 的生产环境中，似乎不是这样工作的。我们部署应用程序，只有当第一个请求到达时，OC4J 才会实例化过滤器。

This leads me to think that OC4J instantiates the filters on everyrequest (or at least multiple times, which is still wrong), thus creating a session factory on every request, wich executes that %&%#%$# query, which leads to my problem!

这让我认为 OC4J 在每个请求（或至少多次，这仍然是错误的）上实例化过滤器，从而在每个请求上创建一个会话工厂，执行那个 %&%#%$# 查询，这导致我的问题！

Now, is that correct? It's there a way for me to configure the OC4J for it to instantiate filters only once?

现在，这是正确的吗？有没有办法让我配置 OC4J 让它只实例化过滤器一次？

Thanks very much to all of you for taking the time to respond this!

非常感谢大家抽出时间来回答这个问题！

采纳答案by Juan Paredes

All right, after months of looking at the thing, it turns out that the problem wasn't my web application. The problem was the other Oracle Forms applications that use the same instance (different user) of the database.

好吧，经过几个月的研究，结果证明问题不是我的 Web 应用程序。问题在于使用数据库的相同实例（不同用户）的其他 Oracle Forms 应用程序。

What was happening was that the Oracle Forms applications were locking records on the database, therefore making pretty much all of the work of the database extremely slow (including my beloved Hibernate query).

发生的事情是 Oracle Forms 应用程序锁定了数据库上的记录，因此几乎所有的数据库工作都非常缓慢（包括我心爱的 Hibernate 查询）。

The reason of the locks was that none of the foreign keys of the Oracle Forms apps were indexed. So as my boss explained it to me (he discovered the reason) when a user is editing the master record of a master-detail relationship in a Oracle Form application, the database locks the entire detail tableif there is no index for its foreign key. That is because the way the Oracle Forms works, that it updates all af the fields of the master record, including the ones of the primary key, wich are the fields that the foreign key references.

锁定的原因是 Oracle Forms 应用程序的外键均未编入索引。所以正如我老板给我解释的（他发现了原因），当用户在 Oracle Form 应用程序中编辑主从关系的主记录时，如果其外键没有索引，数据库就会锁定整个从表. 这是因为 Oracle Forms 的工作方式，它更新主记录的所有字段，包括主键的字段，也就是外键引用的字段。

In short, please NEVER leave your foreign keys without indexes. We suffered a lot with this.

简而言之，请永远不要让您的外键没有索引。我们为此受了很多苦。

Thanks to all of you who took the time to help.

感谢所有花时间提供帮助的人。

回答by BalusC

It's indeed coming from Hibernate and specifically org.hibernate.tool.hbm2ddl.TableMetadata. It's under each been used to validate the schema (table and column mapping). Apparently it's unnecessarily been executed on every spawned request or session instead of only once during application's startup. Are you for example not unnecessarily calling the Hibernate Configurator on every request or session?

它确实来自 Hibernate，特别是org.hibernate.tool.hbm2ddl.TableMetadata. 它在每个被用来验证模式（表和列映射）下。显然，它不必要地在每个产生的请求或会话上执行，而不是在应用程序启动期间只执行一次。例如，您不是在每个请求或会话中不必要地调用 Hibernate Configurator 吗？

回答by Pascal Thivent

As pointed out by @BalusC, this query is performed during schema validation. But validation is usually done once for all when creating the SessionFactory(if activated). Do you call the following method explicitely: Configuration#validateSchema(Dialect, DatabaseMetadata)?

正如@BalusC所指出的，这个查询是在模式验证期间执行的。但是在创建SessionFactory（如果激活）时，验证通常会一劳永逸地完成。你明确地调用下面的方法：Configuration#validateSchema(Dialect, DatabaseMetadata)？

---

---

Now, is that correct? It's there a way for me to configure the OC4J for it to instantiate filters only once?

现在，这是正确的吗？有没有办法让我配置 OC4J 让它只实例化过滤器一次？

Your implementation of the Open Session In View looks fine (and is very close to the one suggested in this page). And according to the Servlet specification only one instance per <filter>declaration in the deployment descriptor is instantiated per Java Virtual Machine (JVMTM) of the container. Since it is very unlikely that this isn't the case with OC4J, I'm tempted to say that there must something else.

您对 Open Session In View 的实现看起来不错（并且非常接近本页中建议的实现）。并且根据 Servlet 规范，每个<filter>容器的 Java 虚拟机 (JVMTM) 仅实例化部署描述符中的每个声明一个实例。由于 OC4J 不太可能出现这种情况，因此我很想说一定还有其他的东西。

Can you put some logging in the filter? What about making the SessionFactorystatic (in a good old HibernateUtilclass)?

你能在过滤器中加入一些日志吗？如何制作SessionFactory静态（在一个很好的老HibernateUtil班级中）？

回答by Robert Nicholson

Specifically what happens is that folks who write software that support different databases package their software in a database neutral way. ie. when an override isn't present what they do is use jdbc db metadata getTables call to check if the connection is still valid. Typically you override with select * from dual etc but when that's not done or you don't specifically say what kind of database you are using the software is written to run something that will work with any JDBC driver. jdbc db metadatabase getTables will do that.

具体而言，编写支持不同数据库的软件的人以数据库中立的方式打包他们的软件。IE。当覆盖不存在时，他们所做的是使用 jdbc db metadata getTables 调用来检查连接是否仍然有效。通常，您使用 select * from dual 等进行覆盖，但如果未完成，或者您没有具体说明您正在使用哪种数据库，则该软件是为了运行可以与任何 JDBC 驱动程序一起使用的内容而编写的。jdbc db metadatabase getTables 会做到这一点。

回答by Rodney Schuler

I just wanted to put in the workaround I used to get around this problem. We typically have lots of schemas in our databases and this would take hours to finish in the application we were trying to use which used hibernate because of the large number of objects that it ended up checking (the query itself would execute fast but it just did so many of them).

我只是想提出我用来解决这个问题的解决方法。我们的数据库中通常有很多模式，这需要几个小时才能在我们尝试使用的应用程序中完成，因为它最终检查了大量对象（查询本身会执行得很快，但它只是这样做了）这么多）。

What I did is overrode the ALL_OBJECTS view in the schema being connected to so that it only brought back it's own objects and not all objects in the db.

我所做的是覆盖正在连接的模式中的 ALL_OBJECTS 视图，以便它只带回它自己的对象，而不是数据库中的所有对象。

e.g.

例如

CREATE OR REPLACE VIEW ALL_OBJECTS AS SELECT USER OWNER, O.* FROM USER_OBJECTS O;

CREATE OR REPLACE VIEW ALL_OBJECTS AS SELECT USER OWNER, O.* FROM USER_OBJECTS O;

It's not the greatest solution but for this application there is nothing else that would be using the ALL_OBJECTS view so it works fine and starts up substantially faster.

这不是最好的解决方案，但对于这个应用程序，没有其他东西可以使用 ALL_OBJECTS 视图，因此它可以正常工作并且启动速度要快得多。

回答by Bax

Had the same problem, the cause was exactly the one described by Bob Breitling, C3P0 uses by default JDBC API for connection testing :

有同样的问题，原因正是 Bob Breitling 描述的，C3P0 默认使用 JDBC API 进行连接测试：

java.sql.DatabaseMetaData#getTables(....)

In order to change this behavior the preferredTestQuerymust be set, or if C3P0 is used through hibernate - hibernate.c3p0.preferredTestQuery

为了改变这种行为，必须设置preferredTestQuery，或者如果 C3P0 通过 hibernate 使用 - hibernate.c3p0.preferredTestQuery

回答by Bob Breitling

This is coming from the default C3PO test query. Supply a simpler query in your configuration. Something like, select 'X' from dual.

这来自默认的 C3PO 测试查询。在您的配置中提供更简单的查询。类似的，从双重选择“X”。

回答by MichaelN

Is the sys schema in your 10g database analyze with updated stats? Have you collected stats on the fixed tables in the sys schema. Queries on all_objects shouldn't be that taxing to a system. If you run the query via autotrace/tkprof what/where is the major of the resources be spent at.

您的 10g 数据库中的 sys 架构是否使用更新的统计数据进行分析？您是否收集了 sys 架构中固定表的统计信息。对 all_objects 的查询不应该对系统造成那么大的负担。如果您通过 autotrace/tkprof 运行查询，则主要资源消耗在哪里/在哪里。

回答by dpbradley

I believe this query is coming from the Oracle JDBC driver to implement a Hibernate request to retrieve database object info through DatabaseMetaData.

我相信这个查询来自 Oracle JDBC 驱动程序来实现一个 Hibernate 请求，以通过 DatabaseMetaData 检索数据库对象信息。

This query shouldn't be too expensive, or at least isn't on a system I have handy. What's your count of all_objects and more importantly, what do you see in the rows/bytes total for the explain plan?

这个查询不应该太昂贵，或者至少不在我手边的系统上。您对 all_objects 的计数是多少，更重要的是，您在解释计划的行/字节总数中看到了什么？