Concurrent Request Analytics. 

Find number of concurrent requests submitted month wise. 

select to_char(a.requested_start_date,'yymon'), count(*) from 

fnd_concurrent_requests a

--where concurrent_program_id in ('20428','20821') use to find for a particular program

GROUP BY to_char(a.requested_start_date,'yymon')  order by to_char(a.requested_start_date,'yymon') desc;

Find size of files per extension

Note: change grep to include month year file or file extension 

cd $APPLCSF

Size of files per month and per type 

ls -ltr | grep Jul |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep xml | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep txt | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep xls | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

size of file per day

ls -ltr | grep Aug |grep xls |awk '{a[$6 $7]+=$5;}END{for(i in a)print i", "a[i];}'

Find concurrent requests older than 60 days with completion duration 

SELECT

      f.request_id ,

      pt.user_concurrent_program_name user_conc_program_name,

      f.actual_start_date start_on,

      f.actual_completion_date end_on,

      floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)

        || ' HOURS ' ||

        floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)

        || ' MINUTES ' ||

        round((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600 -

        (floor((((f.actual_completion_date-f.actual_start_date)

        *24*60*60) -

        floor(((f.actual_completion_date-f.actual_start_date)

        *24*60*60)/3600)*3600)/60)*60) ))

        || ' SECS ' time_difference,

      p.concurrent_program_name concurrent_program_name,

      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

      f.status_code

from  apps.fnd_concurrent_programs p,

      apps.fnd_concurrent_programs_tl pt,

      apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

      and f.program_application_id = p.application_id

      and f.concurrent_program_id = pt.concurrent_program_id

      and f.program_application_id = pt.application_id

      AND pt.language = USERENV('Lang')

      and f.actual_start_date is not null

      and f.actual_start_date > sysdate - 60

      and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60

order by

      f.actual_start_date desc; 

Find average, min, max for concurrent program in last 60 days

SELECT

      pt.user_concurrent_program_name user_conc_program_name,

      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

      count(*),

  

      TRUNC (AVG (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

         || ' Days'

         || ' + '

         || TO_CHAR (

               TRUNC (SYSDATE)

               + NUMTODSINTERVAL (

                    AVG ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

                    * 86400,

                    'second'),

               'HH24:MI:SS')

            "AVERAGE",

            

        TRUNC (MIN (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

         || ' Days'

         || ' + '

         || TO_CHAR (

               TRUNC (SYSDATE)

               + NUMTODSINTERVAL (

                    MIN ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

                    * 86400,

                    'second'),

               'HH24:MI:SS')

            "Min",

            

            TRUNC (MAX (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

         || ' Days'

         || ' + '

         || TO_CHAR (

               TRUNC (SYSDATE)

               + NUMTODSINTERVAL (

                    MAX ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

                    * 86400,

                    'second'),

               'HH24:MI:SS')

            "Max"

from  apps.fnd_concurrent_programs p,

      apps.fnd_concurrent_programs_tl pt,

      apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

      and f.program_application_id = p.application_id

      and f.concurrent_program_id = pt.concurrent_program_id

      and f.program_application_id = pt.application_id

      AND pt.language = USERENV('Lang')

      and f.actual_start_date is not null

      and f.actual_start_date > sysdate - 60

      and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60

  group by pt.user_concurrent_program_name, f.phase_code ;