如何避免这种非常繁重的查询会减慢应用程序的速度？
我们有一个在生产环境中运行的 Web 应用程序，有时客户抱怨应用程序变得多么慢。
当我们检查应用程序和数据库的情况时，我们发现这个“珍贵”的查询正在由多个用户同时执行（从而对数据库服务器造成极高的负载）：
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
我们的应用程序不执行此查询查询，我相信它是一个 Hibernate 内部查询。我发现很少有关于 Hibernate 为什么执行这种极其繁重的查询的信息，因此非常感谢任何关于如何避免它的帮助！
生产环境信息：Red Hat Enterprise Linux 5.3（Tikanga）、JDK 1.5、Web 容器 OC4J（Oracle 应用服务器中）、Oracle Database 10.1.0.4、JDK 1.2 和 1.3 的 JDBC 驱动程序、Hibernate 版本 3.2.6.ga、连接池库 C3P0 版本 0.9.1。
更新：感谢@BalusC 的澄清，确实是 Hibernate 执行了查询，现在我对发生的事情有了更好的了解。我将解释我们处理休眠会话的方式（这是非常基本的，是的，如果您有关于如何更好地处理它的建议，我们非常欢迎！）
我们有一个过滤器（实现 javax.servlet.Filter），当它启动时（init 方法）它构造会话工厂（假设这只发生一次）。然后，每个发送到应用程序的 HttpRequest 都会通过过滤器并获得一个新会话，并启动一个事务。当该过程结束时，它通过过滤器返回，提交事务，杀死 hibernate 会话，然后继续前进页面（我们不将 hibernate 会话存储在 Http 中）会话，因为它在我们的测试中从未表现良好）。
现在到了我认为问题所在的部分。在我们的开发环境中，我们在 Tomcat 5.5 中部署应用程序，当我们启动服务时，所有过滤器都会立即启动，并且仅启动一次。在 OC4J 的生产环境中似乎不是这样工作的。我们部署应用程序，并且仅当第一个请求到达时，OC4J 才会实例化过滤器。
这让我认为 OC4J 在每个请求上实例化过滤器（或者至少多次，这仍然是错误的），从而在每个请求上创建一个会话工厂，执行该 %&%# %$# 查询，这导致了我的问题！
现在，这是正确的吗？有没有办法让我配置 OC4J 使其仅实例化过滤器一次？
非常感谢大家花时间回复此问题！
We have a web application running in a production enviroment and at some point the client complained about how slow the application got.
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!
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.
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!)
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 new session and it starts a transaction. When the process it's over, it comes back through the filter, makes the commit of the transaction, kills the 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).
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.
This leads me to think that OC4J instantiates the filters on every request (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!
Now, is that correct? It's there a way for me to configure the OC4J for it to instantiate filters only once?
Thanks very much to all of you for taking the time to respond this!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
它确实来自 Hibernate，特别是 org.hibernate.tool.hbm2ddl.TableMetadata。它的每一个都被用来验证架构（表和列映射）。显然，它不必要在每个生成的请求或会话上执行，而不是在应用程序启动期间仅执行一次。例如，您是否不需要在每个请求或会话上调用 Hibernate 配置器？
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?正如 所指出的@BalusC，此查询是在模式验证期间执行的。但验证通常在创建 SessionFactory（如果已激活）时一次性完成。您是否显式调用以下方法：
Configuration#validateSchema(Dialect, DatabaseMetadata)？您对“视图中打开会话”的实现看起来不错（并且非常接近此页面）。根据 Servlet 规范，部署描述符中的每个
您可以在过滤器中添加一些日志记录吗？将
SessionFactory设为静态（在一个很好的旧HibernateUtil类中）怎么样？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)?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.Can you put some logging in the filter? What about making the
SessionFactorystatic (in a good oldHibernateUtilclass)?好吧，经过几个月的观察，发现问题不在于我的 Web 应用程序。问题出在使用同一数据库实例（不同用户）的其他 Oracle Forms 应用程序。
发生的情况是，Oracle Forms 应用程序锁定了数据库上的记录，因此几乎所有数据库工作都变得极其缓慢（包括我喜爱的 Hibernate 查询）。
锁定的原因是 Oracle Forms 应用程序的外键均未建立索引。因此，正如我的老板向我解释的那样（他发现了原因），当用户在 Oracle Form 应用程序中编辑主从关系的主记录时，数据库锁定整个明细表（如果存在）其外键没有索引。这是因为 Oracle Forms 的工作方式是更新主记录的所有字段，包括主键字段，即外键引用的字段。
简而言之，请永远不要让外键没有索引。我们因此受了很多苦。
感谢所有花时间提供帮助的人。
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.
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).
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 table if 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.
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.
具体来说，编写支持不同数据库的软件的人们以数据库中立的方式打包他们的软件。 IE。当覆盖不存在时，他们所做的是使用 jdbc db 元数据 getTables 调用来检查连接是否仍然有效。通常，您可以使用 select * from Dual 等进行覆盖，但如果未完成此操作，或者您没有具体说明您正在使用哪种数据库，则该软件是为了运行可与任何 JDBC 驱动程序一起使用的数据库而编写的。 jdbc db 元数据库 getTables 会做到这一点。
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.
我只是想提出我用来解决这个问题的解决方法。我们的数据库中通常有很多模式，在我们尝试使用休眠的应用程序中，这需要几个小时才能完成，因为它最终检查了大量对象（查询本身执行得很快，但它只是这样做了）其中有很多）。
我所做的是覆盖正在连接的模式中的 ALL_OBJECTS 视图，以便它只带回它自己的对象，而不是数据库中的所有对象。
例如，
创建或替换查看所有对象作为选择用户所有者，O.* FROM USER_OBJECTS O；
这不是最好的解决方案，但对于此应用程序来说，没有其他任何东西可以使用 ALL_OBJECTS 视图，因此它工作正常并且启动速度要快得多。
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.
e.g.
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.
您的 10g 数据库中的 sys 模式是否使用更新的统计数据进行分析？您是否收集了 sys 架构中固定表的统计信息。对 all_objects 的查询不应该对系统造成太大负担。如果您通过 autotrace/tkprof 运行查询，主要的资源花在什么/哪里。
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.
这来自默认的 C3PO 测试查询。在您的配置中提供更简单的查询。比如，从 Dual 中选择“X”。
This is coming from the default C3PO test query. Supply a simpler query in your configuration. Something like, select 'X' from dual.
遇到了同样的问题，原因正是 Bob Breitling 所描述的，C3P0 默认使用 JDBC API 进行连接测试：
为了更改此行为，必须设置 preferredTestQuery，或者如果使用 C3P0通过 hibernate - hibernate.c3p0.preferredTestQuery
Had the same problem, the cause was exactly the one described by Bob Breitling, C3P0 uses by default JDBC API for connection testing :
In order to change this behavior the preferredTestQuery must be set, or if C3P0 is used through hibernate - hibernate.c3p0.preferredTestQuery
我相信此查询来自 Oracle JDBC 驱动程序，用于实现 Hibernate 请求以通过 DatabaseMetaData 检索数据库对象信息。
这个查询应该不会太昂贵，或者至少不在我手头的系统上。您的 all_objects 数量是多少，更重要的是，您在解释计划的行/字节总数中看到了什么？
I believe this query is coming from the Oracle JDBC driver to implement a Hibernate request to retrieve database object info through 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?