Defy Logic – the Cost-Based Optimizer does Not Select the Lowest Cost Plan – Implicit Data Type Conversion

28 05 2010

May 28, 2010

How many times have you heard someone say:

If you need to store numbers in the database, store the numbers in a column with a numeric datatype – storing the numbers in a column with a VARCHAR2 datatype invites problems.

If you need to store dates in the database, store the dates in a column with a DATE datatype – storing the dates in a column with a VARCHAR2 datatype invites problems; storing the dates in a column with a numeric datatype invites problems.

Yes, it happens in production envionments

Why is my SQL statement executing so slowly.

Why am I receiving unexpected results from this query?

The same holds true when selecting data from tables. 

  • If a number happens to exist in a column with a VARCHAR2 datatype, and it is necessary to retrieve that row by specifying that column in the WHERE clause, make certain that either the bind variable is defined as a VARCHAR/VARCHAR2 or that the constant (literal) value is wrapped in single quotes so that it is treated as a VARCHAR2, rather than as a number.  Yes, this happens in real life, as demontrated in this very recent OTN thread.
  • If the column is a VARCHAR2 datatype, do not define the bind variable with a NVARCHAR2 datatype.  Yes, this happens in real life, as demonstrated in this recent OTN thread and this slightly older OTN thread.
  • If the column is a DATE datatype, do not define the bind variable with a VARCHAR2 datatype and do not pass in a constant as a VARCHAR2 (string).  Something is bound to go wrong at some point.  I am still trying to determine why this technique was demonstrated multiple times in the “Oracle SQL Recipes” book (I have not had a chance to finish reading this book, so I have not posted a review yet that draws attention to this bad practice).

In the comments section of the most recent True or False Quiz article I showed a couple of demonstrations why numbers should not be stored in VARCHAR2 columns, if only numbers are to be stored in that column.  One of my comments showed why, when the column datatype does not match the datatype of the constant, and one of those is a numeric, why the other entity is converted to a numeric, rather than converting the numeric to a VARCHAR2.  From that comment entry, consider the following:

CREATE TABLE T3(
  CHAR_COL VARCHAR2(10),
  C2 VARCHAR2(100),
  PRIMARY KEY (CHAR_COL));

INSERT INTO T3 VALUES('1','A');
INSERT INTO T3 VALUES('1.0','A');
INSERT INTO T3 VALUES('1.00','A');
INSERT INTO T3 VALUES('1.000','A');
INSERT INTO T3 VALUES('1.0000','A');
INSERT INTO T3 VALUES('1.00000','A');
INSERT INTO T3 VALUES('1.000000','A');
INSERT INTO T3 VALUES('1.0000000','A');
INSERT INTO T3 VALUES('1.00000000','A');

COMMIT;

Now consider the following three queries – would the developer expect the three queries to return the same result rows?

SELECT
  *
FROM
  T3
WHERE
  CHAR_COL=1;

CHAR_COL   C2
---------- -----
1          A
1.0        A
1.00       A
1.000      A
1.0000     A
1.00000    A
1.000000   A
1.0000000  A
1.00000000 A

9 rows selected.

---

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

CHAR_COL   C2
---------- -----
1          A
1.0        A
1.00       A
1.000      A
1.0000     A
1.00000    A
1.000000   A
1.0000000  A
1.00000000 A

9 rows selected.

---

SELECT
  *
FROM
  T3
WHERE
  CHAR_COL=TO_CHAR(1);

CHAR_COL   C2
---------- -----
1          A

Notice that the last query only returned one row, while the other two queries returned nine rows.  So, why are VARCHAR2 columns always converted to numeric values, rather than numeric values converted to VARCHAR2?  If the number was automatically converted by Oracle into a character value, Oracle might need to test a nearly infinite number of 0 characters appended to the end of the converted value after the decimal point (up to the number of characters of precision) for a matching result – this extra work is avoided by converting the character value to a number.

I might have appeared to have drifted off the topic of this blog article, so now let’s see a case where Oracle’s cost-based optimizer does the impossible – it does not pick the execution plan with the lowest calculated cost for a simple SQL statement involving a single table.  This test case can be reproduced on Oracle Database 10.2.0.4 through 11.2.0.1 (and probably a couple of other releases as well).  This test case is from another one of my comments in the recent True or False Quiz article – the bonus question.  The test case:

CREATE TABLE T1 (
  CHAR_COL VARCHAR2(10),
  C2 VARCHAR2(100),
  PRIMARY KEY (CHAR_COL));

INSERT INTO
  T1
SELECT
  TO_CHAR(ROWNUM),
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL <=1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

SET AUTOTRACE TRACEONLY EXPLAIN

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

SELECT /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = 10;

SELECT /*+ INDEX(T1) */  /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = 10;

SELECT /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = '10';

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

The test case creates a simple table with 1,000,000 rows and then executes three SELECT statements.  Page 477 of Tom Kyte’s “Expert Oracle Database Architecture” book states the following:

Case 4: We have indexed a character column. This column contains only numeric data. We query using the following syntax:

select * from t where indexed_column = 5

Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used…”

Jonathan Lewis stated here (a blog article from 2006) that:

“It’s a great shame that Oracle Corp. decided to use the name “hints” for its optimizer directive mechanism.  “Hints” are not hints, they are interception points in the optimizer code path, and must be obeyed.”

So, which expert’s explanation is correct for Oracle Database 10.2.0.4 through 11.2.0.1 for this particular test case?  Neither?  Both?  Let’s take a look at the output that was written to the SQL*Plus screen:

SQL> SELECT /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |  4450   (2)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |  4450   (2)| 00:00:54 |
--------------------------------------------------------------------------

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

The above shows that Tom Kyte’s book is correct – the primary key index on the column CHAR_COL is not used.  Continuing with the SQL*Plus output:

SQL> SELECT /*+ INDEX(T1) */  /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 458899268

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |  3961   (2)| 00:00:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |   108 |  3961   (2)| 00:00:48 |
|*  2 |   INDEX FULL SCAN           | SYS_C0010157 |     1 |       |  3960   (2)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER("CHAR_COL")=10)

The above SQL statement is the same as the first, just with an INDEX hint.  The index hint was obeyed.  Jonathan Lewis’ blog article is correct – index hints are directives, so Oracle’s optimizer selected an index access path using the only available index – the index on the primary key column.

But now we have a serious problem.  What is the problem?  For a SQL statement involving a single table a higher calculated cost execution plan (4,450) with a higher estimated time (54 seconds) was selected rather than using the obviously less expensive execution plan with a lower calculated cost (3,961) and with a lower estimated time (48 seconds).  Interesting…

The SQL*Plus output continues:

SQL> SELECT /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = '10';

Execution Plan
----------------------------------------------------------
Plan hash value: 1657849122

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |   108 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010157 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CHAR_COL"='10')

Notice that the calculated cost decreased significantly when we constructed the query correctly, and the available index access path was automatically selected.

So, why did the optimizer not select the lowest cost access path?  Fortunately, the test case created a 10053 trace file that helps explain what happened.  In the 10053 trace file, we see that the optimizer transformed the original SQL statement a bit:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."CHAR_COL" "CHAR_COL","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE TO_NUMBER("T1"."CHAR_COL")=10

Now, with the transformed version of the SQL statement, it appears that we need a function based index on the CHAR_COL column that converts the column value to a number so that an index access path is possible.  Further down in the 10053 trace we find the following:

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 466 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8 )

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 1000000  #Blks:  16217  AvgRowLen:  108.00
Index Stats::
  Index: SYS_C0010157  Col#: 1
    LVLS: 2  #LB: 3908  #DK: 1000000  LB/K: 1.00  DB/K: 1.00  CLUF: 201245.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  4450.40  Resp: 4450.40  Degree: 0
      Cost_io: 4394.00  Cost_cpu: 315488412
      Resp_io: 4394.00  Resp_cpu: 315488412
  Best:: AccessPath: TableScan
         Cost: 4450.40  Degree: 1  Resp: 4450.40  Card: 1.00  Bytes: 0

***************************************

In the above, we see that the optimizer immediately jumped to a full table scan access path and then immediately declared that a full table scan offered the lowest cost – the optimizer did not even consider an index access path.  Now, let’s compare the above with the SQL statement having a hinted access path:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("T1") */ "T1"."CHAR_COL" "CHAR_COL","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE TO_NUMBER("T1"."CHAR_COL")=10
...

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 466 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8 )

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 1000000  #Blks:  16217  AvgRowLen:  108.00
Index Stats::
  Index: SYS_C0010157  Col#: 1
    LVLS: 2  #LB: 3908  #DK: 1000000  LB/K: 1.00  DB/K: 1.00  CLUF: 201245.00
    User hint to use this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
kkofmx: index filter:TO_NUMBER("T1"."CHAR_COL")=10

  Access Path: index (FullScan)
    Index: SYS_C0010157
    resc_io: 3911.00  resc_cpu: 227852122
    ix_sel: 1.000000  ix_sel_with_filters: 0.000001
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 3960.67  Resp: 3960.67  Degree: 1
  Best:: AccessPath: IndexRange
  Index: SYS_C0010157
         Cost: 3960.67  Degree: 1  Resp: 3960.67  Card: 1.00  Bytes: 0

The hint provided in the SQL statement forced the optimizer to do something that is supposedly not possible.  Notice that unlike the previous output, the full table scan was not even considered because of the hint.

The short summary of the above: do things correct from the start to avoid confusion and unexplained performance problems later.

The 10053 trace files (PDF versions of the 10053 trace files):
Oracle Database 11.2.0.1 OR112_LOWEST_COST
Oracle Database 11.1.0.7 OR1117_LOWEST_COST
Oracle Database 10.2.0.4 OR1024_LOWEST_COST

——–

* Late edit May 28, 2010: In the most recent True or False quiz, Centinul provided a documentation reference that states that VARCHAR2 values are always converted to numbers when a number is implicitly compared to a VARCHAR2 value.


Actions

Information

3 responses

28 05 2010
Sokrates

“The short summary of the above: do things correct from the start to avoid confusion and unexplained performance problems later.”

why “unexplained” ?
You explained this very clearly.

Thank you for this great article !

28 05 2010
Charles Hooper

Sokrates,

Thank you for the compliment. Here is an example of unexplained problems, one of the reasons why I took issue with the advice found in the “Oracle SQL Recipes” book.

I noticed that some of our programs were taking a long time to log in for no apparent reason – the login code was well constructed, other than not using bind variables. But the lack of bind variables was not the problem. The database connection used an Oracle 10.2 ODBC client, with a connection string that looked something like this:

"Data Source=ODBCName;User ID=MyUserName;Password=MyPassword;"

I noticed that an OLEDB connection was much faster for the connection. The OLEDB connection string looked something like this:

"Provider=OraOLEDB.Oracle;Data Source=DBName;User ID=MyUserName;Password=MyPassword;ChunkSize=1000;"

Why the difference in performance? If I recall correctly, changing from ODBC to OLEDB decreased the connection time from about 3-5 seconds to less than a second. A 10046 trace file captured during the ODBC connection showed repeated executions of SQL statements like this:

PARSING IN CURSOR #2 len=370 dep=0 uid=30 oct=3 lid=30 tim=4364034814 hv=2744743845 ad='78b40a58'
SELECT /*+ RULE */ '', b.owner, b.table_name, b.column_name, b.position, b.constraint_name FROM ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b WHERE (b.table_name='NCMR_GROUP_RIGHTS' and b.owner='TESTUSER') AND (a.table_name='NCMR_GROUP_RIGHTS' and a.owner='TESTUSER' and a.constraint_type='P') AND (a.constraint_name = b.constraint_name) ORDER BY b.owner, b.table_name, b.position
END OF STMT
PARSE #2:c=15625,e=21830,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=3,tim=4364034807
EXEC #2:c=0,e=221,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4364035479
WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364035593
FETCH #2:c=31250,e=25959,p=0,cr=4273,cu=0,mis=0,r=1,dep=0,og=3,tim=4364061649
XCTEND rlbk=0, rd_only=1
WAIT #2: nam='SQL*Net message from client' ela= 618 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364062789
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364062992
FETCH #2:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=3,tim=4364063099
WAIT #2: nam='SQL*Net message from client' ela= 20616 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364083954
STAT #2 id=1 cnt=3 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=4273 pr=0 pw=0 time=25972 us)'
STAT #2 id=2 cnt=3 pid=1 pos=1 obj=0 op='FILTER  (cr=4273 pr=0 pw=0 time=16077 us)'
STAT #2 id=3 cnt=3 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=4267 pr=0 pw=0 time=15934 us)'
STAT #2 id=4 cnt=3 pid=3 pos=1 obj=0 op='NESTED LOOPS  (cr=4261 pr=0 pw=0 time=15902 us)'
STAT #2 id=5 cnt=3 pid=4 pos=1 obj=0 op='NESTED LOOPS  (cr=4253 pr=0 pw=0 time=15852 us)'
STAT #2 id=6 cnt=3 pid=5 pos=1 obj=0 op='NESTED LOOPS  (cr=4245 pr=0 pw=0 time=15806 us)'
STAT #2 id=7 cnt=1 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=4242 pr=0 pw=0 time=25624 us)'
STAT #2 id=8 cnt=1 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=4242 pr=0 pw=0 time=25614 us)'
STAT #2 id=9 cnt=1 pid=8 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=4241 pr=0 pw=0 time=25599 us)'
STAT #2 id=10 cnt=1 pid=9 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=4241 pr=0 pw=0 time=25589 us)'
STAT #2 id=11 cnt=1 pid=10 pos=1 obj=0 op='NESTED LOOPS  (cr=4241 pr=0 pw=0 time=25580 us)'
STAT #2 id=12 cnt=246 pid=11 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=3747 pr=0 pw=0 time=21978 us)'
STAT #2 id=13 cnt=246 pid=12 pos=1 obj=0 op='NESTED LOOPS  (cr=3499 pr=0 pw=0 time=20004 us)'
STAT #2 id=14 cnt=578 pid=13 pos=1 obj=0 op='NESTED LOOPS  (cr=2341 pr=0 pw=0 time=16293 us)'
STAT #2 id=15 cnt=578 pid=14 pos=1 obj=0 op='NESTED LOOPS  (cr=1183 pr=0 pw=0 time=9933 us)'
STAT #2 id=16 cnt=578 pid=15 pos=1 obj=0 op='NESTED LOOPS  (cr=25 pr=0 pw=0 time=2408 us)'
STAT #2 id=17 cnt=1 pid=16 pos=1 obj=0 op='NESTED LOOPS  (cr=4 pr=0 pw=0 time=65 us)'
STAT #2 id=18 cnt=1 pid=17 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=43 us)'
STAT #2 id=19 cnt=1 pid=18 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=26 us)'
STAT #2 id=20 cnt=1 pid=17 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=12 us)'
STAT #2 id=21 cnt=1 pid=20 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=7 us)'
STAT #2 id=22 cnt=578 pid=16 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=21 pr=0 pw=0 time=2348 us)'
STAT #2 id=23 cnt=578 pid=22 pos=1 obj=48 op='INDEX RANGE SCAN I_CON1 (cr=6 pr=0 pw=0 time=603 us)'
STAT #2 id=24 cnt=578 pid=15 pos=2 obj=31 op='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1158 pr=0 pw=0 time=5865 us)'
STAT #2 id=25 cnt=578 pid=24 pos=1 obj=50 op='INDEX UNIQUE SCAN I_CDEF1 (cr=580 pr=0 pw=0 time=2770 us)'
STAT #2 id=26 cnt=578 pid=14 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=1158 pr=0 pw=0 time=5598 us)'
STAT #2 id=27 cnt=578 pid=26 pos=1 obj=48 op='INDEX UNIQUE SCAN I_CON1 (cr=580 pr=0 pw=0 time=2742 us)'
STAT #2 id=28 cnt=246 pid=13 pos=2 obj=31 op='TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1158 pr=0 pw=0 time=6475 us)'
STAT #2 id=29 cnt=578 pid=28 pos=1 obj=50 op='INDEX UNIQUE SCAN I_CDEF1 (cr=580 pr=0 pw=0 time=2383 us)'
STAT #2 id=30 cnt=246 pid=12 pos=2 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=248 pr=0 pw=0 time=1280 us)'
STAT #2 id=31 cnt=1 pid=11 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=494 pr=0 pw=0 time=2397 us)'
STAT #2 id=32 cnt=246 pid=31 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=248 pr=0 pw=0 time=1150 us)'
STAT #2 id=33 cnt=0 pid=10 pos=2 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=3 us)'
STAT #2 id=34 cnt=0 pid=9 pos=2 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=0 pr=0 pw=0 time=6 us)'
STAT #2 id=35 cnt=0 pid=34 pos=1 obj=49 op='INDEX UNIQUE SCAN I_CON2 (cr=0 pr=0 pw=0 time=3 us)'
STAT #2 id=36 cnt=0 pid=8 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=1 pr=0 pw=0 time=9 us)'
STAT #2 id=37 cnt=0 pid=36 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=6 us)'
STAT #2 id=38 cnt=0 pid=7 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=5 us)'
STAT #2 id=39 cnt=0 pid=38 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=2 us)'
STAT #2 id=40 cnt=3 pid=6 pos=2 obj=32 op='TABLE ACCESS BY INDEX ROWID CCOL$ (cr=3 pr=0 pw=0 time=27 us)'
STAT #2 id=41 cnt=3 pid=40 pos=1 obj=55 op='INDEX RANGE SCAN I_CCOL2 (cr=2 pr=0 pw=0 time=16 us)'
STAT #2 id=42 cnt=3 pid=5 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=8 pr=0 pw=0 time=35 us)'
STAT #2 id=43 cnt=3 pid=42 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=5 pr=0 pw=0 time=18 us)'
STAT #2 id=44 cnt=3 pid=4 pos=2 obj=21 op='TABLE ACCESS BY INDEX ROWID COL$ (cr=8 pr=0 pw=0 time=40 us)'
STAT #2 id=45 cnt=3 pid=44 pos=1 obj=47 op='INDEX UNIQUE SCAN I_COL3 (cr=5 pr=0 pw=0 time=21 us)'
STAT #2 id=46 cnt=0 pid=3 pos=2 obj=173 op='TABLE ACCESS CLUSTER ATTRCOL$ (cr=6 pr=0 pw=0 time=22 us)'
STAT #2 id=47 cnt=1 pid=2 pos=2 obj=0 op='NESTED LOOPS  (cr=3 pr=0 pw=0 time=45 us)'
STAT #2 id=48 cnt=1 pid=47 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=24 us)'
STAT #2 id=49 cnt=1 pid=47 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=3 pr=0 pw=0 time=18 us)'
STAT #2 id=50 cnt=0 pid=2 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #2 id=51 cnt=1 pid=2 pos=4 obj=0 op='NESTED LOOPS  (cr=3 pr=0 pw=0 time=22 us)'
STAT #2 id=52 cnt=1 pid=51 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=8 us)'
STAT #2 id=53 cnt=1 pid=51 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=3 pr=0 pw=0 time=10 us)'
STAT #2 id=54 cnt=0 pid=2 pos=5 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
...
PARSING IN CURSOR #4 len=1063 dep=0 uid=30 oct=3 lid=30 tim=4364197510 hv=1281792434 ad='657a7c58'
(SELECT /*+ RULE */ '', T.owner, T.table_name, TO_NUMBER(NULL), '', '', 0, TO_NUMBER(NULL), '', '', num_rows, blocks, NULL FROM ALL_TABLES T WHERE table_name='NCMR_GROUP_RIGHTS' UNION SELECT /*+ RULE */ '', a.table_owner, a.table_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.owner = b.index_owner AND a.index_name = b.index_name AND a.table_owner='TESTUSER' AND a.table_name='NCMR_GROUP_RIGHTS' UNION SELECT /*+ RULE */ '', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c WHERE c.synonym_name='NCMR_GROUP_RIGHTS' AND c.owner='TESTUSER' AND c.table_name = a.table_name AND c.table_name = b.table_name AND c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner AND a.index_name = b.index_name ) ORDER BY 4, 5, 6, 8
END OF STMT
PARSE #4:c=109375,e=111764,p=0,cr=22,cu=0,mis=1,r=0,dep=0,og=3,tim=4364197503
EXEC #4:c=0,e=556,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=4364198154
WAIT #4: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364198220
FETCH #4:c=265625,e=263668,p=0,cr=37504,cu=0,mis=0,r=1,dep=0,og=3,tim=4364461943
XCTEND rlbk=0, rd_only=1
WAIT #4: nam='SQL*Net message from client' ela= 752 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364463650
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364463745
FETCH #4:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=3,tim=4364463790
WAIT #4: nam='SQL*Net message from client' ela= 1110 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4364465166
STAT #4 id=1 cnt=4 pid=0 pos=1 obj=0 op='SORT UNIQUE (cr=37504 pr=0 pw=0 time=263686 us)'
STAT #4 id=2 cnt=4 pid=1 pos=1 obj=0 op='UNION-ALL  (cr=37504 pr=0 pw=0 time=11505 us)'
STAT #4 id=3 cnt=1 pid=2 pos=1 obj=0 op='FILTER  (cr=214 pr=0 pw=0 time=7736 us)'
STAT #4 id=4 cnt=1 pid=3 pos=1 obj=0 op='NESTED LOOPS  (cr=211 pr=0 pw=0 time=7627 us)'
STAT #4 id=5 cnt=1 pid=4 pos=1 obj=0 op='NESTED LOOPS  (cr=208 pr=0 pw=0 time=7604 us)'
STAT #4 id=6 cnt=1 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=205 pr=0 pw=0 time=7584 us)'
STAT #4 id=7 cnt=1 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=201 pr=0 pw=0 time=7552 us)'
STAT #4 id=8 cnt=1 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=198 pr=0 pw=0 time=7527 us)'
STAT #4 id=9 cnt=1 pid=8 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=198 pr=0 pw=0 time=7516 us)'
STAT #4 id=10 cnt=1 pid=9 pos=1 obj=0 op='NESTED LOOPS  (cr=195 pr=0 pw=0 time=7488 us)'
STAT #4 id=11 cnt=1 pid=10 pos=1 obj=0 op='NESTED LOOPS  (cr=191 pr=0 pw=0 time=7421 us)'
STAT #4 id=12 cnt=1 pid=11 pos=1 obj=0 op='MERGE JOIN  (cr=0 pr=0 pw=0 time=1900 us)'
STAT #4 id=13 cnt=1 pid=12 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=696 us)'
STAT #4 id=14 cnt=1 pid=12 pos=2 obj=0 op='SORT JOIN (cr=0 pr=0 pw=0 time=1195 us)'
STAT #4 id=15 cnt=1410 pid=14 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=1419 us)'
STAT #4 id=16 cnt=1 pid=11 pos=2 obj=18 op='TABLE ACCESS FULL OBJ$ (cr=191 pr=0 pw=0 time=5516 us)'
STAT #4 id=17 cnt=1 pid=10 pos=2 obj=4 op='TABLE ACCESS CLUSTER TAB$ (cr=4 pr=0 pw=0 time=58 us)'
STAT #4 id=18 cnt=1 pid=17 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=26 us)'
STAT #4 id=19 cnt=1 pid=9 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=21 us)'
STAT #4 id=20 cnt=1 pid=19 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=11 us)'
STAT #4 id=21 cnt=0 pid=8 pos=2 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=2 us)'
STAT #4 id=22 cnt=1 pid=7 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=17 us)'
STAT #4 id=23 cnt=1 pid=22 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=8 us)'
STAT #4 id=24 cnt=1 pid=6 pos=2 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=4 pr=0 pw=0 time=21 us)'
STAT #4 id=25 cnt=1 pid=24 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=12 us)'
STAT #4 id=26 cnt=1 pid=5 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=3 pr=0 pw=0 time=16 us)'
STAT #4 id=27 cnt=1 pid=26 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=7 us)'
STAT #4 id=28 cnt=1 pid=4 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=15 us)'
STAT #4 id=29 cnt=1 pid=28 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=4 us)'
STAT #4 id=30 cnt=1 pid=3 pos=2 obj=0 op='NESTED LOOPS  (cr=3 pr=0 pw=0 time=52 us)'
STAT #4 id=31 cnt=1 pid=30 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=23 us)'
STAT #4 id=32 cnt=1 pid=30 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=3 pr=0 pw=0 time=23 us)'
STAT #4 id=33 cnt=0 pid=3 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=34 cnt=3 pid=2 pos=2 obj=0 op='FILTER  (cr=72 pr=0 pw=0 time=564 us)'
STAT #4 id=35 cnt=3 pid=34 pos=1 obj=0 op='NESTED LOOPS  (cr=66 pr=0 pw=0 time=450 us)'
STAT #4 id=36 cnt=3 pid=35 pos=1 obj=0 op='NESTED LOOPS  (cr=58 pr=0 pw=0 time=396 us)'
STAT #4 id=37 cnt=3 pid=36 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=50 pr=0 pw=0 time=349 us)'
STAT #4 id=38 cnt=3 pid=37 pos=1 obj=0 op='NESTED LOOPS  (cr=44 pr=0 pw=0 time=323 us)'
STAT #4 id=39 cnt=3 pid=38 pos=1 obj=0 op='NESTED LOOPS  (cr=35 pr=0 pw=0 time=268 us)'
STAT #4 id=40 cnt=1 pid=39 pos=1 obj=0 op='NESTED LOOPS  (cr=32 pr=0 pw=0 time=283 us)'
STAT #4 id=41 cnt=1 pid=40 pos=1 obj=0 op='NESTED LOOPS  (cr=29 pr=0 pw=0 time=260 us)'
STAT #4 id=42 cnt=1 pid=41 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=26 pr=0 pw=0 time=234 us)'
STAT #4 id=43 cnt=1 pid=42 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=23 pr=0 pw=0 time=212 us)'
STAT #4 id=44 cnt=1 pid=43 pos=1 obj=0 op='NESTED LOOPS  (cr=19 pr=0 pw=0 time=183 us)'
STAT #4 id=45 cnt=1 pid=44 pos=1 obj=0 op='NESTED LOOPS  (cr=17 pr=0 pw=0 time=167 us)'
STAT #4 id=46 cnt=1 pid=45 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=14 pr=0 pw=0 time=145 us)'
STAT #4 id=47 cnt=1 pid=46 pos=1 obj=0 op='NESTED LOOPS  (cr=14 pr=0 pw=0 time=135 us)'
STAT #4 id=48 cnt=1 pid=47 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=113 us)'
STAT #4 id=49 cnt=1 pid=48 pos=1 obj=0 op='NESTED LOOPS  (cr=9 pr=0 pw=0 time=96 us)'
STAT #4 id=50 cnt=1 pid=49 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=0 pw=0 time=60 us)'
STAT #4 id=51 cnt=1 pid=50 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=25 us)'
STAT #4 id=52 cnt=1 pid=51 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=15 us)'
STAT #4 id=53 cnt=1 pid=50 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=29 us)'
STAT #4 id=54 cnt=1 pid=53 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=19 us)'
STAT #4 id=55 cnt=1 pid=49 pos=2 obj=19 op='TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=32 us)'
STAT #4 id=56 cnt=1 pid=55 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=9 us)'
STAT #4 id=57 cnt=0 pid=48 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=12 us)'
STAT #4 id=58 cnt=0 pid=57 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=9 us)'
STAT #4 id=59 cnt=1 pid=47 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=17 us)'
STAT #4 id=60 cnt=1 pid=59 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=7 us)'
STAT #4 id=61 cnt=0 pid=46 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=6 us)'
STAT #4 id=62 cnt=0 pid=61 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=2 us)'
STAT #4 id=63 cnt=1 pid=45 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=17 us)'
STAT #4 id=64 cnt=1 pid=63 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=6 us)'
STAT #4 id=65 cnt=1 pid=44 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=10 us)'
STAT #4 id=66 cnt=1 pid=65 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=6 us)'
STAT #4 id=67 cnt=1 pid=43 pos=2 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=4 pr=0 pw=0 time=18 us)'
STAT #4 id=68 cnt=1 pid=67 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=9 us)'
STAT #4 id=69 cnt=1 pid=42 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=3 pr=0 pw=0 time=12 us)'
STAT #4 id=70 cnt=1 pid=69 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=1 pr=0 pw=0 time=5 us)'
STAT #4 id=71 cnt=1 pid=41 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=22 us)'
STAT #4 id=72 cnt=1 pid=71 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=14 us)'
STAT #4 id=73 cnt=1 pid=40 pos=2 obj=19 op='TABLE ACCESS BY INDEX ROWID IND$ (cr=3 pr=0 pw=0 time=19 us)'
STAT #4 id=74 cnt=1 pid=73 pos=1 obj=39 op='INDEX UNIQUE SCAN I_IND1 (cr=2 pr=0 pw=0 time=10 us)'
STAT #4 id=75 cnt=3 pid=39 pos=2 obj=20 op='TABLE ACCESS BY INDEX ROWID ICOL$ (cr=3 pr=0 pw=0 time=25 us)'
STAT #4 id=76 cnt=3 pid=75 pos=1 obj=40 op='INDEX RANGE SCAN I_ICOL1 (cr=2 pr=0 pw=0 time=17 us)'
STAT #4 id=77 cnt=3 pid=38 pos=2 obj=21 op='TABLE ACCESS CLUSTER COL$ (cr=9 pr=0 pw=0 time=51 us)'
STAT #4 id=78 cnt=0 pid=37 pos=2 obj=173 op='TABLE ACCESS CLUSTER ATTRCOL$ (cr=6 pr=0 pw=0 time=15 us)'
STAT #4 id=79 cnt=3 pid=36 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=8 pr=0 pw=0 time=35 us)'
STAT #4 id=80 cnt=3 pid=79 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=5 pr=0 pw=0 time=21 us)'
STAT #4 id=81 cnt=3 pid=35 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=8 pr=0 pw=0 time=46 us)'
STAT #4 id=82 cnt=3 pid=81 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=2 pr=0 pw=0 time=14 us)'
STAT #4 id=83 cnt=1 pid=34 pos=2 obj=0 op='NESTED LOOPS  (cr=3 pr=0 pw=0 time=36 us)'
STAT #4 id=84 cnt=1 pid=83 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=17 us)'
STAT #4 id=85 cnt=1 pid=83 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=3 pr=0 pw=0 time=14 us)'
STAT #4 id=86 cnt=0 pid=34 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=87 cnt=1 pid=34 pos=4 obj=0 op='NESTED LOOPS  (cr=3 pr=0 pw=0 time=21 us)'
STAT #4 id=88 cnt=1 pid=87 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=6 us)'
STAT #4 id=89 cnt=1 pid=87 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=3 pr=0 pw=0 time=9 us)'
STAT #4 id=90 cnt=0 pid=34 pos=5 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=91 cnt=0 pid=2 pos=3 obj=0 op='FILTER  (cr=37218 pr=0 pw=0 time=255205 us)'
STAT #4 id=92 cnt=0 pid=91 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=37218 pr=0 pw=0 time=255202 us)'
STAT #4 id=93 cnt=0 pid=92 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=37218 pr=0 pw=0 time=255200 us)'
STAT #4 id=94 cnt=0 pid=93 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=37218 pr=0 pw=0 time=255196 us)'
STAT #4 id=95 cnt=0 pid=94 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255192 us)'
STAT #4 id=96 cnt=0 pid=95 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=37218 pr=0 pw=0 time=255189 us)'
STAT #4 id=97 cnt=0 pid=96 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255186 us)'
STAT #4 id=98 cnt=0 pid=97 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255181 us)'
STAT #4 id=99 cnt=0 pid=98 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255178 us)'
STAT #4 id=100 cnt=0 pid=99 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255175 us)'
STAT #4 id=101 cnt=0 pid=100 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=37218 pr=0 pw=0 time=255172 us)'
STAT #4 id=102 cnt=0 pid=101 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255169 us)'
STAT #4 id=103 cnt=0 pid=102 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255165 us)'
STAT #4 id=104 cnt=0 pid=103 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255162 us)'
STAT #4 id=105 cnt=0 pid=104 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255158 us)'
STAT #4 id=106 cnt=0 pid=105 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255154 us)'
STAT #4 id=107 cnt=0 pid=106 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255151 us)'
STAT #4 id=108 cnt=0 pid=107 pos=1 obj=0 op='NESTED LOOPS  (cr=37218 pr=0 pw=0 time=255148 us)'
STAT #4 id=109 cnt=0 pid=108 pos=1 obj=2434 op='VIEW  ALL_SYNONYMS (cr=37218 pr=0 pw=0 time=255144 us)'
STAT #4 id=110 cnt=0 pid=109 pos=1 obj=0 op='SORT UNIQUE (cr=37218 pr=0 pw=0 time=255141 us)'
STAT #4 id=111 cnt=0 pid=110 pos=1 obj=0 op='UNION-ALL  (cr=37218 pr=0 pw=0 time=255119 us)'
STAT #4 id=112 cnt=0 pid=111 pos=1 obj=0 op='FILTER  (cr=5 pr=0 pw=0 time=48 us)'
STAT #4 id=113 cnt=0 pid=112 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=0 pw=0 time=45 us)'
STAT #4 id=114 cnt=0 pid=113 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=0 pw=0 time=42 us)'
STAT #4 id=115 cnt=1 pid=114 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=20 us)'
STAT #4 id=116 cnt=1 pid=115 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us)'
STAT #4 id=117 cnt=0 pid=114 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=21 us)'
STAT #4 id=118 cnt=1 pid=117 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=12 us)'
STAT #4 id=119 cnt=0 pid=113 pos=2 obj=62 op='TABLE ACCESS BY INDEX ROWID SYN$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=120 cnt=0 pid=119 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SYN1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=121 cnt=0 pid=112 pos=2 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=122 cnt=0 pid=121 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=123 cnt=0 pid=122 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=124 cnt=0 pid=123 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=125 cnt=0 pid=124 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=126 cnt=0 pid=125 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=127 cnt=0 pid=124 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=128 cnt=0 pid=127 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=129 cnt=0 pid=123 pos=2 obj=103 op='INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=130 cnt=0 pid=121 pos=2 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=131 cnt=0 pid=112 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=132 cnt=0 pid=111 pos=2 obj=0 op='NESTED LOOPS  (cr=37213 pr=0 pw=0 time=255057 us)'
STAT #4 id=133 cnt=0 pid=132 pos=1 obj=0 op='NESTED LOOPS  (cr=37213 pr=0 pw=0 time=255053 us)'
STAT #4 id=134 cnt=0 pid=133 pos=1 obj=0 op='NESTED LOOPS  (cr=37213 pr=0 pw=0 time=255050 us)'
STAT #4 id=135 cnt=1 pid=134 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=13 us)'
STAT #4 id=136 cnt=1 pid=135 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us)'
STAT #4 id=137 cnt=0 pid=134 pos=2 obj=2433 op='VIEW  _ALL_SYNONYMS_TREE (cr=37211 pr=0 pw=0 time=255031 us)'
STAT #4 id=138 cnt=0 pid=137 pos=1 obj=0 op='CONNECT BY WITHOUT FILTERING (cr=37211 pr=0 pw=0 time=255027 us)'
STAT #4 id=139 cnt=0 pid=138 pos=1 obj=0 op='FILTER  (cr=37211 pr=0 pw=0 time=254988 us)'
STAT #4 id=140 cnt=0 pid=139 pos=1 obj=0 op='COUNT  (cr=37211 pr=0 pw=0 time=254985 us)'
STAT #4 id=141 cnt=0 pid=140 pos=1 obj=0 op='NESTED LOOPS  (cr=37211 pr=0 pw=0 time=254974 us)'
STAT #4 id=142 cnt=3468 pid=141 pos=1 obj=0 op='NESTED LOOPS  (cr=30189 pr=0 pw=0 time=98989 us)'
STAT #4 id=143 cnt=3468 pid=142 pos=1 obj=0 op='NESTED LOOPS  (cr=26719 pr=0 pw=0 time=64303 us)'
STAT #4 id=144 cnt=16086 pid=143 pos=1 obj=0 op='NESTED LOOPS  (cr=7163 pr=0 pw=0 time=64408 us)'
STAT #4 id=145 cnt=137 pid=144 pos=1 obj=22 op='TABLE ACCESS FULL USER$ (cr=15 pr=0 pw=0 time=726 us)'
STAT #4 id=146 cnt=16086 pid=144 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=7148 pr=0 pw=0 time=49387 us)'
STAT #4 id=147 cnt=16086 pid=146 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=255 pr=0 pw=0 time=16900 us)'
STAT #4 id=148 cnt=3468 pid=143 pos=2 obj=62 op='TABLE ACCESS BY INDEX ROWID SYN$ (cr=19556 pr=0 pw=0 time=109696 us)'
STAT #4 id=149 cnt=3468 pid=148 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SYN1 (cr=16088 pr=0 pw=0 time=60600 us)'
STAT #4 id=150 cnt=3468 pid=142 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=3470 pr=0 pw=0 time=28463 us)'
STAT #4 id=151 cnt=3468 pid=150 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=2 pr=0 pw=0 time=10910 us)'
STAT #4 id=152 cnt=0 pid=141 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=7022 pr=0 pw=0 time=49197 us)'
STAT #4 id=153 cnt=3715 pid=152 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3493 pr=0 pw=0 time=28527 us)'
STAT #4 id=154 cnt=0 pid=139 pos=2 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=155 cnt=0 pid=154 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=156 cnt=0 pid=155 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=157 cnt=0 pid=156 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=158 cnt=0 pid=157 pos=1 obj=62 op='TABLE ACCESS BY INDEX ROWID SYN$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=159 cnt=0 pid=158 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SYN1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=160 cnt=0 pid=157 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=161 cnt=0 pid=160 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=162 cnt=0 pid=156 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=163 cnt=0 pid=162 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=164 cnt=0 pid=155 pos=2 obj=103 op='INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=165 cnt=0 pid=154 pos=2 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=166 cnt=0 pid=138 pos=2 obj=0 op='COUNT  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=167 cnt=0 pid=166 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=168 cnt=0 pid=167 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=169 cnt=0 pid=168 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=170 cnt=0 pid=169 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=171 cnt=0 pid=170 pos=1 obj=22 op='TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=172 cnt=0 pid=170 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=173 cnt=0 pid=172 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=174 cnt=0 pid=169 pos=2 obj=62 op='TABLE ACCESS BY INDEX ROWID SYN$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=175 cnt=0 pid=174 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SYN1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=176 cnt=0 pid=168 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=177 cnt=0 pid=176 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=178 cnt=0 pid=167 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=179 cnt=0 pid=178 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=180 cnt=0 pid=133 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=181 cnt=0 pid=180 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=182 cnt=0 pid=132 pos=2 obj=62 op='TABLE ACCESS BY INDEX ROWID SYN$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=183 cnt=0 pid=182 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SYN1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=184 cnt=0 pid=108 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=185 cnt=0 pid=184 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=186 cnt=0 pid=107 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=187 cnt=0 pid=186 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=188 cnt=0 pid=106 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=189 cnt=0 pid=188 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=190 cnt=0 pid=105 pos=2 obj=20 op='TABLE ACCESS CLUSTER ICOL$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=191 cnt=0 pid=190 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=192 cnt=0 pid=104 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=193 cnt=0 pid=192 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=194 cnt=0 pid=103 pos=2 obj=19 op='TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=195 cnt=0 pid=194 pos=1 obj=39 op='INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=196 cnt=0 pid=102 pos=2 obj=21 op='TABLE ACCESS CLUSTER COL$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=197 cnt=0 pid=101 pos=2 obj=173 op='TABLE ACCESS CLUSTER ATTRCOL$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=198 cnt=0 pid=100 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=199 cnt=0 pid=198 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=200 cnt=0 pid=99 pos=2 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=201 cnt=0 pid=200 pos=1 obj=44 op='INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=202 cnt=0 pid=98 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=203 cnt=0 pid=202 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=204 cnt=0 pid=97 pos=2 obj=19 op='TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=205 cnt=0 pid=204 pos=1 obj=39 op='INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=206 cnt=0 pid=96 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=207 cnt=0 pid=206 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=208 cnt=0 pid=95 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=209 cnt=0 pid=208 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=210 cnt=0 pid=94 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=211 cnt=0 pid=210 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=212 cnt=0 pid=93 pos=2 obj=14 op='TABLE ACCESS CLUSTER SEG$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=213 cnt=0 pid=212 pos=1 obj=9 op='INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=214 cnt=0 pid=92 pos=2 obj=16 op='TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=215 cnt=0 pid=214 pos=1 obj=7 op='INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=216 cnt=0 pid=91 pos=2 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=217 cnt=0 pid=216 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=218 cnt=0 pid=216 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=219 cnt=0 pid=91 pos=3 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=220 cnt=0 pid=91 pos=4 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=221 cnt=0 pid=220 pos=1 obj=0 op='FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=222 cnt=0 pid=220 pos=2 obj=104 op='INDEX RANGE SCAN I_OBJAUTH2 (cr=0 pr=0 pw=0 time=0 us)'
STAT #4 id=223 cnt=0 pid=91 pos=5 obj=0 op='FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)'

Both of those SQL statements show dep=0, yet the application is not submitting those SQL statements – Oracle’s ODBC client for 10.2 is the culprit. No problem, the solution is to just switch to Oracle’s OraOLEDB provider.

But now we have a problem. The SQL statements that for years have worked passing in dates as string literals:

SELECT * FROM T1 WHERE C1='13-MAR-2010';

Are sometimes, maybe always, failing with either a compilation error, or returning the wrong results.

But, how can it fail – the book stated that it was OK, and it has worked for YEARS. Magically, doing it the right way is the fix:

SELECT * FROM T1 WHERE C1=TO_DATE('13-MAR-2010', "DD-MON-YYYY");

Of course, really doing it the right way is to declare a bind variable of type DATE – and that works just as well.

28 07 2010
TI 83 Calculators

I guess number should be in numeric because the SQL engine don’t need to cast it from some other type when you need it as a number.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: