问题:

        数据库性能缓慢，大量的cursor:pin s wait x ，library cache lock以及log file switch(checkpoint incomplete),db file async I/Osubmit等待。

原因:

•  cursor:pin s wait x与library cache lock发生的原因为表S_TRANSPORT_XXXX发生了TRUNCATE DDL操作，导致涉及该表的SQL游标被打破需要重新解析，并且由于SQL语句为长SQL，没有使用绑定变量，导致短时间内出现大量的sql解析争用。

•     log file switch(checkpoint incomplete),db file async I/Osubmit等待原因为存在IO高消耗的全表扫描SQL加上磁盘存储性能较差，致使检查点写数据过慢，日志切换出现等待。

问题分析:

    问题时间段，数据库出现大量的cursor:pin s wait X ，library cache lock以及log file switch(checkpoint incomplete),db file async I/Osubmit等待导致数据库性能缓慢。

    cursor:pin s wait x与library cache lock通常一起伴随发生于sql解析争用堵塞，接下来主要分析sql解析争用堵塞的原因。

    在2022年7月1号早上9点04分开始，library cache lock以及cursor:pin s wait x开始出现增长。

        期间出现大量sql解析的相互争用以及堵塞。

        查看主要的堵塞会话为sid:400,31041，执行sql为9uznnpcayzxqu。

        执行8uznnpcayzxqu对应操作为truncate table S_TRANSPORT_XXXX。

        查看主要的争用sql_id

        sql的格式文本都是相类似的语句(with t0 as)，没有使用绑定变量的长文本SQL，sq文本平均

长度为7260。

        语句里面涉及的基础表S_TRANSPORT_XXXX在2022年7月1日发生过DDL操作，与之前查询的truncate 操作匹配。

        到这里，可以确认cursor:pin s wait x与library cache lock发生的原因为，表S_TRANSPORT_XXXX发生了TRUNCATE DDL操作，导致涉及该表的SQL游标被打破需要重新解析，并且由于SQL语句为长SQL，没有使用绑定变量，导致短时间内出现大量的sql解析争用。

    接下来分析log file switch(checkpoint incomplete),db file async I/Osubmit等待，可以发现后台alert日志存在checkpoint incomplete导致的日志组切换等待问题。

        查看日志组的配置可以发现当前日志组配置为6*1G，当前并没有大量的DML操作，当前日志配置完全可以满足，不存在日志过小不合理的问题。

        查看IO性能，可以发现磁盘IO等待严重，IO队列使用不超过20%，写性能不超过10MB/S，IOPS不超过200，但写等待严重，当前磁盘存储性能较差。

        查看数据库后台慢SQL，存在千万级别的大表全表扫描。

        查看where条件，其中orderid的可选择率极高98.65%，但索引S_PASS_XXXX_OE未被使用。

        查询索引未被使用的原因为索引状态失效unusable，需要对索引进行重建，由于索引是全局索引，在进行分区进行维护时，也需要同时对全局索引进行维护。

        现在，我们可以确认log file switch(checkpoint incomplete),db file async I/Osubmit等待原因为，存在全表扫描SQL导致IO高消耗加上磁盘存储性能较差，导致检查点写数据过慢。

问题总结:

    数据库性能缓慢的原因为数据库出现大量的cursor:pin s wait x ，library cache lock以及log file switch(checkpoint incomplete),db file async I/Osubmit等待导致数据库性能缓慢。

    cursor:pin s wait x与library cache lock发生的原因为表S_TRANSPORT_XXXX发生了TRUNCATE DDL操作，导致涉及该表的SQL游标被打破需要重新解析，并且由于SQL语句为长SQL，没有使用绑定变量，导致短时间内出现大量的sql解析争用。

    log file switch(checkpoint incomplete),db file async I/Osubmit等待原因为存在IO高消耗的全表扫描SQL以及磁盘存储性能较差，致使检查点写数据过慢，日志切换出现等待。

问题解决建议：

• 对于DDL操作，建议在业务空闲时间操作，避免产生游标重新解析产生争用。

• 对产生解析争用的sql进行绑定变量改造，并从应用逻辑程序层面拆分SQL，避免长SQL产生。

• 对失效索引S_PASS_XXXX_OE进行重建。