Re: Re: Re: Re: AWR in OCI ATP
Date: Sat, 25 Jan 2020 22:29:24 +0530
Message-ID: <CAOrSwEqnMK5Jn+0fbmbnJdLrzvpP8r7DsC0LkqOCi3dghSndOw_at_mail.gmail.com>
Let me try this out if it works.
On Sat, 25 Jan 2020 at 22:16, Martin Berger <martin.a.berger_at_gmail.com> wrote:
>
> 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:59:24 CET