Re: sql_text during parsing

It should be in x$kglob even if it's not visible in v$sql etc. Off-hand I think the sql_id maps from KGLOBT03, and from a very recent question on the MOSC forum KQLFNOBJ is the CLOB version of the sql_text, so:

select KQLFNOBJ from x$kglob where KGLOBT03 = ' 99g4t6h9kfumw' and kglhdpar = kglhdadr;

(the last predicate gives the parent rather than parent and child ).

Regards
Jonathan Lewis

On Mon, 12 Jul 2021 at 16:02, Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> An unknown query is parsing for hours and allocates GBytes in PGA:
>
>
>
> SQL> _at_ashtop program2,sql_id "program like 'tab%' and in_parse='Y'" &today
>
>
>
>
> Total
> Distinct
>
> Seconds AAS %This PROGRAM2
> SQL_ID FIRST_SEEN LAST_SEEN Execs Seen
>
> --------- ------- ------- ----------------------------------------
> ------------- ------------------- ------------------- ----------
>
> 17905 .3 97% | (tabprotosrv.exe)
> 99g4t6h9kfumw 2021-07-12 09:28:44 2021-07-12 14:59:10 1
>
>
>
> PGA breakdown for the session:
>
>
>
> PRIVATE HEAP SUMMARY DUMP
>
> 3399 MB total:
>
> 3391 MB commented, 564 KB permanent
>
> 7023 KB free (0 KB in empty extents),
>
> 2779 MB, 2 heaps: "callheap " 2031 KB free held
>
> 581 MB, 1 heap: "kxs-heap-c " 3989 KB free held
>
> ------------------------------------------------------
>
> Summary of subheaps at depth 1
>
> 3391 MB total:
>
> 2814 MB commented, 577 MB permanent
>
> 170 KB free (0 KB in empty extents),
>
> 2777 MB, 1 heap: "TCHK^13276a7c " 16 KB free held
>
>
>
> *** 2021-07-12T14:59:01.897986+02:00
>
> ------------------------------------------------------
>
> Summary of subheaps at depth 2
>
> 2811 MB total:
>
> 2778 MB commented, 33 MB permanent
>
> 28 KB free (0 KB in empty extents),
>
> 625 MB, 4803573 chunks: "optdef: qcopCreateOptInter"
>
> 520 MB, 3096283 chunks: "coldef: qcopCreateCol "
>
> 379 MB, 6792406 chunks: "idndef : qcuAllocIdn "
>
> 373 MB, 3256090 chunks: "strdef: qcopCreateStr "
>
> 288 MB, 3096282 chunks: "prstxt:qcpiStoreParseText "
>
> 213 MB, 3096282 chunks: "jsoncol:qcpistoreparsetext"
>
> 118 MB, 3096282 chunks: "chedef : qcuatc "
>
>
>
>
>
> There are no entries in v$sql and v$sqarea.
>
>
>
> The query doesn’t appear in SQL trace either.
>
>
>
> Any ideas how to get the SQL text?
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
> ____________________________________________________
>
> 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