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".
How come you get the same action for the 3 statements even though you set it differently for each execution?
ReplyDeleteExcellent 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.
DeleteThis comment has been removed by a blog administrator.
ReplyDeleteNice 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.
ReplyDeleteRoger 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.
DeleteA 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.
ReplyDeleteIs this really related to PL/SQL or is it actually related to SQL statements?
ReplyDeleteOne 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!
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.
DeleteTo retain a user comments and white spaces we could use event 10946 at level 64.
ReplyDeleteSmall 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