OraFAQ Forum: Performance Tuning » look like same query, but huge query plan different.

Home » RDBMS Server » Performance Tuning » look like same query, but huge query plan different. (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

look like same query, but huge query plan different. [message #602930]

Tue, 10 December 2013 07:46

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi

Can you please check the below query and query plan. the query look like same but, there is huge different between explan plan.

Can any one explan what is the issue hear

 SQL> explain plan for SELECT a.ticket_no,
  2         a.original_trade_reference,
  3         a.trade_id,
  4         ROWNUM version_no,
  5         a.user_id,
  6         a.team,
  7         a.row_created_dttm,
  8         a.original_value,
  9         a.current_value,
 10         a.adjusted_value,
 11         a.adjustment_comment,
 12         a.sensitivity_type
 13      FROM (select /*+ index(a_adjustment_audit.a_adj_audit_pk) */
 14                 audit_metadata_1 ticket_No,
 15                 audit_metadata_2 original_trade_reference,
 16                 audit_metadata_3 trade_id,
 17                 audit_user_id user_id,
 18                 team team ,
 19                 row_created_dttm  row_created_dttm,
 20                  to_number(audit_metadata_4) original_value ,
 21                  to_number(audit_metadata_5) current_value ,
 22                  to_number(audit_metadata_5) -   to_number(audit_metadata_4) adjusted_value,
 23                  adjustment_comment adjustment_comment,
 24                  (select sensitivity_type_code
 25                                          from f_sensitivity
 26                                          where sensitivity_key = aud.row_bridge_key) sensitivity_type
 27                                from a_adjustment_audit aud where aud.table_name ='F_SENSITIVITY'
 28                                and aud.audit_metadata_1 = 'CSISLS2013070804676'
 29                                ORDER BY row_created_dttm ASC ) a
 30  /

Explained.

Elapsed: 00:00:00.20
SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 908384537

-----------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |     6 |  4542 |     3  (34)| 00:00:01 |       |    |
|   1 |  PARTITION LIST ALL  |                    |     1 |    18 |    42   (0)| 00:00:01 |     1 |    26 |
|   2 |   PARTITION LIST ALL |                    |     1 |    18 |    42   (0)| 00:00:01 |     1 |  LAST |
|*  3 |    INDEX SKIP SCAN   | F_SENSITIVITY_PK   |     1 |    18 |    42   (0)| 00:00:01 |     1 |  1908 |
|   4 |  COUNT               |                    |       |       |            |          |       |    |
|   5 |   VIEW               |                    |     6 |  4542 |     3  (34)| 00:00:01 |       |    |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|   6 |    SORT ORDER BY     |                    |     6 |   762 |     3  (34)| 00:00:01 |       |    |
|*  7 |     TABLE ACCESS FULL| A_ADJUSTMENT_AUDIT |     6 |   762 |     2   (0)| 00:00:01 |       |    |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("SENSITIVITY_KEY"=:B1)
       filter("SENSITIVITY_KEY"=:B1)
   7 - filter("AUD"."AUDIT_METADATA_1"='CSISLS2013070804676' AND
              "AUD"."TABLE_NAME"='F_SENSITIVITY')

22 rows selected.

Elapsed: 00:00:00.73

SQL> explain plan for SELECT a.ticket_no,
  2         a.original_trade_reference,
  3         a.trade_id,
  4         ROWNUM version_no,
  5         a.user_id,
  6         a.team,
  7         a.row_created_dttm,
  8         a.original_value,
  9         a.current_value,
 10         a.adjusted_value,
 11         a.adjustment_comment,
 12         a.sensitivity_type
 13    FROM (  SELECT /*+ index(a_adjustment_audit.a_adj_audit_pk) */
 14                  aud.audit_metadata_1 ticket_no,
 15                   aud.audit_metadata_2 original_trade_reference,
 16                   aud.audit_metadata_3 trade_id,
 17                   aud.audit_user_id user_id,
 18                   aud.team team,
 19                   aud.row_created_dttm row_created_dttm,
 20                   TO_NUMBER(aud.audit_metadata_4) original_value,
 21                   TO_NUMBER(aud.audit_metadata_5) current_value,
 22                   TO_NUMBER(aud.audit_metadata_5) - TO_NUMBER(audit_metadata_4) adjusted_value,
 23                   aud.adjustment_comment adjustment_comment,
 24                   fs.sensitivity_type_code sensitivity_type
 25              FROM a_adjustment_audit aud INNER JOIN f_sensitivity fs ON (aud.row_bridge_key = fs.sensitivity_key)
 26             WHERE aud.table_name = 'F_SENSITIVITY' AND
 27                   aud.audit_metadata_1 = 'CSISLS2013070804676' AND
 28                   ROWNUM <= 1001
 29          ORDER BY row_created_dttm ASC) a
 30  /

Explained.

Elapsed: 00:00:00.21
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2836312423

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     6 |  4542 |   148K  (6)| 00:30:36 |    |  |
|   1 |  COUNT                      |                    |       |       |            |          |    |  |
|   2 |   VIEW                      |                    |     6 |  4542 |   148K  (6)| 00:30:36 |    |  |
|   3 |    SORT ORDER BY            |                    |     6 |   870 |   148K  (6)| 00:30:36 |    |  |
|*  4 |     COUNT STOPKEY           |                    |       |       |            |          |    |  |
|*  5 |      HASH JOIN              |                    |     6 |   870 |   148K  (6)| 00:30:36 |    |  |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       TABLE ACCESS FULL     | A_ADJUSTMENT_AUDIT |     6 |   762 |     2   (0)| 00:00:01 |    |  |
|   7 |       PARTITION LIST ALL    |                    |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |    26 |
|   8 |        PARTITION LIST ALL   |                    |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |  LAST |
|   9 |         INDEX FAST FULL SCAN| F_SENSITIVITY_PK   |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |  1908 |
------------------------------------------------------------------------------------------------------------------

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

   4 - filter(ROWNUM<=1001)
   5 - access("AUD"."ROW_BRIDGE_KEY"="FS"."SENSITIVITY_KEY")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
   6 - filter("AUD"."AUDIT_METADATA_1"='CSISLS2013070804676' AND "AUD"."TABLE_NAME"='F_SENSITIVITY')

23 rows selected.

Elapsed: 00:00:00.59

Report message to a moderator

Re: look like same query, but huge query plan different. [message #602932 is a reply to message #602930]

Tue, 10 December 2013 07:59

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well the 2nd query appears to be wrong
This:

SELECT *
FROM (SELECT ....
      FROM ....
      WHERE ....
      AND rownum <= 1001
      ORDER BY <column>
     )

Finds all records that match the rest of where clause, pick a random selection of 1001 rows and then orders them.
Assuming you want the first 1001 rows according to the order by (a standard top-n query) you need to structure it like this:

SELECT *
FROM (SELECT ....
      FROM ....
      WHERE ....
      ORDER BY <column>
     )
WHERE rownum <= 1001

Remember that the where clause is always applied before the corresponding order by.

So fix the 2nd query and then compare plans.

Report message to a moderator

Re: look like same query, but huge query plan different. [message #602956 is a reply to message #602930]

Tue, 10 December 2013 09:55

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

To me the queries do not look the same:
SQL1 : You select sensitivity_type_code starting on row 24 of the list with a subquery
SQL2 : You select sensitivity_type_code starting on row 25 with an inner join

SQL1 : you do not limit the output by using rownum
SQL2 : you somehow limit the output by using numrow<=1001 (I doubt whether this is a valid strategy)

Ow...is your problem from this question solved? I ask, because the mentioned SQL's do look like very similar, and you did not provide feedback yet.

CM: fixed URL.

[Updated on: Tue, 10 December 2013 10:16] by Moderator

Report message to a moderator

Re: look like same query, but huge query plan different. [message #602966 is a reply to message #602956]

Tue, 10 December 2013 10:22

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

martijn wrote on Tue, 10 December 2013 15:55

To me the queries do not look the same:
SQL1 : You select sensitivity_type_code starting on row 24 of the list with a subquery
SQL2 : You select sensitivity_type_code starting on row 25 with an inner join

Assuming there always a matching record in f_sensitivity those two should give the same results (ignoring the rownum issue).
If there isn't always a matching record then the join in 2nd needs to be an outer join.

Report message to a moderator

Re: look like same query, but huge query plan different. [message #603035 is a reply to message #602966]

Wed, 11 December 2013 02:37

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

Hi cookiemonster,

SQL> explain plan for SELECT a.ticket_no,
  2         a.original_trade_reference,
  3         a.trade_id,
  4         ROWNUM version_no,
  5         a.user_id,
  6         a.team,
  7         a.row_created_dttm,
  8         a.original_value,
  9         a.current_value,
 10         a.adjusted_value,
 11         a.adjustment_comment,
 12         a.sensitivity_type
 13    FROM ( SELECT /*+ index(a_adjustment_audit.a_adj_audit_pk) */
 14                  aud.audit_metadata_1 ticket_no,
 15                   aud.audit_metadata_2 original_trade_reference,
 16                   aud.audit_metadata_3 trade_id,
 17                   aud.audit_user_id user_id,
 18                   aud.team team,
 19                   aud.row_created_dttm row_created_dttm,
 20                   TO_NUMBER(aud.audit_metadata_4) original_value,
 21                   TO_NUMBER(aud.audit_metadata_5) current_value,
 22                   TO_NUMBER(aud.audit_metadata_5) - TO_NUMBER(audit_metadata_4) adjusted_value,
 23                   aud.adjustment_comment adjustment_comment,
 24                   fs.sensitivity_type_code sensitivity_type
 25              FROM a_adjustment_audit aud INNER JOIN f_sensitivity fs ON (aud.row_bridge_key = fs.sensitivity_key)
 26             WHERE aud.table_name = 'F_SENSITIVITY' AND
 27                   aud.audit_metadata_1 = 'CSISLS2013070804676'
 28          ORDER BY row_created_dttm ASC) a
 29          WHERE ROWNUM <= 1001
 30  /

Explained.

Elapsed: 00:00:00.21
SQL> select * from table(xplan.display);
select * from table(xplan.display)
                    *
ERROR at line 1:
ORA-00904: "XPLAN"."DISPLAY": invalid identifier


Elapsed: 00:00:00.37
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4129871990

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                    |     6 |  4542 |   148K  (6)| 00:30:36 |    |  |
|*  1 |  COUNT STOPKEY             |                    |       |       |            |          |    |  |
|   2 |   VIEW                     |                    |     6 |  4542 |   148K  (6)| 00:30:36 |    |  |
|*  3 |    SORT ORDER BY STOPKEY   |                    |     6 |   870 |   148K  (6)| 00:30:36 |    |  |
|*  4 |     HASH JOIN              |                    |     6 |   870 |   148K  (6)| 00:30:36 |    |  |
|*  5 |      TABLE ACCESS FULL     | A_ADJUSTMENT_AUDIT |     6 |   762 |     2   (0)| 00:00:01 |    |  |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|   6 |      PARTITION LIST ALL    |                    |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |    26 |
|   7 |       PARTITION LIST ALL   |                    |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |  LAST |
|   8 |        INDEX FAST FULL SCAN| F_SENSITIVITY_PK   |   467M|  8017M|   145K  (4)| 00:29:53 |     1 |  1908 |
-----------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1001)
   3 - filter(ROWNUM<=1001)
   4 - access("AUD"."ROW_BRIDGE_KEY"="FS"."SENSITIVITY_KEY")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
   5 - filter("AUD"."AUDIT_METADATA_1"='CSISLS2013070804676' AND "AUD"."TABLE_NAME"='F_SENSITIVITY')

23 rows selected.

Elapsed: 00:00:00.64

Report message to a moderator