1、Oracle 19c 主要的新特性
- Data Guard 备库DML自动重定向
- Varchar2 可以支持最大 32767 字节大小
- Oracle的混合分区表支持
- 多实例并行重做日志应用增强
- Multitenant Environment 多租户系统
- In-Memory Option 内存列式存储
- 自动创建索引(仅一体机有此功能)
- 实时统计信息收集(仅一体机有此功能)
- SQL隔离(仅一体机有此功能)
更多新特性请查看:
https://apex.oracle.com/database-features/
2、 PLSQL、函数、存储过程问题
可能有一些 SQL 在原有 11g 环境运行正常,但在 19c 环境下会出错,
此类问题则为 PLSQL、函数、存储过程等的语法问题,需要开发人员重构代码解决。例如:LISTAGG 聚合函数现在支持通过使用新的 DISTINCT 关键字来消除重复项。LISTAGG 聚合函数根据 ORDER BY 表达式对查询中每个组的行进行排序,然后将值连接成单个字符串。在串联成单个字符串之前,可以使用 new DISTINCT 关键字从指定的表达式中删除重复值。这样就无需在使用聚合 LISTAGG 函数之前创建复杂的查询处理来查找不同的值。使用“DISTINCT”选项删除 LISTAGG 函数中的重复值。
另外,如果在 11g 中使用了 wm_concat 函数,19c 中已经没有这个函数了,可以使用listagg替换,开发人员需要修改 SQL 语法或者新建这个函数也可以。
vm_concat(v_colname) ----> listagg(v_colname,',')
5、开发需要关注自建的存储过程、函数的兼容问题
统计用户对象的个数和类型
对象总数
select d.owner,count(1) from dba_objects d
where d.owner in ('PROD','PROD_CC','PROD_OP','PROD_CB','PROD_OS')
and d.owner not in ('PUBLIC')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.object_name
and d.owner = b.owner)
group by d.owner
order by count(1) desc;
查找使用自建函数的 SQL
select distinct sql_id, sql_text, module
from V$SQL,
(select object_name
from DBA_OBJECTS O
where owner = 'PROD'
and object_type in ('FUNCTION', 'PACKAGE'))
where (instr(upper(sql_text), object_name) > 0)
and plsql_exec_time > 0
and regexp_like(upper(sql_fulltext), '^[SELECT]')
and parsing_schema_name = 'PROD';
对象类型汇总
select d.owner,d.object_type,count(1) from dba_objects d where d.owner in ('PROD','PROD_CC','PROD_OP','PROD_CB','PROD_OS') and d.owner not in ('PUBLIC') and not exists (select 1 from dba_recyclebin b where b.object_name=d.object_name and d.owner = b.owner) group by d.owner,d.object_type order by count(1) desc;
OWNER OBJECT_TYPE COUNT(1)
------------------------------ ------------------- ----------
PROD INDEX 7352
PROD_CC INDEX 7125
PROD_OP INDEX 4566
PROD SEQUENCE 1151
PROD TABLE 1144
PROD_CC SEQUENCE 1115
PROD_CC TABLE 1106
PROD_OP SEQUENCE 676
PROD_OP TABLE 668
PROD LOB 126
PROD_CC LOB 118
PROD_OP LOB 55
PROD FUNCTION 18
PROD_CC FUNCTION 17
PROD_CB INDEX 15
PROD PROCEDURE 3
PROD_CB TABLE 3
PROD_CC PROCEDURE 2
PROD_CB SEQUENCE 2
PROD TRIGGER 1
PROD TYPE 1
PROD_OP FUNCTION 1
22 rows selected.
检查业务用户自建对象
select OWNER,OBJECT_TYPE,OBJECT_NAME from dba_objects d
where d.owner in ('PROD','PROD_CC','PROD_OP','PROD_CB','PROD_OS')
and OBJECT_TYPE not in ('INDEX','SEQUENCE','LOB','TABLE')
order by 2,1;
检查无效索引
select owner,index_name,status from dba_indexes
where status='UNUSABLE' order by 1,2;
select i.owner,i.index_name,p.partition_name,p.status
from dba_ind_partitions p,dba_indexes i
where p.index_name=i.index_name and p.status='UNUSABLE' order by 1,2,3;
select i.owner,i.index_name,s.subpartition_name,s.status
from dba_ind_subpartitions s,dba_indexes i
where s.index_name=i.index_name and s.status='UNUSABLE'
order by 1,2,3;
确认系统用户是否包含业务对象
--检查SYS和SYSTEM的重复对象,返回如下行则正常。
set line 345
col OBJECT_NAME for a40
select owner,object_name,object_type from dba_objects
where (object_name,object_type) in
(select object_name,object_type from dba_objects where owner='SYS')
and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ---------------------------------------- -------------------
SYSTEM AQ$_SCHEDULES TABLE
SYSTEM AQ$_SCHEDULES_PRIMARY INDEX
SYSTEM DBMS_REPCAT_AUTH PACKAGE BODY
SYSTEM DBMS_REPCAT_AUTH PACKAGE
select owner,segment_name,segment_type,tablespace_name
from dba_segments
where tablespace_name in('SYSTEM','SYSAUX')
and owner in ('PROD','PROD_CC','PROD_OP','PROD_CB','PROD_OS');
6、SQL 执行计划变差问题
升级后可能会有些 SQL 语句性能变差,这块需要 DBA 介入,重新收集统计信息、固定执行计划或者使用 SPA(SQL 性能分析SQLPerformance Analyzer) 等技术检查,官方文档:Oracle Database Testing Guide 19c
https://docs.oracle.com/en/database/oracle/oracle-database/19/ratug/index.html