RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Fri, 5 Oct 2012 18:40:27 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB401043FB122_at_LITIGMBCRP02.Corp.Acxiom.net>

Chris,

Does all processing have to reside within the database or are OS scripts possible? If so, then you could use the following:

Put this block of code in a script:

---

SET FEEDBACK off HEADING off LINESIZE 1500

VARIABLE DBID NUMBER;
BEGIN
   SELECT dbid INTO :DBID FROM v$database; END;
/

SET ECHO on
DEFINE INSTANCE_ID=&1;
DEFINE BEGIN_DATETIME='&2';
DEFINE END_DATETIME='&3';
SET ECHO off

SELECT *
  FROM TABLE(dbms_workload_repository.awr_report_html(

             (:DBID), &INSTANCE_ID,
             (SELECT MIN(LEAST(snap_id))
                FROM dba_hist_snapshot
               WHERE end_interval_time >=
                     TO_DATE('&BEGIN_DATETIME', 'DD-MON-YY HH24:MI:SS')
                 AND dbid = :DBID),
             (SELECT MAX(GREATEST(snap_id))
                FROM dba_hist_snapshot
               WHERE end_interval_time <=
                     TO_DATE('&END_DATETIME', 'DD-MON-YY HH24:MI:SS')
                 AND dbid = :DBID), 8));

EXIT

---

Execute the script using:

echo "connect <user>/<pass>
_at_<script from above> $inst $begindt $enddt exit" | sqlplus -s /nolog | uuencode awr_daily_rpt.html | mailx -s "Chris's daily AWR report" <chris's email addr>

DAVID HERRING
DBA
Acxiom Corporation

EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM
-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Thursday, October 04, 2012 9:04 PM To: dbaprimatics_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

Right, but I'm curious about doing it in the database :) I've got it working well minus the few seconds it takes to generate the clob and attach it.

Chris

From: Vamshi Damidi [mailto:dbaprimatics_at_gmail.com] Sent: Thursday, October 04, 2012 7:27 PM To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

Hi Chris,

Probably shell script should work fine along with mutt for attachments as email client since awr report can be spooled to html file.

Thanks,
Vamshi .D
On Thu, Oct 4, 2012 at 4:04 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: I figured out how to send attachments using UTL_SMTP (from code found on the web) and the code I'm using uses a clob variable to build the attachment. So I pieced together a function to build a clob from DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML and call that function from my mail procedure.

This works surprisingly well to email me a daily AWR report (for a specific window of time - nightly batch cycle) *yet* the performance is slow to build the clob.

I had looked for a way to read the AWR_REPORT_HTML results directly into an attachment but I couldn't figure it out (being very non-proficient in pl/sql) so I was wondering if there was a better way to do this? (To capture the results of AWR_REPORT_HTML and mail them as an attachment).

I was thinking I should probably use a bulk collect here perhaps? Or am I overthinking this?

Here's my clob function (just for proof of concept):

CREATE OR REPLACE FUNCTION html_clob

   RETURN CLOB
IS

   p_attach_clob CLOB := NULL;

   CURSOR c1
   IS

      SELECT
             output
      FROM
             TABLE (sys.DBMS_WORKLOAD_REPOSITORY.awr_report_html (99999999,
                                                                  1,
                                                                  20003,
                                                                  20011)); BEGIN

   FOR rec IN c1
   LOOP
      p_attach_clob := p_attach_clob ||chr(13)||chr(10)|| rec.output;    END LOOP;    RETURN p_attach_clob;
END;
/

--

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

--

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

---

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

---

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 05 2012 - 20:40:27 CEST