[20131217]pivot应用例子.txt-阿里云开发者社区

[20131217]pivot应用例子.txt

2013-12-18 716

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： [20131217]pivot应用例子.txtPIVOT是11G的新特性,可以把列转换为行,自己写一个例子:SCOTT@test> @verBANNER-------------------------------------------------------...

[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

文章标签：

Java

[20131217]pivot应用例子.txt

热门文章

最新文章

相关电子书