database - 我们如何优化oracle数据库？

Question

我们有一个 Oracle 9i 数据库，其中有 7 个表，每个表有超过 1500 万条记录。表之间没有关系，即没有外键。

这是其中一个表格的示例

CREATE TABLE GSS.SHOWPD
(
  INSERVID          VARCHAR2(7 CHAR)            NOT NULL,
  CAGEPOS           VARCHAR2(8 CHAR)            DEFAULT NULL,
  DETAILEDSTATE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  FAILEDMB          NUMBER                      DEFAULT NULL,
  FREECHUNK         NUMBER                      DEFAULT NULL,
  FREEMB            NUMBER                      DEFAULT NULL,
  FWREV             VARCHAR2(100 CHAR)          DEFAULT NULL,
  FWSTATUS          VARCHAR2(100 CHAR)          DEFAULT NULL,
  AID               NUMBER                      DEFAULT NULL,
  LDA               VARCHAR2(100 CHAR)          DEFAULT NULL,
  MANUF             VARCHAR2(100 CHAR)          DEFAULT NULL,
  AMODEL            VARCHAR2(4000 CHAR)         DEFAULT NULL,
  NODEWWN           VARCHAR2(64 CHAR)           DEFAULT NULL,
  NRMUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDUNAVAIL  VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDOK         VARCHAR2(100 CHAR)          DEFAULT NULL,
  AORDER            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  RDCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  REUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SERIAL            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SIZEMB            NUMBER                      DEFAULT NULL,
  SPARECHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPAREMB           NUMBER                      DEFAULT NULL,
  SPEEDKRPM         VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDUNINIT   VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDOK       VARCHAR2(100 CHAR)          DEFAULT NULL,
  STATE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  TEMPDEGC          NUMBER                      DEFAULT NULL,
  TOTALCHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  ATYPE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  UNAVAILABLEMB     NUMBER                      DEFAULT NULL,
  VOLUMEMB          NUMBER                      DEFAULT NULL,
  WRCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  WRUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  COMMANDTIMESTAMP  TIMESTAMP(6)                DEFAULT NULL  NOT NULL,
  FETCHTIMESTAMP    TIMESTAMP(6)                DEFAULT NULL  NOT NULL
)

请注意，INSERVID 可以是 1400 种类型之一。因此，有可能有 1400 张 table ，每张 table 专用于一个 inserv。那会是疯狂的事情吗？我想知道。

我们遍历 no of inservs 并对它们运行所有查询。现在我们正在清除数据，因此我们预计不会超过 15+ 百万条记录。

1) 我们在查询中有不同，因此需要全表扫描。 Oracle 执行计划显示我们正在进行全表扫描。

select distinct(inservid),commandtimestamp from statpd order by commandtimestamp desc;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   665 | 13300 | 79488 |
|   1 |  SORT UNIQUE         |             |   665 | 13300 | 69088 |
|   2 |   TABLE ACCESS FULL  | STATPD      |  4128K|    78M| 19406 |
--------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.

2) 每个表都有一个关于 inserv id 的索引。它有助于降低查询成本，我们还能做些什么？有什么使事情变得更快的提示/技巧吗？

3) 如果我们将针对每个表运行的 sql 查询作为针对每个表运行的并行脚本，这是否有意义。这会导致更快的完成吗？

4) 我们有足够的 RAM 将整个 DB 加载到内存中。用 ORACLE 可以做到吗？

提前致谢

Answer 1

1-2) 在 (inservid,commandtimestamp) 上建立索引会将 FULL TABLE SCAN 替换为(FAST)FULL INDEX SCAN，因为两列之一不是 NULL(因此 DB可以使用索引而不是表)。这应该比全表扫描更快，但所有 15+ M 行索引条目都将被读取。

您可以使用预先计算的表(例如快速刷新物化 View )获得更快的响应时间，在这种情况下，您可能会对表上的 DML 操作产生性能影响(插入/更新/删除会更慢)

3) 如果您还剩下一些 IO 带宽，让查询并行运行会给您带来一些好处。现在你的全表扫描可能会从磁盘中读取很多行。除非您的表驻留在物理上分开的设备上，否则通过并行获得的 yield 将是最小的。

4) RAM 中的加载在 Oracle 中主要是自动完成的:即在大多数情况下，Oracle 在将频繁访问的数据放入内存方面做得很好。