Script to automate AWR reports (not snapshots)

[Sandra Becker]

Platform: IBM z9
OS: SUSE10
Oracle: 10gR2

Management is under the mistaken impression that the performance problems we
are experiencing on a daily basis ALWAYS happen at the same time. To that
end, they want me to set up a shell script that will run an AWR report daily
for each of the two time periods they believe the problems are occuring. I
haven't been able to convince them we should run reports for the times we
KNOW we had problems. I also haven't been able to locate a script to
automatically run reports for the specific time periods--I'm probably not
entering the right search criteria. Does anyone have such a script or can
point me in the right direction?

[Allen, Brandon]

I think you probably want to start with $ORACLE_HOME/admin/awrrpt.sql, but I don't recall for sure if it will accept beginning/end times/snapshots as command line parameters, or if you have to run it interactively. I've only run it interactively and I think that might be the only option, so you might have to create your own custom copy if you want to automate it for a specified interval. I did the same thing with statspack (spreport.sql) once, but not for AWR. Why go to the trouble of automating it though - why not just run it as needed for troubleshooting this issue, whether it's during the same time period or not? It seems like the effort to script the automation outweighs the benefit based on the available info.

Regards,
Brandon



________________________________
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

[Andrew Kerber]

the command line to call an awr report looks like this, you will need to
find the two most recent snap ids:

select * from
table(dbms_workload_repository.awr_report_text(dbid,instance_number,begin_snap_id,end_snap_id))

You may want the html version instead of the text version.

Post by Allen, Brandon
I think you probably want to start with $ORACLE_HOME/admin/awrrpt.sql,
but I don’t recall for sure if it will accept beginning/end times/snapshots
as command line parameters, or if you have to run it interactively. I’ve
only run it interactively and I think that might be the only option, so you
might have to create your own custom copy if you want to automate it for a
specified interval. I did the same thing with statspack (spreport.sql)
once, but not for AWR. Why go to the trouble of automating it though – why
not just run it as needed for troubleshooting this issue, whether it’s
during the same time period or not? It seems like the effort to script the
automation outweighs the benefit based on the available info.
Regards,
Brandon
------------------------------
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions
and other information in this message that do not relate to the official
business of this company shall be understood as neither given nor endorsed
by it.

[Allen, Brandon]

I hit Send too fast - correction:

$ORACLE_HOME/rdbms/admin/awrrpt.sql



________________________________
Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

[Denis]

Check: http://damir-vadas.blogspot.com/2009/11/automated-awr-reports-in-oracle-10g11g.html


Yu "Denis" Sun
Oracle DBA

From: oracle-l-bounce-***@public.gmane.org [mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of Sandra Becker
Sent: Wednesday, March 03, 2010 3:55 PM
To: oracle-l
Subject: Script to automate AWR reports (not snapshots)


Platform:  IBM z9
OS:  SUSE10
Oracle:  10gR2

Management is under the mistaken impression that the performance problems we are experiencing on a daily basis ALWAYS happen at the same time.  To that end, they want me to set up a shell script that will run an AWR report daily for each of the two time periods they believe the problems are occuring.  I haven't been able to convince them we should run reports for the times we KNOW we had problems.  I also haven't been able to locate a script to automatically run reports for the specific time periods--I'm probably not entering the right search criteria.  Does anyone have such a script or can point me in the right direction?

[Tim Gorman]

Sandra,

Maybe this helps?

It is designed to run via UNIX "cron" from a central database (here
called "dwprod"), but it connects to any configured database to run AWR
reports. The configuration file for the script is located in the
"$HOME" directory of the UNIX account it is running under (i.e.
"oracle") and is named ".run_awr" (i.e. note the leading period in the
file name, to keep it "hidden"). This script uses the standard "oraenv"
shell script to set up the Oracle environment in which it runs.

The format of the ".run_awr" configuration file is described in the
header of the shell script.

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
website => http://www.EvDBT.com/
email => Tim-***@public.gmane.org
mobile => [phone]
fax => [phone]
Lost Data? => http://www.ora600.be/ for info about DUDE...

Post by Sandra Becker
Platform: IBM z9
OS: SUSE10
Oracle: 10gR2
Management is under the mistaken impression that the performance
problems we are experiencing on a daily basis ALWAYS happen at the
same time. To that end, they want me to set up a shell script that
will run an AWR report daily for each of the two time periods they
believe the problems are occuring. I haven't been able to convince
them we should run reports for the times we KNOW we had problems. I
also haven't been able to locate a script to automatically run reports
for the specific time periods--I'm probably not entering the right
search criteria. Does anyone have such a script or can point me in
the right direction?
--
Sandy
Transzap, Inc.

[Dan Norris]

Read the comments in the top of the $OH/rdbms/admin/awrrpti.sql script. It
tells you exactly how to set the proper variables so that you can call it
without prompting. From there, you can automate it as needed.

I've used this method to run AWR reports for each instance of a RAC
environment from a single location (instead of having to login to each
instance) because instance id is a variable you can pass in.

Dan

Post by Sandra Becker
Platform: IBM z9
OS: SUSE10
Oracle: 10gR2
Management is under the mistaken impression that the performance problems
we are experiencing on a daily basis ALWAYS happen at the same time. To
that end, they want me to set up a shell script that will run an AWR report
daily for each of the two time periods they believe the problems are
occuring. I haven't been able to convince them we should run reports for
the times we KNOW we had problems. I also haven't been able to locate a
script to automatically run reports for the specific time periods--I'm
probably not entering the right search criteria. Does anyone have such a
script or can point me in the right direction?
--
Sandy
Transzap, Inc.

[kyle Hailey]

Sandra,

What information are you trying to track in AWR report? What metrics do
you use or does your management use to evaluate performance? Performance can
be evaluated many different ways with AWR but the biggest issue is that AWR
report just averages out the metrics in the report period, which by default
is an hour. Running AWR faster than once an hour was deemed too resource
intensive to release as the default.
I find it much easier to use ASH for performance reporting. ASH tracks
the load in the database and can be rolled up in an aggregated metric called
Average Active Sessions (AAS). AAS can be broken down into it's components
such as CPU demand, IO wait time or other wait times such as application
(locking) , configuration (that kind of stuff the DBA is responsible for) or
concurrency bottlenecks to easily see not only what the load was but what
kind of load was on the database.
ASH and AAS can most easily seen in OEM's performance page. The
performance page works best for viewing the current activity including the
last hour as well but can be difficult to use for viewing a time frame
over the past day or week. For that reason I've been working on DB
Optimizer<http://sites.google.com/site/embtdbo/wait-event-documentation/database-tuning>
as well has putting together some SQL
scripts<http://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history#TOC-Data-Mining-Scripts>to
help quickly browse the ASH data and see any periods that might have
had
issues.

Best Wishes
Kyle Hailey
http://oraclemonitor.com

Post by Dan Norris
Read the comments in the top of the $OH/rdbms/admin/awrrpti.sql script. It
tells you exactly how to set the proper variables so that you can call it
without prompting. From there, you can automate it as needed.
I've used this method to run AWR reports for each instance of a RAC
environment from a single location (instead of having to login to each
instance) because instance id is a variable you can pass in.
Dan

Post by Sandra Becker
Platform: IBM z9
OS: SUSE10
Oracle: 10gR2
Management is under the mistaken impression that the performance problems
we are experiencing on a daily basis ALWAYS happen at the same time. To
that end, they want me to set up a shell script that will run an AWR report
daily for each of the two time periods they believe the problems are
occuring. I haven't been able to convince them we should run reports for
the times we KNOW we had problems. I also haven't been able to locate a
script to automatically run reports for the specific time periods--I'm
probably not entering the right search criteria. Does anyone have such a
script or can point me in the right direction?
--
Sandy
Transzap, Inc.

[girlgeek]

I see from the documentation that it is perfectly valid to write a
parallel hint with neither degree nor default.
For example:
select /* + parallel */ ename from emp;
(please pretend that emp is not a tiny table).

What will oracle use to calculate the degree of parallelism used when
the hint is constructed as above?

I have been unable to find the answer in the documentation.

Thank you,
Claudia
--
http://www.freelists.org/webpage/oracle-l

[Ric Van Dyke]

It uses the default degree of parallelism from the table. So if it's
not set at the table, you'll get a serial plan.

Ric Van Dyke
Hotsos Enterprises, Ltd.


-----Original Message-----
From: oracle-l-bounce-***@public.gmane.org
[mailto:oracle-l-bounce-***@public.gmane.org] On Behalf Of girlgeek
Sent: Thursday, March 04, 2010 2:48 AM
Cc: oracle-l
Subject: parallel hint

I see from the documentation that it is perfectly valid to write a
parallel hint with neither degree nor default.
For example:
select /* + parallel */ ename from emp;
(please pretend that emp is not a tiny table).

What will oracle use to calculate the degree of parallelism used when
the hint is constructed as above?

I have been unable to find the answer in the documentation.

Thank you,
Claudia
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

[girlgeek]

Ric,
Thank you for the explanation. I had always wondered about something
that I saw at a prior shop. Someone had used a parallel hint without
degree and managed to get 57 parallel processes running, (mostly
fighting) on a 12 cpu box. If the table was defined as parallel
without a degree, the documentation says that Oracle defines that the
degree of parallelism to use is the number of cpu times the value in the
init parameter parallel_threads_per_cpu. I didn't check at the time, but
I assume that they must have set the init parameter
parallel_threads_per_cpu to 4. There are a few extra processes not
explained by that logic, but I no longer have access to that particular
box to research further.

Thanks again,
Claudia

Post by Ric Van Dyke
It uses the default degree of parallelism from the table. So if it's
not set at the table, you'll get a serial plan.
Ric Van Dyke
Hotsos Enterprises, Ltd.
-----Original Message-----
Sent: Thursday, March 04, 2010 2:48 AM
Cc: oracle-l
Subject: parallel hint
I see from the documentation that it is perfectly valid to write a
parallel hint with neither degree nor default.
select /* + parallel */ ename from emp;
(please pretend that emp is not a tiny table).
What will oracle use to calculate the degree of parallelism used when
the hint is constructed as above?
I have been unable to find the answer in the documentation.
Thank you,
Claudia
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

[Greg Rahn]

The "default" DOP is cpu_count * parallel_threads_per_cpu, however if
the execution plan has both producer/consumer slave sets, it will use
2 * DOP parallel execution servers.

For example, with cpu_count=12 and parallel_threads_per_cpu=2, the
default DOP would be 12 * 2 = 24, however the query could use 48
parallel execution servers depending on the execution plan.

Ric,
Thank you for the explanation.  I had always wondered about something that I
saw at a prior shop.  Someone had used a  parallel hint without degree and
managed to get 57 parallel processes running, (mostly fighting) on a 12 cpu
box.   If the table was defined as parallel without a degree, the
documentation says that Oracle defines that the degree of parallelism to use
is the number of cpu times the value in the init parameter
parallel_threads_per_cpu. I didn't check at the time, but I assume that they
must have set the init parameter parallel_threads_per_cpu to 4.  There are a
few extra processes not explained by that logic, but I no longer have access
to that particular box to research further.
Thanks again,
Claudia

[Cholakov, Atanas Stoianov]

Hi All,

I need to connect my Oracle DB to MS SQL server.

I also need to have full rights (insert, update and delete) on a table
there!

Can you please share your experience?

Cheers,
Atanas

[Yechiel Adar]

Check out heterogeneous services.

Adar Yechiel
Rechovot, Israel

Post by Cholakov, Atanas Stoianov
Hi All,
I need to connect my Oracle DB to MS SQL server.
I also need to have full rights (insert, update and delete) on a
table there!
Can you please share your experience?
Cheers,
Atanas

--
http://www.freelists.org/webpage/oracle-l

[Niall Litchfield]

or if you are talking 11g the new name for the old thing is database gateway
for ODBC.

Post by Yechiel Adar
Check out heterogeneous services.
Adar Yechiel
Rechovot, Israel

Post by Cholakov, Atanas Stoianov
Hi All,
I need to connect my Oracle DB to MS SQL server.
I also need to have full rights (insert, update and delete) on a table
there!
Can you please share your experience?
Cheers,
Atanas
--

http://www.freelists.org/webpage/oracle-l

[Cholakov, Atanas Stoianov]

Thank you all for the responses.
I decided to implement Oracle Database Gateway for MS SQL server
http://download.oracle.com/docs/cd/B28359_01/gateways.111/b31043/conf_sql.htm


Atanas

Post by Niall Litchfield
or if you are talking 11g the new name for the old thing is database
gateway for ODBC.
Check out heterogeneous services.
Adar Yechiel
Rechovot, Israel
Hi All,
I need to connect my Oracle DB to MS SQL server.
I also need to have full rights (insert, update and delete)
on a table there!
Can you please share your experience?
Cheers,
Atanas
--
http://www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info

[Karl Arao]

Hi Sandra,

You could validate if it's really happening at the same time (or range
of time) by having a time series value of your workload.. See the
script here

http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/

It shows the significant metrics that you see in AWR reports enough to
characterize the workload and tell that there's something going wrong
on that period. The report is divided into Capacity,Requirements,and
Utilization so you would also know if your server is running out of
resources or just waiting or having some bottlenecks (see AAS).

From there you'll detail on the right AWR reports instead of tiresome

generation of each, but yes you could automate it.
And also you'll hit the right drill down when you use ASH and
ADDM...And you could put the values on excel for visualization and for
pretty reports... :)




- Karl Arao
Blog: karlarao.wordpress.com
Wiki: karlarao.tiddlyspot.com
--
http://www.freelists.org/webpage/oracle-l

[Sandra Becker]

Thanks everyone. Looks like Tim's script will do what I want. I did go
back and get dates and times of the performance problems. Management was
off every time so they were not asking for the correct snapshots. They
insisted I get them anyway. I will be doing reports the ACTUAL periods we
had problems and making my decisiions based on those reports. They did
finally fix a critical lookup table I'd been complaining about for about 2
years now and our worst problems mysteriously vanished. We still have other
problems but I think we have enough head room now to do it right the first
time when we fix the problematic sql statements.

Sandy

[Ujang Jaenudin]

sandy,

I've written the same for simplicity....just pass parameters of numofdays,
tns, user, awr location
look at http://blog.dbs247.com/?p=29

Post by Sandra Becker
Thanks everyone. Looks like Tim's script will do what I want. I did go
back and get dates and times of the performance problems. Management was
off every time so they were not asking for the correct snapshots. They
insisted I get them anyway. I will be doing reports the ACTUAL periods we
had problems and making my decisiions based on those reports. They did
finally fix a critical lookup table I'd been complaining about for about 2
years now and our worst problems mysteriously vanished. We still have other
problems but I think we have enough head room now to do it right the first
time when we fix the problematic sql statements.
Sandy