An Invalid, or Do You Just Not Want to Work

1 12 2010

December 1, 2010

Another recent blog article forced me to Stop, Think, … and just about understand (in case you are wondering about the blog title, the definition of invalid).

Consider the following table definition:

CREATE TABLE T3(
  V1 VARCHAR2(10),
  D2 DATE,
  N3 NUMBER);

INSERT INTO T3 VALUES(
  CHR(65),
  TRUNC(SYSDATE)-65,
  65);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3')

SELECT
  *
FROM
  T3;

V1         D2                N3
---------- --------- ----------
A          27-SEP-10         65

—

Which of the following SQL statements are valid SQL statements for the above table (E is not an option)?

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A');

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A';

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010';

——————-

Stop and think about it for a moment, which of the above are valid SQL statements?

—-

We could use the AUTOTRACE feature in SQL*Plus to tell which are valid, and which are not:

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A');

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A';

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010';

The results might look like this:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("V1")=1)

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=TO_NUMBER('A');

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("V1")=TO_NUMBER('A'))

SQL> SELECT
  2    *
  3  FROM
  4    T3 WHERE TO_NUMBER(V1)=1
  5    AND ROWNUM=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1538339754

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  COUNT              |      |       |       |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=2)
   3 - filter(TO_NUMBER("V1")=1)

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1
  7    AND 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter(TO_NUMBER("V1")=1)

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '27-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))=TO_DATE(' 2010-09-27
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '32-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(INTERNAL_FUNCTION("D2"))='32-SEP-2010')

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    D2 = '32-SEP-2010'
  7    AND 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3859223164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter("D2"='32-SEP-2010')

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND TO_CHAR(1)='2';
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND 1=2;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = 'A';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('A'))

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '27-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('27-SEP-2010'))

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '32-SEP-2010';

Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N3"=TO_NUMBER('32-SEP-2010'))

So, which SQL statements are valid, and which are not? Did you guess correctly? Are you surprised, or still not sure?

How about if we do this just to confirm:

SET AUTOTRACE OFF
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST_SQL_OK';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A');

SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2;

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3;

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND TO_CHAR(1)='2';

SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = N3
  AND 1=2;

SELECT
  *
FROM
  T3
WHERE
  N3 = 'A';

SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010';

SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010';

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

The output looks like this:

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1;
  TO_NUMBER(V1)=1
  *
ERROR at line 6:
ORA-01722: invalid number

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=TO_NUMBER('A');
  TO_NUMBER(V1)=TO_NUMBER('A')
  *
ERROR at line 6:
ORA-01722: invalid number

SQL> SELECT
  2    *
  3  FROM
  4    T3 WHERE TO_NUMBER(V1)=1
  5    AND ROWNUM=2;
  T3 WHERE TO_NUMBER(V1)=1
           *
ERROR at line 4:
ORA-01722: invalid number

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_NUMBER(V1)=1
  7    AND 1=2;

no rows selected

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '27-SEP-2010';

V1         D2                N3
---------- --------- ----------
A          27-SEP-10         65

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = '32-SEP-2010';
  TO_DATE(D2) = '32-SEP-2010'
                *
ERROR at line 6:
ORA-01847: day of month must be between 1 and last day of month

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    D2 = '32-SEP-2010'
  7    AND 1=2;

no rows selected

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND TO_CHAR(1)='2';
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    TO_DATE(D2) = N3
  7    AND 1=2;
  TO_DATE(D2) = N3
              *
ERROR at line 6:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = 'A';
  N3 = 'A'
       *
ERROR at line 6:
ORA-01722: invalid number

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '27-SEP-2010';
  N3 = '27-SEP-2010'
       *
ERROR at line 6:
ORA-01722: invalid number

SQL> SELECT
  2    *
  3  FROM
  4    T3
  5  WHERE
  6    N3 = '32-SEP-2010';
  N3 = '32-SEP-2010'
       *
ERROR at line 6:
ORA-01722: invalid number

So, which SQL statements are invalid? Last chance.

—-

Good idea, let’s take a look inside the 10046 trace file to see if it provides any critical clues:

WAIT #6: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208311587
WAIT #6: nam='SQL*Net message from client' ela= 409 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313312
CLOSE #6:c=0,e=0,dep=0,type=1,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208313394
WAIT #0: nam='SQL*Net message from client' ela= 1137 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314551
=====================
PARSING IN CURSOR #2 len=44 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=2056319931 ad='1e367f68' sqlid='94uqsttx91wxv'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208314830
FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #2: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208315213
WAIT #2: nam='SQL*Net break/reset to client' ela= 124 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208315356
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208315926
WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317175
=====================
PARSING IN CURSOR #4 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=4036914934 ad='1e3647e4' sqlid='b34gs4zs9wvrq'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=TO_NUMBER('A')
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208317422
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208317617
WAIT #4: nam='SQL*Net break/reset to client' ela= 125 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208317761
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208318310
WAIT #0: nam='SQL*Net message from client' ela= 1084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319413
=====================
PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=3419871603 ad='1e3641f4' sqlid='2g02uyv5xf6bm'
SELECT
  *
FROM
  T3 WHERE TO_NUMBER(V1)=1
  AND ROWNUM=2
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208319705
FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=1538339754,tim=106208312194
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=3 cnt=0 pid=2 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208319939
WAIT #8: nam='SQL*Net break/reset to client' ela= 112 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208320070
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208320807
WAIT #0: nam='SQL*Net message from client' ela= 1240 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322066
=====================
PARSING IN CURSOR #3 len=54 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1521821591 ad='1e363c04' sqlid='3ty5saddba9wr'
SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(V1)=1
  AND 1=2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322296
FETCH #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208312194
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 366 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322774
CLOSE #3:c=0,e=0,dep=0,type=1,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208322830
WAIT #0: nam='SQL*Net message from client' ela= 1249 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208324098
=====================
PARSING IN CURSOR #2 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208312194 hv=1963953631 ad='1e3634ec' sqlid='du7hngjuhz3fz'
SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '27-SEP-2010'
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326129
FETCH #2:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=106208312194
WAIT #2: nam='SQL*Net message from client' ela= 182 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326423
FETCH #2:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208312194
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=7 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208326523
WAIT #2: nam='SQL*Net message from client' ela= 411 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327021
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208312194
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208327083
WAIT #0: nam='SQL*Net message from client' ela= 28084 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355185
=====================
PARSING IN CURSOR #4 len=56 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1226802804 ad='1e362dd4' sqlid='1hqqd0x4jz1mn'
SELECT
  *
FROM
  T3
WHERE
  TO_DATE(D2) = '32-SEP-2010'
END OF STMT
PARSE #4:c=15625,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208355455
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208355669
WAIT #4: nam='SQL*Net break/reset to client' ela= 134 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208355822
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208356681
WAIT #0: nam='SQL*Net message from client' ela= 1259 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208357960
=====================
PARSING IN CURSOR #8 len=57 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=2831431159 ad='1e3627e4' sqlid='3u7tx3knc8dgr'
SELECT
  *
FROM
  T3
WHERE
  D2 = '32-SEP-2010'
  AND 1=2
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
WAIT #8: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358205
FETCH #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3859223164,tim=106208339668
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #8 id=2 cnt=0 pid=1 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net message from client' ela= 439 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358756
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208358815
WAIT #0: nam='SQL*Net message from client' ela= 1489 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208360322
WAIT #3: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208363231
WAIT #3: nam='SQL*Net break/reset to client' ela= 133 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208363398
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208363421
WAIT #3: nam='SQL*Net message from client' ela= 701 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364163
CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208364233
WAIT #0: nam='SQL*Net message from client' ela= 1322 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208365574
WAIT #6: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208366129
WAIT #6: nam='SQL*Net break/reset to client' ela= 126 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208366285
WAIT #6: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208366308
WAIT #6: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367019
CLOSE #6:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208367086
WAIT #0: nam='SQL*Net message from client' ela= 1229 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208368340
WAIT #2: nam='SQL*Net break/reset to client' ela= 4 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208368838
WAIT #2: nam='SQL*Net break/reset to client' ela= 122 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208368989
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369011
WAIT #2: nam='SQL*Net message from client' ela= 679 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369721
CLOSE #2:c=0,e=0,dep=0,type=0,tim=106208339668
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208369786
WAIT #0: nam='SQL*Net message from client' ela= 1010 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208370816
=====================
PARSING IN CURSOR #4 len=37 dep=0 uid=185 oct=3 lid=185 tim=106208339668 hv=1569184524 ad='1e361d0c' sqlid='54544w5fsgqsc'
SELECT
  *
FROM
  T3
WHERE
  N3 = 'A'
END OF STMT
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208339668
EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208371121
FETCH #4:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #4: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208371337
WAIT #4: nam='SQL*Net break/reset to client' ela= 108 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208371463
CLOSE #4:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208372094
WAIT #0: nam='SQL*Net message from client' ela= 1072 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373186
=====================
PARSING IN CURSOR #8 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=2630877256 ad='1e36173c' sqlid='5xks2pufd0028'
SELECT
  *
FROM
  T3
WHERE
  N3 = '27-SEP-2010'
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
EXEC #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #8: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208373423
FETCH #8:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #8 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #8: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208373612
WAIT #8: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208373732
CLOSE #8:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208374372
WAIT #0: nam='SQL*Net message from client' ela= 1083 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375474
=====================
PARSING IN CURSOR #3 len=47 dep=0 uid=185 oct=3 lid=185 tim=106208370919 hv=1567527007 ad='1e36116c' sqlid='c5w7qdxfqx42z'
SELECT
  *
FROM
  T3
WHERE
  N3 = '32-SEP-2010'
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208375702
FETCH #3:c=0,e=0,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=106208370919
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=104348 op='TABLE ACCESS FULL T3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'
WAIT #3: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=106208375886
WAIT #3: nam='SQL*Net break/reset to client' ela= 102 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=106208376006
CLOSE #3:c=0,e=0,dep=0,type=0,tim=106208370919
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208376714
WAIT #0: nam='SQL*Net message from client' ela= 719 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=106208377453
PARSE #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919
EXEC #6:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=106208370919

It is a little painful that not even the PARSE call for some of the SQL statements completed, therefore the SQL statement is not written to the 10046 trace file.

Maybe I should have clarified what I was asking for? When we say that a SQL statement is invalid, do we mean:

It generates an error message when it is executed.
It generates an error message when it is parsed.
It generates no error message, yet is logically invalid due to impossible values: 1=2 ; September 32, 2010 ; TO_NUMBER(‘A’)
Something else?

Actions

Information

Date : December 1, 2010
Categories : SQL

23 responses

1 12 2010

Damir Vadas (15:41:34) :

From mine point of view a-“It generates an error message when it is executed.”

Look for automatic casting (string is multiplying a number):
SQL> select ‘0.18’ * 10 from dual;

‘0.18’*10
———-
1.8

SQL>

Result is number and correct one.
Rg,
Damir

1 12 2010

Martin Berger (16:21:35) :

I’m not sure if you really ask for invalid? At the other end of the discussion I would say ‘do not expect anything, be clear what you want’. Therefore no implicit castings etc.
e.g. your tests with any date in string format will fail in my env as I use ‘YYYY-MM-DD HH24:MI’ as default format.
I would therefore suggest ‘be precise in what you want’.
This will limit the case, when it ‘does not act as expected at the time written’. (and this is my definition of ‘invalid’!)
Yes, of course this will not save against ‘logically invalid’ situations. But are they expected? 😉
To be honest, I did not pass ‘TO_DATE(D2) = N3’.

1 12 2010

Charles Hooper (17:01:42) :

Martin,

I was very much hoping that someone would argue that the implicit casting of the VARCHAR to DATE makes that SQL statement invalid (I agree). That reminds me of a comment that I made in this book review: https://hoopercharles.wordpress.com/2010/06/06/book-review-oracle-sql-recipes-a-problem-solution-approach/
Invalid in this sense is that the SQL statement might not generate an error when being parsed, might not generate an error when being executed, and is logically valid – but only in the environment where the SQL statement is executed. As you stated, all that it takes is a change in the default date format.

It could be argued that the example provided by Damir should also fail because a VARCHAR cannot be multiplied by a number, yet changing that behavior would break programs that incorrectly define numeric bind variables as VARCHAR2, and probably cause problems for EXPLAIN PLAN when SQL statements are submitted with bind variables.

I am not sure that I understand your comment about “TO_DATE(D2) = N3” – if you are saying that I cannot explicitly convert a value that is already a DATE into a DATE value, and then compare that value to a number, I say why not? Simple algebra: 🙂

TO_DATE(D2) = N3
 
TO_DATE(D2) - N3 = 0

The result of subtracting a number from a date is… if we assume N3 is 65:

SELECT SYSDATE-65 FROM DUAL;
 
SYSDATE-65
---------
27-SEP-10

But then we reach a problem: September 27 is not equal to 0, unless 1 is equal to 2 (https://hoopercharles.wordpress.com/2009/12/25/proving-that-12-is-oracle-wrong-to-short-circuit-an-execution-plan/). OK, enough joking.

Actually, I saw a couple of recent threads on the OTN forums that included SQL statements with silly syntax like TO_DATE(D2) – that mistake really causes problems for the optimizer’s cardinality estimates. Therefore, it *could* be argued that the SQL statement with “TO_DATE(D2) = ’27-SEP-2010′” in the WHERE clause is invalid not just because of the implicit data type conversion, but also because of the affect on the cardinality estimates that are produced by the cost based optimizer.

1 12 2010

Gary (17:13:41) :

An alternative definition of ‘invalid’ is whether it can be included as a static SQL statement in a PL/SQL stored procedure.
That doesn’t mean it can be parsed though, let alone executed. I blogged an example of that with GROUP BY here : http://blog.sydoracle.com/2010/10/fun-with-group-by.html

VPD can also come into play. A statement might be valid when executed with one policy but not with another.

I prefer to be guided by whether a statement is correct or not, with correctness being defined as always producing the desired result. The desired result MIGHT be an exception (and in the PL/SQL world that might be NO_DATA_FOUND or TOO_MANY_ROWS)

2 12 2010

Martin Berger (07:02:13) :

I followed the other arguments here also.
Based on these, and some more thinking about it, I’d like the PARSE as the separator.
My idea behind is this: use all your statements with bin variables – therefore you do not know anything about their values at parsing time (well, sometimes you can. but I also can explicit parse a statement without any bind variables provided).
If it is correct, everything which passes this line is not an invalid statement anymore but invalid data?
(in set theory we could argue this tuple of bind variables provided to the statement is not part of the set, (so it is ‘invalid’) and there is no need for the statement to execute this tuple anyhow).
A little bit philosophical now …

3 12 2010

Charles Hooper (11:29:54) :

Gary and Martin,

Thank you for your points of view on this topic. I was waiting to see if anyone else had an opinion to offer.

Martin, I was close to suggesting that another example of an “invalid” SQL statement is a SQL statement that is written in such a manner that it causes poor performance – that is only a small step away from, “Therefore, it *could* be argued that the SQL statement with “TO_DATE(D2) = ’27-SEP-2010′” in the WHERE clause is invalid not just because of the implicit data type conversion, but also because of the affect on the cardinality estimates that are produced by the cost based optimizer.” I follow your line of thought.

Gary, VPD is something that was completely off my radar. What about relying on public synonyms and the possiblity that an object will exist in the user’s schema with the same object name? Such a situation might make a SQL statement appear to be invalid for only a single user. Yet, if the schema name is prefixed to table names, that could limit the DBA’s ability to consolidate data from multiple databases into a single database with the help of multiple schemas (as would public synonyms I suppose).

31 12 2010

Martin Berger (00:52:01) :

What do you say about a statement which throws an ORA-31603 without a hint, but works fine with a hint? My example: http://berxblog.blogspot.com/2010/12/invalid-sql-if-hint-is-missing.html

31 12 2010

Charles Hooper (07:54:50) :

Martin,

Your test SQL statement work OK on 64 bit Windows 11.2.0.1. However, there is something odd about your output that I was also able to reproduce. Take a look at the following blog article:
https://hoopercharles.wordpress.com/2009/12/09/failure-to-collect-fixed-object-statistics-leads-to-ora-01013-or-ora-07445/

That blog article suggests to execute the following SQL statement to verify that the fixed object statistics were collected:

SELECT
  *
FROM
  SYS.TAB_STATS$;

When I executed the above, the SQL statement returned no rows. We will come back to this in a minute.

The AUTOTRACE execution plans that I received are as follows:

select /*+ no_merge(vv) */
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='PSFT'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'; 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 138201291
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |     1 |    34 |   102   (0)| 00:00:01 |
|*  1 |  VIEW                                 |             |     1 |    34 |   102   (0)| 00:00:01 |
|*  2 |   FILTER                              |             |       |       |            |          |
|   3 |    NESTED LOOPS                       |             |     1 |   106 |   102   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |             |     1 |    84 |   101   (0)| 00:00:01 |
|*  5 |      HASH JOIN                        |             |     2 |   158 |   101   (0)| 00:00:01 |
|   6 |       VIEW                            | DBA_OBJECTS |    28 |   700 |    69   (0)| 00:00:01 |
|   7 |        UNION-ALL                      |             |       |       |            |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID   | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN            | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|* 10 |         FILTER                        |             |       |       |            |          |
|  11 |          NESTED LOOPS                 |             |     1 |   120 |    54   (0)| 00:00:01 |
|  12 |           NESTED LOOPS                |             |     1 |    98 |    53   (0)| 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |    52   (0)| 00:00:01 |
|* 16 |             INDEX RANGE SCAN          | I_OBJ5      |    29 |       |    31   (0)| 00:00:01 |
|* 17 |           INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 18 |          TABLE ACCESS BY INDEX ROWID  | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 19 |           INDEX UNIQUE SCAN           | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  20 |          NESTED LOOPS                 |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 21 |           INDEX FULL SCAN             | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 22 |           INDEX RANGE SCAN            | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 23 |         FILTER                        |             |       |       |            |          |
|  24 |          NESTED LOOPS                 |             |     6 |   186 |     2   (0)| 00:00:01 |
|  25 |           TABLE ACCESS BY INDEX ROWID | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 26 |            INDEX UNIQUE SCAN          | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 27 |           INDEX RANGE SCAN            | I_LINK1     |     6 |   102 |     1   (0)| 00:00:01 |
|  28 |       NESTED LOOPS                    |             |  2947 |   155K|    32   (0)| 00:00:01 |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 30 |         INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 31 |        INDEX RANGE SCAN               | I_OBJ5      |  2947 |   115K|    31   (0)| 00:00:01 |
|* 32 |      INDEX UNIQUE SCAN                | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|* 33 |     INDEX RANGE SCAN                  | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|  34 |    NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 35 |     INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 36 |     INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"VV"."VIEW_NAME","VV"."OWNER") LIKE '%/*+%')
   2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND ("O"."TYPE#"<>4
              AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  14 - access("U"."NAME"='PSFT')
  15 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
              BITAND("O"."FLAGS",128)=0)
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
              OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FU
              NCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
              PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
              (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
              VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
              GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
              DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
              CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi
              on_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi
              on_id')))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"='PSFT')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"='PSFT')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi
              on_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_editi
              on_id')))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='PSFT'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'; 
 
Plan hash value: 2706590226
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   106 |    93   (0)| 00:00:01 |
|*  1 |  FILTER                              |             |       |       |            |          |
|   2 |   NESTED LOOPS                       |             |     1 |   106 |    93   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |             |     1 |    84 |    92   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |             |    10 |   590 |    32   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |             |   147 |  7938 |    32   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN               | I_OBJ5      |   147 |  5880 |    31   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN               | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|  10 |     VIEW                             | DBA_OBJECTS |     1 |    25 |     6   (0)| 00:00:01 |
|  11 |      UNION ALL PUSHED PREDICATE      |             |       |       |            |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID    | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|* 13 |        INDEX UNIQUE SCAN             | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|* 14 |       FILTER                         |             |       |       |            |          |
|* 15 |        FILTER                        |             |       |       |            |          |
|  16 |         NESTED LOOPS                 |             |     1 |   120 |     5   (0)| 00:00:01 |
|  17 |          NESTED LOOPS                |             |     1 |    98 |     4   (0)| 00:00:01 |
|  18 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 19 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 20 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |     3   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN          | I_OBJ5      |     1 |       |     2   (0)| 00:00:01 |
|* 22 |          INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 23 |        TABLE ACCESS BY INDEX ROWID   | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN            | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  25 |        NESTED LOOPS                  |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 26 |         INDEX FULL SCAN              | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN             | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 28 |       FILTER                         |             |       |       |            |          |
|  29 |        NESTED LOOPS                  |             |     1 |    31 |     1   (0)| 00:00:01 |
|  30 |         TABLE ACCESS BY INDEX ROWID  | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 31 |          INDEX UNIQUE SCAN           | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 32 |         INDEX RANGE SCAN             | I_LINK1     |     1 |    17 |     0   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                  | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|  34 |   NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 35 |    INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   7 - access("U"."NAME"='PSFT')
   8 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
   9 - access("O"."OBJ#"="V"."OBJ#")
  12 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
  13 - access("S"."OBJ#"=:B1)
  14 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  15 - filter('_default_auditing_options_'<>"O"."NAME" AND '_NEXT_OBJECT'<>"O"."NAME")
  19 - access("U"."NAME"='PSFT')
  20 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
              BITAND("O"."FLAGS",128)=0)
  21 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"="O"."NAME" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
              OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
              UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
              PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
              (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
              VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
              GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
              DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
              CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  22 - access("O"."OWNER#"="U"."USER#")
  23 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  24 - access("I"."OBJ#"=:B1)
  26 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  27 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  28 - filter(NULL IS NOT NULL)
  31 - access("U"."NAME"='PSFT')
  32 - access("L"."OWNER#"="U"."USER#" AND "L"."NAME"="O"."NAME")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

My database can not only parse the unhinted and the hinted SQL statement, but it can also execute it. Note that the “Predicate Information” section is printed just below the execution plan – the same result that you received.

Now, let’s go back to the problem where I somehow forgot to collect fixed object statistics:

SELECT
  *
FROM
  SYS.TAB_STATS$;
  
no rows selected

Now to collect the fixed object statistics and re-check the above SQL statement:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);
 
SELECT
  *
FROM
  SYS.TAB_STATS$;
 
...
798 rows selected.

Now the SELECT produces 798 rows. Let’s retry the SQL statement (actually executing it) with a 10053 trace enabled:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET STATISTICS_LEVEL='ALL';
ALTER SESSION SET TRACEFILE_IDENTIFIER='ERROR_SQL';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='PSFT'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'; 
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2rdfv5vn7kq87, child number 0
-------------------------------------
select   owner,   view_name from   (select     v.owner,     v.view_name
  from     dba_views v,     dba_objects o   where     v.owner = o.owner
    AND v.view_name = o.object_name     AND o.object_type='VIEW'
AND o.status='VALID'     AND v.owner ='PSFT'  ) vv where
dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'
 
Plan hash value: 2706590226
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |      0 |00:00:00.01 |    1 |
|   1 |  FILTER                              |             |      1 |        |      0 |00:00:00.01 |    1 |
|   2 |   NESTED LOOPS                       |             |      1 |      1 |      0 |00:00:00.01 |    1 |
|   3 |    NESTED LOOPS                      |             |      1 |      1 |      0 |00:00:00.01 |    1 |
|   4 |     NESTED LOOPS                     |             |      1 |     10 |      0 |00:00:00.01 |    1 |
|   5 |      NESTED LOOPS                    |             |      1 |    147 |      0 |00:00:00.01 |    1 |
|   6 |       TABLE ACCESS BY INDEX ROWID    | USER$       |      1 |      1 |      0 |00:00:00.01 |    1 |
|   7 |        INDEX UNIQUE SCAN             | I_USER1     |      1 |      1 |      0 |00:00:00.01 |    1 |
|   8 |       INDEX RANGE SCAN               | I_OBJ5      |      0 |    147 |      0 |00:00:00.01 |    0 |
|   9 |      INDEX UNIQUE SCAN               | I_VIEW1     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  10 |     VIEW                             | DBA_OBJECTS |      0 |      1 |      0 |00:00:00.01 |    0 |
|  11 |      UNION ALL PUSHED PREDICATE      |             |      0 |        |      0 |00:00:00.01 |    0 |
|  12 |       TABLE ACCESS BY INDEX ROWID    | SUM$        |      0 |      1 |      0 |00:00:00.01 |    0 |
|  13 |        INDEX UNIQUE SCAN             | I_SUM$_1    |      0 |      1 |      0 |00:00:00.01 |    0 |
|  14 |       FILTER                         |             |      0 |        |      0 |00:00:00.01 |    0 |
|  15 |        FILTER                        |             |      0 |        |      0 |00:00:00.01 |    0 |
|  16 |         NESTED LOOPS                 |             |      0 |      1 |      0 |00:00:00.01 |    0 |
|  17 |          NESTED LOOPS                |             |      0 |      1 |      0 |00:00:00.01 |    0 |
|  18 |           TABLE ACCESS BY INDEX ROWID| USER$       |      0 |      1 |      0 |00:00:00.01 |    0 |
|  19 |            INDEX UNIQUE SCAN         | I_USER1     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  20 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |      0 |      1 |      0 |00:00:00.01 |    0 |
|  21 |            INDEX RANGE SCAN          | I_OBJ5      |      0 |      1 |      0 |00:00:00.01 |    0 |
|  22 |          INDEX RANGE SCAN            | I_USER2     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  23 |        TABLE ACCESS BY INDEX ROWID   | IND$        |      0 |      1 |      0 |00:00:00.01 |    0 |
|  24 |         INDEX UNIQUE SCAN            | I_IND1      |      0 |      1 |      0 |00:00:00.01 |    0 |
|  25 |        NESTED LOOPS                  |             |      0 |      1 |      0 |00:00:00.01 |    0 |
|  26 |         INDEX FULL SCAN              | I_USER2     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  27 |         INDEX RANGE SCAN             | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |    0 |
|  28 |       FILTER                         |             |      0 |        |      0 |00:00:00.01 |    0 |
|  29 |        NESTED LOOPS                  |             |      0 |      1 |      0 |00:00:00.01 |    0 |
|  30 |         TABLE ACCESS BY INDEX ROWID  | USER$       |      0 |      1 |      0 |00:00:00.01 |    0 |
|  31 |          INDEX UNIQUE SCAN           | I_USER1     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  32 |         INDEX RANGE SCAN             | I_LINK1     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  33 |    INDEX RANGE SCAN                  | I_USER2     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  34 |   NESTED LOOPS                       |             |      0 |      1 |      0 |00:00:00.01 |    0 |
|  35 |    INDEX FULL SCAN                   | I_USER2     |      0 |      1 |      0 |00:00:00.01 |    0 |
|  36 |    INDEX RANGE SCAN                  | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |    0 |
--------------------------------------------------------------------------------------------------------------
 
52 rows selected.

Note that the “Predicate Information” section is NO LONGER printed just below the execution plan. The same changed behavior is also seen with AUTOTRACE.

From the 10053 trace file, the unhinted query after all transformations:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE  WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN  (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END  "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND  (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='PSFT' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND "SYS_ALIAS_4"."NAME"="SYS_ALIAS_2"."NAME" AND '_NEXT_OBJECT'<>"SYS_ALIAS_2"."NAME" AND '_default_auditing_options_'<>"SYS_ALIAS_2"."NAME") UNION ALL  (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='PSFT' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID' AND "L"."NAME"="SYS_ALIAS_2"."NAME")) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"('VIEW',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%/*+%' AND "U"."NAME"='PSFT' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))

A Google search for ORA-31603 (mostly items found on forums.oracle.com) seems to point to a permissions problem when the error appears with dbms_metadata.get_ddl, but that should not be the case for you if you are executing the query as the SYS (internal) user.

31 12 2010

Charles Hooper (09:25:34) :

Martin, Your test SQL statement works OK on 64 bit Linux 11.2.0.1 with fixed object statistics collected (I did not test without those statistics as the statistics were already collected).

SELECT
  COUNT(*)
FROM
  SYS.TAB_STATS$;
 
  COUNT(*)
----------
       798

SET AUTOTRACE TRACEONLY EXPLAIN
 
select /*+ no_merge(vv) */
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type=\'VIEW\'
    AND o.status=\'VALID\'
    AND v.owner =\'PSFT\'  ) vv
where
  dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\'; 

Execution Plan
----------------------------------------------------------
Plan hash value: 138201291

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |     1 |    34 |  124   (1) | 00:00:02 |
|*  1 |  VIEW                                 |             |     1 |    34 |  124   (1) | 00:00:02 |
|*  2 |   FILTER                              |             |       |       |            |          |
|   3 |    NESTED LOOPS                       |             |     1 |   106 |  124   (1) | 00:00:02 |
|   4 |     NESTED LOOPS                      |             |     1 |    84 |  123   (1) | 00:00:02 |
|*  5 |      HASH JOIN                        |             |     3 |   237 |  123   (1) | 00:00:02 |
|   6 |       VIEW                            | DBA_OBJECTS |    35 |   875 |   83   (0) | 00:00:02 |
|   7 |        UNION-ALL                      |             |       |       |            |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID   | SUM$        |     1 |    11 |    1   (0) | 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN            | I_SUM$_1    |     1 |       |    0   (0) | 00:00:01 |
|* 10 |         FILTER                        |             |       |       |            |          |
|  11 |          NESTED LOOPS                 |             |     1 |   120 |   65   (0) | 00:00:01 |
|  12 |           NESTED LOOPS                |             |     1 |    98 |   64   (0) | 00:00:01 |
|  13 |            TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |    1   (0) | 00:00:01 |
|* 14 |             INDEX UNIQUE SCAN         | I_USER1     |     1 |       |    0   (0) | 00:00:01 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |   63   (0) | 00:00:01 |
|* 16 |             INDEX RANGE SCAN          | I_OBJ5      |    37 |       |   38   (0) | 00:00:01 |
|* 17 |           INDEX RANGE SCAN            | I_USER2     |     1 |    22 |    1   (0) | 00:00:01 |
|* 18 |          TABLE ACCESS BY INDEX ROWID  | IND$        |     1 |     8 |    2   (0) | 00:00:01 |
|* 19 |           INDEX UNIQUE SCAN           | I_IND1      |     1 |       |    1   (0) | 00:00:01 |
|  20 |          NESTED LOOPS                 |             |     1 |    29 |    2   (0) | 00:00:01 |
|* 21 |           INDEX FULL SCAN             | I_USER2     |     1 |    20 |    1   (0) | 00:00:01 |
|* 22 |           INDEX RANGE SCAN            | I_OBJ4      |     1 |     9 |    1   (0) | 00:00:01 |
|* 23 |         FILTER                        |             |       |       |            |          |
|  24 |          NESTED LOOPS                 |             |     6 |   180 |    2   (0) | 00:00:01 |
|  25 |           TABLE ACCESS BY INDEX ROWID | USER$       |     1 |    14 |    1   (0) | 00:00:01 |
|* 26 |            INDEX UNIQUE SCAN          | I_USER1     |     1 |       |    0   (0) | 00:00:01 |
|* 27 |           INDEX RANGE SCAN            | I_LINK1     |     6 |    96 |    1   (0) | 00:00:01 |
|  28 |       NESTED LOOPS                    |             |  3675 |   193K|   39   (0) | 00:00:01 |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |    1   (0) | 00:00:01 |
|* 30 |         INDEX UNIQUE SCAN             | I_USER1     |     1 |       |    0   (0) | 00:00:01 |
|* 31 |        INDEX RANGE SCAN               | I_OBJ5      |  3675 |   143K|   38   (0) | 00:00:01 |
|* 32 |      INDEX UNIQUE SCAN                | I_VIEW1     |     1 |     5 |    0   (0) | 00:00:01 |
|* 33 |     INDEX RANGE SCAN                  | I_USER2     |     1 |    22 |    1   (0) | 00:00:01 |
|  34 |    NESTED LOOPS                       |             |     1 |    29 |    2   (0) | 00:00:01 |
|* 35 |     INDEX FULL SCAN                   | I_USER2     |     1 |    20 |    1   (0) | 00:00:01 |
|* 36 |     INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |    1   (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DBMS_METADATA"."GET_DDL"(\'VIEW\',"VV"."VIEW_NAME","VV"."OWNER") LIKE \'%/*+%\')
   2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND ("O"."TYPE#"<>4
              AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))))
  14 - access("U"."NAME"=\'PSFT\')
  15 - filter(DECODE("O"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\' AND
              BITAND("O"."FLAGS",128)=0)
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>\'_NEXT_OBJECT\' AND "O"."NAME"<>\'_default_auditing_options_\' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,\'NEXT
              OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FU
              NCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE
              PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA
              SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE
              SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL(
              (SELECT \'REWRITE EQUIVALENCE\' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED
              VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER
              GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA
              DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION
              CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER
              GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING
              MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE
              FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\')
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_editi
              on_id\')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_editi
              on_id\')))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"=\'PSFT\')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"=\'PSFT\')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_editi
              on_id\')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_editi
              on_id\')))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Unlike on Windows 64 bit 11.2.0.1, the Predicate Information section was printed with the fixed object statistics collected.

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type=\'VIEW\'
    AND o.status=\'VALID\'
    AND v.owner =\'PSFT\'  ) vv
where
  dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   106 |   124   (1)| 00:00:02 |
|*  1 |  FILTER                              |             |       |       |            |          |
|   2 |   NESTED LOOPS                       |             |     1 |   106 |   124   (1)| 00:00:02 |
|   3 |    NESTED LOOPS                      |             |     1 |   101 |   124   (1)| 00:00:02 |
|*  4 |     HASH JOIN                        |             |     1 |    79 |   123   (1)| 00:00:02 |
|   5 |      VIEW                            | DBA_OBJECTS |    35 |   875 |    83   (0)| 00:00:02 |
|   6 |       UNION-ALL                      |             |       |       |            |          |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN            | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|*  9 |        FILTER                        |             |       |       |            |          |
|  10 |         NESTED LOOPS                 |             |     1 |   120 |    65   (0)| 00:00:01 |
|  11 |          NESTED LOOPS                |             |     1 |    98 |    64   (0)| 00:00:01 |
|  12 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 14 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |    63   (0)| 00:00:01 |
|* 15 |            INDEX RANGE SCAN          | I_OBJ5      |    37 |       |    38   (0)| 00:00:01 |
|* 16 |          INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS BY INDEX ROWID  | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 18 |          INDEX UNIQUE SCAN           | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  19 |         NESTED LOOPS                 |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 20 |          INDEX FULL SCAN             | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 21 |          INDEX RANGE SCAN            | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 22 |        FILTER                        |             |       |       |            |          |
|  23 |         NESTED LOOPS                 |             |     6 |   180 |     2   (0)| 00:00:01 |
|  24 |          TABLE ACCESS BY INDEX ROWID | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 25 |           INDEX UNIQUE SCAN          | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 26 |          INDEX RANGE SCAN            | I_LINK1     |     6 |    96 |     1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS                    |             |   184 |  9936 |    39   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN               | I_OBJ5      |   184 |  7360 |    38   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN                 | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN                 | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))))
  13 - access("U"."NAME"=\'PSFT\')
  14 - filter(DECODE("O"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\' AND
              BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>\'_NEXT_OBJECT\' AND "O"."NAME"<>\'_default_auditing_options_\' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,\'NEXT
              OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'F
              UNCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE
              PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA
              SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE
              SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL(
              (SELECT \'REWRITE EQUIVALENCE\' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED
              VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER
              GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA
              DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION
              CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER
              GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING
              MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE
              FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edit
              ion_id\')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edit
              ion_id\')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"=\'PSFT\')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"=\'PSFT\')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"(\'VIEW\',"O"."NAME","U"."NAME") LIKE \'%/*+%\')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edit
              ion_id\')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edit
              ion_id\')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Actually executing the SQL statements:

SET AUTOTRACE OFF
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET STATISTICS_LEVEL=\'ALL\';
 
select /*+ no_merge(vv) */
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type=\'VIEW\'
    AND o.status=\'VALID\'
    AND v.owner =\'PSFT\'  ) vv
where
  dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,\'ALLSTATS LAST\'));

SQL_ID  3w240d53zapaz, child number 0
-------------------------------------
select /*+ no_merge(vv) */   owner,   view_name from   (select
v.owner,     v.view_name   from     dba_views v,     dba_objects o
where     v.owner = o.owner     AND v.view_name = o.object_name     AND
o.object_type=\'VIEW\'     AND o.status=\'VALID\'     AND v.owner =\'PSFT\'
) vv where   dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like
\'%/*+%\'

Plan hash value: 138201291

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|*  1 |  VIEW                                 |             |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|*  2 |   FILTER                              |             |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|   3 |    NESTED LOOPS                       |             |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|   4 |     NESTED LOOPS                      |             |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|*  5 |      HASH JOIN                        |             |      1 |      3 |      0 |00:00:00.01 |       1 |   927K|   927K|  162K (0)|
|   6 |       VIEW                            | DBA_OBJECTS |      1 |     35 |      0 |00:00:00.01 |       1 |       |       |          |
|   7 |        UNION-ALL                      |             |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID   | SUM$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |          INDEX UNIQUE SCAN            | I_SUM$_1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |         FILTER                        |             |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|  11 |          NESTED LOOPS                 |             |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|  12 |           NESTED LOOPS                |             |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|  13 |            TABLE ACCESS BY INDEX ROWID| USER$       |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|* 14 |             INDEX UNIQUE SCAN         | I_USER1     |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
|* 15 |            TABLE ACCESS BY INDEX ROWID| OBJ$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 16 |             INDEX RANGE SCAN          | I_OBJ5      |      0 |     37 |      0 |00:00:00.01 |       0 |       |       |          |
|* 17 |           INDEX RANGE SCAN            | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 18 |          TABLE ACCESS BY INDEX ROWID  | IND$        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 19 |           INDEX UNIQUE SCAN           | I_IND1      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  20 |          NESTED LOOPS                 |             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 21 |           INDEX FULL SCAN             | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 22 |           INDEX RANGE SCAN            | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 23 |         FILTER                        |             |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  24 |          NESTED LOOPS                 |             |      0 |      6 |      0 |00:00:00.01 |       0 |       |       |          |
|  25 |           TABLE ACCESS BY INDEX ROWID | USER$       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 26 |            INDEX UNIQUE SCAN          | I_USER1     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 27 |           INDEX RANGE SCAN            | I_LINK1     |      0 |      6 |      0 |00:00:00.01 |       0 |       |       |          |
|  28 |       NESTED LOOPS                    |             |      0 |   3675 |      0 |00:00:00.01 |       0 |       |       |          |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 30 |         INDEX UNIQUE SCAN             | I_USER1     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 31 |        INDEX RANGE SCAN               | I_OBJ5      |      0 |   3675 |      0 |00:00:00.01 |       0 |       |       |          |
|* 32 |      INDEX UNIQUE SCAN                | I_VIEW1     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 33 |     INDEX RANGE SCAN                  | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  34 |    NESTED LOOPS                       |             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 35 |     INDEX FULL SCAN                   | I_USER2     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 36 |     INDEX RANGE SCAN                  | I_OBJ4      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DBMS_METADATA"."GET_DDL"(\'VIEW\',"VV"."VIEW_NAME","VV"."OWNER") LIKE \'%/*+%\')
   2 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
              AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
              ((SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))) OR  IS NOT NULL))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
              "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
              (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2) OR
              ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))) OR  IS NOT NULL)))))
  14 - access("U"."NAME"=\'PSFT\')
  15 - filter((DECODE("O"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\' AND BITAND("O"."FLAGS",128)=0))
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>\'_NEXT_OBJECT\' AND "O"."NAME"<>\'_default_auditing_options_\' AND "O"."LINKNAME" IS NULL AND
              DECODE("O"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9,
              \'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX
              PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA
              RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB
              SUBPARTITION\',42,NVL(,\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER
              GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATIO
              N CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE
              GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD
              PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\'))
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
              "I"."TYPE#"=9))
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"=\'PSFT\')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"=\'PSFT\')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type=\'VIEW\'
    AND o.status=\'VALID\'
    AND v.owner =\'PSFT\'  ) vv
where
  dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,\'ALLSTATS LAST\'));

SQL_ID  2rdfv5vn7kq87, child number 0
-------------------------------------
select   owner,   view_name from   (select     v.owner,     v.view_name
  from     dba_views v,     dba_objects o   where     v.owner = o.owner
    AND v.view_name = o.object_name     AND o.object_type=\'VIEW\'
AND o.status=\'VALID\'     AND v.owner =\'PSFT\'  ) vv where
dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\'

Plan hash value: 739349040

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        |     0 |00:00:00.01 |        1 |       |       |          |
|*  1 |  FILTER                              |             |      1 |        |     0 |00:00:00.01 |        1 |       |       |          |
|   2 |   NESTED LOOPS                       |             |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|   3 |    NESTED LOOPS                      |             |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|*  4 |     HASH JOIN                        |             |      1 |      1 |     0 |00:00:00.01 |        1 |   927K|   927K|  155K (0)|
|   5 |      VIEW                            | DBA_OBJECTS |      1 |     35 |     0 |00:00:00.01 |        1 |       |       |          |
|   6 |       UNION-ALL                      |             |      1 |        |     0 |00:00:00.01 |        1 |       |       |          |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$        |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|*  8 |         INDEX UNIQUE SCAN            | I_SUM$_1    |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|*  9 |        FILTER                        |             |      1 |        |     0 |00:00:00.01 |        1 |       |       |          |
|  10 |         NESTED LOOPS                 |             |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|  11 |          NESTED LOOPS                |             |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|  12 |           TABLE ACCESS BY INDEX ROWID| USER$       |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|* 13 |            INDEX UNIQUE SCAN         | I_USER1     |      1 |      1 |     0 |00:00:00.01 |        1 |       |       |          |
|* 14 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 15 |            INDEX RANGE SCAN          | I_OBJ5      |      0 |     37 |     0 |00:00:00.01 |        0 |       |       |          |
|* 16 |          INDEX RANGE SCAN            | I_USER2     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 17 |         TABLE ACCESS BY INDEX ROWID  | IND$        |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 18 |          INDEX UNIQUE SCAN           | I_IND1      |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|  19 |         NESTED LOOPS                 |             |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 20 |          INDEX FULL SCAN             | I_USER2     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 21 |          INDEX RANGE SCAN            | I_OBJ4      |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 22 |        FILTER                        |             |      1 |        |     0 |00:00:00.01 |        0 |       |       |          |
|  23 |         NESTED LOOPS                 |             |      0 |      6 |     0 |00:00:00.01 |        0 |       |       |          |
|  24 |          TABLE ACCESS BY INDEX ROWID | USER$       |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 25 |           INDEX UNIQUE SCAN          | I_USER1     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 26 |          INDEX RANGE SCAN            | I_LINK1     |      0 |      6 |     0 |00:00:00.01 |        0 |       |       |          |
|  27 |      NESTED LOOPS                    |             |      0 |    184 |     0 |00:00:00.01 |        0 |       |       |          |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$       |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 29 |        INDEX UNIQUE SCAN             | I_USER1     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 30 |       INDEX RANGE SCAN               | I_OBJ5      |      0 |    184 |     0 |00:00:00.01 |        0 |       |       |          |
|* 31 |     INDEX RANGE SCAN                 | I_USER2     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 32 |    INDEX UNIQUE SCAN                 | I_VIEW1     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|  33 |   NESTED LOOPS                       |             |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 34 |    INDEX FULL SCAN                   | I_USER2     |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
|* 35 |    INDEX RANGE SCAN                  | I_OBJ4      |      0 |      1 |     0 |00:00:00.01 |        0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
              AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
              ((SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))) OR  IS NOT NULL))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
              "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
              (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' AND "U"."TYPE#"<>2) OR
              ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))) OR  IS NOT NULL)))))
  13 - access("U"."NAME"=\'PSFT\')
  14 - filter((DECODE("O"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\' AND BITAND("O"."FLAGS",128)=0))
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>\'_NEXT_OBJECT\' AND "O"."NAME"<>\'_default_auditing_options_\' AND "O"."LINKNAME" IS NULL AND
              DECODE("O"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9
              ,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX
              PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA
              RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB
              SUBPARTITION\',42,NVL(,\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER
              GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATI
              ON CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE
              GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD
              PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\'))
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
              "I"."TYPE#"=9))
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"=\'PSFT\')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"=\'PSFT\')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"(\'VIEW\',"O"."NAME","U"."NAME") LIKE \'%/*+%\')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Examining the 10053 trace file:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET STATISTICS_LEVEL=\'ALL\';
ALTER SESSION SET TRACEFILE_IDENTIFIER=\'ERROR_SQL\';
ALTER SESSION SET EVENTS \'10053 TRACE NAME CONTEXT FOREVER, LEVEL 1\';

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type=\'VIEW\'
    AND o.status=\'VALID\'
    AND v.owner =\'PSFT\'  ) vv
where
  dbms_metadata.get_ddl(\'VIEW\', vv.view_name, vv.owner) like \'%/*+%\';

ALTER SESSION SET EVENTS \'10053 TRACE NAME CONTEXT OFF\';

In the trace file, the final query after transformation:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL( (SELECT \'REWRITE EQUIVALENCE\' "\'REWRITEEQUIVALENCE\'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",\'YYYY-MM-DD:HH24:MI:SS\') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,\'N\',2,\'Y\',\'N\') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,\'N\',4,\'Y\',\'N\') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,\'N\',16,\'Y\',\'N\') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE  WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN  (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE \'ORA$BASE\' END  "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND  (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>\'_NEXT_OBJECT\' AND "SYS_ALIAS_4"."NAME"<>\'_default_auditing_options_\' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\'))))) AND "U"."NAME"=\'PSFT\' AND DECODE("SYS_ALIAS_4"."TYPE#",0,\'NEXT OBJECT\',1,\'INDEX\',2,\'TABLE\',3,\'CLUSTER\',4,\'VIEW\',5,\'SYNONYM\',6,\'SEQUENCE\',7,\'PROCEDURE\',8,\'FUNCTION\',9,\'PACKAGE\',11,\'PACKAGE BODY\',12,\'TRIGGER\',13,\'TYPE\',14,\'TYPE BODY\',19,\'TABLE PARTITION\',20,\'INDEX PARTITION\',21,\'LOB\',22,\'LIBRARY\',23,\'DIRECTORY\',24,\'QUEUE\',28,\'JAVA SOURCE\',29,\'JAVA CLASS\',30,\'JAVA RESOURCE\',32,\'INDEXTYPE\',33,\'OPERATOR\',34,\'TABLE SUBPARTITION\',35,\'INDEX SUBPARTITION\',40,\'LOB PARTITION\',41,\'LOB SUBPARTITION\',42,NVL( (SELECT \'REWRITE EQUIVALENCE\' "\'REWRITEEQUIVALENCE\'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),\'MATERIALIZED VIEW\'),43,\'DIMENSION\',44,\'CONTEXT\',46,\'RULE SET\',47,\'RESOURCE PLAN\',48,\'CONSUMER GROUP\',51,\'SUBSCRIPTION\',52,\'LOCATION\',55,\'XML SCHEMA\',56,\'JAVA DATA\',57,\'EDITION\',59,\'RULE\',60,\'CAPTURE\',61,\'APPLY\',62,\'EVALUATION CONTEXT\',66,\'JOB\',67,\'PROGRAM\',68,\'JOB CLASS\',69,\'WINDOW\',72,\'SCHEDULER GROUP\',74,\'SCHEDULE\',79,\'CHAIN\',81,\'FILE GROUP\',82,\'MINING MODEL\',87,\'ASSEMBLY\',90,\'CREDENTIAL\',92,\'CUBE DIMENSION\',93,\'CUBE\',94,\'MEASURE FOLDER\',95,\'CUBE BUILD PROCESS\',100,\'FILE WATCHER\',101,\'DESTINATION\',\'UNDEFINED\')=\'VIEW\' AND DECODE("SYS_ALIAS_4"."STATUS",0,\'N/A\',1,\'VALID\',\'INVALID\')=\'VALID\') UNION ALL  (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",\'DATABASE LINK\' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",\'VALID\' "STATUS",\'N\' "TEMPORARY",\'N\' "GENERATED",\'N\' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"=\'PSFT\' AND \'DATABASE LINK\'=\'VIEW\' AND \'VALID\'=\'VALID\')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"(\'VIEW\',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE \'%/*+%\' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"=\'PSFT\' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT(\'userenv\',\'current_edition_name\')=\'ORA$BASE\' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT(\'userenv\',\'current_edition_id\')))))

I suspect that the problem might be caused by missing fixed object statistics. However, you might compare the output of a 10053 trace on your system to see if it produces the same final query after transformation. If it does, verify that all of the objects listed in the final query after transformation actually exist. I wonder if there is maybe a missing synonym, missing permission, object name collision (someone created an object with the same name as a public synonym) or possibly one of the data dictionary catalog scripts failed to complete. Let me know what y

31 12 2010

Martin Berger (16:05:44) :

Charles,
I tried to crosscheck on my system.
1st, I have also 798 rows in SYS.TAB_STATS$. So I assume I have no issues with the statistics?
2nd my unhinted select statement has the same Plan hash value: 739349040. I assume this is enough to say, they are the same.
The 10053 trace is uploaded here https://docs.google.com/leaf?id=0B6c1gsEhfcN1MGMxMWUxYTAtYTkzMS00Y2U1LWFjNjEtZTBkMGE5NzJiMmU0&sort=name&layout=list&num=50
Also there the Final query after transformations looks quite similar to me. I have some guesses right now (du you have a user PSFT with a set of views?) but they are not elaborated.
Maybe I manage to simplify the testcase, probably with generated tables to make it more digestive.
Martin

31 12 2010

Charles Hooper (17:20:07) :

Martin,

I checked both Linux and Window 11.2.0.1, as well as a freshly created template database that was created by the DBCA – that user does not exist in any of the databases. I had not considered that as a possible problem area, so I repeated the test when connected as the SYS user with a slightly modified version of the SQL statement that you provided:

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%';

The AUTOTRACE execution plan for this SQL statement (on 11.2.0.1 Windows 64 bit) looks like this:

Execution Plan
----------------------------------------------------------
Plan hash value: 2706590226

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   106 |    93   (0)| 00:00:01 |
|*  1 |  FILTER                              |             |       |       |            |          |
|   2 |   NESTED LOOPS                       |             |     1 |   106 |    93   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |             |     1 |    84 |    92   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |             |    10 |   590 |    32   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |             |   147 |  7938 |    32   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN               | I_OBJ5      |   147 |  5880 |    31   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN               | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|  10 |     VIEW                             | DBA_OBJECTS |     1 |    25 |     6   (0)| 00:00:01 |
|  11 |      UNION ALL PUSHED PREDICATE      |             |       |       |            |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID    | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|* 13 |        INDEX UNIQUE SCAN             | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|* 14 |       FILTER                         |             |       |       |            |          |
|* 15 |        FILTER                        |             |       |       |            |          |
|  16 |         NESTED LOOPS                 |             |     1 |   120 |     5   (0)| 00:00:01 |
|  17 |          NESTED LOOPS                |             |     1 |    98 |     4   (0)| 00:00:01 |
|  18 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 19 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 20 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |     3   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN          | I_OBJ5      |     1 |       |     2   (0)| 00:00:01 |
|* 22 |          INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 23 |        TABLE ACCESS BY INDEX ROWID   | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN            | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  25 |        NESTED LOOPS                  |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 26 |         INDEX FULL SCAN              | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN             | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 28 |       FILTER                         |             |       |       |            |          |
|  29 |        NESTED LOOPS                  |             |     1 |    31 |     1   (0)| 00:00:01 |
|  30 |         TABLE ACCESS BY INDEX ROWID  | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 31 |          INDEX UNIQUE SCAN           | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 32 |         INDEX RANGE SCAN             | I_LINK1     |     1 |    17 |     0   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                  | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|  34 |   NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 35 |    INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   7 - access("U"."NAME"='SYS')
   8 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%')
   9 - access("O"."OBJ#"="V"."OBJ#")
  12 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
  13 - access("S"."OBJ#"=:B1)
  14 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  15 - filter('_default_auditing_options_'<>"O"."NAME" AND '_NEXT_OBJECT'<>"O"."NAME")
  19 - access("U"."NAME"='SYS')
  20 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
              BITAND("O"."FLAGS",128)=0)
  21 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"="O"."NAME" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
              OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
              UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
              PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
              (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
              VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
              GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
              DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
              CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  22 - access("O"."OWNER#"="U"."USER#")
  23 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  24 - access("I"."OBJ#"=:B1)
  26 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  27 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  28 - filter(NULL IS NOT NULL)
  31 - access("U"."NAME"='SYS')
  32 - access("L"."OWNER#"="U"."USER#" AND "L"."NAME"="O"."NAME")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

I executed the SQL statement with AUTOTRACE turned off, and after a couple of minutes the following appeared:

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            DBA_ADVISOR_SQLSTATS
SYS                            DBA_FEATURE_USAGE_STATISTICS
SYS                            DBA_HIGH_WATER_MARK_STATISTICS
SYS                            DBA_SUBSCRIPTIONS
SYS                            EXU9MVLCDCS
SYS                            GV_$ASH_INFO
SYS                            GV_$INCMETER_SUMMARY
SYS                            GV_$LOGSTDBY_PROGRESS
SYS                            KU$_EDITION_SCHEMAOBJ_VIEW
SYS                            KU$_SCHEMAOBJ_VIEW
SYS                            USER_ADVISOR_SQLSTATS
SYS                            USER_SUBSCRIPTIONS
SYS                            V_$ASH_INFO
SYS                            V_$DIAG_INC_METER_SUMMARY
SYS                            V_$DIAG_VTEST_EXISTS
SYS                            V_$INCMETER_SUMMARY
SYS                            V_$LOGSTDBY_PROGRESS
SYS                            _ALL_QUEUE_SCHEDULES
SYS                            _DBA_QUEUE_SCHEDULES
SYS                            _USER_QUEUE_SCHEDULES

20 rows selected.

For some reason, I am not able to access the 10053 trace file that you shared – ~~you~~ do you need to specify permissions so that I am able to access that file? If you leave off the WHERE clause, do you receive the same number of rows from the hinted and unhinted versions of your query?

Are you able to execute my modified version of the SQL statement?

1 01 2011

Martin Berger (02:56:55) :

I tried to lower the permissions on the google doc document as good as I could. but it seems a login is still required. To lower these barriers, I put the file at http://berx.at/traces/PSCRT022_ora_22249_ERROR_SQL.trc .
You will not find the user PSFT in any general Oracle DB. My particular DB was populated with exp/imp from a 9i Peoplesoft database.

Your modified version of the SQL-statement provided this AUTOTRACE:

alter system flush shared_pool;

System altered.

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN


select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%';
  
SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  
Execution Plan
----------------------------------------------------------
Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   107 |	66   (2)| 00:00:02 |
|*  1 |  FILTER                              |             |       |	   |		|          |
|   2 |   NESTED LOOPS                       |             |     1 |   107 |	66   (2)| 00:00:02 |
|   3 |    NESTED LOOPS                      |             |     1 |   102 |	66   (2)| 00:00:02 |
|*  4 |     HASH JOIN                        |             |     1 |	80 |	65   (2)| 00:00:01 |
|   5 |      VIEW                            | DBA_OBJECTS |    26 |   624 |	40   (0)| 00:00:01 |
|   6 |       UNION-ALL                      |             |       |	   |		|          |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$        |     1 |	26 |	 0   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN            | I_SUM$_1    |     1 |	   |	 0   (0)| 00:00:01 |
|*  9 |        FILTER                        |             |       |	   |		|          |
|  10 |         NESTED LOOPS                 |             |     1 |   121 |	40   (0)| 00:00:01 |
|  11 |          NESTED LOOPS                |             |     1 |	99 |	39   (0)| 00:00:01 |
|  12 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |	17 |	 1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |	   |	 0   (0)| 00:00:01 |
|* 14 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |	82 |	38   (0)| 00:00:01 |
|* 15 |            INDEX RANGE SCAN          | I_OBJ5      |    25 |	   |	23   (0)| 00:00:01 |
|* 16 |          INDEX RANGE SCAN            | I_USER2     |     1 |	22 |	 1   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS BY INDEX ROWID  | IND$        |     1 |	 8 |	 2   (0)| 00:00:01 |
|* 18 |          INDEX UNIQUE SCAN           | I_IND1      |     1 |	   |	 1   (0)| 00:00:01 |
|  19 |         NESTED LOOPS                 |             |     1 |	29 |	 2   (0)| 00:00:01 |
|* 20 |          INDEX FULL SCAN             | I_USER2     |     1 |	20 |	 1   (0)| 00:00:01 |
|* 21 |          INDEX RANGE SCAN            | I_OBJ4      |     1 |	 9 |	 1   (0)| 00:00:01 |
|* 22 |        FILTER                        |             |       |	   |		|          |
|  23 |         NESTED LOOPS                 |             |     2 |	54 |	 2   (0)| 00:00:01 |
|  24 |          TABLE ACCESS BY INDEX ROWID | USER$       |     1 |	17 |	 1   (0)| 00:00:01 |
|* 25 |           INDEX UNIQUE SCAN          | I_USER1     |     1 |	   |	 0   (0)| 00:00:01 |
|* 26 |          INDEX RANGE SCAN            | I_LINK1     |     2 |	20 |	 1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS                    |             |   127 |  7112 |	24   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |	17 |	 1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |	   |	 0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN               | I_OBJ5      |   127 |  4953 |	23   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN                 | I_USER2     |     1 |	22 |	 1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN                 | I_VIEW1     |     1 |	 5 |	 0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS                       |             |     1 |	29 |	 2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN                   | I_USER2     |     1 |	20 |	 1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |	 9 |	 1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND
	      "O"."TYPE#"9 AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND
	      "O"."TYPE#"13 AND "O"."TYPE#"14 AND "O"."TYPE#"22 AND "O"."TYPE#"87 AND
	      "O"."TYPE#"88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
	      "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
	      "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
	      (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"2 OR
	      "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
	      EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
	      "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
	      "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND	(SELECT 1 FROM
	      "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
	      OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
	      ("O"."TYPE#"4 AND "O"."TYPE#"5 AND "O"."TYPE#"7 AND "O"."TYPE#"8 AND "O"."TYPE#"9
	      AND "O"."TYPE#"10 AND "O"."TYPE#"11 AND "O"."TYPE#"12 AND "O"."TYPE#"13 AND
	      "O"."TYPE#"14 AND "O"."TYPE#"22 AND "O"."TYPE#"87 AND "O"."TYPE#"88 OR
	      BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
	      "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
	      "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
	      (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"2 OR
	      "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
	      EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
	      "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
	      "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  13 - access("U"."NAME"='SYS')
  14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
	      BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND
	      "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
	      OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
	      UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
	      PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
	      SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
	      SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
	      (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
	      BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
	      VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
	      GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
	      DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
	      CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
	      GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
	      MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
	      FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
	      "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
	      ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
	      ion_id')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"='SYS')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"='SYS')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
	      ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
	      ion_id')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

and this result:
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%'
  *
ERROR at line 18:
ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

Running ‘my’ SQL (owner=PSFT) witout the outer WHERE gibes me 8802 rows back in any case.
(and I did a quickcheck with AUTOTRACE, they generated different execution plans).

I’m quite sure I did not manipulate the data dictionary (as there are also other DBAs I can not be 100% sure on this kind of test/sandbox system).

(edited to remove tabs from the execution plan to improve readability)

1 01 2011

Martin Berger (06:23:45) :

Hi Charles,
Some hours without a keyboard but with my children made my brain working again. So I changed the way I attack my problem:
Based on the Final query after transformations here the part of interest:

SELECT "U"."NAME"           "OWNER", 
       "SYS_ALIAS_2"."NAME" "VIEW_NAME" 
FROM   SYS."OBJ$" "SYS_ALIAS_2", 
       SYS."USER$" "SYS_ALIAS_1", 
       "SYS"."VIEW$" "V", 
       "SYS"."USER$" "U", 
       ...
WHERE  "SYS"."DBMS_METADATA"."Get_ddl"('VIEW', "SYS_ALIAS_2"."NAME", "U"."NAME") 
       LIKE 
              '%/*+%' 
       AND "SYS_ALIAS_2"."NAME" = "O"."OBJECT_NAME" 
       AND "U"."NAME" = 'PSFT' 
       AND "SYS_ALIAS_2"."OBJ#" = "V"."OBJ#" 
       AND "SYS_ALIAS_2"."SPARE3" = "U"."USER#" 
       ...

The dba_views.owner and dba_views.view_name does NOT come from one table, but from a join of OBJ$ and SYS.USER$ (and some others). To get this tupel, Oracle joins USER$ (after the filter for NAME via I_USER1) with I_OBJ5. In this join (access(“O”.”SPARE3″=”U”.”USER#”)) at the same time it has all informations it needs to apply this filter also: filter(“DBMS_METADATA”.”GET_DDL”(‘VIEW’,”O”.”NAME”,”U”.”NAME”) LIKE ‘%TEST%’)
But the filter, if the data returned from I_OBJ5 is really a View (access(“O”.”OBJ#”=”V”.”OBJ#”)) is done later.
There is nothing bad about this action, only DBMS_METADATA.GET_DDL returns an error if the object named by 2nd and 3rd parameter does not match the 1st.
In my case it tries to provide the DDL for “SYS”.”/1000323d_DelegateInvocationHa” (which is a java related object) and fails.

Are my observations correct?

Martin

1 01 2011

Charles Hooper (08:56:40) :

It is interesting to note that the hash value for your plan matches the hash value that I received on 11.2.0.1 on 64 bit Linux, but the hash value for the plan does NOT match the hash value that I received on 11.2.0.1 on 64 bit Windows. With that in mind, what happens when I run the following script on 11.2.0.1 on 64 bit Linux?

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%';
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

This is what I received:

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'
  *
ERROR at line 18:
ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

That error message seems to match what you received, which suggests that this is a bug in the 11.2.0.1 Linux release which was fixed before the 11.2.0.1 Windows release (I think that the Windows release was launched 3 or 4 months later).

Here is the final query after transformation from 11.2.0.1 Linux:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE  WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN  (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END  "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND  (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='SYS' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID') UNION ALL  (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='SYS' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"('VIEW',"SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%/*+%' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"='SYS' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
*************************

Here is the execution plan from the 10053 trace file:

============
Plan Table
============
-----------------------------------------------------------+-----------------------------------+
| Id  | Operation                             | Name       | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                      |            |       |       |   124 |           |
| 1   |  FILTER                               |            |       |       |       |           |
| 2   |   NESTED LOOPS                        |            |     1 |   106 |   124 |  00:00:02 |
| 3   |    NESTED LOOPS                       |            |     1 |   101 |   124 |  00:00:02 |
| 4   |     HASH JOIN                         |            |     1 |    79 |   123 |  00:00:02 |
| 5   |      VIEW                             | DBA_OBJECTS|    35 |   875 |    83 |  00:00:01 |
| 6   |       UNION-ALL                       |            |       |       |       |           |
| 7   |        TABLE ACCESS BY INDEX ROWID    | SUM$       |     1 |    11 |     1 |  00:00:01 |
| 8   |         INDEX UNIQUE SCAN             | I_SUM$_1   |     1 |       |     0 |           |
| 9   |        FILTER                         |            |       |       |       |           |
| 10  |         NESTED LOOPS                  |            |     1 |   120 |    65 |  00:00:01 |
| 11  |          NESTED LOOPS                 |            |     1 |    98 |    64 |  00:00:01 |
| 12  |           TABLE ACCESS BY INDEX ROWID | USER$      |     1 |    14 |     1 |  00:00:01 |
| 13  |            INDEX UNIQUE SCAN          | I_USER1    |     1 |       |     0 |           |
| 14  |           TABLE ACCESS BY INDEX ROWID | OBJ$       |     1 |    84 |    63 |  00:00:01 |
| 15  |            INDEX RANGE SCAN           | I_OBJ5     |    37 |       |    38 |  00:00:01 |
| 16  |          INDEX RANGE SCAN             | I_USER2    |     1 |    22 |     1 |  00:00:01 |
| 17  |         TABLE ACCESS BY INDEX ROWID   | IND$       |     1 |     8 |     2 |  00:00:01 |
| 18  |          INDEX UNIQUE SCAN            | I_IND1     |     1 |       |     1 |  00:00:01 |
| 19  |         NESTED LOOPS                  |            |     1 |    29 |     2 |  00:00:01 |
| 20  |          INDEX FULL SCAN              | I_USER2    |     1 |    20 |     1 |  00:00:01 |
| 21  |          INDEX RANGE SCAN             | I_OBJ4     |     1 |     9 |     1 |  00:00:01 |
| 22  |        FILTER                         |            |       |       |       |           |
| 23  |         NESTED LOOPS                  |            |     6 |   180 |     2 |  00:00:01 |
| 24  |          TABLE ACCESS BY INDEX ROWID  | USER$      |     1 |    14 |     1 |  00:00:01 |
| 25  |           INDEX UNIQUE SCAN           | I_USER1    |     1 |       |     0 |           |
| 26  |          INDEX RANGE SCAN             | I_LINK1    |     6 |    96 |     1 |  00:00:01 |
| 27  |      NESTED LOOPS                     |            |   184 |  9936 |    39 |  00:00:01 |
| 28  |       TABLE ACCESS BY INDEX ROWID     | USER$      |     1 |    14 |     1 |  00:00:01 |
| 29  |        INDEX UNIQUE SCAN              | I_USER1    |     1 |       |     0 |           |
| 30  |       INDEX RANGE SCAN                | I_OBJ5     |   184 |  7360 |    38 |  00:00:01 |
| 31  |     INDEX RANGE SCAN                  | I_USER2    |     1 |    22 |     1 |  00:00:01 |
| 32  |    INDEX UNIQUE SCAN                  | I_VIEW1    |     1 |     5 |     0 |           |
| 33  |   NESTED LOOPS                        |            |     1 |    29 |     2 |  00:00:01 |
| 34  |    INDEX FULL SCAN                    | I_USER2    |     1 |    20 |     1 |  00:00:01 |
| 35  |    INDEX RANGE SCAN                   | I_OBJ4     |     1 |     9 |     1 |  00:00:01 |
-----------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL))))
4 - access("O"."NAME"="O"."OBJECT_NAME")
7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
8 - access("S"."OBJ#"=:B1)
9 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
13 - access("U"."NAME"='SYS')
14 - filter((DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0))
15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
15 - filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW'))
16 - access("O"."OWNER#"="U"."USER#")
17 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
18 - access("I"."OBJ#"=:B1)
20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
20 - filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
22 - filter(NULL IS NOT NULL)
25 - access("U"."NAME"='SYS')
26 - access("L"."OWNER#"="U"."USER#")
29 - access("U"."NAME"='SYS')
30 - access("O"."SPARE3"="U"."USER#")
30 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
31 - access("O"."OWNER#"="U"."USER#")
32 - access("O"."OBJ#"="V"."OBJ#")
34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
34 - filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

It might be worthwhile to search through Metalink to see if there is a one-off patch for Linux that corrects this specific problem, or investigate if it is possible to upgrade to 11.2.0.2. This is certainly an interesting problem.

1 01 2011

Charles Hooper (10:25:07) :

I am a bit unsure what happened. Now when I execute this script in 11.2.0.1 on 64 bit Windows I receive the same error that I saw on Linux:

ALTER SESSION SET TRACEFILE_IDENTIFIER='TEST_SYS';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  
select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%'; 
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

  dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%TEST%'
  *
ERROR at line 18:
ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in
schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

Last night when I excuted this same SQL statement it retrieved 20 rows. This is the plan that I am currently receiving with AUTOTRACE enabled:

Execution Plan
----------------------------------------------------------
Plan hash value: 2706590226

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   106 |    93   (0)| 00:00:01 |
|*  1 |  FILTER                              |             |       |       |            |          |
|   2 |   NESTED LOOPS                       |             |     1 |   106 |    93   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |             |     1 |    84 |    92   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |             |    10 |   590 |    32   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                    |             |   147 |  7938 |    32   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN               | I_OBJ5      |   147 |  5880 |    31   (0)| 00:00:01 |
|*  9 |      INDEX UNIQUE SCAN               | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|  10 |     VIEW                             | DBA_OBJECTS |     1 |    25 |     6   (0)| 00:00:01 |
|  11 |      UNION ALL PUSHED PREDICATE      |             |       |       |            |          |
|* 12 |       TABLE ACCESS BY INDEX ROWID    | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|* 13 |        INDEX UNIQUE SCAN             | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|* 14 |       FILTER                         |             |       |       |            |          |
|* 15 |        FILTER                        |             |       |       |            |          |
|  16 |         NESTED LOOPS                 |             |     1 |   120 |     5   (0)| 00:00:01 |
|  17 |          NESTED LOOPS                |             |     1 |    98 |     4   (0)| 00:00:01 |
|  18 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 19 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 20 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |     3   (0)| 00:00:01 |
|* 21 |            INDEX RANGE SCAN          | I_OBJ5      |     1 |       |     2   (0)| 00:00:01 |
|* 22 |          INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 23 |        TABLE ACCESS BY INDEX ROWID   | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN            | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  25 |        NESTED LOOPS                  |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 26 |         INDEX FULL SCAN              | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN             | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 28 |       FILTER                         |             |       |       |            |          |
|  29 |        NESTED LOOPS                  |             |     1 |    31 |     1   (0)| 00:00:01 |
|  30 |         TABLE ACCESS BY INDEX ROWID  | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 31 |          INDEX UNIQUE SCAN           | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 32 |         INDEX RANGE SCAN             | I_LINK1     |     1 |    17 |     0   (0)| 00:00:01 |
|* 33 |    INDEX RANGE SCAN                  | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|  34 |   NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 35 |    INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 36 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   7 - access("U"."NAME"='SYS')
   8 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%TEST%')
   9 - access("O"."OBJ#"="V"."OBJ#")
  12 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
  13 - access("S"."OBJ#"=:B1)
  14 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  15 - filter('_default_auditing_options_'<>"O"."NAME" AND '_NEXT_OBJECT'<>"O"."NAME")
  19 - access("U"."NAME"='SYS')
  20 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
              BITAND("O"."FLAGS",128)=0)
  21 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"="O"."NAME" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
              OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
              UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
              PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
              (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
              VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
              GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
              DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
              CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  22 - access("O"."OWNER#"="U"."USER#")
  23 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  24 - access("I"."OBJ#"=:B1)
  26 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  27 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  28 - filter(NULL IS NOT NULL)
  31 - access("U"."NAME"='SYS')
  32 - access("L"."OWNER#"="U"."USER#" AND "L"."NAME"="O"."NAME")
  33 - access("O"."OWNER#"="U"."USER#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

There is only one mention of object “/1000323d_DelegateInvocationHa” on Metalink, in this article:
“Bug 6964025: Exceptions while trying to configure JDBC Connection Validation in GF 3.0.1”

Checking DBA_OBJECTS, I see that “/1000323d_DelegateInvocationHa” is a Java class that is owned by the SYS user, and is also a public synonym – it should have been eliminated due to the o.object_type=’VIEW’ predicate in the WHERE clause. The problem is that the o.object_type=’VIEW’ predicate is not applied until execution plan ID 21, which in the unhinted plan is executed after the call to DBMS_METADATA.GET_DDL on line 8 of the execution plan – not all objects in the database are views, and not all objects are owned by one user.

What you might be able to do is something like this:

ALTER SESSION SET TRACEFILE_IDENTIFIER='TEST_SYS2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
  
select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name,
    o.object_type
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl(VV.OBJECT_TYPE, vv.view_name, vv.owner) like '%TEST%'; 
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

In the above, I added the OBJECT_TYPE column to the list of columns retrieved by the inline view, and then passed that column into the DBMS_METADATA.GET_DDL call. That change allowed the SQL statement to complete – return 20 rows. Now the final query after transformation is this:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "U"."NAME" "OWNER","SYS_ALIAS_2"."NAME" "VIEW_NAME" FROM SYS."OBJ$" "SYS_ALIAS_2",SYS."USER$" "SYS_ALIAS_1","SYS"."VIEW$" "V","SYS"."USER$" "U", ( (SELECT "U"."NAME" "OWNER","SYS_ALIAS_4"."NAME" "OBJECT_NAME","SYS_ALIAS_4"."SUBNAME" "SUBOBJECT_NAME","SYS_ALIAS_4"."OBJ#" "OBJECT_ID","SYS_ALIAS_4"."DATAOBJ#" "DATA_OBJECT_ID",DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED') "OBJECT_TYPE","SYS_ALIAS_4"."CTIME" "CREATED","SYS_ALIAS_4"."MTIME" "LAST_DDL_TIME",TO_CHAR("SYS_ALIAS_4"."STIME",'YYYY-MM-DD:HH24:MI:SS') "TIMESTAMP",DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID') "STATUS",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",2),0,'N',2,'Y','N') "TEMPORARY",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",4),0,'N',4,'Y','N') "GENERATED",DECODE(BITAND("SYS_ALIAS_4"."FLAGS",16),0,'N',16,'Y','N') "SECONDARY","SYS_ALIAS_4"."NAMESPACE" "NAMESPACE",CASE  WHEN (("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87) OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0) THEN NULL WHEN "SYS_ALIAS_3"."TYPE#"=2 THEN  (SELECT "EO"."NAME" "NAME" FROM SYS."OBJ$" "EO" WHERE "EO"."OBJ#"="SYS_ALIAS_3"."SPARE2") ELSE 'ORA$BASE' END  "EDITION_NAME" FROM SYS."OBJ$" "SYS_ALIAS_4",SYS."USER$" "SYS_ALIAS_3","SYS"."USER$" "U" WHERE "SYS_ALIAS_4"."SPARE3"="U"."USER#" AND "SYS_ALIAS_4"."LINKNAME" IS NULL AND ("SYS_ALIAS_4"."TYPE#"<>1 AND "SYS_ALIAS_4"."TYPE#"<>10 OR "SYS_ALIAS_4"."TYPE#"=1 AND  (SELECT 1 "1" FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND "SYS_ALIAS_4"."NAME"<>'_NEXT_OBJECT' AND "SYS_ALIAS_4"."NAME"<>'_default_auditing_options_' AND BITAND("SYS_ALIAS_4"."FLAGS",128)=0 AND "SYS_ALIAS_4"."OWNER#"="SYS_ALIAS_3"."USER#" AND ("SYS_ALIAS_4"."TYPE#"<>4 AND "SYS_ALIAS_4"."TYPE#"<>5 AND "SYS_ALIAS_4"."TYPE#"<>7 AND "SYS_ALIAS_4"."TYPE#"<>8 AND "SYS_ALIAS_4"."TYPE#"<>9 AND "SYS_ALIAS_4"."TYPE#"<>10 AND "SYS_ALIAS_4"."TYPE#"<>11 AND "SYS_ALIAS_4"."TYPE#"<>12 AND "SYS_ALIAS_4"."TYPE#"<>13 AND "SYS_ALIAS_4"."TYPE#"<>14 AND "SYS_ALIAS_4"."TYPE#"<>22 AND "SYS_ALIAS_4"."TYPE#"<>87 AND "SYS_ALIAS_4"."TYPE#"<>88 OR BITAND("SYS_ALIAS_3"."SPARE1",16)=0 OR ("SYS_ALIAS_4"."TYPE#"=4 OR "SYS_ALIAS_4"."TYPE#"=5 OR "SYS_ALIAS_4"."TYPE#"=7 OR "SYS_ALIAS_4"."TYPE#"=8 OR "SYS_ALIAS_4"."TYPE#"=9 OR "SYS_ALIAS_4"."TYPE#"=10 OR "SYS_ALIAS_4"."TYPE#"=11 OR "SYS_ALIAS_4"."TYPE#"=12 OR "SYS_ALIAS_4"."TYPE#"=13 OR "SYS_ALIAS_4"."TYPE#"=14 OR "SYS_ALIAS_4"."TYPE#"=22 OR "SYS_ALIAS_4"."TYPE#"=87) AND ("SYS_ALIAS_3"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_3"."TYPE#"=2 AND "SYS_ALIAS_3"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_4"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) AND "U"."NAME"='SYS' AND DECODE("SYS_ALIAS_4"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' "'REWRITEEQUIVALENCE'" FROM SYS."SUM$" "S" WHERE "S"."OBJ#"="SYS_ALIAS_4"."OBJ#" AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW' AND DECODE("SYS_ALIAS_4"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID') UNION ALL  (SELECT "U"."NAME" "OWNER","L"."NAME" "OBJECT_NAME",NULL "SUBOBJECT_NAME",TO_NUMBER(NULL) "OBJECT_ID",TO_NUMBER(NULL) "DATA_OBJECT_ID",'DATABASE LINK' "OBJECT_TYPE","L"."CTIME" "CREATED",TO_DATE(NULL) "LAST_DDL_TIME",NULL "TIMESTAMP",'VALID' "STATUS",'N' "TEMPORARY",'N' "GENERATED",'N' "SECONDARY",NULL "NAMESPACE",NULL "EDITION_NAME" FROM "SYS"."LINK$" "L","SYS"."USER$" "U" WHERE "L"."OWNER#"="U"."USER#" AND "U"."NAME"='SYS' AND 'DATABASE LINK'='VIEW' AND 'VALID'='VALID')) "O" WHERE "SYS"."DBMS_METADATA"."GET_DDL"("O"."OBJECT_TYPE","SYS_ALIAS_2"."NAME","U"."NAME") LIKE '%TEST%' AND "SYS_ALIAS_2"."NAME"="O"."OBJECT_NAME" AND "U"."NAME"='SYS' AND "SYS_ALIAS_2"."OBJ#"="V"."OBJ#" AND "SYS_ALIAS_2"."SPARE3"="U"."USER#" AND "SYS_ALIAS_2"."OWNER#"="SYS_ALIAS_1"."USER#" AND ("SYS_ALIAS_2"."TYPE#"<>4 AND "SYS_ALIAS_2"."TYPE#"<>5 AND "SYS_ALIAS_2"."TYPE#"<>7 AND "SYS_ALIAS_2"."TYPE#"<>8 AND "SYS_ALIAS_2"."TYPE#"<>9 AND "SYS_ALIAS_2"."TYPE#"<>10 AND "SYS_ALIAS_2"."TYPE#"<>11 AND "SYS_ALIAS_2"."TYPE#"<>12 AND "SYS_ALIAS_2"."TYPE#"<>13 AND "SYS_ALIAS_2"."TYPE#"<>14 AND "SYS_ALIAS_2"."TYPE#"<>22 AND "SYS_ALIAS_2"."TYPE#"<>87 AND "SYS_ALIAS_2"."TYPE#"<>88 OR BITAND("SYS_ALIAS_1"."SPARE1",16)=0 OR ("SYS_ALIAS_2"."TYPE#"=4 OR "SYS_ALIAS_2"."TYPE#"=5 OR "SYS_ALIAS_2"."TYPE#"=7 OR "SYS_ALIAS_2"."TYPE#"=8 OR "SYS_ALIAS_2"."TYPE#"=9 OR "SYS_ALIAS_2"."TYPE#"=10 OR "SYS_ALIAS_2"."TYPE#"=11 OR "SYS_ALIAS_2"."TYPE#"=12 OR "SYS_ALIAS_2"."TYPE#"=13 OR "SYS_ALIAS_2"."TYPE#"=14 OR "SYS_ALIAS_2"."TYPE#"=22 OR "SYS_ALIAS_2"."TYPE#"=87) AND ("SYS_ALIAS_1"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "SYS_ALIAS_1"."TYPE#"=2 AND "SYS_ALIAS_1"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "O2",SYS."USER$" "U2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"="SYS_ALIAS_2"."OBJ#" AND "O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))

This is an AUTOTRACE version of the execution plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     1 |   117 |   102   (0)| 00:00:01 |
|*  1 |  FILTER                              |             |       |       |            |          |
|   2 |   NESTED LOOPS                       |             |     1 |   117 |   102   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                      |             |     1 |   112 |   102   (0)| 00:00:01 |
|*  4 |     HASH JOIN                        |             |     1 |    90 |   101   (0)| 00:00:01 |
|   5 |      VIEW                            | DBA_OBJECTS |    28 |  1008 |    69   (0)| 00:00:01 |
|   6 |       UNION-ALL                      |             |       |       |            |          |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$        |     1 |    11 |     1   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN            | I_SUM$_1    |     1 |       |     0   (0)| 00:00:01 |
|*  9 |        FILTER                        |             |       |       |            |          |
|  10 |         NESTED LOOPS                 |             |     1 |   120 |    54   (0)| 00:00:01 |
|  11 |          NESTED LOOPS                |             |     1 |    98 |    53   (0)| 00:00:01 |
|  12 |           TABLE ACCESS BY INDEX ROWID| USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 14 |           TABLE ACCESS BY INDEX ROWID| OBJ$        |     1 |    84 |    52   (0)| 00:00:01 |
|* 15 |            INDEX RANGE SCAN          | I_OBJ5      |    29 |       |    31   (0)| 00:00:01 |
|* 16 |          INDEX RANGE SCAN            | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS BY INDEX ROWID  | IND$        |     1 |     8 |     2   (0)| 00:00:01 |
|* 18 |          INDEX UNIQUE SCAN           | I_IND1      |     1 |       |     1   (0)| 00:00:01 |
|  19 |         NESTED LOOPS                 |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 20 |          INDEX FULL SCAN             | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 21 |          INDEX RANGE SCAN            | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
|* 22 |        FILTER                        |             |       |       |            |          |
|  23 |         NESTED LOOPS                 |             |     6 |   186 |     2   (0)| 00:00:01 |
|  24 |          TABLE ACCESS BY INDEX ROWID | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 25 |           INDEX UNIQUE SCAN          | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 26 |          INDEX RANGE SCAN            | I_LINK1     |     6 |   102 |     1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS                    |             |  2947 |   155K|    32   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$       |     1 |    14 |     1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN             | I_USER1     |     1 |       |     0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN               | I_OBJ5      |  2947 |   115K|    31   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN                 | I_USER2     |     1 |    22 |     1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN                 | I_VIEW1     |     1 |     5 |     0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS                       |             |     1 |    29 |     2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN                   | I_USER2     |     1 |    20 |     1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN                  | I_OBJ4      |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
       filter("DBMS_METADATA"."GET_DDL"("O"."OBJECT_TYPE","O"."NAME","U"."NAME") LIKE
              '%TEST%')
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
              AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
              "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  13 - access("U"."NAME"='SYS')
  14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
              BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
              OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
              UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
              PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
              SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
              SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
              (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
              BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
              VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
              GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
              DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
              CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
              GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
              MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
              FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"='SYS')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"='SYS')
  30 - access("O"."SPARE3"="U"."USER#")
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

I still do not know why I was able to excute my test SQL statement last night without problem, but cannot execute it today – I thought that computer software was supposed to be logically consistent. 🙂 (Maybe I should spend some more time looking at the execution plans.)

1 01 2011

Martin Berger (11:15:39) :

Hmm, I do not have Oracle@Windows available anywhere around, so I tested some of the versions here.
To keep it simpler I used this test-statement:
select
owner,
view_name
from
(select
v.owner, v.view_name
from
dba_views v
where
v.owner =’SYS’ ) vv
where
dbms_metadata.get_ddl(‘VIEW’, vv.view_name, vv.owner) like ‘%TEST%’;

I tested 11.2.0.2 @ Linux 64bit, 11.2.0.1 @ Linux 64bit, 11.1.0.7 @ Solaris 64bit, 10.2.0.4 @ Solaris 64bit, 9.2.0.8 @ Solaris 64bit.
I found this errorstack everywhere. (To not spam this Reply, I put the results into http://berx.at/traces/sql_20110101.txt ).
From my point of view Windows is the alien 😉
Do you have any posibility to croscheck with other Versions/OS?
Martin

1 01 2011

Charles Hooper (11:39:48) :

Martin,

Good news (or maybe bad news). Today 11.2.0.1 on Windows is now giving me the same error message as on Linux – see my previous two comments.

I only spent a couple of minutes reviewing the execution plan where the SQL statement fails to execute. However, in one of my previous comments I mentioned that the execution plan indicated that Oracle was processing this line in the execution plan on plan ID line 8:

dbms_metadata.get_ddl(‘VIEW’, vv.view_name, vv.owner)

That was processed *before* it filtered the list of objects to eliminate everything that was not a VIEW (that was on plan ID line 21). The owner is filtered on plan ID lines 7, 19 and 31, so line 7 should have been able to restrict the objects to just those owned by SYS when line 8 of the execution plan was executed through a nested loops join. Please try the *fixed* version of the SQL statement that I provided to you:

ALTER SESSION SET TRACEFILE_IDENTIFIER='TEST_SYS2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name,
    o.object_type
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl(VV.OBJECT_TYPE, vv.view_name, vv.owner) like '%TEST%'; 

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

1 01 2011

Martin Berger (15:26:02) :

Here is the 10053 trace file of the latest statement (from my ‘original’ DB): http://berx.at/traces/PSCRT021_ora_11954_TEST_SYS2.trc

Do you agree it’s a problem of the function dbms_metadata.get_ddl() in case it gets some parameter combinations it does not expect?

To come back to the topic of this blog: what’s ‘invalid’ here? I don’t think we can blame the optimizer for that, can we?

There might be some ways to circumvent it: you show one, but this still can fail if the optimizer decides to push the filter too ‘deep’.
My ‘no_merge’ hint will also prevent this behavior. But I’d expect a SQL to return the same values (and fail the same way) regardless of any hint?
Oracle could also associate some extended statistics to the function dbms_stats.get_ddl to make it so ‘expensive’ to the optimizer, the optimizer would always try to elaborate the smallest result-set possible before apply the ‘expensive’ get_ddl function to the ‘totally elaborated’ sub-result.
What do you think?
Is there anything we can dwell deeper?
(I’d like to play ma preferred support-game *g*)

1 01 2011

Charles Hooper (16:37:06) :

Martin,

It would be interesting to see if my modified version of your query could still fail to execute (I do not know the answer).

From looking at the execution plan, I am note sure that this would be called a bug – at least not a bug with DBMS_METADATA.GET_DDL.

Consider this example. Assume that there are three (and only three) objects in a schema:

CREATE TABLE T1_TEST AS
SELECT
  ROWNUM C1,
  10000-ROWNUM C2,
  TO_CHAR(TRUNC(SYSDATE+ROWNUM),'DAY') C3,
  LPAD('A',100,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100;
 
CREATE INDEX T1_TEST_C1_IND ON T1_TEST(C1)
 
CREATE VIEW T1_TEST_VIEW AS
SELECT
  *
FROM
  T1_TEST;

You then code a PL/SQL function (maybe someone more skilled with PL/SQL than me can code an example) with a couple of IN parameters. If the word ‘VIEW’ is passed in as a parameter, the PL/SQL function takes one code path, maybe it selects all rows from the view. If the word ‘INDEX’ is passed in as a parameter, the PL/SQL function takes a different code path, maybe selecting a list of columns that are in the index definition. If you then code error checking into the PL/SQL function so that it returns an error message when the user submits an impossible combination (‘VIEW’ and ‘T1_TEST_C1_IND’, for example), that is probably considered good programming practice – possibly better than returning a NULL or random garbage.

I believe that the above is what is happening in your SQL statement. DBA_VIEWS and DBA_OBJECTS are not simple tables, but instead are complex views that are built from several underlying objects. Through an unfortunate optimization, the optimizer has decided to pass the object names through the PL/SQL function (DBMS_METADATA.GET_DDL in your case) before that object list is reduced to just include the list of object names that *are* views.

See if the following will work – it worked OK for me on 11.2.0.1 (64 bit Windows), although it was slow:

select /*+ NO_QUERY_TRANSFORMATION */
  owner,
  view_name
from
  (SELECT
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    OWNER,
    VIEW_NAME
  FROM
    (select
      v.owner,
      v.view_name,
      o.object_type
    from
      dba_views v,
      dba_objects o
    where
      v.owner = o.owner
      AND v.view_name = o.object_name
      AND o.object_type='VIEW'
      AND o.status='VALID'
      AND v.owner ='SYS'  ) vv
  )
WHERE
  DDL like '%TEST%';

1 01 2011

Charles Hooper (16:43:45) :

Just tested on 11.2.0.1 64 bit Linux, and it also works there:

SQL> select /*+ NO_QUERY_TRANSFORMATION */
  owner,
  view_name
from
  (SELECT
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    OWNER,
    VIEW_NAME
  FROM
    (select
      v.owner,
      v.view_name,
      o.object_type
    from
      dba_views v,
      dba_objects o
    where
      v.owner = o.owner
      AND v.view_name = o.object_name
      AND o.object_type='VIEW'
      AND o.status='VALID'
      AND v.owner ='SYS'  ) vv
  )
WHERE
  DDL like '%TEST%';
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25  

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            GV_$ASH_INFO
SYS                            V_$ASH_INFO
SYS                            GV_$INCMETER_SUMMARY
SYS                            V_$INCMETER_SUMMARY
SYS                            DBA_SUBSCRIPTIONS
SYS                            USER_SUBSCRIPTIONS
SYS                            EXU9MVLCDCS
SYS                            DBA_FEATURE_USAGE_STATISTICS
SYS                            DBA_HIGH_WATER_MARK_STATISTICS
SYS                            V_$DIAG_INC_METER_SUMMARY
SYS                            V_$DIAG_VTEST_EXISTS
SYS                            DBA_ADVISOR_SQLSTATS
SYS                            USER_ADVISOR_SQLSTATS
SYS                            _ALL_QUEUE_SCHEDULES
SYS                            _DBA_QUEUE_SCHEDULES
SYS                            _USER_QUEUE_SCHEDULES
SYS                            KU$_SCHEMAOBJ_VIEW
SYS                            KU$_EDITION_SCHEMAOBJ_VIEW
SYS                            V_$LOGSTDBY_PROGRESS
SYS                            GV_$LOGSTDBY_PROGRESS

20 rows selected.

1 01 2011

Martin Berger (17:13:56) :

I (re-)tested both of your statements – both worked fine.
I assume our observations are now quite consistent. My initial goal to find what’s going on there is met.

You raised a good question about good programming practice. Basically dbms_metadata.get_ddl is supposed to be used in the SELECT part of a query, my utilisation as a filter is quite un-common. So should the (any) function be coded to fulfill the designed situation (and throw an error if anything is wrong) of should it stay any situation where it can be used? Quite a nice question about programming principles.

1 01 2011

Charles Hooper (20:29:11) :

Martin,

I made some poor assumptions yesterday when Oracle on Windows was able to execute my test SQL statement and Linux could not execute the test SQL statement – it must be a year 2011 problem because Oracle on Windows cannot execute the test SQL statement today either. 🙂

I did not spend as much time looking at the execution plan that you provided as I should have, instead I wasted time heading in other directions trying to determine why the test SQL statement seemed to work in some cases and not in other cases.

I think that the reason why your SQL statement which had a plan hash value of 739349040 failed is simply because a hash join was performed on plan ID line 4 of the execution plan, rather than a nested loops join. That hash join is what joins the DBA_OBJECTS view to the DBMS_METADATA.GET_DDL function that is on plan ID line 30 of the execution plan – I think that a nested loops join here would have been able to provide the list of objects (drive into the second object) that were SYS owned views, while a hash join is supposed to fully scan the second object listed in the hash join.

In earlier testing, if I did not specify the /*+ NO_QUERY_TRANSFORMATION */ hint in my modified query, it would fail with the same error message that we encountered earlier.

select
  owner,
  view_name
from
  (SELECT
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    OWNER,
    VIEW_NAME
  FROM
    (select
      v.owner,
      v.view_name,
      o.object_type
    from
      dba_views v,
      dba_objects o
    where
      v.owner = o.owner
      AND v.view_name = o.object_name
      AND o.object_type='VIEW'
      AND o.status='VALID'
      AND v.owner ='SYS'  ) vv
  )
WHERE
  DDL like '%TEST%';
 
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    *
ERROR at line 6:
ORA-31603: object "/1000323d_DelegateInvocationHa" of type VIEW not found in
schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

This was an interesting exercise, and I agree with your comments.

3 01 2011

joel garry (17:35:24) :

This was very illuminating.

I surmise the bug is in Oracle instrumentation – if it is going to be doing overly helpful stuff transforming deep under the covers, it needs to be better about informing what problem it hit. I don’t see at all why using a supplied package as a filter would be bad programming practice. I’d think it would be good programming practice.

But then again, that makes iffy assumptions about how well engineered and documented supplied packages are.

	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Favorite Free Applications to…
	Zach on Favorite Free Applications to…
	Charles Hooper on Huge Presentation – Work…
	Dan on Huge Presentation – Work…
	Charles Hooper on Review of Synology DS415+, How…
	Matthias Clausen on Review of Synology DS415+, How…
	Charles Hooper on _SMALL_TABLE_THRESHOLD Paramet…
	mohd atif on _SMALL_TABLE_THRESHOLD Paramet…
	Charles Hooper on Free ANSI SQL to Oracle Specif…
	Kalin Agrawal on Free ANSI SQL to Oracle Specif…
	Charles Hooper on Large Page Support or Not…
	vaurob on Large Page Support or Not…
	Locating Oracle… on Finding a New Home for a Clien…

Charles Hooper's Oracle Notes