问题描述
你好,汤姆,
当我使用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中阅读时相比,获取的一致性较差吗?
祝你愉快,
大卫·杜布瓦
专家解答
我会按情况返回订单,但让我们从以下设置开始:
所以1500读取和10,000一致得到。现在我已经对我的SQLPlus设置做了微小的改变,我将重新运行
呜呼!从10,000到1735!
因此...这是怎么回事?当我们读取内存中的一个块时,我们需要
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限
这基本上就是一致的get。
如果我一次要求一行,那么我会这样做:
第1行:
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限
第2行:
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-提取我们想要的行
-释放我们的访问权限
等等... 所以我会看到一致的gets = 行数 (大致)。
但是,如果告诉数据库我想要 * 批 * 中的行,我可以这样做:
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-告诉数据库我将访问此块一段时间
-释放我们的访问权限
-在这一点上,我们允许其他人看到该街区,但我们在其中放了一个标志,上面写着 “使用我,但不要改变我”
-提取大量行 (1、2、3、4...)
-锁定对该块的访问权限 (即,当我们阅读它时,没有人会搞砸)
-告诉数据库我已经访问完这个街区了
-释放我们的访问权限
所以现在一个一致的get可以产生很多行。回到我的代码-这是我做的:
我告诉数据库我会批量提取500。这让数据库使用第二个更优化的方法来实现一致的获取。原始统计信息的默认数组大小为10
以身作则回到我们的订单。我们正在阅读PGA,对它们进行排序,所以它一次是块 (而不是一次是行),所以我们的一致性将与你在统计数据中看到的块结合在一起。然后,我们从PGA (或临时存储) 检索回我们的客户端,但是到那个阶段,它对我们来说是私有的,而不是来自缓冲区缓存。
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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
相关阅读
Oracle RAC 一键安装翻车?手把手教你如何排错!
Lucifer三思而后行
595次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布,如何快速升级和安装?
Lucifer三思而后行
561次阅读
2025-04-18 14:18:38
XTTS跨版本迁移升级方案(11g to 19c RAC for Linux)
zwtian
482次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果,免费脚本速来下载!
陈举超
472次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
457次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
430次阅读
2025-04-22 00:13:51
Oracle 19c RAC更换IP实战,运维必看!
szrsu
430次阅读
2025-04-08 23:57:08
【活动】分享你的压箱底干货文档,三篇解锁进阶奖励!
墨天轮编辑部
414次阅读
2025-04-17 17:02:24
火焰图--分析复杂SQL执行计划的利器
听见风的声音
358次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
356次阅读
2025-04-15 14:48:05