SQL Tuning by adding column alias

SQL Tuning is not about adding an index to the table or adding hints or rewriting the query, it is all about understanding how oracle works and most importantly how the CBO makes use of the table and column level statistics, histograms, constraints, datatypes, system statistics and other optimizer transformation applied to build an efficient plan.

The below example started from a real project where we are moving an application database running on 11.2.0.4 in HPUX platform to a database 11.2.0.4 running on Exadata platform.

After migration, we found that a delete statement was running for more than 9hours in Exadata that got completed in few secs in non-exadata platform.

Here is a test case, a table with sample data in it.

demo@EXACC> create table t as

  2  select object_id c1,

  3     owner c2,

  4     object_type c3 ,a.*

  5  from all_objects a

  6  where rownum <= 10000;

Table created.

demo@EXACC> insert into t select * from t where rownum <=10;

10 rows created.

demo@EXACC> commit;

Commit complete.

demo@EXACC> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

The estimated plan for the “delete” statement in Exadata platform was like this.

demo@EXACC> explain plan for

  2  delete from t where rowid in (

  3  select rowid from (

  4  select c1,c3,rowid, rank() over( partition by c1 order by c3 desc) rnk

  5  from t

  6  where c1 in (

  7  select c1 from t

  8  group by c1,c2

  9  having count(*) > 1 )

 10     )

 11  where rnk > 1 ) ;

Explained.

demo@EXACC> @xplan

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------

Plan hash value: 3590743719

------------------------------------------------------------------------------------

| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT                   |          |     1 |    24 |   362K  (3)|

|   1 |  DELETE                            | T        |       |       |            |

|   2 |   NESTED LOOPS                     |          |     1 |    24 |   362K  (3)|

|   3 |    VIEW                            | VW_NSO_1 |     1 |    12 |   362K  (3)|

|   4 |     SORT UNIQUE                    |          |     1 |    25 |            |

|*  5 |      VIEW                          |          |     1 |    25 |   362K  (3)|

|   6 |       WINDOW SORT                  |          |     1 |    24 |   362K  (3)|

|*  7 |        FILTER                      |          |       |       |            |

|   8 |         TABLE ACCESS STORAGE FULL  | T        | 10010 |   234K|    71   (0)|

|*  9 |         FILTER                     |          |       |       |            |

|  10 |          HASH GROUP BY             |          |     2 |    20 |    72   (2)|

|  11 |           TABLE ACCESS STORAGE FULL| T        | 10010 |    97K|    71   (0)|

|  12 |    TABLE ACCESS BY USER ROWID      | T        |     1 |    12 |     1   (0)|

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - filter("RNK">1)

   7 - filter( EXISTS (SELECT 0 FROM "T" "T" GROUP BY "C1","C2" HAVING "C1"=:B1 AND

              COUNT(*)>1))

   9 - filter("C1"=:B1 AND COUNT(*)>1)

27 rows selected.

The plan shows that for each row we retrieve from table T at the step# 8 , the sub query associated at the step#7 will be executed once  for each row we retrieve from table T.

The actual plan for the “delete” statement in Exadata was like this.

demo@EXACC> set serveroutput off

demo@EXACC> delete /*+ gather_plan_statistics */ from t where rowid in (

  2  select rowid from (

  3  select c1,c3,rowid, rank() over( partition by c1 order by c3 desc) rnk

  4  from t

  5  where c1 in (

  6  select c1 from t

  7  group by c1,c2

  8  having count(*) > 1 )

  9     )

 10  where rnk > 1 ) ;

0 rows deleted.

demo@EXACC> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  39gn43tyrtdh4, child number 0

-------------------------------------

delete /*+ gather_plan_statistics */ from t where rowid in ( select

rowid from ( select c1,c3,rowid, rank() over( partition by c1 order by

c3 desc) rnk from t where c1 in ( select c1 from t group by c1,c2

having count(*) > 1 )  ) where rnk > 1 )

Plan hash value: 3590743719

---------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT                   |          |      1 |        |      0 |00:00:42.43 |    1740K|

|   1 |  DELETE                            | T        |      1 |        |      0 |00:00:42.43 |    1740K|

|   2 |   NESTED LOOPS                     |          |      1 |      1 |      0 |00:00:42.43 |    1740K|

|   3 |    VIEW                            | VW_NSO_1 |      1 |      1 |      0 |00:00:42.43 |    1740K|

|   4 |     SORT UNIQUE                    |          |      1 |      1 |      0 |00:00:42.43 |    1740K|

|*  5 |      VIEW                          |          |      1 |      1 |      0 |00:00:42.43 |    1740K|

|   6 |       WINDOW SORT                  |          |      1 |      1 |     20 |00:00:42.43 |    1740K|

|*  7 |        FILTER                      |          |      1 |        |     20 |00:00:00.08 |    1740K|

|   8 |         TABLE ACCESS STORAGE FULL  | T        |      1 |  10010 |  10010 |00:00:00.01 |     174 |

|*  9 |         FILTER                     |          |  10000 |        |     10 |00:00:42.41 |    1740K|

|  10 |          HASH GROUP BY             |          |  10000 |      2 |     99M|00:00:41.83 |    1740K|

|  11 |           TABLE ACCESS STORAGE FULL| T        |  10000 |  10010 |    100M|00:00:10.80 |    1740K|

|  12 |    TABLE ACCESS BY USER ROWID      | T        |      0 |      1 |      0 |00:00:00.01 |       0 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - filter("RNK">1)

   7 - filter( IS NOT NULL)

   9 - filter(("C1"=:B1 AND COUNT(*)>1))

34 rows selected.

It is clear that Step# 9,10,11 was executed once for each row we retrieve from Step#8 – and those portions were the top consumers of logical IO’s and elapsed time.

Where as in non-exadata platform the plan produced by the Optimizer was like this:

demo@NON-EXACC> set serveroutput off

demo@NON-EXACC> delete /*+ gather_plan_statistics */ from t where rowid in (

  2  select rowid from (

  3  select c1,c3,rowid, rank() over( partition by c1 order by c3 desc) rnk

  4  from t

  5  where c1 in (

  6  select c1 from t

  7  group by c1,c2

  8  having count(*) > 1 )

  9     )

 10  where rnk > 1 ) ;

0 rows deleted.

demo@NON-EXACC> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------

SQL_ID  39gn43tyrtdh4, child number 0

-------------------------------------

delete /*+ gather_plan_statistics */ from t where rowid in ( select

rowid from ( select c1,c3,rowid, rank() over( partition by c1 order by

c3 desc) rnk from t where c1 in ( select c1 from t group by c1,c2

having count(*) > 1 )  ) where rnk > 1 )

Plan hash value: 1715516498

---------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |          |      1 |        |      0 |00:00:00.01 |     346 |

|   1 |  DELETE                      | T        |      1 |        |      0 |00:00:00.01 |     346 |

|   2 |   NESTED LOOPS               |          |      1 |      1 |      0 |00:00:00.01 |     346 |

|   3 |    VIEW                      | VW_NSO_2 |      1 |    502 |      0 |00:00:00.01 |     346 |

|   4 |     SORT UNIQUE              |          |      1 |      1 |      0 |00:00:00.01 |     346 |

|*  5 |      VIEW                    |          |      1 |    502 |      0 |00:00:00.01 |     346 |

|   6 |       WINDOW SORT            |          |      1 |    502 |     20 |00:00:00.01 |     346 |

|*  7 |        HASH JOIN RIGHT SEMI  |          |      1 |    502 |     20 |00:00:00.01 |     346 |

|   8 |         VIEW                 | VW_NSO_1 |      1 |    501 |     10 |00:00:00.01 |     173 |

|*  9 |          FILTER              |          |      1 |        |     10 |00:00:00.01 |     173 |

|  10 |           SORT GROUP BY      |          |      1 |     26 |  10000 |00:00:00.01 |     173 |

|  11 |            TABLE ACCESS FULL | T        |      1 |  10010 |  10010 |00:00:00.01 |     173 |

|  12 |         TABLE ACCESS FULL    | T        |      1 |  10010 |  10010 |00:00:00.01 |     173 |

|  13 |    TABLE ACCESS BY USER ROWID| T        |      0 |      1 |      0 |00:00:00.01 |       0 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - filter("RNK">1)

   7 - access("C1"="C1")

   9 - filter(COUNT(*)>1)

35 rows selected.

The subquery was transformed as join and executed only once in overall execution.

Upon analyzing why the subquery does not transformed into join in Exadata platform, we took 10053 trace from both the environment for comparison.

10053 trace from non-exadata platform.

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

Cost-Based Subquery Unnesting

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

SU: Unnesting query blocks in query block DEL$1 (#1) that are valid to unnest.

Subquery removal for query block SEL$1 (#2)

RSW: Not valid for subquery removal SEL$1 (#2)

Subquery unchanged.

Subquery removal for query block SEL$3 (#4)

RSW: Not valid for subquery removal SEL$3 (#4)

Subquery unchanged.

Subquery Unnesting on query block SEL$2 (#3)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block SEL$2 (#3).

SU:   Checking validity of unnesting subquery SEL$3 (#4)

SU:   Passed validity checks, but requires costing.

SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (4) : (1)

SU:   Transform an ANY subquery to semi-join or distinct.

10053 trace from exadata platform.

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

Cost-Based Subquery Unnesting

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

SU: Unnesting query blocks in query block DEL$1 (#1) that are valid to unnest.

Subquery removal for query block SEL$1 (#2)

RSW: Not valid for subquery removal SEL$1 (#2)

Subquery unchanged.

Subquery removal for query block SEL$3 (#4)

RSW: Not valid for subquery removal SEL$3 (#4)

Subquery unchanged.

Subquery Unnesting on query block SEL$2 (#3)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block SEL$2 (#3).

SU:   Checking validity of unnesting subquery SEL$3 (#4)

SU:     SU bypassed: Subquery in a view with rowid reference.

SU:   Validity checks failed.

Subquery Unnesting on query block DEL$1 (#1)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block DEL$1 (#1).

SU:   Checking validity of unnesting subquery SEL$1 (#2)

SU:   Passed validity checks, but requires costing.

SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

SU:   Transform an ANY subquery to semi-join or distinct.

The above highlighted section clearly shows that – the “rowid’ referenced in the query block Q3, doesn’t point to “rowid’ referenced in query block Q2 and hence the subquery doesn’t transform into a join in Exadata platform.

delete from t where rowid in (

select /*+ qb_name(q3) */ rowid from (

select /*+ qb_name(q2) */ c1,c3,rowid, rank() over( partition by c1 order by c3 desc) rnk

from t

where c1 in (

select /*+ qb_name(q1) */ c1 from t

group by c1,c2

having count(*) > 1 )

       )

where rnk > 1 ) ;

However if we do alias the column “rowid” in the query block Q2, then plan changes – sub query transformed into a join- and completed within few seconds.

demo@EXACC> set serveroutput off

demo@EXACC> delete /*+ gather_plan_statistics */ from t where rowid in (

  2  select /*+ qb_name(q3) */ rid from (

  3  select /*+ qb_name(q2) */ c1,c3,rowid rid, rank() over( partition by c1 order by c3 desc) rnk

  4  from t

  5  where c1 in (

  6  select /*+ qb_name(q1) */ c1 from t

  7  group by c1,c2

  8  having count(*) > 1 )

  9     )

 10  where rnk > 1 ) ;

0 rows deleted.

demo@EXACC> select * from table( dbms_xplan.display_cursor(format=>'allstats last +alias'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  3kqqfx7cvx958, child number 0

-------------------------------------

delete /*+ gather_plan_statistics */ from t where rowid in ( select /*+

qb_name(q3) */ rid from ( select /*+ qb_name(q2) */ c1,c3,rowid rid,

rank() over( partition by c1 order by c3 desc) rnk from t where c1 in (

select /*+ qb_name(q1) */ c1 from t group by c1,c2 having count(*) > 1

)  ) where rnk > 1 )

Plan hash value: 1715516498

----------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |     348 |

|   1 |  DELETE                             | T        |      1 |        |      0 |00:00:00.01 |     348 |

|   2 |   NESTED LOOPS                      |          |      1 |      1 |      0 |00:00:00.01 |     348 |

|   3 |    VIEW                             | VW_NSO_2 |      1 |    502 |      0 |00:00:00.01 |     348 |

|   4 |     SORT UNIQUE                     |          |      1 |      1 |      0 |00:00:00.01 |     348 |

|*  5 |      VIEW                           |          |      1 |    502 |      0 |00:00:00.01 |     348 |

|   6 |       WINDOW SORT                   |          |      1 |    502 |     20 |00:00:00.01 |     348 |

|*  7 |        HASH JOIN RIGHT SEMI         |          |      1 |    502 |     20 |00:00:00.01 |     348 |

|   8 |         VIEW                        | VW_NSO_1 |      1 |    501 |     10 |00:00:00.01 |     174 |

|*  9 |          FILTER                     |          |      1 |        |     10 |00:00:00.01 |     174 |

|  10 |           SORT GROUP BY             |          |      1 |     26 |  10000 |00:00:00.01 |     174 |

|  11 |            TABLE ACCESS STORAGE FULL| T        |      1 |  10010 |  10010 |00:00:00.01 |     174 |

|  12 |         TABLE ACCESS STORAGE FULL   | T        |      1 |  10010 |  10010 |00:00:00.01 |     174 |

|  13 |    TABLE ACCESS BY USER ROWID       | T        |      0 |      1 |      0 |00:00:00.01 |       0 |

----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$BB91A27F

   3 - SEL$186AFB95 / VW_NSO_2@SEL$BB91A27F

   4 - SEL$186AFB95

   5 - SEL$61D88735 / from$_subquery$_002@Q3

   6 - SEL$61D88735

   8 - SEL$CAF36C18 / VW_NSO_1@SEL$61D88735

   9 - SEL$CAF36C18

  11 - SEL$CAF36C18 / T@Q1

  12 - SEL$61D88735 / T@Q2

  13 - SEL$BB91A27F / T@DEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - filter("RNK">1)

   7 - access("C1"="C1")

   9 - filter(COUNT(*)>1)

50 rows selected.

demo@EXACC>

So the lesson learnt is when having the Pseudo columns in the Select statement have them properly aliased & never leave them open as such (specifically if those Pseudo columns are referenced in the outer most query block either in the filters/projections).