现代Oracle数据库的版本选择问题

Oracle公司从版本12c以后实际上引入了类似于SQL Server的以年份为版本号的版本风格。Oracle 12c以后的已release版本为18c,19c,21c,2020年由于全球疫情的问题未发布大版本号。

其大版本的释放周期大大缩短了,实际上大版本的释放频率已经覆盖了过去Patch Set补丁集的作用。

对于先进企业选择Oracle数据库版本而言,建议可以考虑对当前年份-2/3的算法。

即如果当前是2021年,则考虑使用(2021-2 or 3) 18c或19c;并打上最新的RU(Release Update)和RUR(Release Update Revision)。

 

 

更高效的DBA_EXTENTS 查询脚本

DBI的Franck Pachot给出了一个更高效率的DBA_EXTENTS脚本,对于特别大的ORACLE数据库定位EXTENT时很有用:

Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID

 

 


column owner format a6
column segment_type format a20
column segment_name format a15
column partition_name format a15
set linesize 200
set timing on time on echo on autotrace on stat
WITH
 l AS ( /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */
  SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn,
         ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno
  FROM sys.x$ktfbue
 ),
 d AS ( /* DMT extents ts#, segfile#, segblock# */
  SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn,
         block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno
  FROM sys.uet$
 ),
 s AS ( /* segment information for the tablespace that contains afn file */
  SELECT /*+ materialized */
  f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize
  FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2 
  WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn
 ),
 m AS ( /* extent mapping for the tablespace that contains afn file */
SELECT /*+ use_nl(e) ordered */
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,l e
 WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) ordered */ 
 s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
 FROM s,d e
  WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn
 UNION ALL
 SELECT /*+ use_nl(e) use_nl(t) ordered */
 f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
 FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t
 WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn
 ),
 o AS (
  SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block   segbid,s.segment_type,s.owner,s.segment_name,s.partition_name
  FROM SYS_DBA_SEGS s
 ),
datafile_map as (
SELECT
 afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,
 owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,
 tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid
 FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)
UNION ALL
SELECT
 file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,
 1 block_id,blocks,'tempfile' segment_type,
 '' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,
  tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid
 FROM dba_temp_files
)
select * from datafile_map where file_id=5495 and 11970455 between block_id and block_id+blocks


Dgg Oracle MySQL SQL Server数据同步与比对

 

下载:

Dgg Oracle MySQL数据同步校准工具最新版:https://zcdn.askmac.cn/Dgg2107.zip

Dgg申请个人版注册码: http://askmac.cn:3333/acquire

 

教程:

使用Dgg实现MySQL数据库同构跨库数据校验

使用Dgg实现Oracle数据库同构跨库数据校验

使用Dgg全量同步/初始化Initial Load MySQL数据

使用Dgg全量同步/初始化Initial Load Oracle数据

 

Dgg Oracle/MySQL数据库复制同步校准软件

Dgg Rdbms Oracle MySQL Table/Schema Sync Compare Repair

最新版:

 

Dgg is a single application that allows you to tranfer data between Oracle and MySQL. It will also support Microsoft SQL Server in future.

It supports below directionals:

Oracle To Oracle
Oracle To MySQL
MySQL To MySQL
MySQL To Oracle

特性:

  • 快速比较同步Oracle表数据
  • 快速比较同步MySQL表数据
  • 支持异构传输 Oracle <=> MySQL , 未来将支持 SQL Server
  • 将支持快速数据表校准修复
  • 将支持索引、视图、触发器等其他类型对象

 

当目标数据库类型为MySQL时,需要对应数据库用户具备SUPER ON *.* 权限,

 

GRANT SUPER ON *.* to USER;

 

否则会出现报错:msg: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

Oracle到Oracle的传输速度:

在有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为4500条/秒。

在没有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下,DataGoGo的单线程传输速度约为30000条/秒。

 

 

MySQL到MySQL的传输速度

没有LOB/LONGTEXT的情况下,单线程速度约为20000条/秒。

 

 

 

使用Dgg实现MySQL数据库同构跨库数据校验

在使用Dgg实现MYSQL跨库数据同步后,可以使用Dgg的数据校验功能来验证数据是否一致。

 

请首先参考《使用Dgg 全量同步/初始化Initial Load MySQL数据》 https://www.askmac.cn/archives/datagogo-initial-mysql.html

选中 树形图最上方的用户名,右击 Compare:

 

 

 

 

 

DataGoGo中存在三种 表数据的比较粒度:

  1. 只比较行数 ( 适用于大表 如日志表,为最粗粒度的比较)
  2. 只比较主键HASH (适用于中大表,要求表有主键,中等粒度比较)
  3. 比较全表HASH (适用于中小表,最细粒度比较 , 速度较慢)

 

测试 90万行 7列的表, 比较约耗时 10秒。

 

 

使用Dgg实现Oracle数据库同构跨库数据校验

在使用Dgg实现Oracle跨库数据同步后,可以使用Dgg的数据校验功能来验证数据是否一致。

 

请首先参考《使用Dgg 全量同步/初始化Initial Load Oracle数据》 https://www.askmac.cn/archives/datagogo-initial-load-oracle-database.html

选中 树形图最上方的用户名,右击 Compare:

 

 

 

 

DataGoGo中存在三种 表数据的比较粒度:

  1. 只比较行数 ( 适用于大表 如日志表,为最粗粒度的比较)
  2. 只比较主键HASH (适用于中大表,要求表有主键,中等粒度比较)
  3. 比较全表HASH (适用于中小表,最细粒度比较 , 速度较慢)

 

测试 90万行 24列的表, 比较约耗时 2分钟。

 

比较结果如下:

 

Compare start date: 2021-06-07 14:58:54

Comparing SCOTT.T_XT_SYNCPERSONLOG and EAS.T_XT_SYNCPERSONLOG:

SCOTT.T_XT_SYNCPERSONLOG full table checksum: 235801620123
EAS.T_XT_SYNCPERSONLOG full table checksum: 235801620123

Full table checksum is the same.

End date: 2021-06-07 15:00:31

 

 

udev reload会影响oracle ASM?

UDEV RELOAD是否会影响oracle ASM?

 

回答: 如果频繁 reload 显然会影响Oracle ASM ,甚至导致ASM Diskgroup OFFLINE。

如果只是一次很快速的reload,那么影响的概率较小。

 

参考链接:

 

‘udev’ rules continuously being reloaded resulted in Oracle ASM diskgroup outage

https://access.redhat.com/solutions/1465913

LINUX 7 配置 multipath udev Oracle ASM

multipath.conf

 


# This is a basic configuration file with some examples, for device mapper
# multipath.
#
# For a complete list of the default configuration values, run either
# multipath -t
# or
# multipathd show config
#
# For a list of configuration options with descriptions, see the multipath.conf
# man page

## By default, devices with vendor = "IBM" and product = "S/390.*" are
## blacklisted. To enable mulitpathing on these devies, uncomment the
## following lines.
#blacklist_exceptions {
#	device {
#		vendor	"IBM"
#		product	"S/390.*"
#	}
#}

## Use user friendly names, instead of using WWIDs as names.
defaults {
	user_friendly_names yes
	find_multipaths yes
}
##
## Here is an example of how to configure some standard options.
##
#
#defaults {
#	polling_interval 	10
#	path_selector		"round-robin 0"
#	path_grouping_policy	multibus
#	uid_attribute		ID_SERIAL
#	prio			alua
#	path_checker		readsector0
#	rr_min_io		100
#	max_fds			8192
#	rr_weight		priorities
#	failback		immediate
#	no_path_retry		fail
#	user_friendly_names	yes
#}
##
## The wwid line in the following blacklist section is shown as an example
## of how to blacklist devices by wwid.  The 2 devnode lines are the
## compiled in default blacklist. If you want to blacklist entire types
## of devices, such as all scsi devices, you should use a devnode line.
## However, if you want to blacklist specific devices, you should use
## a wwid line.  Since there is no guarantee that a specific device will
## not change names on reboot (from /dev/sda to /dev/sdb for example)
## devnode lines are not recommended for blacklisting specific devices.
##
#blacklist {
#       wwid 26353900f02796769
#	devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"
#	devnode "^hd[a-z]"
#}
#multipaths {
#	 {
#       device {
#               vendor                  "COMPAQ  "
#               product                 "HSV110 (C)COMPAQ"
#               path_grouping_policy    multibus
#               path_checker            readsector0
#               path_selector           "round-robin 0"
#               hardware_handler        "0"
#               failback                15
#               rr_weight               priorities
#               no_path_retry           queue
#       }
#       device {
#               vendor                  "COMPAQ  "
#               product                 "MSA1000         "
#               path_grouping_policy    multibus
#       }
#}

#		wwid			3600508b4000156d700012000000b0000
#		alias			yellow
#		path_grouping_policy	multibus
#		path_selector		"round-robin 0"
#		failback		manual
#devices {
#       device {
#               vendor                  "COMPAQ  "
#               product                 "HSV110 (C)COMPAQ"
#               path_grouping_policy    multibus
#               path_checker            readsector0
#               path_selector           "round-robin 0"
#               hardware_handler        "0"
#               failback                15
#               rr_weight               priorities
#               no_path_retry           queue
#       }
#       device {
#               vendor                  "COMPAQ  "
#               product                 "MSA1000         "
#               path_grouping_policy    multibus
#       }
#}
#		rr_weight		priorities
#		no_path_retry		5
#	}
#	multipath {
#		wwid			1DEC_____321816758474
#		alias			red
#	}
#
multipaths {

multipath {
 wwid 360050764008103127000000000000067
 alias disk30001
 }
 multipath {
 wwid 360050764008103127000000000000068
 alias disk30002
 }
 multipath {
 wwid 360050764008103127000000000000069
 alias disk30003
 }


multipath {
 wwid 360050764008103127000000000000064
 alias disk31001
 }
 multipath {
 wwid 360050764008103127000000000000065
 alias disk31002
 }



 multipath {
 wwid 36005076400810312700000000000005f
 alias disk32001
 }
 multipath {
 wwid 360050764008103127000000000000060
 alias disk32002
 }
 multipath {
 wwid 360050764008103127000000000000061
 alias disk32003
 }
 multipath {
 wwid 360050764008103127000000000000062
 alias disk32004
 }
 multipath {
 wwid 360050764008103127000000000000063
 alias disk32005
 }


 multipath {
 wwid 360050764008103127000000000000066
 alias disk33001
 }




}



12-dm-permissions.rules


ENV{DM_NAME}=="disk30001",NAME="asmdisk30001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk30002",NAME="asmdisk30002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk30003",NAME="asmdisk30003", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk31001",NAME="asmdisk31001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk31002",NAME="asmdisk31002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32001",NAME="asmdisk32001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32002",NAME="asmdisk32002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32003",NAME="asmdisk32003", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32004",NAME="asmdisk32004", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32005",NAME="asmdisk32005", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk33001",NAME="asmdisk33001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"


99-oracle-asmdevices.rules


KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000067", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000068", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000069", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000064", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000065", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="36005076400810312700000000000005f", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000060", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000061", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000062", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000063", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000066", OWNER="grid", GROUP="asmadmin", MODE="0660"


solidity学习笔记

UINT 是 UINT256的别名

INT是INT256的别名

uint and int are aliases for uint256 and int256, respectively.

Solidity 中整型有明确范围,例如 uint32 的范围是 0 ~ 2**32-1 , 若结果超过范围 会被truncate截断

address 类型  存放一个20字节的以太坊地址

address payable  类似于address ,且提供 transfer 和 send成员 ;   即 普通address默认不能传输ETH , address payable 可以

pragma solidity 0.5.10;


contract Test1 {
    
    address public notpaybleaddress;
    address      public payableaddress;
    uint public balance1;
    uint public balance2;
    uint public balance3;
    
    constructor() public payable {
        notpaybleaddress = msg.sender;
        payableaddress=address(0);
        balance1= address(msg.sender).balance;
        balance2= address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2).balance;
        balance3= address(this).balance;

    }
    
    
    function test1() public payable {
       address payable x = address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2);
 
 
      x.transfer(1 ether);
 
        balance1= address(msg.sender).balance;
        balance2= address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2).balance;
        balance3= address(this).balance;
      
 
      
    }
    
    
}


 

 

 

ABI 是 Application Binary Interface的简称,即2进制接口。是Ethereum生态中与合约交互的标准接口,包括了 链外和 合约对合约调用。其数据按照类型编码,由于不是自描述的,因此需要对应模式解码。

一个函数调用的调用数据的前4个字节,被用来指定要调用的函数。其是该函数的签名的Keccak-256 (SHA-3)哈希值的前4位。

 

 

 

bytes4 private constant SELECTOR = bytes4(keccak256(bytes('transfer(address,uint256)')));
(bool success, bytes memory data) = token.call(abi.encodeWithSelector(SELECTOR, to, value));

测试代码

pragma solidity 0.5.10;


contract Test2 {
    
    
    address public owner;
    mapping( address => uint ) public balances;
    bytes4 private constant SELECTOR = bytes4(keccak256(bytes('transfer(address,uint256)')));
    
    function transfer(address to,uint256 value) public {
        
        balances[to]+= value;
        balances[address(msg.sender)]-=value;
        
    }
    
    
    function _safeTransfer(address token, address to, uint value) private {
        (bool success, bytes memory data) = token.call(abi.encodeWithSelector(SELECTOR, to, value));
        require(success && (data.length == 0 || abi.decode(data, (bool))), 'TRANSFER_FAILED');
    }
    
    
    function testabitransfer() public {
        
        _safeTransfer(address(this),address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2),100);
        
    }
    
    
    constructor() public {
        
        owner= msg.sender;
        
        balances[msg.sender]=10000;
        balances[address(this)]=10000;
    }
    
    
}

 

type(C).creationCode

 

包含协定的创建字节码的内存字节数组。这可以在内联程序集中用于构建自定义创建例程,特别是通过使用 create2 操作码。此属性可以 not 在合同本身或任何派生合同中访问。它使字节码包含在调用站点的字节码中,因此不可能像这样循环引用。

 

 

bytes32 public constant INIT_CODE_PAIR_HASH = keccak256(abi.encodePacked(type(PancakePair).creationCode));




    function createPair(address tokenA, address tokenB) external returns (address pair) {
        require(tokenA != tokenB, 'Pancake: IDENTICAL_ADDRESSES');
        (address token0, address token1) = tokenA < tokenB ? (tokenA, tokenB) : (tokenB, tokenA);
        require(token0 != address(0), 'Pancake: ZERO_ADDRESS');
        require(getPair[token0][token1] == address(0), 'Pancake: PAIR_EXISTS'); // single check is sufficient
        bytes memory bytecode = type(PancakePair).creationCode;
        bytes32 salt = keccak256(abi.encodePacked(token0, token1));
        assembly {
            pair := create2(0, add(bytecode, 32), mload(bytecode), salt)
        }
        IPancakePair(pair).initialize(token0, token1);
        getPair[token0][token1] = pair;
        getPair[token1][token0] = pair; // populate mapping in the reverse direction
        allPairs.push(pair);
        emit PairCreated(token0, token1, pair, allPairs.length);
    }



 

 

create2 参考:      https://hackernoon.com/using-ethereums-create2-nw2137q7

 

 

关于 memory 和  storage , storage最后都会记录在blockchain上,默认函数里申明的变量 都是memory的, 一般   string 字符串需要 单独申明  memory。

例如 你在函数中特意申明 一个memory变量

uint memory var1=0;

编译器一般会警告:

TypeError: Storage location can only be given for array or struct types. uint memory var1=0; ^————–^

若在contract中的直接定义一个memory 变量 ,则:

uint memory var2=0;

ParserError: Expected identifier but got ‘memory’ uint memory var2=0; ^—-^

 

若在函数中 一个string 变量未指定为memory ,则会报错:

 

 

Warning: Variable is declared as a storage pointer. Use an explicit “storage” keyword to silence this warning. string name1 = name; ^————^

TypeError: Type string memory is not implicitly convertible to expected type string storage pointer. string name1 = name; ^——————-^

 

所以 一般只需要记录 在函数中的临时 string变量, 要指定要memory。其实也不用特意记住,因为不加memory会报错。

 

 

 

gas 评估 ,在remix 中

 

(async () => {
    try {

        let result = await web3.eth.estimateGas({
            to: "0xc4abd0339eb8d57087278718986382264244252f",
            data: "0xc6888fa10000000000000000000000000000000000000000000000000000000000000003"
        });

        console.log(result);



    } catch (e) {
        console.log(e.message)
    }
})()

 

在nodejs中:

 

var Web3 = require('web3');
var web3 = new Web3(new Web3.providers.HttpProvider("https://ropsten.infura.io/v3/260d84d657264ecba48c8d6e95fc707d"));
var result = web3.eth.estimateGas({
    to: "0xc4abd0339eb8d57087278718986382264244252f", 
    data: "0xc6888fa10000000000000000000000000000000000000000000000000000000000000003"
});
console.log(result);


pure 关键字的函数 效率更高 其暗示这个函数 既不读取 blockchain上的状态 也不修改 状态。
These functions cannot:
Read from state variables – read the block chain
Access balances
Call functions not marked pure


library SafeMath {
    function add(uint x, uint y) internal pure returns (uint z) {
        require((z = x + y) >= x, 'ds-math-add-overflow');
    }

    function sub(uint x, uint y) internal pure returns (uint z) {
        require((z = x - y) <= x, 'ds-math-sub-underflow');
    }

    function mul(uint x, uint y) internal pure returns (uint z) {
        require(y == 0 || (z = x * y) / y == x, 'ds-math-mul-overflow');
    }
}


view 函数则按时该函数用以读取状态。 这些关键字可以让编译器优化的更好一点。
View functions are read only functions and do not modify the state of the block chain. In other words if you want to read data from the block chain one can use view. Getter method are by default view functions. These functions cannot:
Write to state variables – update the block chain
Emit events
Create contracts or use self destruct
Send ether

参考 https://cryptomarketpool.com/pure-and-view-in-solidity-smart-contracts/

使用Dgg全量同步/初始化Initial Load MySQL数据

现有2套MySQL服务器,需要将一台上的服务器上的数据同步到另一台。可以使用Dgg数据同步校准软件实现数据初始化同步的目的。

 

打开Dgg软件,点击开始 => 打开, 创建2个MySQL session:

 

 

选择源端 并点击下一步,选择目标端 并点击 连接:

 

 

可以在两侧的对象树上看到相关的表 , 点中用户 右键 => Transfer:

 

 

 

初始化同步后,若对表数据的一致性存在疑问,可以通过Compare功能比对,在左侧树形种点中你要比对的表,右键Compare:

校验分成几种:

  1. 行数对比校验 (最粗粒度)
  2. 主键对比校验  (较粗粒度)
  3. 全表对比校验    (精细粒度)

 

其中行数对比校验 ,仅对比2个表的行数是否一致;即只比较count(1)

主键比对,只比对主键信息是否一致,支持联合主键

全表校验,比对全表数据信息(会排除TEXT LONGTEXT等长字段列)是否一致

 

对于小表而言(一般来说小于10万行),全表校验的速度也较快,其时间可接受。对于大表而言,除非是一致性要求极高的账目表,否则建议使用主键比对或行数比对。

 

 

 

 

 

 

Oracle 相比 mysql 的优势在哪里

Oracle 相比 mysql 的优势在哪里   原帖子在这里:  https://v2ex.com/t/773654?p=1

 

目前的项目用的 mysql,支撑几十亿数据没问题(分库分表),偶尔慢 sql 也能优化索引解决,至于复杂查询通过搜索引擎实现,听说 Oracle 超级贵,那 Oracle 具体有什么优势呢,哪些场景下是 oracle only 的?

看完了回复,oracle 能单表支撑几十亿数据还是很强的,因为目前因为分表键导致很多需求无法实现,只能同步一个表用另一个分表键。

我再问下,如果是同时涉及几十个字段的复杂搜索,oracle 可以支持吗,目前用的搜索引擎实现也非常贵。

有一说一,单表支撑几十亿数据,听着有点玄乎,真的假的啊

我的回复:

 

oracle 的优化器 CBO optimizer 目前应该是所有 RDBMS 里最复杂的(不说是最先进的)。MYSQL 至少在优化器上还处于比较初级的阶段,虽然 MySQL 的目标可能并不希望实现非常复杂的优化器算法。其他一些东西 例如 undo 的实现等等 可能优势并不明显, 这里就不提了。

 

举一个例子, 都不使用索引的情况下,NO INDEX ! NO INDEX | NO INDEX !

以下数据量是一样的,机器是同一台。 Oracle 11.2.0.4 MySQL 8.0.14

都没有索引的情况下:oracle 使用 0.04 秒, MySQL 等了 10 分钟也没运行完

对于简单的 SQL 而言,差别不会有那么大。对于复杂的 SQL 而言, 能明显体现出优化器的优势。

对于拔高某个技术,没有兴趣。 所以 MYSQL 死忠请勿拍。

Oracle : 

SQL> set timing on;
SQL> SELECT c.cust_city,
  2         t.calendar_quarter_desc,
  3         SUM(s.amount_sold) sales_amount
  4    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  5   WHERE s.time_id = t.time_id
  6     AND s.cust_id = c.cust_id
  7     AND s.channel_id = ch.channel_id
  8     AND c.cust_state_province = 'FL'
  9     AND ch.channel_desc = 'Direct Sales'
 10     AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
 11   GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Cypress Gardens                2000-01      3545.82
Candler                        2000-01      4166.32
Sanibel                        2000-02     17908.67
Ocala                          2000-02      7081.73
Molino                         2000-01     18765.25
Ocala                          2000-01      7146.73
Palmdale                       2000-02     25829.24
Palmdale                       2000-01     37793.44
Molino                         2000-02      17144.7
Saint Marks                    2000-01     55781.37
Noma                           2000-01     33572.55
Evinston                       2000-02     62657.21
Candler                        2000-02      6493.94
Winter Springs                 2000-02           20
Sugarloaf Key                  2000-01     12027.66
Saint Marks                    2000-02      48858.7
Blountstown                    2000-02     38052.58
Sugarloaf Key                  2000-02      9659.44
Cypress Gardens                2000-02      4928.93
Evinston                       2000-01     53509.69
Blountstown                    2000-01      27024.7
Sanibel                        2000-01     15870.34
Winter Springs                 2000-01        31.46
Noma                           2000-02     23903.58

已选择 24 行。

已用时间:  00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 1865285285

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   607 | 46132 |   955   (2)| 00:00:12 |       |       |
|   1 |  HASH GROUP BY                 |           |   607 | 46132 |   955   (2)| 00:00:12 |       |       |
|*  2 |   HASH JOIN                    |           |  2337 |   173K|   954   (2)| 00:00:12 |       |       |
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |    18   (0)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS FULL          | TIMES     |   274 |  4384 |    18   (0)| 00:00:01 |       |       |
|*  5 |    HASH JOIN                   |           | 12456 |   729K|   936   (2)| 00:00:12 |       |       |
|   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |       |       |
|*  7 |      TABLE ACCESS FULL         | CHANNELS  |     1 |    13 |     3   (0)| 00:00:01 |       |       |
|   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|    18M|   526   (2)| 00:00:07 |:BF0000|:BF0000|
|  11 |      TABLE ACCESS FULL         | SALES     |   918K|    18M|   526   (2)| 00:00:07 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."TIME_ID"="T"."TIME_ID")
   4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
              "T"."CALENDAR_QUARTER_DESC"='2000-02')
   5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
   9 - filter("C"."CUST_STATE_PROVINCE"='FL')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1726  consistent gets
          0  physical reads
          0  redo size
       1495  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         24  rows processed


SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

SQL> select * From v$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

MySQL :

mysql> SELECT c.cust_city,
    ->        t.calendar_quarter_desc,
    ->        SUM(s.amount_sold) sales_amount
    ->   FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
    ->  WHERE s.time_id = t.time_id
    ->    AND s.cust_id = c.cust_id
    ->    AND s.channel_id = ch.channel_id
    ->    AND c.cust_state_province = 'FL'
    ->    AND ch.channel_desc = 'Direct Sales'
    ->    AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
    ->  GROUP BY c.cust_city, t.calendar_quarter_desc;


+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ch    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |      5 |    20.00 | Using where; Using temporary                       |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   1804 |    30.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  55065 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 914584 |     0.10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+



mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.14    |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sh.sales;
+----------+
| count(*) |
+----------+
|   918843 |
+----------+
1 row in set (0.96 sec)

沪ICP备14014813号-2

沪公网安备 31010802001379号