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 optionsSQL> 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.242sSQL*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 optionsSQL> 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
fisqlplus 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.
No comments:
Post a Comment