[20120216]檢視資料庫整體效能問題sql語句.txt

從這個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)。過幾天再觀察看看。