Oracle AWR 데이터를 편히 볼 수 있는 PIVOT 기능 - 11g new feature

11g에서 나온 PIVOT 구문입니다. 

아래 sample 처럼 동일 column 값에 대해 pivot 기능이 적용되어 display 됩니다. 

SQL> select job,deptno,avg(sal) from emp  group by job,deptno;

JOB                             DEPTNO   AVG(SAL)

--------------------------- ---------- ----------

PRESIDENT                           10       5000

MANAGER                             30       2850

CLERK                               30        950

CLERK                               10       1300

MANAGER                             20       2975

ANALYST                             20     6499.5

CLERK                               20        950

SALESMAN                            30       1400

MANAGER                             10       2450

SQL> select * from (select job,deptno,sal from emp) 

pivot (avg(sal) for deptno in (10,20,30)) order by job;

JOB                                 10         20         30

--------------------------- ---------- ---------- ----------

ANALYST                                    6499.5

CLERK                             1300        950        950

MANAGER                           2450       2975       2850

PRESIDENT                         5000

SALESMAN                                                1400

PIVOT 기능은 AWR 데이터를 보기편하게 변형하는 데 유용합니다. 

* 아래 SQL은 노드별 user call 값을 출력해본 간단한 SQL 입니다. 

select * from

(

 select to_char(end_interval_time,'HH24:MI:SS') end_interval_time

       ,a.instance_number,value from dba_hist_sysstat a, dba_hist_snapshot b

 where stat_name in ('user calls')

 and a.snap_id = b.snap_id

 and a.instance_number = b.instance_number

)

pivot (avg(value) for instance_number in (1 as "Node1",2 as "Node2",3 as "Node3"))

order by 1

/

END_INTERV                Node1      Node2      Node3

---------- -------------------- ---------- ----------

00:10:31          1,432,826,125 1278172133 1281672038

00:10:34          1,254,265,411 1153949372 1154503152

00:10:39            233,495,867  223300171  222547544

00:20:01          1,255,123,846 1154019492 1154577807

00:20:02          2,260,343,067 2050291466 2048154492

00:20:08            719,183,716  664321484  663506525

00:20:13            478,942,298  446272763  444528572

* V$SYSSTAT의 특정 statistic 값을 query하는 SQL 

select *

from (select to_char(sysdate,'MMDDHH24MISS') dat, a.name,a.value

        from v$sysstat a 

        where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)

        )

pivot (sum(value) for name in ('logons cumulative','user rollbacks','user commits','user calls','session logical reads','physical reads','db block changes', 'physical writes','redo size','parse count (total)','parse count (hard)','execute count'))

order by dat

/

* PIVOT을 decode로 바꿔본 SQL (11g 이전 버전)

select to_char(sysdate,'MMDDHH24MISS') dat,

       sum(decode(name,'logons cumulative',value)) "logons cumulative",

       sum(decode(name,'user rollbacks',value)) "user rollbacks",

       sum(decode(name,'user commits',value)) "user commits",

       sum(decode(name,'user calls',value)) "user calls",

       sum(decode(name,'session logical reads',value)) "session logical reads",

       sum(decode(name,'physical reads',value)) "physical reads",

       sum(decode(name,'db block changes',value)) "db block changes",

       sum(decode(name,'physical writes',value)) "physical writes",

       sum(decode(name,'redo size',value)) "redo size",

       sum(decode(name,'parse count (total)',value)) "parse count (total)",

       sum(decode(name,'parse count (hard)',value)) "parse count (hard)",

       sum(decode(name,'execute count',value)) "execute count"

from v$sysstat

where statistic# in (146,9,67,62,72,6,446,447,0,451,5,4)

group by to_char(sysdate,'MMDDHH24MISS')

/

(왠지 좀 무지해 보이는데.. 더 좋은 방법이 있으면 알려주세요 *^^*)

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!