Friday 13 February 2015

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.

Thursday 5 February 2015

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

19c Multitenant RAC- Default service disables parallelism

We recently upgraded one of our RAC databases from 11.2 to 19.10 on Exadata. And to everyone's surprise, almost everything on the system...