Oracle 12.2高分区模型下本地索引性能大幅下降问题问询

问题描述 #

我搭建了一个采用LIST分区且嵌套LIST子分区的Oracle数据库，最近增加分区数量后遇到了严重的性能问题：原本毫秒级就能执行完的简单查询，现在居然要耗时1分49秒——数据库里几乎没有额外数据，仅仅是分区数量增加了而已。

环境配置 #

• 共54张表
• 每张表包含10000个分区，当前每个分区对应1个子分区
• 每张表平均有10个索引

相关DDL配置已整理完成，可提供具体内容用于进一步排查

测试场景 #

在近乎空的数据库中执行两类操作：

1. 基于分区和子分区键的简单SELECT语句
2. 基于分区和子分区键的DELETE语句

测试用例1的追踪与执行信息已整理完成，可提供具体内容

排查过程 #

• 删除所有索引后，性能问题完全解决
• **索引范围扫描（index range scan）**占用了几乎100%的执行时间
• 当每张表分区数为1600时，相同配置下性能表现正常

核心线索（来自追踪日志） #

SQL ID: 9b4m3fr3vf9kn Plan Hash: 2436900644
select obj#, dataobj#, subpart#, hiboundlen, hiboundval, flags, ts#, file#,
block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel,
leafcnt, distkey, lblkkey, dblkkey, clufac, spare2, length(bhiboundval), bhiboundval
from indsubpart$ where pobj# = :1 order by subpart#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        23      0.00       0.00          0          0          0           0
Execute  230000     19.13      20.91          0          0          0           0
Fetch    460000     25.59      27.83       3357    1380000          0      230000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    690023     44.73      48.75       3357    1380000          0      230000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: USERNAME (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID INDSUBPART$ (cr=5 pr=4 pw=0 time=1813 us starts=1 cost=4 size=69 card=1)
         1          1          1   INDEX RANGE SCAN I_INDSUBPART_POBJSUBPART$ (cr=4 pr=3 pw=0 time=1317 us starts=1 cost=3 size=0 card=1)(object id 815)

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
PGA memory operation                              2        0.00          0.00
cell single block physical read                 3357        0.01          1.68
latch: shared pool                                1        0.00          0.00
********************************************************************************

疑问 #

我的当前配置远未达到Oracle官方给出的逻辑上限，但性能却出现了断崖式下降。想请教一下：是我的DDL配置存在错误，还是当前的分区规模本身就会导致这类性能问题？另外，这个规模下出现性能问题是合理的吗？

内容的提问来源于stack exchange，提问作者HermanJose