Enq: SQ-contention

-- Execution per minute SQLselectsql_id, mi, count (mi) 2 from (selectevent, SQL _id, to_char (sample_time, yyyymmddhh24mi) mi --, 3 -- average (201407

-- Execution per minute 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

-- Execution per minute
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 ('2014, 20140717 111100', 'yyyymmdd hh24mi ')
7 and sample_time <to_date ('2014, 20140717 111100', '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

Through the image, it is found that the statement for getting the first sequence is of temporary performance.

-- Wait for events and statements
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 ('2014, 20140717 111100', 'yyyymmdd hh24mi ')
7 and sample_time <to_date ('2014, 20140717 111100', '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: sql-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: sql-contention 7wxfw53bsmgpq 20140717 0947 112
Buffer busy waits 7wxfw53bsmgpq 20140717 0949 1
Enq: SQ-contention 7wxfw53bsmgpq 20140717 0949 43

-- Query by size acquisition
SQL> select sequence_name, cache_size from dba_sequences where sequence_name like upper ('% qreque % ');

SEQUENCE_NAME CACHE_SIZE
----------------------------------------
Change QREQUESTNO 20 to 100 during testing

SQL>

-- Query by size acquisition
SQL> select sequence_name, cache_size from dba_sequences where sequence_name like upper ('% qreque % ');

SEQUENCE_NAME CACHE_SIZE
----------------------------------------
Change QREQUESTNO 20 to 100 during testing