This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 3, 2013 3:19 PM by Jonathan Lewis RSS

Differenet Explain Plan for Same Query

Osama_Mustafa Oracle ACE
Currently Being Moderated
DB Version : 11.2.0.3
OS Version : AIX 6

I have two Queries ( The Difference between Them Only 940 and 584 ) When I Generate Explain Plan Different Output Why ? Why CPU time is Different Each Time

First Query Statement  :
INSERT INTO TempSearchResult (t_aid,
                              t_umidl,
                              t_umidh,
                              X_CREA_DATE_TIME_MESG)
   SELECT z.aid,
          z.mesg_s_umidl,
          z.mesg_s_umidh,
          z.mesg_crea_date_time
     FROM (  SELECT m.aid,
                    m.mesg_s_umidl,
                    m.mesg_s_umidh,
                    m.mesg_crea_date_time
               FROM RSMESG_ESIDE m
              WHERE 1 = 1
                    AND m.mesg_crea_date_time BETWEEN TO_DATE (
                                                         '20120131 10:00:00',
                                                         'YYYYMMDD HH24:MI:SS')
                                                  AND TO_DATE (
                                                         '20120131 13:00:00',
                                                         'YYYYMMDD HH24:MI:SS')
                    AND m.mesg_frmt_name = 'Swift'
                    AND m.mesg_sender_x1 = 'SOGEFRPPXXX'
                    AND m.mesg_nature = 'FINANCIAL_MSG'
                    AND m.mesg_type LIKE '950'
           ORDER BY mesg_crea_date_time) z
    WHERE ROWNUM <= 5000
     
Explain Plan for First Query :
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3901722890

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                          |                   |  2866 |   134K|   197   (3)| 00:00:03 |       |       |
|   1 |  LOAD TABLE CONVENTIONAL                  | TEMPSEARCHRESULT  |       |       |            |          |       |       |
|*  2 |   COUNT STOPKEY                           |                   |       |       |            |          |       |       |
|   3 |    VIEW                                   |                   |  2866 |   134K|   197   (3)| 00:00:03 |       |       |
|*  4 |     SORT ORDER BY STOPKEY                 |                   |  2866 |   333K|   197   (3)| 00:00:03 |       |       |
|   5 |      NESTED LOOPS                         |                   |  2866 |   333K|   196   (2)| 00:00:03 |       |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       NESTED LOOPS                        |                   |  1419 |   148K|   196   (2)| 00:00:03 |       |       |
|*  7 |        HASH JOIN                          |                   |  1419 |   141K|   196   (2)| 00:00:03 |       |       |
|   8 |         NESTED LOOPS                      |                   |    91 |  1911 |     2   (0)| 00:00:01 |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID      | SUSER             |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|* 10 |           INDEX UNIQUE SCAN               | IX_SUSER          |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 11 |          INDEX FULL SCAN                  | PK_SUNITUSERGROUP |    91 |  1001 |     1   (0)| 00:00:01 |       |       |
|  12 |         PARTITION RANGE SINGLE            |                   |  1450 |   114K|   193   (2)| 00:00:03 |     2 |     2 |
|* 13 |          TABLE ACCESS BY LOCAL INDEX ROWID| RMESG             |  1450 |   114K|   193   (2)| 00:00:03 |     2 |     2 |
|* 14 |           INDEX SKIP SCAN                 | IX_RMESG          |   415 |       |    14  (15)| 00:00:01 |     2 |     2 |
|* 15 |        INDEX UNIQUE SCAN                  | PK_SMSGUSERGROUP  |     1 |     5 |     0   (0)| 00:00:01 |       |       |
|* 16 |       INDEX UNIQUE SCAN                   | PK_SBICUSERGROUP  |     2 |    24 |     0   (0)| 00:00:01 |       |       |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=5000)
   4 - filter(ROWNUM<=5000)
   7 - access("X_INST0_UNIT_NAME"="UNIT")
  10 - access("SUSER"."USERNAME"="SIDE"."GETMYUSER"())
  11 - access("SUSER"."GROUPID"="SUNITUSERGROUP"."GROUPID")
       filter("SUSER"."GROUPID"="SUNITUSERGROUP"."GROUPID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  13 - filter("RMESG"."MESG_SENDER_X1"='SOGEFRPPXXX' AND "RMESG"."MESG_NATURE"='FINANCIAL_MSG' AND
              "RMESG"."MESG_FRMT_NAME"='Swift')
  14 - access("RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-31 10:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "RMESG"."MESG_TYPE"='950' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-31 13:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("RMESG"."MESG_TYPE"='950')
  15 - access("X_CATEGORY"="CATEGORY" AND "SUSER"."GROUPID"="SMSGUSERGROUP"."GROUPID")
  16 - access("X_OWN_LT"="BICCODE" AND "SUSER"."GROUPID"="SBICUSERGROUP"."GROUPID")

40 rows selected.
Second query
 INSERT INTO TempSearchResult (t_aid,
                              t_umidl,
                              t_umidh,
                              X_CREA_DATE_TIME_MESG)
   SELECT z.aid,
          z.mesg_s_umidl,
          z.mesg_s_umidh,
          z.mesg_crea_date_time
     FROM (  SELECT  m.aid,
                    m.mesg_s_umidl,
                    m.mesg_s_umidh,
                    m.mesg_crea_date_time
               FROM RSMESG_ESIDE m
              WHERE 1 = 1
                    AND m.mesg_crea_date_time BETWEEN TO_DATE (
                                                         '20120117 10:00:00',
                                                         'YYYYMMDD HH24:MI:SS')
                                                  AND TO_DATE (
                                                         '20120117 13:00:00',
                                                         'YYYYMMDD HH24:MI:SS')
                    AND m.mesg_frmt_name = 'Swift'
                    AND m.mesg_sender_x1 = 'SOGEFRPPGSS'
                    AND m.mesg_nature = 'FINANCIAL_MSG'
                    AND m.mesg_type LIKE '548'
           ORDER BY mesg_crea_date_time) z
    WHERE ROWNUM <= 5000
Explain Plan For Second Query :
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4106071428

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                           |                   |  1073 | 51504 |       |  2622   (1)| 00:00:32 |       |       |
|   1 |  LOAD TABLE CONVENTIONAL                   | TEMPSEARCHRESULT  |       |       |       |            |          |       |       |
|*  2 |   COUNT STOPKEY                            |                   |       |       |       |            |          |       |       |
|   3 |    VIEW                                    |                   |  1073 | 51504 |       |  2622   (1)| 00:00:32 |       |       |
|*  4 |     SORT ORDER BY STOPKEY                  |                   |  1073 |   124K|       |  2622   (1)| 00:00:32 |       |       |
|   5 |      NESTED LOOPS                          |                   |  1073 |   124K|       |  2621   (1)| 00:00:32 |       |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       NESTED LOOPS                         |                   |   531 | 56817 |       |  2621   (1)| 00:00:32 |       |       |
|   7 |        NESTED LOOPS                        |                   |   531 | 54162 |       |  2621   (1)| 00:00:32 |       |       |
|   8 |         NESTED LOOPS                       |                   |   543 | 49413 |       |  2621   (1)| 00:00:32 |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID       | SUSER             |     1 |    10 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |           INDEX UNIQUE SCAN                | IX_SUSER          |     1 |       |       |     0   (0)| 00:00:01 |       |       |
|  11 |          PARTITION RANGE SINGLE            |                   |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
|* 12 |           TABLE ACCESS BY LOCAL INDEX ROWID| RMESG             |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
|  13 |            BITMAP CONVERSION TO ROWIDS     |                   |       |       |       |            |          |       |       |
|  14 |             BITMAP AND                     |                   |       |       |       |            |          |       |       |
|  15 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
|* 16 |               INDEX RANGE SCAN             | IX_SENDER         | 25070 |       |       |   894   (1)| 00:00:11 |     2 |     2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  17 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
|  18 |               SORT ORDER BY                |                   |       |       |   408K|            |          |       |       |
|* 19 |                INDEX RANGE SCAN            | IX_RMESG          | 25070 |       |       |  1405   (1)| 00:00:17 |     2 |     2 |
|* 20 |         INDEX UNIQUE SCAN                  | PK_SUNITUSERGROUP |     1 |    11 |       |     0   (0)| 00:00:01 |       |       |
|* 21 |        INDEX UNIQUE SCAN                   | PK_SMSGUSERGROUP  |     1 |     5 |       |     0   (0)| 00:00:01 |       |       |
|* 22 |       INDEX UNIQUE SCAN                    | PK_SBICUSERGROUP  |     2 |    24 |       |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - filter(ROWNUM<=5000)
   4 - filter(ROWNUM<=5000)
  10 - access("SUSER"."USERNAME"="SIDE"."GETMYUSER"())
  12 - filter("RMESG"."MESG_NATURE"='FINANCIAL_MSG' AND "RMESG"."MESG_FRMT_NAME"='Swift')
  16 - access("RMESG"."MESG_SENDER_X1"='SOGEFRPPGSS')
  19 - access("RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-17 10:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "RMESG"."MESG_TYPE"='548' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-17 13:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("RMESG"."MESG_TYPE"='548' AND "RMESG"."MESG_CREA_DATE_TIME"<=TO_DATE(' 2012-01-17 13:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "RMESG"."MESG_CREA_DATE_TIME">=TO_DATE(' 2012-01-17 10:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  20 - access("X_INST0_UNIT_NAME"="UNIT" AND "SUSER"."GROUPID"="SUNITUSERGROUP"."GROUPID")
  21 - access("X_CATEGORY"="CATEGORY" AND "SUSER"."GROUPID"="SMSGUSERGROUP"."GROUPID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  22 - access("X_OWN_LT"="BICCODE" AND "SUSER"."GROUPID"="SBICUSERGROUP"."GROUPID")

45 rows selected.
Table Structure TEMPSEARCHRESULT
CREATE GLOBAL TEMPORARY TABLE TEMPSEARCHRESULT
(
  T_AID                  NUMBER(3),
  T_UMIDL                NUMBER(10),
  T_UMIDH                NUMBER(10),
  X_CREA_DATE_TIME_MESG  DATE
)
ON COMMIT PRESERVE ROWS
NOCACHE;


CREATE INDEX SIDE.TEMP_SEARCH_INDEX ON SIDE.TEMPSEARCHRESULT
(T_AID, T_UMIDL, T_UMIDH, X_CREA_DATE_TIME_MESG);
  • 1. Re: Differenet Explain Plan for Same Query
    Fran Guru
    Currently Being Moderated
    did you gather new statistics between?
    both querys are executed in same database?
  • 2. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Fran wrote:
    did you gather new statistics between?
    Yes .
    both querys are executed in same database?
    Yes
  • 3. Re: Differenet Explain Plan for Same Query
    Fran Guru
    Currently Being Moderated
    When you gather statistics, the next time you run the query, the optimizer automatically chooses a new execution plan based on the new statistics. That's why you are getting a new execution plan.
  • 4. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Fran wrote:
    When you gather statistics, the next time you run the query, the optimizer automatically chooses a new execution plan based on the new statistics. That's why you are getting a new execution plan.
    I did Gather Statistics before 1 days ago , Never been made today .
  • 5. Re: Differenet Explain Plan for Same Query
    Dom Brooks Guru
    Currently Being Moderated
    I have two Queries ( The Difference between Them Only 940 and 584 ) When I Generate Explain Plan Different Output Why ?
    But it's not the only difference.
    See the date literal.

    You have two queries that are expected to return a different number of rows.
    It's no surprise at all that these should return different execution plans.

    Rowsource selectivities and cardinalities are a key part of the optimizer calculations and any difference in parameters can make one access path or join mechanism appear more favourable.

    In general, if you want reusable, shareable SQL you use bind variables (and I will conveniently ignore bind variable peeking).
    If you want specific execution plans for the specifically supplied values, use literals.

    By the way, a LIKE without a wildcard is an equality operator (=).
  • 6. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Thank you Dom for Clarify.


    But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?

    Another Question Dom , How Should make My Order For indexes , I search On google I found it all Depend on your query But what if you have more than One Query .
  • 7. Re: Differenet Explain Plan for Same Query
    Dom Brooks Guru
    Currently Being Moderated
    The numbers you refer to here are estimates.
    What are at least equally important are the actual numbers.

    The thread [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a SQL Tuning request explains how/what to investigate.

    The critical point is that the optimizer tends to make good decisions provided that the estimates are broadly accurate.


    The main features of note in your two query plans are the skip scan in the first versus the btree bitmap conversion in the second.
    Both are noted for being particularly ineffective if the estimates are not accurate.


    Will the order by affect query time?
    It may.
    An order by may require an additional sort operation. It may not.
    It may be executed optimally, it may not.
    Depends.

    For indexes it's a balance.
    If you have more than one query - the norm - you have to find the minimal set of indexes that provide the best/required query performance levels with an accepted overhead of storing and maintaining those indexes.
  • 8. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Again Thank you For your amazing Answer.
    For indexes it's a balance.
    Check this query which is Simple
    Select * from RMESG
    I generated Explain Plan for it to see effect of indexes .
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 1686435785
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |       |    11M|  8920M|   376K  (1)| 01:15:20 |      |        |
    |   1 |  PARTITION RANGE ALL|       |    11M|  8920M|   376K  (1)| 01:15:20 |    1 |     12 |
    |   2 |   TABLE ACCESS FULL | RMESG |    11M|  8920M|   376K  (1)| 01:15:20 |    1 |     12 |
    ---------------------------------------------------------------------------------------------
    1:15:20 For table access and Full Scan Also , I generate new Indexes on the table like the following
    CREATE TABLE RMESG(
            aid NUMBER(3) NOT NULL,
            mesg_s_umidl NUMBER(10) NOT NULL,
            mesg_s_umidh NUMBER(10) NOT NULL,
            mesg_validation_requested CHAR(18) NOT NULL,
            mesg_validation_passed CHAR(18) NOT NULL,
            mesg_class CHAR(16) NOT NULL,
            mesg_is_text_readonly NUMBER(1) NOT NULL,
            mesg_is_delete_inhibited NUMBER(1) NOT NULL,
            mesg_is_text_modified NUMBER(1) NOT NULL,
            mesg_is_partial NUMBER(1) NOT NULL,
            mesg_crea_mpfn_name CHAR(24) NOT NULL,
            mesg_crea_rp_name CHAR(24) NOT NULL,
            mesg_crea_oper_nickname CHAR(151) NOT NULL,
            mesg_crea_date_time DATE NOT NULL,
            mesg_mod_oper_nickname CHAR(151) NOT NULL,
            mesg_mod_date_time DATE NOT NULL,
            mesg_frmt_name VARCHAR2(17) NOT NULL,
            mesg_nature CHAR(14) NOT NULL,
            mesg_sender_x1 CHAR(11) NOT NULL,
            mesg_sender_corr_type VARCHAR2(24) NOT NULL,
            mesg_uumid VARCHAR2(50) NOT NULL,
            mesg_uumid_suffix NUMBER(10) NOT NULL,
            x_own_lt CHAR(8) NOT NULL,
            x_inst0_unit_name VARCHAR2(32) default 'NONE' NOT NULL,
            x_category CHAR(1) NOT NULL,
            archived NUMBER(1) NOT NULL,
            restored NUMBER(1) NOT NULL,
            mesg_related_s_umid CHAR(16) NULL,
            mesg_status CHAR(12) NULL,
            mesg_crea_appl_serv_name CHAR(24) NULL,
            mesg_verf_oper_nickname CHAR(151) NULL,
            mesg_data_last NUMBER(10) NULL,
            mesg_token NUMBER(10) NULL,
            mesg_batch_reference VARCHAR2(46) NULL,
            mesg_cas_sender_reference VARCHAR2(40) NULL,
            mesg_cas_target_rp_name VARCHAR2(20) NULL,
            mesg_ccy_amount VARCHAR2(501) NULL,
            mesg_copy_service_id VARCHAR2(4) NULL,
            mesg_data_keyword1 VARCHAR2(80) NULL,
            mesg_data_keyword2 VARCHAR2(80) NULL,
            mesg_data_keyword3 VARCHAR2(80) NULL,
            mesg_delv_overdue_warn_req NUMBER(1) NULL,
            mesg_fin_ccy_amount VARCHAR2(24) NULL,
            mesg_fin_value_date CHAR(6) NULL,
            mesg_is_live NUMBER(1) NULL,
            mesg_is_retrieved NUMBER(1) NULL,
            mesg_mesg_user_group VARCHAR2(24) NULL,
            mesg_network_appl_ind CHAR(3) NULL,
            mesg_network_delv_notif_req NUMBER(1) NULL,
            mesg_network_obso_period NUMBER(10) NULL,
            mesg_network_priority CHAR(12) NULL,
            mesg_possible_dup_creation VARCHAR2(8) NULL,
            mesg_receiver_alia_name VARCHAR2(32) NULL,
            mesg_receiver_swift_address CHAR(12) NULL,
            mesg_recovery_accept_info VARCHAR2(80) NULL,
            mesg_rel_trn_ref VARCHAR2(80) NULL,
            mesg_release_info VARCHAR2(32) NULL,
            mesg_security_iapp_name VARCHAR2(80) NULL,
            mesg_security_required NUMBER(1) NULL,
            mesg_sender_x2 VARCHAR2(21) NULL,
            mesg_sender_x3 VARCHAR2(21) NULL,
            mesg_sender_x4 VARCHAR2(21) NULL,
            mesg_sender_branch_info VARCHAR2(71) NULL,
            mesg_sender_city_name VARCHAR2(36) NULL,
            mesg_sender_ctry_code VARCHAR2(3) NULL,
            mesg_sender_ctry_name VARCHAR2(71) NULL,
            mesg_sender_institution_name VARCHAR2(106) NULL,
            mesg_sender_location VARCHAR2(106) NULL,
            mesg_sender_swift_address CHAR(12) NULL,
            mesg_sub_format VARCHAR2(6) NULL,
            mesg_syntax_table_ver VARCHAR2(8) NULL,
            mesg_template_name VARCHAR2(32) NULL,
            mesg_trn_ref VARCHAR2(16) NULL,
            mesg_type CHAR(3) NULL,
            mesg_user_issued_as_pde NUMBER(1) NULL,
            mesg_user_priority_code CHAR(4) NULL,
            mesg_user_reference_text VARCHAR2(30) NULL,
            mesg_zz41_is_possible_dup NUMBER(1) NULL,
            x_fin_ccy CHAR(3) NULL,
            x_fin_amount NUMBER(21,4) NULL,
            x_fin_value_date DATE NULL,
            x_fin_ocmt_ccy CHAR(3) NULL,
            x_fin_ocmt_amount NUMBER(21,4) NULL,
            x_receiver_x1 CHAR(11) NULL,
            x_receiver_x2 VARCHAR2(21) NULL,
            x_receiver_x3 VARCHAR2(21) NULL,
            x_receiver_x4 VARCHAR2(21) NULL,
            last_update DATE NULL,
            set_id NUMBER(10) NULL,
            mesg_requestor_dn VARCHAR2(101) NULL,
            mesg_service VARCHAR2(31) NULL,
            mesg_request_type VARCHAR2(31) NULL,
            mesg_identifier VARCHAR2(31) NULL,
            mesg_xml_query_ref1 VARCHAR2(101) NULL,
            mesg_xml_query_ref2 VARCHAR2(101) NULL,
            mesg_xml_query_ref3 VARCHAR2(101) NULL,
            mesg_appl_sender_reference VARCHAR2(51) NULL,
            mesg_payload_type VARCHAR2(31) NULL,
            mesg_sign_digest_reference VARCHAR2(41) NULL,
            mesg_sign_digest_value VARCHAR2(51) NULL,
            mesg_use_pki_signature NUMBER(1) NULL
    )
    PARTITION BY RANGE(MESG_CREA_DATE_TIME) (
        PARTITION SIDE_MIN VALUES LESS THAN (TO_DATE(20000101, 'YYYYMMDD')) TABLESPACE TBS_SIDEDB_DA_01);
    
         
    CREATE UNIQUE INDEX SIDE.IX_PK_RMESG on SIDE.RMESG (AID, MESG_S_UMIDH, MESG_S_UMIDL, MESG_CREA_DATE_TIME) LOCAL;
    ALTER TABLE SIDE.RMESG ADD CONSTRAINT IX_PK_RMESG PRIMARY KEY (AID, MESG_S_UMIDH, MESG_S_UMIDL, MESG_CREA_DATE_TIME) USING INDEX SIDE.IX_PK_RMESG;
    CREATE INDEX SIDE.ix_rmesg_cassender ON SIDE.rmesg (MESG_CAS_SENDER_REFERENCE) LOCAL;
    CREATE INDEX SIDE.ix_rmesg_creationdate ON SIDE.rmesg (MESG_CREA_DATE_TIME) LOCAL;
    CREATE INDEX SIDE.ix_rmesg_trnref ON SIDE.rmesg (MESG_TRN_REF) LOCAL;
    CREATE INDEX SIDE.ix_rmesg_uumid ON SIDE.rmesg (MESG_UUMID, MESG_UUMID_SUFFIX) LOCAL;
    CREATE INDEX SIDE.IX_UNIT_NAME_RMESG on RMESG(mesg_crea_date_time,X_INST0_UNIT_NAME) LOCAL;
    CREATE INDEX SIDE.IX_RMESG on RMESG(mesg_crea_date_time ,mesg_type,x_fin_ccy) LOCAL;
    CREATE INDEX SIDE.IX_NAME_FORMAT_TYPE_RMESG on RMESG(mesg_frmt_name,mesg_sub_format,mesg_type,mesg_crea_date_time ) LOCAL;
    same Explain Plan Same Result .
    I always remember TOM Quote "full scans are not evil, indexes are not good"

    Which Mean Something Wrong Goes with Indexes , the partition depend on MESG_CREA_DATE_TIME Column I create Index for this column but same explain plan Appear every time. With Same Time.


    Thank you
    Osama
  • 9. Re: Differenet Explain Plan for Same Query
    sb92075 Guru
    Currently Being Moderated
    WHERE clause needs to include indexed column in order for index to be used.
  • 10. Re: Differenet Explain Plan for Same Query
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Osama_mustafa wrote:

    But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?
    The effect on CPU time is minimal - the total elapsed time predicted changes from 3 seconds to 32 seconds, the percentage of the cost due to CPU changes from 3% to 1%, the latter being the smallest non-zero value available for the percentage. Since predicted time is simply a scaled representation of predicted cost, the expected change in CPU is tiny, most of the change is due to predicted I/O.

    Apart from checking the system stats to see if they are realistic (or just the defaults), I would look at the date and type columns, checking low and high values, number of distinct values, and whether either column had a histogram - and I'm guessing that histograms are having an effect here. (Check the partition stats, not the table stats).

    The change in plan is the same basic reason that usually appears - different input values result in different cardinality estimates. The use of a skip scan is interesting given the access and filter predicates - I think it might be telling us something about the number of distinct values for crea_date across the ranges requested.

    Regards
    Jonathan Lewis
  • 11. Re: Differenet Explain Plan for Same Query
    rp0428 Guru
    Currently Being Moderated
    >
    But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?
    >
    Dom was trying to be kind and point out that your query predicates (note the plural) are different. You said
    >
    The Difference between Them Only 940 and 584
    >
    1. There is no '940' or '584' in the code you posted.
    2. The dates in the where clause are different
    3. the m.mesg_sender_x1 value is different

    The first query has
    AND m.mesg_sender_x1 = 'SOGEFRPPXXX'
    but the second query has
    AND m.mesg_sender_x1 = 'SOGEFRPPGSS'
    It is hard to keep things straight when the text of the question doesn't match the code or plan that was posted.

    Both the different date ranges and the different sender values could grossly affect the plan used.

    What question are you really trying to address? And why aren't you using bind variables?
  • 12. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    But Should This Effect On CPU time , Since in the first Query Only 3 Second , And Second One 32 Big Difference of time . and the order of condition in where Should effect On Query Time ?
    >
    Dom was trying to be kind and point out that your query predicates (note the plural) are different. You said
    >
    The Difference between Them Only 940 and 584
    >
    1. There is no '940' or '584' in the code you posted.
    2. The dates in the where clause are different
    3. the m.mesg_sender_x1 value is different

    The first query has
    AND m.mesg_sender_x1 = 'SOGEFRPPXXX'
    but the second query has
    AND m.mesg_sender_x1 = 'SOGEFRPPGSS'
    It is hard to keep things straight when the text of the question doesn't match the code or plan that was posted.

    Both the different date ranges and the different sender values could grossly affect the plan used.

    What question are you really trying to address? And why aren't you using bind variables?
    Thank you for your reply.
    What question are you really trying to address? And why aren't you using bind variables?
    I understand Dom , But My question is in index IX_RMESG For the first explain plan its used "INDEX SKIP SCAN" But in the second one "INDEX RANGE SCAN"
    i will check with developers about that actually.
  • 13. Re: Differenet Explain Plan for Same Query
    rp0428 Guru
    Currently Being Moderated
    >
    But My question is in index IX_RMESG For the first explain plan its used "INDEX SKIP SCAN" But in the second one "INDEX RANGE SCAN"
    >
    I still think you are comparing apples to oranges due to the different filter predicates.

    You mention a range scan for the second query but in the context of the IX_RMESG index.

    To me the key difference in the second query is
    |  14 |             BITMAP AND
    It is that 'BITMAP AND' that is driving the second query. And it seems to me that the reason for that is this
    |* 16 |               INDEX RANGE SCAN             | IX_SENDER         | 25070 |       |       |   894   (1)| 00:00:11 |     2 |     2 |
    That is the INDEX RANGE SCAN that caught my eye and it appears to be for the SENDER predicate: m.mesg_sender_x1 = 'SOGEFRPPGSS'

    Referring back to what I said in my first reply
    >
    3. the m.mesg_sender_x1 value is different

    The first query has
    AND m.mesg_sender_x1 = 'SOGEFRPPXXX'
    but the second query has
    AND m.mesg_sender_x1 = 'SOGEFRPPGSS'
    The question I would explore is why the value 'SOGEFRPPGSS' uses the index but the value 'SOGEFRPPXXX' does not.

    I think the answer to that would answer your question about why the plans are different.

    It is the use of the IX_SENDER index that is causing the use of a range scan for the IX_REMSG index. Oracle may be doing the range scans of both indexes because that allows the indexes to be bitmap converted and 'ANDed' together to get the result.
    |  11 |          PARTITION RANGE SINGLE            |                   |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
    |* 12 |           TABLE ACCESS BY LOCAL INDEX ROWID| RMESG             |   543 | 43983 |       |  2621   (1)| 00:00:32 |     2 |     2 |
    |  13 |            BITMAP CONVERSION TO ROWIDS     |                   |       |       |       |            |          |       |       |
    |  14 |             BITMAP AND                     |                   |       |       |       |            |          |       |       |
    |  15 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
    |* 16 |               INDEX RANGE SCAN             | IX_SENDER         | 25070 |       |       |   894   (1)| 00:00:11 |     2 |     2 |
    |  17 |              BITMAP CONVERSION FROM ROWIDS |                   |       |       |       |            |          |       |       |
    |  18 |               SORT ORDER BY                |                   |       |       |   408K|            |          |       |       |
    |* 19 |                INDEX RANGE SCAN            | IX_RMESG          | 25070 |       |       |  1405   (1)| 00:00:17 |     2 |     2 |
  • 14. Re: Differenet Explain Plan for Same Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Thank you All .
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points