首页 » ORACLE 9i-23c, 老哥杂谈 » 2024年了，某客户核心还在用Oracle 9i

2024年了，某客户核心还在用Oracle 9i

上周有个客户咨询Oracle数据库历史时间SQL性能变慢的问题, 还是个偏重要的核心业务，当然最终确认因为人员的一些变更操作非专业性，导致JOB未运行，产生的数据累积。思考一个问题，Oracle 9i没有AWR, 没有ASH, 甚至当时国内也是没有太多的技术团队储备，是不是像极了现在的国产库现状？我去查了一下《Oracle Database History》9i似乎是2001年 release的（20多年前）。当然现在信息的开放和知识积累，及国内最不缺的“优化”和仿制小能手，可以直接“借轮攒车”，但核心技术并不是一蹴而就的，Oracle 好多功能创新都是基于多版本的迭代如AI index, Auto SPM。另外我希望多听到”AWR”这类技术类创新，少些“可观测性”这类高大上的概念名次。 目前像达梦、GaussDB、翰高、人大金仓也有类似的报告，也有一些DB借助数据库外工具，但数据库指标度量实现的位置是内核层还是SQL层, 希望不要成为DB的top 负载。

Oracle新版本发现一些易用性功能技术创新，如create table xx for exchange with

create table t(c1 int,c2 int invisible)
 partition by range(c1)
(
   partition p0 values less than (11)，
   partition p1 values less than (21)
);

create table ext1 as select * from t where 1=0;

alter table t excange partition p0 with table ext1;
-- error
ORA-14097: COLUMN type or size mismatch in ALTER TABLE EXCHANGE PARTITION;

create table ext2 as for exchange with table t;

alter table t excange partition p0 with table ext2;
-- will work

及ORACLE TRUE CACHE

当然有些人会说我不需要什么新特性，只是把数据库当数据存储如excel 使用， OK, You win, but not forever！

这里附一段这套库DB alert日志。

Sat Feb  2 01:29:50 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.7.0.
System parameters with non-default values:
  processes                = 1200
  timed_statistics         = TRUE
  shared_pool_size         = 318767104
  large_pool_size          = 318767104
  java_pool_size           = 33554432
...
... truncated
...

Sat Jan 27 03:22:16 2024
Completed: ALTER DATABASE CLOSE NORMAL
Sat Jan 27 03:22:16 2024
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Jan 27 03:22:51 2024
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.7.0.
System parameters with non-default values:
  processes                = 1200
  timed_statistics         = TRUE
  shared_pool_size         = 318767104
  large_pool_size          = 318767104
  java_pool_size           = 33554432
  control_files            = /oradata1/anbob/control01.ctl, /oradata2/anbob/control02.ctl, /oradata3/anbob/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 21474836480
  max_commit_propagation_delay= 10000
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  log_archive_dest_1       = LOCATION=/archive
  log_archive_format       = anbob_%s.arc
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS2
  undo_retention           = 3600
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  instance_name            = anbob
  dispatchers              = (PROTOCOL=TCP) (SERVICE=anbobXDB)
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /oracle/admin/anbob/bdump
  user_dump_dest           = /oracle/admin/anbob/udump
  core_dump_dest           = /oracle/admin/anbob/cdump
  sort_area_size           = 524288
  db_name                  = anbob
  open_cursors             = 1300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 4294967296
PMON started with pid=2
DBW0 started with pid=3
DBW1 started with pid=4
DBW2 started with pid=5
DBW3 started with pid=6
LGWR started with pid=7
CKPT started with pid=8
SMON started with pid=9
RECO started with pid=10
CJQ0 started with pid=11
Sat Jan 27 03:22:53 2024
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=14
ARC0: Archival started
ARC1 started with pid=15
ARC1: Archival started
Sat Jan 27 03:22:53 2024
ARCH: STARTING ARCH PROCESSES COMPLETE
Sat Jan 27 03:22:53 2024
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no FAL' ARCHARC1: Thread not mounted
Sat Jan 27 03:22:53 2024
ARC0: Becoming the heartbeat ARCH
ARC0: Becoming the heartbeat ARCHARC0: Thread not mounted
Sat Jan 27 03:22:53 2024
ALTER DATABASE   MOUNT
Sat Jan 27 03:23:00 2024
Successful mount of redo thread 1, with mount id 1962011024
Sat Jan 27 03:23:00 2024
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Sat Jan 27 03:23:00 2024
ALTER DATABASE OPEN
Sat Jan 27 03:23:00 2024
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 124471
  Current log# 2 seq# 124471 mem# 0: /oradata1/anbob/redo21.log
  Current log# 2 seq# 124471 mem# 1: /oradata2/anbob/redo22.log
Successful open of redo thread 1
Sat Jan 27 03:23:00 2024
SMON: enabling cache recovery
Sat Jan 27 03:23:00 2024
Successfully onlined Undo Tablespace 13.
Sat Jan 27 03:23:00 2024
SMON: enabling tx recovery
Sat Jan 27 03:23:00 2024
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

Note:
这个库有配置1200最大进程数，但shared pool和large pool 给300M, db cache给20G, PGA给4G, 9i还没有AMM, ASMM自动内存管理,所以这内存配置不是很合理，日志最老是2013年，已持续运行了10几年，搜了一下没出现过ora-4031，看来SQL Hard Parse也并不大。有一些KGH层的ora-600错误

	行 143264: ORA-00600: �ڲ�������룬����: [kghbigasp:ds], [0x110321408], [], [], [], [], [], []
	行 143265: ORA-00600: �ڲ�������룬����: [kghasp1], [0x110321448], [], [], [], [], [], []
	行 143266: ORA-00600: �ڲ�������룬����: [99999], [0x110321418], [], [], [], [], [], []
	行 171325: ORA-000060: Deadlock detected. More info in file /oracle/admin/anbob/udump/anbob_ora_1503370.trc.
	行 191814: ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
	行 245365: ORA-000060: Deadlock detected. More info in file /oracle/admin/anbob/udump/anbob_ora_1012148.trc.
	行 252750: ORA-00600: �ڲ�������룬����: [kghfrf:nxt], [0x1103227E8], [], [], [], [], [], []

相信国产集中数据库做的好，还是有很大的市场可替代的， 如这套环境现在就有更多的选择，还有很多开始就未考虑oracle 费用，而大才小用选用oracle的中小客户。国产库别浮躁，踏实搞核心技术，要么距离只会更远。这个技术阶段的数据库还是需要大量的人力去补坑。如果明天oracle 9i 宣布免费开放使用或出售给国有公司，是否会影响你数据库迁移的决心？~_~!

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：