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". 

Check your SQL and SQL*Plus skills #1

What will be the output of this in SQL*Plus as written here (predict it without running it, then run it and see if you're right):


SELECT 1 -
SAL FROM EMP;


How did you do?

Does the LIMIT clause on a BULK COLLECT select really matter?

Mostly I teach SQL optimization because SQL has the most impact on performance, with performance being from TIME perspective.  But there is more to performance then time.  Scalability is really at the heart of performance.  And your SQL might not scale well for a other things not directly related to time.  Like memory usage.  If your SQL consumes a lot of memory, then it may run fast but could scale horribly and hence not run well under pressure.

In PL/SQL land there is a great feature to read in a set of rows from a table (or tables) called BULK COLLECT.  It's a great way to read in a set of rows at a time, then process then rather than trying to get one row at a time.  And there is a LIMIT clause that can be used to limit the number of rows coming back.  If working with a few rows (less than 100 or so) then you can likely not use this and be fine, but if you're working with a larger set of rows you really should set this to something realistic.

Here is a simple example to show what happens with the setting of LIMIT.

First, I have a routine to get PGA space used:

CREATE OR REPLACE FUNCTION pga_memory_used
RETURN NUMBER
AS
    l_used_memory  NUMBER;
BEGIN
    SELECT ms.value
      INTO l_used_memory
      FROM v$mystat ms,
           v$statname sn
     WHERE ms.statistic# = sn.statistic#
       AND sn.name = 'session pga memory';
    RETURN l_used_memory;
END;
/

Now I'll run this little code block to illustrate the effects of the LIMIT clause. I'll run it twice once with limit set to 5000 and then again will it set to 1000.  The table BIG_TAB has about 2,868,512 rows and the average row length is 115.  (NOTE: The call to bigtab_process after the fetch call is to a stub procedure in this case, it does nothing.)

DECLARE
   l_start_mem  number;
   l_start_time pls_integer;
   l_start_cpu  pls_integer;
   CURSOR bigtab_cur IS
   SELECT * FROM big_tab;
   TYPE big_tab_t IS TABLE OF big_tab%ROWTYPE;
   bigtab big_tab_t;
BEGIN
   l_start_mem  := pga_memory_used;
   l_start_time := DBMS_UTILITY.GET_TIME;
   l_start_cpu  := DBMS_UTILITY.GET_CPU_TIME;
   OPEN bigtab_cur;
   LOOP
     FETCH bigtab_cur
     BULK COLLECT INTO bigtab LIMIT 5000;    
     EXIT WHEN bigtab.COUNT = 0;
     bigtab_process;
   END LOOP;
   DBMS_OUTPUT.put_line ('----------------------------');
   DBMS_OUTPUT.put_line ('--- BULK COLLECTION DEMO ---');
   DBMS_OUTPUT.put_line ('The limit clause set to 5000');
   DBMS_OUTPUT.put_line ('Times in hundredths of a second');
   DBMS_OUTPUT.put_line ('**** TIME   - '||to_char(DBMS_UTILITY.get_time - l_start_time));
   DBMS_OUTPUT.put_line ('**** CPU    - '||to_char(DBMS_UTILITY.GET_CPU_TIME - l_start_cpu));
   DBMS_OUTPUT.put_line ('**** Memory '||to_char(((pga_memory_used - l_start_mem))/1024,'9,999,999')||' KB');
END;
/

And now the run results:


----------------------------
--- BULK COLLECTION DEMO ---
The limit clause set to 5000
Times in hundredths of a second
**** TIME   - 548
**** CPU    - 520
**** Memory      7,552 KB

----------------------------
--- BULK COLLECTION DEMO ---
The limit clause set to 1000
Times in hundredths of a second
**** TIME - 537
**** CPU  - 487
**** Memory      2,112 KB


Notice that the time (wall clock time) doesn't really change much, the CPU is less in the second one but look at the memory usage.  A rather significant drop!  And yes this is consistent over multiple runs, the actually numbers vary but the second one always uses something like half or less memory and less CPU. 

You might say, hey 7M vs 2M what's the big deal?  I have gigabytes of PGA space!  Sure on a one run bases this isn't a big deal. But multiple this by a few 1000 users.  Now it might make a difference.

If you're using BULK COLLECT (which you should be!) then check the LIMIT clause, could save some memory and could scale your application much better.