YashanDB 23.1 个人版脚本安装及简单查询测试

JiekeXu之路

发布于 2023-11-29 12:47:42

1910

发布于 2023-11-29 12:47:42

文章被收录于专栏：JiekeXu之路JiekeXu之路

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 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 ~]# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    1
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E7-4809 v4 @ 2.10GHz
Stepping:              1
CPU MHz:               2094.952
BogoMIPS:              4189.90
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              20480K
NUMA node0 CPU(s):     0-7
Flags:                 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 -l
8
[root@jieke-19c ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:            15G        1.1G        6.1G        6.3G        8.3G        7.9G
Swap:          7.9G         16M        7.9G
[root@jieke-19c ~]# df -h
Filesystem                 Size  Used Avail Use% Mounted on
/dev/mapper/rootvg-lvroot  392G  314G   78G  81% /
devtmpfs                   7.8G     0  7.8G   0% /dev
tmpfs                      7.8G   56K  7.8G   1% /dev/shm
tmpfs                      7.8G   81M  7.7G   2% /run
tmpfs                      7.8G     0  7.8G   0% /sys/fs/cgroup
/dev/sda1                 1014M  190M  825M  19% /boot
tmpfs                      1.6G     0  1.6G   0% /run/user/54321
tmpfs                      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 65536
yashan hard nofile 65536
yashan soft nproc 65536
yashan hard nproc 65536
yashan soft rss unlimited
yashan hard rss unlimited
yashan soft stack 8192
yashan 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]$ ll
total 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]$ ll
total 171312
drwxrwxr-x 6 yashan yashan        70 Nov  9 18:16 admin
drwxrwxr-x 2 yashan yashan       188 Nov  9 18:16 bin
drwxrwxr-x 2 yashan yashan       152 Nov  9 18:16 conf
drwxrwxr-x 5 yashan yashan        60 Nov  9 18:16 ext
-rw-rw-r-- 1 yashan yashan     11632 Nov  9 18:16 gitmoduleversion.dat
drwxrwxr-x 2 yashan yashan        65 Nov  9 18:16 include
drwxrwxr-x 3 yashan yashan        17 Nov  9 18:16 java
drwxrwxr-x 2 yashan yashan      4096 Nov  9 18:16 lib
-rw-r----- 1 yashan yashan     14989 Nov  9 18:16 LICENSE
drwxrwxr-x 3 yashan yashan        21 Nov  9 18:16 plug-in
drwxrwxr-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]$ ll
total 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_home
YASDB_DATA=~/yashandb/yasdb_data
REDO_FILE_SIZE=100M
REDO_FILE_NUM=4
INSTALL_SIMPLE_SCHEMA_SALES=N
NLS_CHARACTERSET=UTF8
[instance]
LISTEN_ADDR=0.0.0.0:1688
DB_BLOCK_SIZE=8K
DATA_BUFFER_SIZE=256M
SHARE_POOL_SIZE=256M
WORK_AREA_POOL_SIZE=32M
LARGE_POOL_SIZE=32M
REDO_BUFFER_SIZE=8M
UNDO_RETENTION=300
OPEN_CURSORS=310
MAX_SESSIONS=1024
RUN_LOG_LEVEL=INFO
NODE_ID=1-1:1
[yashan@jieke-19c scripts]$ more install.sh
#!/bin/bash
#install.sh
FILE_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=SC2002
cat "${YASDB_INSTALL_FILE}" | grep "=" > "${YASDB_TEMP_FILE}"
# shellcheck disable=SC1090
source "${YASDB_TEMP_FILE}"

if [ -d "$YASDB_HOME" ]; then
    echo -e "install failed!\n$YASDB_HOME already exists."
    exit 1
fi
if [ -f "$YASDB_DATA"/config/yasdb.ini ]; then
    echo -e "install failed!\n$YASDB_DATA used, yasdb.ini is already exists"
    exit 1
fi

##创建数据库软件目录
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"/client
touch "$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}" <<EOF
export YASDB_HOME=$YASDB_HOME
export YASDB_DATA=$YASDB_DATA
export PATH=\$YASDB_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$YASDB_HOME/lib:\$LD_LIBRARY_PATH
EOF

cat >>~/.bashrc <<EOF
[ -f $YASDB_ENV_FILE ] && source $YASDB_ENV_FILE
EOF

[yashan@jieke-19c ~]$ more /home/yashan/soft/scripts/initDB.sh 
#!/bin/bash
#initDB.sh
FILE_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=SC1090
source "${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=SC1090
source "${YASDB_ENV_FILE}"

if [ ! -d "$YASDB_HOME" ] || [ ! -d "$YASDB_DATA" ]; then
    echo -e "Software installation \"./install.sh\" is not performed."
    exit 1
fi

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 1
fi

e_i=$(sed -n '$=' "$INSTALL_INI_FILE")
s_i=$(sed -n -e '/\<instance\>/=' "$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"
fi
cp "$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,"
    fi
done

##创建数据库
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=0
while ((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 1
fi

"${YASQL_BIN}" sys/$YASDB_PASSWORD >>"$START_LOG_FILE" <<EOF
create database yasdb CHARACTER SET $NLS_CHARACTERSET logfile $REDOFILE;
exit;
EOF

i=0
while ((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 1
fi

##创建样例数据:sales
if [ "$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"
fi
exit 0
####密码以二进制形式存储，不可读。根据上面 initDB.sh 文件中的 YASDB_PASSWORD 可知密码为 yasdb_123
[yashan@jieke-19c ~]$ strings  $YASDB_HOME/admin/yasdb.pwd
S:5C8774C730A775DF857AC562BBA45B20B07C7BB8123D75F03C9FA007645B69CF5168AD585B10A5F795E0

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

[yashan@jieke-19c scripts]$ sh install.sh
[yashan@jieke-19c scripts]$ cd 
[yashan@jieke-19c ~]$ ll
total 0
drwxrwxr-x 11 yashan yashan 220 Nov 22 16:27 soft
drwxrwxr-x  4 yashan yashan  42 Nov 22 17:42 yashandb
[yashan@jieke-19c ~]$ cd yashandb/
[yashan@jieke-19c yashandb]$ ll
total 0
drwxrwxr-x 10 yashan yashan 117 Nov 22 17:42 yasdb_data
drwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home
[yashan@jieke-19c yashandb]$ du -sh *
0  yasdb_data
432M  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.sh
process started!
Database open succeed !

--初始化之后会生成 yasdb_data 的文件，看着目录结构和 O 记很像很像。
[yashan@jieke-19c yashandb]$ ll
total 0
drwxrwxr-x 11 yashan yashan 128 Nov 27 11:01 yasdb_data
drwxrwxr-x 11 yashan yashan 152 Nov 22 17:42 yasdb_home
[yashan@jieke-19c yashandb]$ tree -L 3 yasdb_data
yasdb_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 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> 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 yasdb
yashan     980 31966 21 11:00 pts/0    00:00:10 yasdb open
yashan    1171 31966  0 11:01 pts/0    00:00:00 grep --color=auto yasdb
[yashan@jieke-19c ~]$ yasql / as 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> 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/bin
total 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 yasdb
yashan     980     1 14 11:00 pts/0    00:04:39 yasdb open
yashan    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 2023
Version 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 - Production
Version 19.12.0.0.0

SYS@test> show parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 6560M
sga_min_size                         big integer 0
sga_target                           big integer 6560M
unified_audit_sga_queue_size         integer     1048576
SYS@test> !free -h
              total        used        free      shared  buff/cache   available
Mem:            15G        1.7G        5.4G        6.2G        8.4G        7.4G
Swap:          7.9G         16M        7.9G

--同样我们需要设置 YashanDB  的参数为上面 SGA 的大小，并重启数据库
--查看参数
show parameter DATA_BUFFER_SIZE  --256M
show 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;
exit
nohup yasdb open &

Oracle 19c 导出操作

grant connect,resource,dba to test;
conn test/test
create 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 9999
TEST@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 Linux
SQL> 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 -H
YashanDB 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 -V
YashanDB 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.ctl
LOAD 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.ctl
YashanDB Loader Personal Edition Release 23.1.1.100 x86_64 0e623bd
131341 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                                        16
2272:6:0:132:1                                        20
2272: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                  2272
SQL> 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 19c
alter database datafile 7 resize 10g;
conn test/test
create table T_BIG_TABLE as select * from T_BASE_TABLE;

begin 
for i in 1..8 loop
insert 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 loop
insert 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 traceonly
22: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 Linux
SQL> 
SQL> alter database datafile '/home/yashan/yashandb/yasdb_data/dbfiles/test' resize 10g;

conn test/test
create table T_BIG_TABLE as select * from T_BASE_TABLE;

begin 
for i in 1..8 loop
insert 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 loop
insert 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 19.12	YashanDB23.1.1.100	备注
插入3362W T_BIG_TABLE	201.09s	171.247s	Y
Count(*) T_BIG_TABLE	72.27s	3.837s	Y
插入 210W T_SMALL_TABLE	4.50s	7.631s	N
Count(*) T_SMALL_TABLE	0.24s	0.260s	N
两表关联 Hash join 查询	76.32s	203.322s	N
创建索引 T_BIG_TABLE	153.97s	104.781s	Y
创建索引 T_SMALL_TABLE	7.62s	4.731s	Y
两表关联 NESTED LOOPS 查询	175.55s	219.800s	N

这个结果和上次使用企业版体验的结果有所差别，小表的插入和查询还是 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 年公众号历史文章合集整理

Linux 环境搭建 MySQL8.0.28 主从同步环境

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-11-29，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自 JiekeXu之路微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！