JPPD does not occur for a view with UNION-ALL

Here is a small test case to demonstrate the problem ( on 11.2.0.4)

create table t1(a number);

create table t2(a number);

create index idx_t2 on t2(a);

create view v as

(

select * from t1

UNION ALL

select * from t2

)

create table t3(a number);

insert into t1 values(1);

insert into t2 values(2);

insert into t3 values(1);

commit;

exec dbms_stats.gather_table_stats(user,'T1')

exec dbms_stats.gather_table_stats(user,'T2')

exec dbms_stats.gather_table_stats(user,'T3')

explain plan for

select * from v where a in (select a from t3);

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |     1 |    16 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI              |      |     1 |    16 |     6   (0)| 00:00:01 |

|   2 |   VIEW                       | V    |     2 |    26 |     4   (0)| 00:00:01 |

|   3 |    UNION-ALL                 |      |       |       |            |          |

|   4 |     TABLE ACCESS STORAGE FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |

|   5 |     TABLE ACCESS STORAGE FULL| T2   |     1 |     3 |     2   (0)| 00:00:01 |

|   6 |   TABLE ACCESS STORAGE FULL  | T3   |     1 |     3 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"="A")

Looking at the predicates, view is evaluated without any filters or predicates. If my view returns billions of rows, those rows are evaluated first and then a semi join is performed. This is very inefficient. I expect sub-query to be executed first, transformed into a view and that be joined with the view V and join predicates should be pushed down. If the predicates are pushed, you would see smart scans (on Exadata) or Index Range Scan.

There is bug on 11.2.0.4 which should have been fixed on 12.2.

Bug 18798414: JPPD NOT HONOURED ON VIEW WITH UNION, WHEN EXECUTION STARTS WITH SUBQUERY

I did test on 12.2 but the issue still remains. I will be testing this on 18c and 19c soon.

For now, you can rewrite the query as shown below and this provides a good workaround .

select * from T1 where a in  (select a from t3);

UNION ALL

select * from T2 where a in  (select a from t3);

This query takes advantage of JPPD.

Update On 7-SEP-2019 : I followed this up with Oracle support. There are certain heuristics that disables JPPD when one of the tables does not have indexes. Like in the example shown above T1 does not have an index.  But JPPD will still be useful without indexes, so that one branch can take advantage of smart scans (on Exadata) and other branch can benefit from indexes.

Here is the solution to get this working on 11.2.0.4

1) get backport of Bug:23246817 for 11.2.0.4.0

-> this gets heuristic from Bug:19803410 in its latest improved version

2) get backport of Bug:18798414 for 11.2.0.4.0

3) get merge patch of 23246817 with 18798414 and install it

-> these two fixes have common files so we need a merge

4) set the following in the session where the JPPD is needed

alter session set "_fix_control"='4569940:0','19803410:0','16053273:1';

Multi Threading with PL/SQL code to improve the Performance

Purpose of this document is achieve multi threading within a PL/ SQL code to improve the performance.

Consider this scenario

for i IN ( 1..100)

  LOOP

  {

      f(i) ;

  }

Function f is executed serially, 100 times one after another. Starting with 11gR2 we have the dbms_parallel_execute utility, this divides the work into smaller chunks and different sessions ( on different RAC instances) would execute the function concurrently ! Isn't this a cool new feature ?

Here is a quick little demo on how to get this working

SQL> create table T1 ( ID NUMBER);

-- Insert 4 rows into T1

SQL> insert into T1 values(1);

SQL> insert into T1 values(2);

SQL> insert into T1 values(3);

SQL> insert into T1 values(4);

SQL> commit;

SQL> create table T2 (A NUMBER);

-- Create function F that will insert a row into T2. This is the function which we would be running  concurrently. 

CREATE OR REPLACE PROCEDURE F ( start_id IN number, end_id IN number)

AS

BEGIN

FOR sql_rec IN (SELECT ID FROM T1 WHERE ID BETWEEN start_id and end_id)

LOOP

INSERT INTO T2 VALUES(9);

END LOOP;

END;

/

-- This is the real code. 

BEGIN 

    

-- Create a parallel execute task   

      

dbms_parallel_execute.create_task('MULTI_THREAD_TASK');

-- Create chunks by number col.  

DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col

                             (task_name => 'MULTI_THREAD_TASK',

                              table_owner => 'VISHAL',

                              table_name => 'T1',

                              table_column => 'ID',

                              chunk_size => 1);

-- Run the task with parallel level 4.

dbms_parallel_execute.run_task

                        ( task_name => 'MULTI_THREAD_TASK',

                          sql_stmt  => 'begin  F( :start_id, :end_id ); end;',

                          language_flag  => DBMS_SQL.NATIVE,

                          parallel_level => 4);

-- Just in case you want to drop the task.                                   

-- dbms_parallel_execute.drop_task('MULTI_THREAD_TASK' );

END;

/

You can query DBA_PARALLEL_EXECUTE_CHUNKS view to get the job names and then map it to  dba_scheduler_job_run_details. You would get the instance ID and session ID details. By default Oracle would distribute the work in a uniform way across all the RAC instances.

If in case you want all the concurrent sessions to run on a single node, create a service that runs only on that specific node. Then create a new job class and assign the service to it. And then run the task with the job class you just created.

How on earth can a single row insert take an hour to complete ?

I came across this very intriguing issue recently. Customer reported that a single row insert into a cluster table was taking more than an hour to complete.

This post assumes that you have a basic understanding of Table Clusters. Let's understand how Oracle handles a insert into Cluster Tables. And that there is a serious design flaw here.
Here is a small test case to demonstrate the problem. 

Version : 11.2.0.4

Additional Info : Table reside in NON-ASSM managed Tablespace (Similar issues occur in ASSM managed tablespaces as well)

-- Create a Cluster. No size clause specified. This implies that we can have just one cluster key value per block

SQL> CREATE CLUSTER ABC_C1 (C1 number(5));

-- Create Cluster Index

SQL> CREATE INDEX ABC_C1_IX ON CLUSTER ABC_C1 ;

-- Create table

SQL> create table abc1 (c1 number(5),C2 CHAR(1000), C3 CHAR(1000)) cluster ABC_C1(c1);


-- Now INSERT 1000 rows ( 2 times) with Cluster key Value 1-1000

( ** This PL SQL block is executed two times **)

BEGIN
FOR i in 1..1000
LOOP
insert into abc1 values(i,'A','A');
END LOOP;
END;
/

/

SQL> commit;

-- Analyze the table

SQL> ANALYZE TABLE ABC1 COMPUTE STATISTICS;

SQL> SELECT BLOCKS,NUM_FREELIST_BLOCKS FROM USER_TABLES;

BLOCKS             NUM_FREELIST_BLOCKS
----------           ---------------------------
1000                             1000

Now we have 2,000 rows spanned over 1000 blocks. Each block having 2 rows for the same cluster key value.Each block has some free space in it because the average row length is 2k and block size is 8k. Therefore we have 1000 blocks in the FREELIST.

-- 

SQL> alter system flush buffer_cache;

-- Now insert a new cluster key Value

SQL> set autotrace traceonly exp stat
SQL>
SQL>
SQL> insert into abc1 values (12000,'H','H');

1 row created.

Statistics
----------------------------------------------------------
2 recursive calls
2019 db block gets
4 consistent gets
1021 physical reads
1961796 redo size
839 bytes sent via SQL*Net to client
796 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

As you can see, a single row insert has done 1021 physical reads via db file sequential read. This is insane! Oracle reads 1021 data blocks for a simple insert. In real world scenarios - Big tables may have millions of blocks in the FREELIST. This can cause major performance issues where the number of blocks in the FREELIST is huge.

How Oracle handles this insert ?

To insert a new cluster key , Oracle must find free block in the segment.To find the free space, current behaviour is to scan through the ENTIRE free list. In this case, we have 1000 free blocks. Each of the blocks is read via "db file sequential read", but inserts cannot be performed in either of those blocks because we can have only 1 distinct cluster key value in one block. So, we now scanned through 1000 blocks and none of the blocks are eligible for an insert.  This is where performance takes a hit. Moreover, Oracle also unlinks all these 1000 blocks from the free list because none of them could accommodate the new insert.

How should Oracle actually handle this ?

For a new cluster key, Look into the index to figure out if value being inserted is a new key or not. If yes ( Like in our case) - Do not bother to look into the free list because those free blocks are anyways not eligible for inserts. So, for new cluster key values Oracle should directly allocate a new block.

Update

Oracle has fixed this issue. 

Bug 18938517 - Single row insert into a cluster table does a lot of "db file sequential read"

This fix is included in 12.2.0.1 and above. For lower versions, you need to request for a backport. 

This fix is not enabled by default. You need to set event 45051 to enable this fix.

SQL> alter system set events "45051 trace name context forever, level 50";

Level N is the number of blocks that Oracle probes in the free list before allocating a new block. Basically, this fix forces Oracle to not to scan the entire free list blocks. Read the first 50 blocks and see if it is eligible for an insert. Otherwise allocate a new block for the insert. 

You could choose any number between 1-10000. 

Join Elimination and potential risk of Wrong Results

Join Elimination is a query transformation feature introduced in 10gR2. This feature lives up to its name, Optimizer now has the ability to not to execute a join even if the SQL asks for it.

Let's look at this example. ( These tests were done on 12.1.0.2)

SQL> create table m(a number not null,n number not null, constraint m_pk primary key(n));

Table created.

SQL> create table d(b number not null,n number not null, constraint m_fk foreign key(n) references m(n));

Table created.

M-D has a parent-child relationship. All rows in table D reference exactly one row in table M

Now Consider this query.

SQL>  select d.b from m inner join d on m.n = d.n;

Plan hash value: 3387653791

----------------------------------

| Id  | Operation         | Name |

----------------------------------

|   0 | SELECT STATEMENT  |      |

|   1 |  TABLE ACCESS FULL| D    |

- - - - - - - - - - - - - - - - - 

In the SQL - you explicitly asked for a join (m.n = d.n ). But if you look at the execution plan Optimizer has not executed the join. It does a FULL TABLE SCAN on table D. This is legitimate - Optimizer is aware that all rows in table D reference exactly one row in table M. So there is no need to perform a join here.

For join elimination to happen certain conditions must be met.

1. There must be a PK-FK relationship

2. SQL must select columns ONLY from the child table.

If you select columns from parent table as well, JE does not kick in. 

SQL>  select m.a, d.b from m inner join d on m.n = d.n;

---------------------------------------------

| Id  | Operation                    | Name |

---------------------------------------------

|   0 | SELECT STATEMENT             |      |

|   1 |  NESTED LOOPS                |      |

|   2 |   NESTED LOOPS               |      |

|   3 |    TABLE ACCESS FULL         | D    |

|   4 |    INDEX UNIQUE SCAN         | M_PK |

|   5 |   TABLE ACCESS BY INDEX ROWID| M    |

---------------------------------------------

It is expected to see a nested loop join here. Join is executed by the Optimizer because you have to access both the tables to satisfy the query.

Interestingly, you see two NESTED LOOPS in the execution plan but our query has just one join condition.This is another 11g Optimization feature called Nested Loop Join Batching ( Controlled by the parameter_nlj_batching_enabled). Will talk more about this in some other blog post.

We now talk about certain parameters and constraint attributes that can potentially cause wrong results due to JE.

Consider this example

SQL> create table m(a number not null, n number not null, constraint m_pk primary key(n) rely enable validate);

Table created.

SQL> create table d(b number not null, n number not null, constraint m_fk foreign key(n) references m(n) rely disable novalidate);

Table created.

SQL> insert into d values(1,1);

1 row created.

SQL> commit;

Commit Complete

Note that I'm able to insert into table D even though value '1' does not exist in the parent table M. This is because my foreign key constraint is in a state of RELY DISABLE NOVALIDATE

SQL> select d.b from m inner join d on m.n = d.n;

no rows selected

This is correct result. Join condition is never met because value '1' does not exist in table M.

Looking at the execution plan

Plan hash value: 299975660

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|   1 |  NESTED LOOPS      |      |

|   2 |   TABLE ACCESS FULL| D    |

|   3 |   INDEX UNIQUE SCAN| M_PK |

-----------------------------------

JE elimination does not kick in. This is because we have a RELY NOVALIDATE constraint. 

Now alter the value of the parameter query_rewrite_integrity to TRUSTED.

SQL> alter session set query_rewrite_integrity=TRUSTED;

SQL> select d.b from m inner join d on m.n = d.n;

         B

----------

         1

 

( WRONG RESULTS)

Plan hash value: 3387653791

----------------------------------

| Id  | Operation         | Name |

----------------------------------

|   0 | SELECT STATEMENT  |      |

|   1 |  TABLE ACCESS FULL| D    |

----------------------------------

When query_rewrite_integrity is set to TRUSTED - Join Elimination kicks in. It does a full table scan on D and the query returns 1 row. This is a wrong result.

CONCLUSION

Join elimination does not kick in with RELY NOVALIDATE constraints when QUERY_REWRITE_INTEGRITY is set to ENFORCED. Join elimination continues to consider RELY NOVALIDATE constraints when QUERY_REWRITE_INTEGRITY is set to TRUSTED or STALE_TOLERATED.This can cause the query to return wrong results. Therefore to avoid wrong results, you should be careful in choosing the right value for the parameter QUERY_REWRITE_INTEGRITY specially when you have RELY NOVALIDATE constraints.

Using Result Cache and SQL Patch together

Result Caching is a new feature in 11g that caches the complete result sets ( Not the database blocks).To use the result cache, you must set the initialization parameter RESULT_CACHE_MODE  to FORCE or use the hint /*+ RESULT_CACHE */ in the query. Wondering if you are using a packaged application and you can't really edit the query ? Here is when SQL Patch feature comes to the rescue. We can use both these features combined to use result cache without having to edit the query.

SQL> drop table test;
SQL> create table test (a number); 
SQL> insert into test values(1); 
SQL> commit; 
SQL> exec sys.dbms_sqldiag_internal.i_create_patch 
    (sql_text  => 'select count(*) from test', 
     hint_text=> 'OPT_PARAM(''result_cache_mode'' ''FORCE'')',  
     name => 'MYPATCH');
 
 SQL> select count(*) from test; 
 SQL> select * from table(dbms_xplan.display_cursor); 

Plan hash value: 1950795681
--------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)|
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                            |       |     3 (100)|
         
|   1 |  RESULT CACHE       | 6wgaf3vqwzvzx1sfmk7cw30v0v |       |            |
         
|   2 |   SORT AGGREGATE    |                            |     1 |            |
         
|   3 |    TABLE ACCESS FULL| TEST                       |     1 |     3   (0)| 
--------------------------------------------------------------------------------

Execution Plan confirms that a cache ID is generated and subsequent executions of this SQL would use the result cache.

NOTE: If your database version is lower than 12.1.0.2 you must install Patch 16974854 to use this feature. Without this fix, Optimizer ignores the result cache hint.

How to find the bind variable value of a SQL that is currently executing

There are two reliable ways to get the bind variable values of a SQL that is currently executing.

1. SQL Monitoring (V$SQL_MONITOR.BIND_XML)

2. Errorstack Trace

We look at both of these methods in details. 

I've not included 10046 ( level 8 or 12 ) trace here as it may not report the bind variable value if you have enabled the trace after the SQL has crossed the parse phase.

SQL Monitoring (V$SQL_MONITOR.BIND_XML)

SQL Monitoring was introduced in 11.1 but the column BIND_XML was added only in 11.2

We run a query with bind variables that takes little longer to complete.

SQL> VAR BIND1 NUMBER;

SQL> EXEC :BIND1:=999;

SQL> select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1;

While this SQL is still running, query v$sql_monitor from a different session.

SQL> select xmltype(binds_xml) from v$sql_monitor where sid =74 and status = 'EXECUTING';

XMLTYPE(BINDS_XML)

------------------

<binds>

<bind name=":BIND1" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="3">999</bind>

</binds>

Note that SQL Monitoring kicks in for SQL statements that run for 5 seconds or more.

If you have a bind variable in the SELECT clause of the query, bind_xml is not populated. For e.g.

select :bind1 AS BIND, ENAME from SCOTT.EMP ;

 ERRORSTACK

Errorstack trace has lot of information in it. Here I focus on the bind variable section of the trace file alone.

This is how you can take an errorstack trace. 12280 is the OS Process ID of the session that runs the SELECT.

connect / as sysdba

oradebug setospid 12280

oradebug unlimit

oradebug dump errorstack 3

oradebug tracefile_name /* This tells you the file name  */

exit

Header section of the trace file shows you the the Current SQL statement text and its SQL ID.

----- Error Stack Dump -----

----- Current SQL Statement for this session (sql_id=9z7qgrmf5at7b) 

select count(*) from DBA_OBJECTS A,DBA_OBJECTS B WHERE A.OBJECT_ID>:BIND1

Now search the trace file with the keyword "Dump Cursor sql_id=9z7qgrmf5at7b". Scroll down a bit and you would see the bind information.

----- Bind Info (kkscoacd) -----

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

kxsbbbfp=2b479dde1cc8 bln=22 avl=03 flg=05

value=999

oacdty is the data type of the variable. 02 indicates a NUMBER datatype. These are some of other identifiers.

01 - Char

02 - Number

08 - Long

09 - Varchar

12 - Date

112- CLOB

113- BLOB

114- BFILE

Evolving SQL Plan Baselines and Adjusting the Acceptance Threshold

Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. 
The execution is performed using peeked bind values of the non-accepted plan. 
If the non-accepted plan's performance is better (threshold of 1.5), the function will make it accepted, thus adding it to the SQL plan baseline. 

Consider the following. 

There is one Non-accepted plan for SQL_HANDLE SQL_0f8c8f355212ed9a

SQL> select sql_handle,plan_name,enabled,accepted from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu22368d7d YES NO
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu6ddd367b YES YES

We will now evolve this non-accpeted plan.

SQL> var report clob;  
SQL> exec :report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle=> 'SQL_0f8c8f355212ed9a', verify=>'YES', commit=>'YES' );

PL/SQL procedure successfully completed

SQL> print :report


-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_0f8c8f355212ed9a
  PLAN_NAME  = SQL_PLAN_0z34g6p915vcu22368d7d
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_0z34g6p915vcu22368d7d
-----------------------------------
  Plan was verified: Time used .06 seconds.
  Plan failed performance criterion: 1.29 times better than baseline plan.
  
                      Baseline Plan      Test Plan     Stats Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:               10            10
  Elapsed Time(ms):             3.434         .101            34
  CPU Time(ms):                 3.555         .1111           32.03
  Buffer Gets:                  19            15              1.27
  Disk Reads:                   0             0
  Direct Writes:                0             0
  Fetches:                      0             0
  Executions:                   1             1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1
Number of plans accepted: 0

The new plan is 1.29 times better than baseline plan. ( 1.29 is computed as a function of buffer gets and CPU time).
This plan was not accepted because it did not meet the threshold criterion of 1.5. A plan has to be at least 1.5 times
better for the SPM to accept it.
This threshold of 1.5 is controlled by a hidden parameter "_plan_verify_improvement_margin". 150 is the default value

Now let us set this parameter to 120 and evolve the plan again.

SQL> ALTER SYSTEM SET "_plan_verify_improvement_margin"=120;

SQL> var report clob;  
SQL> exec :report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle=> 'SQL_0f8c8f355212ed9a', verify=>'YES', commit=>'YES' );

PL/SQL procedure successfully completed

SQL> print :report


-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_0f8c8f355212ed9a
  PLAN_NAME  = SQL_PLAN_0z34g6p915vcu22368d7d
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_0z34g6p915vcu22368d7d
-----------------------------------
  Plan was verified: Time used .06 seconds.
  Plan passed performance criterion: 1.29 times better than baseline plan.
  Plan was changed to an accepted plan.
  
                       Baseline Plan      Test Plan     Stats Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:               10            10
  Elapsed Time(ms):             3.434         .101            34
  CPU Time(ms):                 3.555         .1111           32.03
  Buffer Gets:                  19            15              1.27
  Disk Reads:                   0             0
  Direct Writes:                0             0
  Fetches:                      0             0
  Executions:                   1             1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1
Number of plans accepted: 1

SPM evolution now automatically accepts SQL_PLAN_0z34g6p915vcu22368d7d.

SQL> select sql_handle,plan_name,enabled,accepted from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu22368d7d YES YES
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu6ddd367b YES YES