[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