[20131217]pivot應用例子.txt

PIVOT是11G的新特性,可以把列轉換為行,自己寫一個例子:

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select name from V$SEGSTAT_NAME order by statistic#;
SCOTT@test> select statistic#,name from V$SEGSTAT_NAME order by statistic#;
STATISTIC# NAME
---------- ------------------------------
         0 logical reads
         1 buffer busy waits
         2 gc buffer busy
         3 db block changes
         4 physical reads
         5 physical writes
         6 physical read requests
         7 physical write requests
         8 physical reads direct
         9 physical writes direct
        11 optimized physical reads
        12 gc cr blocks received
        13 gc current blocks received
        14 ITL waits
        15 row lock waits
        17 space used
        18 space allocated
        20 segment scans

18 rows selected.

--10g
STATISTIC# NAME
---------- ------------------------------
         0 logical reads
         1 buffer busy waits
         2 gc buffer busy
         3 db block changes
         4 physical reads
         5 physical writes
         6 physical reads direct
         7 physical writes direct
         9 gc cr blocks received
        10 gc current blocks received
        11 ITL waits
        12 row lock waits
        14 space used
        15 space allocated
        17 segment scans
15 rows selected.

WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                   IN (select name from V$SEGSTAT_NAME order by statistic#)
--這樣寫不行.
ERROR at line 10:
ORA-00936: missing expression

--11G
WITH pivot_stats
     AS (SELECT owner,
                object_name,
                statistic_name,
                VALUE
           FROM v$segment_statistics)
SELECT *
FROM pivot_stats PIVOT (SUM (VALUE)
                   FOR statistic_name
                  IN ('logical reads',
                       'buffer busy waits',
                       'gc buffer busy',
                       'db block changes',
                       'physical reads',
                       'physical writes',
                       'physical read requests',
                       'physical write requests',
                       'physical reads direct',
                       'physical writes direct',
                       'optimized physical reads',
                       'gc cr blocks received',
                       'gc current blocks received',
                       'ITL waits',
                       'row lock waits',
                       'space used',
                       'space allocated',
                       'segment scans'))
--適當的選擇排序,可以定位那個object_name存在問題.

--如果使用10g寫法就比較複雜了.
SELECT owner,
         object_name,
         MAX (DECODE (statistic_name, 'logical reads', VALUE, 0))              "logical reads",
         MAX (DECODE (statistic_name, 'buffer busy waits', VALUE, 0))          "buffer busy waits",
         MAX (DECODE (statistic_name, 'gc buffer busy', VALUE, 0))             "gc buffer busy",
         MAX (DECODE (statistic_name, 'db block changes', VALUE, 0))           "db block changes",
         MAX (DECODE (statistic_name, 'physical reads', VALUE, 0))             "physical reads",
         MAX (DECODE (statistic_name, 'physical writes', VALUE, 0))            "physical writes",
         MAX (DECODE (statistic_name, 'physical read requests', VALUE, 0))     "physical read requests",
         MAX (DECODE (statistic_name, 'physical write requests', VALUE, 0))    "physical write requests",
         MAX (DECODE (statistic_name, 'physical reads direct', VALUE, 0))      "physical reads direct",
         MAX (DECODE (statistic_name, 'physical writes direct', VALUE, 0))     "physical writes direct",
         MAX (DECODE (statistic_name, 'optimized physical reads', VALUE, 0))   "optimized physical reads",
         MAX (DECODE (statistic_name, 'gc cr blocks received', VALUE, 0))      "gc cr blocks received",
         MAX (DECODE (statistic_name, 'gc current blocks received', VALUE, 0)) "gc current blocks received",
         MAX (DECODE (statistic_name, 'ITL waits', VALUE, 0))                  "ITL waits",
         MAX (DECODE (statistic_name, 'row lock waits', VALUE, 0))             "row lock waits",
         MAX (DECODE (statistic_name, 'space used', VALUE, 0))                 "space used",
         MAX (DECODE (statistic_name, 'space allocated', VALUE, 0))            "space allocated",
         MAX (DECODE (statistic_name, 'segment scans', VALUE, 0))              "segment scans"
    FROM v$segment_statistics
GROUP BY owner, object_name

[20131217]pivot應用例子.txt

相關文章