什么存储在JNDI缓存中?

时间:2020-12-30 03:51:55

We are using WebSphere 6.1 on Windows connecting to a DB2 database on a different Windows machine. We are using prepared statements in our application. While tuning a database index (adding a column to the end of an index) we do not see the performance boost we saw on a test database with the same query, after changing the index the processor on database server actually was pegged.

我们在Windows上使用WebSphere 6.1连接到不同Windows机器上的DB2数据库。我们在申请中使用预备陈述。在调整数据库索引(将列添加到索引末尾)时,我们没有看到在具有相同查询的测试数据库上看到的性能提升,在更改索引之后,数据库服务器上的处理器实际上被挂起。

Are the prepared statements query plans actually stored in JNDI? If so, how can they be cleared? If not, how can we clear the cache on the DB2 server?

准备好的语句查询计划是否实际存储在JNDI中?如果是这样,他们怎么能被清除?如果没有,我们如何清除DB2服务器上的缓存?

2 个解决方案

#1


The execution plans for prepared statements are stored in the DB2 package cache. It's possible that after an index is added, the package cache is still holding on to old access plans that are now sub-optimal.

预准备语句的执行计划存储在DB2包缓存中。在添加索引之后,包缓存仍然可以保留现在是次优的旧访问计划。

After adding an index, you will want to issue a RUNSTATS statement on at least that index in order to provide the DB2 optimizer with the information it needs to choose a reasonable access plan.

添加索引后,您将希望至少在该索引上发出RUNSTATS语句,以便为DB2优化器提供选择合理访问计划所需的信息。

Once the RUNSTATS statistics exist for the new index, issue a FLUSH PACKAGE CACHE statement to release any access plans that involved the affected table. The downside of this is that access plans for other dynamic SQL statements will also be ejected, leading to a temporary uptick in optimizer usage as each distinct SQL statement is optimized and cached.

一旦存在新索引的RUNSTATS统计信息,就发出FLUSH PACKAGE CACHE语句以释放涉及受影响表的任何访问计划。这样做的缺点是,其他动态SQL语句的访问计划也将被弹出,导致优化器使用暂时上升,因为每个不同的SQL语句都经过优化和缓存。

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007117.html

#2


Query plans are 'normally' held in the database by the RDBMS itself, with the exact life cycle being vendor specific I'd guess. They are definitely not held in a JNDI registry.

查询计划通常由RDBMS本身在数据库中保存,确切的生命周期是供应商特定的我猜。它们绝对不是在JNDI注册表中。

  1. I assume there is a similar volume of data in both databases?

    我假设两个数据库中都有类似的数据量?

  2. If so have you looked at the explain plan for both databases and confirmed they match?

    如果是这样,你看了两个数据库的解释计划并确认它们匹配?

If the answer to both these questions is yes I'm out of ideas and it's time to reboot the database server.

如果这两个问题的答案都是肯定的,我就没有想法了,是时候重启数据库服务器了。

#1


The execution plans for prepared statements are stored in the DB2 package cache. It's possible that after an index is added, the package cache is still holding on to old access plans that are now sub-optimal.

预准备语句的执行计划存储在DB2包缓存中。在添加索引之后,包缓存仍然可以保留现在是次优的旧访问计划。

After adding an index, you will want to issue a RUNSTATS statement on at least that index in order to provide the DB2 optimizer with the information it needs to choose a reasonable access plan.

添加索引后,您将希望至少在该索引上发出RUNSTATS语句,以便为DB2优化器提供选择合理访问计划所需的信息。

Once the RUNSTATS statistics exist for the new index, issue a FLUSH PACKAGE CACHE statement to release any access plans that involved the affected table. The downside of this is that access plans for other dynamic SQL statements will also be ejected, leading to a temporary uptick in optimizer usage as each distinct SQL statement is optimized and cached.

一旦存在新索引的RUNSTATS统计信息,就发出FLUSH PACKAGE CACHE语句以释放涉及受影响表的任何访问计划。这样做的缺点是,其他动态SQL语句的访问计划也将被弹出,导致优化器使用暂时上升,因为每个不同的SQL语句都经过优化和缓存。

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007117.html

#2


Query plans are 'normally' held in the database by the RDBMS itself, with the exact life cycle being vendor specific I'd guess. They are definitely not held in a JNDI registry.

查询计划通常由RDBMS本身在数据库中保存,确切的生命周期是供应商特定的我猜。它们绝对不是在JNDI注册表中。

  1. I assume there is a similar volume of data in both databases?

    我假设两个数据库中都有类似的数据量?

  2. If so have you looked at the explain plan for both databases and confirmed they match?

    如果是这样,你看了两个数据库的解释计划并确认它们匹配?

If the answer to both these questions is yes I'm out of ideas and it's time to reboot the database server.

如果这两个问题的答案都是肯定的,我就没有想法了,是时候重启数据库服务器了。