Blog

On ORA-07445, VPD, materialized views and a new bug

Share this article

One of my customers had a problem with a query which ran fine for one person, but would result in a disconnected session for another user.
Checking the alert file showed that the reason for the disconnect was an ORA-07445 [evaopn3()+107] exception.
Unfortunately that answer was not sufficient for them, they also wanted to know what caused this exception and how to solve it  ;-)
An ORA-07445 [evaopn3] error is typically related to a faulty execution plan with specific data, which makes it hard to debug.
Certainly when the problem query is actually a generated BO query (you know, lots of joins and brackets).

Virtual Private Database

As one user received the error and another didn’t and there were no bind variables in the query, something in their environment must influence the CBO to generate a different execution plan.
Then I remembered that this client was a heavy user of virtual private database, using RLS ( row level security) policies to add additional predicates to queries. Predicates that are (potentially) dependent on the user executing the query.
Checking the dba_policies view indeed confirmed that several tables involved in the query were tagged with RLS policies.
But how could I check which predicates would be added? (The RLS setup at this site is somewhat complex, to say the least).
Searching the documentation and some googling learned me that the predicates returned from the RLS policies were not just added to the where clause, but that the tables in the from clause where replaced with inline views in which the filtering is added.

Searching the documentation and some googling learned me that the predicates returned from the RLS policies were not just added to the where clause, but that the tables in the from clause where replaced with inline views in which the filtering is added.
I also found that there is a view called gv$vpd_policy, which shows you per sql cursor which predicate (coming from which policy) were added to the different objects.
There is however no direct link to the session executing the sql cursor.
Another way I found was to set event 10730 (level 12) before executing the statement.
This will create a trace file containing the object, policy name and the actual RLS view that is generated by that policy, which is nice.

I ended up using that event, to simply replace the tables in the from clause with the generated RLS view. After doing this, I was able to reproduce the issue with users that had the exempt access policy privilege.
By making the replacements one by one, I was also able to find out which tables played a role in the problem and to boil down the complex statement to something much simpler.
At that point I had something that I could use to test hypotheses on what actually caused the problem.

Hypotheses and finding ways not to do it

One of the things I noticed was that the select part of one of the involved RLS views also involved virtual columns (auto generated by the Oracle adaptive framework). By removing them from the select in the test case, I could quickly check if these had anything to do with the ORA-07445 (it didn’t).
After going over several tests, I found that it had something to do with a materialized view rewrite that was triggered by the RLS predicates.
Selecting directly from the materialized view worked, but when the select on the MV was caused by a MV rewrite, an ORA-07445 would be thrown.

Both the base tables and the materialized view were partitioned and had materialized view logs. Further testing to determine the conditions would require modifying the tables, which was not an option in this production environment.
This meant that my next step was to try to create my own set of tables and materialized views and reproduce the error.
As said, with an ORA-07445 [evaopn3()+107] there is often also a data component involved, so reproducing it with a synthetic test case could be difficult. But it would help enormously when opening a service request with Oracle… .

A lot of frustrations later – yes yes, they say you didn’t fail but you discovered how not to do it. But they don’t tell you how frustrating that discovery can be  ;-) – I accidentally found the culprit.
You see, these tables and materialized views had a lot of partitions, and I suddenly noticed that the MV had a default partition while the table had not. As they both had 264 partitions there was 1 other partition that existed in the table, but not in the MV.
A quick check later revealed that the data I needed was indeed in this missing partition on the table, while on the MV in was in the default partition.
Some further testing confirmed that the problem indeed is triggered with a query rewrite when a record exists in a different partition on the MV than on the underlying table.
In addition to this, the partition key column needs to be a char datatype.

The Test Case

To summarize, the following conditions are required

  • A partitioned table, using a char datatype column as partition key
  • A partitioned materialized view on top of such a table, using the same partitioning, but with slightly different partition boundaries
  • A query selecting a value from that table that is located in a different partition in the materialized view than in the table
  • Query rewrite occurring, causing the query to use the materialized view instead of the table

Knowing this, I was able to create the below test case for Oracle support:

set echo on
set linesize 250
set pages 50000
set tab off
set time on

-- cleanup previous test runs

drop materialized view m1;
drop table t1;

-- create the partitioned table
-- using filler fields to make it big (and make the query using the MV, without requiring a hint)

create table T1
( field1     varchar2(30),
  field2     char(5) not null,
  field3     varchar2(30),
  filler1    char(2000),
  filler2    char(2000),
  filler3    char(2000),
  filler4    char(2000),
  filler5    char(2000)
)
partition by list (field2)
  ( partition p_00001 values ('00001'),
    partition p_00002 values ('00002'),
    partition p_00003 values ('00003'),
    partition p_00004 values ('00004'),
    partition p_default values (default)
  )
  enable row movement
/

-- add some data

insert into T1 (field1, field2, field3, filler1, filler2, filler3, filler4, filler5)
values ('aaaaa', '00001', 'part00001', 'a', 'a', 'a','a','a');

insert into T1 (field1, field2, field3, filler1, filler2, filler3, filler4, filler5)
values ('aaaaa', '00002', 'part00002', 'a', 'a', 'a','a','a');

insert into T1 (field1, field2, field3, filler1, filler2, filler3, filler4, filler5)
values ('aaaaa', '00003', 'part00003', 'a', 'a', 'a','a','a');

insert into T1 (field1, field2, field3, filler1, filler2, filler3, filler4, filler5)
values ('aaaaa', '00004', 'part00004', 'a', 'a', 'a','a','a');

commit;

-- create the partitioned materialized view
-- note that the p_00004 partition is not part of the materialized view definition

create materialized view M1
  partition by list (t1_field2)
    ( partition p_00001 values ('00001'), 
      partition p_00002 values ('00002'),
      partition p_00003 values ('00003'),
      partition p_default values (default)
    )  
  enable row movement
  build immediate
  enable query rewrite
  as
    select
      t1.field1   t1_field1,
      t1.field2   t1_field2,
      t1.field3   t1_field3
    from
      t1
/

-- check the execution plan
-- select from the p00004 partition on the T1 table
-- this need to show a query rewrite towards the M1 materialized view in combination with a partition list single operation,
-- selecting the p0004 partition, which does not exist in the materialized view (data is in the p_default partition)

show parameter query_rewrite

explain plan for
select
  /*+ no_parallel
  */
  t1.field3
from
  t1
where
  t1.field2 = '00004'
;

select * from table(dbms_xplan.display);

-- executing the query will trigger the ORA-07445

select
  /*+ no_parallel
  */
  t1.field3
from
  t1
where
  t1.field2 = '00004'
;

As you can see, the execution plan uses a materialized view rewrite, combined with partition pruning

testcase@FDHTEST> explain plan for
  2  select
  3    /*+ no_parallel
  4    */
  5    t1.field3
  6  from
  7    t1
  8  where
  9    t1.field2 = '00004'
 10  ;

Explained.

testcase@FDHTEST> 
testcase@FDHTEST> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 2365420108

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    16 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE        |      |     1 |    16 |     7   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| M1   |     1 |    16 |     7   (0)| 00:00:01 |     4 |     4 |
------------------------------------------------------------------------------------------------------

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

   2 - filter("M1"."T1_FIELD2"='00004')

Note
-----
   - Degree of Parallelism is 1 because of hint

18 rows selected.

The SR is filed with Oracle and is currently being investigated by development.
So far, the issue can be reproduced on 12.1.0.2 and 12.2.0.1 (thanks Pieter for testing).
I have not tested it on other versions

One ORA-07445 bug or two?

Some time after encountering this problem I noticed a different ORA-7445 error, this time with argument [qerixGetKey()+782].
Because of my experience with the previous problem I was able to quickly reproduce it and both problems seem to be related.
In the new case, the error is triggered when the materialized view rewrite uses an index.
The condition of the value being located in a different partition in the table than on the MV is not needed, but the partition key being a char datatype is.

Which makes me wonder what else is going on in the code path of materialized view rewrites against partitions with a char key column….

 

PS: When encountering an ORA-600, ORA-07445 or ORA-700 you can use Note 7445.1 ORA-600/ORA-7445 Lookup tool to quickly check for known issues in different versions.
Unfortunately my issues seem to be brand new (or old undiscovered) ones, so they aren’t documented yet.

Also checkout our Handy Oracle support notes page for more of these useful Oracle support notes.

Update 19/07/2017

Oracle has come up with a fix for both described issues.
Either disable the fix for bug 7426911 or enable the fix for bug 18754357.
You enable and disable these fixes through the _fix_control parameter, which you use at system level, but often also at session level or at statement level via the opt_param hint.

sys@FDHTEST> select * from v$system_fix_control where bugno in (18754357,7426911);

     BUGNO      VALUE SQL_FEATURE                    DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE       EVENT IS_DEFAULT     CON_ID
---------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------- ---------- ---------- ----------
   7426911          1 QKSFM_DBMS_STATS_5099019       no duplicate select list items in kkqtutlGenInlineView           11.2.0.1                           0          1          3
  18754357          0 QKSFM_COMPILATION_18959892     table expansion only if more than one partition after pruning                                       0          1          3

We enabled bug fix 18754357, which is not public, but according to support relates to following issues:

  • invalid range partition with PSTART/PSTOP
  • issue with partition when right hand side of query predicate has a function
  • a couple regressions caused by other fixes
  • long parse times due to partitions
  • slow or errorring out with partitions and materialized views
  • ORA-600/ORA-7445 with materialized view refresh/Star schema/PDB
Tags: Blog
Oracle Content and Experience Cloud Service
Why DB_BIG_TABLE_CACHE_PERCENT_TARGET was a good solution

You May Also Like