各种读取方式对CPU的消耗

最新推荐文章于 2020-11-14 08:15:40 发布

cotchte0421

最新推荐文章于 2020-11-14 08:15:40 发布

阅读量478

点赞数

文章标签：数据库

表大小 136 BLOCKS 17,408

cpu_time代表百万分之一秒

读取方式	CPU消耗
in-memory	7999
buffer cache	19997
direct path read	46993
db file scatterd read	133980

in-memory对cpu的消耗对比其他几种方式，CPU消耗最小

大体的测量方法：

@size
Enter value for segment_name: test
Enter value for owner: test
old   2: from dba_segments where segment_name=UPPER('&segment_name') and owner = upper('&owner') order by 1
new   2: from dba_segments where segment_name=UPPER('test') and owner = upper('test') order by 1

OWNER           SEGMENT_NAME                  PARTITION_NAME                 TABLESPACE_NAME                     SIZE_M       BLOCKS
--------------- ----------------------------- ------------------------------ ----------------------------- ------------ ------------
TEST            TEST                                                         USERS                                  136       17,408

表cache

alter table test cache;

select count(*)from test;

in-memory

test@WXHPDB1>select 27996 - 19997 from dual;

27996-19997
-----------
7999

direct path read

清空buffer_cache,shared_pool

select count(*)from test;

想知道后台是什么等待事件，可以通过 v$active_session_history session_id;查询

db file scatterd read

调大_small_table_threshold，清空buffer_cache,shared_pool

select count(*)from test;