ITPub博客

首页 > 数据库 > Oracle > [20131217]pivot应用例子.txt

[20131217]pivot应用例子.txt

原创 Oracle 作者:lfree 时间:2013-12-18 09:55:21 0 删除 编辑
[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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1063539/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2669
  • 访问量
    6427238