Today, the good news is that I have time to write this blog post. The less good news is that our basement got flooded and I have to stay home. The bad news is, my current client does not allow remote work 🙁
So, blog post it is…

There are a number of reasons why a SQL Plan Baseline might not get used. Here’s one I was not fully aware of until recently (although it makes perfect sense when you think about it): ALTER SESSION FORCE PARALLEL QUERY [PARALLEL n].
In the simplest of cases the outcome whether a SQL Plan Baseline is used depends on the following:

• PARALLEL_DEGREE_POLICY parameter
• table decoration (DEGREE)
• optimizer enviroment used to generate the plan baseline (ENABLE PARALLEL / FORCE PARALLEL / FORCE PARALLEL n)
• optimizer environment of the session executing the query (ENABLE PARALLEL / FORCE PARALLEL / FORCE PARALLEL n)
• plan in the baseline (serial or parallel plan?)

The base of the test case is a simple table that I’m going to select:

create table t1
as
select rownum id, 'ABC' text
from dual connect by level <= 100
;

For every combination of interest we run through the following procedure:
1) open and configure a new session for the parse environment on which the plan baseline is based
2) run this query “select * from t1”
3) create a fixed plan baseline for the generated plan
4) re-connect and configure the parse environment for the executing session
5) run query from step 2) and collect cursor information
do steps 4) and 5) for ENABLE PARALLEL, FORCE PARALLEL, and FORCE PARALLEL n

Test 1: object DEGREE 1, parallel_degree_policy = manual

parsing session (SPB) / executing session	enable (serial plan)	force (parallel plan)	force 4 (parallel plan)
enable (default)	used	not used (*3)	not used (*3)
force	not used (*1)	used	used
force 4	not used (*2)	used	used

*1) Degree of Parallelism is 8 because of table property
*2) Degree of Parallelism is 4 because of session
*3) No note in the plan about DOP or baseline
Summary for test 1:
If you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
If you have a parallel plan in the baseline and run the query on a session with ENABLE PARALLEL QUERY (default settings) the plan baseline is not used and you get a serial plan.

Test 2: object DEGREE 1, parallel_degree_policy = limited

parsing session (SPB) / executing session	enable (serial plan)	force (parallel plan)	force 4 (parallel plan)
enable (default)	used	used	used (*3)
force	not used (*1)	used	used (*3)
force 4	not used (*2)	used	used

*1) automatic DOP: Computed Degree of Parallelism is 2
*2) Degree of Parallelism is 4 because of session
*3) Degree of Parallelism is 2 because of hint
Summary for test 2:
If you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
Now that we allow for auto DOP the session with ENABLE PARALLEL QUERY can use parallel plans in plan baselines.

Test 3: object DEGREE DEFAULT, parallel_degree_policy = limited

parsing session (SPB) / executing session	enable (serial plan (*4))	force (parallel plan)	force 4 (parallel plan)
enable (default)	used	used	used (*3)
force	not used (*1)	used	used (*3)
force 4	not used (*2)	used	used

*1) automatic DOP: Computed Degree of Parallelism is 2
*2) Degree of Parallelism is 4 because of session
*3) Interestingly, there is no note about DOP in the plan at all. But it uses the plan baseline.
*4) automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Summary for test 3:
If you have a serial plan in the baseline and use any force parallel on the session the plan baseline is not used and you get a parallel plan.
Again, as we allow for auto DOP the session with ENABLE PARALLEL QUERY can use parallel plans in plan baselines. The result is the same as Test 2 but some the notes in the plans differ.

Test 4: object DEGREE DEFAULT, parallel_degree_policy = limited, fake stats so DOP > 1 for all plan baselines

parsing session (SPB) / executing session	enable (parallel plan (*1))	force (parallel plan)	force 4 (parallel plan)
enable (default)	used	used	used
force	used	used	used
force 4	used	used	used

*1) automatic DOP: Computed Degree of Parallelism is 2
Summary for test 4:
Naturally, now that we always have parallel plans in the plan baselines and the object statistics call for auto DOP > 1 the plan baselines get used in all cases.

Why did I do this? See, there’s this batch job with a SQL that has a SQL Plan Baseline on it (serial plan). Now, every once in a while the run-time of this batch job goes through the roof and every time this happens I see that the query does not use the baseline (v$sql.sql_plan_baseline is NULL). Also, next to different PLAN_HASH_VALUEs I noticed different OPTIMIZER_ENV_HASH_VALUEs. Checking the session settings V$SES_OPTIMIZER_ENV showed that “parallel_query_forced_dop” was set to “default”, which means “ALTER SESSION FORCE PARALLEL QUERY” was run previously on that session.
But why is it not deterministic? The tool that runs all the batch jobs uses a connection pool, some job steps force parallel and some don’t. We haven’t been able to technically confirm this but everything points towards that this session property is not cleared to default when a connection gets reused. So, sometimes this batch job just gets unlucky by the session it gets from the connection pool.
The solution: Adding second SQL Plan Baseline. This plan is a parallel plan with the same structure as the original serial plan. Now, either one of the plan baselines (serial or parallel plan) is being used depending on the session configuration.

Footnote:
When you use “FORCE PARALLEL QUERY” you might get a serial plan. You’ll see this in the plan notes: “automatic DOP: Computed Degree of Parallelism is 1”. Obviously, this would change some of above results.

This is a short note about a limitation in complex view merging for outer joins.

We start with two simple tables, t1 and t2. To show the effect we don’t even need to load any data.

create table t1 (
    id1 number not null
  , vc1 varchar2(200)
);

create table t2 (
    id1 number not null
  , id2 number not null
  , num1 number
);

I know, I said it’s about outer joins but let’s first check the execution plan for the INNER JOIN.

explain plan for
select *
from t1
  inner join (
    select /*+ merge */
        id2
      , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;

select * from dbms_xplan.display();

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   153 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     1 |   153 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |   153 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |   127 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Oracle merges the inline view as instructed by the MERGE hint. Btw., I’m only using the hint for demonstration purposes.

What happens if we change the join to a LEFT OUTER JOIN?

explain plan for
select *
from t1
  left outer join (
    select /*+ merge */
        id2
      , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;
select * from dbms_xplan.display();

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |   143 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |      |     1 |   143 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |     1 |   115 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    28 |     3  (34)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

The inline view is not merged anymore. The optimizer trace reveals why it cannot merge the view anymore:

CVM:   Checking validity of merging in query block SEL$2 (#2)
CVM:     CVM bypassed: View on right side of outer join contains view with illegal column.
CVM:     CVM bypassed: Externally referenced expressions are not merge-safe.
CVM:     CVM bypassed: view on right side of Outer Join + MuLTiple TABle.
CVM:     CVM bypassed: view on right side of Outer Join + MuLTiple TABle.

In case you haven’t noticed, there’s this little expression in the projection of the inner SELECT on line 7 (“0 x”). As soon as we remove it, the view will be merged by the optimizer also for LEFT OUTER JOIN.

explain plan for
select *
from t1
  left outer join (
    select /*+ merge */
        id2
--    , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;
select * from dbms_xplan.display();

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   153 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     1 |   153 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |     1 |   153 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |   127 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Thanks to point and click tools *cough* Cognos *cough* I’ve seen this a lot lately 😉

Footnote: tests run on 12.2.0.1

While analyzing a performance issue with a 63-table join query (you gotta love Siebel) I came accross a little optimizer oddity. Looking at a 600MB optimizer trace was fun, though 😉

The problem boiled down to this:

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    83 |  6225 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |           |    83 |  6225 |    10   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL  | T1        |     2 |    20 |     9   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2        |    41 |  2665 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T2_IDX_01 |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Would you expect the cardinality estimate for table access on T2 (plan Id 3) to be 41?
I certainly wouldn’t. It’s doing an INDEX UNIQUE SCAN on index T2_IDX_01 (plan Id 4) and according to the cardinality estimate on T2 (plan Id 2) it will do that INDEX UNIQUE SCAN two times.
Why does the optimizer think it will get 41 rows per given ID value in the index while obviously a UNIQUE INDEX SCAN can only return 0 or 1 ROWID?

From the large Siebel query I managed to deduce a simple test case:

create table t1 (
    id number(10) not null
  , id2 number(10) not null
  , id3 number(10)
  , constraint t1_pk primary key (id)
      using index (create unique index t1_idx_01 on t1 (id))
);

create table t2 (
    id number(10)
  , text varchar2(100)
  , constraint t2_pk primary key (id)
      using index (create unique index t2_idx_01 on t2 (id))
);

Only table T1 is populated with data. Column ID3 will be 50% NULL values, the other 50% will be “1”.

insert into t1 (id, id2, id3)
select
    rownum id
  , rownum id2
  , decode(mod(rownum, 2), 0, null, 1) id3
from dual connect by level <= 10000
;
commit;

-- gather stats on T1 and related indexes without histograms
exec dbms_stats.gather_table_stats(user, 'T1', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1')

And that’s the query which produced above execution plan:

select *
from t1
   , t2
where t1.id2 in (10, 20)
and t1.id3 = t2.id(+)
;

Perhaps you noticed that I didn’t gather statistics for table T2, which was exactly the sitution I had in the Siebel database. Several tables involved in the 63-table join did not have statistics on them.
In case you’re wondering, according to Oracle’s Siebel “best practice” you’re not supposed to have statistics on tables with less than 15 rows in them (see Oracle’s script coe_siebel_stats.sql v11.4.4.6).

Now, back to the orginal question: How does Oracle come up with 41?
First, for any table that does not have statistics Oracle seems to assume a cardinality of 82. I don’t know where that magic number comes from. Maybe it simply takes 1% of 8192, the default database block size.
The extract from optimizer trace shows table T2 is not analyzed and contains 82 rows:

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T2  Alias: T2  (NOT ANALYZED)
  #Rows: 82  SSZ: 0  LGR: 0  #Blks:  1  AvgRowLen:  100.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 0
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#1): ID(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 3 Nulls: 0 Density: 0.390244

...
SINGLE TABLE ACCESS PATH 
...
  Table: T2  Alias: T2
    Card: Original: 82.000000  Rounded: 82  Computed: 82.000000  Non Adjusted: 82.000000

Also, the optimizer guesses the NDV(3) and number of nulls(0) for the ID column of table T2.

… if you think it simply divides 82 by 2, read on 🙂 …

Secondly, after studying different data patterns I think this is what happens.
Because of above outlined assumptions the adjusted selectivty for T2 will always be 1 in the join selectivity calculation.
And, since we have a low NDV on T1.ID3 we end up with a gross misestimate for the join selectivity.

join-sel =
  ((num_rows(t1) - num_nulls(t1.id3) / num_rows(t1)) *
  ((num_rows(t2) - num_nulls(t2.id) / num_rows(t2)) /
  greater(num_distinct(t1.id3), num_distinct(t2.id))

join-sel =
  ((10000 - 5000 / 10000) *
  ((82 - 0 / 82) /
  greater(1, 3)
  = 0.16666666666666666666666666666667

From the optimzer trace we see that the join selectivity of 0.500000 does not exactly match our calculation. Interestingly, the optimizer seems to ignore the guessed NDV of 3 for T2.ID and instead use the NDV from T1.ID3, which would give you 0.5.

Outer Join Card:  83.000000 = max ( outer (2.000000),(outer (2.000000) * inner (82.000000) * sel (0.500000)))

So here it is, we’ve got our number 41: (82.000000) * sel (0.500000)

Note, the table access cardinality (plan Id 3) is based on the join selectivity which doesn’t account for the in-list predicate on T1, as one would expect. The in-list is accounted for in the filtered table cardinality of table T1 and so is reflected in the join cardinality (plan Id 1).

Lastly, the cardinality estimate for plan Id 3 (TABLE ACCESS BY INDEX ROWID) is independently calculated from plan Id 4 (INDEX UNIQUE SCAN). I think there should be a sanity check to adjust the estimate for the table access to T2 (plan Id 3) when the row source is fed by an INDEX UNIQUE SCAN.

Here’s another example:

insert into t1 (id, id2, id3)
select
    rownum id
  , rownum id2
  , decode(mod(rownum, 4), 0, null, dbms_random.value(1, 6)) id3
from dual connect by level <= 10000
;
commit;

-- gather stats on T1 without histograms
exec dbms_stats.gather_table_stats(user, 'T1', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1')

This time, column ID3 contains 25% NULL values, the other 75% are evenly distributed between “1” and “6”.

SQL> select column_name, histogram, num_distinct, density, num_nulls from dba_tab_columns where table_name = 'T1' order by column_name;

COLUMN_NAME                    HISTOGRAM       NUM_DISTINCT    DENSITY  NUM_NULLS
------------------------------ --------------- ------------ ---------- ----------
ID                             NONE                   10000      .0001          0
ID2                            NONE                   10000      .0001          0
ID3                            NONE                       6 .166666667       2500

So, according to above formulae & data it would go like this:

join-sel =
  ((10000 - 25000 / 10000) *
  ((82 - 0 / 82) /
  greater(6, 3)
  = 0.125

card = round(82 * 0.125) = 10

Again, the optimizer trace confirms the calculation, this time it’s spon-on because it again uses the NDV from T1.ID3 (which is greater than 3 anyway):

Outer Join Card:  21.000000 = max ( outer (2.000000),(outer (2.000000) * inner (82.000000) * sel (0.125000)))

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    21 |  1596 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |           |    21 |  1596 |    10   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL  | T1        |     2 |    22 |     9   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2        |    10 |   650 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T2_IDX_01 |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

The case for the Siebel query was little more complex but ulitmately it was the magic number 82 that caused the optimizer to choose a inefficient join order.

I recently had various cases where functions/procedures of DBMS_QOPATCH raised “ORA-20001: Latest xml inventory is not loaded into table” on Windows:

• Platform: Windows Server 2012 R2
• Oracle Version: 12.1.0.2.11

The issue can best be seen from SQL*Plus:

SQL> select dbms_qopatch.get_opatch_install_info from dual;
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: in "SYS.DBMS_QOPATCH", line 1937
ORA-06512: in "SYS.DBMS_QOPATCH", line 133

Another symptom of the issue can be found during instance startup when there is no patch information dumped to the alert.log, even though you know there are patches installed.
Extract from alert.log:

===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================

Trust me, this database has patches installed 🙂
So, what’s going on…

DBMS_QOPATCH writes a log in %ORACLE_HOME%\QOpatch\qopatch_log.log. Here’s what it says:

 LOG file opened at 05/02/16 21:54:17

KUP-05004:   Warning: Intra source concurrency disabled because parallel select was not requested.

KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table OPATCH_XML_INV
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

	XML_INVENTORY                   CHAR (100000000)
	  Terminated by "UIJSVTBOEIZBEFFQBL"
	  Trim whitespace same as SQL Loader
KUP-04004: error while reading file C:\app\oracle\product\ora1210211\QOpatch\qopiprep.bat
KUP-04017: OS message: The operation completed successfully.
KUP-04017: OS message: Argument(s) Error... Cannot use file "C:\app\oracle\product\ora1210211\QOpatch\xml_file.xml" to generate XML output.
Specify path/filename and make sure filena
KUP-04118: operation "read_pipe", location "skudmir"

Alright, DBMS_QOPATCH calls “qopiprep.bat” as a pre-processor which in turn tries to write (and at the very end delete) a file named “xml_file.xml”. So, this file is used temporarily and should not exist when there’s currently no call to DBMS_QOPATCH running. When I checked the file was there and Process Explorer revealed that there were multiple “cmd.exe” processes having an open file handle, thus locking the file from deletion.

Most of these “cmd.exe” processes were spawned by “oracle.exe” and were not doing any work anymore. I went and killed all of them, one after another. Just be careful and make sure these processes are actually “zombies”. After killing these “cmd.exe” processes there were no locks on “xml_file.xml” anymore and DBMS_QOPATCH worked again as expected.

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-6f58d48c-880c-45ab-88b4-5831abc60f31</UId><targetTyp

Unfortunately, I haven’t been able to reproduce the issue at will. For the moment I’m fine having a solution.

Remember, datapatch/sqlpatch uses the same funcionality of DBMS_QOPATCH…so applying a patch could fail as well.