使用Vagrant Boxes来搜索你想要的环境

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用VAGRANT BOXES来搜索你想要的环境
 
自从有了Vagrant之后,安装各类环境我一直都采用它,昨天突然想安装一个PostgreSQL。现在开源的产品特别多,要是一个一个的去安装操作系统,安装软件,是非常耗时的,还好Vagrant能很好的帮助我们解决这类问题。但是我们如何寻找已经配置好的环境呢?我们可以通过官方网站的Vagrant Boxes来找。

进入到Boxes之后,我们可以搜索,比如我想安装pg11,我就搜到了一个在debain环境下安装的pg11.

当然这里要和你的虚拟机搭配,比如我搜索的这个就是VirtualBox环境。点到虚拟机里面,然后就看到下面两条命令,执行一下,就可以把环境拖下来搭建好。

执行命令就可以搭建了。

C:\Users\Administrator>cd /d G:\VM DATA\vagrant-debain
G:\VM DATA\vagrant-debain>vagrant init jurafejfar/debian-9.5-pg11
G:\VM DATA\vagrant-debain>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'jurafejfar/debian-9.5-pg11' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'jurafejfar/debian-9.5-pg11'
    default: URL: https://vagrantcloud.com/jurafejfar/debian-9.5-pg11
==> default: Adding box 'jurafejfar/debian-9.5-pg11' (v2018-10-17) for provider: virtualbox
    default: Downloading: https://vagrantcloud.com/jurafejfar/boxes/debian-9.5-pg11/versions/2018-10-17/providers/virtualbox.box
==> default: Box download is resuming from prior download progress
    default: Download redirected to host: vagrantcloud-files-production.s3.amazonaws.com
    default: Progress: 100% (Rate: 1000k/s, Estimated time remaining: --:--:--)
==> default: Successfully added box 'jurafejfar/debian-9.5-pg11' (v2018-10-17) for 'virtualbox'!
==> default: Importing base box 'jurafejfar/debian-9.5-pg11'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'jurafejfar/debian-9.5-pg11' is up to date...
==> default: Setting the name of the VM: vagrant-debain_default_1539794274257_97733
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default:
    default: Vagrant insecure key detected. Vagrant will automatically replace
    default: this with a newly generated keypair for better security.
    default:
    default: Inserting generated public key within guest...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: The guest additions on this VM do not match the installed version of
    default: VirtualBox! In most cases this is fine, but in rare cases it can
    default: prevent things such as shared folders from working properly. If you see
    default: shared folder errors, please make sure the guest additions within the
    default: virtual machine match the version of VirtualBox you have installed on
    default: your host and reload your VM.
    default:
    default: Guest Additions Version: 5.2.20
    default: VirtualBox Version: 5.1
==> default: Mounting shared folders...
    default: /vagrant => G:/VM DATA/vagrant-debain

然后配置下网络,在Vagrantfile文件中添加“config.vm.network “private_network”, ip: “192.168.56.90””就可以通过CRT进行连接了。

以后再也不同折腾各种环境了!

使用dbms_compression.get_compression_ratio评估压缩比例

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用dbms_compression.get_compression_ratio评估压缩比例

DBMS_COMPRESSION是用于管理Oracle数据库表压缩功能的包。它通过消除磁盘页中的重复值来压缩数据。随着数据库大小的增加,表压缩可以节省资源,它不仅可以节省磁盘,还可以减少缓冲区缓存中的内存使用量。DBMS_COMPRESSION是在11gR2中引入,这个软件包一直在不断发展,现在在12c版本中引入了一些非常有用的增强功能。我们来试用一下。

首先创建一个用户和表用于测试。

SQL> alter session set container=ORCLPDB1;
Session altered.

SQL> create user test1 identified by test1;
User created.

SQL> grant dba to test1;
Grant succeeded.

SQL> create tablespace testtbs datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/testtbs.dbf' size 100m autoextend on next 100m maxsize unlimited;
Tablespace created.

SQL> alter user test1 quota unlimited on testtbs;
User altered

SQL> connect test1/test1@ORCLPDB1
Connected.

SQL> create table testtab tablespace testtbs as select rownum id, a.* from dba_objects a where 1 = 0;
Table created.

接下来我们往表里面插入一百万数据。

declare
  l_cnt  number;
  l_rows number := 1000000;
begin
  insert /*+ append */ into testtab select rownum, a.* from dba_objects a;
  l_cnt := sql%rowcount;
  commit;
  while (l_cnt < l_rows) loop
     insert /*+ append */ into testtab
     select rownum+l_cnt, owner,object_name,subobject_name,object_id,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,
     secondary,namespace,edition_name,sharing,editionable,oracle_maintained,application,default_collation,duplicated,sharded,created_appid,created_vsnid,modified_appid,modified_vsnid
       from testtab a
      where rownum <= l_rows-l_cnt;
     l_cnt := l_cnt + sql%rowcount;
     commit;
  end loop;
  commit;
end;
/

让我们查询dba_segments目录视图,可以看到分配了160MB。

SQL> SELECT segment_name, SUM(bytes)/1024/1024 MB FROM dba_segments WHERE segment_name='TESTTAB' GROUP BY segment_name;

SEGMENT         MB
------- ----------
TESTTAB        160

现在在12c可以在使用dbms_compression包得到每个块的估计行数。DBMS_COMPRESSION包使用不同的数字常量来描述每种类型的可能压缩,现在可以从1到1000000,具体可以参考官方文档,如下列表所示:

当选择(comptype = 2)的时候执行的是COMP_ADVANCED。具体如下所示:

set serveroutput on
declare
  v_blocks_comp   pls_integer;
  v_blocks_uncomp   pls_integer;
  v_rows_comp   pls_integer;
  v_rows_uncomp   pls_integer;
  v_compress_ratio  number;
  v_compress_type   varchar2(32767);
begin
  dbms_compression.get_compression_ratio (
   scratchtbsname  => 'testtbs',
   ownname=>'test1',
   objname=>'testtab',
   subobjname=> null,
   comptype=> 2,
   blkcnt_cmp=> v_blocks_comp,
   blkcnt_uncmp=> v_blocks_uncomp,
   row_cmp=>v_rows_comp,
   row_uncmp=>v_rows_uncomp,
   cmp_ratio=>v_compress_ratio,
   comptype_str=>v_compress_type,
   subset_numrows=>dbms_compression.comp_ratio_minrows
  ) ;
  dbms_output.put_line('output: ');
  dbms_output.put_line('estimated compression ratio: ' || v_compress_ratio);
  dbms_output.put_line('blocks used - compressed sample: ' || v_blocks_comp);
  dbms_output.put_line('blocks used - uncompressed sample: ' || v_blocks_uncomp);
  dbms_output.put_line('rows in a block - compressed sample: ' || v_rows_comp);
  dbms_output.put_line('rows in a block - uncompressed sample: ' || v_rows_uncomp);
end;
/
Estimated Compression Ratio: 2.6
Blocks used - compressed sample: 915
Blocks used - uncompressed sample: 2432
Rows in a block - compressed sample: 129
Rows in a block - uncompressed sample: 48

这里可以计算出压缩比例未2.6%,使用这个级别的压缩可以使blocks从2432(uncompressed)到915(compressed),压缩前是每个block存储48行,到压缩之后可以存储129行。
我们可以再次执行上述查询,把comptype=> 2修改成4,4是HCC Query High压缩方式。

结果如下:

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000000 rows
OUTPUT:
Estimated Compression Ratio: 23.1
Blocks used - compressed sample: 872
Blocks used - uncompressed sample: 20192
Rows in a block - compressed sample: 1147
Rows in a block - uncompressed sample: 49

可以看到,压缩比例提升到23.1%,使用这个级别的压缩可以使blocks从20192(uncompressed)到872(compressed),压缩前是每个block存储49行,到压缩之后可以存储1147行。当然上面的提示也很明显,是做的EHCC压缩。当然这需要在Exadata才能压缩出这种比例。

RedHat7.4上安装SQL Server 2017

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:REDHAT7.4上安装SQL SERVER 2017

今天测试了一下在RedHat7.4上安装SQL Server 2017,整个安装过程并不复杂。以下是安装过程的详细步骤。
1.检查CPU和内存,CPU至少2个核心,内存2GB以上,磁盘空间大于6GB,如图所示:

[root@10 ~]# grep -i --color "model name" /proc/cpuinfo
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz

[root@10 ~]# grep -i --color "MemTotal" /proc/meminfo
MemTotal: 4046612 kB

[root@10 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 42G 4.0G 38G 10% /
devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 9.5M 2.0G 1% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 497M 153M 344M 31% /boot
tmpfs 396M 4.0K 396M 1% /run/user/992
tmpfs 396M 48K 396M 1% /run/user/1000
tmpfs 396M 0 396M 0% /run/user/0

2.下载Microsoft SQL Server 2017 Red Hat存储库配置文件:

[root@10 ~]# sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 232 100 232 0 0 778 0 --:--:-- --:--:-- --:--:-- 781

3.运行yum安装sql server

[root@10 ~]# yum install -y mssql-server
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
packages-microsoft-com-mssql-server-2017 | 2.9 kB 00:00:00
packages-microsoft-com-mssql-server-2017/primary_db | 16 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3038.14-2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================================================================================================
Installing:
mssql-server x86_64 14.0.3038.14-2 packages-microsoft-com-mssql-server-2017 169 M

Transaction Summary
================================================================================================================================================================================================================================================================
Install 1 Package

Total download size: 169 M
Installed size: 169 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/packages-microsoft-com-mssql-server-2017/packages/mssql-server-14.0.3038.14-2.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY====================================== ] 5.7 MB/s | 168 MB 00:00:00 ETA
Public key for mssql-server-14.0.3038.14-2.x86_64.rpm is not installed
mssql-server-14.0.3038.14-2.x86_64.rpm | 169 MB 00:00:30
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
Userid : "Microsoft (Release signing) <gpgsecurity@microsoft.com>"
Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
From : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-14.0.3038.14-2.x86_64 1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
Verifying : mssql-server-14.0.3038.14-2.x86_64 1/1

Installed:
mssql-server.x86_64 0:14.0.3038.14-2

4.运行SQL Server配置脚本(/opt/mssql/bin/mssql-conf),这里选择安装的版本,因为我们是测试,就选择了Developer版。同时还需要设置SA的密码,密码规则是强密码,需要最少8个字符,包括大写和小写字母、数字和/或非字母数字符号。

[root@10 bin]# pwd
/opt/mssql/bin
[root@10 bin]# ./mssql-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:
The specified password contains an invalid character. Valid characters include uppercase letters, lowercase letters, numbers, symbols, punctuation marks, and unicode characters that are categorized as alphabetic but are not uppercase or lowercase.
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

5.查看mssql服务的状态,目前是running的。

systemctl status mssql-server
[root@10 bin]# systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2018-10-15 23:02:15 CST; 3min 27s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 4375 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─4375 /opt/mssql/bin/sqlservr
└─4414 /opt/mssql/bin/sqlservr

Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.81 spid11s Polybase feature disabled.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.82 spid11s Clearing tempdb database.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.84 spid6s 8 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.90 spid6s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.13 spid11s Starting up database 'tempdb'.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.34 spid11s The tempdb database has 1 data file(s).
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.36 spid22s The Service Broker endpoint is in disabled or stopped state.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.36 spid22s The Database Mirroring endpoint is in disabled or stopped state.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.40 spid22s Service Broker manager has started.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.41 spid6s Recovery is complete. This is an informational message only. No user action is required.

6.默认SQL Server通过1433端口连接,此时要检查防火墙状态,如果是打开的,需要把1433端口在防火墙上做放开设置,允许连接。或者直接关闭Linux防火墙。我在这里直接关闭了防火墙。

[root@10 bin]# firewall-cmd --state
running
[root@10 bin]# systemctl stop firewalld.service
[root@10 bin]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

7.接下来需要安装sqlcmd和bcp等客户端工具。和安装服务器一样,首先也要下载配置文件。

[root@10 bin]# sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 682 0 --:--:-- --:--:-- --:--:-- 681

接下来如果有旧版的mssql工具,需要删除。

[root@10 bin]# yum remove unixODBC-utf16 unixODBC-utf16-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
No Match for argument: unixODBC-utf16
No Match for argument: unixODBC-utf16-devel
No Packages marked for removal

删除完成之后安装新的工具,在安装过程中出现下列的问题:

[root@10 bin]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 144 kB 00:00:00
No package unixODBC-devel available.
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.2.0.2-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.3.0.0 for package: mssql-tools-17.2.0.2-1.x86_64 --> Processing Dependency: msodbcsql17 >= 17.2.0.0 for package: mssql-tools-17.2.0.2-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.2.0.1-1.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql17-17.2.0.1-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.2.0.1-1.x86_64
---> Package unixODBC-utf16.x86_64 0:2.3.1-1 will be installed
--> Processing Conflict: msodbcsql17-17.2.0.1-1.x86_64 conflicts unixODBC-utf16
--> Finished Dependency Resolution
Error: msodbcsql17 conflicts with unixODBC-utf16-2.3.1-1.x86_64
Error: Package: msodbcsql17-17.2.0.1-1.x86_64 (packages-microsoft-com-prod)
Requires: unixODBC >= 2.3.1
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

下列问题主要是因为软件的yum仓库不可用,百度了办法修改成了163的centos的yum仓库就可以了。

[root@10 run]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.2.0.2-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.3.0.0 for package: mssql-tools-17.2.0.2-1.x86_64 --> Processing Dependency: msodbcsql17 >= 17.2.0.0 for package: mssql-tools-17.2.0.2-1.x86_64
---> Package unixODBC-devel.x86_64 0:2.3.1-11.el7 will be installed
--> Processing Dependency: unixODBC(x86-64) = 2.3.1-11.el7 for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtemplate.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtdsS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libsapdbS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboraodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboplodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbctxtS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcnnS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcminiS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg2S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg1S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbccr.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libnn.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libmimerS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libesoobS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================================================================================================
Installing:
mssql-tools x86_64 17.2.0.2-1 packages-microsoft-com-prod 254 k
unixODBC-devel x86_64 2.3.1-11.el7 base 55 k
Installing for dependencies:
msodbcsql17 x86_64 17.2.0.1-1 packages-microsoft-com-prod 4.3 M
unixODBC x86_64 2.3.1-11.el7 base 413 k

Transaction Summary
================================================================================================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)

Total download size: 5.1 M
Installed size: 6.0 M
Downloading packages:
warning: /var/cache/yum/x86_64/$releasever/base/packages/unixODBC-devel-2.3.1-11.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for unixODBC-devel-2.3.1-11.el7.x86_64.rpm is not installed
(1/4): unixODBC-devel-2.3.1-11.el7.x86_64.rpm | 55 kB 00:00:00
(2/4): unixODBC-2.3.1-11.el7.x86_64.rpm | 413 kB 00:00:00
(3/4): mssql-tools-17.2.0.2-1.x86_64.rpm | 254 kB 00:00:00
(4/4): msodbcsql17-17.2.0.1-1.x86_64.rpm | 4.3 MB 00:00:01
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.4 MB/s | 5.1 MB 00:00:01
Retrieving key from http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
From : http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
rhn-check-2.0.2-17.el7.noarch has missing requires of yum-rhn-plugin >= ('0', '1.6.4', '1')
Installing : unixODBC-2.3.1-11.el7.x86_64 1/4
The license terms for this product can be downloaded from
https://aka.ms/odbc172eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : msodbcsql17-17.2.0.1-1.x86_64 2/4
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : mssql-tools-17.2.0.2-1.x86_64 3/4
Installing : unixODBC-devel-2.3.1-11.el7.x86_64 4/4
Verifying : unixODBC-devel-2.3.1-11.el7.x86_64 1/4
Verifying : unixODBC-2.3.1-11.el7.x86_64 2/4
Verifying : msodbcsql17-17.2.0.1-1.x86_64 3/4
Verifying : mssql-tools-17.2.0.2-1.x86_64 4/4

Installed:
mssql-tools.x86_64 0:17.2.0.2-1 unixODBC-devel.x86_64 0:2.3.1-11.el7

Dependency Installed:
msodbcsql17.x86_64 0:17.2.0.1-1 unixODBC.x86_64 0:2.3.1-11.el7

8.接下来做一些测试连接,先查看用的是否是1434端口

[root@10 run]# netstat -tulpn | grep sqlservr
tcp 0 0 0.0.0.0:1433 0.0.0.0:* LISTEN 4414/sqlservr
tcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN 4414/sqlservr
tcp6 0 0 :::1433 :::* LISTEN 4414/sqlservr
tcp6 0 0 ::1:1434 :::* LISTEN 4414/sqlservr

[root@10 run]# sqlcmd -S 127.0.0.1 -U SA -P 'Passwd!123*'
bash: sqlcmd: command not found...

这里没配置好环境变量。先设置一下。

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

再一次连接,如果成功,就会显示sqlcmd命令提示符:1>

[root@10 run]# sqlcmd -S 127.0.0.1 -U SA
Password:
1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)

1> USE TestDB
2> CREATE TABLE test (id INT, name VARCHAR(50))
3> INSERT INTO test VALUES (1, 'test install');
4> GO
Changed database context to 'TestDB'.

(1 rows affected)

1> select * from test
2> GO
id name
----------- --------------------------------------------------
1 test install

(1 rows affected)

至此整个安装过程结束,参考微软官方文档:https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017

Oracle 12cR2新特性-跨容器DML(insert into container)

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:Oracle 12cR2新特性-跨容器DML(insert into container)
 
Oracle在12c推出了多租户的概念,那么在CDB里面,怎么对PDB进行事务呢?我们一般会创建DB_LINK,而如果你不想创建DB_LINK,可以使用另外一种办法就是跨容器的DML(insert into container),该功能非常有用,可以避免创建DB_LINK,在做事务操作的时候系统会自动打开一个Link,当你退出会话的时候,打开的link就会随着消失。我们来看一下这个功能。

这是我的CDB,下面有一个PDB叫ORCLPDB1.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

我在ORCLPDB1中先要创建test1表用作测试。

SQL> connect sys/oracle@//localhost/ORCLPDB1  as sysdba
Connected.

SQL> create table test1 (key number primary key, value varchar2(90));
Table created.

然后我连接到CDB$ROOT开始一个事务,然后检查容器中事务的情况,由于是我开启后,没有做任何DML,因此这里没有查到任何事务。

SQL> connect sys/oracle@//localhost/ORCLCDB as sysdba
Connected.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> set transaction name 'Buddy';
Transaction set.

SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);
no rows selected

接下来我来执行跨容器的DML,使用insert into container()语法,注意这里需要指明容器的ID。

SQL> insert into containers(test1) (con_id,key,value) values (3,1,'Buddy Test');
1 row created.

SQL> select con_id,addr,xidusn,ubafil,ses_addr,ptx_xid,name,used_urec from containers(v$transaction);

    CON_ID ADDR                 XIDUSN     UBAFIL SES_ADDR         PTX_XID          NAME                  USED_UREC
---------- ---------------- ---------- ---------- ---------------- ---------------- -------------------- ----------
         1 000000006881E0C0          5          0 000000006DFB01F0 0000000000000000 Buddy                         1
         3 0000000068914258          3         11 000000006DFC36B0 0000000000000000                               2

这里可以看到,我开启了两个事务,一个是在容器1,也就是CDB$root下,另外一个是容器3,在ORCLPDB1里面。然后我查询事务相关的会话,我们可以发现,容器1当中的会话sqlplus也就是我现在正在执行事务的会话,而在容器3,也开启了一个会话。是系统自动开启的。

SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);

TADDR                CON_ID PROGRAM                                  ACTION               MODULE
---------------- ---------- ---------------------------------------- -------------------- ----------------------------------------
0000000068914258          3 oracle@oracle-18c-vagrant (TNS V1-V3)                         oracle@oracle-18c-vagrant (TNS V1-V3)
000000006881E0C0          1 sqlplus@oracle-18c-vagrant (TNS V1-V3)                        sqlplus@oracle-18c-vagrant (TNS V1-V3)

此时我们在查一下v$dblink,可以看到系统自动打开了一个db_link,而这个DB_LINK的IN_TRANSACTION是yes的。也就是说刚刚的dblink做了事务操作。

SQL> select * from v$dblink;

DB_LINK                          OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
------------------------------ ---------- --- --- ------ ------------ --- --- --------------------- ----------
ORCLPDB1                                0 YES YES UNKN              0 YES YES                     1          1

我们当前的事务并未提交,我们使用containers语法查询数据的时候,不会看到这条未提交的数据。这是因为这个时候会话并没有使用数据库链接去查询,而是通过切换容器来查询的。这意味着不是在同一个事务中,因此我们没看到插入的数据。

SQL> select * from containers(test1);
no rows selected

当我们执行提交。此时db_link链接还在,而IN_TRANSACTION已经变成了false。因为刚刚提交了事务,在容器3产生的会话也已经消失了。

SQL> commit;
Commit complete.

SQL> select * from v$dblink;
DB_LINK                OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH     CON_ID
-------------------- ---------- --- --- ------ ------------ --- --- --------------------- ----------
ORCLPDB1                      0 YES YES UNKN              0 NO  NO                      1          1

SQL> select taddr,con_id,program,action,module from v$session where saddr in (select ses_addr from v$transaction);
no rows selected

此时再执行刚刚的容器查询,就能够查看到刚刚插入的数据了。

SQL> select * from containers(test1);

       KEY VALUE                                                                                          CON_ID
---------- ------------------------------------------------------------------------------------------ ----------
         1 Buddy Test                                                                                          3

而当我们退出sqlplus,再次登陆,查看v$dblink的时候,可以看到之前的dblink伴随这session已经消失了。
对于CDB的管理员来说,这是一个在不同的PDB里面执行DML然后却又不用创建DB_LINK的一种方式。非常的方便。

oracle 12cR2新特性-PDB lockdown profile

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:oracle 12cR2新特性-PDB lockdown profile

在12c多租户架构当中,PDB不仅仅共享了主机环境外,它还共享了操作系统,网络和公共对象。考虑到一些特权会允许数据库用户执行跨PDB操作,PDB可能会暴露某一些漏洞。特别是在公有云和私有云的环境中,多租户隔离是安全性的关键要求。因此,在Oracle 12cR2推出了lockwodn profile进行控制,并在Oracle 18c里面进一步得到了增强。

锁定配置文件可以限制pdb中用户执行某些操作:
1.管理功能,限制某些SQL语句,如alter system和alter session、alter database;
2.运行访问网络的程序,例如utl_smtp和utl_http
3.访问普通用户的对象;
4.与OS交互,比如UTL_FILE、DBMS_FILE_TRANSFER包
5.在cdb中执行不受限制的跨pdb连接;
6.生成awr快照;
7.部分或者整体使用java;
8.使用某些数据库选项,例如高级队列和分区表;

那么我们先来测试下这个功能,测试功能需要了解以下注意点:
1.创建、启用、禁用和删除lockdown文件的过程相对简单,用户需要具备create lockdown profile、alter lockdown profile、drop lockdown profile的权限。如果要启用lockdown profile(在CDB或者是PDB级别),还需要该用户有ALTER SYSTEM或者是SYSDBA的权限。
2.单个lockdown profile文件可以在其中定于多个规则。
3.一个pdb一次只能激活一个lockdown profile配置文件。
4.lockdown profile文件强制执行的限制是PDB范围的,它们会影响到每个用户,包括sys和systsm.
5.如果在cdb$root中启用了lockdown profile配置文件,则会影响所有的pdb,如果在pdb中启用,则只会影响pdb。

接下来我们就来测试一下这个功能
1.首先创建lockdown profile

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> create lockdown profile default_pdb_lockdown;
Lockdown Profile created.

2.接下来增加一些限制,限制前面说过有几种,我们来一一介绍。
2.1.锁定数据库选项。在Oracle 12cR2中,只有两个选项(DATABASE QUEUING,PARTITIONING)可以在lockdown配置文件中启用或禁用。

alter lockdown profile default_pdb_lockdown disable option=('PARTITIONING');
alter lockdown profile default_pdb_lockdown disable option=('DATABASE QUEUING');
alter lockdown profile default_pdb_lockdown disable option all;
alter lockdown profile default_pdb_lockdown disable option all except = ('DATABASE QUEUING','PARTITIONING');

这里几个选项的意思如option all,就是禁用所有数据库选项,option all except就是禁用所有选项除了DATABASE QUEUING,PARTITIONING;
如果要启用,就把disable关键字修改成enable。

alter lockdown profile default_pdb_lockdown enable option all;

2.2锁定数据库功能。可以参考官方文档中的Table 11-1 PDB Lockdown Profile Features介绍的。可以指定一组功能(Feature Bundle),也可以指定Feature。

alter lockdown profile default_pdb_lockdown disable feature=('OS_ACCESS');
alter lockdown profile default_pdb_lockdown disable feature=('UTL_HTTP','UTL_SMTP');

2.3.锁定语句
目前4个ALTER语句(ALTER DATABASE,ALTER PLUGGABLE DATABASE,ALTER SESSION,ALTER SYSTEM)可以被pdb lockdown profile锁定。

alter lockdown profile default_pdb_lockdown disable statement=('ALTER DATABASE','ALTER PLUGGABLE DATABASE');
alter lockdown profile default_pdb_lockdown disable statement all except=('ALTER DATABASE','ALTER PLUGGABLE DATABASE');

当然限制的范围可以使用CLAUSE,OPTION,MINVALUE,MAXVALUE。
以下语句禁用ALTER SYSTEM SUSPEND和ALTER SYSTEM RESUME语句;

alter lockdown profile default_pdb_lockdown disable statement=('ALTER SYSTEM') clause = ('SUSPEND', 'RESUME');

以下语句禁用所有ALTER PLUGGABLE DATABASE语句,除了DEFAULT TABLESPACE和DEFAULT TEMPORARY TABLESPACE;

alter lockdown profile default_pdb_lockdown disable statement=('ALTER PLUGGABLE DATABASE') clause all except =('DEFAULT TABLESPACE','DEFAULT TEMPORARY TABLESPACE');

以下语句禁用ALTER SESSION语句设置COMMIT_WAIT或CURSOR_SHARING

alter lockdown profile default_pdb_lockdown disable statement=('ALTER SESSION') clause=('SET') option=('COMMIT_WAIT','CURSOR_SHARING');

以下语句禁用ALTER SYSTEM语句设置或修改CPU_COUNT小于8;

alter lockdown profile default_pdb_lockdown disable statement=('ALTER SYSTEM') clause=('SET') option = ('CPU_COUNT') minvalue = '8';

以下语句禁用ALTER SYSTEM语句设置或修改CPU_COUNT大于2;

alter lockdown profile default_pdb_lockdown disable statement=('ALTER SYSTEM') clause=('SET') option = ('CPU_COUNT') maxvalue = '2';

以下语句禁用ALTER SYSTEM语句设置或修改CPU_COUNT小于2和大于8;

alter lockdown profile default_pdb_lockdown disable statement=('ALTER SYSTEM') clause=('SET') option = ('CPU_COUNT') minvalue = '2' maxvalue = '8';

3.限制策略需要好好的考虑好,不然就会经常出现alter session set container都出现权限错误的问题,因为有一些递归SQL调用就会出现问题。如下所示:

SQL> alter session set container=ORCLPDB1;
ERROR:
ORA-01031: insufficient privileges

SQL> show pdbs; 
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01031: insufficient privileges

这里我简单测试,做了以下几个限制。

SQL> alter lockdown profile default_pdb_lockdown disable option=('PARTITIONING');
Lockdown Profile altered.

SQL> alter lockdown profile default_pdb_lockdown disable option=('DATABASE QUEUING');
Lockdown Profile altered.

SQL> alter lockdown profile default_pdb_lockdown disable feature=('OS_ACCESS');
Lockdown Profile altered.

SQL> alter lockdown profile default_pdb_lockdown disable feature=('UTL_HTTP','UTL_SMTP');
Lockdown Profile altered.

SQL> alter lockdown profile default_pdb_lockdown disable statement=('alter system') clause=('set') option all;
Lockdown Profile altered.

4.启用lockdown profile。我们在CDB中设置参数PDB_LOCKDOWN,那么所有PDB的都会使用这个lockdown profile。

SQL> ALTER SYSTEM SET PDB_LOCKDOWN = default_pdb_lockdown;
System altered.

我们可以通过查询DBA_LOCKDOWN_PROFILES视图,查看我们已经设置的限制规则。

SQL> select profile_name,rule_type,rule,clause,clause_option,status,users from DBA_LOCKDOWN_PROFILES where PROFILE_NAME='DEFAULT_PDB_LOCKDOWN';

PROFILE_NAME RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS
-------------------- -------------------- ------------------------- ------------------------------ ------------------------- ------- ------
DEFAULT_PDB_LOCKDOWN FEATURE OS_ACCESS DISABLE ALL
DEFAULT_PDB_LOCKDOWN FEATURE UTL_HTTP DISABLE ALL
DEFAULT_PDB_LOCKDOWN FEATURE UTL_SMTP DISABLE ALL
DEFAULT_PDB_LOCKDOWN OPTION DATABASE QUEUING DISABLE ALL
DEFAULT_PDB_LOCKDOWN OPTION PARTITIONING DISABLE ALL
DEFAULT_PDB_LOCKDOWN STATEMENT ALTER SYSTEM SET DISABLE ALL

5.执行测试,接下来我们进入到PDB里面,执行相关的SQL测试下限制是否生效了。

SQL> alter session set container=ORCLPDB1;
Session altered.

SQL> show parameter pdb_lockdown
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown string DEFAULT_PDB_LOCKDOWN

可以看到是没有权限使用ALTER SYSTEM的,创建分区表会报ORA-00439: feature not enabled: Partitioning

SQL> alter system set cursor_sharing=EXACT;
alter system set cursor_sharing=EXACT
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> create table t1 (c1 number) partition by hash (c1);
create table t1 (c1 number) partition by hash (c1)
*
ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

再次回到CDB,在CDB下执行这些语句,可以看到功能是不受限制的。

SQL> alter session set container=cdb$root;
Session altered.

SQL> create table t1 (c1 number) partition by hash (c1);
Table created.

所以,我们可以看到,lockdown profile是限制PDB中某些操作的安全机制。它可以限制ALTER SYSTEM等强大的权限。也可以禁止使用一些访问网络或者是操作系统层面的强大的函数和包,当然还可以控制使用一些数据库的选项。这么做的好处是提升了安全性,做好了租户隔离。在公有云和私有云的租户环境是非常有用的功能。

又遇ORA-04021: timeout occurred while waiting to lock object ,这次是DBRM进程

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:又遇ORA-04021: timeout occurred while waiting to lock object,这次是DBRM进程

第二次在ADG上遇到这个错误了,上一次遇到这个错误的传送门,但是这一次的错误并不是LGWR进程引起的,这一次是另外一套11.2.0.4的DG备库,是DBRM进程导致的实例宕机。我们先来看下Trace。

Errors in file /oracle/app/product/diag/rdbms/dghbyydba/hbyydba2/trace/hbyydba2_dbrm_12256282.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04021: timeout occurred while waiting to lock object 
DBRM (ospid: 12256282): terminating the instance due to error 604
Wed Oct 10 01:20:08 2018
System state dump requested by (instance=2, osid=12256282 (DBRM)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/product/diag/rdbms/dghbyydba/hbyydba2/trace/hbyydba2_diag_11470468_20181010012008.trc
Dumping diagnostic data in directory=[cdmp_20181010012008], requested by (instance=2, osid=12256282 (DBRM)), summary=[abnormal instance termination].
Instance terminated by DBRM, pid = 12256282

Alert的trace报错很清楚,是DBRM进程最后终止了实例,我们在看一下DBRM进程的Trace。

*** 2018-10-10 00:35:41.489
PQQ: Active Services changed
PQQ: Old service table
SvcIdx  SvcId Active ActDop
PQQ: New service table
SvcIdx  SvcId Active ActDop
     1      1      1      0
     2      2      1      0
error 604 detected in background process

*** 2018-10-10 01:20:08.823
ORA-00604: error occurred at recursive SQL level 1
ORA-04021: timeout occurred while waiting to lock object
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+240<-kjzdssdmp()+240<-kjzduptcctx()+228<-kjzdicrshnfy()+120<-ksuitm()+1532<-ksbrdp()+4696<-opirip()+1620<-opidrv()+608<-sou2o()+136<-opimai_real()+188<-ssthrdmain()+276<-main()+20
4<-__start()+112
----- End of Abridged Call Stack Trace -----

*** 2018-10-10 01:20:08.824
DBRM (ospid: 12256282): terminating the instance due to error 604
ksuitm: waiting up to [5] seconds before killing DIAG(11470468)

这里有堆栈信息,根据上述错误,我们在MOS中很快就定位了这个问题。根据文档:Bug 18101696 Database Resource Manager Crashes Standby Instance with ORA-604 / ORA-1489 After Turning Apply On。文档中描述下列信息。我们这里报了ORA-00604的递归错误,同时我们的堆栈也包含了”kjzdicrshnfy<-ksuitm<-ksbrdp“这三个。

Problem Description:
-------------------------
Database Resource Manager (DBRM) terminating with:
 
      ORA-604: error occurred at recursive SQL level 1
      ORA-1489: result of string concatenation is too long
Instance terrmination is likely to follow. The call stack would include:

kjzdicrshnfy<-ksuitm<-ksbrdp
但是疑问就是我们这里没有报ORA-1489,而是报了ORA-04021: timeout occurred while waiting to lock object,其实这个我们在上一篇文章介绍过,当通过ADG中的恢复,LGWR将DB INSTANCE状态对象锁定为独占模式。这样的结果是LGWR可以阻止SQL的解析,而SQL的解析也能阻止LGWR。而这里不是LGWR进程而是DBRM进程,这说明了另外一点,DBRM进程也会将一些对象锁定,从而导致超时问题。针对这个超时问题,还是建议将隐含参数”_adg_parselock_timeout”设置成500。
而MOS上的根本workground是关闭resource manager,但是奇怪的地方就是resource manager plan是没有开启的。而DBRM进程主要作用是为数据库实例配置资源计划。那么这可能是一个ADG上的BUG,但是如果我们安装补丁18101696是会出问题的,他会导致出现另外一个错误:ORA-604 AND ORA-904 AFTER INSTALLING PATCH 18101696 (文档 ID 2072853.1)。这个补丁安装之后就增加了一个新函数rm $ _get_mappings_hash,以及调用该函数的代码。而如果要使用它就必须运行postinstall.sql,但是问题就在于ADG是只读的。所以打这个补丁需要在主库上也打,并且在主库上运行该脚本,然后同步到物理备库。不然就会出现ORA-00904报错。而还有一个办法就是安装补丁:Bug 25188350 – DBRM terminating the instance due to error 604 (文档 ID 25188350.8),这个补丁有一个说明就是“This fix supersedes the fix of bug 18101696, installing patch 25188350 resolves both issues.”。也就是补丁18101696已经被取代了,安装补丁25188350能解决这所有的问题。

因此,我认为这个问题的终极solution就是:
1、设置参数”_adg_parselock_timeout”,防止lock object而导致的超时。
2、安装补丁25188350,阻止DBRM进程运行调用SYS.DBMS_RMIN”出现的递归错误引起的ORA-604。

Oracle 18c新特性-MemOptimized RowStore

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:Oracle 18c新特性-MemOptimized RowStore

Oracle Database 18c中引入的MemOptimized RowStore,该功能可以提高通过主键列访问数据的查询性能。比如我们经常使用的这样的SQL语句:SELECT value FROM t WHERE key=:key,其中key是表中的唯一主键。
MemOptimized RowStore的想法是将堆表完全存储在SGA的一块区域内。这块区域被称作:Memoptimized Pool。它主要由以下两个部分构成:

1.Memoptimize Buffer Area
这是表块的专用缓冲区缓存,memoptimized Pool的75%是为此缓冲区缓存保留的。
2.Hash Index
哈希索引就是在内存区域做的一个映射表,主键就是映射键,并且指向Memoptimize Buffer Area中的块。哈希索引使用memoptimized Pool的另外25%。
如下图所示:

数据库运行SQL语句的时候,将会绕过SQL层,利用内存中的哈希索引直接访问所需的数据。减少了物理访问,避免了磁盘I/O。memoptimized pool的大小由初始化参数MEMOPTIMIZE_POOL_SIZE定义的。默认大小是0。更改值需要重新启动数据库。最小尺寸是100M。

使用MemOptimized RowStore必须满足以下条件:
1.该表上有一个主键。
2.该表未压缩。
开启该功能,会出现一些新的等待事件,总共有65个。如下所示:

SQL> select * from v$statname where name like '%memopt%';
STATISTIC# NAME                                                                  CLASS    STAT_ID DISPLAY_NAME                                                         CON_ID
---------- ---------------------------------------------------------------- ---------- ---------- ---------------------------------------------------------------- ----------
      1682 memopt r fail to pin buffer                                             128  311744847 memopt r fail to pin buffer                                               1
      1683 memopt r entries deleted                                                128 1246583635 memopt r entries deleted                                                  1
      1684 memopt r lookups                                                        128 2449760253 memopt r lookups                                                          1
      1685 memopt r hits                                                           128 2704996161 memopt r hits                                                             1
      1686 memopt r misses                                                         128 2658693813 memopt r misses                                                           1
      1687 memopt r tag collisions                                                 128 1615233916 memopt r tag collisions                                                   1
      1688 memopt r lookup skipped deleted rows                                    128 4063719983 memopt r lookup skipped deleted rows                                      1
      1689 memopt r lookup skipped locked rows                                     128 1501413709 memopt r lookup skipped locked rows                                       1
      1690 memopt r lookup skipped chained rows                                    128 2134479771 memopt r lookup skipped chained rows                                      1
      1691 memopt r failed reads on buckets                                        128 2119256684 memopt r failed reads on buckets                                          1
      1692 memopt r failed reads on blocks                                         128 3038931328 memopt r failed reads on blocks                                           1
      1693 memopt r lookup detected CR buffer                                      128 2118516459 memopt r lookup detected CR buffer                                        1
      1694 memopt r puts                                                           128   12188864 memopt r puts                                                             1
      1695 memopt r puts:buckets full                                              128  860399351 memopt r puts:buckets full                                                1
      1696 memopt r successful puts                                                128 2284911377 memopt r successful puts                                                  1
      1697 memopt r successful puts:with evictions                                 128  835169295 memopt r successful puts:with evictions                                   1
      1698 memopt r successful puts:with cuckoo                                    128 3118361144 memopt r successful puts:with cuckoo                                      1
      1699 memopt r successful puts:cuckoo deadend                                 128 4283018875 memopt r successful puts:cuckoo deadend                                   1
      1700 memopt r successful puts:max cuckoo                                     128  606780851 memopt r successful puts:max cuckoo                                       1
      1701 memopt r failed puts                                                    128  141088098 memopt r failed puts                                                      1
      1702 memopt r failed puts:bucket in flux                                     128 1843422109 memopt r failed puts:bucket in flux                                       1
      1703 memopt r failed puts:no space                                           128 2311881927 memopt r failed puts:no space                                             1
      1704 memopt r populate tasks accepted                                        128 3727334836 memopt r populate tasks accepted                                          1
      1705 memopt r populate tasks not accepted                                    128  236934488 memopt r populate tasks not accepted                                      1
      1706 memopt r populate skipped locked rows                                   128 3833166773 memopt r populate skipped locked rows                                     1
      1707 memopt r populate skipped deleted rows                                  128 2993279601 memopt r populate skipped deleted rows                                    1
      1708 memopt r populate skipped chained rows                                  128 3189475334 memopt r populate skipped chained rows                                    1
      1709 memopt r rows populated                                                 128 2576444784 memopt r rows populated                                                   1
      1710 memopt r populate                                                       128 1331937481 memopt r populate                                                         1
      1711 memopt r blocks populated                                               128   99459750 memopt r blocks populated                                                 1
      1712 memopt r failed to get tbs drop EQ                                      128 2581303612 memopt r failed to get tbs drop EQ                                        1
      1713 memopt r failed to get tbs offline EQ                                   128 4132756765 memopt r failed to get tbs offline EQ                                     1
      1714 memopt r failed to get segment drop EQ                                  128 2711296718 memopt r failed to get segment drop EQ                                    1
      1715 memopt r repopulate tasks accepted                                      128   55882086 memopt r repopulate tasks accepted                                        1
      1716 memopt r repopulate tasks not accepted                                  128  308832077 memopt r repopulate tasks not accepted                                    1
      1717 memopt r repopulate                                                     128 4292673878 memopt r repopulate                                                       1
      1718 memopt r rows repopulated                                               128  412578977 memopt r rows repopulated                                                 1
      1719 memopt r blocks repopulated                                             128 4086731426 memopt r blocks repopulated                                               1
      1720 memopt r repopulate skipped locked rows                                 128 1039941343 memopt r repopulate skipped locked rows                                   1
      1721 memopt r repopulate skipped deleted rows                                128 1453092269 memopt r repopulate skipped deleted rows                                  1
      1722 memopt r repopulate skipped chained rows                                128 1283143522 memopt r repopulate skipped chained rows                                  1
      1723 memopt r repopulate invalidated entries                                 128 2406172163 memopt r repopulate invalidated entries                                   1
      1724 memopt r cleanup                                                        128    1970750 memopt r cleanup                                                          1
      1725 memopt r NO IM tasks accepted                                           128 1465237793 memopt r NO IM tasks accepted                                             1
      1726 memopt r NO IM tasks not accepted                                       128 3768980909 memopt r NO IM tasks not accepted                                         1
      1727 memopt r DROP IM tasks accepted                                         128 2574115598 memopt r DROP IM tasks accepted                                           1
      1728 memopt r DROP IM tasks not accepted                                     128 2139934761 memopt r DROP IM tasks not accepted                                       1
      1729 memopt w buffer gets                                                    128  944697423 memopt w buffer gets                                                      1
      1730 memopt w rows written                                                   128 3945680858 memopt w rows written                                                     1
      1731 memopt w rows flushed                                                   128 1806100090 memopt w rows flushed                                                     1
      1732 memopt w flush tasks                                                    128  945910151 memopt w flush tasks                                                      1
      1733 memopt w flush tasks deferred                                           128 2737332709 memopt w flush tasks deferred                                             1
      1734 memopt w buffer miss space                                              128 2208213025 memopt w buffer miss space                                                1
      1735 memopt w buffer miss latch                                              128 3552849954 memopt w buffer miss latch                                                1
      1736 memopt w buffer miss waits                                              128  315661965 memopt w buffer miss waits                                                1
      1737 memopt w buffer miss spc nolatch                                        128 1789953959 memopt w buffer miss spc nolatch                                          1
      1738 memopt w buffer miss wait unq                                           128  196625028 memopt w buffer miss wait unq                                             1
      1739 memopt w buffer gotcur                                                  128 3639906483 memopt w buffer gotcur                                                    1
      1740 memopt w buffer miss spc unq nolat                                      128 3892484010 memopt w buffer miss spc unq nolat                                        1
      1741 memopt w buffer miss nobuf                                              128 1059644383 memopt w buffer miss nobuf                                                1
      1742 memopt w buffer hit bucket 0                                            128 1458260122 memopt w buffer hit bucket 0                                              1
      1743 memopt w buffer wake post                                               128 1524849321 memopt w buffer wake post                                                 1
      1744 memopt w drain sleep work                                               128 3595850574 memopt w drain sleep work                                                 1
      1745 memopt w drain sleep                                                    128 1578572087 memopt w drain sleep                                                      1
      1746 memopt w drain sleep wake post                                          128 2681285354 memopt w drain sleep wake post                                            1

下面我们就来做一些测试。
1.首先需要调整memoptimize_pool_size参数.

ALTER SYSTEM SET memoptimize_pool_size = 200M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

2.创建表并插入数据

CREATE TABLE t1 
(
   key    INTEGER            NOT NULL,
   value  VARCHAR2(20)  NOT NULL,
   CONSTRAINT pk_key PRIMARY KEY (key)
) 

SQL> insert into t1 select rownum,'value'||to_char(rownum) from dual connect by level<=1000000; 1000000 rows created. SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','T1');
PL/SQL procedure successfully completed.

3.启用MemOptimized RowStore,创建hash index

SQL> alter table t1 memoptimize for read;
Table altered.

SQL> exec dbms_memoptimize.populate(schema_name=>'SYS',table_name=>'T1');
PL/SQL procedure successfully completed.

4.执行查询,查看执行计划

SQL> set linesize 175 pagesize 1000
SQL> set autotrace traceonly
SQL> SELECT * FROM t1 WHERE key = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3650286101

-------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |        |     1 |    17 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| T1     |     1 |    17 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | PK_KEY |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("KEY"=99)

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        628  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到这里没有物理读,而执行计划显示2步分别是“INDEX UNIQUE SCAN READ OPTIM”,“TABLE ACCESS BY INDEX ROWID READ OPTIM”。 我们还可以通过下列查询来查该操作的一些统计值,再执行一次,memopt r lookups和memopt r hint都增加了。代表这访问哈希索引(1 memopt r lookups),找到内存的数据,hint命中(memopt r hint)

SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
memopt r lookups                                                          3
memopt r hits                                                             3
memopt r populate tasks accepted                                          1

SQL> SELECT * FROM t1 WHERE key = 99;

       KEY VALUE
---------- --------------------
        99 value99

SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
memopt r lookups                                                          4
memopt r hits                                                             4
memopt r populate tasks accepted                                          1

那么我们在来看其他几个例子
1.使用大于或者小于.

SQL> set autotrace ON explain
SQL> select * from t1 WHERE key <10; Execution Plan ---------------------------------------------------------- Plan hash value: 1458221975 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 153 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 9 | 153 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_KEY | 9 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- SQL> set autotrace off
SQL> select a.name,b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.CON_ID=b.CON_ID and a.name like '%memopt%' and value<>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
memopt r lookups                                                          4
memopt r hits                                                             4
memopt r populate tasks accepted                                          1

可以看到使用>和<并没有使用MemOptimized RowStore特性。

2.使用多个=

SQL> select * from t1 WHERE key=10 or key=11;
Execution Plan
----------------------------------------------------------
Plan hash value: 2536766119

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    34 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     2 |    34 |     5   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_KEY |     2 |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("KEY"=10 OR "KEY"=11)

SQL> select * from t1 WHERE key=10 and key=11;

Execution Plan
----------------------------------------------------------
Plan hash value: 3010271221

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    17 |     0   (0)|          |
|*  1 |  FILTER                      |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |     1 |    17 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_KEY |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter(NULL IS NOT NULL)
   3 - access("KEY"=10)

3.使用多个列

SQL> select * from t1 WHERE key=10 and value='value10';
Execution Plan
----------------------------------------------------------
Plan hash value: 3650286101

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    17 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    17 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_KEY |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("VALUE"='value10')
   2 - access("KEY"=10)

可以看到,使用其他的条件都不能使用MemOptimized RowStore。

参考文档
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/memory-architecture.html#GUID-D58DC90F-0ABB-4B1E-96C1-6094A04A5E12
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/tuning-system-global-area.html#GUID-4434D082-4748-47C3-A410-B7E2B443DD16