I've noticed that the autotrace display of predicate information is not quite right in exadata plans. Predicates being applied to the storage are showing as "access predicates" not "storage predicates" as they should and do when using DBMS_XPLAN. Example with DBMS_XPLAN.DISPLAY_CURSOR:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID 0bxjs903a3n34, child number 0
-------------------------------------
select /*+ qb_name(opt) */b.object_name, b.object_type, a.username
from allusers_tab a, bigger_tab b where a.username = b.owner and
b.object_type = 'PROCEDURE' and a.username not in ('SYS','SYSTEM')
Plan hash value: 2223160658
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 81140 (100)| | 12288 |00:00:00.22 | 297K| 297K| |
|* 1 | HASH JOIN | | 1 | 11317 | 453K| 81140 (1)| 00:00:04 | 12288 |00:00:00.22 | 297K| 297K| 4038K| 4
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 17 | 136 | 3 (0)| 00:00:01 | 17 |00:00:00.01 | 9 | 0 | |
|* 3 | TABLE ACCESS STORAGE FULL| ALLUSERS_TAB | 1 | 17 | 136 | 3 (0)| 00:00:01 | 17 |00:00:00.01 | 9 | 0 | 1025K| 1
| 4 | JOIN FILTER USE | :BF0000 | 1 | 11946 | 384K| 81137 (1)| 00:00:04 | 12288 |00:00:00.21 | 297K| 297K| |
|* 5 | TABLE ACCESS STORAGE FULL| BIGGER_TAB | 1 | 11946 | 384K| 81137 (1)| 00:00:04 | 12288 |00:00:00.21 | 297K| 297K| 1025K| 1
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."USERNAME"="B"."OWNER")
3 - storage(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
5 - storage(("B"."OBJECT_TYPE"='PROCEDURE' AND "B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM' AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."OWNER")))
filter(("B"."OBJECT_TYPE"='PROCEDURE' AND "B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM' AND SYS_OP_BLOOM_FILTER(:BF0000,"B"."OWNER")))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
**************************
Same plan in SQL Developer with autotrace:
