JDE UBE automatic AWR for jobs

This is easy and cool and you could do a lot more with it.  You’ll understand what I mean when I’m done.

When tracking down performance problems wouldn’t it be nice to see all of the tracing behind the scenes. 

You no not need stats pack it seems to get this cool information:

"Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended." Thanks Tom, one of the few articles I understood https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522853800346871377 

Great, standard edition is going to be fine too.

Here is the script that you’ll need, it does the following:

1. create AWR snapshot (begin)
2. run UBE, with unix time also
3. create AWR snapshot (end)
4. run AWR report based upon the two snapshots
5. aws s3 cp the AWR to an AWS S3 bucket for review
6. used a generated link in excel to point your results (summary of F986110|F986114) to the html file in the bucket.

Holy moly!

The script:

if [ $# -lt 2 ]
  then
   echo "USAGE: $0 REPORT VERSION"
   exit
fi
echo "set feedback off" > sql$$.sql
echo "EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;" >> sql$$.sql
echo "select 'START##' || max(snap_id) from dba_hist_snapshot ;" >> sql$$.sql
echo "quit;" >> sql$$.sql
STARTSNAP=`sqlplus JDE/JDE@JDETEST @sql$$.sql |grep \#\# |grep -v \'|grep START|awk -F\# '{print $3}'`
echo $1_$2 RUNNING##
time runube JDE JDE PY900 *ALL $1   $2     QBATCH     Interactive Hold Save 2>/dev/null
ENDSNAP=`sqlplus JDE/JDE@JDETEST @sql$$.sql |grep \#\# |grep -v \'|grep START|awk -F\# '{print $3}'`
#AWR time  <--  This is a comment – how RAD!
echo "set linesize 8000" >sql$$.sql
echo "set feedback off;" >>sql$$.sql
echo "set heading off;" >>sql$$.sql
echo "set verify off;" >>sql$$.sql
echo "SELECT output FROM    TABLE(dbms_workload_repository.awr_report_html ((select dbid from v\$database),(select instance_number from v\$instance),$STARTSNAP,$ENDSNAP,8 ));" >> sql$$.sql
echo "quit;" >> sql$$.sql
cat sql$$.sql
sqlplus JDE/JDE@JDETEST @sql$$.sql > $1_$2_AWR.html
rm -f ./sql$$.sql
aws s3 cp $1_$2_AWR.html s3://mybucketofawr/$1_$2_AWR.html

The script is run as the jde user that runs the services.  You need to ensure that it can connect to the relevant database that JDE connects to.  I could put some #defines / exports, but you get the picture.

So, if I run this at the command line:

./runube.ksh R0010P XJDE0001

It does everything for me and creates an AWR HTML file in my S3 bucket.

[jde900@bear AWR]$ ./runubedemo.ksh R0010P XJDE0001
R0010P_XJDE0001 RUNNING##

real 0m1.744s
user 0m0.322s
sys 0m0.182s
set linesize 8000
set feedback off;
set heading off;
set verify off;
SELECT output FROM    TABLE(dbms_workload_repository.awr_report_html ((select dbid from v$database),(select instance_number from v$instance),1956,1957,8 ));
quit;
upload: ./R0010P_XJDE0001_AWR.html to s3://mybucketofawr/R0010P_XJDE0001_AWR.html

Note that this is nice, shows you if the job was CPU intensive too (at the logic tier). 

• Real: this is the wall clock time. If other processes are running at the same time, they will slow down your process and thus will increase "real".
• User: the time that CPU spent on your program in user mode.( Kernel mode does not counted in this. For example you requested a disk IO and your disk is very slow. Such a system call is invoked on kernel mode, therefore it will not be reflected to "user".)
• Sys: the time that CPU spent on kernel mode during the execution. Kernel mode contains operations like disk IO, network IO, devices, memory allocation etc. (Part of the mem. allocation is still in user space, though.)

Then you get to goto https://s3-ap-southeast-2.amazonaws.com/mybucketofawr/R0010P_XJDE0001_AWR.html and you can see the actual report!

So, I create a simple spreadsheet based upon the output of the following

SELECT JCPID as INNERPID, JCVERS as INNERVERS, rtrim(JCPID) || rtrim(JCVERS) as CONCAT_PID_VERS, vrjd,
   count(1) as INNERCOUNT,
   Avg(86400*(JCETDTIM-JCSTDTIM)) as INNERAVERAGE,
   min(86400*(JCETDTIM-JCSTDTIM)) AS INNERMIN,
   max(86400*(JCETDTIM-JCSTDTIM)) AS INNERMAX,
   avg(jcpwprcd) as "ROWS PROCESSED"
from svm900.f986114, py900.f983051
where  trim(jcvers) = trim (vrvers) and trim(jcpid) = trim (vrpid)
and (JCETDTIM + interval '13' hour) < TO_DATE('14012018','DDMMYYYY') and (JCETDTIM + interval '13' hour) >= TO_DATE('18062017', 'DDMMYYYY')
group by jcpid, JCVERS, vrjd ;

Great!  so you could now write a custom exit (or – WOW an CAFE1 page) that would link to the AWR automatically.  That’s a nice solution to see the performance stats of your UBEs.

It would also be easy to put this into a “OSA” to make it automatic for mapped UBE’s