AWR your UBE. Performance investigation with JD Edwards (JDE) oracle database and UBEs

This is pretty cool, if I do say so myself.

Have you ever wanted a little more oracle performance information out of your UBEs?  I know logging is good, but AWR is better for performance.

Here are some basics for you (but this could be taken to a whole other level!).

Firstly, the basics of AWR:

create a snaphot, create another one and then create a report based upon the first and second snaphots – RAD!

create

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

run UBE

runube JDE JDE PY900 *ALL $1   $2     QBATCH     Interactive Hold Save

create

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Let’s put that into a simple korn shell script:

if [ $# -ne 2 ]
  then
   echo "USAGE: $0 REPORT VERSION"
   exit
fi
sqlplus JDE/JDE@JDETEST <<EOF
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select max(snap_id)
from
    dba_hist_snapshot ;
quit;
EOF
time runube JDE JDE PY900 *ALL $1   $2     QBATCH     Interactive Hold Save
sqlplus JDE/JDE@JDETEST <<EOF
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select max(snap_id)
from
    dba_hist_snapshot;
quit;
EOF

I like to start simple, then get complex.

This is cool.  It’ll snap and tell you the ID, it’ll run the job and tell you how long it took to run and then it will snap again and tell you the next id – coolio

But I want more and neater.

So now:

if [ $# -ne 2 ]
  then
   echo "USAGE: $0 REPORT VERSION"
   exit
fi
sqlplus JDE/JDE@JDETEST <<EOF
set feedback off
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select 'START##' || max(snap_id)
from
    dba_hist_snapshot ;
quit;
EOF
echo $1_$2 RUNNING##
time runube JDE JDE PY900 *ALL $1   $2     QBATCH     Interactive Hold Save
sqlplus JDE/JDE@JDETEST <<EOF
set feedback off
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
select 'FINISH##' || max(snap_id)
from
    dba_hist_snapshot;
quit;
EOF

This is a little easier – because the output looks like:

you call it like this:

./runme.ksh R30812 F4109 >> R30812_F4109.out 2>&1

and the output looks like:

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 30 14:16:17 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SQL> SQL>   2    3
'START##'||MAX(SNAP_ID)
-----------------------------------------------
START##146
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
R047001A_S0001 RUNNING##
Using User/Password information on the command line has been deprecated.  Although it will continue to work in this release, it will no longer be available as an option in a future release.  Please switch to using one of the -p -f or -d options instead.

New Usage: runube       <[-p|-P] [-f|-F|-d|-D FilePath] [user password]>
                        <Environment>
                        <Role>
                        <ReportName>
                        <VersionName>
                        <JobQueue>
                        <"Interactive"|"Batch">
                        <"Print"|"Hold">
                        <"Save"|"Delete">
                        [Printer]
        -p|-P                   Prompts for user/password information

        -f|-f FilePath          Reads user/password information from the plain text file that is specified in FilePath.

        -d|-D FilePath          Reads user/password information from the plain text file, and indicates the automatic removal of the file.

real    0m19.343s
user    0m8.120s
sys     0m0.242s

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 30 14:16:38 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> SQL> SQL>   2    3
'FINISH##'||MAX(SNAP_ID)
------------------------------------------------
FINISH##147
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

You just need to awk through the output to find ## and grep –v ‘FINISH## and you’ll feed those results into the next script.

Right, but I want to parameterize this, I don’t want to enter in the snaps.

SELECT
    output
FROM    TABLE(dbms_workload_repository.awr_report_text ((select dbid from v$database),(select instance_number from v$instance),112,113 ));

So then I can automate the output too!

./runme.ksh R0010P XJDE0001 >> R0010P_XJDE0001.out 2>&1

The following bad boy will create the AWR report for you.

if [ $# -ne 2 ]
   then
     echo "USAGE FROMSNAP TOSNAP"
     exit
fi

sqlplus JDE/JDE@JDETEST <<EOF
SELECT
     output
  FROM    TABLE(dbms_workload_repository.awr_report_text ((select dbid from v\$database),(select instance_number from v\$instance),$1,$2 ));
quit;
EOF

Putting it all together, you can now grep though the output of your script.

extra for experts

If you wanted to be totally RAD, you could actually create a script and call it runube (but make it a korn shell script).  Basically it would call your stuff and then the actual runube later.  You could put all of the AWR magic in there also, so that you could have AWR’s for all of your reports.  Note that it might be a bit messy because of other transactions, but you’d get the hint about the performance and you’d know which snaps to use for what reports.