Updating table with data from db link

About two months ago, I was contacted by one my friends. He is quite new to Oracle and he was asking me if I can help him with a service request he was working on. Problem he was facing was following:

• He needed to update rows in table app.app_num_service with data from table customers which was in remote database
• Data in table customers was versioned by order of columns STATUS_IND, PRIMARY_IND DESC, SEQNO and he needed to use most recent version for given customer (CUST_CODE)
• Data in customers table did not contain data about all customers, so some customer codes where non existent in app.app_num_service table

I wont place original statement here. All you need to know is that he was making a rookie mistake of directly joining on big remote table in his update. I'm not claiming it's always bad idea, but definitely here it was. Just imagine how many network round trips double check on customer table for each row would be needed (expecting that CUST_CODE in indexed on remote side in first place).

Generally when we are working with remote databases in ETL processes ... and in cases like this also, we are trying to accomplish few main goals:

• Minimize amount of data transferred through database link
• Minimize amount of "queries" through database link

Both these goals go hand in hand with goal of minimizing time we are transferring our data. Don't forget that even select through remote link opens a transaction on remote side and we want to make it as short as possible and constrain remote database with our query for as short time as possible.

Tool we usually use to accomplish this goal is CTAS or TRUNCATE & INSERT APPEND into existing staging table. This goes hand in hand with transferring to local side ONLY data we really need which in our case is like 25% of whole table. What I suggested was following two DDLs:

CREATE TABLE service.banner TABLESPACE service AS
SELECT /*+ DRIVING_SITE(remote_table) */ customercode,
       phonenumbFROM (SELECT ROW_NUMBER()
              OVER (PARTITION BY CUST_CODE
                    ORDER BY STATUS_IND,
                             PRIMARY_IND DESC,
                             SEQNO) rn,
             CUST_CODE customercode,
             CONCAT(PHONE_AREA, PHONE_NUMBER) phonenumber
      FROM customers@remote_db)
WHERE rn=1;

CREATE UNIQUE INDEX service.banner_ix1 ON service.banner(customercode)
TABLESPACE service;

What we did here is, that we transferred only most recent versions of rows we will need for update and then created index for fast access based on customercode.

Hint DRIVING_SITE is quite irrelevant here but I'm used to use it. What is does is that it tells Oracle which database will process the query (driving side) and which one will send data. This comes in play when we are joining tables through database link and we are considering table sizes on both sides.

BEGIN
    dbms_stats.gather_table_stats(
        ownname          => 'SERVICE',
        tabname          => 'BANNER',
        estimate_percent => dbms_stats.auto_sample_size,
        method_opt       => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
        degree           => dbms_stats.auto_degree
    );
END;
/

Now we'll calculate database statistics for our new table. Estimate is most of the time sufficient and quite fast. We do not need to calculate index statistics 'cos we where on Oracle 11g R2 so stats where calculated with index build.

With all of that done, we could run our simple update:

UPDATE app.app_num_service app
SET app.PHONENUMBER = (SELECT PHONENUMBER
                           FROM service.banner
                           WHERE service.customercode = app.customercode)
WHERE EXISTS(SELECT NULLFROM service.banner
             WHERE app.customercode = service.customercode);

COMMIT;

From non-ending update we ended with one pretty fast.

In conclusion you want to grab ONLY what you need and save it locally as fast as possible. Then work with it (probably with some extra work on indexing and stats) as needed.

BGOUG 2016 session: Joins – which, when and why

I think it’s time to share my presentation from Joins – which, when and why session, which I was presenting at BGOUG 2016:

How to associate statistics to a function - Part 2

In our second and last part about association of statistics to PL/SQL functions in Oracle we’ll take a look at how to estimate I/O and CPU usage.

First we’ll try to measure I/O. Let’s have a procedure discount from an online shop. All that it does is that I takes order ID and applies requested discount on it. For I/O measurement we’ll use handy PL/SQL program called mystats which is variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. You can get it here: https://github.com/oracle-developer/mystats.

Simple example of usage should look like follows:

set serveroutput on
/

begin
 mystats_pkg.ms_start;
end;
/

begin
 discount(1,20);
end;
/

begin
 mystats_pkg.ms_stop(mystats_pkg.statname_ntt('consistent gets','db block gets'));
end;
/

rollback
/

Output will look something like this:

========================================================================================== MyStats report : 14-OCT-2016 22:35:31 ========================================================================================== ------------------------------------------------------------------------------------------ 1. Summary Timings ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- TIMER snapshot interval (seconds) 0.02 TIMER CPU time used (seconds) 0.01 ------------------------------------------------------------------------------------------ 2. Statistics Report ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- STAT db block gets 5 STAT consistent gets 9 ------------------------------------------------------------------------------------------ 3. Options ------------------------------------------------------------------------------------------ - Statistics types : statistics=Y, latches=Y, time model=Y - Reporting filter : statnames in=consistent gets,db block gets ------------------------------------------------------------------------------------------ 4. About ------------------------------------------------------------------------------------------ - MyStats v3.0 by Adrian Billington (http://www.oracle-developer.net) - Based on the SNAP_MY_STATS utility by Jonathan Lewis ========================================================================================== End of report ==========================================================================================

From report we see that our measured I/O is 14. This is very simple and probably not very representative test. It would be a good idea to give procedure more iterations over different data and then divide results by number of iterations.

Now that we now how much I/O will function usually consume, we can focus on CPU.  For CPU estimate we will use PL/SQL function called DBMS_ODCI.ESTIMATE_CPU_UNITS (https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_odci.htm#i996771) which returns the approximate number of CPU instructions (in thousands) corresponding to a specified time interval (in seconds).

We can do something like this with following output:

set serveroutput on
/

declare
 v_start PLS_INTEGER := DBMS_UTILITY.GET_TIME;
 v_end PLS_INTEGER;
begin
 mtg.order_pkg.discount(1,20);
 v_end := DBMS_UTILITY.GET_TIME;
 dbms_output.put_line('Time:'||to_char((v_end-v_start)/100,'999,999.999'));
 DBMS_OUTPUT.PUT_LINE(ROUND(1000 * DBMS_ODCI.ESTIMATE_CPU_UNITS(v_end-v_start)/100,0));
end;
/

rollback
/

PL/SQL procedure successfully completed. Time: .080 119287673 Rollback complete.

Again I would advise you to give it more iterations to get nicer picture a use average value.

How to associate statistics to a function - Part 1

It has been a long time since my last post and I’m sorry for that. I’ve been very busy lately and a lot of things happened in my life. Most importantly; I’ve finally decided to move to the next level and I’ve founded my own company – Terama Consulting. You can check our web site here http://www.terama.consulting. We focus mainly on IT consulting, employee training and small cloud services. Feel free to contact us :).

Now, last time we asked ourselves an interesting question. How does Oracle order predicates in where clause and how to tell him how expensive our function really is. Reason for this is that Oracle has none to very limited way to say how CPU or I/O intensive your function is.

We’ll break our post into two parts. In first part, we will learn how to give Oracle information he needs for best possible decision. And second part, where we actually learn how to measure resource consumption, so we know what to tell to Oracle.

So, how to give Oracle information about cost and other attributes of our function?

Answer is quite simple: ASSOCIATE STATISTICS

Purpose of this statement is to associate statistic of given type to our function. By using it, we can tell Oracle what is the CPU cost or our function, what is its selectivity, I/O cost, etc.

Let’s have a look on our options here. We can associate statistics with following object types, while only one set of statistics may be attached to that object:

• Columns
• Functions
• Packages
• Types
• Indexes
• Indextypes

There are certain limitations to what you can set with what object type but I don’t think we need to go into so much detail here. You can study Oracle documentation for that (https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4006.htm).

Now, let’s see what statistics you can associate with your objects:
• CPU cost (number of units greater than 0)
• I/O cost (number of units greater than 0)
• Network cost (not implemented as of 12c)
• Selectivity (number between 0 and 100 where 100 means no filtering at all)

Here are some usage examples:

Selectivity 5%:

ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT SELECTIVITY 5;

CPU cost 950, I/O cost 11 and network cost of 0 (which you can happily ignore):

ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT COST (950,11,0);

All together on one function:

ASSOCIATE STATISTICS WITH FUNCTIONS my_where_func DEFAULT SELECTIVITY 5 DEFAULT COST (950,11,0);

Now it would be a nice time for some execution plans where we play with statistics, but I think you’ve already got the trick. So no execution plans for today and see you in next part :)

Where clause predicate evaluation order

On my lectures I get quite often asked about how Oracle chooses evaluation order in where clause. It’s quite interesting question so I’ve decided to make today’s blog post about that. Officially Oracle says as follows:

Some of the things that influence the order of application of the predicates are:

• Their position in the WHERE clause
• The kind of operators involved
• Automatic Datatype conversion
• The presence of bind variables
• Joins
• The optimizer mode (Rule or Cost)
• The cost model (IO or CPU)
• The Query Rewrite feature
• Distributed operations
• View merging
• Query Un-nesting
• Other Documented and Undocumented Parameters
• Hints

In addition, the optimizer can also add, transform or remove predicates. One example being transitivity:

a=1 AND a=b

Seeing that, Optimizer knows that b must be equal 1 and adds that to where clause creating following result

a=1 AND a=b AND b=1

This all might seem little confusing. So let’s simplify all of this in one sentence. Basically Oracle is trying to figure out best order of evaluation of predicates so that he can as soon as possible AND as cheaply as possible find out that he can throw away current row. So in that context he wants to check most selective and cheapest predicates first so he can cheaply and right away find out that he does not want current row.

After all we have said here you’ll definitely deduce, that Oracle will very likely evaluate constant predicate 1st, functions second and he will try to keep those pesky subqueries to the last if possible.

Well that is logical, right? Yes, it is and a lot of developers know that and tend to count on that … and bad things happen as usual :)

Let’s make a little demonstration:

CREATE TABLE where_table AS
SELECT * FROM dba_objects;
UPDATE where_table SET object_type = NULL WHERE object_type != 'INDEX';
COMMIT;

CREATE OR REPLACE FUNCTION my_where_func(
    v VARCHAR2)
  RETURN VARCHAR2 DETERMINISTIC
IS
  d pls_integer;
BEGIN
  IF v='INDEX' THEN
    RETURN(v);
  ELSE
    RAISE_APPLICATION_ERROR(-20000, 'NULL not allowed');
  END IF;
END;
/

As you can see, we have a table which is copy of dba_objects and we make it so that object_type column has value NULL or INDEX. Our function is also very simple. When it gets INDEX it returns INDEX. On any other value it throws an error.

Ok. Now let’s have this simple query:

SELECT *
FROM where_table
WHERE owner                   IS NOT NULL
AND object_type                = 'INDEX'
AND my_where_func(object_type) = 'INDEX';

Logically, Oracle will evaluate if object_type is equal to INDEX 1st (its cheapest and most selective check), then he will check whether owner is not null (still very cheap but not so selective) and after all of that, IF row still survives, he’ll call our function (it costs him more CPU then previous predicates).

Now let us run our query and see what we get:

SQL Error: ORA-20000: NULL not allowed
ORA-06512: at "TESTDBA.MY_WHERE_FUNC", line 9

Now being very clever reader you are, you know that only way this can happen is that our function was called with NULL. But how is this even possible when object_type = 'INDEX' should be checked 1st. Ok, let us see the execution plan:

Plan hash value: 3066599507 -------------------------------------------------- | Id | Operation | Name | E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | | |* 1 | TABLE ACCESS FULL| WHERE_TABLE | 48 | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("MY_WHERE_FUNC"("OBJECT_TYPE")='INDEX' AND "OBJECT_TYPE"='INDEX' AND "OWNER" IS NOT NULL))

Well, this is not what we were expecting at all. This is not even remotely right. But how is this possible? Well the reason is simple … I cheated. After the creation of my_where_func I ran this:

ASSOCIATE STATISTICS WITH FUNCTIONs my_where_func DEFAULT COST (1,0,0);

Basically I told Oracle that CPU cost of that function is 1, IO cost is 0 and network cost is 0. So he thinks it’s very very cheap. You might wonder what my point here is. Well, my point is that sometimes Oracle messes up (wrong stats, damaged stats) and will do things like this. You should take that into account when designing your functions. Just a month ago I was helping with issue when report ran for 1 year without any problem and on one day from 23:44 it started to crush with ORA-06502: PL/SQL: numeric or value error: NULL index table key value. Problem was same select as stated before and developer counted on Oracle to call his function last. He was using passed value as a key to associative array and didn’t check the input ‘cos he knew that all not null values must be in index of that array. But Oracle did change order of evaluation and null happened.

Now don’t forget that we are talking about filter predicates. Access predicates on indexes are of course used first.

You might ask youself what happens when Oracle can’t decide which of your two predicates is more expensive. Well he keeps them in order you placed them in your where clause. So sometimes it is good to do a little ordering in your where clause. BUT best think you can do is to TELL Oracle how much each of your functions costs so he knows. We’ll look at how to do that next time.

Think simple and spare yourself a facepalm session

I think it’s time to share my presentation from Think simple and spare yourself a facepalm session, which I was presenting at BGOUG & DOAG 2015:

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.