enq:SQ-contention

--每分钟执行情况 SQL select sql_id, mi, count(mi) 2 from (select event, sql_id, to_char(sample_time, yyyymmdd hh24mi) mi --, 3 --session_id 4 from dba_hist_active_sess_history 5 where sql_id = 7wxfw53bsmgpq 6 and sample_time to_date(201407

--每分钟执行情况
SQL> select sql_id, mi, count(mi)
2 from (select event, sql_id, to_char(sample_time, 'yyyymmdd hh24mi') mi --,
3 --session_id
4 from dba_hist_active_sess_history
5 where sql_id = '7wxfw53bsmgpq'
6 and sample_time > to_date('20140717 0940', 'yyyymmdd hh24mi')
7 and sample_time 8 group by sql_id, mi
9 order by mi ;

SQL_ID MI COUNT(MI)
------------- ------------- ----------
7wxfw53bsmgpq 20140717 0942 1
7wxfw53bsmgpq 20140717 0943 23
7wxfw53bsmgpq 20140717 0944 125
7wxfw53bsmgpq 20140717 0946 96
7wxfw53bsmgpq 20140717 0947 114
7wxfw53bsmgpq 20140717 0949 44

通过图片，发现对 第一条获取序列的语句比较暂用性能

--等待事件以及语句情况
SQL> select event,sql_id, mi, count(mi)
2 from (select substrb(event,1.30) event, sql_id, to_char(sample_time, 'yyyymmdd hh24mi') mi --,
3 --session_id
4 from dba_hist_active_sess_history
5 where sql_id = '7wxfw53bsmgpq'

6 and sample_time > to_date('20140717 0940', 'yyyymmdd hh24mi')
7 and sample_time 8 group by event,sql_id, mi
9 order by mi ;

EVENT SQL_ID MI COUNT(MI)
---------------------------------------------------------------- ------------- ------------- ----------
7wxfw53bsmgpq 20140717 0942 1
buffer busy waits 7wxfw53bsmgpq 20140717 0943 1
enq: SQ - contention 7wxfw53bsmgpq 20140717 0943 22
buffer busy waits 7wxfw53bsmgpq 20140717 0944 3

enq: SQ - contention 7wxfw53bsmgpq 20140717 0944 122
buffer busy waits 7wxfw53bsmgpq 20140717 0946 2
enq: SQ - contention 7wxfw53bsmgpq 20140717 0946 94
buffer busy waits 7wxfw53bsmgpq 20140717 0947 2
enq: SQ - contention 7wxfw53bsmgpq 20140717 0947 112
buffer busy waits 7wxfw53bsmgpq 20140717 0949 1
enq: SQ - contention 7wxfw53bsmgpq 20140717 0949 43

--大小获取查询
SQL> select sequence_name,cache_size from dba_sequences where sequence_name like upper('%qreque%');

SEQUENCE_NAME CACHE_SIZE
------------------------------ ----------
QREQUESTNO 20 改成100 在测试

SQL>

--大小获取查询
SQL> select sequence_name,cache_size from dba_sequences where sequence_name like upper('%qreque%');

SEQUENCE_NAME CACHE_SIZE
------------------------------ ----------
QREQUESTNO 20 改成100 在测试