Home » RDBMS Server » Performance Tuning » look like same query, but huge query plan different. (Oracle 11g)
look like same query, but huge query plan different. [message #602930] Tue, 10 December 2013 07:46 Go to next message
na.dharma@gmail.com
Messages: 74
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
Re: look like same query, but huge query plan different. [message #602932 is a reply to message #602930] Tue, 10 December 2013 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: look like same query, but huge query plan different. [message #602956 is a reply to message #602930] Tue, 10 December 2013 09:55 Go to previous messageGo to next message
martijn
Messages: 278
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 Go to previous messageGo to next message
cookiemonster
Messages: 11285
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.
Re: look like same query, but huge query plan different. [message #603035 is a reply to message #602966] Wed, 11 December 2013 02:37 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 74
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

Re: look like same query, but huge query plan different. [message #603036 is a reply to message #603035] Wed, 11 December 2013 02:39 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 74
Registered: May 2008
Location: bangalore
Member

After moving WHERE ROWNUM <= 1001 into last, there is no change of query plan
Re: look like same query, but huge query plan different. [message #603038 is a reply to message #603036] Wed, 11 December 2013 02:48 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 74
Registered: May 2008
Location: bangalore
Member

what is the different between INDEX SKIP SCAN and INDEX FAST FULL SCAN?
Re: look like same query, but huge query plan different. [message #603043 is a reply to message #603038] Wed, 11 December 2013 03:08 Go to previous message
John Watson
Messages: 4861
Registered: January 2010
Location: Global Village
Senior Member
na.dharma@gmail.com wrote on Wed, 11 December 2013 08:48
what is the different between INDEX SKIP SCAN and INDEX FAST FULL SCAN?
See chapter 11 of the 11.2 Performance Tuning Guide.
Previous Topic: SQL with BIND Variable Slow - Bind Peeking
Next Topic: how to avoid negative values to number datatype in oracle
Goto Forum:
  


Current Time: Sat Dec 20 10:46:43 CST 2014

Total time taken to generate the page: 0.12516 seconds