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('20140717 0940', 'yyyymmdd hh24mi')
  7             and sample_time < to_date('20140717 0950', 'yyyymmdd hh24mi'))
  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 < to_date('20140717 0950', 'yyyymmdd hh24mi'))
  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  在测试