Apr 29 2010
Almost one year ago Iggy Fernandez asked me to write a short text for the Ask the Oracles column of the NoCOUG Journal. The topic was “Bind Variable Peeking: Bane or Boon?”. My text along with the ones of Wolfgang Breitling, Dan Tow and Jonathan Lewis were published in the August issue. For some (unknown) reasons I never published that text on this site. Today, I correct that oversight. The text can be downloaded from this page.
Mar 08 2010
Last week I had to analyze a strange performance problem. Since the cause/solution was somehow surprising, at least for me, I thought to share it with you.
Let me start by quickly describing the setup and what was done to reproduce the problem:
The strange thing was that the rate of the inserts performed by the script decreased over time. In fact, while at the beginning of the processing about 500 rows per second were inserted into T1 (and, therefore, T2), at the end of the processing only about 50 rows per second were processed.
The first thing I did to find out what the problem was is to trace one run by enabling SQL trace. This analysis pointed out that two SQL statements (the ones inserting data into T1 and T2) were responsible for most of the elapsed time. This is not a surprise, of course. The interesting thing was that most of the time was spent on CPU.
Since the rate of the inserts decreased over time, I extracted from the trace file all the lines providing information about the executions of the INSERT statement on T1 and loaded that data into Excel. Then, I created one chart for each performance figure. From all of them the following, that shows the amount of CPU used for every single execution, was the most interesting. In fact, it shows that while at the beginning of the processing one insert uses about 30 milliseconds of CPU, at the end it uses about 300 milliseconds of CPU for doing the same work. Note that all other charts did not show such a behavior. For example, the number of PIO and LIO were exactly the same at the beginning and at the end of the processing.
Since the trace file was not able to provide further information to investigate the problem, I started looking at V$SESSTAT. The aim was to find another statistic experiencing a similar increase. The search pointed out that the statistic “session uga memory” was also increasing during the processing. In fact, while at the beginning of the processing the session was using about 5MB of UGA, at the end of the processing about 110MB were used. This is strange and, as far as I know, there is no good reason for such a behavior. Hence, it was time to review the code of the triggers. While doing so I noticed, by chance, that a trigger was also available on T2 (the table used to store the log about all modifications). The strange thing was its definition:
CREATE OR REPLACE TRIGGER t2 AFTER INSERT ON t2 FOR EACH ROW
BEGIN
/* execute the referential-integrity actions */
DECLARE
NUMROWS INTEGER;
BEGIN
numrows:=1;
END;
END;
As you can see the trigger does nothing. Apparently, it exists just because triggers are used to implement integrity constraints (something you should avoid, by the way…) and, as a result, they were automatically created for each table. And, in case of T2, there is no constraint to check.
Since the trigger is pointless, I disabled it. After that, surprisingly, it was no longer possible to reproduce the problem! The following chart, created in the same way as the previous one, shows that without the trigger on T2 the CPU utilization is constant during the whole processing.
Therefore, for some unknown reasons, the pointless trigger was the cause of the problem.
By the way, once the trigger was disabled also the UGA memory was no longer increasing. Hence, to me it seems that the customer hit a bug…
Feb 28 2010
Even though a number of articles and blog posts have already been written on this topic (e.g. on Pete Finnigan’s site I found references dating back from 2003), from time to time I’m still asked “How to trace predicates generated by VPD?”. Hence, here’s yet another blog post about this topic…
Let’s setup the scene before explaining how you can do it:
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> CREATE OR REPLACE FUNCTION emp_restrict (p_schema IN VARCHAR2, p_table IN VARCHAR2) RETURN VARCHAR2 AS
2 BEGIN
3 RETURN '''' || sys_context('userenv','session_user') || ''' = ename';
4 END emp_restrict;
5 /
SQL> execute dbms_rls.add_policy('CHA','EMP','EMP_POLICY','CHA','EMP_RESTRICT');
SQL> connect scott
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> connect clark
SQL> SELECT * FROM cha.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
If the function used for the VPD policy is simple and does generate a predicate that can be correctly parsed, to view the predicate it is enough to give a look to the output of the dbms_xplan package. The following SQL statements illustrate this:
SQL> SELECT * FROM table(dbms_xplan.display_cursor(sql_id=>'dmc3z4t0u57y1', format=>'basic predicate'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM cha.emp
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='SCOTT')
Another possibility, if you have access to the V$VPD_POLICY view, is to execute a query like the following one:
SQL> SELECT predicate FROM v$vpd_policy WHERE sql_id = 'dmc3z4t0u57y1'; PREDICATE ---------------------- 'SCOTT' = ename
However, in case you want to see the whole SQL statement or the predicate generated by the VPD policy leads to an ORA-28113 (policy predicate has error), there is no documented way I’m aware of to display the generated predicate. One of the undocumented ways to do it is to use the event 10730. Note that the event generates a trace file containing the information we are looking for in such situations. Here is an example:
SQL> ALTER SESSION SET events '10730 trace name context forever, level 1';
SQL> SELECT * FROM cha.emp;
SELECT * FROM cha.emp
*
ERROR at line 1:
ORA-28113: policy predicate has error
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user : SCOTT
Table/View : CHA.EMP
Policy name : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP" "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------
As you can see, the trace file contains not only the whole SQL statement but also the reason for the ORA-28113 error.
Jan 26 2010
The short answer to this question is “yes”, it does. Unfortunately, the distribution costs are not externalized through the execution plans and, as a result, this limitation (yes, it is really a limitation in the current implementation, not a bug) confuses everyone that carefully look at the information provided in an execution plan of a SQL statement executed in parallel. Hence, let’s remove some confusion…
To illustrate what the problem is, let’s have a look to a simple query that joins two tables:
SELECT * FROM master m JOIN detail d ON (m.id = d.id)
Now, let’s have a look at two parallel executions. If the two tables are equipartitioned, the following execution plan (which takes advantage of partition-wise join) is probably the most effective for such a query. Note that thanks to the partition-wise join not only there is a single set of parallel slaves (Q1,00), but, in addition, the parallel slaves do not communicate with each other (they only communicate with the query coordinator). As a result, the communication costs are equal to zero (this is because the query optimizer does not compute the costs of the communication towards the query coordinator).
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16G| 162524 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 16G| 162524 (1)| Q1,00 | P->S | QC (RAND) | | 3 | PX PARTITION HASH ALL| | 16G| 162524 (1)| Q1,00 | PCWC | | | 4 | HASH JOIN | | 16G| 162524 (1)| Q1,00 | PCWP | | | 5 | TABLE ACCESS FULL | MASTER | 125M| 1422 (1)| Q1,00 | PCWP | | | 6 | TABLE ACCESS FULL | DETAIL | 15G| 161052 (1)| Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------
If the two tables are not equipartitioned, the following execution plan might be chosen by the query optimizer. Since it does not take advantage of a partition-wise join, several set of parallel slaves are used. The first one (Q1,00) scans the MASTER table, the second one (Q1,01) scans the DETAIL table, and both of them send the data to the third one (Q1,02) that performs the join of the two tables and sends the data to the query coordinator. Since all data (about 15GB; yes, the estimations are good) is sent through the PX channels, the cost should not be zero. However, as you can see, the cost is exactly the same as the one of the previous execution plan.
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16G| 162524 (1)| | | | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 16G| 162524 (1)| Q1,02 | P->S | QC (RAND) | | 3 | HASH JOIN BUFFERED | | 16G| 162524 (1)| Q1,02 | PCWP | | | 4 | PX RECEIVE | | 125M| 1422 (1)| Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 125M| 1422 (1)| Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 125M| 1422 (1)| Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| MASTER | 125M| 1422 (1)| Q1,00 | PCWP | | | 8 | PX RECEIVE | | 15G| 161052 (1)| Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 15G| 161052 (1)| Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 15G| 161052 (1)| Q1,01 | PCWC | | | 11 | TABLE ACCESS FULL| DETAIL | 15G| 161052 (1)| Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------
For completeness, let’s compare the cost of several distribution methods (“none-none” is the one of the first execution plan above, “hash-hash” of the second one). As you can see the cost is always the same!
SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-none' FOR SELECT /*+ pq_distribute(d none none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'hash-hash' FOR SELECT /*+ pq_distribute(d hash hash) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'broadcast-none' FOR SELECT /*+ pq_distribute(d broadcast none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-broadcast' FOR SELECT /*+ pq_distribute(d none broadcast) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'partition-none' FOR SELECT /*+ pq_distribute(d partition none) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> EXPLAIN PLAN SET STATEMENT_ID 'none-partition' FOR SELECT /*+ pq_distribute(d none partition) */ * FROM master m JOIN detail d ON (m.id = d.id); SQL> SELECT statement_id, cost FROM plan_table WHERE id = 0; STATEMENT_ID COST ------------------------------ ---------- none-none 162524 hash-hash 162524 broadcast-none 162524 none-broadcast 162524 partition-none 162524 none-partition 162524
As I wrote before, the problem is not that the costs are not computed. The problem is that they are not externalized. In fact, by giving a look to a trace file generated through the event 10053 the costs are available. Here’s the relevant part (the lines starting with “---- cost” contain the most important information). As you can see there are two costs associated with every distribution method: one with the distribution costs (w/ dist) and one without them (w/o dist).
Enumerating distribution method for join between M[MASTER] and D[DETAIL]
-- Using join method #Hash Join:
---- cost NONE = 0.00
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 513 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162524.05 (w/ dist)
---- cost VALUE = 278.52
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 2 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162802.57 (w/ dist)
---- cost PARTITION-RIGHT = 271.40
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 576 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162795.46 (w/ dist)
---- cost PARTITION-LEFT = 7.12
Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 544 #groups: 1
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162531.17 (w/ dist)
---- cost BROADCAST-RIGHT = 920.78
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 8 #groups: 4
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162755.25 (w/ dist)
---- cost BROADCAST-LEFT = 7.22
---- cost with slave mapping = Outer table: MASTER Alias: M
resc: 5120.11 card 4118000.00 bytes: 32 deg: 4 resp: 1422.25
Inner table: DETAIL Alias: D
resc: 579787.63 card: 31954000.00 bytes: 526 deg: 4 resp: 161052.12
using dmeth: 16 #groups: 4
Cost per ptn: 49.68 #ptns: 4
hash_area: 16384 (max=16384) buildfrag: 5530 probefrag: 524636 ppasses: 1
buildfrag: 5530 probefrag: 524636 passes: 1
Hash join: Resc: 585106.45 Resp: 162524.05 [multiMatchCost=0.00]
---- cost(Hash Join) = 162524.05 (w/o dist), 162526.86 (w/ dist)
Since the cost are (correctly) computed, the query optimizer is able to choose the optimal plan. However, it would be nice to have the actual costs in the execution plans.
Jan 11 2010
In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination. The first one was introduced in Oracle Database 10g Release 2, the second one in Oracle Database 11g Release 2. Let’s take a look at two cases to illustrate how join elimination works.
Case #1
Up to Oracle Database 11g Release 1 join elimination is especially useful when views containing joins are used. Note, however, that join elimination does not work only with views. It can be applied to SQL statements without views as well. The following SQL statements define two tables and one view. Notice that between table T1 and table T2, there is a master-child relationship. In fact, table T2, with its column T1_ID, references the primary key of table T1.
SQL> CREATE TABLE t1 ( 2 id NUMBER NOT NULL, 3 n NUMBER, 4 pad VARCHAR2(4000), 5 CONSTRAINT t1_pk PRIMARY KEY(id) 6 ); SQL> CREATE TABLE t2 ( 2 id NUMBER NOT NULL, 3 t1_id NUMBER NOT NULL, 4 n NUMBER, 5 pad VARCHAR2(4000), 6 CONSTRAINT t2_pk PRIMARY KEY(id), 7 CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 8 ); SQL> CREATE VIEW v AS 2 SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n 3 FROM t1, t2 4 WHERE t1.id = t2.t1_id;
When all the columns are referenced, as shown in the following example, the join is regularly executed. No surprise here.
SQL> EXPLAIN PLAN FOR SELECT * FROM v; SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic')); PLAN_TABLE_OUTPUT ---------------------------------------------- Plan hash value: 3114288414 ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | T2 | | 4 | INDEX UNIQUE SCAN | T1_PK | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | ----------------------------------------------
However, as illustrated in the next example, when only columns defined in the child table are referenced, the query optimizer is able to eliminate the join. It can do so because there is a validated foreign key constraint that guarantees that all rows in table T2 reference one row in table T1.
SQL> EXPLAIN PLAN FOR SELECT t2_id, t2_n FROM v; PLAN_TABLE_OUTPUT ---------------------------------- Plan hash value: 1513984157 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T2 | ----------------------------------
The relevant part of the output of event 10053 is the following (notice that two queries are shown; the one before the transformation and the one after the transformation):
JE: Considering Join Elimination on query block SEL$2 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T1"."ID"="T2"."T1_ID"
JE: cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
JE: cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
Query block (0x2b732c78) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T2"."T1_ID"="T1"."ID"
JE: eliminate table: T1 (T1)
Registered qb: SEL$FFBD8603 0x2b732c78 (JOIN REMOVED FROM QUERY BLOCK SEL$2; SEL$2; "T1"@"SEL$2")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$FFBD8603 nbfros=1 flg=0
fro(0): flg=0 objn=86873 hint_alias="T2"@"SEL$2"
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T2" "T2"
Query block SEL$FFBD8603 (#0) simplified
Case #2
As of Oracle Database 11g Release 2 join elimination covers another case. Its aim is to avoid the execution of “unnecessary” self-joins. The following SQL statements show an example. Notice that since the join is performed on the primary key (column ID) there is no need to access the table twice. In fact, it is possible to replace the references to the eliminated table (T2 in this example) in the SELECT clause with columns of the table that is not eliminated (T1).
SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id; SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic')); PLAN_TABLE_OUTPUT ---------------------------------- Plan hash value: 3617692013 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T1 | ----------------------------------
The relevant part of the output of event 10053 is the following (also in this case notice that there are two queries):
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
Query block (0x2c14f098) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE: eliminate table: T1 (T12)
JE: Replaced column: T12.PAD with column: T11.PAD
JE: Replaced column: T12.N with column: T11.N
JE: Replaced column: T12.ID with column: T11.ID
Registered qb: SEL$DF69B110 0x2c14f098 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T12"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$DF69B110 nbfros=1 flg=0
fro(0): flg=0 objn=86871 hint_alias="T11"@"SEL$1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD" FROM "CHA"."T1" "T11"
Query block SEL$DF69B110 (#0) simplified
Note that running the previous example in Oracle Database 11g Release 1 or earlier leads, as expected, to a join like the following one.
SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id; SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic')); PLAN_TABLE_OUTPUT ---------------------------------------------- Plan hash value: 774821007 ---------------------------------------------- | Id | Operation | Name | ---------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | T1 | | 4 | INDEX UNIQUE SCAN | T1_PK | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | ----------------------------------------------