大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 YashanDB 23.1 个人版脚本安装及简单查询测试，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！

为啥会写这篇文章呢，起初是因为上篇文章测试的是崖山企业版本以及和 Oracle 12c 的性能对比，总体感觉还不错，但这几天看到有几位大佬测试的结果是 Oracle 11.2.0.4 比 YashanDB 性能要强很多，感觉和我之前测试的有出入，所以想在测一测，在体验体验，看看问题在哪是不是我出现了啥错误，而且这次把 Oracle 12c 多租户环境换成非容器 19c 单机版本来试试。

1.下载个人版 YashanDB

我这里下载 x86 架构软件包 YashanDB  https://download.yashandb.com/download

2.操作系统基本信息

[root@jieke-19c ~]# lscpuArchitecture:          x86_64CPU op-mode(s):        32-bit, 64-bitByte Order:            Little EndianCPU(s):                8On-line CPU(s) list:   0-7Thread(s) per core:    1Core(s) per socket:    4Socket(s):             2NUMA node(s):          1Vendor ID:             GenuineIntelCPU family:            6Model:                 79Model name:            Intel(R) Xeon(R) CPU E7-4809 v4 @ 2.10GHzStepping:              1CPU MHz:               2094.952BogoMIPS:              4189.90Hypervisor vendor:     VMwareVirtualization type:   fullL1d cache:             32KL1i cache:             32KL2 cache:              256KL3 cache:              20480KNUMA node0 CPU(s):     0-7Flags:                 fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 avx2 smep bmi2 invpcid rdseed adx smap xsaveopt arat spec_ctrl intel_stibp flush_l1d arch_capabilities[root@Ops-19cOGG ~]# cat /proc/cpuinfo |grep processor  |wc -l8[root@jieke-19c ~]# free -h              total        used        free      shared  buff/cache   availableMem:            15G        1.1G        6.1G        6.3G        8.3G        7.9GSwap:          7.9G         16M        7.9G[root@jieke-19c ~]# df -hFilesystem                 Size  Used Avail Use% Mounted on/dev/mapper/rootvg-lvroot  392G  314G   78G  81% /devtmpfs                   7.8G     0  7.8G   0% /devtmpfs                      7.8G   56K  7.8G   1% /dev/shmtmpfs                      7.8G   81M  7.7G   2% /runtmpfs                      7.8G     0  7.8G   0% /sys/fs/cgroup/dev/sda1                 1014M  190M  825M  19% /boottmpfs                      1.6G     0  1.6G   0% /run/user/54321tmpfs                      1.6G     0  1.6G   0% /run/user/0[root@jieke-19c ~]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.6 (Maipo)

3.系统参数调整

调整资源限制值

[root@jieke-19c ~]# echo "yashan soft nofile 65536yashan hard nofile 65536yashan soft nproc 65536yashan hard nproc 65536yashan soft rss unlimitedyashan hard rss unlimitedyashan soft stack 8192yashan hard stack 8192" >> /etc/security/limits.conf

4.创建用户和组

[root@jieke-19c ~]# groupadd YASDBA[root@jieke-19c ~]# useradd yashan -G YASDBA[root@jieke-19c ~]# echo "yashan"|passwd --stdin yashan

5.上传软件包并解压

[root@jieke-19c ~]# su - yashan[yashan@jieke-19c ~]$ mkdir soft [yashan@jieke-19c ~]$ cd soft/[yashan@jieke-19c soft]$ lltotal 171280-rw-r--r-- 1 root root 175386922 Nov 20 11:32 yashandb-personal-23.1.1.100-linux-x86_64.tar.gz[yashan@jieke-19c soft]$ tar -zxvf yashandb-personal-23.1.1.100-linux-x86_64.tar.gz [yashan@jieke-19c soft]$ lltotal 171312drwxrwxr-x 6 yashan yashan        70 Nov  9 18:16 admindrwxrwxr-x 2 yashan yashan       188 Nov  9 18:16 bindrwxrwxr-x 2 yashan yashan       152 Nov  9 18:16 confdrwxrwxr-x 5 yashan yashan        60 Nov  9 18:16 ext-rw-rw-r-- 1 yashan yashan     11632 Nov  9 18:16 gitmoduleversion.datdrwxrwxr-x 2 yashan yashan        65 Nov  9 18:16 includedrwxrwxr-x 3 yashan yashan        17 Nov  9 18:16 javadrwxrwxr-x 2 yashan yashan      4096 Nov  9 18:16 lib-rw-r----- 1 yashan yashan     14989 Nov  9 18:16 LICENSEdrwxrwxr-x 3 yashan yashan        21 Nov  9 18:16 plug-indrwxrwxr-x 2 yashan yashan       170 Nov  9 18:16 scripts-rw-r--r-- 1 root   root   175386922 Nov 20 11:32 yashandb-personal-23.1.1.100-linux-x86_64.tar.gz

6.使用自带脚本快速安装并初始化库

本次只是简单体验,不进行单独目录规划及参数规划，scripts 目录下的 install.ini 文件则是一个初始化参数文件，可修改相关参数后初始化使用，我们直接使用 install.sh 脚本安装数据库软件，initDB.sh  初始化数据库实例。

[yashan@jieke-19c soft]$ cd scripts/[yashan@jieke-19c scripts]$ lltotal 32-rwxrwxr-x 1 yashan yashan 3296 Nov  9 18:16 createResourceCgroup.sh-rwxrwxr-x 1 yashan yashan 2934 Nov  9 18:16 initDB.sh-rwxrwxr-x 1 yashan yashan 2104 Nov  9 18:16 initStandby.sh-rw-rw-r-- 1 yashan yashan  411 Nov  9 18:16 install.ini-rwxrwxr-x 1 yashan yashan 1412 Nov  9 18:16 install.sh-rwxrwxr-x 1 yashan yashan 1157 Nov  9 18:16 startup.sh-rwxrwxr-x 1 yashan yashan 1157 Nov  9 18:16 stop.sh-rwxrwxr-x 1 yashan yashan  667 Nov  9 18:16 yascheckStart.sh[yashan@jieke-19c scripts]$ more install.ini [install]YASDB_HOME=~/yashandb/yasdb_homeYASDB_DATA=~/yashandb/yasdb_dataREDO_FILE_SIZE=100MREDO_FILE_NUM=4INSTALL_SIMPLE_SCHEMA_SALES=NNLS_CHARACTERSET=UTF8[instance]LISTEN_ADDR=0.0.0.0:1688DB_BLOCK_SIZE=8KDATA_BUFFER_SIZE=256MSHARE_POOL_SIZE=256MWORK_AREA_POOL_SIZE=32MLARGE_POOL_SIZE=32MREDO_BUFFER_SIZE=8MUNDO_RETENTION=300OPEN_CURSORS=310MAX_SESSIONS=1024RUN_LOG_LEVEL=INFONODE_ID=1-1:1[yashan@jieke-19c scripts]$ more install.sh#!/bin/bash#install.shFILE_PATH=$(dirname "$(readlink -f "$0")")PACKAGE_PATH=$(dirname "${FILE_PATH}")YASDB_TEMP_FILE="${FILE_PATH}/.temp.ini"YASDB_INSTALL_FILE="${FILE_PATH}/install.ini"
# shellcheck disable=SC2002cat "${YASDB_INSTALL_FILE}" | grep "=" > "${YASDB_TEMP_FILE}"# shellcheck disable=SC1090source "${YASDB_TEMP_FILE}"
if [ -d "$YASDB_HOME" ]; then    echo -e "install failed!\n$YASDB_HOME already exists."    exit 1fiif [ -f "$YASDB_DATA"/config/yasdb.ini ]; then    echo -e "install failed!\n$YASDB_DATA used, yasdb.ini is already exists"    exit 1fi
##创建数据库软件目录mkdir -p "$YASDB_HOME"cp -ra "$PACKAGE_PATH"/{admin,bin,conf,gitmoduleversion.dat,include,java,lib,plug-in,scripts} "$YASDB_HOME"mkdir -p "$YASDB_HOME"/clienttouch "$YASDB_HOME"/client/yasc_service.ini
##创建数据库数据目录mkdir -p "$YASDB_DATA"/{config,data,dbfiles,instance,archive,local_fs,log/{run,audit,trace,alarm,alert,listener},diag/{metadata,hm,blackbox}}
##配置数据库环境变量sed -i '/'"source ${YASDB_HOME//\//\\/}\/conf\/yasdb.bashrc"'/d' ~/.bashrc
YASDB_ENV_FILE="${YASDB_HOME}/conf/yasdb.bashrc"cat >"${YASDB_ENV_FILE}" <export YASDB_HOME=$YASDB_HOMEexport YASDB_DATA=$YASDB_DATAexport PATH=\$YASDB_HOME/bin:\$PATHexport LD_LIBRARY_PATH=\$YASDB_HOME/lib:\$LD_LIBRARY_PATHEOF
cat >>~/.bashrc <[ -f $YASDB_ENV_FILE ] && source $YASDB_ENV_FILEEOF
[yashan@jieke-19c ~]$ more /home/yashan/soft/scripts/initDB.sh #!/bin/bash#initDB.shFILE_PATH=$(dirname "$(readlink -f "$0")")YASDB_TEMP_FILE="${FILE_PATH}/.temp.ini"INSTALL_INI_FILE="${FILE_PATH}/install.ini"YASDB_PASSWORD="yasdb_123"
# shellcheck disable=SC1090source "${YASDB_TEMP_FILE}"YASDB_ENV_FILE="${YASDB_HOME}/conf/yasdb.bashrc"YASDB_HOME_BIN_PATH="${YASDB_HOME}/bin"YASDB_BIN="${YASDB_HOME_BIN_PATH}/yasdb"YASQL_BIN="${YASDB_HOME_BIN_PATH}/yasql"YASPWD_BIN="${YASDB_HOME_BIN_PATH}/yaspwd"
# shellcheck disable=SC1090source "${YASDB_ENV_FILE}"
if [ ! -d "$YASDB_HOME" ] || [ ! -d "$YASDB_DATA" ]; then    echo -e "Software installation \"./install.sh\" is not performed."    exit 1fi
if [ -f "$YASDB_DATA"/config/yasdb.ini ]; then    echo -e "init failed!\n$YASDB_DATA used, yasdb.ini is already exists"    echo -e "do not reinit in $YASDB_DATA, if you want start yasdb, run \"./startup.sh\""    exit 1fi
e_i=$(sed -n '$=' "$INSTALL_INI_FILE")s_i=$(sed -n -e '/\/=' "$INSTALL_INI_FILE")n_i=$((s_i + 1))
sed -n "${n_i},${e_i} p" "$INSTALL_INI_FILE" >>"$YASDB_DATA"/config/yasdb.ini
##创建密码文件if [ ! -f "$YASDB_HOME/admin/yasdb.pwd" ]; then    "$YASPWD_BIN" file="$YASDB_HOME"/admin/yasdb.pwd password="$YASDB_PASSWORD"else    rm -f "$YASDB_HOME"/admin/yasdb.pwd    "$YASPWD_BIN" file="$YASDB_HOME"/admin/yasdb.pwd password="$YASDB_PASSWORD"ficp "$YASDB_HOME"/admin/yasdb.pwd "$YASDB_DATA"/instance/yasdb.pwd
REDOFILE="("for ((i = 0; i < "$REDO_FILE_NUM"; i++)); do    if [ $i == $((REDO_FILE_NUM - 1)) ]; then        REDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE)"    else        REDOFILE=${REDOFILE}"'redo${i}'"" size $REDO_FILE_SIZE,"    fidone
##创建数据库START_LOG_FILE="$YASDB_DATA/log/start.log"rm -rf "${START_LOG_FILE}""${YASDB_BIN}" nomount -D "$YASDB_DATA" >"$START_LOG_FILE" 2>&1 &i=0while ((i < 5))do    sleep 2    # shellcheck disable=SC2002 disable=SC2126    alive=$(cat "$START_LOG_FILE" | grep "Instance started" | wc -l)    if [ "$alive" -ne 0 ]; then        echo "process started!"        break    fi    i=$((i+1))done
if [ "$i" -eq "5" ];then    echo "start process failed. read $START_LOG_FILE"    cat "$START_LOG_FILE"    exit 1fi
"${YASQL_BIN}" sys/$YASDB_PASSWORD >>"$START_LOG_FILE" <create database yasdb CHARACTER SET $NLS_CHARACTERSET logfile $REDOFILE;exit;EOF
i=0while ((i < 60))do    sleep 1    alive=$($YASQL_BIN sys/$YASDB_PASSWORD -c "select open_mode from v\$database" | grep -c READ_WRITE)    if [ "$alive" -eq 1 ]; then        echo "Database open succeed !"        break    fi    i=$((i+1))done
if [ "$i" -eq "60" ];then    echo "Failed ! please check logfile $START_LOG_FILE ."    exit 1fi
##创建样例数据:salesif [ "$INSTALL_SIMPLE_SCHEMA_SALES" == 'Y' ] || [ "$INSTALL_SIMPLE_SCHEMA_SALES" == 'y' ]; then    "${YASQL_BIN}" sys/$YASDB_PASSWORD -f "$YASDB_HOME"/admin/simple_schema/sales.sql >>"$START_LOG_FILE"fiexit 0####密码以二进制形式存储，不可读。根据上面 initDB.sh 文件中的 YASDB_PASSWORD 可知密码为 yasdb_123[yashan@jieke-19c ~]$ strings  $YASDB_HOME/admin/yasdb.pwdS:5C8774C730A775DF857AC562BBA45B20B07C7BB8123D75F03C9FA007645B69CF5168AD585B10A5F795E0

执行 install.sh 没有输出记录，但是在当前用户家目录下生成了 yashandb 子目录，此目录下包含 yasdb_home 和 yasdb_data 目录。

[yashan@jieke-19c scripts]$ sh install.sh[yashan@jieke-19c scripts]$ cd [yashan@jieke-19c ~]$ lltotal 0drwxrwxr-x 11 yashan yashan 220 Nov 22 16:27 softdrwxrwxr-x  4 yashan yashan  42 Nov 22 17:42 yashandb[yashan@jieke-19c ~]$ cd yashandb/[yashan@jieke-19c yashandb]$ lltotal 0drwxrwxr-x 10 yashan yashan 117 Nov 22 17:42 yasdb_datadrwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home[yashan@jieke-19c yashandb]$ du -sh *0  yasdb_data432M  yasdb_home[yashan@jieke-19c yashandb]$ pwd/home/yashan/yashandb
--初始化数据库实例，类似于 Oracle 的 dbca [yashan@jieke-19c scripts]$ cd /home/yashan/soft/scripts[yashan@jieke-19c scripts]$ ./initDB.shprocess started!Database open succeed !
--初始化之后会生成 yasdb_data 的文件，看着目录结构和 O 记很像很像。[yashan@jieke-19c yashandb]$ lltotal 0drwxrwxr-x 11 yashan yashan 128 Nov 27 11:01 yasdb_datadrwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home[yashan@jieke-19c yashandb]$ tree -L 3 yasdb_datayasdb_data+-- archive+-- config¦   +-- yasdb.ini+-- data+-- dbfiles¦   +-- ctrl1¦   +-- ctrl2¦   +-- ctrl3¦   +-- dwf¦   +-- redo0¦   +-- redo1¦   +-- redo2¦   +-- redo3¦   +-- swap¦   +-- sysaux¦   +-- system¦   +-- temp¦   +-- undo¦   +-- users+-- diag¦   +-- adr.pid¦   +-- blackbox¦   +-- hm¦   +-- metadata¦   ¦   +-- hm_finding¦   ¦   +-- hm_run¦   ¦   +-- incident¦   ¦   +-- problem¦   +-- trace+-- instance¦   +-- yasdb.ipc¦   +-- yasdb.pid¦   +-- yasdb.pwd+-- local_fs¦   +-- users+-- log¦   +-- alarm¦   +-- alert¦   ¦   +-- alert.log¦   +-- audit¦   +-- listener¦   ¦   +-- listener.log¦   +-- run¦   ¦   +-- run.log¦   +-- slow¦   ¦   +-- slow.log¦   +-- start.log¦   +-- trace+-- tmp
21 directories, 28 files
--生效环境变量[yashan@jieke-19c scripts]$ cd[yashan@jieke-19c ~]$ . ~/.bashrc

7.登录数据库

通过上述方式脚本安装的数据库，据上面脚本介绍 sys 默认密码是 yasdb_123。

[yashan@jieke-19c ~]$ yasql / as sysdbaYashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> select status,version from v$instance;
STATUS        VERSION                                                          ------------- ---------------------------------------------------------------- OPEN          Personal Edition Release 23.1.1.100 x86_64                      
1 row fetched.
SQL> exit[yashan@jieke-19c ~]$ yasql sys/yasdb_123         --居然不用 sysdba 方式登录YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> exit[yashan@jieke-19c ~]$ yasql sys/yasdb_12          --错误密码无法登录YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
YAS-02143 invalid username/password, login denied
please input user name: 

-- 关闭数据库/重启数据库SQL> shutdown immediate;Succeed.
SQL> exit [yashan@jieke-19c ~]$ nohup yasdb open &[1] 980[yashan@jieke-19c ~]$ ps -ef | grep yasdbyashan     980 31966 21 11:00 pts/0    00:00:10 yasdb openyashan    1171 31966  0 11:01 pts/0    00:00:00 grep --color=auto yasdb[yashan@jieke-19c ~]$ yasql / as sysdbaYashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux
SQL> select status from v$instance;
STATUS        ------------- OPEN         
1 row fetched.SQL> SELECT NAME,STATUS,EXTENT_BLOCKS,ALLOCATION_TYPE,MEMORY_MAPPED,ENCRYPTED,COMPRESSED FROM V$TABLESPACE;
NAME                                                             STATUS            EXTENT_BLOCKS ALLOCATION_TYPE MEMORY_MAPPED ENCRYPTED COMPRESSED ---------------------------------------------------------------- ----------------- ------------- --------------- ------------- --------- ---------- SYSTEM                                                           ONLINE                        8 AUTO            FALSE         FALSE     FALSE     SYSAUX                                                           ONLINE                        8 AUTO            FALSE         FALSE     FALSE     TEMP                                                             ONLINE                        8 UNIFORM         FALSE         FALSE     FALSE     SWAP                                                             ONLINE                        8 UNIFORM         FALSE         FALSE     FALSE     USERS                                                            ONLINE                        8 AUTO            FALSE         FALSE     FALSE     UNDO                                                             ONLINE                        1 UNIFORM         FALSE         FALSE     FALSE     
6 rows fetched.

8.查看崖山数据库线程

[yashan@jieke-19c bin]$ pwd/home/yashan/yashandb/yasdb_home/bin[yashan@jieke-19c bin]$ ls -l /home/yashan/yashandb/yasdb_home/bintotal 59076-rwxr-xr-x 1 yashan yashan   101320 Nov  9 18:16 exp-rwxr-xr-x 1 yashan yashan    79944 Nov  9 18:16 imp-rwxrwxr-x 1 yashan yashan 19865208 Nov  9 18:16 yasagent-rwxrwxr-x 1 yashan yashan  5986112 Nov  9 18:16 yasbak-rwxrwxr-x 1 yashan yashan  9381440 Nov  9 18:16 yasboot-rwxr-xr-x 1 yashan yashan   181096 Nov  9 18:16 yasdb-rwxr-xr-x 1 yashan yashan   779640 Nov  9 18:16 yasldr-rwxrwxr-x 1 yashan yashan 21896816 Nov  9 18:16 yasom-rwxr-xr-x 1 yashan yashan    66072 Nov  9 18:16 yaspwd-rwxr-xr-x 1 yashan yashan   645680 Nov  9 18:16 yasql-rwxr-xr-x 1 yashan yashan   773256 Nov  9 18:16 yasrman-rwxr-xr-x 1 yashan yashan   120136 Nov  9 18:16 yaswrap-rwxr-xr-x 1 yashan yashan   590416 Nov  9 18:16 yex_server
--查看数据库进程 980 对应的线程 top -Hp 980[yashan@jieke-19c bin]$ ps -ef | grep yasdbyashan     980     1 14 11:00 pts/0    00:04:39 yasdb openyashan    4716  2449  0 11:33 pts/0    00:00:00 grep --color=auto yasdb[yashan@jieke-19c bin]$  ps -Tp 980  PID  SPID TTY          TIME CMD  980   980 pts/0    00:00:03 yasdb  980   981 pts/0    00:00:33 TIMER  980   982 pts/0    00:00:04 BUFFER_POOL  980   983 pts/0    00:00:00 PRELOADER  980   984 pts/0    00:00:00 PRELOADER  980   985 pts/0    00:00:00 SMON  980   986 pts/0    00:00:05 CKPT  980   988 pts/0    00:00:00 DBWR  980   989 pts/0    00:00:00 DBWR  980   990 pts/0    00:00:00 SCHD_TIMER  980   991 pts/0    00:00:04 LISTENER_LOG  980   992 pts/0    00:00:00 TCP_LSNR  980   993 pts/0    00:00:00 TCP_LSNR  980  1013 pts/0    00:00:00 HEALTH_MONITOR  980  1033 pts/0    00:00:00 HOT_CACHE_RECYC  980  1034 pts/0    00:00:00 LOGW  980  1041 pts/0    00:00:07 XFMR  980  1042 pts/0    00:01:31 XFMR_WORKER_0  980  1043 pts/0    00:00:03 MMON  980  1044 pts/0    00:00:00 JOB_QUEUE  980  1045 pts/0    00:00:00 XFMR_WORKER_1  980  1046 pts/0    00:00:00 XFMR_WORKER_2  980  1047 pts/0    00:00:00 XFMR_WORKER_3  980  1048 pts/0    00:00:00 XFMR_WORKER_4  980  1049 pts/0    00:00:00 XFMR_WORKER_5  980  1050 pts/0    00:00:00 XFMR_WORKER_6  980  1051 pts/0    00:00:00 XFMR_WORKER_7  980  1052 pts/0    00:00:00 XFMR_WORKER_8  980  1053 pts/0    00:00:00 XFMR_WORKER_9  980  1054 pts/0    00:00:00 XFMR_WORKER_10  980  1055 pts/0    00:00:00 XFMR_WORKER_11  980  1056 pts/0    00:00:00 XFMR_WORKER_12  980  1057 pts/0    00:00:00 XFMR_WORKER_13  980  1058 pts/0    00:00:00 XFMR_WORKER_14  980  1059 pts/0    00:00:00 XFMR_WORKER_15  980  1060 pts/0    00:00:00 XFMR_WORKER_16  980  1061 pts/0    00:00:00 XFMR_WORKER_17  980  1062 pts/0    00:00:00 XFMR_WORKER_18  980  1063 pts/0    00:00:00 XFMR_WORKER_19  980  1066 pts/0    00:00:00 XFMR_WORKER_20  980  1067 pts/0    00:00:00 XFMR_WORKER_21  980  1068 pts/0    00:00:00 XFMR_WORKER_22  980  1069 pts/0    00:00:00 XFMR_WORKER_23  980  1070 pts/0    00:00:00 XFMR_WORKER_24  980  1071 pts/0    00:00:00 XFMR_WORKER_25  980  1072 pts/0    00:00:00 XFMR_WORKER_26  980  1073 pts/0    00:00:00 XFMR_WORKER_27  980  1074 pts/0    00:00:00 XFMR_WORKER_28  980  1075 pts/0    00:00:00 XFMR_WORKER_29  980  1076 pts/0    00:00:00 XFMR_WORKER_30  980  1077 pts/0    00:00:00 XFMR_WORKER_31
  -- v$process 视图也可以看到上述线程。  SQL> select * from v$process;

可以看到 YashanDB 是单进程多线程架构，Oracle 中的很多进程，SMON、MMON、CKPT、DBWR 等进程在崖山数据库中都以线程的形式存在。

9.修改参数及数据插入对比

--Oracle 19.12 单机文件系统，配置为 8c16g 的同一台虚拟机，且均开启了归档日志
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 28 11:10:48 2023Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.12.0.0.0
SYS@test> show parameter sga_
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------sga_max_size                         big integer 6560Msga_min_size                         big integer 0sga_target                           big integer 6560Munified_audit_sga_queue_size         integer     1048576SYS@test> !free -h              total        used        free      shared  buff/cache   availableMem:            15G        1.7G        5.4G        6.2G        8.4G        7.4GSwap:          7.9G         16M        7.9G
--同样我们需要设置 YashanDB  的参数为上面 SGA 的大小，并重启数据库--查看参数show parameter DATA_BUFFER_SIZE  --256Mshow parameter VM_BUFFER_SIZE    --32M--修改参数并重启数据库alter system set DATA_BUFFER_SIZE=6560M scope=spfile;alter system set VM_BUFFER_SIZE=6560M scope=spfile;
shutdown immediate;exitnohup yasdb open &

Oracle 19c 导出操作

grant connect,resource,dba to test;conn test/testcreate table T_base_table as select * from dba_objects;select count(*) from T_base_table;
  COUNT(*)----------    131341
Elapsed: 00:00:00.54
--获取此基表 DDL 建表语句，并将其简单修改下TEST@test> set long 9999TEST@test> SELECT DBMS_METADATA.GET_DDL('TABLE','T_BASE_TABLE','TEST') DDL_SQL FROM DUAL; 
CREATE TABLE "TEST"."T_BASE_TABLE"   (    "OWNER" VARCHAR2(128) ,        "OBJECT_NAME" VARCHAR2(128) ,        "SUBOBJECT_NAME" VARCHAR2(128) ,        "OBJECT_ID" NUMBER,        "DATA_OBJECT_ID" NUMBER,        "OBJECT_TYPE" VARCHAR2(23) ,        "CREATED" DATE,        "LAST_DDL_TIME" DATE,        "TIMESTAMP" VARCHAR2(19) ,        "STATUS" VARCHAR2(7) ,        "TEMPORARY" VARCHAR2(1) ,        "GENERATED" VARCHAR2(1) ,        "SECONDARY" VARCHAR2(1) ,        "NAMESPACE" NUMBER,        "EDITION_NAME" VARCHAR2(128) ,        "SHARING" VARCHAR2(18) ,        "EDITIONABLE" VARCHAR2(1) ,        "ORACLE_MAINTAINED" VARCHAR2(1) ,        "APPLICATION" VARCHAR2(1) ,        "DEFAULT_COLLATION" VARCHAR2(100) ,        "DUPLICATED" VARCHAR2(1) ,        "SHARDED" VARCHAR2(1) ,        "CREATED_APPID" NUMBER,        "CREATED_VSNID" NUMBER,        "MODIFIED_APPID" NUMBER,        "MODIFIED_VSNID" NUMBER   )  TABLESPACE "TEST";
--使用 sqluldr2 导出 CSV 文件，将其导入到崖山数据库 --当使用 table 参数时，在目录下会生成对应的 ctl 控制文件，如下语句会生成 t_base_table_sqlldr.ctl 文件。./sqluldr2_linux64_10204.bin test/test query="select * from t_base_table" table=t_base_table  file=/home/oracle/tmp/sqluldr2/t_base_table.csv           0 rows exported at 2023-11-28 15:38:18, size 0 MB.      131341 rows exported at 2023-11-28 15:38:19, size 19 MB.         output file /home/oracle/tmp/sqluldr2/t_base_table.csv closed at 131341 rows, size 19 MB.cp t_base_table* /tmp/

YashanDB 导入数据

Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;TABLESPACE_NAME                                                  ---------------------------------------------------------------- SYSTEM                                                          SYSAUX                                                          TEMP                                                            SWAP                                                            USERS                                                           UNDO                                                            6 rows fetched.
--YashanDB 没有类似 oracle 的 col name for a30 这样的格式化语句，显示看的很难受。SQL> select file_id,file_name from dba_data_files;
     FILE_ID FILE_NAME                                                        ------------ ----------------------------------------------------------------            0 /home/yashan/yashandb/yasdb_data/dbfiles/system                            1 /home/yashan/yashandb/yasdb_data/dbfiles/sysaux                            2 /home/yashan/yashandb/yasdb_data/dbfiles/temp                              3 /home/yashan/yashandb/yasdb_data/dbfiles/swap                              4 /home/yashan/yashandb/yasdb_data/dbfiles/users                             5 /home/yashan/yashandb/yasdb_data/dbfiles/undo                   6 rows fetched.--创建表空间和用户SQL> CREATE TABLESPACE test datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' size 1g;Succeed.SQL> CREATE USER test IDENTIFIED BY test;SQL> grant dba to test;SQL> grant connect to test;SQL> grant resource to test;SQL> alter user test default tablespace test;SQL> select username,account_status,default_tablespace from dba_users;

yasldr 是 YashanDB 提供的数据快速导入工具，用于将 CSV 格式的数据文件快速导入至崖山数据库中，支持通过数据库连接向远端节点进行数据导入。

-- yasldr 查看帮助信息[yashan@jieke-19c ~]$ yasldr -HYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd
YashanDB LOADER HELP:To specify parameters, should use keywords:  Command Format:    YASLDR USERNAME/PASSWORD@IP:PORT KEYWORD=value
    "USERNAME/PASSWORD@URL" must be the first on the command line, "@URL" default 127.0.0.1:1688    KEYWORD                    DESCRIPTION (DEFAULT)    ---------------------------------------------------------------------------------------------------    BATCH_SIZE                 the number of lines per batch, default 4032, range [1, 65535]    CONTROL_FILE               a file containing the LOAD statement    CONTROL_TEXT               the LOAD statement, only be used when CONTROL_FILE is not specified    MODE                       import mode, including BASIC/BATCH, default BATCH    PACKET_SIZE                client expected packet size, default 128KB, range [65536, 524288]    CONN_POOL_SIZE             client connection pool size, default 5, range [1, 32]
  Example:    YASLDR USERNAME/PASSWORD@IP:PORT CONTROL_FILE=LOAD.CTL
--使用 yasldr -V 命令可查看版本信息。[yashan@jieke-19c ~]$ yasldr -VYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd
--命令格式$ YASLDR USERNAME/PASSWORD@IP:PORT {LOAD Options} {LOAD Statement} 
--官方手册参考链接：https://doc.yashandb.com/yashandb/23.1/zh/%E5%B7%A5%E5%85%B7%E6%89%8B%E5%86%8C/yasldr/yasldr%E4%BD%BF%E7%94%A8%E6%8C%87%E5%AF%BC.html--参考导出的 ctl 文件和官方文档给出的示例编写 ctl 控制文件vim t_base_table.ctlLOAD DATA INFILE '/tmp/t_base_table.csv' FIELDS TERMINATED BY ',' optionally enclosed by '"'INTO TABLE t_base_table       ("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"        )
--导入数据[yashan@jieke-19c tmp]$ yasldr test/test@127.0.0.1:1688 batch_size=4032 mode=batch packet_size=131072 control_file=/tmp/t_base_table.ctlYashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd131341 rows successfully loaded.Check /tmp/t_base_table.log for more info.[YASLDR] execute succeeded
#查询数据库表中的数据条数是否和 Oracle 中条数一致[yashan@jieke-19c tmp]$ yasql test/test@127.0.0.1:1688 -c "select count(*) from t_base_table"
             COUNT(*) ---------------------                131341
1 row fetched.
[yashan@jieke-19c tmp]$ yasql / as sysdba
SQL> select sysdate from dual;
SYSDATE                          -------------------------------- 2023-11-28                      SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYY                                             ---------------------------------------------------------------- 2023-11-28 17:34:47  
SQL> select rowid,object_id from test.t_base_table where rownum<=3;
ROWID                                          OBJECT_ID -------------------------------------------- ----------- 2272:6:0:132:0                                        162272:6:0:132:1                                        202272:6:0:132:2                                         8
3 rows fetched.SQL> select object_id,data_object_id from dba_objects where object_name='T_BASE_TABLE';
            OBJECT_ID        DATA_OBJECT_ID --------------------- ---------------------                  2272                  2272SQL> select file_id from dba_data_files where tablespace_name='TEST';
     FILE_ID ------------            6

不难看出崖山数据库的 ROWID 结构为五组数字用冒号分割来表示：daba_object_id:tablespace_file_id:file_id:block_number:row#。

简单查询对比

接下来我们进行一个简单的插入和表关联对比，为了避免影响，同一时刻只有一个数据库是启动状态的，现在我们在 Oracle 中进行插入数据，关闭崖山数据库。

Oracle 19c

#### oracle 19calter database datafile 7 resize 10g;conn test/testcreate table T_BIG_TABLE as select * from T_BASE_TABLE;
begin for i in 1..8 loopinsert into T_BIG_TABLE select * from T_BIG_TABLE;end loop;commit;end;/
Elapsed: 00:03:21.09
select count(*) from T_BIG_TABLE;
  COUNT(*)----------  33623296
Elapsed: 00:01:12.27
create table T_SMALL_TABLE as select * from T_BASE_TABLE;
begin for i in 1..4 loopinsert into T_SMALL_TABLE select * from T_SMALL_TABLE;end loop;commit;end;/
Elapsed: 00:00:04.50
select count(*) from   T_SMALL_TABLE;
  COUNT(*)----------   2101456
Elapsed: 00:00:00.24
select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
  COUNT(*)---------- 537956352
Elapsed: 00:01:16.32
create index idx_t_big_table_id on t_big_table(object_id);
Index created.
Elapsed: 00:02:33.97
create index idx_t_small_table_id on t_small_table(object_id);
Index created.
Elapsed: 00:00:07.62
 select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
  COUNT(*)---------- 537956352
Elapsed: 00:02:55.90
22:16:26 TEST@test> set autot traceonly22:16:32 TEST@test> select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;

Elapsed: 00:02:41.73
Execution Plan----------------------------------------------------------Plan hash value: 188159531
--------------------------------------------------------------------------------------------| Id  | Operation           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |                      |     1 |    10 |   263K  (1)| 00:00:11 ||   1 |  SORT AGGREGATE     |                      |     1 |    10 |            |          ||   2 |   NESTED LOOPS      |                      |   131K|  1282K|   263K  (1)| 00:00:11 ||   3 |    TABLE ACCESS FULL| T_BIG_TABLE          |   131K|   641K|   344   (2)| 00:00:01 ||*  4 |    INDEX RANGE SCAN | IDX_T_SMALL_TABLE_ID |     1 |     5 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):Total hints for statement: 1 (U - Unused (1))---------------------------------------------------------------------------
   3 -  SEL$1 / A@SEL$1         U -  USE_NL(A B)

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets   38649498  consistent gets     444251  physical reads          0  redo size        554  bytes sent via SQL*Net to client        438  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

现在关闭 Oracle 数据库，启动 YashanDB 执行上述操作，我们先扩一下 test 表空间,然后使用相同 SQL 插入相同数据量的数据进行简单查询。

YashanDB 个人版

#### YashanDB 个人版YashanDB SQL Personal Edition Release 23.1.1.100 x86_64
Connected to:YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit LinuxSQL> SQL> alter database datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' resize 10g;
conn test/testcreate table T_BIG_TABLE as select * from T_BASE_TABLE;
begin for i in 1..8 loopinsert into T_BIG_TABLE select * from T_BIG_TABLE;end loop;commit;end;/
Elapsed: 00:02:51.247
select count(*) from T_BIG_TABLE;
  COUNT(*)----------  33623296
Elapsed: 00:00:03.837
select object_id,data_object_id from dba_objects where object_name='T_SMALL_TABLE';
            OBJECT_ID        DATA_OBJECT_ID --------------------- ---------------------                  2274                  2288
create table T_SMALL_TABLE as select * from T_BASE_TABLE;
begin for i in 1..4 loopinsert into T_SMALL_TABLE select * from T_SMALL_TABLE;end loop;commit;end;/
Elapsed: 00:00:07.631
select count(*) from T_SMALL_TABLE;
  COUNT(*)----------   2101456
Elapsed: 00:00:00.260
select /*+ USE_HASH(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
  COUNT(*)---------- 537956352
Elapsed: 00:03:23.322
--创建索引SQL> create index idx_t_big_table_id on t_big_table(object_id);Elapsed: 00:01:44.781
SQL> create index idx_t_small_table_id on t_small_table(object_id);
Elapsed: 00:00:04.731
SQL> select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
             COUNT(*) ---------------------             537956352
Elapsed: 00:03:39.800
SQL> explain select /*+ USE_NL(A B) */ count(*) from T_BIG_TABLE A,T_SMALL_TABLE B WHERE A.object_id=B.object_id;
PLAN_DESCRIPTION                                                 ---------------------------------------------------------------- SQL hash value: 1167597945                                      Optimizer: ADOPT_C                                              
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+|  0 | SELECT STATEMENT               |                      |            |          |             |                                ||  1 |  AGGREGATE                     |                      |            |         1|      152( 0)|                                ||  2 |   NESTED LOOPS INNER           |                      |            |    100000|      150( 0)|                                ||  3 |    INDEX FAST FULL SCAN        | IDX_T_BIG_TABLE_ID   | TEST       |    100000|       91( 0)|                                ||* 4 |    INDEX RANGE SCAN            | IDX_T_SMALL_TABLE_ID | TEST       |         1|        6( 0)|                                |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):             ---------------------------------------------------             
   4 - Predicate : access("B"."OBJECT_ID" = "A"."OBJECT_ID")    
17 rows fetched.
Elapsed: 00:00:00.001

汇总对比结果

这个结果和上次使用企业版体验的结果有所差别，小表的插入和查询还是 Oracle 19c 更快一些，当然可能的原因是上次使用的是配置比较低的个人虚拟机安装的 Oracle 12c 版本，本次使用的是公司的虚拟机环境，个人感觉是比较公平公正的测试了，如果有其他意见或建议欢迎交流。总体来看，YashanDB 在大表插入及查询以及创建索引的过程要比 Oracle 19c 更快一些，但是在 Hash join 和 Nest LOOP 关联查询中，性能偏低一些。

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下三个地址可以找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。

——————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理

Oracle 19c RAC 遇到的几个问题
OGG|Oracle 数据迁移后比对一致性
OGG|Oracle GoldenGate 微服务架构
Oracle 查询表空间使用率超慢问题一则
Oracle 11g升级到19c需要关注的几个问题
国产数据库|TiDB 5.4 单机快速安装初体验
Oracle ADG 备库停启维护流程及增量恢复
Linux 环境搭建 MySQL8.0.28 主从同步环境
从国产数据库调研报告中你都能了解哪些信息及我的总结建议