Oracle ERP 유지보수 하던 시절에 사용하던 check스크립트

COL snap_column NEW_VALUE snap_time
SELECT to_char(sysdate, 'YYYY.MM.DD') "SNAP_COLUMN" FROM   dual
/


break on today 
column characterset noprint new_value char 
select a.value||'.'||b.value||'.'||c.value characterset 
from 
(select value from NLS_DATABASE_PARAMETERS where parameter='NLS_LANGUAGE')a,
(select value from NLS_DATABASE_PARAMETERS where parameter='NLS_TERRITORY')b,
(select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET')c
group by a.value||'.'||b.value||'.'||c.value

 
column name noprint new_value dbname 
select name from v$database; 
 
set heading on 
set feedback off 
set linesize 140

SET ECHO OFF

CLEAR COLUMNS;


SPOOL check.out.&snap_time


select to_char(creation_time, 'RRRR-MM') month, sum(bytes)/1024/1024 growth
  from sys.v_$datafile
 group by to_char(creation_time, 'RRRR-MM')
 order by to_char(creation_time, 'RRRR-MM');
 
 
prompt ********************************************************** 
prompt *****            Database Information                ***** 
prompt ********************************************************** 
ttitle left "DATABASE:"dbname" (characterset: "charset" )" 
select name, created, log_mode from v$database; 
prompt 


ttitle off 
prompt *******************************************************
prompt *               version info                          *
prompt *******************************************************
prompt 
col comp_name for a35;
col version for a30;
select * from v$version;
select comp_name, version from dba_registry;
clear columns;

prompt
prompt *******************************************************
prompt *               parameter info                        *
prompt *******************************************************
prompt 
col name for a20
col value for a20
select name, value from v$parameter
where name IN ('cpu_count', 'db_name', 'db_block_size','db_cache_size', 'shared_pool_size', 'log_buffer');

prompt
prompt
prompt *******************************************************
prompt *               HOT BACKUP INFO                       *
prompt *******************************************************
prompt
col BACKUP_TIME for a30
select file#, status, change#, to_char(TIME, 'YYYY-MM-DD HH24:MI') as BACKUP_TIME from v$backup;
clear columns;

prompt
prompt *******************************************************
prompt *            Redo Log Switch Info                     *
prompt *******************************************************
prompt
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF CNT ON REPORT
SELECT THREAD#, TO_CHAR(FIRST_TIME, 'YYYYMMDD') FIRST_TIME, count(*) CNT from v$log_history
WHERE FIRST_TIME BETWEEN SYSDATE -6 AND SYSDATE
GROUP BY THREAD#, TO_CHAR(FIRST_TIME, 'YYYYMMDD')
ORDER BY THREAD#, FIRST_TIME;

SELECT THREAD#, TO_CHAR(FIRST_TIME, 'YYYYMMDD HH24:MI') FIRST_TIME from v$log_history
WHERE FIRST_TIME BETWEEN SYSDATE -6 AND SYSDATE
ORDER BY 1, 2;
prompt
prompt

prompt
prompt *******************************************************
prompt *            Invalid Obecjt                           *
prompt *******************************************************
prompt
SELECT OWNER, OBJECT_TYPE,count(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER, OBJECT_TYPE;
prompt 
prompt # $ORACLE_HOME/rdbms/admin/utlrp.sql
prompt # alter package schemaname.packagename compile;
prompt


PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *               LIBRARY CACHE TUNING                  *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT 

SELECT TO_CHAR(TRUNC(SUM(reloads)/SUM(pins)*100, 5),99.99999)||
       '% (LESS THAN 1%)' "LIBRARY CACHE MISS RATIO"
  FROM V$LIBRARYCACHE;

PROMPT 
PROMPT # libary cache miss ratio is good if it is less than 1 -2 %
PROMPT # ratio를 늘리려면 shared pool size의 값을 증가 시켜야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            DICTIONARY CACHE TUNING                  *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN Parameter Heading "INIT.ORA PARAMETER"
COLUMN Parameter FORMAT A22
COLUMN "HIT %"   FORMAT 99990.0

SELECT TRUNC(SUM(getmisses)/SUM(gets)*100, 5)||'%  (LESS THAN 9.8%)' 
       "DATA DICTIONARY MISS RATIO "
  FROM V$ROWCACHE;

PROMPT
PROMPT # data dictionary miss ratio is good if it is less than 9.8% 
PROMPT # ratio를 늘리려면 shared pool size의 값을 증가 시켜야 한다. 
PROMPT


PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *              BUFFER CACHE TUNING                    *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT


select trunc((a.value+b.value-c.value)/(a.value+b.value)*100,2) 
       "Buffer Cache hit ratio"
  from sys.v_$sysstat a, sys.v_$sysstat b, sys.v_$sysstat c
 where a.name='db block gets' 
   and b.name='consistent gets' 
   and c.name='physical reads';


PROMPT
PROMPT # buffer_cache hit ratio is good if it is more than 60-70% 
PROMPT # ratio를 늘리려면 parameter file의 db_block_buffers의 값을 
PROMPT # 증가 시켜야 한다. 
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        INTERNAL SORT AND EXTERNAL SORT              *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN name FORMAT A20 

SELECT name, value
  FROM V$SYSSTAT
 WHERE name IN ('sorts (memory)', 'sorts (disk)');

PROMPT
PROMPT # MEMORY SORT에 비해 DISK SORT가 상대적으로 많으면
PROMPT # SORT_AREA_SIZE의 크기를 늘려 주어야 한다.
PROMPT


PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *        ROLLBACK SEGMENT'S WAIT RATIO                *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

COLUMN miss_ratio FORMAT A20

SELECT name "ROLLBACK SEGMENT NAME", 
       TRUNC(waits/gets*100, 5)||'%' miss_ratio
  FROM V$ROLLSTAT, V$ROLLNAME
 WHERE V$ROLLSTAT.usn = V$ROLLNAME.usn;

PROMPT
PROMPT # MISS RATIO가 1~2% 이하 이어야 한다.
PROMPT # 1~2%보다 크면 ROLLBACK SEGMENT의 갯수를 늘려주어야 한다.
PROMPT

PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *            REDO LOG FILE'S WAIT RATIO               *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

SELECT value "REDO LOG REQUEST"
  FROM V$SYSSTAT
 WHERE name = 'redo log space requests';

PROMPT
PROMPT # 0에 가까워야 한다.
PROMPT # LOG_bUFFER를 늘려주어야 한다.
PROMPT
CLEAR COLUMNS;
PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *           Internal latch Contention                 *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

set numwidth 10
col name format a30
col ratio format 9.99
col name format a25
col sleeps format 9999999

select NAME   , GETS   , MISSES, (gets-misses)/gets ratio,
       SLEEPS, round(sleeps/decode(misses,0,1,misses),3) "SLEEP/MISS"
  from v$latch
 where gets != 0
 order by MISSES desc



PROMPT
PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *                      free space확보                 *
PROMPT *                                                     * 
PROMPT *******************************************************
PROMPT
PROMPT
PROMPT
CLEAR COLUMNS;


SET PAGESIZE 100 
SET LINESIZE 100
COLUMN pct_free FORMAT 999.99 HEADING "% Free" 
COLUMN pct_used FORMAT 999.99 HEADING "% Used"
COLUMN name FORMAT A20 HEADING "Tablespace Name" 
COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes" 
COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes" 
COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes" 

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF mbytes free used ON REPORT 

select df.tablespace_name name, ts.extent_management ext_mgt,
df.totalspace mbytes,
(df.totalspace - NVL(fs.freespace,0)) used,
NVL(fs.freespace,0) free,
100 * (NVL(fs.freespace,0) / df.totalspace) pct_free,
100 * ((df.totalspace - NVL(fs.freespace,0)) / df.totalspace) pct_used
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM dba_free_space
GROUP BY tablespace_name
) fs, dba_tablespaces ts
WHERE df.tablespace_name = fs.tablespace_name(+)
AND df.tablespace_name = ts.tablespace_name
order by 100 * ((df.totalspace - NVL(fs.freespace,0)) / df.totalspace) desc ;



PROMPT
PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *                O/S DISK  사용량                     *
PROMPT *                                                     *
PROMPT *******************************************************
# 구미에 맞게 선택
#!df -k
!df -h
#!dbf
PROMPT 
PROMPT *******************************************************
PROMPT *               host      info                        *
PROMPT *******************************************************
PROMPT 
! /usr/platform/sun4u/sbin/prtdiag
! uname -a
! model
! grep -i CPU /var/adm/syslog/syslog.log
! grep -i Physical /var/adm/syslog/syslog.log
! cat /etc/hosts

PROMPT *******************************************************
PROMPT *                                                     *
PROMPT *                     Total Space                     *
PROMPT *                                                     * 
PROMPT *******************************************************

select 
	   sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes
from sys.dba_free_space a, sys.dba_data_files b;



PROMPT
PROMPT *******************************************
PROMPT * Shared Pool Free Memory                 *
PROMPT *******************************************
PROMPT

select pool, name, bytes/1024/1024 "MB"
from v$sgastat where name = 'free memory';



PROMPT
PROMPT *******************************************
PROMPT * Total SGA size                 *
PROMPT *******************************************
PROMPT

select sum(value)/1024/1024 from v$sga;



PROMPT
PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *            DATATFILE 의 Read/Write   I/O            *
PROMPT *                                                     *
PROMPT *******************************************************

 select name "Name"
       ,phyrds "Physical Reads"
       ,phywrts "Physical Writes"
       ,phyrds+phywrts "Total Block IO"
       ,trunc(phyrds/read_sum*100,2) "Read(%%)"
       ,trunc(phywrts/write_sum*100,2) "Write(%%)"
       ,trunc( (phyrds+phywrts) / (read_sum+write_sum) * 100 , 2) "Total IO(%%)"
   from v$filestat a, v$datafile b,
       (select sum(phyrds) read_sum, sum(phywrts) write_sum
          from v$filestat ) c
  where a.file# = b.file#
  order by 7 desc;
  
  

PROMPT   v$filestat
PROMPT >> file# : file number(이름을 얻기 위해서는 v$datafile 의 file# 에 조인)
PROMPT >> Phyrds : 물리적 읽기 수
PROMPT >> Phywrts : 물리적 쓰기 수
PROMPT >> phyblkrd : 물리적 블록 읽기 수
PROMPT >> phyblkwrt : 물리적 블록 쓰기 수
PROMPT >> readtim : 읽기에 사용된 시간
PROMPT >> writetim : 쓰기에 사용된 시간





PROMPT
PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *           Monitor Session Idle Time                 *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

col sid      format 999
col username format a10 truncated
col status   format a1  truncated
col logon    format a17
col idle     format a9
col program  format a30 truncated

select 
   sid,username,status,
   to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
   floor(last_call_et/3600)||':'||
   floor(mod(last_call_et,3600)/60)||':'||
   mod(mod(last_call_et,3600),60) "IDLE",
   program
from 
   v_$session
where 
   type='USER'
order by last_call_et;

PROMPT
PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *                Redo Log Summary                     *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

col group# 	format 999      heading 'Group'  
col member 	format a45	heading 'Member' justify c 
col status 	format a10	heading 'Status' justify c	 
col archived	format a10	heading 'Archived' 	 
col fsize 	format 999 	heading 'Size|(MB)'  
 
select  l.group#, 
        member, 
 	archived, 
        l.status, 
        (bytes/1024/1024) fsize 
from    v$log l, 
	v$logfile f 
where f.group# = l.group# 
order by 1 ;



PROMPT
PROMPT *******************************************************
PROMPT *                                                     * 
PROMPT *           Rollback Segments Summary                 *
PROMPT *                                                     *
PROMPT *******************************************************
PROMPT

set feed off 
set pause off 
col nm format a7 heading 'Name' trunc 
col ex format 999 headin 'NrEx' 
col rs format a7 heading 'Size' 
col init format 999,999 heading 'Init' 
col next format 999,999 heading 'Next' 
col mi format 999 heading 'MinE' 
col ma format 999 heading 'MaxE' 
col op format 99,999,999 heading 'Opt size' 
col pct format 990 heading 'PctI' 
col st format a4 heading 'Stat' 
col sn format a15 heading 'Segm Name' 
col ts format a12 heading 'In TabSpace' 
col fn format a45 heading 'File containing header of rbs' 
col ow format a4  heading 'Ownr' 

PROMPT 
prompt All Rollback Segments 
PROMPT

select segment_name sn, owner, 
       tablespace_name ts, name fn 
from sys.dba_rollback_segs d, v$datafile f 
where d.file_id = f.file#; 
 
prompt 
prompt Online Rollback Segments: 
PROMPT
select d.segment_name name, 
       s.extents ex, 
       (s.rssize/1024)||'K' rs, 
       d.initial_extent init_ext, 
       d.next_extent next_ext, 
   d.pct_increase pct, 
       d.min_extents , 
       d.max_extents , 
optsize op, 
       decode(d.status,'ONLINE','On','OFFLINE','Off') st 
from v$rollname n, v$rollstat s, sys.dba_rollback_segs d 
where n.usn = s.usn 
and   d.segment_name = n.name(+); 




prompt ###############################################################
prompt ### TEMP 사용하는 session 잡아내기. 
prompt ###############################################################

select s.sid, s.username, u.tablespace, u.contents, u.extents, u.blocks, 
u.blocks*8192/1024/1024 as "M byte",
(select sql_text from v$sql where address = s.sql_address) sql
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY';


prompt ################################################################### 
prompt ### temporary datafile used ( temp datafile 의 사용현황을 확인 )
prompt ###################################################################

set line 250;
set pages 1000;
column "Status"     format a6;
column "Name"       format a40;
column "Tablespace" format a20;
column ".Used %"     format a7;
column ".        Used /     Total (M)" format a29;
column "Auto"     format a4;
column "Contents"   format a9;

SELECT "Status","Contents", "Name", "Auto", "Tablespace", 
       ".Used %", ".        Used /     Total (M)"  FROM (
SELECT /*+ ordered no_merge(v) */ v.status "Status",
       d.file_name "Name",
       x.contents  "Contents",
       d.tablespace_name "Tablespace",
       NVL(d.autoextensible,'NO') "Auto",
       TO_CHAR(NVL(d.bytes/1024/1024,0),'99999990.000') "Size (M)",
       TO_CHAR(NVL(t.bytes_cached/1024/1024,0),'99999999.999') || ' / ' || 
       TO_CHAR(NVL(d.bytes/1024/1024,0),'99999999.999') ".        Used /     Total (M)",
       TO_CHAR(NVL(t.bytes_cached/d.bytes*100,0),'990.00') ".Used %"
FROM   sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v, dba_tablespaces x
WHERE  (t.file_id (+)= d.file_id) AND  (d.file_id = v.file#) 
and (x.TABLESPACE_NAME = d.TABLESPACE_NAME)
) order by 4 desc, 1, 5 desc;

column col1 format a8                 heading "Owner"
column col2 format a30                heading "Object_Name"
column col3 format 999,999,999        heading "Row_CNT"
column col4 format 999,999,999        heading "Chain_CNT"
column col5 format 999                heading "%"
column col6 format 999                heading "Used"
column col7 format 999                heading "Free"
 

prompt
prompt *******************************************************
prompt *                                                     *
prompt *        Chain 발생이 심한 Table List(All User)       *
prompt *                                                     *
prompt *******************************************************
prompt

select owner col1 ,table_name col2 , num_rows col3 , nvl(chain_cnt,0) col4, 
       round((nvl(chain_cnt,0)/num_rows) * 100,2) col5,
       PCT_USED col6 ,PCT_FREE col7
from  dba_tables
where nvl(chain_cnt,0) > 0 
order by round((nvl(chain_cnt,0)/num_rows) * 100) desc 
/

prompt
prompt =========================================================================
prompt # Owner       : Owner
prompt # Object_Name : Object Name
prompt # Row_CNT     : Insert된 모든 Row Count
prompt # Chain_CNT   : Chain 발생된 Row Count
prompt # %           : Chain 발생 비율 = Chain_CNT/Row_CNT * 100  
prompt # Used        : PCT_USED
prompt # Free        : PCT_FREE
prompt =========================================================================
prompt

set feedback off
clear columns;

column resource_name        format a30;
column Curr_Val             format 9999999999;
column Max_Val              format 9999999999;
column Init_Allo            format a10;
column Limit_Val          format a10;

 
prompt
prompt *******************************************************
prompt *                                                     * 
prompt *                   Resource Limit                    *
prompt *                                                     *
prompt *******************************************************
prompt 

select resource_name,current_utilization as Curr_Val , max_utilization as Max_Val 
       ,initial_allocation as Init_Allo,limit_value as Limit_Val
from v$resource_limit
/

prompt 
prompt =========================================================================
prompt # 각 자원에 대한 제한 사항 
prompt =========================================================================
prompt 


SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 200000
set feedback off

column col1 format a50        heading "Manager Name"
column col2 format 999999    heading "Actual"
column col3 format 999999    heading "Target"
column col4 format 9999999    heading "Running"
column col5 format 9999999    heading "Pending"
column col6 format 99999    heading "Cache"
column col7 format 99999    heading "Sleep"

 
prompt
prompt =========================================================================
PROMPT			     APPS STATUS
prompt =========================================================================
prompt


prompt
prompt *******************************************************
prompt *                                                     *
prompt *               Concurrent Manager 조회               *
prompt *                                                     *
prompt *******************************************************
prompt

/* Concurrent Manager 현황 조회 */

select /*+ rule */m.USER_CONCURRENT_QUEUE_NAME  col1,
  m.a_cnt  col2,
  m.MAX_PROCESSES  col3,
  r.r_cnt col4,
  p.p_cnt col5,
  m.cache_size  col6,
  m.sleep_seconds  col7
from (
    SELECT a.node_name,
      CONCURRENT_QUEUE_NAME,
      QUEUE_APPLICATION_ID,
      vl.USER_CONCURRENT_QUEUE_NAME,
      vl.MAX_PROCESSES,
      vl.cache_size,
      vl.sleep_seconds,
      a_cnt,
      vl.application_id,
      vl.concurrent_queue_id
    FROM APPS.FND_CONCURRENT_QUEUES_VL vl,
      (
        SELECT /*+ use_hash(a b) */b.node_name,
          QUEUE_APPLICATION_ID,
          CONCURRENT_QUEUE_ID,
          COUNT(*) a_cnt
        FROM APPS.FND_V$PROCESS a,
          APPS.FND_CONCURRENT_PROCESSES b
        WHERE PID = ORACLE_PROCESS_ID
          AND SPID = OS_PROCESS_ID
          AND (PROCESS_STATUS_CODE||'' IN ( 'A',
                  'C',
                  'T' ))
        group by b.node_name, QUEUE_APPLICATION_ID, CONCURRENT_QUEUE_ID ) A
    WHERE enabled_flag='Y'
      and A.QUEUE_APPLICATION_ID(+) = vl.PROCESSOR_APPLICATION_ID
      and A.CONCURRENT_QUEUE_ID(+) = vl.CONCURRENT_QUEUE_ID ) M,
  (
    SELECT CONCURRENT_QUEUE_NAME,
      QUEUE_APPLICATION_ID,
      COUNT(PHASE_CODE) p_cnt
    FROM APPS.FND_CONCURRENT_WORKER_REQUESTS
    WHERE PHASE_CODE = 'P'
      AND HOLD_FLAG != 'Y'
      AND REQUESTED_START_DATE <= SYSDATE
    GROUP BY CONCURRENT_QUEUE_NAME, QUEUE_APPLICATION_ID ) P,
  (
    SELECT CONCURRENT_QUEUE_NAME,
      QUEUE_APPLICATION_ID,
      COUNT(PHASE_CODE) r_cnt
    FROM APPS.FND_CONCURRENT_WORKER_REQUESTS
    WHERE PHASE_CODE = 'R'
    GROUP BY CONCURRENT_QUEUE_NAME, QUEUE_APPLICATION_ID ) R
where P.CONCURRENT_QUEUE_NAME(+) = M.CONCURRENT_QUEUE_NAME
  and P.QUEUE_APPLICATION_ID(+) = M.QUEUE_APPLICATION_ID
  and R.CONCURRENT_QUEUE_NAME(+) = M.CONCURRENT_QUEUE_NAME
  and R.QUEUE_APPLICATION_ID(+) = M.QUEUE_APPLICATION_ID
order by m.USER_CONCURRENT_QUEUE_NAME
/



set linesize 150
set pagesize 500

col "USER_NAME"       format a20
col "DESCRIPTION"     format a20
col "PGM_NAME"        format a45
col "STATUS"          format a15
col "MODULE"          format a20
 
prompt
prompt *******************************************************
prompt *                                                     *
prompt *    일자별 ERP애플리케이션 Concurrent Job 수행 결과  *
prompt *                                                     *
prompt *******************************************************
prompt 

select trunc(sysdate-1)                                      "날짜",
                                              total_end  as  "Concurrent 작업갯수",
                                              normal_end as  "정상종료",
                                              error_end  as  "비정상종료",
                                              etc_end    as  "기타"
from (select count(*)                                 total_end
        from apps.fnd_concurrent_requests
       where actual_start_date >= trunc(sysdate-1)
         and actual_start_date  < trunc(sysdate)),
     (select count(*)                                 normal_end
        from apps.fnd_concurrent_requests
       where actual_start_date >= trunc(sysdate-1)
         and actual_start_date  < trunc(sysdate)
         and phase_code = 'C'
         and status_code != 'E' )                      ,
     (select count(*)                                 error_end
        from apps.fnd_concurrent_requests
       where actual_start_date >= trunc(sysdate-1)
         and actual_start_date  < trunc(sysdate)
         and phase_code = 'C'
         and status_code = 'E' ),
     (select count(*)                                 etc_end
        from apps.fnd_concurrent_requests
       where actual_start_date >= trunc(sysdate-1)
         and actual_start_date  < trunc(sysdate)
         and phase_code not in 'C'
         and status_code NOT IN  ('E', 'C'))
;

prompt
prompt ************************************************************
prompt *                                                          *
PROMPT * 일주일간 ERP애플리케이션 Error 발생 Concurrent Job List  *
prompt *                                                          *
prompt ************************************************************
prompt

select trunc(sysdate-7)                      "날짜",
       p.concurrent_program_name             "PGM_CODE",
       p.user_concurrent_program_name        "PGM_NAME",
       count(*)                              "ERR_COUNT"
  from apps.fnd_concurrent_programs_vl   p,
       apps.fnd_concurrent_requests      r
 where r.concurrent_program_id not in (31659,37933,39554,36034,36887)
   and r.actual_start_date >= trunc(sysdate-7)
   and r.actual_start_date  < trunc(sysdate)
   and r.phase_code             = 'C'
   and r.status_code            = 'E'
   and r.program_application_id = p.application_id
   and r.concurrent_program_id  = p.concurrent_program_id
group by p.concurrent_program_name,
         p.user_concurrent_program_name
order by 3 desc, 1
;

prompt
prompt ************************************************************
prompt *                                                          *
PROMPT * 시간대별 ERP애플리케이션 Concurrent Job 수행 결과 List  *
prompt *                                                          *
prompt ************************************************************
prompt

 
select substr(to_char(actual_start_date,'HH24:MI:SS'),1,2)||':00'        "시간대",
       count(actual_start_date)                                          "Concurrent Job 수행건수",
       round(sum(actual_completion_date-actual_start_date) * 1440)       "Total 수행시간(분)",
       round((avg(actual_completion_date-actual_start_date)*86400),2)    "평균수행시간(초)"
  from apps.fnd_concurrent_requests
 where actual_start_date >= trunc(sysdate-1)
   and actual_start_date  < trunc(sysdate)
   and actual_start_date is not null
   and actual_completion_date is not null
 group by substr(to_char(actual_start_date,'HH24:MI:SS'),1,2)||':00'
;

prompt
prompt ************************************************************
prompt *                                                          *
PROMPT *    ERP애플리케이션 10분이상 수행한 Concurrent Job List   *
prompt *                                                          *
prompt ************************************************************
prompt
col PGM_NAME format a50
col STATUS format a6

select r.request_id                                                                        "REQ_ID",
       substrb(p.concurrent_program_name,1,15)                                             "PGM_CODE",
       substrb(p.user_concurrent_program_name,1,50)                                        "PGM_NAME",
       ceil((nvl(r.actual_completion_date,r.actual_start_date)-r.actual_start_date)*1440)  "Time(분)",
       to_char(actual_start_date, 'yyyy-mm-dd HH24:MI')                                    "FROM_TIME",
       to_char(actual_completion_date, 'yyyy-mm-dd HH24:MI')                               "TO_TIME",
       l.meaning                                                                           "STATUS"
  from apps.fnd_concurrent_programs_vl   p,
       apps.fnd_lookups                  l,
       apps.fnd_concurrent_requests      r
 where 1=1
   --and r.concurrent_program_id not in (36034,31659,36887,39554,42950,42955,43701)  
       /* 
          36034(요청 세트 단계), 31659(보고서 세트), 36887(통지 메일 시스템), 39554(연방세 및 지방세 수탁 보고서 관리자)
          42950(WF 로컬 테이블 동기화),42955(OAM 애플리케이션 대시보드 수집),43701(워크플로우 관리 대기열 삭제)
       */ 
   and r.actual_start_date >= trunc(sysdate-10)
   and r.actual_start_date  <= trunc(sysdate)
   and r.phase_code             = 'C'
   and r.program_application_id = p.application_id
   and r.concurrent_program_id  = p.concurrent_program_id
   and ceil((nvl(r.actual_completion_date,r.request_date)-r.actual_start_date)*1440)  > 10
   and l.lookup_type            = 'CP_STATUS_CODE'
   and r.status_code            = l.lookup_code
order by 6 desc
;

prompt
prompt ************************************************************
prompt *                                                          *
PROMPT *        누적수행시간 상위 20개 Concurrent Job List        *
prompt *                                                          *
prompt ************************************************************
prompt
 
select t.module                          "MODULE",
       t.pgm_code                        "PGM_CODE",
       t.pgm_name                        "PGM_NAME",
       t.exe_minute                      "수행시간(분)",
       t.exe_count                       "수행횟수",
       t.avg_second                      "평균수행시간(초)"
  from
       (
        select a.application_short_name                            module,
               p.concurrent_program_name                           pgm_code,
               p.user_concurrent_program_name                      pgm_name,
               ceil(r.exe_second/60)                               exe_minute,
               r.exe_count                                         exe_count,
               ceil(exe_second/r.exe_count)                        avg_second
          from apps.fnd_concurrent_programs_vl p,
               apps.fnd_application            a,
               (select program_application_id                      application_id,
                       concurrent_program_id                       program_id,
                       sum(actual_completion_date
                           - nvl(actual_start_date,sysdate))*1440*60  exe_second,
                       count(*)                                    exe_count
                  from apps.fnd_concurrent_requests
                 where concurrent_program_id not in (36034,31659,36887,39554,42950,42955,43701)  
                       /* 
                         36034(요청 세트 단계), 31659(보고서 세트), 36887(통지 메일 시스템), 39554(연방세 및 지방세 수탁 보고서 관리자)
                         42950(WF 로컬 테이블 동기화),42955(OAM 애플리케이션 대시보드 수집),43701(워크플로우 관리 대기열 삭제)
                       */ 
                   and actual_start_date >= trunc(sysdate-5)
                   and actual_start_date  < trunc(sysdate)
                   and phase_code||''        = 'C'
                group by program_application_id,
                         concurrent_program_id ) r
         where r.application_id = p.application_id
           and r.program_id     = p.concurrent_program_id
           and r.application_id = a.application_id
        order by 4 desc,5 desc, 1,2
       ) t
 where rownum < 21
;





spool off

CLEAR COLUMNS