Monday, February 24, 2014

PL/SQL and formating of SQL text

What does the PL/SQL engine do with the SQL in a block? 

It does some pretty cool things actually.  It coverts the entire statement to upper case, except for quoted strings of course, and it strips out all white space down to a single space.  So carriage returns, line feeds, tabs and such all become a single space.  Also comments are removed, but not HINTS.


Here is s simple example.  I'm using MODULE and ACTION name here to identify these different runs.  I'll run what is really the same query 4 times but each time the text is a little different and the last one has a hint:

SQL> exec dbms_application_info.set_module('PLSQL','LOWER');

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2   cnt number;
  3  begin
  4    select count(*) into cnt from emp e;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_application_info.set_module('PLSQL','UPPER');

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2   CNT NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO CNT FROM EMP E;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_application_info.set_module('PLSQL','TWOLINES');

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2   cnt number;
  3  begin
  4    select count(*) into cnt
  5    from emp e;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_application_info.set_module('PLSQL','HINT');

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2   cnt number;
  3  begin
  4    select /*+ full (e) */ count(*) into cnt
  5    from emp e;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_application_info.set_module(null,null);

PL/SQL procedure successfully completed.

SQL>
SQL> select action, executions, version_count, hash_value, sql_text
  2    from v$sqlarea
  3   where module = 'PLSQL' and command_type != 47
  4   order by hash_value;

ACTION      EXEC VERSIONS   HASH_VALUE SQL_TEXT
---------- ----- -------- ------------ --------------------------------------------------
HINT           1        1   2245097906 SELECT /*+ full (e) */ COUNT(*) FROM EMP E
LOWER          3        1   2728343546 SELECT COUNT(*) FROM EMP E

2 rows selected.


Notice that for for 3 of them they all ended up being the same SQL statement.  The hinted statement is different and notice that the hint text is not converted to upper case.  This is pretty cool. 

What this means is that you might want to put even simple code in a PL/SQL block since this will promote the reuse of cursors that only have slight differences in text but are really the same statement. 

The PL/SQL engine has been doing this "forever". 



9 comments:

  1. How come you get the same action for the 3 statements even though you set it differently for each execution?

    ReplyDelete
    Replies
    1. Excellent question! The reason it's the same action for all three is that it's the only one that was run. The other two "actions" are not associated with any SQL because the one version of the SQL was used for all three. Also notice that the the name of the one action that is used it "LOWER" yet the statement is in upper case, it was the first one ran and therefore has the SQL statement associated with it. For actions UPPER and TWOLINES there isn't a SQL statement associated with those two and therefore no statement to retrieve from V$SQL_AREA.

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Nice demo. It looks like Oracle might be keying on the "+" sign in the hint. If you modify the SQL so the hint is bad or missing but /*+ */ appears you get the statements. Personally I wish that Oracle had parsed all SQL this way before generating the hash value. A lot more SQL would compare equal if a difference in spacing or breaking a query into two lines instead of one still generated the same hash for what is really the same statement.

    ReplyDelete
    Replies
    1. Roger that Mark. It would be nice if the SQL engine did that as well. However, since it hasn't done that for oh so long, it might break things that assume it doesn't do this kind of formatting with the SQL. I suspect the SQL engine will never do this, but we shall see.

      Delete
  4. A small notice. PL/SQL rolls up whitespaces (+ tabs + newlines) to one, but it doesn't insert whitespace when needed. For example, WHERE a= b will not be the same as WHERE a = b. These clauses will lead to different statement signatures.

    ReplyDelete
  5. Is this really related to PL/SQL or is it actually related to SQL statements?
    One thing about the hints: We often use "fake" hints to tag where the statement comes from:
    select /*+package.procedure*/ ... from ... or select /*+some_reference_to_the_source*/ ... from ...
    Often useful!

    ReplyDelete
    Replies
    1. It's a bit of both of course. It's the SQL that matters, but it's the PL/SQL engine that does the formatting.

      Delete
  6. To retain a user comments and white spaces we could use event 10946 at level 64.
    Small example:

    SQL> alter session set events '10946 level 64';

    Session altered.

    SQL>
    SQL> exec dbms_application_info.set_module('PLSQL','LOWER')

    PL/SQL procedure successfully completed.

    SQL>
    SQL> declare
    2 cnt number;
    3 begin
    4 select /*this's a comment*/ count(*) into cnt from emp e;
    5 end;
    6 /

    PL/SQL procedure successfully completed.

    SQL>
    SQL> exec dbms_application_info.set_module( '','')

    PL/SQL procedure successfully completed.

    SQL>
    SQL> select action, executions, version_count, hash_value, sql_text
    2 from v$sqlarea
    3 where module = 'PLSQL'
    4 and command_type != 47
    5 order by hash_value;

    ACTION EXECUTIONS VERSION_COUNT HASH_VALUE SQL_TEXT
    -------------------- ---------- ------------- ---------- ------------------------------------------------
    LOWER 3 1 2324858491 SELECT /*this's a comment*/ count(*) from emp e

    ReplyDelete