DBMS_XPLAN can lie!

disclaimer: here

"Don't trust what you see!"

A cliche in spy movies, this sentence can also have a less impressive interpretation: "Don't be over-excited by any finding!"

I came to believe in this interpretation when I was studying for Oracle SQL Tuning Expert exam.

Going through experiments on using different DOP for INSERT...SELECT statement (a more comprehensive post coming up soon), I came across something which looked like a behavior change between 11.2.0.4 and 12.1.0.2 and -more importantly- seemed to contradict the documentation.

All I heard in my brain was this: "Shit! This is going to put me on the map! OakTable! Here I come!"

I got in touch with Martin Widlake, who was kind enough to go through the results of my experiment, and advised me to publish the results -which I'm extremely grateful for it.

DOP in INSERT..SELECT

Just for clarification, I'd like to rewrite the rule for calculation of DOP in INSERT..SELECT as below:

After the decision to parallelize the SELECT or INSERT operation is made, one parallel directive is picked for deciding the DOP of the whole statement, using the following precedence rule:

1.       Insert hint directive

2.       then Session

3.       then Parallel declaration specification of the inserting table

4.       and then Maximum query directive.

Oracle states: "In this context, maximum query directive means that among multiple tables and indexes, the table or index that has the maximum DOP determines the parallelism for the query operation."

(You can find 11gR2's original documentation here and 12.1.0.2's here.)

But the results of the experiment below in 12.1.0.2 appears to suggest that rule 4 overrides the rule 3, in this version.

Tables:

SQL> CREATE TABLE SOURCE_DATA NOLOGGING PARALLEL 4 AS
SELECT ROWNUM ID , MOD(ROWNUM,5) PART_ID, ROUND(DBMS_RANDOM.VALUE(1,ROWNUM)*5,5) VALUE
FROM DUAL
CONNECT BY LEVEL < 1000001;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SOURCE_DATA');

SQL> CREATE TABLE DESTINATION_DATA AS
SELECT * FROM SOURCE_DATA WHERE 1=2;

Session:

SQL> ALTER SESSION ENABLE PARALLEL DML;

The experiment

SQL> ALTER TABLE DESTINATION_DATA PARALLEL 3;

As you can see below, there are no hints on the statement, and Parallel DML is merely enabled on he session. We are doing a simple direct-path insert into a table with parallel declaration of 3, and selecting from a table which has a parallel declaration of 4. So based on Oracle documentation we expect the DOP to be 3 (i.e. "Parallel declaration specification of the inserting table") despite the higher DOP on the source table.

Using DBMS_XPLAN.DISPLAY_CURSOR, we get:

PLAN_TABLE_OUTPUT                                                                             
-----------------------------------------------------------------------------------------------
SQL_ID  cwrqam2gbx7ds, child number 0                                                         
-------------------------------------                                                         
INSERT /*+ append TEST3 */ INTO DESTINATION_DATA SELECT * FROM                                
SOURCE_DATA                                                                                   
                                                                                              
Plan hash value: 2115782672                                                                   
                                                                                              
-----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |             |       |        |      |            |
|   1 |  PX COORDINATOR                    |             |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000    |  1000K|  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|             |       |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |             |  1000K|  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |             |  1000K|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL            | SOURCE_DATA |  1000K|  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   6 - access(:Z>=:Z AND :Z<=:Z)                                                              
                                                                                              
Note                                                                                          
-----                                                                                         
   - Degree of Parallelism is 4 because of table property                                     
                                                                                              
 28 rows selected

WHAT?! DOP is 4?!

Not so fast though ... #sigh

During the comparison of the behavior between 11g and 12c, I kept checking the V$SQL.PX_SERVERS_EXECUTIONS, mainly because the DBMS_XPLAN.DISPLAY_CURSOR was not giving any hints on the employed DOP.

I hadn't done it for 12.1.0.2 because the output seemed to have enough information in that regard. But Martin Widlake suggested I do the same, to be consistent.

SQL> SELECT sql_id, child_number, PX_SERVERS_EXECUTIONS
FROM v$sql
WHERE sql_text LIKE '%TEST3%';

SQL_ID        CHILD_NUMBER PX_SERVERS_EXECUTIONS
------------- ------------ ---------------------
cwrqam2gbx7ds            0                     3

There you go! Oracle's documentation was not wrong after all. The DOP is in fact equal to the parallel declaration on the inserting table (i.e. 3).

One last blow came from OEM. You can clearly see the parallel servers involved in the INSERT...SELECT.

Just a note that seeing only one set of parallel servers proves Oracle's statement that when the decision is made to do both INSERT & SELECT in parallel, ONE parallel directive is used to carry out the operation; i.e. both operation use the same DOP.

Conclusion

Don't be deceived by the glamor of the extra information which is presented in 12.1.0.2. DBMS_XPLAN can lie.

Here I have to mention that I had also sought Jonathan Lewis' opinion about the post.

I'd like to quote this from Jonathan's response, when I got back to him to say that I was wrong:

 "There's always one more thing to check."

IMPDP/EXPDP, Virtual Columns, and user-defined functions

If you have virtual columns based on user defined functions in a schema, you can not use IMPDP/EXPDP for that schema; not that easily, at least.

In this post I would lay out a semantic scheme for a test, and propose a successfully-tested work-around.

Required material for the scenario:

- A schema named VCOL_TEST, containing:
- A function named VCOL_FUNCTION
- A table named VCOL_TABLE

The script below can be used to create such schema:

create user vcol_test identified by vcol_test;

grant   create session,
        select any dictionary,
        create procedure,
        create table,
        debug connect session,
        debug any procedure
to vcol_test;

conn vcol_test/vcol_test;

create or replace function vcol_function(p_parameter number)
return number
deterministic
is
begin
  if p_parameter > 10 then
    return 10;
  else
    return 1;
  end if;
end vcol_function;
/

show errors;

create table vcol_table(id number, vcol as(vcol_function(id)));

exit;

What's the problem?

If you try to use IMPDP to create a "remapped" schema (e.g. VCOL_TEST2) on a different server, you would come across this:

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"VCOL_TEST2"."VCOL_TABLE" failed to create with error:
ORA-00904: "VCOL_TEST"."VCOL_FUNCTION": invalid identifier
Failing sql is:
CREATE TABLE "VCOL_TEST2"."VCOL_TABLE" ("ID" NUMBER, "VCOL" NUMBER GENERATED ALWAYS AS ("VCOL_TEST"."VCOL_FUNCTION"("ID")) VIRTUAL VISIBLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS"
…
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s)

What's happening here?

Checking the "Failing sql" would manifest the bug: IMPDP is trying to create table VCOL_TEST2.VCOL_TABLE referring to function VCOL_TEST.VCOL_FUNCTION (as opposed to VCOL_TEST2.VCOL_FUNCTION).

i.e. IMPDP is not able to remap the owner of the function used for the virtual column, to the new schema name.

What does Oracle say about this?

Officially, nothing.

Although a consulting member of technical staff at Oracle was kind enough to send the following response:

"In releases prior to 11.2.0.4, a table with a virtual column based on the result of a PL/SQL function would not be recreated successfully on Import, even when REMAP_SCHEMA was not used. This is because the necessary PL/SQL function would not have been created before the table was created.

The problem was addressed by modifying Import to create the table with a temporary definition for virtual columns, then creating the function, then modifying the definition of the virtual columns to use the correct function.

The initial fix was in BUG#10186633 and this was later extended in BUG#14506804. The extended fix is also included in certain patch set updates and one-off patches for 11.2.0.2 and 11.2.0.3.

Unfortunately this fix does not address the problem of REMAP_SCHEMA. Even in 11.2.0.4.0 or 12.1.0.1.0, it is still possible to encounter a failure during Import, or a failure during runtime, depending on the privileges of the owner of the target schema and the existence of the source schema.

The work-around

Since re-importing and remapping existing test schemas is part of our daily tasks, I have developed a bash script which follows these steps:

In Export:
1. Create a list of user defined functions which are used in virtual columns, and according to their dependencies, use DBMS_METADATA to create a SQL script which can recreate the functions with no reference to the schema name.

2. Create a list of tables whose virtual columns rely on the exported functions, and (using DBMS_METADATA, again) build a schema-independent SQL script to recreate them.

3. Obviously you would need to build another script to grant quotas on different tablespaces that would hold the concerning tables, to the new user/schema.
4. Export as usual, using EXPDP.

In Import:
1. Create the new schema, grant all the privileges and quotas according to the source schema.
2. Use the script created in step 1, above, to create the functions.
3. Use the script created in step 2, above, to create the concerning tables.
4. IMPDP everything EXCLUDING the tables mentioned in the previous step. Ignore the "already exists" messages during the process.
5. IMPDP DATA_ONLY for the tables mentioned in previous step.

IMPDP is done in two passes, due to the fact that the existence of the already-created concerning tables (in step 3 of Import) would definitely lead to "already exist" errors, which would prevent importing the data for the tables.

Note: The method above only works, if -like us- the concerning tables do not hold any referential constraints to any other tables (our "solution" is a datawarehouse and the concerning tables are "fact" tables whose integrity constraints -if any- are enforced through application).

If they did, you might have to make sure that their "child" tables -if any- are imported after they are.

Final Note

The same problem could occur for function-based indexes as well, and it does not seem to be an issue that Oracle DBAs can overlook.

Although Virtual Columns are extremely helpful, But as Matthias Rogel explains in an example their implementation has not been without issues.

Fortunately this time, based on an Oracle insider, the Datapump team are aware of the bug.

There's still hope!