Re: Re: Re: Re: AWR in OCI ATP
Date: Sat, 25 Jan 2020 17:45:54 +0100
Message-ID: <CALH8A91Hm5iPhuwdPaiuOV+p9QB59Ty2M1d4E9VFu64jT03NwA_at_mail.gmail.com>
You get the report as a cursor of (HTML) lines. to get it in a clob, please have a look at https://grokbase.com/t/freelists.org/oracle-l/12a4tswsd3/looking-for-thoughts-advice-on-awr-report-html-and-utl-smtp/oldest
hth,
Martin
Am Fr., 24. Jan. 2020 um 11:43 Uhr schrieb Arpit Aggarwal < arpitdba2019_at_gmail.com>:
> It comes in same format.
>
> On Fri, 24 Jan 2020 at 15:58, l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>
> wrote:
>
>> Hi,
>>
>> what happens if you store the result as HTML file (rather than ext) and
>> open it in a Browser?
>>
>> Regards
>>
>> Lothar
>>
>> ----Ursprüngliche Nachricht----
>> Von : arpitdba2019_at_gmail.com
>> Datum : 24/01/2020 - 10:56 (MN)
>> An : l.flatz_at_bluewin.ch
>> Cc : oracle-l_at_freelists.org
>> Betreff : Re: Re: Re: AWR in OCI ATP
>>
>> <p />Top 10 Foreground Events by Total Wait Time<p />
>> <ul>
>> </ul>
>> <table border="0" width="600" class="tdiff" summary="This table displays
>> top 10 wait events by total wait time"><tr><th class="awrbg"
>> scope="col">Event</th><th class="awrbg" scope="col">Waits</th><th
>> class="awrbg" scope="col">Total Wait Time (sec)</th><th class="awrbg"
>> scope="col">Avg Wait</th><th class="awrbg" scope="col">% DB time</th><th
>> class="awrbg" scope="col">Wait Class</th></tr>
>> <tr><td scope="row" class='awrc'>DB CPU</td><td align="right"
>> class='awrc'> </td><td align="right" class='awrc'>210.9</td><td
>> align="right" class='awrc'> </td><td align="right"
>> class='awrc'>55.7</td><td class='awrc'> </td></tr>
>> <tr><td scope="row" class='awrnc'>gcs drm freeze in enter server
>> mode</td><td align="right" class='awrnc'>1</td><td align="right"
>> class='awrnc'>126.2</td><td align="right" class='awrnc'>126.19 s</td><td
>> align="right" class='awrnc'>33.4</td><td class='awrnc'>Other</td></tr>
>> <tr><td scope="row" class='awrc'>enq: JG - queue lock</td><td
>> align="right" class='awrc'>15</td><td align="right"
>> class='awrc'>39.3</td><td align="right" class='awrc'>2618.18ms</td><td
>> align="right" class='awrc'>10.4</td><td class='awrc'>Other</td></tr>
>> <tr><td scope="row" class='awrnc'>SQL*Net message to client</td><td
>> align="right" class='awrnc'>62,104</td><td align="right"
>> class='awrnc'>33</td><td align="right" class='awrnc'>531.50us</td><td
>> align="right" class='awrnc'>8.7</td><td class='awrnc'>Network</td></tr>
>>
>>
>>
>> On Fri, 24 Jan 2020 at 15:10, l.flatz_at_bluewin.ch < l.flatz_at_bluewin.ch>
>> wrote:
>>
>>> Hi,
>>>
>>> thanks, that looks like the actual report is missing.
>>>
>>> Regards
>>>
>>> Lothar
>>>
>>> ----Ursprüngliche Nachricht----
>>> Von : arpitdba2019_at_gmail.com
>>> Datum : 24/01/2020 - 09:58 (MN)
>>> An : l.flatz_at_bluewin.ch
>>> Cc : oracle-l_at_freelists.org
>>> Betreff : Re: Re: AWR in OCI ATP
>>>
>>> Here you go :-
>>>
>>> OUTPUT
>>> <html lang="en"><head><title>AWR Report for DB: ***, Inst: ***, Snaps:
>>> 827-828</title>
>>> <style type="text/css">
>>> body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black;
>>> background:White;}
>>> pre.awr {font:8pt Courier;color:black; background:White;}
>>> pre_sqltext.awr {white-space: pre-wrap;}
>>> h1.awr {font:bold 20pt
>>> Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px
>>> solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
>>> h2.awr {font:bold 18pt
>>> Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt;
>>> margin-bottom:0pt;}
>>>
>>> On Fri, 24 Jan 2020 at 14:17, l.flatz_at_bluewin.ch < l.flatz_at_bluewin.ch>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> Ok, so now we know it works in priciple.
>>>> can you post a sample of the html format? The way you describe it it
>>>> could active html format, which I suppose it should not be.
>>>> You see this in sql monitor reports. It looks like that:
>>>>
>>>> <!--FXTMODEL-->
>>>> <report db_version="12.1.0.2.0" elapsed_time="2.12" cpu_time="0.06"
>>>> inst_count="2" cpu_cores="8" hyperthread="Y" timezone_offset="7200" exa="Y"
>>>> packs="2" encode="base64" compress="zlib">
>>>>
>>>> <report_id><![CDATA[/orarep/sqlmonitor/main?sql_exec_id=16777244&sql_id=3n7v97thw1wy5]]></report_id>
>>>>
>>>>
>>>> eAHtXFt32liWfq9fodHKVOyeTNAdKYVZhY1ScduBDODUpGrV0pJB2CSACBK59EP/
>>>> 9v72ObojCeGkqqtmkhdLR2ef697fvpJO8H7prPz1IvS3ztbb+NtQ+OBtg4W/PhO1
>>>> p5IoBJ+DmRt6Z6JktGSzpUiyJUjqM017pmpi9ztB6HAyZ+Nu3ZUXgpha0U5DL2Zd
>>>> dd3+YLXD+4/yx896pxW1pl28T96U+slGu91WNI13iVt5v9vd9J0XOlN/tw67cqeV
>>>>
>>>> ...
>>>>
>>>> and so on.
>>>>
>>>> Regards
>>>>
>>>> Lothar
>>>>
>>>> ----Ursprüngliche Nachricht----
>>>> Von : arpitdba2019_at_gmail.com
>>>> Datum : 24/01/2020 - 09:37 (MN)
>>>> An : nenad.noveljic_at_vontobel.com
>>>> Cc : salem.ghassan_at_gmail.com, oracle-l_at_freelists.org
>>>> Betreff : Re: AWR in OCI ATP
>>>>
>>>> Hi Nenad,
>>>>
>>>> Telnet also hangs after trying .
>>>>
>>>> _at_Lothar,
>>>>
>>>> Tried that option after your recommendation and exported the o/p in
>>>> text format, this looks much better to read and would be using this in case
>>>> I don't find any way to generate in html format.
>>>>
>>>>
>>>> Thanks,
>>>> Arpit
>>>>
>>>> On Fri, 24 Jan 2020 at 13:29, Noveljic Nenad <
>>>> nenad.noveljic_at_vontobel.com> wrote:
>>>>
>>>>> Hi Arpit
>>>>>
>>>>>
>>>>>
>>>>> Ø tmsping or sqlplus connection just hangs without giving any error
>>>>> message.
>>>>>
>>>>>
>>>>>
>>>>> Is the connection blocked by firewalls?
>>>>>
>>>>>
>>>>>
>>>>> You can check this with:
>>>>>
>>>>>
>>>>>
>>>>> telnet server port
>>>>>
>>>>>
>>>>>
>>>>> Best regards,
>>>>>
>>>>>
>>>>>
>>>>> Nenad
>>>>>
>>>>>
>>>>>
>>>>> https://nenadnoveljic.com/blog/
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> *From:*oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>>>> Behalf Of *Arpit Aggarwal
>>>>> *Sent:* Freitag, 24. Januar 2020 08:55
>>>>> *To:* Ghassan Salem <salem.ghassan_at_gmail.com>; oracle-l_at_freelists.org
>>>>> *Subject:* Re: AWR in OCI ATP
>>>>>
>>>>>
>>>>>
>>>>> Yes I have mentioned proxy host and port as well in sqlnet.ora file.
>>>>>
>>>>>
>>>>>
>>>>> Are there any other settings that needs to be done, tmsping or sqlplus
>>>>> connection just hangs without giving any error message.
>>>>>
>>>>>
>>>>>
>>>>> On Fri 24 Jan, 2020, 1:04 PM Ghassan Salem, <salem.ghassan_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> For option 2, are you behind a firewall? If so try setting the
>>>>> sqlnet.ora and tnsnames.ora to go through an http proxy.
>>>>>
>>>>>
>>>>>
>>>>> Rgds
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Jan 24, 2020, 08:11 Arpit Aggarwal <arpitdba2019_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>> Hi Friends,
>>>>>
>>>>>
>>>>>
>>>>> Has anyone tried generating a AWR report in OCI ATP database ,
>>>>> considering we don't have physical host access.
>>>>>
>>>>> I tried 2 options but had hit a blocker for both, if anyone else came
>>>>> across same, kindly assist.
>>>>>
>>>>>
>>>>>
>>>>> Option#1 ,
>>>>>
>>>>> I logged in to ATP sql developer as ADMIN user and executed below
>>>>> query to get a list of available snapshots,
>>>>>
>>>>> select SNAP_ID,DBID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from
>>>>> dba_hist_snapshot where INSTANCE_NUMBER=1 order by SNAP_ID ;
>>>>>
>>>>> And further executed below query to generate AWR in html format,
>>>>>
>>>>> SELECT output FROM TABLE
>>>>> (dbms_workload_repository.awr_report_html(&dbid,&instnum,&beginsnap,&endsnap
>>>>> ));
>>>>>
>>>>> The only problem with above was that the resulted AWR html report when
>>>>> exported from sql developer does not converts into an easy readable format.
>>>>>
>>>>> Option#2,
>>>>>
>>>>> The other approach that I tried is by connecting to ATP database from
>>>>> my local machine but for some reasons the ATP host is not resolvable from
>>>>> my local machine though I have modified sqlnet.ora and tnsnames.ora
>>>>> accordingly.
>>>>>
>>>>> The benefit of this approach would be that we can just spool the html
>>>>> o/p of above query of AWR repository and can view it easily on our local
>>>>> machine.
>>>>>
>>>>> Reference -
>>>>> https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-preparing.html#GUID-6F118C83-5BD2-4C10-88F2-B7A6280D1EE1
>>>>>
>>>>> https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/connect-sqlplus.html#GUID-A3005A6E-9ECF-40CB-8EFC-D1CFF664EC5A
>>>>>
>>>>> My tnsnames.ora and sqlnet.ora has been modified based on oracle doc.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Arpit
>>>>>
>>>>> ____________________________________________________
>>>>>
>>>>> Please consider the environment before printing this e-mail.
>>>>>
>>>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>>>
>>>>>
>>>>> Important Notice
>>>>> This message is intended only for the individual named. It may contain
>>>>> confidential or privileged information. If you are not the named addressee
>>>>> you should in particular not disseminate, distribute, modify or copy this
>>>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>>>> received this message by mistake and delete it from your system.
>>>>> Without prejudice to any contractual agreements between you and us
>>>>> which shall prevail in any case, we take it as your authorization to
>>>>> correspond with you by e-mail if you send us messages by e-mail. However,
>>>>> we reserve the right not to execute orders and instructions transmitted by
>>>>> e-mail at any time and without further explanation.
>>>>> E-mail transmission may not be secure or error-free as information
>>>>> could be intercepted, corrupted, lost, destroyed, arrive late or
>>>>> incomplete. Also processing of incoming e-mails cannot be guaranteed. All
>>>>> liability of Vontobel Holding Ltd. and any of its affiliates (hereinafter
>>>>> collectively referred to as "Vontobel Group") for any damages resulting
>>>>> from e-mail use is excluded. You are advised that urgent and time sensitive
>>>>> messages should not be sent by e-mail and if verification is required
>>>>> please request a printed version. Please note that all e-mail
>>>>> communications to and from the Vontobel Group are subject to electronic
>>>>> storage and review by Vontobel Group. Unless stated to the contrary and
>>>>> without prejudice to any contractual agreements between you and Vontobel
>>>>> Group which shall prevail in any case, e-mail-communication is for
>>>>> informational purposes only and is not intended as an offer or solicitation
>>>>> for the purchase or sale of any financial instrument or as an official
>>>>> confirmation of any transaction.
>>>>> The legal basis for the processing of your personal data is the
>>>>> legitimate interest to develop a commercial relationship with you, as well
>>>>> as your consent to forward you commercial communications. You can exercise,
>>>>> at any time and under the terms established under current regulation, your
>>>>> rights. If you prefer not to receive any further communications, please
>>>>> contact your client relationship manager if you are a client of Vontobel
>>>>> Group or notify the sender. Please note for an exact reference to the
>>>>> affected group entity the corporate e-mail signature. For further
>>>>> information about data privacy at Vontobel Group please consult
>>>>> www.vontobel.com.
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 25 2020 - 17:45:54 CET