Re: Sqlid details inside Procedure

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