performance - 我的 Oracle 查询在非常短和非常长的执行时间之间变化。什么会导致这种情况？

Question

[如果我重复发布此内容，我深表歉意 - 我以为我上周五发布了一个问题，但我的帐户没有显示任何提出的问题。]

主要问题:Linux 上的 Oracle 11g 在 4 秒和 1000 秒内交替完成和返回一个特定查询的数据。 Oracle 在两个不同的执行计划之间来回切换，其中一个执行计划的速度非常慢。

我们已经确定了对查询的几个语义相同的更改，这使得 Oracle 不断选择快速执行计划。我们担心由于无明显原因的来回切换而出现错误或数据损坏。

任何有关此行为原因的想法将不胜感激。

以下是具体细节:

我们对模式的四个简单表进行了非常简单的 Oracle 查询。当我们运行这个查询时，我们得到的执行时间截然不同 - 如果我们连续运行它 20 次，其中三到四次执行需要 4 秒才能返回数据，其他执行需要超过 1000 秒。

我们尝试记录执行计划，Oracle 在两个不同的执行计划之间进行更改 - 一个计划提供 4 秒响应，另一个计划提供 1.000 秒以上响应。

每个表大约有 30.000 行，响应大约有 5.000 行。当 Oracle 选择慢速执行计划时，获取每个结果行的时间会呈指数级减慢 - 响应的前 1.000 行需要 2 秒，第 1.000-2.000 行需要 30 秒，第 2.000-3.000 行需要 90 秒，依此类推.

我们在所使用的列上有索引，并且为了快速执行计划，它们按预期使用。慢速计划始终对其中一个索引执行“快速完整扫描”(成本约为 2.000)，而快速计划则对同一索引执行“范围扫描”(成本约为 2) 。计划完全不同——也许就是因为这个。我们尝试过 DROP:ing 这个索引并重新 CREATE:ing 它，但结果没有区别。

此外，查询在其中一个表的主键列上包含 NOT LIKE。如果我们将这些 NOT LIKE 表达式移至引用列，Oracle 始终会选择快速执行计划。

我们不想锁定执行计划，因为查询预计会发生微妙的变化。此外，执行计划之间的这种来回改变让我们担心 - 它闻起来有错误或损坏的数据。

有谁知道 Oracle 为何会这样做？除了锁定执行计划之外，还有其他方法吗？

这是在快速和慢速执行计划之间切换的查询:

select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member
and ggf.ucid_group = g.ucid
and a.status = 'active'
and g.unix_gid is not null
and gm.valid_from <= sysdate
and gm.valid_to >= sysdate
and g.ucid not like '$_%' escape '$'
and g.ucid not like 's$_%' escape '$'

如果我对引用列而不是主键列执行 NOT LIKE，查询总是很快:

select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member
and ggf.ucid_group = g.ucid
and a.status = 'active'
and g.unix_gid is not null
and gm.valid_from <= sysdate
and gm.valid_to >= sysdate
and ggf.ucid_group not like '$_%' escape '$'
and ggf.ucid_group not like 's$_%' escape '$'

如果我删除帐户表(“a.status = 'active'”)或组表(“g.unix_gid is not null”)上的限制，查询总是很快，但当然会返回更多行。但是，它确实会在相当恒定的 10 秒内返回 30.000 行(而对于限制更严格的查询的慢速执行计划，它会在 1.000 秒内返回 5.000 行)。

查询涉及的架构的相关部分是:

CREATE TABLE "PDB"."GROUPS"
(
  "UCID"        VARCHAR2(256 BYTE),    
  "UNIX_GID"    NUMBER(*,0),
  [...]

  PRIMARY KEY ("UCID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "PDB" ENABLE,

  CONSTRAINT "GN_FK" FOREIGN KEY ("UCID") REFERENCES "PDB"."NAMESPACE" ("UCID") ENABLE
)
CREATE TABLE "PDB"."ACCOUNT"
(
  "UCID"           VARCHAR2(256 BYTE),
  "STATUS"         VARCHAR2(10 BYTE) NOT NULL ENABLE,
  [...]

  PRIMARY KEY ("UCID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "PDB" ENABLE,

  FOREIGN KEY ("STATUS") REFERENCES "PDB"."ACCOUNT_STATUS" ("STATUS") ENABLE,
  CONSTRAINT "AN_FK" FOREIGN KEY ("UCID") REFERENCES "PDB"."NAMESPACE" ("UCID") ENABLE,
)
CREATE TABLE "PDB"."GROUP_MEMBERS"
(
  "UCID_GROUP"  VARCHAR2(256 BYTE) NOT NULL ENABLE,
  "UCID_MEMBER" VARCHAR2(256 BYTE) NOT NULL ENABLE,
  "VALID_FROM" DATE NOT NULL ENABLE,
  "VALID_TO" DATE NOT NULL ENABLE,
  CONSTRAINT "GROUP_MEMBERS_GROUPS_FK1" FOREIGN KEY ("UCID_GROUP") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE,
  CONSTRAINT "GROUP_MEMBERS_MEMBER_FK1" FOREIGN KEY ("UCID_MEMBER") REFERENCES "PDB"."ACCOUNT" ("UCID") ENABLE
)
CREATE INDEX "PDB"."IDX_GROUP_MEMBERS_FROM" ON "PDB"."GROUP_MEMBERS"("VALID_FROM")
CREATE INDEX "PDB"."IDX_GROUP_MEMBERS_TO" ON "PDB"."GROUP_MEMBERS"("VALID_TO")
CREATE TABLE "PDB"."GROUP_GROUPS_FLAT"
(
  "UCID_GROUP"  VARCHAR2(256 BYTE),
  "UCID_MEMBER" VARCHAR2(256 BYTE),
  CONSTRAINT "GROUP_GROUPS_FLAT_GROUPS_FK1" FOREIGN KEY ("UCID_GROUP") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE,
  CONSTRAINT "GROUP_GROUPS_FLAT_GROUPS_FK2" FOREIGN KEY ("UCID_MEMBER") REFERENCES "PDB"."GROUPS" ("UCID") ENABLE
)
CREATE INDEX "PDB"."IDX_GROUP_GROUPS_FLAT_GROUP" ON "PDB"."GROUP_GROUPS_FLAT("UCID_GROUP")
CREATE INDEX "PDB"."IDX_GROUP_GROUPS_FLAT_MEMBER" ON "PDB"."GROUP_GROUPS_FLAT("UCID_MEMBER")

Answer 1

如果您使用 11g，则可以使用计划管理来阻止 Oracle 切换计划。

http://www.oracle-base.com/articles/11g/sql-plan-management-11gr1.php