Outer join Filter Push Down (FPD)

Version is 12.1.0.2. Given the below tables:

drop table t1 purge;
drop table t2 purge;

create table t1 (id1 number, id2 number, x varchar2(100));
create table t2 (id1 number, id2 number, x varchar2(100));

insert into t1 select rownum, rownum, lpad('X', 100) from dual connect by level <= 1e5; 
insert into t2 select rownum, NULL, lpad('X', 100) from dual connect by level <= 1e5;  

commit; 

create index t1_id1 on t1(id1); 
create index t1_id2 on t1(id2); 
create index t2_id1 on t2(id1); 
create index t2_id2 on t2(id2); 

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=> NULL, cascade=> true);
exec dbms_stats.gather_table_stats(user, 'T2', estimate_percent=> NULL, cascade=> true);

Query 1:

select /*+ GATHER_PLAN_STATISTICS USE_CONCAT */ * from t1, t2
where t1.id2 = t2.id1(+) and (t1.id1 = 1 or t2.id2 = 1)

Plan hash value: 3676062550

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |        |      1 |        |      1 |00:00:00.08 |    3221 |       |       |          |
|   1 |  CONCATENATION                         |        |      1 |        |      1 |00:00:00.08 |    3221 |       |       |          |
|*  2 |   FILTER                               |        |      1 |        |      0 |00:00:00.08 |    3213 |       |       |          |
|*  3 |    HASH JOIN RIGHT OUTER               |        |      1 |   1000 |    100K|00:00:01.12 |    3213 |    16M|  2201K|   17M (0)|
|   4 |     TABLE ACCESS FULL                  | T2     |      1 |    100K|    100K|00:00:00.23 |    1578 |       |       |          |
|   5 |     TABLE ACCESS FULL                  | T1     |      1 |    100K|    100K|00:00:00.16 |    1635 |       |       |          |
|*  6 |   FILTER                               |        |      1 |        |      1 |00:00:00.01 |       8 |       |       |          |
|   7 |    NESTED LOOPS OUTER                  |        |      1 |      1 |      1 |00:00:00.01 |       8 |       |       |          |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|*  9 |      INDEX RANGE SCAN                  | T1_ID1 |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|  10 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
|* 11 |      INDEX RANGE SCAN                  | T2_ID1 |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."ID2"=1)
   3 - access("T1"."ID2"="T2"."ID1")
   6 - filter(LNNVL("T2"."ID2"=1))
   9 - access("T1"."ID1"=1)
  11 - access("T1"."ID2"="T2"."ID1")

Query 2:

select /*+ GATHER_PLAN_STATISTICS */ * from t1, t2 
where t1.id2 = t2.id1(+) and (t1.id1 = 1 and t2.id2 = 1) 
union 
select * from t1, t2
where t1.id2 = t2.id1(+) and (t1.id1 = 1 and lnnvl(t2.id2 = 1))

Plan hash value: 3122228532

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |        |      1 |        |      1 |00:00:00.01 |      10 |       |       |          |
|   1 |  SORT UNIQUE                            |        |      1 |      2 |      1 |00:00:00.01 |      10 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                             |        |      1 |        |      1 |00:00:00.01 |      10 |       |       |          |
|   3 |    NESTED LOOPS                         |        |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|   4 |     NESTED LOOPS                        |        |      1 |      1 |      0 |00:00:00.01 |       4 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  6 |       INDEX RANGE SCAN                  | T1_ID1 |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |      INDEX RANGE SCAN                   | T2_ID2 |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID         | T2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |    FILTER                               |        |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|  10 |     NESTED LOOPS OUTER                  |        |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|  11 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |       INDEX RANGE SCAN                  | T1_ID1 |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  13 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 14 |       INDEX RANGE SCAN                  | T2_ID1 |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1"."ID1"=1)
   7 - access("T2"."ID2"=1)
   8 - filter("T1"."ID2"="T2"."ID1")
   9 - filter(LNNVL("T2"."ID2"=1))
  12 - access("T1"."ID1"=1)
  14 - access("T1"."ID2"="T2"."ID1")

Query 3:

select /*+ GATHER_PLAN_STATISTICS USE_CONCAT */ * from t1, t2 
where t1.id2 = t2.id1 and (t1.id1 = 1 or t2.id2 = 1)

Plan hash value: 2977095197

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |        |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  CONCATENATION                         |        |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS                         |        |      1 |      1 |      0 |00:00:00.01 |       1 |
|   3 |    NESTED LOOPS                        |        |      1 |      1 |      0 |00:00:00.01 |       1 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  5 |      INDEX RANGE SCAN                  | T2_ID2 |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  6 |     INDEX RANGE SCAN                   | T1_ID2 |      0 |      1 |      0 |00:00:00.01 |       0 |
|   7 |    TABLE ACCESS BY INDEX ROWID         | T1     |      0 |      1 |      0 |00:00:00.01 |       0 |
|   8 |   NESTED LOOPS                         |        |      1 |      1 |      1 |00:00:00.01 |       8 |
|   9 |    NESTED LOOPS                        |        |      1 |      1 |      1 |00:00:00.01 |       7 |
|  10 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |      1 |      1 |00:00:00.01 |       4 |
|* 11 |      INDEX RANGE SCAN                  | T1_ID1 |      1 |      1 |      1 |00:00:00.01 |       3 |
|* 12 |     INDEX RANGE SCAN                   | T2_ID1 |      1 |      1 |      1 |00:00:00.01 |       3 |
|* 13 |    TABLE ACCESS BY INDEX ROWID         | T2     |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T2"."ID2"=1)
   6 - access("T1"."ID2"="T2"."ID1")
  11 - access("T1"."ID1"=1)
  12 - access("T1"."ID2"="T2"."ID1")
  13 - filter(LNNVL("T2"."ID2"=1))

Seems like an outer join prevents Filter Push Down (FPD), and that results in plan that is ineffective. The database performs all the work for nothing, as the filter nullifies the result on that branch.

Oracle 12c Flex Cluster Installation in VirtualBox

Installing a flex cluster requires additional configuration, because GNS (Grid Naming Service) is mandatory for this new type of Oracle cluster that was introduced in Oracle Grid Infrastructure 12c. For this specific setup I have used VirtualBox with 5 virtual machines: 4-node cluster and a DHCP/DNS server VM. I don’t want to go into the details of the regular parts of the installation, this post is mainly about the GNS configuration (DHCP and DNS) and the new parts in the installation.

Read more of this post

Oracle Database 12c New Features – Data Redaction

Introduction

Starting with Oracle Database version 12.1.0.1 (and 11.2.0.4!) the Advanced Security option contains a new feature: Data Redaction. Data Redaction provides in-built support for hiding sensitive data on-the-fly in query results on certain conditions.

Read more of this post

Installing Oracle Linux 6 in VirtualBox

Oracle Linux ISOs can be downloaded at: http://edelivery.oracle.com/linux

Virtual Machine Setup

Create a new Virtual Machine:

Read more of this post

Enterprise Manager Cloud Control 12.1.0.4 upgrade and downgrade

This post is about upgrading Enterprise Manager Cloud Control from version 12.1.0.3 to 12.1.0.4 with a fallback plan. As of now, there is no supported method for downgrading Enterprise Manager Cloud Control, so actually this is carried out by making the required backups, and reverting to the older state from them.

Preparations

One of the requirements for upgrading is to copy the emkey to the repository database:

$ export OMS_HOME=/oracle/base/product/mw12103/oms
$ $OMS_HOME/bin/emctl config emkey -copy_to_repos_from_file -repos_host emdb.sequoia.vbox -repos_port 1521 -repos_sid emrep -repos_user sysman -emkey_file $OMS_HOME/sysman/config/emkey.ora

Next export the OMS configuration, which should be part of the regular backups also. The file created by this operation can be used to recreate the OMS from a software-only installation in case the binaries are lost.

$ $OMS_HOME/bin/emctl exportconfig oms

Read more of this post

Adding host and database target in Cloud Control

On the top right, select Setup, Add Target, Add Targets Manually:

Adding target manually

Adding target manually

Select Add Host Targets:

Add Host Targets

Add Host Targets

Click Add, in the new empty line, type the hostname, and select the platform, then click Next:

Add, type hostname and select platform

Add, type hostname and select platform

Specify:

  • Installation Base Directory
  • Instance Directory
Specify installation parameters

Specify installation parameters

Create new Named Credential:

Create new Named Credential

Create new Named Credential

In order for the above to work, on the host emdb.sequoia.vbox, I have changed the following in /etc/sudoers:

  • disable requiretty:
Defaults    !requiretty
  • enable sudo without password for oracle:
oracle          ALL=(ALL)       NOPASSWD: ALL

Summary before deploying the agent:

Summary before deploying the agent

Summary before deploying the agent

Summary after deploying the agent:

Summary after deploying the agent

Summary after deploying the agent

Next step is to discover the targets on the newly added host. On the top right, select Setup, Add Target, Configure Auto Discovery:

Configure Auto Discovery

Configure Auto Discovery

Select All Discovery Modules:

Select All Discovery Modules

Select All Discovery Modules

Select the newly added host, then Configure Discovery Modules:

Select the newly added host, then Configure Discovery Modules

Select the newly added host, then Configure Discovery Modules

Enable the required plugins:

Enable the required plugins

Enable the required plugins

Run Discovery for the newly added host:

Run Discovery for the newly added host

Run Discovery for the newly added host

View the discovered targets (click on the number):

View the discovered targets

View the discovered targets

Select the database instance, then promote the target:

Select the database instance, then promote the target

Select the database instance, then promote the target

Select the database, provide the password for DBSNMP, and select the related listener:

Select the database, provide the password for DBSNMP, and select the related listener

Select the database, provide the password for DBSNMP, and select the related listener

Review before promoting the targets:

Review before promoting the targets

Review before promoting the targets

Targets promoted successfully:

Targets promoted successfully

Targets promoted successfully

INDEX SKIP SCAN not considered

This post was inspired by the below question:

http://dba.stackexchange.com/questions/64880/why-does-the-cbo-not-chose-a-lower-cost-skip-scan-without-stats

Short version: INDEX SKIP SCAN is not considered by the optimizer, when optimizer statistics are missing.

Let’s go with the example from the above post, the database version is 12.1.0.1.

drop table foo purge;

create table foo( bar integer, baz integer, qux char(99), constraint pk_foo primary key (bar, baz) );

insert into foo(bar,baz) 
with g as (select dummy from dual connect by level <= 1000)
select mod(rownum,1000), rownum from g, g;

commit;

Do not collect statistics at this point, as it is a key point for the further. The query we want to run (explain) is:

select * from foo where baz=1

Explaining the query:

explain plan for select * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1245013993

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    22 |  2794 |   610   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| FOO  |    22 |  2794 |   610   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The optimizer decided to perform a FTS on foo. Let’s try again with the INDEX_SS hint:

explain plan for select /*+ INDEX_SS(FOO PK_FOO) */ * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 790150314

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    22 |  2794 |   486   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO    |    22 |  2794 |   486   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | PK_FOO |  4067 |       |   421   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

With the INDEX_SS hint, we force an INDEX SKIP SCAN, and the cost is even lower than before. Then why wasn’t the INDEX SKIP SCAN chosen originally? It’s time to trace the optimizer.

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
explain plan for select * from foo where baz=1;
alter session set events '10053 trace name context forever, off';

Here are the relevant parts from the trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FOO  Alias: FOO  (NOT ANALYZED)
  #Rows: 164014  #Blks:  2008  AvgRowLen:  100.00  ChainCnt:  0.00
Index Stats::
  Index: PK_FOO  Col#: 1 2    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00

Values like “#Rows: 164014″, “#DK: 100″ (Distinct Keys) or “CLUF: 800.00″ (Clustering Factor) are definitely inaccurate, even without checking them. We can also see “(NOT ANALYZED)”, since we didn’t collect statistics on the table and the index.

Access path analysis for FOO
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for FOO[FOO]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

*** 2014-06-11 21:11:51.049
** Performing dynamic sampling initial checks. **
  Column (#2): BAZ(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 5125 Nulls: 0 Density: 0.000195
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: PK_FOO, blocks=4150
** Dynamic sampling updated table stats.: blocks=2008

*** 2014-06-11 21:11:51.049
** Generated dynamic sampling query:
    query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("FOO") FULL("FOO") NO_PARALLEL_INDEX("FOO") */ 1 AS C1, CASE WHEN "FOO"."BAZ"=1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."FOO" SAMPLE BLOCK (3.137450 , 1) SEED (1) "FOO") SAMPLESUB

Here we can see dynamic sampling kicking in because of the missing statistics.

*** 2014-06-11 21:11:51.053
** Executed dynamic sampling query:
    level : 2
    sample pct. : 3.137450
    actual sample size : 31898
    filtered sample card. : 0
    orig. card. : 164014
    block cnt. table stat. : 2008
    block cnt. for sampling: 2008
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000
** Using dynamic sampling card. : 1016685
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00002173
  Table: FOO  Alias: FOO
    Card: Original: 1016685.460317  Rounded: 22  Computed: 22.09  Non Adjusted: 22.09
  Access Path: TableScan
    Cost:  610.10  Resp: 610.10  Degree: 0
      Cost_io: 604.00  Cost_cpu: 237971185
      Resp_io: 604.00  Resp_cpu: 237971185
kkofmx: index filter:"FOO"."BAZ"=1

Above is the result of dynamic sampling, and the cost for FTS. And below is the interesting part. Though INDEX_SKIP_SCAN is mentioned, it is not even considered. The optimizer examined the cost of INDEX FULL SCAN, found it higher than TABLE ACCESS FULL, so it chose the later as the best access path.

SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SKIP_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (FullScan)
    Index: PK_FOO
    resc_io: 4216.00  resc_cpu: 30525891
    ix_sel: 1.000000  ix_sel_with_filters: 0.004000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 4218.09  Resp: 4218.09  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 610.10  Degree: 1  Resp: 610.10  Card: 22.09  Bytes: 0

Let’s check the hidden parameters related to INDEX SKIP SCAN:

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b 
where a.indx = b.indx
and a.ksppinm like '\_%skip_scan%' escape '\'
order by name;

NAME                           VALUE DESCRIPTION
------------------------------ ----- ----------------------------------------------------------------------
_optimizer_skip_scan_enabled   TRUE  enable/disable index skip scan
_optimizer_skip_scan_guess     FALSE consider index skip scan for predicates with guessed selectivity

According to “_optimizer_skip_scan_enabled”, the INDEX SKIP SCAN is enabled. The second parameter however, disables INDEX SKIP SCAN when the selectivity for the predicate is guessed, which is the default behaviour. Let’s test it.

alter session set "_optimizer_skip_scan_guess" = true;

explain plan for select * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 790150314

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    22 |  2794 |   486   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO    |    22 |  2794 |   486   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | PK_FOO |  4067 |       |   421   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

As expected, the optimizer chose INDEX SKIP SCAN this time, even without statistics (indicated by dynamic sampling).

 

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: