暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 顺序和一致除以十

askTom 2018-10-17
307

问题描述


你好,汤姆,

当我使用ORDER BY时,我看到一致的得到在我的测试中除以10,你能解释为什么吗?


这是我的测试。

首先,我创建一个表格。
          SQL> create table test_obj01 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
          SQL> insert into test_obj01 select * from test_obj01;
          78417 rows created.

          SQL> /
          ...
          ...

          SQL> select count(*) from test_obj01;
          COUNT(*)
          ----------
          627352
复制


我用ROWNUM创建一个ID,以避免使用Oracle Net进行重复数据删除并收集统计信息。
          SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
          627352 rows updated.

          SQL> commit;

          SQL> exec dbms_stats.gather_schema_stats('HR');
复制




我使用AUTOTRACE查看统计信息。
第一次执行 “无订单选择”: 块从硬盘驱动器中读取。
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

          SQL> select owner from test_obj01;
          627352 rows selected.

          Elapsed: 00:00:04.36

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          ---------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL     | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          45886  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          627352  rows processed
复制



第二次执行SELECT无ORDER BY: 块仅从内存中读取; 0物理读取,46 000一致得到。
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.13

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          -------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT   |                       |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45841  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          627352  rows processed
复制




我为下一个测试刷新硬盘驱动器上的块; 我必须这样做,因为我的数据库没有工作负载,所以DBWR写数据非常非常慢。
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
复制



现在,我们执行相同的SELECT,但使用ORDER BY。Oracle进行了排序; 成本是4029,几乎是进行全表扫描的成本的四倍。
首次执行: Oracle从硬盘读取块。
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

          SQL> select owner from test_obj01 order by owner;
          627352 rows selected.

          Elapsed: 00:00:03.96

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          -----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time   |
          -----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|                |  4029   (1)| 00:00:01 |
          |   1 |  SORT ORDER BY            |                      |   627K|  7351K|    12M      |  4029   (1)| 00:00:01 |
          |   2 |   TABLE ACCESS FULL    | TEST_OBJ01  |   627K|  7351K|                |  1168   (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          4328  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          627352  rows processed
复制



第二次执行,仅从内存中读取块。
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.10

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          ----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          ----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                      |   627K   |  7351K|            |  4029   (1)| 00:00:01   | 
          |   1 |  SORT ORDER BY          |                      |   627K   |  7351K|    12M  |  4029   (1)| 00:00:01   |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ01 |   627K    |  7351K|            |  1168   (1)| 00:00:01   |
          ----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4283  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          627352  rows processed
复制




我们看到了什么?有了ORDER BY,选择比没有ORDER BY快一点: 04分10秒VS 04分13秒。

传输的数据的大小相同,因此在我的选择中没有重复数据删除; 解释在别处。


我看到,没有ORDER BY,Oracle做45个841一致得到,但是,有ORDER BY,Oracle只做4个283: 10倍!

我知道SGA很久以前就被称为 “共享全球区域”,有人称PGA为 “私人全球区域”。因此,我认为这些块是在SGA中使用闩锁和互斥锁进行管理的,而不是在PGA中,并且管理这些锁需要一些时间。但是,我对此不是很了解,我读到SGA和PGA的结构非常不同,例如,chunk的大小不一样等等。

您能告诉我为什么在相同数量的数据下,在PGA中阅读时,与在SGA中阅读时相比,获取的一致性较差吗?

祝你愉快,

大卫·杜布瓦


专家解答

我会按情况返回订单,但让我们从以下设置开始:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> set autotrace traceonly stat
SQL> select owner from t1;

83292 rows selected.


Statistics
---------------------------------------
         13  recursive calls
          7  db block gets
       9731  consistent gets
       1567  physical reads

复制


所以1500读取和10,000一致得到。现在我已经对我的SQLPlus设置做了微小的改变,我将重新运行

SQL> ???????????????
SQL> select owner from t1;

83292 rows selected.


Statistics
---------------------------------------
          0  recursive calls
          0  db block gets
       1735  consistent gets
          0  physical reads
复制


呜呼!从10,000到1735!

因此...这是怎么回事?当我们读取内存中的一个块时,我们需要

-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限

这基本上就是一致的get。

如果我一次要求一行,那么我会这样做:

第1行:
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限

第2行:
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限

等等... 所以我会看到一致的gets = 行数 (大致)。

但是,如果告诉数据库我想要 * 批 * 中的行,我可以这样做:

-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-告诉数据库我将访问此块一段时间
-释放我们的访问权限
-在这一点上,我们允许其他人看到该街区,但我们在其中放了一个标志,上面写着 “使用我,但不要改变我”
-提取大量行 (1、2、3、4...)
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-告诉数据库我已经访问完这个街区了
-释放我们的访问权限

所以现在一个一致的get可以产生很多行。回到我的代码-这是我做的:

SQL> set arraysize 500  <<=============
SQL> select owner from t1;

83292 rows selected.


Statistics
---------------------------------------
          0  recursive calls
          0  db block gets
       1735  consistent gets
          0  physical reads
复制


我告诉数据库我会批量提取500。这让数据库使用第二个更优化的方法来实现一致的获取。原始统计信息的默认数组大小为10

以身作则回到我们的订单。我们正在阅读PGA,对它们进行排序,所以它一次是块 (而不是一次是行),所以我们的一致性将与你在统计数据中看到的块结合在一起。然后,我们从PGA (或临时存储) 检索回我们的客户端,但是到那个阶段,它对我们来说是私有的,而不是来自缓冲区缓存。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论