第30期 SELECT * 对SQL性能不利的原因

今天讨论一下SELECT * 对SQL性能不利的原因有哪些，有时候我们应用程序并不需要查询所有的列， 当我编写生产代码时，我在查询选择列表（投影）中都会明确指定感兴趣的列， 这不仅是出于性能原因，也是出于应用程序可靠性原因。 例如，当我们为表添加了新列或表中的列顺序发生变化时，应用程序的数据处理代码是否能正常运行，会不会突然中断？ 在本文中，我将只关注SQL性能方面。我们使用的是基于Oracle的示例，但这也适用于大多数的关系数据库。 我们从以下几点来进行讨论： 1、网络流量增加 2、客户端CPU使用率增加 3、某些查询计划优化是不可能的 4、服务器端内存使用情况 5、服务器端CPU使用率增加 6、硬解析/优化需要更多时间 7、缓存游标占用共享池中的更多内存 8、LOB获取 9、总结 1、网络流量增加 首先我们从网络流量方面讨论一下，网络流量增加是非常显著的一个方面， 因为我们查询的数据量多，当然网络流量也就会增加，这是最明显的效果。 比如我们查询一个每行800列和每行8列的一个表，查询是不同的。 那么每次执行查询时，你最终可能会在网络上发送100倍以上的字节（当然，可能会因单个列的长度不同有所差异）。 更多的网络字节意味着发送更多的网络数据包，根据您的RDBMS实现，也意味着更多的应用程序与数据库之间需要更多数据包网络往返。 Oracle可以将单个fetch调用的结果数据以多个连续的SQL*Net数据包的形式以流式的形式传输回客户端，而不需要客户端应用程序首先确认前面的每个数据包。 这种突发的吞吐量取决于TCP发送缓冲区的大小，当然还有网络链路带宽和延迟。我们可以参考关于SQL*Net更多数据到客户端等待事件的信息。 SQL> SET AUTOT TRACE STAT SQL> SELECT * FROM scott.customers; 1699260 rows selected. Elapsed: 00:01:35.82 Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       45201  consistent gets           0  physical reads           0  redo size   169926130  bytes sent via SQL*Net to client      187267  bytes received via SQL*Net from client       16994  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)     1699260  rows processed 大约需要1分35秒的时间，169 MB的数据从数据库发送回客户端（平均每行大约100字节）。 有趣的是，数据字典统计的粗略行长估计显示，平均行大小应该是119个字节（116加上3个字节的行头、锁字节和列计数）： SQL> SELECT COUNT(*),SUM(avg_col_len) FROM dba_tab_columns       WHERE owner = 'SCOTT' AND table_name = 'CUSTOMERS';   COUNT(*) SUM(AVG_COL_LEN) ---------- ----------------         16              116 CUSTOMERS上表只有16列，现在让我们只选择我的应用程序需要的3列： SQL> SELECT customer_id, credit_limit, customer_since FROM scott.customers; 1699260 rows selected. Elapsed: 00:00:43.20 Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       45201  consistent gets           0  physical reads           0  redo size    31883155  bytes sent via SQL*Net to client      187307  bytes received via SQL*Net from client       16994  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)     1699260  rows processed 因此，在16列中仅选择3列，查询响应时间就提高了2倍多（1分35秒对比43秒）。 sqlplus Elapsed指标包括在DB服务器上执行查询并将其所有记录从客户端获取所需的时间，因此网络延迟、吞吐量和TCP发送缓冲区配置将对其产生影响。 Oracle可以在每次提取调用的结果集中消除重复的字段值，因此，如果您需要提取大量行和列并节省网络带宽（例如，通过数据库链接将数据从纽约复制到新加坡）， 您可以通过按最重复（最不明显的值）的列对查询结果集进行排序来最大限度地实现这种“压缩”，这些列也很宽。 SQL> SELECT * FROM scott.customers       ORDER BY customer_class,nls_territory,nls_language,cust_first_name; 1699260 rows selected. Elapsed: 00:01:09.23 Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets       28478  consistent gets           0  physical reads           0  redo size    65960489  bytes sent via SQL*Net to client      187334  bytes received via SQL*Net from client       16994  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)     1699260  rows processed 上面的测试再次是SELECT *，但是这次查询是按几个VARCHAR2列排序，这些列的最大大小为10-40字节，有很多重复值。在SQL*Net协议级重复数据删除后，服务器仅发送了约65MB数据包。 请注意，对于上述所有测试运行，SQL*Net往返客户端的值都是相同的，这是因为我的应用程序中的fetch arraysize已设置为100。 arraysize控制您最终通过网络发送多少个用于数据检索的fetch调用，第一个fetch之后的每个fetch都会请求返回arraysize的行，无论它们有多宽： 1699260行除以arraysize 100将需要16993次取数+1次初始单行取数=16994次的SQL*Net roundtrips to/from client 因此，SQL*net往返度量取决于通过网络发送的DB调用的数量（取数），而每个往返派生度量发送的字节数取决于取数在单个DB调用中请求的行数以及这些行的宽度。 实际情况稍微复杂一些，取决于应用程序客户端库的行为，但为了简洁起见，我将跳过这一部分。 请注意，您可以进一步增加arraysize（例如从100增加到1000），这样不仅可以减少SQL*Net的往返次数（1700而不是16994），而且传输的字节数也会略有减少，这可能是由于更好的压缩和SQL*Net数据包开销的略微降低。 通过Oracle links传输数据时，不需要在客户端会话中增加arraysize，因为Oracle会自动为dblinks使用最大可能的arraysize（~32767）。 2、客户端CPU使用率增加 在客户端处理的行越多，列越多（列越宽），处理它们所需的CPU时间就越多。 在我的例子中，应用程序认为时间就是提取、格式化记录并将其写入输出文件。 我直接登录到Linux数据库服务器，并通过本地管道运行sqlplus，以排除任何网络/TCP开销。我正在运行的两个脚本是： 使用select *  选择全部16列的情况： selectstar.sql：选择全部16列： SET ARRAYSIZE 100 TERMOUT OFF SPOOL customers.txt SELECT * FROM scott.customers; SPOOL OFF EXIT 选择3列的情况： selectsome.sql: Select 3 columns: SET ARRAYSIZE 100 TERMOUT OFF SPOOL customers.txt SELECT customer_id, credit_limit, customer_since FROM scott.customers; SPOOL OFF EXIT 所以，在本地运行脚本: $ time sqlplus -s system/oracle @selectstar real   1m21.056s user   1m3.053s sys    0m15.736s 当将user+sysCPU加在一起时，我们得到了大约1分19秒的CPU时间，而real的总运行时间为1米21秒，这意味着sqlplus只花了很少的时间， 等待更多的结果从管道中到达。因此，我的“应用程序”在客户端的应用程序思考时间中花费了99%的运行时间，在处理检索到的数据时消耗了CPU。 由于几乎所有的时间都花在客户端应用程序上，我在数据库上做的“调优”不多，添加索引或增加各种数据库缓冲区无济于事，因为数据库时间仅占我总运行时间的1%。 但是，通过更改应用程序代码，只获取我需要的列，我可以大大减少客户端处理/应用程序的时间： $ time sqlplus -s system/oracle @selectsome real   0m4.047s user   0m2.752s sys    0m0.349s 总运行时间仅为4秒，其中约3.1秒用于CPU。更好的性能，更低的CPU使用率！ 当然，您的查询可能会有所不同，具体取决于您运行的应用程序类型以及您使用的DB客户端库。 然而，当你的表有500多列时（就像许多数据仓库表一样），SELECT *和SELECT 10列之间的区别。。。可以是巨大的。 顺便说一句，从Oracle 12.2开始，您可以使用sqlplus -fast选项使sqlplus启用一些性能选项（arraysize、大输出页面大小等）： $ time sqlplus -fast -s system/oracle @selectstar real 0m16.046s user 0m11.851s sys     0m1.718s 而这次选择星脚本现在只需16秒即可运行，而不是1分21秒。 通过将输出直接打印到CSV，sqlplus可以避免一些（列对齐的）格式化代码路径，可以使用更少的CPU： $ time sqlplus -m "csv on" -fast -s system/oracle @selectstar real 0m12.048s user 0m10.144s sys 0m0.447s Oracle编写的快速CSV卸载器终于来了！ 3、某些查询计划优化是不可能的 Oracle的优化器，如果它认为这对性能有好处的话，可以将sql的查询转换为不同的、但在逻辑上等效的sql查询。 sql的查询转换开辟了额外的优化机会（更高效的数据访问路径），有些甚至允许您跳过执行查询的一部分。 例如，如果恰好有一个索引覆盖了SQL所需的所有列，Oracle可以只扫描“索引”，而不是整个“胖”宽的表。 这种索引快速全扫描不是使用索引树遍历，而是更像是按照存储顺序对所有索引块进行全表扫描（忽略根和分支块）。 下面是一个select * 与select col1、col2的示例，其中col1、coll2恰好位于索引中： SQL> select * from table(dbms_xplan.display_cursor('f2czqvfz3pj5w',0)); SELECT * FROM scott.customers --------------------------------------------------------------------------- | Id | Operation         | Name      | Starts | A-Rows | A-Time   | Reads | --------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |           |      1 |   1699K| 00:00.57 | 28475 | |  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |   1699K| 00:00.57 | 28475 | --------------------------------------------------------------------------- 上面的select * 必须扫描表以获取其所有列。总运行时间0.57秒，读取28475个块。 现在，让我们只选择恰好被单个多列索引覆盖的几列： SQL> select * from table(dbms_xplan.display_cursor('9gwxhcvwngh96 ',0)); SELECT customer_id, dob FROM scott.customers --------------------------------------------------------------------------------------- | Id  | Operation            | Name              | Starts | A-Rows | A-Time   | Reads | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |                   |      1 |   1699K| 00:00.21 |  5915 | |   1 |  INDEX FAST FULL SCAN| IDX_CUSTOMER_DOB2 |      1 |   1699K| 00:00.21 |  5915 | --------------------------------------------------------------------------------------- 上述查询从全表扫描访问切换到索引快速全扫描，因此只读取5915个索引块，并在0.21秒内完成，而不是0.57秒。 另一个更复杂的例子是Oracle的联接消除转换。 它可以帮助处理访问幕后使用多个连接的视图的大型复杂查询，但我将在这里展示一个微观测试用例。 U（用户）和O（对象）测试表之间定义了一个外键约束-O.owner指向U.username，FK约束强制O表中的每个对象记录在U表中都必须有一个相应的用户记录。 那么，让我们在SQL中运行一个双表连接： SELECT o.owner FROM u, o WHERE u.username = o.owner -------------------------------------------------------------- | Id  | Operation         | Name | Starts | A-Rows | Buffers | -------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |  61477 |    1346 | |   1 |  TABLE ACCESS FULL| O    |      1 |  61477 |    1346 | -------------------------------------------------------------- 根据上述执行计划，实际上只访问了一个表？这就是Oracle正在实施的加入消除转换。 通过仅访问父子关系中的子表可以满足此查询，因为我们希望O中的记录在U中有相应的记录，外键约束保证这是真的！ 在上面的查询中，我们只从子表O中选择了列， 下面让我们也将U.username添加到选择列表中： SELECT o.owner,u.username FROM u, o WHERE u.username = o.owner Plan hash value: 3411128970 -------------------------------------------------------------- | Id  | Operation         | Name | Starts | A-Rows | Buffers | -------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |  61477 |    1346 | |   1 |  TABLE ACCESS FULL| O    |      1 |  61477 |    1346 | -------------------------------------------------------------- 尽管从表U中选择了一列，但我们仍然不必去表U——这是因为由于WHERE U.username=o.owner连接条件，该列保证与o.owner完全相同。Oracle足够聪明，可以避免进行连接， 因为它知道这是一个逻辑上有效的快捷方式。 但是现在让我们从表U中选择一个额外的非连接列，我甚至没有使用具有相同效果的select *： SELECT o.owner,u.username,u.created FROM u, o WHERE u.username = o.owner -------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | A-Rows | Buffers | Used-Mem | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |  61477 |    1350 |          | |*  1 |  HASH JOIN         |      |      1 |  61477 |    1350 | 1557K (0)| |   2 |   TABLE ACCESS FULL| U    |      1 |     51 |       3 |          | |   3 |   TABLE ACCESS FULL| O    |      1 |  61477 |    1346 |          | --------------------------------------------------------------------------    1 - access("U"."USERNAME"="O"."OWNER") 现在我们看到这两个表都被访问和连接，因为没有有效的快捷方式（优化）可供选择。 您可能会说，这似乎是一个非常奇特的优化，在现实生活中几乎没有价值（您不需要父表中的列，并且父表无论如何都是由其主键索引的）。 在实践中，对于复杂的sql查询（连接了数十个表，有多个子查询、视图等），sql的执行计划，这可能是非常有益的。 此外，如果转换阶段可以从连接中删除一些表，那么“物理优化器”将更容易为剩余的表找出一个好的连接顺序。 4、服务器端内存使用情况 在查看sql执行计划时，有一个名为Used-Mem的列。 如用于排序的排序缓冲区或用于哈希连接的哈希表、区分和分组，都需要一个内存暂存区（SQL游标工作区）来操作。一次处理的行越多，通常需要的内存就越多。 而且，每行缓冲的列越多，所需的内存就越多！ 最简单的例子就是ORDER BY： SELECT * FROM scott.customers ORDER BY customer_since Plan hash value: 2792773903 ---------------------------------------------------------------------------------- | Id  | Operation          | Name      | Starts | A-Rows |   A-Time   | Used-Mem | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           |      1 |   1699K|00:00:02.31 |          | |   1 |  SORT ORDER BY     |           |      1 |   1699K|00:00:02.31 |  232M (0)| |   2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   1699K|00:00:00.24 |          | ---------------------------------------------------------------------------------- 232 MB内存用于上述排序。（0）表示零通过操作，我们不必将任何临时结果溢出到磁盘，整个排序都适合内存。 现在只选择2列（按第3列排序）： SELECT customer_id,dob FROM scott.customers ORDER BY customer_since Plan hash value: 2792773903 ---------------------------------------------------------------------------------- | Id  | Operation          | Name      | Starts | A-Rows |   A-Time   | Used-Mem | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           |      1 |   1699K|00:00:00.59 |          | |   1 |  SORT ORDER BY     |           |      1 |   1699K|00:00:00.59 |   67M (0)| |   2 |   TABLE ACCESS FULL| CUSTOMERS |      1 |   1699K|00:00:00.13 |          | ---------------------------------------------------------------------------------- 内存使用量从232 MB下降到67 MB。查询仍然必须扫描整个Customers表， 并像以前一样处理1699k行，但它的运行速度快了4倍，因为它在排序阶段没有花费太多的CPU时间。 更窄的记录不仅在缓冲区中使用更少的内存，而且对CPU缓存友好，需要移动更少的字节（RAM访问速度较慢）。 由于通过数据库网络和客户端库发送/接收记录数组（这里甚至不谈论TCP发送/接收缓冲区），宽结果集也会增加内存使用量（包括服务器和客户端）。 当你每次提取检索1000条记录，每条1000列记录的平均大小为5kB时，我们谈论的是数据库端的每个连接至少有5MB的内存， 应用程序端的每个打开的游标至少有5Mb的内存。在实践中，由于处理和打包（和保存）结果的数据结构有一些开销，因此使用量会更大。 如今，内存相对便宜，这不是什么大问题，但我记得15年前的一个大型Oracle系统，客户不得不减小数组大小，否则他们的80000个数据库连接会耗尽服务器内存：-）此外， 如果你的应用程序有某种游标泄漏（语句句柄泄漏），与未使用的游标结果集数组相关的内存可能会积聚起来。 5、服务器端CPU使用率增加 当然，如果上述更高级别的查询方式和优化方式没有发挥作用，最终会做更多的工作。更多的工作意味着更多的CPU使用率（可能还有磁盘I/O等）。 当从数据块中的记录中提取所有500个字段时，而不是仅仅20个字段时，并将其传递给执行计划树时，您将使用更多的CPU周期来完成这项工作。如果使用列式存储布局，您也可能最终也会执行更多的I/O。 例如，使用Oracle的传统的数据块，数据库表有500列，我们要查询第500列，需要跳过所有前499列的信息（行程编码）， 以找到最后一列的起始位置，开始读取数据。但是，现在，如果你真的需要从所有行中检索所有500个字段，SELECT* 对于这个任务来说是有效的， 但如果你的应用程序只使用结果集中的少数列，你将在（昂贵的）数据库服务器上不必要地消耗大量额外的CPU时间。 还有就是我们查询的结果如果需要类型转换，例如在服务器端执行数据类型转换（数字、日期格式到客户端期望的格式）以及字符串的字符集转换（如果有的话），您也会在数据库服务器上消耗更多的CPU。 例如，如果你在某个分析应用程序中从数据库中“仅”选择1M行，但选择了表的所有500列，那么你最终只会为这一个查询进行5亿次数据类型/字符集转换操作，你很快就会意识到这些操作非常消耗CPU。 6、硬解析/优化需要更多时间 我使用create_wide_table.sql脚本创建了一个宽表（1000列）。它有100行，每列都有直方图。 DECLARE     cmd CLOB := 'CREATE TABLE widetable ( id NUMBER PRIMARY KEY ';     ins CLOB := 'INSERT INTO widetable SELECT rownum'; BEGIN     FOR x IN 1..999 LOOP         cmd := cmd || ', col'||TRIM(TO_CHAR(x))||' VARCHAR2(10)';         ins := ins || ', TRIM(TO_CHAR(rownum))';     END LOOP;     cmd := cmd || ')';     ins := ins || ' FROM dual CONNECT BY level <= 100';     EXECUTE IMMEDIATE cmd;     EXECUTE IMMEDIATE ins; END; / COMMIT; -- stats with histograms EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 254'); -- no histograms -- EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 1'); -- EXEC sys.dbms_shared_pool.purge('SYSTEM', 'WIDETABLE', 1, 1); 我正在对它运行一个非常简单的单表选择查询（注释中的 /* test nnn */用于在每次运行查询时强制执行新的硬解析）。 在前两个测试中，我在重新创建表并收集统计数据后立即运行select语句（在此表上没有执行其他查询）： SQL> SET AUTOTRACE TRACE STAT SQL> SELECT * FROM widetable /* test100 */; 100 rows selected. Statistics ----------------------------------------------------------        2004  recursive calls        5267  db block gets        2458  consistent gets           9  physical reads     1110236  redo size      361858  bytes sent via SQL*Net to client         363  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         100  rows processed 2004 recursive calls 的SELECT*的递归调用（用于数据字典访问，可以使用SQL*Trace进行验证）。 我再次创建了表，然后只运行了两列select next： SQL> SELECT id,col1 FROM widetable /* test101 */; 100 rows selected. Statistics ----------------------------------------------------------           5  recursive calls          10  db block gets          51  consistent gets           0  physical reads        2056  redo size        1510  bytes sent via SQL*Net to client         369  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         100  rows processed 硬解析只需要5次递归调用。请看，要求Oracle做更多的工作（“请检查、评估和提取1000列而不是2列”）会对性能产生影响。 好吧，这可能不是什么大问题——假设你的共享池足够大，可以将所有列（及其统计数据/直方图）信息保存在字典缓存中， 那么你就不会有所有这些具有良好热缓存的递归SQL。让我们看看当所有内容都很好地缓存在字典缓存中时，硬解析阶段需要多少时间。 我在一个单独的Oracle会话中使用会话跟踪工具 orasnapper来报告另一个会话（1136）中硬解析测试的指标： SQL> SELECT * FROM widetable /* test1 */; -----------------------------------------------------------------------------     SID, USERNAME  , TYPE, STATISTIC                          ,         DELTA -----------------------------------------------------------------------------    1136, SYSTEM    , TIME, hard parse elapsed time            ,         78158    1136, SYSTEM    , TIME, parse time elapsed                 ,         80912    1136, SYSTEM    , TIME, PL/SQL execution elapsed time      ,           127    1136, SYSTEM    , TIME, DB CPU                             ,         89580    1136, SYSTEM    , TIME, sql execute elapsed time           ,          5659    1136, SYSTEM    , TIME, DB time                            ,         89616 --  End of Stats snap 1, end=2020-11-24 19:31:49, seconds=5 硬解析/优化/编译阶段花费了78毫秒（所有CPU时间）来执行这个非常简单的查询，该查询选择了所有1000列，即使所有表元数据和列统计数据和直方图都已缓存。Oracle必须对所有1000列进行分析和类型检查。现在让我们在同一个表上运行另一个查询，只选择2列： SQL> SELECT id,col1 FROM widetable /* test2 */; -----------------------------------------------------------------------------     SID, USERNAME  , TYPE, STATISTIC                          ,         DELTA -----------------------------------------------------------------------------    1136, SYSTEM    , TIME, hard parse elapsed time            ,          1162    1136, SYSTEM    , TIME, parse time elapsed                 ,          1513    1136, SYSTEM    , TIME, PL/SQL execution elapsed time      ,           110    1136, SYSTEM    , TIME, DB CPU                             ,          2281    1136, SYSTEM    , TIME, sql execute elapsed time           ,           376    1136, SYSTEM    , TIME, DB time                            ,          2128     硬解析只花了大约1毫秒！SQL在结构上是相同的，在同一个表上，只选择了更少的列。 出于好奇，当我们删除所有列上的直方图并再次执行SELECT*时会发生什么： SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SQL> SELECT * FROM widetable /* test3 */; -----------------------------------------------------------------------------     SID, USERNAME  , TYPE, STATISTIC                          ,         DELTA -----------------------------------------------------------------------------    1136, SYSTEM    , TIME, hard parse elapsed time            ,         30018    1136, SYSTEM    , TIME, parse time elapsed                 ,         30547    1136, SYSTEM    , TIME, PL/SQL execution elapsed time      ,           202    1136, SYSTEM    , TIME, DB CPU                             ,         37899    1136, SYSTEM    , TIME, sql execute elapsed time           ,          5770    1136, SYSTEM    , TIME, DB time                            ,         37807 现在，对于1000列查询，硬解析需要30毫秒，显然它枚举/映射了查询中涉及的所有列的直方图， 包括刚刚选择的列（在任何过滤器或连接中都没有使用，直方图实际上用于计划优化）。 7、缓存游标占用共享池中的更多内存 Oracle将编译的游标缓存在共享池内存中。Oracle很聪明，只将所需的元数据（各种操作码、数据类型、规则）包含在编译的游标中。 因此，使用1000列的缓存游标将比仅使用2列的游标大得多： SQL> SELECT sharable_mem, sql_id, child_number, sql_text FROM v$sql       WHERE sql_text LIKE 'SELECT % FROM widetable'; SHARABLE_MEM SQL_ID        CHILD_NUMBER SQL_TEXT ------------ ------------- ------------ -------------------------------------        19470 b98yvssnnk13p            0 SELECT id,col1 FROM widetable       886600 c4d3jr3fjfa3t            0 SELECT * FROM widetable 在共享池中，2列游标占用19kB内存，1000列游标占用886kB内存！ 从大约10g开始，Oracle将大多数大型库缓存对象分配拆分为标准化的数据块大小（4kB），以减少共享池碎片的影响。 让我们用我的orasqlmem.sql脚本（v$sql_shared_memory）来看看这些游标： Show shared pool memory usage of SQL statement with SQL_ID c4d3jr3fjfa3t CHILD_NUMBER SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM ------------ ------------ -------------- -----------            0       886600         324792      219488 TOTAL_SIZE   AVG_SIZE     CHUNKS ALLOC_CL CHUNK_TYPE STRUCTURE            FUNCTION             CHUNK_COM            HEAP_ADDR ---------- ---------- ---------- -------- ---------- -------------------- -------------------- -------------------- ----------------     272000        272       1000 freeabl           0 kccdef               qkxrMem              kccdef: qkxrMem      000000019FF49290     128000        128       1000 freeabl           0 opn                  qkexrInitO           opn: qkexrInitO      000000019FF49290     112568         56       2002 freeabl           0                      qosdInitExprCtx      qosdInitExprCtx      000000019FF49290      96456         96       1000 freeabl           0                      qosdUpdateExprM      qosdUpdateExprM      000000019FF49290      57320         57       1000 freeabl           0 idndef*[]            qkex                 idndef*[]: qkex      000000019FF49290      48304         48       1000 freeabl           0 qeSel                qkxrXfor             qeSel: qkxrXfor      000000019FF49290      40808         40       1005 freeabl           0 idndef               qcuAll               idndef : qcuAll      000000019FF49290      40024      40024          1 freeabl           0 kafco                qkacol               kafco : qkacol       000000019FF49290      37272        591         63 freeabl           0                      237.kggec            237.kggec            000000019FF49290      16080       8040          2 freeabl           0 qeeRwo               qeeCrea              qeeRwo: qeeCrea      000000019FF49290       8032       8032          1 freeabl           0 kggac                kggacCre             kggac: kggacCre      000000019FF49290       8024       8024          1 freeabl           0 kksoff               opitca               kksoff : opitca      000000019FF49290       3392         64         53 freeabl           0 kksol                kksnsg               kksol : kksnsg       000000019FF49290       2880       2880          1 free              0                      free memory          free memory          000000019FF49290       1152        576          2 freeabl           0                      16751.kgght          16751.kgght          000000019FF49290       1040       1040          1 freeabl           0 ctxdef               kksLoadC             ctxdef:kksLoadC      000000019FF49290        640        320          2 freeabl           0                      615.kggec            615.kggec            000000019FF49290        624        624          1 recr           4095                      237.kggec            237.kggec            000000019FF49290        472        472          1 freeabl           0 qertbs               qertbIAl             qertbs:qertbIAl      000000019FF49290 ... 53 rows selected. 1000列的SELECT*游标有很多内部分配（在游标堆内分配），其中内部块的计数为1000或接近1000的倍数，因此编译游标中的每一列都有一个（或两个）。 这些结构是执行计划所必需的（比如当字段#3需要向上传递到执行计划树时，需要调用Oracle内核的C函数）。 例如，如果列#77恰好是一个DATE，并且稍后在计划的一个单独步骤中将其与TIMESTAMP列#88进行比较，则需要在某处添加一个操作码， 指示Oracle在该计划步骤中为其中一个列执行额外的数据类型转换函数。执行计划是一个由动态分配的结构和其中的操作码组成的树。 显然，即使是从单个表中进行简单的选择而没有任何进一步的复杂性，也需要大量的内部分配。 让我们看看2列游标内存的内部： Show shared pool memory usage of SQL statement with SQL_ID b98yvssnnk13p CHILD_NUMBER SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM ------------ ------------ -------------- -----------            0        19470           7072        5560 TOTAL_SIZE   AVG_SIZE     CHUNKS ALLOC_CL CHUNK_TYPE STRUCTURE            FUNCTION             CHUNK_COM            HEAP_ADDR ---------- ---------- ---------- -------- ---------- -------------------- -------------------- -------------------- ----------------       1640       1640          1 free              0                      free memory          free memory          00000001AF2B75D0       1152        576          2 freeabl           0                      16751.kgght          16751.kgght          00000001AF2B75D0       1040       1040          1 freeabl           0 ctxdef               kksLoadC             ctxdef:kksLoadC      00000001AF2B75D0        640        320          2 freeabl           0                      615.kggec            615.kggec            00000001AF2B75D0        624        624          1 recr           4095                      237.kggec            237.kggec            00000001AF2B75D0        544        272          2 freeabl           0 kccdef               qkxrMem              kccdef: qkxrMem      00000001AF2B75D0        472        472          1 freeabl           0 qertbs               qertbIAl             qertbs:qertbIAl      00000001AF2B75D0        456        456          1 freeabl           0 opixpop              kctdef               opixpop:kctdef       00000001AF2B75D0        456        456          1 freeabl           0 kctdef               qcdlgo               kctdef : qcdlgo      00000001AF2B75D0        328         54          6 freeabl           0                      qosdInitExprCtx      qosdInitExprCtx      00000001AF2B75D0        312        312          1 freeabl           0 pqctx                kkfdParal            pqctx:kkfdParal      00000001AF2B75D0        296        296          1 freeabl           0                      unmdef in opipr      unmdef in opipr      00000001AF2B75D0        256        128          2 freeabl           0 opn                  qkexrInitO           opn: qkexrInitO      00000001AF2B75D0        256         42          6 freeabl           0 idndef               qcuAll               idndef : qcuAll      00000001AF2B75D0        208         41          5 freeabl           0                      kggsmInitCompac      kggsmInitCompac      00000001AF2B75D0        192         96          2 freeabl           0                      qosdUpdateExprM      qosdUpdateExprM      00000001AF2B75D0        184        184          1 freeabl           0                      237.kggec            237.kggec            00000001AF2B75D0 ... 事实上，我们不再看到数千个内部分配块（例如，与之前的1000个相比，只有2个kccdef）。 8、LOB获取 当您从表中选择LOB列时，由于为每个返回的行单独获取LOB项所做的额外网络往返，您的性能将大幅下降。 可以将arraysize设置为1000，但如果您从结果集中选择一个LOB列，那么对于每个（1000）行的数组，您将不得不进行1000次额外的网络往返以获取单个LOB值。 除了“普通”列之外，我还将创建一个包含2个LOB列的表： SQL> CREATE TABLE tl (id INT, a VARCHAR2(100), b CLOB, c CLOB); Table created. SQL> INSERT INTO tl SELECT rownum, dummy, dummy, dummy FROM dual CONNECT BY LEVEL <= 1000; 1000 rows created. SQL> COMMIT; Commit complete. 让我们先只选择2个普通列： SQL> SET AUTOT TRACE STAT SQL> SET TIMING ON SQL> SELECT id, a FROM tl; 1000 rows selected. Elapsed: 00:00:00.04 Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets          28  consistent gets           0  physical reads           0  redo size       10149  bytes sent via SQL*Net to client         441  bytes received via SQL*Net from client          11  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        1000  rows processed     获取2个正常列非常快（0.04秒），只需要11次SQL*Net往返（使用arraysize 100）。 现在，让我们添加一个LOB列： SQL> SELECT id, a, b FROM tl; 1000 rows selected. Elapsed: 00:00:05.50 Statistics ----------------------------------------------------------          10  recursive calls           5  db block gets        2027  consistent gets           0  physical reads        1052  redo size      421070  bytes sent via SQL*Net to client      252345  bytes received via SQL*Net from client        2002  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        1000  rows processed 由于LOB检索的“破坏性”，它需要5.5秒和2002 SQL*Net往返。默认情况下， 任何具有非空LOB列的行都会立即发送回（在获取的数组中只有一行），并且会发送回一个LOB定位器，而不是LOB列值， 这会导致客户端发出单独的LOBREAD数据库调用，只是为了获取单个LOB列值。当您选择多个LOB列时，情况会变得更糟： SQL> SELECT id, a, b, c FROM tl; 1000 rows selected. Elapsed: 00:00:09.28 Statistics ----------------------------------------------------------           6  recursive calls           5  db block gets        3026  consistent gets           0  physical reads         996  redo size      740122  bytes sent via SQL*Net to client      493348  bytes received via SQL*Net from client        3002  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        1000  rows processed 现在只需要一个LOB列就需要9秒多，而不是之前的5.5秒。我们有大约3000次往返，每行一次（因为LOB项检索会中断数组获取），每行有一次两个LOB项获取往返。 从Oracle 12.2（我认为）开始，sqlplus中有一个参数LOBPREFETCH，它允许将一定量的LOB数据“捆绑”到行提取往返中。 Oracle客户端库应允许更大的LOB预取值，但sqlplus中的限制为32kB：     SQL> SET LOBPREFETCH 32767 SQL>  SQL> SELECT id, a, b, c FROM tl; 1000 rows selected. Elapsed: 00:00:04.80 Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        1005  consistent gets           0  physical reads           0  redo size      366157  bytes sent via SQL*Net to client       11756  bytes received via SQL*Net from client        1002  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)        1000  rows processed     现在我们又减少到了大约1000次往返，因为我的LOB值很小，它们都捆绑在每行的获取结果中。但Oracle仍然一次只获取一行，尽管我的arraysize=100。 因此，由于偶然的SELECT*添加了LOB列，您的40毫秒查询最终可能需要超过9秒。而且，您根本看不到数据库的太多活动， 因为大部分响应时间都花在客户端和服务器之间的SQL*Net往返上。没有索引会让这更快，更多的CPU不会让这更快——修复你的应用程序代码会让这更快。 这就引出了一个问题，如果想将数百万个LOB值引入我的应用程序，该怎么办，但我会把这留给一个单独的博客条目！         9、总结： 当我看到一个性能问题时，我会考虑如何少做。另一种选择是添加更多硬件。 “少做”的一种方法是确保你从数据库中准确地询问你想要什么，不多也不少。 仅选择您实际需要的列是该方法的最佳方案，而不是使用select *。