我们如何优化oracle数据库呢？

Question

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

以下是其中一张表的示例

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 张桌子，每张桌子专用于一个 inserv。这样做会是疯狂的事情吗？我想知道。

我们迭代 inserv 的数量并对它们运行所有查询。目前我们正在清除数据，因此我们预计不会超过 1500 万条记录。

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来将整个数据库加载到内存中。用ORACLE可以做到吗？

提前致谢

Answer 1

1-2) 在 (inservid,commandtimestamp) 上建立索引将用（快速）完整索引扫描替换完整表扫描，因为两列之一不为空（因此数据库可以使用索引而不是表）。这应该比全表扫描更快，但所有 15+ M 行索引条目都将被读取。

使用预先计算的表（例如快速刷新物化视图）可以获得更快的响应时间，在这种情况下，表上的 DML 操作的性能可能会受到影响（插入/更新/删除会更慢）

3）如果您还有一些 IO 带宽，并行运行的查询会给您带来一些好处。现在，您的全表扫描可能会从磁盘读取大量行。除非您的表驻留在物理上分离的设备上，否则并行的收益将是最小的。

4) RAM中的加载在Oracle中主要是自动完成的：即在大多数情况下Oracle很好地将频繁访问的数据放置在内存中。

Answer 2

第 4 部分，这通常不是必需的。在 9i 中，您可以设置一个特殊的“保留”缓冲池来尝试将索引保留在内存中。 （但由于现在 Oracle 不提供支持，因此现在可能不是开始尝试迄今为止尚未使用过的功能的好时机）

您的客户端是否在本地计算机上，如果不是，客户端可能会受益于 11g 功能 客户端（侧面”结果缓存，以最大程度地减少数据库服务器往返次数。

摘自 Oracle® 数据库性能调优指南
11g 版本 2 (11.2) 部件号 E16638-03

7.6.2.1 客户端结果缓存的工作原理
客户端结果缓存存储最外层查询的结果，这些结果是 OCI 应用程序定义的列。子查询和查询块不会被缓存。

图 7-4 显示了具有数据库登录会话的客户端进程。该客户端进程具有一个客户端结果缓存，该客户端结果缓存在该客户端进程中运行的多个应用程序会话之间共享。如果第一个应用程序会话运行查询，那么它会从数据库检索行并将它们缓存在客户端结果缓存中。如果其他应用程序会话运行相同的查询，那么它们也会从客户端结果缓存中检索行。

Answer 3

1),4) 我同意文森特的观点。

2）你有很长的行 - 所以最好有一个更大的 db_block_size - 16Kb 或 32Kb
也许值得尝试在你的桌子上进行压缩。还要检查您的 db_file_multiblock_read_count。可以对其进行分区 - 将其分发到尽可能多的驱动器上。

3）你可以玩一下并行提示，看看效果如何。

Answer 4

您的数据模型已损坏。您能否提供有关您运行的查询的更多详细信息？添加索引只能在一定程度上帮助您。