Re: Sqlid details inside Procedure

Hi All,
Thanks for your reply , will try out the recommendations.

Regards,
Krishna

On Mon, 7 Feb 2022, 13:56 Noveljic Nenad, <nenad.noveljic_at_vontobel.com> wrote:

> Hi Krishna
>
>
>
> When identifying SQL which takes most of the time in PLSQL, I would leave
> the column current_obj# out, that is:
>
>
>
> The problem with including current_obj# is that a query that takes lots of
> time, but that time is spent on accessing a large number of tables and
> indexes, wouldn’t show up at the top, like it’s the case with 1dckrmkg3rpqu.
> 1dckrmkg3rpqu seems to be the query of interest.
>
>
>
> I would use PLSQL profiler only when there’s a substantial number of
> samples where sql_id=top_level_sql_id, i.e. the time is spent in PL/SQL
> code, unlike on SQL.
>
>
>
> By the way, for the sake of efficiency, I recommend using Tanel Poder’s
> ashtop and dashtop for aggregations on v$active_session_history and
> dba_hist_active_sess_history, respectively. Notice that
> v$active_session_history has higher resolution than
> dba_hist_active_sess_history.
>
>
>
> The ashtop equivalent of your query (without current_obj#) is:
>
>
>
> _at_ashtop sql_id top_level_sql_id='8r602jwaxp6fm' "to_date('2022-02-04
> 11:00:00','yyyy-mm-dd hh24:mi:ss')" "to_date('2022-02-04
> 12:0:00','yyyy-mm-dd hh24:mi:ss')"
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *Andy Sayer
> *Sent:* Montag, 7. Februar 2022 08:56
> *To:* chrishna0007_at_gmail.com
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *Subject:* Re: Sqlid details inside Procedure
>
>
>
> **** E-Mail from outside Vontobel:* Do not click on links or open
> attachments unless you know the content is safe. ***
>
> Hi Krishna,
>
>
>
> Get the object_id of the procedure and look at rows in v$sql where the
> program_id is this.
>
>
>
> If you order by elapsed time you can see the SQLs that are most
> responsible for the execution time.
>
>
>
> Don’t forget, if your procedure is doing any processing, that could also
> take some time. The hierarchical plsql profiler is ideal for checking this
> out. It takes a little bit of setup (much easier in the latest versions)
> but will give you some useful information.
>
>
>
> Hope this helps,
>
> Andy
>
>
>
> On Mon, 7 Feb 2022 at 04:05, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
> Hi Experts ,
>
>
>
> Currently there is a situation , were we are looking for alternative to
> get Top expensive sqlid details from execution procedure .
>
> we tried to trace down(i.e 10046 trace with level 12) the procedure with
> sqlid of it , but we see spike occurring in oem .
>
> we are trying to get sqlid by dba_hist_active_sess_history ,but we are
> unable to get top sql contributions in procedure since it just shows
> the random sqlid , we are looking to top sql present in procedure .
>
> Below is output of what we have tried :
>
> SQL> select count(0), sql_id, CURRENT_OBJ#
> from dba_hist_active_sess_history
> where TOP_LEVEL_SQL_ID='8r602jwaxp6fm'
> and
> sample_time between
> to_date('2022-02-04 11:00:00','yyyy-mm-dd hh24:mi:ss')
> and
> to_date('2022-02-04 12:0:00','yyyy-mm-dd hh24:mi:ss')
> group by sql_id, CURRENT_OBJ#
> order by 1 desc;
>
>
> COUNT(0) SQL_ID CURRENT_OBJ#
> ---------- ------------- ------------
> 112 8r602jwaxp6fm -1
> 60 8r602jwaxp6fm 639493
> 56 c6quqbusshg6a -1
> 54 5nv8stdv9wnxa 639493
> 46 gydh9m5b3jkkm 639493
> 41 gydh9m5b3jkkm -1
> 40 -1
> 39 1dckrmkg3rpqu 3156305
> 36 fu01n34dw54wn -1
> 34 1dckrmkg3rpqu 3357339
> 34 1dckrmkg3rpqu 3221186
> 33 1dckrmkg3rpqu -1
> 31 1dckrmkg3rpqu 3031763
> 29 1dckrmkg3rpqu 2965174
> 28 1dckrmkg3rpqu 2713166
> 28 1dckrmkg3rpqu 3293309
> 28 5nv8stdv9wnxa 642284
> 26 8r602jwaxp6fm 642284
> 26 frq0qj978uqnt -1
> 26 1dckrmkg3rpqu 2895871
> 26 1dckrmkg3rpqu 2895862
> 24 1dckrmkg3rpqu 1830648
> 24 639493
> 22 fu01n34dw54wn 3548953
> 21 363anj011mq2t 639455
> 21 1dckrmkg3rpqu 2521114
> 20 1dckrmkg3rpqu 3422360
> 20 b9p3mvgfu51nr -1
>
>
>
> Regards,
>
> Krishna
>
>
>
> ____________________________________________________
>
> 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-l