序列Sequence等待及SQL全表扫的分析调优。

创建sequence是开发的任务，正如创建索引一样，但是每个操作都需要考虑因素，否则很容易引起性能问题，比如sequence争用以及索引不合理，这是一次真实的 生产库性能分析，用户抱怨近很慢，希望给处理下。

现问题：DBCPU和enq: SQ - contention占DBTime比例较高，通过优化SQL，和调整序列号cache解决。下面是AWR报告信息

等待事件如下

下面SQL造成enq: SQ - contention等待严重
select ID_DB_DYNAMIC.nextval from dual
查询发现cache_size 20 太小
SQL>select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE,ORDER_FLAG from dba_sequences where SEQUENCE_NAME='ID_DB_DYNAMIC'

SEQUENCE_OWNER SEQUENCE_NAME CACHE_SIZE ORDER_FLAG
-------------------- ------------------------------ ---------- -----------------
user1 ID_DB_DYNAMIC 20 N
user2 ID_DB_DYNAMIC 20 N
user3 ID_DB_DYNAMIC 20 N
user4 ID_DB_DYNAMIC 20 N

后调整到10000；
SQL> alter sequence user1.ID_DB_DYNAMIC cache 10000;
alter sequence user5.ID_DB_DYNAMIC cache 10000;
alter sequence HJCS.ID_DB_DYNAMIC cache 10000;
alter sequence PJCZJK.ID_DB_DYNAMIC cache 10000;
Sequence altered.
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE,ORDER_FLAG from dba_sequences where SEQUENCE_NAME='ID_DB_DYNAMIC';

SEQUENCE_OWNER SEQUENCE_NAME CACHE_SIZE ORDER_FLAG
-------------------- ------------------------------ ---------- -----------------
user1 ID_DB_DYNAMIC 10000 N
user2 ID_DB_DYNAMIC 10000 N
user3 ID_DB_DYNAMIC 10000 N
user4 ID_DB_DYNAMIC 10000 N

下面SQL语句耗费CPU资源
SQL_ID 8yt2jby7ratbu, child number 1
-------------------------------------
UPDATE tab1 T SET HC = (SELECT SUM(HC) FROM crs WHERE MLPK =
T.PK) WHERE PK = :B1
Plan hash value: 1384642760
-----------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------
| 0 | UPDATE STATEMENT | | |
| 1 | UPDATE | tab1 | |
|* 2 | INDEX UNIQUE SCAN | SYS_C0021636 | 1 |
| 3 | SORT AGGREGATE | | 1 |
|* 4 | TABLE ACCESS FULL | crs | 60 |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PK"=:B1)
4 - filter("MLPK"=:B1
分析该SQL可以，第4步骤返回60行，此时通过全表扫描过滤数据，显然不合理，通过在子查询的表中创建对应谓词的索引解决
SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where table_name='crs'
OWNER INDEX_NAME STATUS
-------------------- -------------------- --------
user2 SYS_C0021536 VALID
user3 SYS_C0028586 VALID
user4 SYS_C0033452 VALID

创建索引
create index user5.idx_crs_MLPK on user5.crs(MLPK) online;
create index user4.idx_crs_MLPK on user4.crs(MLPK) local online;
下面是新的执行计划
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2327078715
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 11 | 15 (7)| 00:00:01 |
| 1 | UPDATE | tab1 | | | | |
|* 2 | INDEX UNIQUE SCAN | SYS_C0021636 | 1 | 11 | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 10 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| crs | 60 | 600 | 12 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_crs_MLPK | 60 | | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PK"=:B1)
5 - access("MLPK"=:B1)
18 rows selected.
后续观察故障期间的上述等待事件消失，SQL执行计划合理，DBtime降低

等待事件消失