Monthly Archives: 12月 2019

应用报错“Io 异常: Connection reset”异常案例

2019 年 12 月 28 日 – 12:49

前几日，一套核心业务数据库待投产环境进行投产前测试时应用出现大量“Io 异常: Connection reset”报错，应用无法连接数据库。要来数据库相关信息后，登陆数据库检查状态发现数据库正处于大量’Library Cache Lock与Library Cache Mutex :X等待中，如下图ASH所示：

大量ddl_lock type为79，已经知晓了这种现象的根因，随手做了一个数据库systemstate dump进行验证：

oradebug setmypid
oradebug dump systemstate 266;
oradebug tracefile_name;

探寻 dump Trace文件，我们看到会话正在等待Library Cache Lock,做system dump 的意义在于，要找到持有Library Cache Handle Lock的会话为什么会持有锁，为什么不及时释放锁从而导致后续活动会话get不到Lock资源从而等待，

dump trace文件中已经明确显示了该handle address,继续搜寻后发现证实了我的猜测，

我们看到，该Hanlde Lock既有RequestMode=X，同时又存在LockMode=X ，不兼容从而发生的阻塞等待是正常现象，但是发现了该Library Cache Lock 的Namespace=ACCOUNT_STATUS(79)，证实了我的猜测，该阻塞等待是因为数据库账户密码错误导致触发Oracle 延迟登录特性，从而产生大量Library Cache Lock。但是为何Io 异常: Connection reset呢？猜测是因为数据库活动会话大量积压，应用服务器连接数被撑满。

既然找到并且证实了根因，针对此问题下一步该如何操作？针对数据库账户密码错误，我们可以执行：

alter system set events '1017 trace name errorstack forever,level 3';

该errorstack会生成trace文件，在trace文件中确认，waitint for library cahce lock中的machine，也就是密码错误触发延迟登录长时间持有Library Cache Lock的应用服务器。

设置完成后，只要利用错误密码登录数据库抛出ORA-01017,则会在数据库Alert中产生一个Trace文件.

随后将报错应用服务器发送至应用方，检查配置的数据库账户密码是否正确。

最后，为消除之后隐患，在数据库中执行

ALTER SYSTEM SET EVENTS '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' ：

从而使得，今后会话在因密码登录失败后，直接返回报错，不会长时间持有LIBRARY CACHE LOCK 。

已关闭评论

Oracle 12c中分页查询增强-TOP N SQL

2019 年 12 月 24 日 – 09:01

继之前文章中提到的分页查询SQL优化思路提到的分页框架：

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+ index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

我们前文中提到过，需要在排序列中创建索引，借助索引有序特性来避免排序并且利用正确的分页框架实现COUNT STOPKEY特性，在Oracle 12c版本之后，Oracle提供了新的分页TOP N功能，来实现上述分页需求并且极大的减少了代码复杂程度。

参考Oracle 官方文档中的的描述：

我们可以利用FETCH ROWS ONLY来实现文章分页TOP-N需求代码：

已关闭评论

alter table drop column（default value not null ）导致ORA-12899

2019 年 12 月 15 日 – 19:17

近日，同事的测试库中出现一则奇怪的现象邀请我共同探讨，一张数据表在删除一个含有DEFAULT值的非空列时报错，Error Code为ORA-12899，报错详情如下：

12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
// which is too wide for the width of the destination column.
// The name of the column is given, along with the actual width
// of the value, and the maximum allowed width of the column.
// Note that widths are reported in characters if character length
// semantics are in effect for the column, otherwise widths are
// reported in bytes.
// *Action: Examine the SQL statement for correctness. Check source
// and destination column data types.
// Either make the destination column wider, or use a subset
// of the source column (i.e. use substring).

专业的同事，在同样觉得蹊跷的之后，再一次执行此语句并且抓取了相关10046 Trace文件，分析Trace文件后，发现有一条递归update语句导致此错误，导致drop column失败，进入死循环。

已关闭评论

针对SCHEMA Rename需求

2019 年 12 月 11 日 – 09:28

近日，某测试数据库用户存在更换schema名称的需求，同事准备将数据进行导入导出的同时进行remap schema的工作，其实，从ora11g（11.2.0.2）版本开始，Oracle提供在不动数据的情况下，将schema rename的功能，但是代价是需要将数据库启动restrict模式下，实验如下：

rename之前需要将_enable_rename_user隐含参数改为TRUE（默认为FALSE），此参数意义为enable RENAME-clause using ALTER USER statement。

将数据库启动到restrict模式后，可以利用alter user rename命令进行更改schema name，本案例中为schema独占测试数据库，若生产库将数据库中一般存在多个schema共享一套数据库，启动到 restrict模式会造成应用连接无法连接数据库的情况发生，因本案例为测试系统，生产系统慎用此方式。

已关闭评论

Oracle 12c column add default value功能增强

2019 年 12 月 09 日 – 10:52

前几日晚，某核心应用负责人准备进行上线操作前打来一个确认电话，咨询了一个Oracle 11g 表添加default值的问题，询问是否有何影响，听完他的描述后，简单跟他描述完原理后，建议他取消此上线操作，否则会造成核心应用严重的性能问题，但是此问题再Oracle 12c新特性中已得到很大改进，对性能的影响已经降到最低。

11g情况下，可以看到，增加一个带有default值的并且没有添加非空约束的列，需要分钟级别（与表数据量有直接关系），完成时间会随着数据量的增加而耗时变长。

会话2，我们在执行alter table时会造成读写阻塞，等待library cache lock

SID=916会话被阻塞

SID=346会话被阻塞：

所以在此期间内产生的应用事务，查询均会被阻塞，如果TABLE量级比较大，会造成更严重的性能问题，因为11g在添加DEFAULT值且没有非空约束时，在更新数据字典的同时仍要更新表中全部已有记录值，我们从segment大小与10046 event trace文件中可以得到论证。

已关闭评论

Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

2019 年 12 月 05 日 – 09:33

今天，公司同事问了我一个问题，Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性，是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状？

答案是否定的，我们先看一下如下测试实验：

我们在12c环境下执行如下SQL：

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制（12C 默认为TRUE），我们分别将该参数改为FLASE，与TRUE：

（1）关闭TABLE ACCESS BY INDEX ROWID BATCHED特性，同ora11g版本：

已关闭评论

应用端频繁报出java.sql.SQLException: pingDatabase failed, status=-1案例

2019 年 12 月 04 日 – 09:39

近日，某航司B2C网站应用人员发来求助排查邮件，告知JBOSS连接数据库经常报出java.sql.SQLException: pingDatabase failed, status=-1的错误,并且经过确认，数据库正处于正常状态下，应用人员提供了相对具体报错时间点，希望尽快帮忙排查一下数据库端有无异常。