RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Oct 2012 08:32:38 -0500
I'll give that a try - I was thinking about it and I believe it's the
awr_report_html function that is taking some time to return and not the actual
building of the CLOB.
Thanks again!
Chris
-----Original Message-----
From: Administrator User [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Friday, October 05, 2012 1:34 AM
To: Taylor Christopher - Nashville; oracle-l@xxxxxxxxxxxxx
Subject: RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
Just had a simple thought.
If most of the excessive is due to concatenating with a CLOB for each line of
the report, concatenate with a varchar2() variable - which can get to 32KB in
pl/sql. Then concatenate the varchar2() with the CLOB every few hundred times
round the loop - you might guess at 400 times (assuming 80 character strings),
you might check the length of each string and keep a running total as you take
each string out of the cursor.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Administrator User [jonathan@xxxxxxxxxxxxxxxxxx]
Sent: 05 October 2012 07:06
To: Christopher.Taylor2@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
You didn't say which version of Oracle, and that could be relevant.
I ran your select statement from SQL*Plus, and then called your procedure from
SQL*Plus. In 11.1.0.7 the change in "db block gets" (to pick just one of the
more obvious stats) was dramatic, so it's possible that there's some feature of
the simple concatenation of LOBs that makes a difference.
I wrote something about a similar anomaly a few years ago, although the
strategy for improvement doesn't help:
http://jonathanlewis.wordpress.com/2009/07/09/concatenating-lobs/
You might try explicit use of dbms_lob.create_temporary_lob (with the cache
attribute) and dbms_lob.wrte_append() in case it makes a difference - but I
suspect that's what your code is doing under the covers anyway.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Christopher.Taylor2@xxxxxxxxxxxx [Christopher.Taylor2@xxxxxxxxxxxx]
Sent: 04 October 2012 21:04
To: oracle-l@xxxxxxxxxxxxx
Subject: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
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;
/
--
//www.freelists.org/webpage/oracle-l--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
