http://dbatrain.wordpress.com/2010/11/22/do-you-have-an-oracle-background/
从这个blog获得这个sql语句，通过这个查询到生产系统数据库一个seqence没有设置好。
/* Formatted on 2012/02/16 09:45 (Formatter Plus v4.8.8) */
column value format 9999,9999,9999,9999
column stat_name format a60
SELECT LPAD (' ', (LEVEL - 1) * 2) || stat_name AS stat_name, VALUE,
CASE stat_name
WHEN 'background elapsed time'
THEN '1'
WHEN 'background cpu time'
THEN '1-1'
WHEN 'RMAN cpu time (backup/restore)'
THEN '1-1-1'
WHEN 'DB time'
THEN '2'
WHEN 'DB CPU'
THEN '2-1'
WHEN 'connection management call elapsed time'
THEN '2-2'
WHEN 'sequence load elapsed time'
THEN '2-3'
WHEN 'sql execute elapsed time'
THEN '2-4'
WHEN 'parse time elapsed'
THEN '2-5'
WHEN 'hard parse elapsed time'
THEN '2-5-1'
WHEN 'hard parse (sharing criteria) elapsed time'
THEN '2-5-1-1'
WHEN 'hard parse (bind mismatch) elapsed time'
THEN '2-5-1-1-1'
WHEN 'failed parse elapsed time'
THEN '2-5-2'
WHEN 'failed parse (out of shared memory) elapsed time'
THEN '2-5-2-1'
WHEN 'PL/SQL execution elapsed time'
THEN '2-6'
WHEN 'inbound PL/SQL rpc elapsed time'
THEN '2-7'
WHEN 'PL/SQL compilation elapsed time'
THEN '2-8'
WHEN 'Java execution elapsed time'
THEN '2-9'
WHEN 'repeated bind elapsed time'
THEN '2-A'
ELSE '9'
END AS pos
FROM (SELECT /*+ no_merge */
stat_name, stat_id, VALUE,
CASE
WHEN stat_id IN (2411117902)
THEN 2451517896
WHEN stat_id IN (268357648)
THEN 3138706091
WHEN stat_id IN (3138706091)
THEN 372226525
WHEN stat_id IN (4125607023)
THEN 1824284809
WHEN stat_id IN (2451517896)
THEN 4157170894
WHEN stat_id IN (372226525, 1824284809)
THEN 1431595225
WHEN stat_id IN (3649082374, 4157170894)
THEN NULL
ELSE 3649082374
END parent_stat_id
FROM v$sys_time_model)
CONNECT BY PRIOR stat_id = parent_stat_id
START WITH parent_stat_id IS NULL
ORDER BY pos;
quit;
values表示的是microseconds.
例子:
STAT_NAME VALUE POS
------------------------------------------------------------ -------------------- ---------
background elapsed time 3121,7786,0635 1
background cpu time 1666,7215,3938 1-1
RMAN cpu time (backup/restore) 1646,8060,3756 1-1-1
DB time 7462,1532,0455 2
DB CPU 3556,3403,6028 2-1
connection management call elapsed time 2,6776,8809 2-2
sequence load elapsed time 10,0778,7756 2-3
sql execute elapsed time 6428,4081,1248 2-4
parse time elapsed 119,6809,5382 2-5
hard parse elapsed time 13,8575,2087 2-5-1
hard parse (sharing criteria) elapsed time 1,9757,1748 2-5-1-1
hard parse (bind mismatch) elapsed time 2045,7314 2-5-1-1-1
failed parse elapsed time 779,6920 2-5-2
failed parse (out of shared memory) elapsed time 0 2-5-2-1
PL/SQL execution elapsed time 413,7540,3091 2-6
inbound PL/SQL rpc elapsed time 2,2555,8554 2-7
PL/SQL compilation elapsed time 1,5043,7340 2-8
Java execution elapsed time 2805 2-9
repeated bind elapsed time 589,4462 2-A
19 rows selected.
可以发现以层次的方式显示，很清晰！sequence load elapsed time占用1007秒,而sql execute elapsed time占用642840秒！通过查看awr很难看出seq有问题。1007787756/642840811248 = .001567709669 占用0.1%,不算很多。检查发现一些seqence确实设置太小(设置都是100)。过几天再观察看看。