29 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

Push down your hints into inner views using an object alias

User Rating:  / 1
PoorBest 

Push down your hints into inner views using an object alias.


You use thirth party software, a schema is compiled, tables, indexes, views, procedures, triggers ...
You face a particular performance problem with one given in house written query, this query is yours and accesses one of the thirth party views, you can' t modify ( it is not supported )
The schema statistics are up to date
You know that if you would have the ability to recompile the view using a particular hint your performance problem would be solved, but as said above you are not allowed to modify that view.


Here is how you push down your hints into inner views using an object alias.

We use the sql tuning sample schema and compile some additional objects

 

STEP 1 : In the below example we assume your thirth party view is compiled


SQL > create or replace view I_VIEW as
select su_pk,su_name,su_comment,inner_view.maxamount from
t_supplier_su,
( select max(or_totalamount) maxamount,su_fk from t_order_or group by su_fk ) inner_view
where t_supplier_su.su_pk = inner_view.su_fk(+) and t_supplier_su.su_name is not null;

View created

SQL> create table I_TABLE as select * from I_VIEW;

Table created.

SQL> truncate table I_TABLE;

Table truncated.

 


STEP 2 : We assume this is your problem query  ( note this is just an example )

 

SQL> explain plan for insert /*+ APPEND  PARALLEL(I_TABLE,DEFAULT,DEFAULT) */ into I_TABLE (su_pk,su_name,su_comment,maxamount) (select * from I_VIEW);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1638255513

------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |   Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   1 |  LOAD AS SELECT        | I_TABLE       |       |       |              |          |
|*  2 |   HASH JOIN RIGHT OUTER|               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   3 |    VIEW                |               |    99 |  2574 |  6947     (1)| 00:01:24 |
|   4 |     SORT GROUP BY      |               |    99 |   891 |  6947     (1)| 00:01:24 |
|   5 |      TABLE ACCESS FULL | T_ORDER_OR    |  1000K|  8789K|  6902     (1)| 00:01:23 |
|*  6 |    TABLE ACCESS FULL   | T_SUPPLIER_SU |   100 | 20300 |     3     (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK"(+))
6 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
19 rows selected.

 

STEP 3 : The hint is not taken into account  ( note this is just an example )

 
SQL> explain plan for insert /*+ APPEND PARALLEL(I_TABLE,DEFAULT,DEFAULT) */ into I_TABLE (su_pk,su_name,su_comment,maxamount) (select /*+ PUSH_PRED(inner_view) */ * from I_VIEW);

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1638255513

------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |   Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   1 |  LOAD AS SELECT        | I_TABLE       |       |       |              |          |
|*  2 |   HASH JOIN RIGHT OUTER|               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   3 |    VIEW                |               |    99 |  2574 |  6947     (1)| 00:01:24 |
|   4 |     SORT GROUP BY      |               |    99 |   891 |  6947     (1)| 00:01:24 |
|   5 |      TABLE ACCESS FULL | T_ORDER_OR    |  1000K|  8789K|  6902     (1)| 00:01:23 |
|*  6 |    TABLE ACCESS FULL   | T_SUPPLIER_SU |   100 | 20300 |     3     (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK"(+))
6 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
19 rows selected.

 

STEP 4 : We retrieve the aliases using dbms_xplan

 
SQL> select * from table(dbms_xplan.display(null,null,'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1638255513

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes |   Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |                |   100 | 22900 |  6951     (1)| 00:01:24 |
|   1 |  LOAD AS SELECT        | I_TABLE        |       |       |              |          |
|*  2 |   HASH JOIN RIGHT OUTER|                |   100 | 22900 |  6951     (1)| 00:01:24 |
|   3 |    VIEW                |                |    99 |  2574 |  6947     (1)| 00:01:24 |
|   4 |     SORT GROUP BY      |                |    99 |   891 |  6947     (1)| 00:01:24 |
|   5 |      TABLE ACCESS FULL | T_ORDER_OR     |  1000K|  8789K|  6902     (1)| 00:01:23 |
|*  6 |    TABLE ACCESS FULL   | T_SUPPLIER_SU  |   100 | 20300 |     3     (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - SEL$F5BB74E1
3 - SEL$3        / INNER_VIEW@SEL$2
4 - SEL$3
5 - SEL$3        / T_ORDER_OR@SEL$3
6 - SEL$F5BB74E1 / T_SUPPLIER_SU@SEL$2

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

2 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK"(+))
6 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
28 rows selected.


STEP 5 : The hint is taken into account if we reference the object alias  ( note this is just an example )

 

SQL> explain plan for insert /*+ APPEND  PARALLEL(I_TABLE,DEFAULT,DEFAULT) */ into I_TABLE (su_pk,su_name,su_comment,maxamount) (select /*+ PUSH_PRED(INNER_VIEW@SEL$2) */  * from I_VIEW);

Explained.


SQL> select * from table(dbms_xplan.display(null,null,'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3639051378

--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT           |               |   100 | 21800 |   690K  (1)| 02:18:01 |
|   1 |  LOAD AS SELECT            | I_TABLE       |       |       |            |          |
|   2 |   NESTED LOOPS OUTER       |               |   100 | 21800 |   690K  (1)| 02:18:01 |
|*  3 |    TABLE ACCESS FULL       | T_SUPPLIER_SU |   100 | 20300 |     3   (0)| 00:00:01 |
|   4 |    VIEW PUSHED PREDICATE   |               |     1 |    15 |  6900   (1)| 00:01:23 |
|   5 |     SORT GROUP BY          |               |     1 |     9 |  6900   (1)| 00:01:23 |
|*  6 |      TABLE ACCESS FULL     | T_ORDER_OR    | 10101 | 90909 |  6900   (1)| 00:01:23 |
--------------------------------------------------------------------------------------------

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

1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / T_SUPPLIER_SU@SEL$2
4 - SEL$B01C6807 / INNER_VIEW@SEL$2
5 - SEL$B01C6807
6 - SEL$B01C6807 / T_ORDER_OR@SEL$3

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

3 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
6 - filter("SU_FK"="T_SUPPLIER_SU"."SU_PK")

28 rows selected.

SQL> explain plan for insert /*+ APPEND  PARALLEL(I_TABLE,DEFAULT,DEFAULT) */ into I_TABLE (su_pk,su_name,su_comment,maxamount) (select /*+ NO_PUSH_PRED(INNER_VIEW@SEL$2) */  * from I_VIEW);

Explained.

SQL>  select * from table(dbms_xplan.display(null,null,'ALIAS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1638255513

------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |   Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   1 |  LOAD AS SELECT        | I_TABLE       |       |       |              |          |
|*  2 |   HASH JOIN RIGHT OUTER|               |   100 | 22900 |  6951     (1)| 00:01:24 |
|   3 |    VIEW                |               |    99 |  2574 |  6947     (1)| 00:01:24 |
|   4 |     SORT GROUP BY      |               |    99 |   891 |  6947     (1)| 00:01:24 |
|   5 |      TABLE ACCESS FULL | T_ORDER_OR    |  1000K|  8789K|  6902     (1)| 00:01:23 |
|*  6 |    TABLE ACCESS FULL   | T_SUPPLIER_SU |   100 | 20300 |     3     (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

1 - SEL$F5BB74E1
3 - SEL$3        / INNER_VIEW@SEL$2
4 - SEL$3
5 - SEL$3        / T_ORDER_OR@SEL$3
6 - SEL$F5BB74E1 / T_SUPPLIER_SU@SEL$2

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

2 - access("T_SUPPLIER_SU"."SU_PK"="INNER_VIEW"."SU_FK"(+))
6 - filter("T_SUPPLIER_SU"."SU_NAME" IS NOT NULL)
28 rows selected.