Oracle Scratchpad

October 3, 2017

Parsing

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 4:15 pm GMT Oct 3,2017

Here’s a quick quiz.

According to the Oracle 12.1 Database SQL Tuning Guide the first stage of parsing a statement is the Syntax Check, which is followed by the Semantic Check, followed by the Shared Pool Check. So where you do think the statement text will be while the Syntax Check is going on ?

 

 

 

 

 

 

And the answer looks like ….

 

 

 

… the shared pool. Here’s a simple test, cut-n-paste from SQL*Plus running under 12.1.0.2 in the SYS schema (I’ve also done this in the past with older versions):

 

 

 


SQL> select user frrom dual;
select user frrom dual
                  *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like '%frrom%' and kglnaobj not like '%kgl%';

KGLHDPAR         KGLHDADR         KGLOBT03      KGLNAOBJ
---------------- ---------------- ------------- --------------------------------------------------------------------------------
00000000D1EE3880 00000000AEF43F40 bshhvh0ypcz6x select user frrom du
00000000D1EE3880 00000000D1EE3880 bshhvh0ypcz6x select user frrom du

2 rows selected.

SQL> 

So the statement is garbage and fails (or ought to be failing) on a syntax text – but I can find it in x$kglob – the library cache objects – in the SGA with a parent and child entry.

I have to say that when I first read, many years ago, that there was a syntax check it struck me that the fastest way of doing a syntax check on a “good” system would be to start with a search for the text in the library cache. If it were there (and on a good system it probably would be within a few minutes of startup) then you could have a flag that avoided the CPU cost of doing the syntax check and move straight on to the semantic (basically object and permissions) check.

 

23 Comments »

  1. Somewhere on the client side?

    Comment by Valentin Nikotin — October 3, 2017 @ 4:24 pm GMT Oct 3,2017 | Reply

    • To clarify: some 12c features doesn’t work with 11g client, that means that some checks are definitely performed by client. I wouldn’t state that ALL syntax checks are client responsibility though.

      Comment by Valentin Nikotin — October 3, 2017 @ 7:01 pm GMT Oct 3,2017 | Reply

  2. Private process memory? memcpy calls during the second stage are the ones who copy the text from private process memory to shared memory?

    Comment by Nenad Noveljic (@NenadNoveljic) — October 3, 2017 @ 4:35 pm GMT Oct 3,2017 | Reply

  3. With the private process memory I mean heap memory.

    Comment by Nenad Noveljic (@NenadNoveljic) — October 3, 2017 @ 4:41 pm GMT Oct 3,2017 | Reply

  4. It seems can’t be in SGA initially.
    1. You need to keep stuff somewhere before you calculate MD5 hash (sql_id)
    2. To manipulate library cache (even look up) you need to take library cache latch/pin which is going much much later.

    Comment by Dmitry Remizov — October 3, 2017 @ 4:48 pm GMT Oct 3,2017 | Reply

  5. in PGA, in private SQL area

    Comment by Dmitry L. — October 3, 2017 @ 6:45 pm GMT Oct 3,2017 | Reply

  6. My first suspicion would be the SQL text would first be found in PGA. I would try and test that, but am not able to do so at is time.

    Comment by jkstill — October 3, 2017 @ 7:36 pm GMT Oct 3,2017 | Reply

  7. Hi Jonathan,

    true, even cursor with syntactic errors can be found in x$kglob. However, the client is for sure doing some lexical analysis and tokenization. as suggested here by somenone, for example it was not possigle to issue DROP TABLE xyz PURGE from 9i client against 10g database

    Comment by Pavol Babel — October 3, 2017 @ 9:03 pm GMT Oct 3,2017 | Reply

    • Pavol,

      I should have been more precise with my question. DDL is generally not shared SQL (the exception is parallel CTAS, and I’d have to check parallel create index) so it’s easy to imagine that there might be a completely different path to the code that bypasses the library cache for the initial syntax check. I’ll have to check, some time, whether the query of a CTAS gets pushed into x$kglob.

      Comment by Jonathan Lewis — October 4, 2017 @ 8:25 am GMT Oct 4,2017 | Reply

      • Hi Jonathan,

        Here is one more interesting case:

        DECLARE 
         c integer;
        BEGIN
            c:= dbms_sql.open_cursor;
            dbms_sql.parse(c=>c,
                                   statement=>'INSERT INTO YY VALUESSS (1)',
                                   language_flag=>dbms_sql.native);                           
        END;
        /  
        
        select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like 'INSERT INTO YY VALUES%' and kglnaobj not like '%kgl%' and kglnaobj not like '%DECLARE%'; 
        
        -- nothing, but we did syntax check for sure
        
        DECLARE 
         c integer;
        BEGIN
            c:= dbms_sql.open_cursor;
            dbms_sql.parse(c=>c,
                                   statement=>'INSERT INTO YY VALUES (1)',
                                   language_flag=>dbms_sql.native); 
        END;
        /  
        
        select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like 'INSERT INTO YY VALUES%' and kglnaobj not like '%kgl%' and kglnaobj not like '%DECLARE%';
        
        

        Comment by Dmitry Remizov — October 4, 2017 @ 10:23 am GMT Oct 4,2017 | Reply

        • Dmitry,

          Thanks for that, it’s interesting to see how many variations on a theme show up when you start poking at a detail.

          You probably know that the PL/SQL engine has a separate parser from the SQL engine – we only have to remember the cases when a new SQL feature has appeared but caused an error to be raised when embedded in PL/SQL to realise that. We also know that there are other oddities – like dbms_sql.parse executes the statement if the statement is DDL – so who knows how much of a special case Oracle makes of your example ;) (and I’d guess it’s running as SYS to get at the x$ – and that often gets all sorts of special treatment).

          Comment by Jonathan Lewis — October 4, 2017 @ 11:13 am GMT Oct 4,2017

      • Jonathan,

        even in case of Query statement client performs some basic lexical analysis. If you type in sqlplus by mistake SLECT instead of SELECT, sqlplus for sure would not even try to send this statemnt to datbase server. It has to find begin and end of the SQL statement. so it has to recognize many keywords as SELECT, WITH (i think it was also not use subquery factoring by an old sqlplus against oralce 9i), CRETATE, GRANT, DROP … any many many others. However, I agree most of real syntax checks (from the point of comipler stuff) is done on database side inside shared pool.

        Comment by Pavol Babel — October 4, 2017 @ 11:50 pm GMT Oct 4,2017 | Reply

        • Hi Jonathan, Pavol,

          It seems that the very first token should have a special meaning, Oracle can decide whether it is DML or DDL or whatever.
          I came up to my example trying to reproduce almost the same case as Pavol mentioned.

          EELECT 1 from dual;

          which can be easily sent to server by JDBC layer (not cached for sure in x$kglob) but not by sqlplus (SP-xxxx error).

          Comment by dmitryremizov — October 5, 2017 @ 7:13 am GMT Oct 5,2017

        • Dmitry, Pavol,

          Basic tokenisation is another possibility – perhaps the first syntax check is purely one of tokenisation and seeing if a couple of critical tokens are present in the right order.

          Another rabbit-hole to chase down is the variation due to “execute immediate”:

          declare
              n1 number;
          begin
              execute immediate 'eelect count(*) from t1' into n1;
              dbms_output.put_line(n1);
          end;
          /
          
          declare
          *
          ERROR at line 1:
          ORA-00900: invalid SQL statement
          ORA-06512: at line 4
          
          --------
          
          SQL> select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like 'eelect%' and kglnaobj not like '%kgl%';
          
          KGLHDPAR         KGLHDADR         KGLOBT03
          ---------------- ---------------- -------------
          KGLNAOBJ
          ------------------------------------------------------------------------------------------------------------------------------------
          000000009F842A00 0000000098786818 6tqgw3r2w36cf
          eelect count(*) from
          
          000000009F842A00 000000009F842A00 6tqgw3r2w36cf
          eelect count(*) from
          
          
          

          Do you want to take the Blue pill or the Red pill ?

          Comment by Jonathan Lewis — October 5, 2017 @ 7:53 am GMT Oct 5,2017

        • Hi Jonathan,

          It seems you have executed as SYS (as you mentioned before)
          So “this rabbit hole” works different under SYS and mere mortal :).

          When I executed as regular user I’ve go
          select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like ‘eelect%’ and kglnaobj not like ‘%kgl%’;

          no rows selected

          after that I repeated as SYS and got the same as you

          select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj like ‘eelect%’ and kglnaobj not like ‘%kgl%’;

          KGLHDPAR KGLHDADR KGLOBT03
          —————- —————- —————————————
          KGLNAOBJ
          ——————————————————————————–
          00000000A1754780 00000000A1BB77B8 6tqgw3r2w36cf
          eelect count(*) from

          00000000A1754780 00000000A1754780 6tqgw3r2w36cf
          eelect count(*) from

          Comment by Dmitry Remizov — October 5, 2017 @ 12:37 pm GMT Oct 5,2017

        • Dmitry,

          I executed the pl/sql as an ordinary user then switched to the SYS account for the query against x$kglob.
          I’ve just repeated the test (on 12.1.0.2) in case I’d done it wrong the first time and it is correct. (That’s why I put a “break ———–” in the output).

          Comment by Jonathan Lewis — October 6, 2017 @ 9:11 am GMT Oct 6,2017

        • Hi Jonathan,

          execute immediate is completely another story :) . And that’s what I used as a fast workaround, when I was not able to run command from older version of oracle client against newer database server. In case of dynamic sql, all the stuff has to be done by server (even the basic tokenization).

          BR
          Pavol

          Comment by Pavol Babel — October 9, 2017 @ 2:33 pm GMT Oct 9,2017

  8. Maybe I’m missing something, but if bad syntax winds up in the library cache, how could you avoid syntax checking by looking in the library cache? Or is it more complex because x$kglob is from several places?

    Comment by jgarry — October 3, 2017 @ 10:41 pm GMT Oct 3,2017 | Reply

    • jgarry,

      I’m assuming that each “new” piece of text would have to go through a syntax check and would end up with a flag showing “good” or “failed” – so subsequent lookups would find a piece of text that was flagged as good, or flagged as bad and therefore not need further checking.

      Assumed common scenario – text pre-exists and is flagged as good: frequent savings.
      Worst case scenario – bad text arrives and its previous version has aged out so it is loaded into the library cache and tested

      Comment by Jonathan Lewis — October 4, 2017 @ 8:29 am GMT Oct 4,2017 | Reply

  9. Jonathan, I just went to the booth of real world performance team at OOW demo grounds and they had a nice demo there. One of the things they showed is running an application that is executing many invalid queries. It was interesting to see that it caused a contention on one of the shared pool latches. So yes, it seems that it does go to the shared pool.

    Comment by amitzil — October 4, 2017 @ 6:01 am GMT Oct 4,2017 | Reply

    • amitzil,

      That’s a convenient coincidence.

      I think I saw this type of problem a few years ago – a feeder from SQL Server replicating to Oracle by inserting one row at a time with literals, but getting a syntax error on the insert. Didn’t get latch contention, but there was rarely any useful stuff in the library cache. I don’t recall now if this was on OTN or at a client.

      Comment by Jonathan Lewis — October 4, 2017 @ 8:31 am GMT Oct 4,2017 | Reply

  10. […] on from a comment to a recent posting of mine about “bad” SQL ending up in the shared pool and the specific detail that too […]

    Pingback by 12c Parse | Oracle Scratchpad — October 6, 2017 @ 9:07 am GMT Oct 6,2017 | Reply

  11. […] is short post related to recent discussion on Jonanthan Lewis’s […]

    Pingback by Follow up of one discussion | dmitry remizov's weblog — October 27, 2017 @ 11:36 pm GMT Oct 27,2017 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.