Beginner's mistakes in PL/SQL - Part 4

In this post we’ll have a final look on our little PL/SQL program as this is going to be an end to our Beginner's mistakes in PL/SQL series ... for now ;).

We’ll going to take a look at last (yet very important) structural flaw in our PL/SQL code. If we take a closer look at our code, we’ll discover that there are several function calls in main data fetch cycle. 

We are talking about functions:

• get_indexes
• has_nullable
• has_default
• get_columns

If we look at them, we’ll discover that pure goal of these functions is to produce concatenated list of indexes and columns for particular object or to provide a flag on existence of nullable columns and defaults.

This approach has a HUGE negative impact on performance. Reason for that is an event we call “the context switch”. This event occurs because Oracle has two language processing engines:

• Database engine – which has SQL statement executor (he knows how to execute and process SQL statements)
• PL/SQL engine – which is capable of processing PL/SQL procedural language

Here is a nice picture of how your PL/SQL code processing looks like in Oracle:

So, every time PL/SQL engine sees an SQL statement, he basically takes it and sends it to the database engine for execution and waits for results. This act of “outsourcing” is what is called context switch and is quite CPU expensive. This phenomenon is not only about execution your SQL but also about receiving data from it. In our example, the code creator is nicely fetching data from main select by 5000 rows into an array, so he does not need to ask SQL engine for every row. He just asks for next batch of 5000 hence minimizing number of context switches while fetching the data.

Our goal is that if we have to call a SQL statement, we want to get most out of it. We want to get our data, play with it, and when we are done, ask for next batch of them.

It seems that in this case the programmer didn’t see any efficient way (or any way at all) how to get the information he needs from main query. My guess is that the issue here was the pivoting. He didn’t know how to take the list of indexes (or columns) and produce that list as one line. Because this is a simple concatenation problem; one function comes to my mind – LISTAGG. It’s basically an aggregation by concatenation function. Only limitation we have here is that LISTAGG cannot produce string longer than 4000 bytes (for purposes of this demonstration we’ll pretend it’ll never happen :) ).

So, we know now that we CAN put EVERY function we listed above into our main SQL statement and thus eliminating number of context switches by huge amount. We'll not only eliminate the function calls but also the bunch of context switches that are produced by FOR cycles in those functions.  Here is how it’s done:

1. We’ll have to change our data model a little bit so we can store few new columns

DROP TABLE my_data_tmp
/

CREATE GLOBAL TEMPORARY TABLE my_data_tmp
( table_name VARCHAR2(30) NOT NULL,
partitioned VARCHAR2(3),
temporary VARCHAR2(1),
index_list VARCHAR2(4000),
has_nullable VARCHAR2(1),
has_data_default VARCHAR2(1),
column_list VARCHAR2(4000)
) ON COMMIT DELETE ROWS
/

2. Now we can rewrite our main query as follows

    INSERT INTO my_data_tmp
    SELECT tabs.table_name,
           tabs.partitioned,
           tabs.temporary,
           idxs.index_list,
           COALESCE(CASE WHEN cols.count_nullable > 1 THEN 'Y' ELSE 'N' END, 'N'),
           COALESCE(CASE WHEN cols.count_data_default > 1 THEN 'Y' ELSE 'N' END, 'N'),
           cols.column_list
    FROM my_tables tabs
    LEFT OUTER JOIN
    (SELECT /*+ PUSH_PRED */
            object_id_table,
            LISTAGG(index_name,',') WITHIN GROUP (ORDER BY index_name) index_list
     FROM my_table_indexes
     GROUP BY object_id_table
    ) idxs
    ON (idxs.object_id_table = tabs.object_id)
    LEFT OUTER JOIN
      (SELECT /*+ PUSH_PRED */
              object_id,
              LISTAGG(column_name,',') WITHIN GROUP (ORDER BY column_id) column_list,
              COUNT(CASE WHEN nullable = 'Y' THEN 1 ELSE NULL END) count_nullable,
              COUNT(CASE WHEN data_default = 'Y' THEN 1 ELSE NULL END) count_data_default
       FROM my_table_columns
       GROUP BY object_id
      ) cols
    ON (cols.object_id = tabs.object_id);

I had to use PUSH_PRED hint to force Oracle to push object_id into the subquery so that everything is nice and tidy.

As you can see we were also able to easily get all the flags we needed inside the main query without any extra performance hit. We just took advantage of data we had to read anyway.

Now let us compare run times of old and new version of our code after all the work we had done on it.

Old version:

System FLUSH altered.
PL/SQL procedure successfully completed.
Start tmp 19:55:10
End tmp 19:55:10
Start blob 19:55:10
Exported 3306 rows
End blob 19:56:05

New version:

System FLUSH altered.
PL/SQL procedure successfully completed.
Tmp loaded in         .71 sec
Exported 3306 rows in         .16 sec

Ok that’s 55 seconds vs 0.87 of second. I think that the result is so obvious that we don’t even have to check for stats.

You can download the final package code here:

data_to_ascii.pkb

Thank you for bearing with me through all 4 parts of this series and see you soon.

Beginner's mistakes in PL/SQL - Part 3

Welcome to the Beginner's mistakes in PL/SQL - Part 3. In the last Blog, I’ve posed a question about this particular piece of code:

SELECT COUNT(*) INTO v_num_records FROM my_data_tmp; 

Well the point here is that why should I select the temporary table one more time to count number of rows, if I had it directly at hand before in form of information from cursor - SQL%ROWCOUNT.

Another thing is that there is no application orchestration what so ever in our code. If we try to locate our batch job in v$session view, our only way is by sniffing around and checking active SQL (if it is not run under particular user). Big, big mistake. So we will add some application and stage identification:

dbms_application_info.set_module(module_name => 'data_to_ascii',
                                                    action_name => 'load_tmp_table');

We will add this kind of information into other functions in our package, so that when we observe the session, we can easily see in which state our export is.

Last but not least I do not like our time measurement technique. First problem is, that SYSDATE is basically macro for

SELECT SYSDATE FROM DUAL;

That means that each call is PL/SQL – SQL context switch, which is quite expensive. Secondary it is not very precise, ‘cos it can measure only with precision up to 1 second. PL/SQL has much better tools for time measurement and it’s pure PL/SQL. That means no context switching and precision up to 1/100 of second. That tool is

dbms_utility.get_time;

This function returns PLS_INTEGER. By capturing this number in two points in time, you can get number of 100th of seconds between those two points. It’s very fast and very lightweight. The only thing you should keep in mind is, that counter number can wrap around, so if you measure for “very long time” the difference you’re going to get is nonsense.

After all what was said, sample code from package should look like following:

CREATE OR REPLACE PACKAGE BODY data_to_ascii AS
  --
  -- Number of rows in tmp table
  vg_row_count PLS_INTEGER;
  --
  -- Loades data into the temporary table
  --
  PROCEDURE load_tmp_table
  IS
    v_start PLS_INTEGER := dbms_utility.get_time;
    v_end   PLS_INTEGER;
  BEGIN
    --
    -- Orchestration
    --
    dbms_application_info.set_module(module_name => 'data_to_ascii',
                                     action_name => 'load_tmp_table');
    --
    DELETE my_data_tmp;
    --
    INSERT INTO my_data_tmp
    SELECT tabs.object_id,
           tabs.table_name,
           tabs.partitioned,
           tabs.temporary
    FROM my_tables tabs;
    --
    -- Get number of lines processed which is number of rows in temporary table
    --
    vg_row_count := SQL%ROWCOUNT;
    --
    -- Measure
    --
    v_end := dbms_utility.get_time;
    dbms_output.put_line('Tmp loaded in ' || TO_CHAR((v_end - v_start) / 100, '999,999.99') || ' sec');
    --
  END load_tmp_table;
  ...

Continue to Part 4.

Beginner's mistakes in PL/SQL - Part 2

In the last blog post we’ve setup an example with some interesting errors which beginners in Oracle PL/SQL tend to do.

In this post we’ll take a look at one of them and hint another one.

First, let us observe very strange behavior on list of indexes. If I’ll run following query:

SELECT LISTAGG(index_name, ',') WITHIN GROUP (
ORDER BY index_name) index_name
FROM my_table_indexes
WHERE object_id_table=69048
GROUP BY object_id_table;

I’ll get this result:

SYS_C004167,SYS_IL0000069048C00004$$,SYS_IL0000069048C00007$$,SYS_IL0000069048C00015$$,SYS_IL0000069048C00016$$,SYS_IL0000069048C00017$$,SYS_IL0000069048C00022$$,SYS_IL000 …

Now, I’ll put this declaration in our package specification, which will allow me to run it from “outside” :

FUNCTION get_indexes ( p_object_id_in my_data_tmp.object_id%TYPE ) RETURN VARCHAR2;

And I’ll try to get same result from our function as it definitely has to be same:

SET SERVEROUTPUT ON;
BEGIN
 dbms_output.put_line('idx name:' || data_to_ascii.get_indexes( 69048 ));
END;
/

PL/SQL procedure successfully completed.
idx name:

Well that didn’t work out. But how is it possible?

If we check the code, one particular part should cache out eye:

FUNCTION get_indexes ( p_object_id_in my_data_tmp.object_id%TYPE ) RETURN VARCHAR2
  IS
    v_text VARCHAR2(1024);
  BEGIN
    --
    -- Select and concat indexes
    --
    FOR l_sel IN (SELECT index_name FROM my_table_indexes WHERE object_id_table = p_object_id_in ORDER BY index_name)
    LOOP
      v_text := v_text || l_sel.index_name || ',';
    END LOOP;
    --
    RETURN( v_text );
    --
    -- Error handling
    --
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN('');
      WHEN OTHERS THEN
        RETURN('');
  END get_indexes;

Yes … WHEN OTHERS THEN. Basically what it says is following “Whatever something else goes wrong, just return empty string” and that’s what will Oracle do. Corrupted block? Return empty string. Buffer overflow? Return empty string.

You would be surprised how often I do see this. My advice is: Just don’t do it. You’ll get wrong results when something goes wrong and you’ll have NO IDEA why. You’ll spend days by reproducing error which might never happen again. If there is an error, let it fail and handle the error based on your business (log it, etc.).

So let’s see what went wrong. I’ll delete general exception handling and rerun the block:

ORA-06502: PL/SQL: numeric of value error: character string buffer too small
ORA-06512: at "DATA_TO_ASCII", line 35
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"

Ok. That’s much better. Now we see that there is a problem with size of our v_text variable. Apparently concatenation of index names for this table is longer than 1024 bytes. So what we will probably do is to increase it.

Now, I should point out that your data might differ so you’ll have to find our own object id, which makes same problem as mine.

First problem solved. Now let’s take a bite in next one, shall we? I really do not like this line:

SELECT COUNT(*) INTO v_num_records FROM my_data_tmp;

Do you like it? What change would you make?

Continue to Part 3.

Beginner's mistakes in PL/SQL - Part 1

Before a month or so, I was given an interesting PL/SQL code for tuning. Its function was to read certain data from database and then export them into a flat file. I was told that customer is complaining that time to export usual batch of data (not very large) is unacceptable. So it was typical "make it faster" problem.

As I was reading through and analyzing the code, I've decided I'm going to make a blog from that as it had all the nice mistakes in one place.

All we'll do in our first post is that we are going to setup our example. After that, in the mean time, you can read the code and try to identify mistakes and slow parts. Also you can try to construct your own faster solution and then you can compare it with next blog posts.

The code below is given for demonstration purposes only and replicates all the mistakes and tuning issues original program had. I’ve also cut some code to make it more readable.

DDL script to setup our data model and populate it with data:

data_to_ascii_ddl.sql

Package specification and body:

data_to_ascii.pks

data_to_ascii.pkb

To test it, just run following code:

SET SERVEROUTPUT ON;
BEGIN
 data_to_ascii.load_tmp_table;
 data_to_ascii.create_blob;
END;
/

Continue to Part 2