2014. 3. 1. 15:48

[오라클] 월별 통계

SH 스키마 필요 // 년도별 월별 통계 //2014.10.29 수정

 

SELECT
    CUST_ID, YYYY, SUM("01") AS "1월", SUM("02") AS "2월", SUM("03") AS "3월", SUM("04") AS "4월", SUM("05") AS "5월", SUM("06") AS "6월",
    SUM("07") AS "7월", SUM("08") AS "8월", SUM("09") AS "9월", SUM("10") AS "10월", SUM("11") AS "11월", SUM("12") AS "12월",
    SUM(AMOUNT_SOLD) AS "총계"
FROM(
        SELECT CUST_ID, TO_CHAR(TIME_ID,'YYYY') AS YYYY, TO_CHAR(TIME_ID,'MM') AS MM, TO_CHAR(TIME_ID,'DD') AS DD, AMOUNT_SOLD
        , DECODE(TO_CHAR(TIME_ID,'MM'), '01', AMOUNT_SOLD, 0) AS "01"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '02', AMOUNT_SOLD, 0) AS "02"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '03', AMOUNT_SOLD, 0) AS "03"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '04', AMOUNT_SOLD, 0) AS "04"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '05', AMOUNT_SOLD, 0) AS "05"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '06', AMOUNT_SOLD, 0) AS "06"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '07', AMOUNT_SOLD, 0) AS "07"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '08', AMOUNT_SOLD, 0) AS "08"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '09', AMOUNT_SOLD, 0) AS "09"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '10', AMOUNT_SOLD, 0) AS "10"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '11', AMOUNT_SOLD, 0) AS "11"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '12', AMOUNT_SOLD, 0) AS "12"
        FROM SH.SALES
--        WHERE TO_CHAR(TIME_ID,'YYYY') BETWEEN 1999 AND 2000 --특정년도 범위

--        AND TO_CHAR(TIME_ID,'YYYYMM') = '199801'  --특정년월
--        AND TO_CHAR(TIME_ID,'MM') = 05 --특정달만
)GROUP BY CUST_ID, YYYY
ORDER BY CUST_ID ASC, YYYY ASC

 

결과

 

 

 

2014. 3. 1. 13:48

오라클 상태 조회

1. 세션별 CPU Time, Memory 사용량
 
select s.sid, s.serial#, p.spid as "os pid", s.username, s.module, s.sql_id, event, seconds_in_wait,
st.value/100 as "cpu sec",
round(pga_used_mem/1024/1024) "pga_tot(mb)",
round(pga_used_mem/1024/1024) "pga_per_sess(mb)"
from v$sesstat st, v$statname sn, v$session s, v$process p
where sn.name = 'CPU used by this session' -- cpu
and st.statistic# = sn.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and s.last_call_et < 1800 -- active within last 1/2 hour
and s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
order by st.value

 
 
 
 
2. Client별 Memory 사용량
 
select machine,status,count(*) cnt, 
       round(sum(pga_used_mem)/1024/1024) "pga_tot(mb)",
       round(sum(pga_used_mem)/count(*)/1024/1024) "pga_per_sess(mb)"
from v$session s, v$process p
where 1=1
--and s.status='active'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1