Re: Sqlid details inside Procedure

Hi Sayan, I am seeing the plsql_object_id/plsql_subprogram_id column populated as null in this case. It's a third party app and I do see the program_id in the v$sql pointing to correct immediate procedure calls. And in this case i am seeing proc_3 which is the top parent , is calling proc_2 from a dynamic sql (which fetches its name/proc_2 and parameters list from a setup table etc) and then proc_2 calls proc_1. So it means Proc_3 can call proc_5, proc_6.. proc_7 etc from within based on certain input criteria and their parameters from setup tables. Is this the cause why ASH is not showing the immediate procedure call?

Basically my concern was, if we are trying to fetch/analyze the historical performance data from dba_hist_active_sess_history and the sql_id is not available in v$sql , how we would be able to get the immediate procedure call from dba_hist_active_sess_histroy in above scenario?. As because that really helps to group all the ASH samples and see what are the top resources consuming sqls in that procedure.

On Tue, Feb 8, 2022 at 2:08 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Pap,
>
> Have you checked v$sql.program_id and v$sql.program_line# from v$sql? and
> plsql_object_id/plsql_subprogram_id from ASH?
>
>
> On Mon, Feb 7, 2022 at 11:11 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> One related doubt I have, say we have proc_1, called from proc_2, which
>> is called from Proc_3. Now I see in dba_hist_active_sess_history, all the
>> sqls executed inside proc_1 are showing the top_level_sql_id as PROC_3
>> only. even plsql_entry_object_id and plsql_entry_subprogram_id are
>> pointing to the same Proc_3. Is there any way to track the exact immediate
>> top program/procedure name for the sql_id from ASH(which in this case
>> should be shown as proc_1)?
>>
>> On Mon, Feb 7, 2022 at 5:24 PM Krishnaprasad Yadav <
>> chrishna0007_at_gmail.com> wrote:
>>
>>> 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.
>>>>
>>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE
> http://orasql.org
>

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