英文:
Oracle Database performance tuning (on database level)
问题
I have a big table in the oracle database (it's almost 400 million rows) and a program which connects to that table and executes a query.
我有一个在Oracle数据库中的大表格（约有4亿行），还有一个连接到该表格并执行查询的程序。
I don't have any possibility to modify the program neither the database structure.
我没有修改程序或数据库结构的可能性。
The problem is that the query execution takes a long time. Sometimes 11 seconds, which in my case is really bad. But the query is always the same. It's something like:
问题在于查询执行时间很长。有时候需要11秒，对我来说是非常糟糕的情况。但查询总是一样的。它类似于：
SELECT COL1, COL2 FROM TABLE1 WHERE upper(COL3) = upper('abc')
and the result is ~5 rows.
查询结果大约是5行。
These are the indexes in the table from COL3:
这些是表格中关于COL3的索引：
CREATE INDEX "DATABASE1"."PIPTABLE1_2" ON "DATABASE1"."PTABLE1" (UPPER("COL3"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATABASE1_INDX";
CREATE INDEX "DATABASE1"."PIPTABLE1_3" ON "DATABASE1"."PTABLE1" ("COL3")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATABASE1_INDX";
Is there any way to optimize it on the database level?
有没有办法在数据库级别对其进行优化？
(Oracle Database Version = 19c)
（Oracle数据库版本=19c）
In the execution plan below, I had to hide real names and values.
(Of course now (Saturday) the query worked fast)
在下面的执行计划中，我必须隐藏真实的名称和值。
（当然，现在（星期六）查询执行得很快）
英文:
I have a big table in oracle database (it's almost 400 million rows) and a program which connects to that table and executes a query.
I don't have any possibility to modify the program neither the database structure.
The problem is that the query execution takes long time. Sometimes 11 seconds, which in my case is really bad. But the query is always the same. It's something like:
SELECT COL1, COL2 FROM TABLE1 WHERE upper(COL3) = upper('abc')
and the result is ~5 rows.
These are the indexes in the table from COL3:
CREATE INDEX "DATABASE1"."PIPTABLE1_2" ON "DATABASE1"."PTABLE1" (UPPER("COL3"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATABASE1_INDX";
CREATE INDEX "DATABASE1"."PIPTABLE1_3" ON "DATABASE1"."PTABLE1" ("COL3")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATABASE1_INDX";
Is there any way to optimize it on database level?
(Oracle Database Version = 19c)
In the execution plan below I had to hide real names and values.
(Of course now (Saturday) the query worked fast)
答案1
得分: 1
即使在列上有索引，如果查询将返回许多值，那么这些值可能分散在许多物理数据块中。因此，如果您要返回（例如）10,000行，您可能已经读取了10,000个不同的块，即使通过索引，这也会花费您的时间。
您可以尝试对数据进行聚类以减少这种成本。您可以对表使用属性聚类，然后进行在线移动。
alter table xxx add clustering by ...
alter table xxx move online;
更多细节和演示，请参见此视频（包括优缺点）：
英文:
Even if you have an index on a column, if the query is going to return many values, then its possible that those values are scattered across many physical data blocks. Thus if you were getting (say) 10,000 rows back, you might have read 10,000 different blocks which, even via an index, is going to cost you time.
You might be able to look at clustering the data to reduce this cost. You can use attribute clustering on the table for this, followed by the an online move.
alter table xxx add clustering by ...
alter table xxx move online;
More details and demo in this video (including pros and cons)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论