减小和降低高水位标会在 OLTP 系统中引发问题吗?

huangapple go评论43阅读模式
英文:

Will Shrinking and lowering the high water mark cause issues in OLTP systems

问题

新手上路,我们有一个旧的Oracle 10g实例,必须保持运行,直到被替代。夜间作业一直运行得非常慢,导致一些问题。每隔一周会运行一个大型处理过程,执行大量的DML操作(删除、插入、更新)。一些表格中有超过200万行数据。我注意到一些表格的HWM高于预期,在Toad中运行了数据库顾问检查,建议缩小一些表格,但我担心这些表格可能需要空间来执行DML操作,或者缩小它们会使处理速度更快还是更慢?

由于许可成本问题,我们无法增加CPU。

英文:

newb here, We have an old Oracle 10g instance that they have to keep alive until it is replaced. The nightly jobs have been very slow causing some issues. Every other Week there is a large process that does large amounts of DML (deletes, inserts, updates). Some of these tables have 2+ million rows. I noticed that some of the tables the HWM is higher than expected and in Toad I ran a database advisor check that recommended shrinking some tables, but I am concerned that the tables may need the space for DML operations or will shrinking them make the process faster or slower?

We cannot add cpu due to licensing costs

答案1

得分: 1

如果您正在进行完整扫描并且在HWM下有很多空间,则确实需要重组这些表(使用alter table move)。这没有任何负面影响,只有好处。但如果您的慢查询是使用索引的话,那么好处将是有限的。

不要假设慢查询是由于空间碎片导致的。使用ASH(v$active_session_history)和SQL监视器(v$sql_plan_monitor)数据,或者使用利用这些数据的图形工具来精确了解您的查询正在执行什么操作。了解如何阅读执行计划,并确定是否为您的数据使用了正确的计划。调优不幸地不是可以在这个论坛的问题中解决的简单事情。

英文:

If you are accessing the tables with full scans and have a lot of empty space below the HWM, then yes, definitely reorg those (alter table move). There is no downside, only benefit. But if your slow jobs are using indexes, then the benefit will be minimal.

Don't assume that your slow jobs are due to space fragmentation. Use ASH (v$active_session_history) and SQL monitor (v$sql_plan_monitor) data or a graphical tool that utilizes this data to explore exactly what your queries are doing. Understand how to read execution plans and determine whether the correct plan is being used for your data. Tuning is unfortunately not a simple thing that can be addressed with a question on this forum.

答案2

得分: 0

一般来说,缩小表格或重建索引应该加速对表格的读取,或者对执行全表扫描的操作。它不应该影响其他 DML 操作。

在选择或搜索数据时,表格中的所有空块以及查询使用的任何索引仍然需要被读取,因此重新构建它们以减少空间浪费并降低高水位线通常会提高性能。这在索引中尤其如此,因为由于删除的行而丢失的空间不会被重新利用。

英文:

In general, shrinking tables or rebuilding indexes should speed up reads of the table, or anything that does full table scans. It should not affect other DML operations.

When selecting or searching data, all of the empty blocks in the table and any indexes used by the query must still be read, so rebuilding them to reduce empty space and lower the high water mark will generally improve performance. This is especially true in indexes, where space lost to deleted rows is not recovered for reuse.

huangapple
  • 本文由 发表于 2023年2月8日 22:28:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/75387229.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定