前一篇文章简单分享了一些崖山数据库的情况，这里我们继续进行相关的测试和研究，以供大家参考选择。首先我们来看看有哪些参数：
SQL> EXEC DBMS_PARAM.OPTIMIZE();PL/SQL Succeed.SQL> SELECT DBMS_PARAM.SHOW_RECOMMEND() FROM dual;DBMS_PARAM.SHOW_RECO----------------------------------------------------------------********** Recommended Settings For HEAP Table ***********+--------------------------------+-------------+---------+| name | recommend | restart |+--------------------------------+-------------+---------+| DATA_BUFFER_SIZE | 11315M | True || VM_BUFFER_SIZE | 1444M | True || WORK_AREA_STACK_SIZE | 2M | True || WORK_AREA_POOL_SIZE | 64M | True || WORK_AREA_HEAP_SIZE | 512K | True || SHARE_POOL_SIZE | 1444M | True || LARGE_POOL_SIZE | 240M | True || MAX_PARALLEL_WORKERS | 32 | True || SCOL_DATA_BUFFER_SIZE | 128M | True || SCOL_DATA_PRELOADERS | 2 | True || COLUMNAR_WORK_AREA_HEAP_SIZE | 32M | True || COLUMNAR_VM_BUFFER_SIZE | 128M | True || COLUMNAR_BULK_SIZE | 1024 | True || COMPRESSION | LZ4 | True || PQ_POOL_SIZE | 128M | True || MAX_SESSIONS | 1024 | True || MAX_WORKERS | 0 | True || TAB_QUEUE_WINDOW_SIZE | 4 | True || BLOOM_FILTER_FACTOR | 0.3 | True || DEGREE_OF_PARALLEL | 1 | True || MMS_DATA_LOADERS | 8 | True || CHECKPOINT_INTERVAL | 1024M | False || CHECKPOINT_TIMEOUT | 60 | False || REDOFILE_IO_MODE | SYNC | True || DATAFILE_IO_MODE | DEFAULT | True || COMMIT_LOGGING | BATCH | False || RECOVERY_PARALLELISM | 8 | True || REDO_BUFFER_SIZE | 32M | True |+--------------------------------+-------------+---------+Note: You can execute 'DBMS_PARAM.APPLY_RECOMMEND()' to apply the recommend parameters.After applying the parameters, you need to restart the database.1 row fetched.SQL> EXEC DBMS_PARAM.APPLY_RECOMMEND();PL/SQL Succeed.
从这一点来看，还是非常贴心的，基本上一键优化数据库参数，然后apple重启实例即可，此处点个赞。
到这里我就比较好奇，崖山数据库到底多少参数呢，参数看参数文件确实不多，只有几十个而已。这里通过查询动态试图来看看。
SQL> select * from V$FIXED_TABLE;NAME OBJECT_ID TYPE--------------------------------------------------------------X$INSTANCE -129 TABLEX$FIXED_TABLE -130 TABLEX$FIXED_VIEW -131 TABLEX$AXCTOPO -132 TABLEX$SQLTEXT -133 TABLEX$CM_TASK_INFO -134 TABLEX$DIN_STAT -200 TABLEX$DIN_NODE -201 TABLEX$DIN_LINK -202 TABLEX$DATABASE -135 TABLEX$LOGFILE -136 TABLEX$ARCHIVED_LOG -137 TABLEX$ARCHIVE_DEST -138 TABLEX$ARCHIVE_DEST_STATUS -139 TABLEX$REPLICATION_STATUS -140 TABLEX$ARCHIVE_GAP -141 TABLEX$DBWR_STATS -142 TABLEX$TABLESPACE -143 TABLEX$REPLICATION_EVENT -144 TABLEX$REDOSTAT -145 TABLEX$RECOVERY_PROGRESS -146 TABLEX$BACKUP_PROGRESS -147 TABLEX$YFS_DISKGROUP -148 TABLEX$YFS_DISK -149 TABLEX$YFS_FAILGROUP -150 TABLEX$YFS_FILE -151 TABLEX$SPINLOCK -152 TABLEX$SYSTEM_EVENT -153 TABLEX$SYSTEM_WAIT_CLASS -154 TABLEX$TEMPORARY_EXTENT_POOL -155 TABLEX$TRANSACTION -156 TABLEX$UNDO_SEGMENTS -157 TABLEX$TEMPORARY_SEGMENT -158 TABLEX$TABLE_DICTIONARY -159 TABLEX$CONTROLFILE -160 TABLEX$HM_RUN -161 TABLEX$HM_FINDING -162 TABLEX$HM_CHECK_PARAM -163 TABLEX$HM_CHECK -164 TABLEX$DIAG_PROBLEM -165 TABLEX$DIAG_INCIDENT -166 TABLEX$DIAG_FAULT -167 TABLEX$SQLTEXT -133 TABLEX$SHARE_POOL -168 TABLEX$SGASTAT -169 TABLEX$SGA -170 TABLEX$SESSION_WAIT -171 TABLEX$SESSION -172 TABLEX$ROLLBACK -173 TABLEX$PRIVATE_TEMP_TABLES -174 TABLEX$LSC_XFMR_SLICES -175 TABLEX$COLUMNAR_MEM_CACHE -176 TABLEX$CHANNEL_PERF -177 TABLEX$CHECKPOINT -178 TABLEX$BUFFER_POOL_STATISTICS -179 TABLEX$BUFFER_POOL -180 TABLEX$BUFFER_CONTROL -181 TABLEX$BUFFER_ACCESS_STATISTICS -182 TABLEX$LOCKED_OBJECT -183 TABLEX$LOCK -184 TABLEX$LARGE_POOL -185 TABLEX$HOT_CACHE -186 TABLEX$DISKCACHE -187 TABLEX$DICT_CACHE -188 TABLEX$CORRUPTED_TABLE -189 TABLEX$COLUMNAR_MEM_POOL -190 TABLEX$CLUSTER_MESSAGE -199 TABLEX$CLUSTER_MESSAGE_POOL -198 TABLEX$CLUSTER_MESSAGE_STAT -197 TABLEX$CLUSTER_TASK_STAT -196 TABLEX$GLS_LOCK -195 TABLEX$GRC_DHTRULE -194 TABLEX$GRC_PASTCOPY -193 TABLEX$GRC_RESOURCE -192 TABLEX$RESOURCE_REQUEST -191 TABLEX$DATA_CONNECTION -203 TABLEX$COL_CODEC_DICT_VALUES -204 TABLEX$2PC_PENDING -205 TABLEX$NODE -206 TABLEX$ELECTION -207 TABLEX$CM_NODE_INFO -208 TABLEX$CM_GROUP_INFO -209 TABLEX$CM_CLUSTER_INFO -210 TABLEX$ALLOCATOR -211 TABLEX$PUB_STAT -212 TABLEX$LSC_SLICE_STAT -213 TABLEX$TASK -214 TABLEX$OSSTAT -215 TABLEX$PLANCACHE -216 TABLEX$PARAMETER -217 TABLEX$VISIBLE_PARAMETER -218 TABLEX$SYSSTAT -219 TABLEX$MYSTAT -220 TABLEX$SESSTAT -221 TABLEX$STATNAME -222 TABLEX$AUDITABLE_SYSTEM_ACTIONS -223 TABLEX$AUDITABLE_OBJECT_ACTIONS -224 TABLEX$OPEN_CURSOR -226 TABLEX$PX_WORKER -227 TABLEX$PX_SESSION -228 TABLEX$PQ_TQSTAT -229 TABLEX$SESSION_WORKER -230 TABLEX$PROCESS -231 TABLEX$COM_WORKER -225 TABLEX$WINDOW_FUNCTION -232 TABLEX$SQL_TRACE -233 TABLEX$SQL_PLAN_TRACE -234 TABLEX$SQL_PLAN_STATISTICS -235 TABLEX$SQL_PLAN -236 TABLEX$DATATYPE -238 TABLEX$DML_STATS -239 TABLEX$ERROR_CODE -240 TABLEX$FUNCTION -241 TABLEX$GLOBAL_MPOOL -242 TABLEX$RESERVED_WORDS -243 TABLEX$DYNAMIC_VIEWS -244 TABLEX$VERSION -237 TABLEX$SQL -245 TABLEX$SQLAREA -246 TABLEX$SQLSTATS -247 TABLEX$VM -248 TABLEX$VMSTAT -249 TABLEX$CPUSTAT -250 TABLEX$DATAFILE -251 TABLEX$DATABUCKET -252 TABLEX$SESSION_ROLES -253 TABLEX$UNDOSTAT -254 TABLEX$DICT_CURSOR -255 TABLEX$ALERT_EVENT -256 TABLEX$DUAL -257 TABLEV$INSTANCE -2049 VIEWGV$INSTANCE -2050 VIEWV$FIXED_TABLE -2051 VIEWGV$FIXED_TABLE -2052 VIEWV$FIXED_VIEW_DEFINITION -2053 VIEWGV$FIXED_VIEW_DEFINITION -2054 VIEWV$SQL_PLAN_TRACE -2055 VIEWGV$SQL_PLAN_TRACE -2056 VIEWV$CM_TASK_INFO -2057 VIEWGV$CM_TASK_INFO -2058 VIEWV$DIN_STAT -2177 VIEWGV$DIN_STAT -2178 VIEWV$DIN_NODE -2179 VIEWGV$DIN_NODE -2180 VIEWV$DIN_LINK -2181 VIEWGV$DIN_LINK -2182 VIEWV$DATABASE -2059 VIEWV$LOGFILE -2060 VIEWV$ARCHIVED_LOG -2061 VIEWV$ARCHIVE_DEST -2062 VIEWGV$ARCHIVE_DEST -2063 VIEWV$ARCHIVE_DEST_STATUS -2064 VIEWGV$ARCHIVE_DEST_STATUS -2065 VIEWV$REPLICATION_STATUS -2066 VIEWV$ARCHIVE_GAP -2067 VIEWV$DBWR_STATISTICS -2068 VIEWGV$DBWR_STATISTICS -2069 VIEWV$TABLESPACE -2070 VIEWV$REPLICATION_EVENT -2071 VIEWGV$REPLICATION_EVENT -2072 VIEWV$REDOSTAT -2073 VIEWGV$REDOSTAT -2074 VIEWV$RECOVERY_PROGRESS -2075 VIEWGV$RECOVERY_PROGRESS -2076 VIEWV$BACKUP_PROGRESS -2077 VIEWGV$BACKUP_PROGRESS -2078 VIEWV$YFS_FILE -2079 VIEWV$YFS_FAILGROUP -2080 VIEWV$YFS_DISKGROUP -2081 VIEWV$YFS_DISK -2082 VIEWV$SPINLOCK -2083 VIEWGV$SPINLOCK -2084 VIEWV$SYSTEM_EVENT -2085 VIEWGV$SYSTEM_EVENT -2086 VIEWV$SYSTEM_WAIT_CLASS -2087 VIEWGV$SYSTEM_WAIT_CLASS -2088 VIEWV$TEMP_EXTENT_POOL -2089 VIEWGV$TEMP_EXTENT_POOL -2090 VIEWV$TRANSACTION -2091 VIEWGV$TRANSACTION -2092 VIEWV$UNDO_SEGMENTS -2093 VIEWGV$UNDO_SEGMENTS -2094 VIEWV$TEMPORARY_SEGMENT -2095 VIEWGV$TEMPORARY_SEGMENT -2096 VIEWV$TABLE_DICTIONARY -2097 VIEWGV$TABLE_DICTIONARY -2098 VIEWV$CONTROLFILE -2099 VIEWV$HM_RUN -2100 VIEWGV$HM_RUN -2101 VIEWV$HM_FINDING -2102 VIEWGV$HM_FINDING -2103 VIEWV$HM_CHECK_PARAM -2104 VIEWGV$HM_CHECK_PARAM -2105 VIEWV$HM_CHECK -2106 VIEWGV$HM_CHECK -2107 VIEWV$DIAG_PROBLEM -2108 VIEWGV$DIAG_PROBLEM -2109 VIEWV$DIAG_INCIDENT -2110 VIEWGV$DIAG_INCIDENT -2111 VIEWV$DIAG_FAULT -2112 VIEWGV$DIAG_FAULT -2113 VIEWV$SHARE_POOL -2114 VIEWGV$SHARE_POOL -2115 VIEWV$SGASTAT -2116 VIEWGV$SGASTAT -2117 VIEWV$SGA -2118 VIEWGV$SGA -2119 VIEWV$SESSION_WAIT -2120 VIEWGV$SESSION_WAIT -2121 VIEWV$SESSION -2122 VIEWGV$SESSION -2123 VIEW......V$UNDOSTAT -2279 VIEWGV$UNDOSTAT -2280 VIEWV$DICT_CURSOR -2281 VIEWGV$DICT_CURSOR -2282 VIEWV$ALERT_EVENT -2283 VIEWGV$ALERT_EVENT -2284 VIEWDUAL -2285 VIEW
从V$FIXED_TABLE 来看，还是不少，有367个，去掉其中gv$部分也还有180多个，还是不少了（x$FIXED_TABLE也是Oracle才有的东西). 从这里来看，崖山数据库确实下了不少功夫呀！进一步来看看参数情况：
SQL> select name,value from v$parameter;NAME VALUE---------------------------------------- ----------------------------------------------------------------MAX_SESSIONS 1024MAX_WORKERS 0MAX_PARALLEL_WORKERS 32MAX_REACTOR_CHANNELS 0WORK_AREA_STACK_SIZE 2MRUN_LOG_LEVEL INFORUN_LOG_FILE_COUNT 10RUN_LOG_FILE_SIZE 20MRUN_LOG_FILE_PATH /home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashantest/db-1-1/runUSE_LARGE_PAGES FALSECONTROL_FILES ('/home/yashan/yasdb_home/yashandb/data/yashantest/db-1-1/dbfiles/ctrl1','/home/yashan/yasdb_home/yashandb/data/yashantest/db-1-1/dbfiles/ctrl2','/home/yashan/yasdb_home/yashandb/data/yashantest/db-1-1/dbfiles/ctrl3')DB_BLOCK_SIZE 8192DATA_BUFFER_SIZE 8709MWORK_AREA_HEAP_SIZE 512KWORK_AREA_POOL_SIZE 64MHA_HEARTBEAT_INTERVAL 3HA_ELECTION_TIMEOUT 9HA_ELECTION_ENABLED FALSEHA_ELECTION_LEADER_LEASE_ENABLED FALSEREDO_BUFFER_SIZE 32MREDO_BUFFER_PARTS 4LARGE_POOL_SIZE 176MMAX_PRIVATE_TEMP_TABLES 16VM_BUFFER_SIZE 1111MDBWR_BUFFER_SIZE 4MUNDO_RETENTION 300UNDO_SHRINK_ENABLED TRUEUNDO_SHRINK_INTERVAL 3600TRANSACTION_LOCK_TIMEOUT 0DDL_LOCK_TIMEOUT 0STARTUP_ROLLBACK_PARALLELISM 2ISOLATION_LEVEL READ_COMMITTEDCOMMIT_WAIT WAITCOMMIT_LOGGING BATCHRECOVERY_PARALLELISM 8CHECKPOINT_INTERVAL 256MCHECKPOINT_TIMEOUT 60LISTEN_ADDR 172.20.22.146:1688REPLICATION_ADDR 172.20.22.146:1689CHARACTER_SET UTF8NATIONAL_CHARACTER_SET UTF16DBWR_COUNT 2DB_FILE_NAME_CONVERTREDO_FILE_NAME_CONVERTDB_BUCKET_NAME_CONVERTARCHIVE_LOCAL_DEST ?/archiveARCHIVE_DEST_1......ARCHIVE_DEST_32QUORUM_SYNC_STANDBYS MAJORITYREQUIRED_SYNC_STANDBYSDATAFILE_IO_MODE DEFAULTREDOFILE_IO_MODE DSYNCDOUBLE_WRITE_ENABLED TRUEDEFAULT_TABLE_TYPE HEAPMMS_USE_LARGE_PAGES FALSEMMS_DATA_LOADERS 8ARCH_CLEAN_UPPER_THRESHOLD 16GARCH_CLEAN_LOWER_THRESHOLD 12GARCH_CLEAN_IGNORE_MODE NONEDATE_FORMAT yyyy-mm-ddTIME_FORMAT hh24:mi:ss.ffTIMESTAMP_FORMAT yyyy-mm-dd hh24:mi:ss.ffYMINTERVAL_FORMAT yy-mmDSINTERVAL_FORMAT dd hh24:mi:ss.ffPASSWORD_FILE ?/instance/yasdb.pwdSQL_PLUGIN NONESCOL_DATA_BUFFER_SIZE 128MSCOL_CACHEABLE_SCAN_ROWS 18446744073709551615SCOL_DISK_CACHEABLE_SCAN_ROWS 18446744073709551615SCOL_DATA_PRELOADERS 2RECYCLEBIN_ENABLED OFFCOLUMNAR_VM_BUFFER_SIZE 128MCOLUMNAR_MATERIAL_PERCENT 80COLUMNAR_WORK_AREA_HEAP_SIZE 32MDB_BLOCK_CHECKSUM TYPICALBLOCK_REPAIR_ENABLED TRUEBLOCK_REPAIR_TIMEOUT 60COMPRESSION LZ4COMPRESSION_LEVEL LOWOPTIMIZER_DYNAMIC_SAMPLING 0LSC_DICTIONARY_CACHE_AUTOEXTEND FALSEBUCKET_RESERVED_SPACE 1GDIAGNOSTIC_DEST ?/diagDIAG_ADR_ENABLED TRUECOLUMNAR_BULK_SIZE 1024DEGREE_OF_PARALLEL 1QUERY_REWRITE_ENABLED FALSENODE_ID 1-1:1CM_ADDR 1-1:1/127.0.0.1:1679DIN_ADDR 127.0.0.1:1690DIN_RECONNECT_TIME 5000DEFAULT_MCOL_TTL 3600SCOL_WRITE_CACHE_POLICY WRITE_BACKDS_SCALE_OUT_FACTOR 7STATISTICS_LEVEL TYPICALUNIFIED_AUDITING FALSESHARE_POOL_SIZE 1111MCGROUP_FLAG 0CGROUP_ROOT_DIR /sys/fs/cgroupRESOURCE_MANAGER_PLANJOB_QUEUE_PROCESSES 16OPEN_CURSORS 310LSNR_LOG ONJVM_XMS 512MJVM_XMX 512MCLUSTER_DATABASE FALSEINSTANCE_NAME yasdbCLUSTER_INTERCONNECT 127.0.0.1:1700INTERCONNECT_LINKS 2INTERCONNECT_MESSAGE_POOL 1024:64;512:8800;128:16800;64:32800INTERCONNECT_RECEIVE_TIMEOUT 5GRC_TASK_COUNT 2GCS_TASK_COUNT 3GLS_TASK_COUNT 2CLUSTER_SERVICECLUSTER_RECONNECT_TIME 5000YASFS_DATA_DIR +DG0SESSION_MAX_OPEN_FILES 50AC_MAX_SOURCE_SLICE_COUNT 20AC_SLICE_THRESHOLD_SIZE 64MSSL_CERT_FILESSL_KEY_FILESSL_DH_PARAM_FILESSL_ENABLE OFFENABLE_SEPARATE_DUTY FALSEBLOOM_FILTER_FACTOR .3ENABLE_SLOW_LOG FALSESLOW_LOG_TIME_THRESHOLD 1000SLOW_LOG_FILE_NAME slow.logSLOW_LOG_FILE_PATH /home/yashan/yasdb_home/yashandb/23.1.1.100/log/yashantest/db-1-1/slowSLOW_LOG_OUTPUT FILESLOW_LOG_SQL_MAX_LEN 2000AUDIT_QUEUE_WRITE TRUEAUDIT_QUEUE_SIZE 16MAUDIT_FLUSH_INTERVAL 100TAB_QUEUE_WINDOW_SIZE 4DATA_RETENTION 3600DATA_TRANSFORMER_ENABLED TRUESCOL_SLICE_ROWS 8MCOLUMNAR_VM_SWAP_SIZE 200GBROADCAST_GTS_TIME 5HA_SSL_ENABLE OFFEXTSERVER_CONNS 64DBWR_FLUSH_NEIGHBORS_COUNT 16ENABLE_DISKCACHE FALSEENABLE_ARCH_DATA_IGNORE_BACKUP FALSESQL_MAP FALSE177 rows fetched.
说明崖山23.1版本有177个参数。那么是否也有类似Oracle 一样的隐含参数呢？ 这里我们继续查一下x$试图来看看，是否有所发现：
SQL> select name,value from X$PARAMETER order by 1;NAME VALUE---------------------------------------------- --------------------------------AUDIT_SYS_OPERATIONS FALSECOLUMNAR_BULK_COUNT 3500COLUMNAR_LOG_TRACE FALSECOLUMNAR_MATERIAL_TRACE FALSECOLUMNAR_MAX_JOIN_MEM 512MCOLUMNAR_MAX_OPERATOR_MEM_PERCENT 50COLUMNAR_MAX_SORT_MEM 512MCOLUMNAR_MAX_STAGE_MEM_PERCENT 10COLUMNAR_MIN_OPERATOR_MEM_PERCENT 1COLUMNAR_NON_COLLIDING_HASH TRUECONTROL_ADDR 127.0.0.1:1689COST_INDEX_FULL_SCAN_MIN_MAX -1.0CURRENT_SCHEMACURSOR_POOL_SIZE 32MDERM_ENABLED FALSEDERM_PARALLEL_EXECUTIONS 50DICTIONARY_CACHE_SIZE 25DIN_CONNECTIONS_PER_NODE 5DSTB_POOL_SIZE 0LOCK_POOL_SIZE 32MMAX_PARALLELISMS 0MAX_PARALLELISMS_PER_EXEC 0MAX_VM_OPEN 128MEX_POOL_SIZE 512MPACKET_SIZE 128KPQ_POOL_SIZE 128MRCY_DDL_TESTSQL_POOL_PARTS 1SQL_POOL_SIZE 50THREAD_STACK_SIZE 1024KTIMESTAMP_TZ_FORMAT yyyy-mm-dd hh24:mi:ss.ff AM TZH:TZM TZH:TZMUSE_STORED_OUTLINES FALSEVM_BUFFER_PARTS 1VM_BUFFER_SWAP_TIMES 1024_ARCHIVE_DELAY_TIME 0_ARCH_AUTO_CELAN_DATA_NUM 100_ARCH_DATA_THRESHOLD 1000000_BATCH_COMMIT_DELAY 0_BUCKET_VALUE_SCALE_IN_HASH_SET 1_BUCKET_VALUE_SCALE_IN_HASH_TABLE 1_BULKLOAD_WITH_SCOL_DATA_BUFFER ON_CHECK_PASSWORD_COMPLEXITY FALSE_CHN_MAX_SEGMENT_SIZE 64K_CLUSTER_ID 6553362c3d3cec527b2adf3484e0952c_CLUSTER_MESSAGE_LOG_ENABLED OFF_COAST_SIMD_MODE SIMD_MODE_512_COLUMNAR_DYNAMIC_ARRAY_THRESHOLD 4000000_COLUMNAR_ENABLE_HASH_GROUP_DISTINCT TRUE_COLUMNAR_MAX_BATCH_COUNT 1024_COLUMNAR_MAX_HASH_BUCKET 40000000_COLUMNAR_ROWGROUP_ROWS 4k_COL_MEM_POOL_EVICT_ENABLED TRUE_COL_RUNTIME_FILTER_USE_PRECISE_ROWS TRUE_CONSISTENT_WRITE FALSE_COST_AC_SCAN -1.0_COST_GROUP -1.0_COST_HASH_GROUP -1.0_COST_HASH_JOIN -1.0_COST_IDX_NL_JOIN -1.0_COST_INDEX_FAST_FULL_SCAN -1.0_COST_INDEX_FULL_SCAN -1.0_COST_INDEX_RANGE_SCAN -1.0_COST_INDEX_RANGE_SCAN_MIN_MAX -1.0_COST_INDEX_SCAN -1.0_COST_INDEX_SKIP_SCAN -1.0_COST_INDEX_UNIQUE_SCAN -1.0_COST_MS_JOIN -1.0_COST_MULTI_TO_MULTI -1.0_COST_MULTI_TO_MULTI_BCST -1.0_COST_MULTI_TO_SGL -1.0_COST_NL_JOIN -1.0_COST_ORDER_BY -1.0_COST_PART_AC_SCAN -1.0_COST_PART_ALL_AC_SCAN -1.0_COST_PART_ALL_SCAN -1.0_COST_PART_INDEX_SCAN -1.0_COST_PART_ITER_AC_SCAN -1.0_COST_PART_ITER_SCAN -1.0_COST_PART_MULTICOL_SCAN -1.0_COST_PART_SCAN -1.0_COST_PART_SINGLE_AC_SCAN -1.0_COST_PART_SINGLE_SCAN -1.0_COST_PX_QUEUE -1.0_COST_ROWID_SCAN -1.0_COST_RTREE_SCAN -1.0_COST_SDT -1.0_COST_SDT_GROUP -1.0_COST_SGL_TO_MULTI -1.0_COST_SGL_TO_MULTI_BCST -1.0_COST_SGL_TO_SGL -1.0_COST_SORT -1.0_COST_SORTED_WINFUNC -1.0_COST_SORT_AGGRDIST -1.0_COST_SORT_GROUP -1.0_COST_TABLE_FULL_SCAN -1.0_COST_UNIQUE -1.0_DATA_BUFFER_PARTS 1_DATA_TRANSFORMER_MAX_TASK_COUNT 200K_DATA_TRANSFORMER_SCHEDULING_TIME 300_DATA_TRANSFORMER_WORKERS 32_DBWR_SORT_ENABLED ON_DB_BLOCK_MAX_CR_DBA 5_ENABLE_ALTER_SLICE FALSE_ENABLE_CREATE_AC_BTREE FALSE_ENABLE_DISTRIBUTE_TRANSACTION FALSE_ENABLE_EXPLAIN_STATS TRUE_ENABLE_HEAP TRUE_ENABLE_INDEX TRUE_ENABLE_LSC TRUE_ENABLE_OLD_DECIMAL FALSE_ENABLE_REDO_TRAFFIC_CONTROL FALSE_ENABLE_S3 FALSE_ENABLE_TAC TRUE_FAULT_POINT_GCS_REQ_CLEAN_INTERVAL 30_HA_ELECTION_VOTE_FOR_PRE_CANDIDATE_INTERVAL 50_HOT_BUFFER_PCT 60_HOT_CACHE_SIZE 16M_LATEST_RESET_ID -1_MCOL_CACHE_REFRESH_SIZE 100_MCOL_SLICE_ROWS 2M_MIGRATION_MODE_ENABLED OFF_OM_ELECTION_ENABLE FALSE_OPTIMIZER_MODE CBO_OPTIMIZE_EXTEND_FILTER TRUE_OPTMZ_EARLY_GROUP_OPT ON_OPTMZ_ENABLE_DSTB_AC TRUE_OPTMZ_ENABLE_DUP_TABLE_PARALLEL TRUE_OPTMZ_EXEC_ENGINE UNSPECIFIED_OPTMZ_MINMAX_OPT SINGLE_PROMOTE_NUMBER FALSE_PX_SENDER_PROJECT FALSE_RCY_BPWR_COUNT 0_REDO_FLUSH_TRIGGERED_DELAY 1_REMOTE_CR_THRESHOLD 4_REPLICATION_BUFFER_SIZE 16M_RWRT_OPT 255_SCOL_EXTENT_SIZE 1M_SCOL_FILTER_MODE FILTER_ROW_SCOL_MANAGER_CAPACITY 32767_SCOL_SLICE_LAYOUT SILO_SESSION_RESERVED_CURSORS 8_SESSION_RESERVED_LOCKS 16_SESSION_TEMP_TABLE_CACHE_SIZE 1K_SLOW_LOG_EPOCH_SIZE 64_SLOW_LOG_QUEUE_SIZE 256_SPAREINDEX_OPT ON_SPLIT_BRAIN_DATA_LIMIT 4G_SQL_MAP FALSE_STANDBY_MAX_FAILURE_TIMEOUT 300_TAC_FILTER_PUSH_THRESHOLD 5_TEMP_BUFFER_PCT 20_TWO_PHASE_FETCH_THRESHOLD 75_UNDO_FORCE_RETENTION FALSE_UNDO_MAX_AUTOEXTEND_SEGMENTS 64_UNDO_SEG_BUSY_TIMES 2_UNDO_SEG_EXTEND_INTERVAL 600_USE_OPTMZ_INFO FALSE_WAIT_BOC TRUE_XACT_UNDO_TIMES 20
虽然参数并不太多；但是还是有不少隐含参数，应该也是类似Oracle，通过隐含参数来启用或关闭一些特殊的功能，默认值如果是false的，通常说明是不稳定的功能。从上面的隐含参数，我们可以发现不少有意思的地方：
SQL> select * from x$parameter where name like '%INDEX%';NAME VALUE DEFAULT_VALUE---------------------------------- ------------------ -------------------_ENABLE_INDEX TRUE TRUE_SPAREINDEX_OPT ON ON_COST_INDEX_UNIQUE_SCAN -1.0 -1_COST_INDEX_FULL_SCAN -1.0 -1_COST_INDEX_RANGE_SCAN -1.0 -1_COST_INDEX_RANGE_SCAN_MIN_MAX -1.0 -1COST_INDEX_FULL_SCAN_MIN_MAX -1.0 -1_COST_INDEX_FAST_FULL_SCAN -1.0 -1_COST_INDEX_SKIP_SCAN -1.0 -1_COST_INDEX_SCAN -1.0 -1_COST_PART_INDEX_SCAN -1.0 -1
我们可以看到崖山数据库居然支持Index fast full scan；目前opengauss和达梦好像都还在不支持，这里简单验证一下。
SQL> create table t17 as select * from sys.dba_objects;Succeed.SQL> insert into t17 select * from t17;1357 rows affected.......SQL> insert into t17 select * from t17;694784 rows affected.SQL> commit;Succeed.SQL> create index idx_t17_owner on t17(owner) parallel 4;Succeed.SQL> alter index idx_t17_owner noparallel;Succeed.SQL> alter index idx_t17_owner rebuild online;Succeed.SQL>SQL> BEGIN2 DBMS_STATS.GATHER_TABLE_STATS('ENMO', 'T17', 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);3 END;/ 4YAS-04253 PL/SQL compiling errors:[2:5] YAS-04401 data type STRING expected, but INTEGER gotSQL> BEGIN2 DBMS_STATS.GATHER_TABLE_STATS('ENMO', 'T17', '', 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);3 END;/ 4PL/SQL Succeed.SQL>
从上面测试可以看到，统计信息收集时遇到了一个小Bug，居然不支持单表，支持分区表，对于非分区表也必须指定‘’选项才行。不得不说,跟Oracle非常像，连dbms_stats都有了，看官方文档的用法也差不多。
SQL> set autot onSQL> select owner,count(1) from t17 group by owner order by 1;OWNER COUNT(1)---------------------------------------------- ----------ENMO 1024MDSYS 4096PUBLIC 549888SYS 834560Execution Plan----------------------------------------------------------------SQL hash value: 2300714586Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| 0 | SELECT STATEMENT | | | | | | | | | | || 1 | SORT GROUP | | | 4| | 455( 0)| | | | | || 2 | INDEX FAST FULL SCAN | IDX_T17_OWNER | ENMO | 1389568| | 385( 0)| | | | | |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+Operation Information (identified by operation id):---------------------------------------------------1 - Group Expression: ("T17"."OWNER")Statistics----------------------------------------------------------------------------------------------------19 rows fetched.SQL>SQL> select /*+NO_INDEX_FFS(a IDX_T17_OWNER) */ owner,count(1) from t17 a group by owner order by 2;OWNER COUNT(1)-------------------------------------- ---------------------ENMO 1024MDSYS 4096PUBLIC 549888SYS 834560Execution Plan----------------------------------------------------------------SQL hash value: 3609744365Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| 0 | SELECT STATEMENT | | | | | | | | | | || 1 | SORT | | | 4| | 484( 0)| | | | | || 2 | SORT GROUP | | | 4| | 472( 0)| | | | | || 3 | INDEX FULL SCAN | IDX_T17_OWNER | ENMO | 1389568| | 402( 0)| | | | | |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+Operation Information (identified by operation id):---------------------------------------------------2 - Group Expression: ("A"."OWNER")Statistics----------------------------------------------------------------------------------------------------20 rows fetched.
我们可以看到，确实支持Index fast full scan及index full scan；相关hint用法也非常类似。不过从这里来看，执行计划信息显示不完整，statistics部分无法展示，不知道算不算bug。
从前面参数来看，崖山也支持hash join；所以我想崖山的hash join性能跟oracle比如何呢？最后测一下并进行下对比。
#yashanDB
SQL> set autot onSQL> set autot offSQL> select count(1) from t17;COUNT(1)---------------------13895681 row fetched.Elapsed: 00:00:00.123SQL> select count(1) from t17_1;COUNT(1)---------------------4999991 row fetched.Elapsed: 00:00:00.048SQL> set autot onSQL> select b.owner, count(*)2 from t17 a, t17_1 b3 where a.object_id = b.object_id4 group by b.owner;OWNER COUNT(*)------------------------------------ ---------------------ENMO 1048576MDSYS 4194304PUBLIC 506756096Execution Plan----------------------------------------------------------------SQL hash value: 1577418467Optimizer: ADOPT_C+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+| 0 | SELECT STATEMENT | | | | | | | | | | || 1 | SORT GROUP | | | 3| | 27293( 0)| | | | | ||* 2 | HASH JOIN INNER | | | 511998112| | 7867( 0)| | | | | || 3 | TABLE ACCESS FULL | T17_1 | ENMO | 499999| | 568( 0)| | | | | || 4 | TABLE ACCESS FULL | T17 | ENMO | 1389568| | 1308( 0)| | | | | |+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+Operation Information (identified by operation id):---------------------------------------------------1 - Group Expression: ("B"."OWNER")2 - Predicate : access("B"."OBJECT_ID" = "A"."OBJECT_ID")Statistics----------------------------------------------------------------------------------------------------21 rows fetched.Elapsed: 00:03:22.044SQL>
两个测试表数据分别为138w、49w，执行时间大概是3分22秒，也就是200秒左右。
#Oracle 11204
SQL> create table t17 as select * from sys.dba_objects;Table created......SQL> select count(1) from t17;COUNT(1)----------1388000SQL> create table t17_1 as select * from t17 where rownum < 500000;Table created.SQL> analyze table t17 compute statistics;Table analyzed.SQL> c/t17/t17_11* analyze table t17_1 compute statisticsSQL> /Table analyzed.SQL> select * from v$version where rownum < 2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionElapsed: 00:00:00.00SQL> set autot onSQL> set lines 200SQL> select b.owner, count(*)2 from t17 a, t17_1 bwhere a.object_id = b.object_id3 4 group by b.owner;OWNER COUNT(*)------------------------------ ----------OWBSYS_AUDIT 960MDSYS 160720ZB_TEST 80ROGER 2560PUBLIC 3184752......SYS 3629216WMSYS 31968SI_INFORMTN_SCHEMA 640TEST1 8029 rows selected.Elapsed: 00:00:01.72Execution Plan----------------------------------------------------------Plan hash value: 3832458698-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 29 | 377 | | 9101 (3)| 00:01:50 || 1 | HASH GROUP BY | | 29 | 377 | | 9101 (3)| 00:01:50 ||* 2 | HASH JOIN | | 7999K| 99M| 10M| 8876 (1)| 00:01:47 || 3 | TABLE ACCESS FULL| T17_1 | 499K| 4394K| | 1939 (1)| 00:00:24 || 4 | TABLE ACCESS FULL| T17 | 1388K| 5421K| | 5363 (1)| 00:01:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")Statistics----------------------------------------------------------0 recursive calls0 db block gets26909 consistent gets0 physical reads0 redo size1264 bytes sent via SQL*Net to client535 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)29 rows processed
Oracle 居然在1.7s跑出结果了，有点震惊。这样粗略算一下相差117倍 ？？？
当然写这篇文章没有其他的含义，不是为了贬低，希望后续能不断改进。
最后简单总结一下：
1、大量兼容Oracle的x$、v$、dba_xxx等试图，兼容性较高。
2、支持索引并行创建，online rebuild、index fast full scan等。
3、统计信息收集也非常符合oracle dba的使用习惯，因为几乎一致。
4、从测试hash join来看，似乎性能不太理想，看来基础算子的性能提升还是任重道远。
最后修改时间：2023-11-23 15:15:55
文章转载自Roger的数据库专栏，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
