Re: sql_text during parsing

Sayan,

Coincidentally your statement is 20 characters - and it's possible that that's also the limit of the number of characters that could be dumped. It seems that it might be the limit for invalid SQL getting into x$kglob.

Regards
Jonathan Lewis

On Mon, 12 Jul 2021 at 18:40, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Nenad,
>
> I've just ran short test with ORA-00936: missing expression:
> SQL> select () from dual
> *
> ERROR at line 1:
> ORA-00936: missing expression
>
> Test case:
> alter session set tracefile_identifier='compiler';
> alter session set events 'trace [*SQL_Compiler*.*] disk=high
> errorstack(2)';
> select () from dual;
> disc
>
>
> And got it in the trace file:
> # grep -A2 -B5 "Parsing cur" *compiler*
> ts:1626111216[07/12/2021 17:33:36] [1000:55926] type:3 [MASTER UPD
> MIN-SCN] inst#:0 inc#:0 min-scn: 0x0000000000414df9 global-recent-scn:
> 0x000000000068c2a4 calc-scn: 0x000000000068c2a5
> grec-scn: 0x000000000068c2a4 gmin-scn: 0x0000000000414df9 calc-scn:
> 0x000000000068c2a5
> scn-map not available - probably slave
> End Dumping Min-Act Trace
> ----- Pluggagle DB Traces -----
> Parsing cur#=5 sqlid=0g4um7fjk6mg4 len=20
> sql=select () from dual
>
>
> So at least we can be sure that parse errors are traced by "SQL_Compiler"
>
> On Mon, Jul 12, 2021 at 8:31 PM Mohamed Houri <mohamed.houri_at_gmail.com>
> wrote:
>
>> Nenad
>>
>> In a case similar to yours I was saved by to *dba_hist_sqltext* :-)
>>
>> select sql_text from dba_hist_sqltext where sql_id = '6tcs65pchhp71';
>>
>> https://hourim.wordpress.com/2018/03/17/library-cache-lock/
>>
>> Best regards
>> Mohamed
>>
>>
>>
>> Le lun. 12 juil. 2021 à 19:20, Noveljic Nenad <
>> nenad.noveljic_at_vontobel.com> a écrit :
>>
>>> This statement could be considered invalid because it failed parsing
>>> (due to memory allocation error).
>>>
>>>
>>>
>>> The same text (~ 20 characters long) was shown in v$sqlarea.text as well.
>>>
>>>
>>>
>>> Interestingly, oradebug current_sql shows a much longer text. It’s
>>> possible that this is the unparsed text – the Oracle dedicated server
>>> process has the full text all the time, but it’s just not externalized
>>> until the parsing is over. Unfortunately, this text is trimmed – maybe by
>>> the oradebug presentation layer code.
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>> Behalf Of *Jonathan Lewis
>>> *Sent:* Montag, 12. Juli 2021 19:08
>>> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>> *Subject:* Re: sql_text during parsing
>>>
>>>
>>>
>>>
>>>
>>> I was just looking back at a note I wrote a few years ago (
>>> https://jonathanlewis.wordpress.com/2017/10/03/parsing ) about invalid
>>> SQL getting into the library cache (x$kglob) even though not being made
>>> visible through v$sql; and I've only just noticed that the content was
>>> trimmed to the first 20 charactes - which is the same as the fragment that
>>> you found with the query.I suggested. So now I'm wondering whether it's
>>> just a small chunk acting as a place-holder in the library cache, until the
>>> parse is complete - although this comment from Liron Amitzi on the blog
>>> note suggests it might be the whole thing:
>>> https://jonathanlewis.wordpress.com/2017/10/03/parsing/#comment-101173
>>>
>>>
>>>
>>>
>>>
>>> Regards
>>>
>>> Jonathan Lewis
>>>
>>>
>>>
>>>
>>>
>>> On Mon, 12 Jul 2021 at 17:52, Noveljic Nenad <
>>> nenad.noveljic_at_vontobel.com> wrote:
>>>
>>> A combination of Andy's and Jonathan's ideas to try tomorrow:
>>>
>>> set long 10000
>>> oradebug setospid ...
>>> oradebug current_sql
>>>
>>> Best regards,
>>>
>>> Nenad
>>>
>>> -----Original Message-----
>>> From: Noveljic Nenad
>>> Sent: Montag, 12. Juli 2021 18:35
>>> To: 'Stefan Koehler' <contact_at_soocs.de>; ORACLE-L (
>>> oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>> Subject: RE: sql_text during parsing
>>>
>>> Hi Stefan,
>>>
>>> I suspended the process during parsing and got the process dump and
>>> error stack trace, but nothing.
>>>
>>> My guess is that the statement is properly registered in the memory
>>> (from the Oracle C code perspective) only after the parsing.
>>>
>>> Unless someone comes up with a better idea, I'll ask Tableau people
>>> tomorrow if they could trace generated SQLs on the client.
>>>
>>> Otherwise, I have to look for SQL text in the process private memory. In
>>> 12c, the second argument to the first qcplgte call (there are many
>>> recursive calls to this function) contained the pointer to a structure
>>> which contained the pointer to SQL text. I'll start from there.
>>>
>>> Best regards,
>>>
>>> Nenad
>>>
>>>
>>> -----Original Message-----
>>> From: Stefan Koehler <contact_at_soocs.de>
>>> Sent: Montag, 12. Juli 2021 17:28
>>> To: Noveljic Nenad <nenad.noveljic_at_vontobel.com>; ORACLE-L (
>>> oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>> Subject: RE: sql_text during parsing
>>>
>>> Hello Nenad,
>>> any chance with a process state dump (or error stack trace)?
>>>
>>> Best Regards
>>> Stefan Koehler
>>>
>>> Independent Oracle performance consultant and researcher
>>> Website: http://www.soocs.de
>>> Twitter: _at_OracleSK<
>>>
>>> > Noveljic Nenad <nenad.noveljic_at_vontobel.com> hat am 12.07.2021 17:25
>>> geschrieben:
>>> >
>>> > Thanks!
>>> >
>>> > I got a part of SQL (it’s chopped)
>>> >
>>> > Any idea for getting the full text?
>>> >
>>> > SQL> oradebug setospid 17876
>>> > Oracle pid: 107, Unix process pid: 17876, image: oracle_at_svdbp02p
>>> > SQL> oradebug current_sql
>>> > SELECT (CASE WHEN ((CASE WHEN (CASE WHEN (CASE WHEN (CASE WHEN (CASE
>>> WHEN (CASE WHEN (CASE WHEN (CASE WHEN ((CASE WHEN ((CASE WHEN (((CASE WHEN …
>>> >
>>> > The problem might be with the parsing of a nested case.
>>> >
>>> > Have to get the full text to experiment.
>>> ____________________________________________________
>>> 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 <https://www.vontobel.com>.
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>> 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.
>>>
>>
>>
>> --
>>
>> Houri Mohamed
>>
>> Oracle DBA-Developer-Performance & Tuning
>>
>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>> <https://twitter.com/MohamedHouri>
>>
>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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