Re: sql_text during parsing

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