How to find differences between Oracle production and test envs

Determining why things behave differently in production versus a test environment can be time-consuming and painful.

That is until you realize there is a convenient package that allows you to compare the two environments quickly.

Introduced in Oracle Database 11g, the DBMS_COMPARISON package allows you to compare objects, schemas, or data between two databases or schemas (within the same database) to find where they differ.

Let’s take a closer look at how this works.

Create the Comparison

First, you must create a comparison using the DBMS_COMPARISON.CREATE_COMPARISON procedure. You will need to supply a unique name for the Comparison, the schema name, and, optionally, the object name in the current database you wish to compare. You must also supply the location of the other schema you want to compare. The comparison schema can be in the same database (specify NULL for DBLINK_NAME). Still, in my case, below, I’m comparing our product system to our test system, which is reachable from production via a database link called ‘orcl2_test’.

BEGIN
     dbms_comparison.create_comparison(
      					  comparison_name => 'COMP_SALES',
      					  schema_name     => 'SH',
      					  object_name     => 'SALES',
      					  dblink_name     => 'orcl2_test'
      					  );
END;
/  
PL/SQL PROCEDURE successfully completed.

Note: You need a unique index on both tables to compare tables, as shown above.

Run the Comparison

Now we have created the Comparison; we can execute it using the DBMS_COMPARISON.COMPARE function. The COMPARE function returns a BOOLEAN, indicating whether the objects are consistent. It returns TRUE when no differences are found and FALSE when discrepancies are found.

However, the details of the differences are not returned by the function. Instead, the differences are stored in dictionary tables; user_comparison_columns, user_comparison_row_dif, and user_comparison_scan. If a discrepancy is found, you can find details on the differences by querying these tables using the scan ID, which is returned in the variable scan_info.

How you call the COMPARE function is a little more complex than a standard PL/SQL function call. I’ve wrapped the function call in an IF NOT statement in the example below. If the DBMS_COMPARISON.COMPARE function returns FALSE (IF NOT FALSE = TRUE), the function will print out the SCAN ID, so we can use it to query the dictionary table. If, on the other hand, the function returns TRUE (IF NOT TRUE = FALSE), it will return nothing.

DECLARE
        scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
         IF NOT DBMS_COMPARISON.COMPARE
       		     ( comparison_name => 'COMP_SALES'
       		     , scan_info       => scan_info
       		     , perform_row_dif => TRUE
       		     ) THEN
           DBMS_OUTPUT.PUT_LINE('Scan ID:'||scan_info.scan_id);
        END IF;
END;
 /
 
Scan ID:3

Find the details on the Comparison

As you can see, there must be a discrepancy between our production and test environments, as the COMPARE function returned a SCAN ID of 3. We need to query the dictionary tables using the comparison name and the scan id to determine the difference. Below is the query we need to use. You will notice I’ve included two case statements to indicate if the difference was found on the local database (in this case, production) or on the Remote database (in this case, our test environment).

SELECT c.column_name, r.index_value, 
       CASE WHEN r.local_rowid IS NULL  THEN 'No' ELSE 'Yes' END  LOC, 
       CASE WHEN r.remote_rowid IS NULL THEN 'No' ELSE 'Yes' END REM 
  2  FROM   user_comparison_columns c, user_comparison_row_dif r, user_comparison_scan s
  3  WHERE  c.comparison_name = 'COMP_SALES'
  4  AND    r.scan_id         = s.scan_id
  5  AND    r.status	      = 'DIF'
  6  AND    c.index_column    = 'Y'
  7  AND    c.comparison_name = r.comparison_name
  8  AND    s.scan_id         = 3  
  9  ORDER BY r.index_value;
 
COLUMN_NAME    INDEX_VALUE    LOC    REM    
______________ ______________ ______ ______ 
ID             5000           Yes    Yes

The result of our query shows a difference in the data that occurs in the row with ID 5000. However, it doesn’t show us which column has the problem. I must extract the row with ID=5000 from both systems and manually compare them.

SELECT * FROM sales WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                     
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140          .5          5000 
 
SELECT * FROM  sales@orcl2_test WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                       
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140        xx.xx        5000

As you can see from the output above, the difference is in the TAX_CODE column. The TAX_CODE has been masked in the test environment for this particular row.

You may be wondering why Oracle couldn’t show me the exact difference between the two tables. If I had a unique index on the TAX_CODE column, Oracle could have told me the value that differed. But since this is not a unique column, Oracle can only pinpoint the row for me using the unique index on the ID column.

This blog post is part of a series on useful PL/SQL packages. Other blogs in this series include:

How to determine which view is the right view to use in Oracle?

Database views are a handy tool to help obfuscate complex joins from developers and analysts. However, knowing which view to use can be tricky, especially when faced with multiple views with similar-sounding names. After all, you don’t want to join various tables only to discover that all the information you needed was in just a single table. But how do you know which view has the data you are after and will allow you to get it with the minimum number of joins?

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands any references to a view within a query, turning it into a subquery in the original statement that displays the full query text behind that view. This trick lets you see where and how the needed data will be retrieved.

Let’s look at an elementary example of this procedure in action, using a SELECT * statement on a view called SALES_V.

SET serveroutput ON 
DECLARE 
    l_clob CLOBBEGIN 
    dbms_utility.Expand_sql_text(input_sql_text => 'SELECT * FROM sales_v', output_sql_text => l_clob); 
    dbms_output.Put_line(l_clob)END/

The result of this procedure call is the following output.

SELECT "A1"."order_id" "ORDER_ID", 
       "A1"."time_id"  "TIME_ID", 
       "A1"."cust_id"  "CUST_ID", 
       "A1"."prod_id"  "PROD_ID" 
FROM   (SELECT "A3"."order_id" "ORDER_ID", 
               "A3"."time_id"  "TIME_ID", 
               "A3"."cust_id"  "CUST_ID", 
               "A3"."prod_id"  "PROD_ID" 
        FROM   "SH"."sales" "A3", 
               "SH"."products" "A2" 
        WHERE  "A3"."prod_id" = "A2"."prod_id") "A1"

The subquery with the alias A1 above is the view definition for SALES_V.

It’s a simple two-table join between SALES (alias A3) and PRODUCTS (alias A2). Although the view only returns columns from the SALES table (A3), it does come with the overhead of a join. The execution plan for our simple SELECT * query below shows that.

PLAN_TABLE_OUTPUT                                                                          
__________________________________________________________________________________________ 
Plan hash VALUE: 2857462611                                                                
 
---------------------------------------------------------------------------------------    
| Id  | Operation                  | Name     | ROWS  | Bytes | Cost (%CPU)| TIME     |    
---------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT           |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|*  1 |  HASH JOIN                 |          |  9773 |   314K|     7   (0)| 00:00:01 |    
|   2 |   TABLE ACCESS STORAGE FULL| PRODUCTS |  4999 | 29994 |     2   (0)| 00:00:01 |    
|   3 |   TABLE ACCESS STORAGE FULL| SALES    | 10000 |   263K|     5   (0)| 00:00:01 |    
---------------------------------------------------------------------------------------    
 
Predicate Information (IDENTIFIED BY operation id):                                        
---------------------------------------------------                                        
 
   1 - access("S"."PROD_ID"="P"."PROD_ID")

In this case, it would be best to find an alternative view that only accesses the SALES table or access the SALES table directly. Below is the plan for a direct select statement from the SALES table, and as you can see, the cost of this plan is lower.

EXPLAIN PLAN FOR
SELECT s.order_id, s.date_id, s.cust_id, s.product_id 
FROM sales s;
 
Explained.
 
SELECT * FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT                                                                      
______________________________________________________________________________________ 
Plan hash VALUE: 781590677                                                             
 
-----------------------------------------------------------------------------------    
| Id  | Operation                 | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |    
-----------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT          |       | 10000 |   263K|     5   (0)| 00:00:01 |    
|   1 |  TABLE ACCESS STORAGE FULL| SALES | 10000 |   263K|     5   (0)| 00:00:01 |    
-----------------------------------------------------------------------------------

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. This blog post is part of that series. Other blogs in this series include How to add a SLEEP COMMAND to your Oracle PL/SQL code and How diff optimizer statistics.

How do I add a SLEEP to a PL/SQL Stored Procedure?

Over the last couple of releases, Oracle has added several handy PL/SQL packages and procedures you might not know about. So, I put together a short blog series highlighting some of my favorites. First up, DBMS_SESSION.SLEEP().

Oracle has always enabled you to add a sleep command to your stored procedures to suspend a session for a specified number of seconds, as shown in the code below.

    DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_LOCK.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
    END;
   /
 
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
 
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

However, the sleep function was part of the DBMS_LOCK package, which is not granted to PUBLIC, by default, due to the other more powerful functions inside that package. That means you had to beg the DBA or the security team to give you access to this package just to put your session to sleep for a few minutes.

DBMS_SESSION.SLEEP()

Things got a lot easier starting in Oracle Database 18c, as the sleep function is now available in the DBMS_SESSION package, which is granted to PUBLIC by default. That means you can call the function without any additional privileges. Plus, the function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code!

DECLARE
       v_start TIMESTAMP;
       v_end   TIMESTAMP; 
    BEGIN
       v_start := SYSTIMESTAMP;
       -- Sleep for 10 seconds
       DBMS_SESSION.SLEEP(10);
       v_end   := SYSTIMESTAMP;
       DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
       DBMS_OUTPUT.PUT_LINE('This procedure ended   at ' ||v_end);
   END;
   /
This PROCEDURE started AT 10-SEP-22 12.39.40.587041 AM
This PROCEDURE ended   AT 10-SEP-22 12.39.50.637738 AM
PL/SQL PROCEDURE successfully completed.
Elapsed: 00:00:10.02

Jonathan Lewis made a great point in the comments below about the granularity of the DBMS_SESSION.SLEEP is 1/100th of a second. If you want to introduce a sleep in milliseconds (or less), you can call Java from PL/SQL, as he demonstrated in his blog posts, little-sleeps.