BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]Exadata虚拟机测试SMART SCAN

2017-07-07

[原创]Exadata虚拟机测试SMART SCAN

安装参考

如何一步步搭建Exadata虚拟机——Cell节点

http://blog.csdn.net/lixora/article/details/8937586

【Oracle】Exadata虚拟机配置(一)

http://blog.csdn.net/badly9/article/details/39677815

搭建测试环境exadata一体机 (vm虚拟机redhat上配置)

http://blog.itpub.net/29500582/viewspace-1410794/

参考:

https://docs.oracle.com/cd/E50790_01/doc/doc.121/e50471/administer.htm#SAGUG20530

测试安装使用

创建ASM表空间及用户test01

SQL> select group_number,name,block_size from v$asm_diskgroup;

GROUP_NUMBER NAME                           BLOCK_SIZE
------------ ------------------------------ ----------
		   1 DATA                                 4096
		   2 FRA                                  4096

SQL> create tablespace my_tbs datafile '+DATA';

Tablespace created.

SQL> SELECT tablespace_name, predicate_evaluation FROM dba_tablespaces;

TABLESPACE_NAME                PREDICA
------------------------------ -------
SYSTEM                         HOST
SYSAUX                         HOST
UNDOTBS1                       HOST
TEMP                           HOST
USERS                          HOST
EXAMPLE                        HOST
MY_TBS                         STORAGE

7 rows selected.

SQL>

SQL> create user test01 identified by test01 default tablespace my_tbs;

User created.

SQL> grant connect,resource to test01;

Grant succeeded.

SQL> grant dba to test01;

Grant succeeded.

SQL> 

创建测试表

测试表sales 大小171M左右

***drop table sales;

create table sales as select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id,  mod(rownum,1000) as cust_id , 5000 as amount_sold, to_date('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2017' ,'dd.mm.yyyy') as time_id from dual connect by level<=2e5; 

select count(*) from sales;

select bytes/1024/1024/1024 as gb from user_segments;


alter table sales nologging;

insert /*+ append */ into sales select * from sales;

commit;

insert /*+ append */ into sales select * from sales;

commit;

insert /*+ append */ into sales select * from sales;

commit;

insert /*+ append */ into sales select * from sales;

commit;

select bytes/1024/1024/1024 as gb from user_segments;

exec dbms_stats.gather_table_stats('TEST01','sales');

大小

SQL> select bytes/1024/1024/1024 as gb from user_segments;

	GB
----------
.171875

SQL> 

测试没有开启和开启SMART SCAN之后执行时间的比较

测试结果如下:8秒 vs 0.73秒 10倍左右

SQL> alter session set cell_offload_processing=false;

Session altered.

SQL> set timing on
SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

  COUNT(*)
----------
	640000

Elapsed: 00:00:07.56
SQL> select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1;

  COUNT(*)
----------
	640000

Elapsed: 00:00:08.19
SQL> set timing off
SQL> 
SQL> 
SQL> alter session set cell_offload_processing=true;

Session altered.

SQL> set timing on
SQL> select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

  COUNT(*)
----------
	640000

Elapsed: 00:00:00.75
SQL> select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1;

  COUNT(*)
----------
	640000

Elapsed: 00:00:00.73
SQL> set timing off
SQL> 

执行过程使用cell使用情况

SQL> select a.name,b.value/1024/1024 MB
from v$sysstat a , v$mystat b
where
a.statistic#=b.statistic#
and (a.name in ('physical read total bytes','physical write total bytes',
'cell IO uncompressed bytes') or a.name like 'cell phy%');  2    3    4    5    6  

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        1621.14844
physical write total bytes                                          168.625
cell physical IO interconnect bytes                              1306.22004
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload               505.875
cell physical IO bytes saved by storage index                             0
cell physical IO interconnect bytes returned by smart scan       22.3216019
cell IO uncompressed bytes                                       506.007813

9 rows selected.

SQL> 

另外执行计划结果比较如下:

注意使用smart scan的执行计划filter部分有 “2 - storage(“CHANNEL_ID”=1)”

SQL> select sql_id from v$sql where sql_text like '%NO_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
81vy8y7fkvzta

SQL>

SQL> select plan_table_output from table (dbms_xplan.display_cursor('81vy8y7fkvzta'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  81vy8y7fkvzta, child number 0
-------------------------------------
select /* NO_SMART_SCAN */ count(*) from sales where channel_id=1

Plan hash value: 1047182207

--------------------------------------------------------------------------------
----

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time
   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT           |       |       |       |  2982 (100)|
   |

|   1 |  SORT AGGREGATE            |       |     1 |     3 |            |
   |

|*  2 |   TABLE ACCESS STORAGE FULL| SALES |   320K|   937K|  2982   (1)| 00:00:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
36 |

--------------------------------------------------------------------------------
----


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=1)


19 rows selected.

SQL> 

SQL> select sql_id from v$sql where sql_text like '%WITH_SMART_SCAN%' and sql_text not like '%like%';

SQL_ID
-------------
09w5f5hxqar4p

SQL> select plan_table_output from table (dbms_xplan.display_cursor('09w5f5hxqar4p'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  09w5f5hxqar4p, child number 0
-------------------------------------
select /* WITH_SMART_SCAN */ count(*) from sales where channel_id=1

Plan hash value: 1047182207

--------------------------------------------------------------------------------
----

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time
   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----

|   0 | SELECT STATEMENT           |       |       |       |  5935 (100)|
   |

|   1 |  SORT AGGREGATE            |       |     1 |     3 |            |
   |

|*  2 |   TABLE ACCESS STORAGE FULL| SALES |   653K|  1915K|  5935   (1)| 00:01:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 |

--------------------------------------------------------------------------------
----


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("CHANNEL_ID"=1)
	   filter("CHANNEL_ID"=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


20 rows selected.

SQL> 

HCC压缩测试

测试脚本如下:

create tablespace host_tbs datafile '/etc/oracle/oradata/orcl/host_tbs01.dbf' size 800m;

create bigfile tablespace cell_hcc_tbs datafile '+DATA' size 1g autoextend on maxsize unlimited default COMPRESS FOR QUERY HIGH extent management local uniform size 4m segment space management auto;

create table tab01 tablespace host_tbs as select 'Oracle Enterprise Edition' as product, mod(rownum,5) as channel_id,  mod(rownum,1000) as cust_id , 1000 as amount_sold, to_date('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2017' ,'dd.mm.yyyy') as time_id from dual connect by level<=2e5 ; 

insert /*+ append */ into tab01 select * from tab01;

commit;

insert /*+ append */ into tab01 select * from tab01;

commit;

insert /*+ append */ into tab01 select * from tab01;

commit;

insert /*+ append */ into tab01 select * from tab01;

commit;

insert /*+ append */ into tab01 select * from tab01;

commit;

exec dbms_stats.gather_table_stats('USER01','TAB01');

create table TAB01_HCC_QH tablespace cell_hcc_tbs as select * from tab01;

exec dbms_stats.gather_table_stats('USER01','TAB01_HCC_QH');

col segment_name format a20

select segment_name, bytes/1024/1024 as MB from dba_segments where segment_name = 'TAB01' or segment_name = 'TAB01_HCC_QH';

select segment_name, bytes from dba_segments where segment_name = 'TAB01' or segment_name = 'TAB01_HCC_QH';

测试结果如下:

USER01@orcl> select segment_name, bytes/1024/1024 as MB from dba_segments where segment_name = 'TAB01' or segment_name = 'TAB01_HCC_QH';

SEGMENT_NAME                 MB
-------------------- ----------
TAB01                       792
TAB01_HCC_QH                  4

USER01@orcl>

USER01@orcl> select segment_name, bytes from dba_segments where segment_name = 'TAB01' or segment_name = 'TAB01_HCC_QH';

SEGMENT_NAME              BYTES
-------------------- ----------
TAB01                 830472192
TAB01_HCC_QH            4194304

USER01@orcl> select 830472192/4194304 from dual;

830472192/4194304
-----------------
			  198

USER01@orcl>

792M的大小压缩到4M大小,压缩比有198倍!!!!

** 由于表的内容相同内容太多的缘故,之后再找不同测试样例

后期待续

~ 后期待续 ~


Similar Posts

Comments