使用全表扫描缓存大表的相关问题

最新推荐文章于 2021-01-19 01:23:06 发布
最新推荐文章于 2021-01-19 01:23:06 发布
阅读量140 收藏
点赞数
文章标签： 数据库

问题:
有一个批量操作，需要依次更新一个大表的大部分数据，而数据库buffer cache足够大，可以容纳该表.
是否可以通过并行全表扫描(db scattered read)将该表读入buffer cache，避免单块读取(db sequential read)导致的大量物理IO?
后者物理读次数是前者物理读次数的db_file_multiblock_read_count(缺省为16)倍.

为了验证该方案，需要明确如下问题，在全表扫描时:
(1)将表的数据块缓存到buffer cache还是PGA?是否使用多块读？
(2)使用parallel提示情况下，缓存到buffer cache还是PGA?是否使用多块读？

1 测试表准备
create table t
as
select * from dba_objects;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as pg@dev95
 
SQL>
SQL> select bytes，blocks，extents from user_segments where segment_name='T';
 
     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
   9437184       1152         24
SQL> select extent_id，bytes，blocks from user_extents where segment_name='T';
 
 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
 
24 rows selected

SQL> select object_id，data_object_id from user_objects where object_name='T';
 
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    155073         155073
 
SQL> select file#，block#，class#，status from v$bh where bjd=155073;
 
     FILE#     BLOCK#     CLASS# STATUS
---------- ---------- ---------- -------
         6     253578          8 xcur
        16      56457          8 xcur
        12      30649          8 xcur
        15      57481          8 xcur
        12      30665          8 xcur
        20      26890          8 xcur
        19      23306          8 xcur
        15      57497          8 xcur
        12      30681          8 xcur
        18      44682          8 xcur
        12      30634          9 xcur
         7     160905          8 xcur
        12      30697          8 xcur
        17     555273          8 xcur
        16      56458          8 xcur
         8     126217          8 xcur
        12      30713          8 xcur
         6     253577          8 xcur
        12      30635          4 xcur
         7     160906          8 xcur
        17     555274          8 xcur
        20      26889          8 xcur
        19      23305          8 xcur
         8     126218          8 xcur
        18      44681          8 xcur
        12      30633          8 xcur
 
26 rows selected

2 全表扫描缓存到buffer cache还是pga？是否使用多块读？
结论：缓存到buffer cache；使用了多块读。

SQL>
SQL> alter system flush shared_pool;
 
System altered
SQL> alter system flush buffer_cache;
 
System altered
 
SQL>
SQL>
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
free          1026
SQL> select statistic_name，value
  2    from v$segment_statistics ss
  3   where wner = user
  4     and object_name = 'T'
  5     and value>0
  6  order by 2;
segment scans                                                             1
db block changes                                                        256
physical writes direct                                                 1125
physical reads                                                         1126
physical writes                                                        1151
logical reads                                                          2704
space used                                                          9216000
space allocated                                                     9437184
 
8 rows selected
SQL> select sid， name， value
  2    from v$mystat ses， v$statname sn
  3   where ses.STATISTIC# = sn.STATISTIC#
  4     and value > 0
  5     and name like '%read%'
  6   order by 3;
       822 physical read total multi block requests                                 95
       822 physical read total IO requests                                         694
       822 physical read IO requests                                               694
       822 physical reads cache prefetch                                          1018
       822 physical reads                                                         1712
       822 physical reads cache                                                   1712
       822 no work - consistent read gets                                       641666
       822 session logical reads                                               1094037
       822 physical read total bytes                                          14024704
       822 physical read bytes                                                14024704
 
10 rows selected
SQL> select event， total_waits
  2    from v$session_event
  3   where sid = 822
  4     and wait_class = 'User I/O'
  5   order by 1;
db file scattered read                                                    95
db file sequential read                                                  606
 
SQL> select count(*) from t;
     80677
 
SQL> select sql_id，disk_reads，direct_writes，user_io_wait_time，buffer_gets，sql_text from v$sql where sql_text like '%count(*) from t%';
a3uxj45fdjv7m       1128             0             69853        1199  select count(*) from t
 
==〉v$sql.disk_reads:Number of disk reads for this child cursor


SQL> select * from table(dbms_xplan.display_cursor('a3uxj45fdjv7m'));
SQL_ID  a3uxj45fdjv7m， child number 0
-------------------------------------
 select count(*) from t
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |    93 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 80442 |    93   (3)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
 
18 rows selected


SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
xcur          1126  free           788
cr               1
==〉xcur：1126，说明缓存到了buffer cache

SQL> select statistic_name，value
  2    from v$segment_statistics ss
  3   where wner = user
  4     and object_name = 'T'
  5     and value>0
  6  order by 2;
segment scans                                                             1
db block changes                                                        256
physical writes direct                                                 1125
physical writes                                                        1151
physical reads                                                         2252 logical reads                                                          3904
space used                                                          9216000
space allocated                                                     9437184
 
8 rows selected
SQL> select sid， name， value
  2    from v$mystat ses， v$statname sn
  3   where ses.STATISTIC# = sn.STATISTIC#
  4     and value > 0
  5     and name like '%read%'
  6   order by 3;
       822 physical read total multi block requests                                189        822 physical read total IO requests                                         822        822 physical read IO requests                                               822        822 physical reads cache prefetch                                          2032        822 physical reads                                                         2854        822 physical reads cache                                                   2854        822 no work - consistent read gets                                       648919
       822 session logical reads                                               1107445
       822 physical read total bytes                                          23379968
       822 physical read bytes                                                23379968
 
==〉physical read total multi block requests：94，说明使用了多块读

SQL> select 94*16 from dual;
      1504
 
SQL> select event， total_waits
  2    from v$session_event
  3   where sid = 822
  4     and wait_class = 'User I/O'
  5   order by 1;
db file scattered read                                                   189 db file sequential read                                                  633

==〉db file scattered read：94，说明使用了多块读


3，使用并行提示，全表扫描缓存到buffer cache还是pga？是否使用多块读？
==> 读入pga；使用了多块读。

SQL> alter system flush shared_pool;
 
System altered
 
SQL> alter system flush buffer_cache;
 
System altered
 
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
free          1232
 
SQL>
SQL> select statistic_name，value
  2    from v$segment_statistics ss
  3   where wner = user
  4     and object_name = 'T'
  5     and value>0
  6  order by 2;
segment scans                                                             1
db block changes                                                        256
physical writes direct                                                 1125
physical writes                                                        1151
physical reads                                                         3388
logical reads                                                          7376
space used                                                          9216000
space allocated                                                     9437184
 
8 rows selected
SQL> select sid， name， value
  2    from v$mystat ses， v$statname sn
  3   where ses.STATISTIC# = sn.STATISTIC#
  4     and value > 0
  5     and name like '%read%'
  6   order by 3;
       822 physical read total multi block requests                                272
       822 physical read total IO requests                                        1546
       822 physical read IO requests                                              1546
       822 physical reads cache prefetch                                          3094
       822 physical reads                                                         4640
       822 physical reads cache                                                   4640
       822 no work - consistent read gets                                       671268
       822 session logical reads                                               1153284
       822 physical read total bytes                                          38010880
       822 physical read bytes                                                38010880
 
10 rows selected
SQL> select event， total_waits
  2    from v$session_event
  3   where sid = 822
  4     and wait_class = 'User I/O'
  5   order by 1;
db file scattered read                                                   272
db file sequential read                                                 1278
 
SQL> select /*+ parallel(t 8) */ count(*) from t;
     80677
 
SQL> select sql_id，disk_reads，direct_writes，user_io_wait_time，buffer_gets，sql_text from v$sql where sql_text like '%count(*) from t%';
6861j0dxkvvr4       1136             0              1056        1388  select /*+ parallel(t 8) */ count(*) from t
 
SQL> select * from table(dbms_xplan.display_cursor('6861j0dxkvvr4'));
SQL_ID  6861j0dxkvvr4， child number 0
 select /*+ parallel(t 8) */ count(*) from t
NOTE: cannot fetch plan for SQL_ID: 6861j0dxkvvr4， CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
 
8 rows selected
SQL> explain plan for select /*+ parallel(t 8) */ count(*) from t
  2  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3126468333
--------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |
|   2 |   PX COORDINATOR       |          |       |            |          |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,
|   5 |      PX BLOCK ITERATOR |          | 80677 |    13   (0)| 00:00:01 |  Q1,
|   6 |       TABLE ACCESS FULL| T        | 80677 |    13   (0)| 00:00:01 |  Q1,
--------------------------------------------------------------------------------
 
13 rows selected
 
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
xcur             1
free           720
==〉xcur：1，说明缓存到了pga
 
SQL>
SQL> select statistic_name，value
  2    from v$segment_statistics ss
  3   where wner = user
  4     and object_name = 'T'
  5     and value>0
  6  order by 2;
segment scans                                                           108
db block changes                                                        256
physical reads direct                                                  1125  physical writes direct                                                 1125
physical writes                                                        1151 
physical reads                                                         4514  logical reads                                                          8720
space used                                                          9216000
space allocated                                                     9437184
==〉physical reads direct：1125，说明使用了直接读


SQL> select sid， name， value
  2    from v$mystat ses， v$statname sn
  3   where ses.STATISTIC# = sn.STATISTIC#
  4     and value > 0
  5     and name like '%read%'
  6   order by 3;
       822 table scans (direct read)                                               107        822 physical read total multi block requests                                384        822 physical reads direct                                                  1125        822 physical read total IO requests                                        1733        822 physical read IO requests                                              1733        822 physical reads cache prefetch                                          3094
       822 physical reads cache                                                   4714
       822 physical reads                                                         5839        822 no work - consistent read gets                                       678775
       822 session logical reads                                               1167932
       822 physical read total bytes                                          47833088
       822 physical read bytes                                                47833088
 
==〉physical read total multi block requests：112，说明使用了多块读

SQL> select event， total_waits
  2    from v$session_event
  3   where sid = 822
  4     and wait_class = 'User I/O'
  5   order by 1;
db file scattered read                                                   272
db file sequential read                                                 1348

==〉db file scattered read：0，说明没有使用多块读或这种情况下的多块读不计入该事件计数。


4，遗留问题：
表上的cache属性，可以控制在全表扫描时把数据放置在LRU的热端。为了缓存大表，该属性是否影响缓存效果？

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-754214/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-754214/

cuian7084
关注
oracle 全表扫描和索引扫描
Ronin_88的博客
06-18 5450
1) 全表扫描（Full Table Scans, FTS）        为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read...
Mysql缓存-全表扫描过程、LRU算法
qq_35519587的博客
04-25 1117
在日常开发中，常因为分页查询的使用，或者SQL的语法问题导致的全表扫描，这带来的问题可能会有系统的OOM，或者频繁的GC，以及慢SQL，数据返回慢等问题。那么我们如果光从内存这个角度看这个全表扫描的问题，他是怎么样进行查询返回结果的呢？ 假设我的主机内存就几个G，但是全表的数据可能是它的几倍，那么机器的内存会直接用光吗？ 结合日常的开发工作，对大表做全表扫描，看来应该是没问题的(只看数据而言)。但...
在Buffer Cache中自动大表缓存
cuchou5321的博客
08-05 147
约翰，在A银行做DBA。凯特琳，新的首席技术官，她正在为数据库性能问题由于长的I/O响应时间而沮丧。为了提高性能，她希望增加数据库实例的缓冲区缓存，以便在缓存中缓存更多的数据，从而减少了频繁需要去读磁盘。戴比，系统开发工程...
MySQL的InnoDB缓冲池和Checkpoint
程序猿的世界
11-23 633
目的 解决如下问题： 缩短数据库的恢复时间（如果没有checkpoint，redo日志会非常大） 缓冲池不够用时，将脏页刷新到磁盘 重做日志不可用时，刷新脏页 Sharp Checkpoint 发生在数据库关闭时将所有脏页刷新回磁盘，防止数据丢失，由参数innodb_fast_shutdown=1控制 Fuzzy Checkpoint 有四种情况: Master Thread Ch...
各种缓存介绍
热门推荐
Jesse_cool的博客
07-27 2万+
说起缓存相关技术，老多了， memcache、redis、squid、varnish、web cache、 CDN等等。缓存技术五花八门，但这些技术间有什么共性的地方，又有什么不同的地方呢？答案肯定是有的，这次为大家分享及整理一下缓存方面的技术，主要分为三个系列展开： 缓存随谈系列之一：数据库缓存 缓存随谈系列之二：静态缓存 缓存随谈系列之三：动态缓存 一、什么
大幅提升MySQL中InnoDB的全表扫描速度的方法
09-10
为了解决这个问题，Facebook的数据库工程师团队引入了一项名为"Logical Readahead"的功能，显著提升了全表扫描的速度。 通常，InnoDB在进行全表扫描时，并非按照物理顺序读取数据，而是进行随机读取，这在硬盘驱动...
不使用全表扫描取数据前10条
03-26
要避免全表扫描，可以使用以下方法： 1. 使用索引：如果存在索引，可以使用索引来快速获取数据，而不需要扫描整个表。例如，如果有一个名为`idx_score`的分数索引，可以使用以下查询来获取前10名的学生： ```sql ...
浅析MySQL内存的使用说明（全局缓存+线程缓存）
12-15
2. `read_buffer_size`：当进行表的顺序扫描时，MySQL会使用此缓冲，增加其大小可以提高顺序扫描的速度。 3. `read_rnd_buffer_size`：用于随机读取数据时的缓冲，当需要非顺序的数据访问时，这很有帮助。 4. `sort_...
MySQL千万级大表深度分页为什么慢，以及优化的方法、原理
最新发布
05-20
这是因为MySQL的查询优化器在面对大量数据的分页请求时，可能选择全表扫描而不是利用索引来提高效率。这个问题在描述中的例子中得到了体现，当尝试获取数据集的远端分页时，查询速度显著降低。 1. **分页查询原理：...
最全面的缓存架构设计（全是干货）
zjttlance的专栏
05-08 2万+
最全面的缓存架构设计（全是干货）程序员的日常那些事 2018-05-07 17:53:401：缓存技术和框架的重要性互联网的一些高并发，高性能的项目和系统中，缓存技术是起着功不可没的作用。缓存不仅仅是key-value的简单存取，它在具体的业务场景中，还是很复杂的，需要很强的架构设计能力。我曾经就遇到过因为缓存架构设计不到位，导致了系统崩溃的案例。2：缓存的技术方案分类1）是做实时性比较高的那块数...
高性能mysql（四）缓存表和计数器表
国苏
05-21 4377
缓存表（汇总表）假如统计一个网站23小时发出的消息数，在一个比较忙碌的网站下不可能随时维护一个精准的计数器。代替方案是每小时生成一张汇总表，这样比实时计算要高效的多。简单的说就是维护一些复杂 耗时的计算 那么用汇总表是比较好的选择。计数器表要计数我们可以创建一个计数器表。create table hit_counter(cnt int unsigend not null)engine=innodb;
工作笔记-使用Redis作为Mysql数据库的缓存
马路上开飞机
11-22 1524
  有一个新需求，需要使用Redis做为Mysql的缓存，需要做全表缓存。 Redis是K/V的简单键值对存储形式，MySQL是传统关系型数据库，是一维和二维的区别，怎么把表映射到Redis中呢。 Redis对数据结构有很丰富的支持，使用String-Map作为Redis的Key-Value是一个很好的方法，Map中的key对应Mysql中的列名，一个Map就对应Mysql中的一行数据。再使...
缓存表 内存表(将表keep到内存)
mandy_rxr的专栏
10-27 902
缓存表 内存表(将表keep到内存) 一、引言： 有时候一些基础表需要非常的频繁访问，尤其是在一些循环中，对该表中的访问速度将变的非常重要。为了提高系统的处理性能，可以考虑将一些表及索引读取并保存到内存中。 二、关于keep内存的几个参数 下面了解一下具体和CACHE有关的几个概念，即DB_CACHE中的几个pool： DB_CACHE_SIZE：...
本地全量缓存
petershuang的博客
12-18 1014
全量缓存 背景及目标 当前我们的数据流向是这样的:Hdfs->redis->程序内部缓存->缓存。存在redis性能不足。读写过于频繁。写频繁主要是从hdfs一条一条的读取， 没有批量写入。因为程序还不太稳定， 经常会重启，内部缓存被清空， 内存不大命中率低等原因， 导致读取效率不高。所以我们需要一个缓存解决以上这些问题，它得具有这些特征: 1.1 独立进程 --- 解决引擎...
Oracle 全表扫描及其执行计划(full table scan)
乐沙弥的世界
05-24 1万+
全表扫描是Oracle访问数据库表是较为常见的访问方式之一。很多朋友一看到SQL语句执行计划中的全表扫描，就要考虑对其进行修理一番。全表扫描的存在，的确存在可能优化的余地。但事实上很多时候全表扫描也并非是最低效的，完全要看不同的情形与场合，任一方式都是有利有弊的，也就是具体情况要具体分析。本文描述了什么是全表扫描以及何时发生全表扫描，何时全表扫描才低效。  本文涉及到的相关链接：     高水位线
mysql缓存优化_MySQL优化之缓存优化
weixin_36463131的博客
01-19 629
1. MySQL缓存为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。当我们的数据库打开了Query Cache(简称QC)功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。但一个缓存机制是否有效，效果如何...
【oracle调优】优化全表扫---cache整张表或索引
践行见远
10-08 1855
情形解析： 若 某查询必须要走全表扫描 该查询执行的频率相当高 对执行时间的要求也相当苛刻的话 数据库的IO比较吃紧 数据库的内存比较宽松 该表的大小没有大到离谱   这种情况下，可以使用“把整张表放到内存里”的方式进行优化。 操作方法： --缓存wjf_test表 alter table wjf_test storage(buffer_pool keep) ; al
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值