9 Replies Latest reply: Oct 29, 2010 7:22 AM by Charles Hooper RSS

    SQL Tuning questions

    santi
      Hi,

      I feel, Hash join in following is causing performance problem. Please have a look and suggest me whether my interpretation is right or wrong:
      SELECT COUNT(*),d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE as AuthorizeDate,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL as AuthorizationLevel 
      FROM CHQPRINT.T_DATA_MASTER_FIELD_DETAILS d,CHQPRINT.T_DATA_CORP_AUTHORIZATION r,CHQPRINT.t_data_file_details m,CHQPRINT.T_DATA_RECORD_DETAILS N 
      WHERE d.file_id=r.FILE_ID 
      and d.RECORD_REFERENCE_NO=r.RECORD_REFERENCE_NO 
      and r.file_id=m.file_id AND N.FILE_ID=R.FILE_ID 
      AND N.RECORD_REFERENCE_NO=R.RECORD_REFERENCE_NO 
      and d.file_id=m.file_id 
      and N.CORPORATE_AUTHORIZATION_DONE='Y' 
      AND TO_DATE(m.FILE_UPLOADED_ON) between ('01-OCT-2010') and ('28-OCT-2010') AND(N.PRINTING_STATUS<>'C'  OR N.PRINTING_STATUS IS NULL)
      GROUP BY d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL
      
      
      Plan hash value: 904523798
      
      --------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |                           |     1 |    80 |       |  5464   (1)| 00:01:06 |
      |   1 |  HASH GROUP BY                 |                           |     1 |    80 |       |  5464   (1)| 00:01:06 |
      |   2 |   NESTED LOOPS                 |                           |     1 |    80 |       |  5463   (1)| 00:01:06 |
      |*  3 |    HASH JOIN                   |                           |    36 |  2340 |  6376K|  5401   (1)| 00:01:05 |
      |   4 |     TABLE ACCESS BY INDEX ROWID| T_DATA_CORP_AUTHORIZATION |   408 |  9792 |       |    75   (0)| 00:00:01 |
      |   5 |      NESTED LOOPS              |                           |   116K|  5003K|       |  2535   (1)| 00:00:31 |
      |*  6 |       TABLE ACCESS FULL        | T_DATA_FILE_DETAILS       |   286 |  5720 |       |   202   (4)| 00:00:03 |
      |*  7 |       INDEX RANGE SCAN         | PK_DATA_CORPAUTHORIZATION |   408 |       |       |     6   (0)| 00:00:01 |
      |   8 |     INDEX FAST FULL SCAN       | IDX_FILE_REF_PLOC         |   911K|    18M|       |  1120   (1)| 00:00:14 |
      |*  9 |    TABLE ACCESS BY INDEX ROWID | T_DATA_RECORD_DETAILS     |     1 |    15 |       |     2   (0)| 00:00:01 |
      |* 10 |     INDEX UNIQUE SCAN          | PK_DATA_RECORD_DETAILS    |     1 |       |       |     1   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND
                    "D"."FILE_ID"="M"."FILE_ID")
         6 - filter(TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))>=TO_DATE('2010-10-01 00:00:00',
                    'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))<=TO_DATE('2010-10-28
                    00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
         7 - access("R"."FILE_ID"="M"."FILE_ID")
         9 - filter(("N"."PRINTING_STATUS" IS NULL OR "N"."PRINTING_STATUS"<>'C') AND
                    "N"."CORPORATE_AUTHORIZATION_DONE"='Y')
        10 - access("N"."FILE_ID"="R"."FILE_ID" AND "N"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO")
      
      Elapsed: 00:00:08.49
      
      Statistics
      ----------------------------------------------------------
               22  recursive calls
                0  db block gets
          1149987  consistent gets
              383  physical reads
             1560  redo size
            14528  bytes sent via SQL*Net to client
              679  bytes received via SQL*Net from client
               19  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
              268  rows processed
      Database version: 10.2.0.3

      Regards,
        • 1. Re: SQL Tuning questions
          Dbb
          1º 7 - access("R"."FILE_ID"="M"."FILE_ID") INDEX RANGE SCAN
          can you rewrite the index or create a new index with the requeriments of the query ?

          2º 6 - filter(TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))>=TO_DATE('2010-10-01 00:00:00',
          'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))<=TO_DATE('2010-10-28
          00:00:00', 'yyyy-mm-dd hh24:mi:ss')) TABLE ACCESS FULL
          can you rewrite the query? have you considered to create a function base index?

          3º 3 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND
          "D"."FILE_ID"="M"."FILE_ID") INDEX FAST FULL SCAN
          can you rewrite the index or create a new index with the requeriments of the query ?
          • 2. Re: SQL Tuning questions
            santi
            1º 7 - access("R"."FILE_ID"="M"."FILE_ID") INDEX RANGE SCAN 
            can you rewrite the index or create a new index with the requeriments of the query ?
            That is a primary key
            2º 6 - filter(TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))>=TO_DATE('2010-10-01 00:00:00',
            'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(INTERNAL_FUNCTION("M"."FILE_UPLOADED_ON"))<=TO_DATE('2010-10-28
            00:00:00', 'yyyy-mm-dd hh24:mi:ss')) TABLE ACCESS FULL
            can you rewrite the query? have you considered to create a function base index?
            The field "FILE_UPLOADED_ON" itself has a datatype 'DATE'. An index is already there on the column. But since the table size is 7mb only, therefore it's ignoring index.
            3º 3 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND
            "D"."FILE_ID"="M"."FILE_ID") INDEX FAST FULL SCAN 
            can you rewrite the index or create a new index with the requeriments of the query ? 
            Please clarify this. I have already a composite index on the three columns.
             
            SQL> select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where index_name='IDX_FILE_REF_PLOC';
            
            INDEX_OWNER                    TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION
            ------------------------------ ------------------------------ ------------------------------ ---------------
            CHQPRINT                       T_DATA_MASTER_FIELD_DETAILS    PRINT_LOCATION                               3
            CHQPRINT                       T_DATA_MASTER_FIELD_DETAILS    RECORD_REFERENCE_NO                          2
            CHQPRINT                       T_DATA_MASTER_FIELD_DETAILS    FILE_ID                                      1
            regards
            • 3. Re: SQL Tuning questions
              Charles Hooper
              If I were to take a guess from the projected cardinality numbers in the plan that you posted, plan ID line 5 is actually not a nested loops join, but instead a Cartesian join (row source 1 is expected to provide 286 rows, row source 2 is expected to provide 408 rows, and the nested loops join produces 286 * 408 = 116K rows). If the cardinality estimates are incorrect, which is very likely in part due to the implicit datatype conversions, the resulting number of rows could be much larger than the projected 116,000 rows.

              You should always specify the format of a date, rather than requiring Oracle to implicitly guess the format. Rather than:
              TO_DATE(m.FILE_UPLOADED_ON) between ('01-OCT-2010') and ('28-OCT-2010') 
              You should use something like this:
              TO_DATE(m.FILE_UPLOADED_ON, 'DD-MON-YYYY') between TO_DATE('01-OCT-2010', 'DD-MON-YYYY') and TO_DATE('28-OCT-2010', 'DD-MON-YYYY') 
              Even with the above fix in place, the optimizer's guess regarding the number of matching rows for the above could still be far from correct. Why is the m.FILE_UPLOADED_ON column not defined as a DATE column?

              Use the following to help determine if the optimizer's cardinality estimates are significantly different from the actual number of rows returned at each stage of the execution plan:
              SET AUTOTRACE OFF
              SET TIMING OFF
              SET LINESIZE 150
              SET PAGESIZE 1000
              SET TRIMSPOOL ON
              
              SELECT /*+ GATHER_PLAN_STATISTICS */
                COUNT(*),
                d.file_id,
                d.PRINT_LOCATION,
                m.corporate_id,
                m.file_uploaded_on,
                r.PROCESS_DATE as AuthorizeDate,
                r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL as AuthorizationLevel 
              FROM
                CHQPRINT.T_DATA_MASTER_FIELD_DETAILS d,
                CHQPRINT.T_DATA_CORP_AUTHORIZATION r,
                CHQPRINT.t_data_file_details m,
                CHQPRINT.T_DATA_RECORD_DETAILS N 
              WHERE
                d.file_id=r.FILE_ID 
                and d.RECORD_REFERENCE_NO=r.RECORD_REFERENCE_NO 
                and r.file_id=m.file_id
                AND N.FILE_ID=R.FILE_ID 
                AND N.RECORD_REFERENCE_NO=R.RECORD_REFERENCE_NO 
                and d.file_id=m.file_id 
                and N.CORPORATE_AUTHORIZATION_DONE='Y' 
                AND TO_DATE(m.FILE_UPLOADED_ON) between ('01-OCT-2010') and ('28-OCT-2010') 
                AND (N.PRINTING_STATUS != 'C'  OR N.PRINTING_STATUS IS NULL)
              GROUP BY
                d.file_id,
                d.PRINT_LOCATION,
                m.corporate_id,
                m.file_uploaded_on,
                r.PROCESS_DATE,
                r.AUTHORIZATION_STATUS,
                r.AUTHORIZATION_LEVEL;
              
              SELECT
                *
              FROM
                TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
              -----------------
              Edit:
              It appears that the OP has stated in a follow up that m.FILE_UPLOADED_ON is a DATE column. Therefore, the following:
              TO_DATE(m.FILE_UPLOADED_ON) between ('01-OCT-2010') and ('28-OCT-2010') 
              You should be changed to this:
              m.FILE_UPLOADED_ON between TO_DATE('01-OCT-2010', 'DD-MON-YYYY') and TO_DATE('28-OCT-2010', 'DD-MON-YYYY') 
              -----------------

              Charles Hooper
              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.

              Edited by: Charles Hooper on Oct 29, 2010 6:39 AM
              Saw the OP's follow up post stating that m.FILE_UPLOADED_ON is a DATE column.
              • 4. Re: SQL Tuning questions
                Dbb
                1º Ok. Cost 6...

                2º TO_DATE(M.FILE_UPLOADED_ON) break any index on it. Use M.FILE_UPLOADED_ON between to_date() and to_date()

                3º INDEX FAST FULL SCAN runs through all index, rewrite the index using the cardinality of the columns.
                • 5. Re: SQL Tuning questions
                  Dbb
                  And so so sorry for my English,
                  • 6. Re: SQL Tuning questions
                    santi
                    Thanks Charles,

                    Please see the output:
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------------------------------------------------------------
                    SQL_ID  cyrhfqb4x2f5w, child number 0
                    -------------------------------------
                    SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*),d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE as
                    AuthorizeDate,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL as AuthorizationLevel FROM CHQPRINT.T_DATA_MASTER_FIELD_DETAILS
                    d,CHQPRINT.T_DATA_CORP_AUTHORIZATION r,CHQPRINT.t_data_file_details m,CHQPRINT.T_DATA_RECORD_DETAILS N WHERE d.file_id=r.FILE_ID and
                    d.RECORD_REFERENCE_NO=r.RECORD_REFERENCE_NO and r.file_id=m.file_id AND N.FILE_ID=R.FILE_ID AND
                    N.RECORD_REFERENCE_NO=R.RECORD_REFERENCE_NO and d.file_id=m.file_id and N.CORPORATE_AUTHORIZATION_DONE='Y' AND m.FILE_UPLOADED_ON between
                    '01-OCT-2010' and '28-OCT-2010' AND(N.PRINTING_STATUS<>'C'  OR N.PRINTING_STATUS IS NULL) GROUP BY
                    d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL
                    
                    Plan hash value: 615652328
                    
                    --------------------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation              | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
                    --------------------------------------------------------------------------------------------------------------------------------------------------
                    |   1 |  SORT GROUP BY         |                           |      1 |      7 |     29 |00:00:01.17 |   27386 |  10156 |  9216 |  9216 | 8192  (0)|
                    |*  2 |   HASH JOIN            |                           |      1 |      7 |     35 |00:00:01.08 |   27386 |  10156 |   845K|   845K| 1056K (0)|
                    |*  3 |    HASH JOIN           |                           |      1 |  21044 |     35 |00:00:00.29 |   23284 |  10156 |  1011K|  1011K| 1370K (0)|
                    |*  4 |     TABLE ACCESS FULL  | T_DATA_FILE_DETAILS       |      1 |   1900 |   3993 |00:00:00.01 |     883 |      0 |       |       |      |
                    |*  5 |     HASH JOIN          |                           |      1 |  39391 |  17113 |00:00:00.50 |   22401 |  10156 |  1376K|  1376K| 2003K (0)|
                    |*  6 |      TABLE ACCESS FULL | T_DATA_RECORD_DETAILS     |      1 |  25588 |  13151 |00:00:00.25 |    9092 |      0 |       |       |      |
                    |   7 |      TABLE ACCESS FULL | T_DATA_CORP_AUTHORIZATION |      1 |   1450K|   1452K|00:00:01.45 |   13309 |  10156 |       |       |      |
                    |   8 |    INDEX FAST FULL SCAN| IDX_FILE_REF_PLOC         |      1 |    911K|    933K|00:00:00.93 |    4102 |      0 |       |       |      |
                    --------------------------------------------------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       2 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND "D"."FILE_ID"="M"."FILE_ID")
                       3 - access("R"."FILE_ID"="M"."FILE_ID")
                       4 - filter(("M"."FILE_UPLOADED_ON">=TO_DATE('2010-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
                                  "M"."FILE_UPLOADED_ON"<=TO_DATE('2010-10-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
                       5 - access("N"."FILE_ID"="R"."FILE_ID" AND "N"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO")
                       6 - filter((("N"."PRINTING_STATUS" IS NULL OR "N"."PRINTING_STATUS"<>'C') AND "N"."CORPORATE_AUTHORIZATION_DONE"='Y'))
                    Regards

                    Edited by: santi on Oct 29, 2010 4:22 AM
                    Enter value for tab: T_DATA_FILE_DETAILS
                    Enter value for own: CHQPRINT
                    
                    COLUMN_NAME                    DATA_TYPE                      NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
                    ------------------------------ ------------------------------ ------------ ---------- ---------- ---------------
                    CORPORATE_ID                   VARCHAR2                                374          0 .002538071 HEIGHT BALANCED
                    FILE_DATE                      DATE                                   2908      72745 .000343879 NONE
                    FILE_ID                        NUMBER                               114228          0 8.7544E-06 NONE
                    FILE_NAME                      VARCHAR2                               5179     109308 .000193096 HEIGHT BALANCED
                    FILE_SEQ_NUMBER                VARCHAR2                              22175          0  .00026455 HEIGHT BALANCED
                    FILE_UPLOADED_BY               VARCHAR2                                459          0 .002747253 HEIGHT BALANCED
                    FILE_UPLOADED_ON               DATE                                 113288          0 8.8271E-06 NONE
                    FUND_AUTHORIZED_BY             VARCHAR2                                 16     113350      .0625 NONE
                    FUND_AUTHORIZED_ON             DATE                                    402     113350 .002487562 NONE
                    FUND_REJECTED_BY               VARCHAR2                                  5     114211         .2 NONE
                    FUND_REJECTED_ON               DATE                                     10     114211         .1 NONE
                    TOTAL_AMOUNT                   NUMBER                                96975          0 .000010312 NONE
                    TOTAL_RECORDS                  NUMBER                                 1202          0 .002192982 HEIGHT BALANCED
                    UPLOAD_USER_ACCESS             CHAR                                      2          0 4.4024E-06 FREQUENCY
                    
                    14 rows selected.
                    
                    SQL> /
                    Enter value for tab: T_DATA_RECORD_DETAILS
                    Enter value for own: CHQPRINT
                    
                    COLUMN_NAME                    DATA_TYPE                      NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
                    ------------------------------ ------------------------------ ------------ ---------- ---------- ---------------
                    AUTHORIZED_BY                  VARCHAR2                                  5     918900         .2 NONE
                    AUTHORIZED_ON                  DATE                                   1600     918900    .000625 NONE
                    CHECKER_AUTHORIZED_ON          DATE                                     27     918900 .037037037 NONE
                    CHECKER_REJECTED_ON            DATE                                    700     919800 .001428571 NONE
                    CHECKER_USER_ID                VARCHAR2                                  0     918200          0 NONE
                    CHQ_FORMAT_ID                  NUMBER                                   15          0 5.4318E-07 FREQUENCY
                    CORPORATE_AUTHORIZATION_DONE   CHAR                                      2          0 5.4318E-07 FREQUENCY
                    CORPORATE_REJECTION_DONE       CHAR                                      2          0 5.4318E-07 FREQUENCY
                    CORP_PROD_ID                   NUMBER                                  174          0 5.4318E-07 FREQUENCY
                    FILE_ID                        NUMBER                                 3153          0 .001038422 HEIGHT BALANCED
                    LATEST_PRINT_RECORD_ID         NUMBER                               896200      24300 1.1158E-06 NONE
                    PRINTING_STATUS                CHAR                                      4      24300 5.5791E-07 FREQUENCY
                    PRINTING_USER                  VARCHAR2                                200      27100 5.5966E-07 FREQUENCY
                    RECORD_REFERENCE_NO            NUMBER                               403175          0 2.4803E-06 NONE
                    REJECTED_BY                    VARCHAR2                                  0     920500          0 NONE
                    REJECTED_ON                    DATE                                      0     920500          0 NONE
                    
                    16 rows selected.
                    
                    SQL> /
                    Enter value for tab: T_DATA_CORP_AUTHORIZATION
                    Enter value for own: CHQPRINT
                    
                    COLUMN_NAME                    DATA_TYPE                      NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
                    ------------------------------ ------------------------------ ------------ ---------- ---------- ---------------
                    AUTHORIZATION_LEVEL            NUMBER                                    3          0 3.4480E-07 FREQUENCY
                    AUTHORIZATION_STATUS           CHAR                                      2          0 3.4480E-07 FREQUENCY
                    CHECKER_USER                   VARCHAR2                                145          0 3.4480E-07 FREQUENCY
                    CHECKER_USER_ACCESS            CHAR                                      2          0 3.4480E-07 FREQUENCY
                    CORPORATE_ID                   VARCHAR2                                122          0 3.4480E-07 FREQUENCY
                    FILE_ID                        NUMBER                                 3556          0 .001054852 HEIGHT BALANCED
                    GRADE_ID                       NUMBER                                   77     231200 4.1021E-07 FREQUENCY
                    PROCESS_DATE                   DATE                                 126209          0 7.9234E-06 NONE
                    RECORD_REFERENCE_NO            NUMBER                               484552          0 2.0638E-06 NONE
                    • 7. Re: SQL Tuning questions
                      santi
                      Hi Charles,

                      I have made modification in the query as per your suggestion. Following is the changed plan:
                      SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*),d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE as
                      AuthorizeDate,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL as AuthorizationLevel FROM CHQPRINT.T_DATA_MASTER_FIELD_DETAILS
                      d,CHQPRINT.T_DATA_CORP_AUTHORIZATION r,CHQPRINT.t_data_file_details m,CHQPRINT.T_DATA_RECORD_DETAILS N WHERE d.file_id=r.FILE_ID
                      and d.RECORD_REFERENCE_NO=r.RECORD_REFERENCE_NO and r.file_id=m.file_id AND N.FILE_ID=R.FILE_ID AND
                      N.RECORD_REFERENCE_NO=R.RECORD_REFERENCE_NO and d.file_id=m.file_id and N.CORPORATE_AUTHORIZATION_DONE='Y' AND
                      m.FILE_UPLOADED_ON between TO_DATE('01-OCT-2010', 'DD-MON-YYYY') and TO_DATE('28-OCT-2010', 'DD-MON-YYYY')
                      AND(N.PRINTING_STATUS<>'C'  OR N.PRINTING_STATUS IS NULL) GROUP BY
                      d.file_id,d.PRINT_LOCATION,m.corporate_id,m.file_uploaded_on,r.PROCESS_DATE,r.AUTHORIZATION_STATUS,r.AUTHORIZATION_LEVEL
                      
                      Plan hash value: 615652328
                      
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation              | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      |   1 |  SORT GROUP BY         |                           |      1 |      7 |     29 |00:00:00.89 |   27386 |  9216 |  9216 | 8192  (0)|
                      |*  2 |   HASH JOIN            |                           |      1 |      7 |     35 |00:00:00.83 |   27386 |   845K|   845K| 1068K (0)|
                      |*  3 |    HASH JOIN           |                           |      1 |  21044 |     35 |00:00:00.27 |   23284 |  1011K|  1011K| 1358K (0)|
                      |*  4 |     TABLE ACCESS FULL  | T_DATA_FILE_DETAILS       |      1 |   1900 |   3993 |00:00:00.01 |     883 |       |       |          |
                      |*  5 |     HASH JOIN          |                           |      1 |  39391 |  16389 |00:00:00.33 |   22401 |  1278K|  1278K| 1678K (0)|
                      |*  6 |      TABLE ACCESS FULL | T_DATA_RECORD_DETAILS     |      1 |  25588 |  12392 |00:00:00.32 |    9092 |       |       |          |
                      |   7 |      TABLE ACCESS FULL | T_DATA_CORP_AUTHORIZATION |      1 |   1450K|   1452K|00:00:01.45 |   13309 |       |       |          |
                      |   8 |    INDEX FAST FULL SCAN| IDX_FILE_REF_PLOC         |      1 |    911K|    933K|00:00:00.93 |    4102 |       |       |          |
                      -----------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access("D"."FILE_ID"="R"."FILE_ID" AND "D"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO" AND
                                    "D"."FILE_ID"="M"."FILE_ID")
                         3 - access("R"."FILE_ID"="M"."FILE_ID")
                         4 - filter(("M"."FILE_UPLOADED_ON">=TO_DATE('2010-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
                                    "M"."FILE_UPLOADED_ON"<=TO_DATE('2010-10-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
                         5 - access("N"."FILE_ID"="R"."FILE_ID" AND "N"."RECORD_REFERENCE_NO"="R"."RECORD_REFERENCE_NO")
                         6 - filter((("N"."PRINTING_STATUS" IS NULL OR "N"."PRINTING_STATUS"<>'C') AND "N"."CORPORATE_AUTHORIZATION_DONE"='Y'))
                      Although the cost reflected in the new plan is 2000 higher than the previous plan, but I see significant gain in consistent gets:
                      Elapsed: 00:00:00.64
                      
                      Statistics
                      ----------------------------------------------------------
                               22  recursive calls
                                0  db block gets
                            27392  consistent gets
                                0  physical reads
                                0  redo size
                             2441  bytes sent via SQL*Net to client
                              503  bytes received via SQL*Net from client
                                3  SQL*Net roundtrips to/from client
                                1  sorts (memory)
                                0  sorts (disk)
                               29  rows processed
                      Regards,
                      • 8. Re: SQL Tuning questions
                        Dbb
                        To avoid the TABLE ACCESS FULL of T_DATA_CORP_AUTHORIZATION, you can try some like

                        CREATE INDEX INTO T_DATA_CORP_AUTHORIZATION (RECORD_REFERENCE_NO, PROCESS_DATE, FILE_ID, CHECKER_USER, CORPORATE_ID)

                        Edited by: Agustin UN on 29-oct-2010 13:57
                        • 9. Re: SQL Tuning questions
                          Charles Hooper
                          santi wrote:
                          Hi Charles,

                          I have made modification in the query as per your suggestion. Following is the changed plan:
                          (snip)

                          Although the cost reflected in the new plan is 2000 higher than the previous plan, but I see significant gain in consistent gets:
                          Elapsed: 00:00:00.64
                          
                          Statistics
                          ----------------------------------------------------------
                          22  recursive calls
                          0  db block gets
                          27392  consistent gets
                          0  physical reads
                          0  redo size
                          2441  bytes sent via SQL*Net to client
                          503  bytes received via SQL*Net from client
                          3  SQL*Net roundtrips to/from client
                          1  sorts (memory)
                          0  sorts (disk)
                          29  rows processed
                          Regards,
                          What you have done is to give Oracle's optimizer much better estimates of the actual number of rows that will be returned by the operations in the execution plan by eliminating the TO_DATE function that wrapped the m.FILE_UPLOADED_ON, so the calculated costs are expected to increase when the cardinality estimates increases. Notice that now the T_DATA_FILE_DETAILS table is no longer expected to return 286 rows, but instead 1900 rows - with 3993 rows actually being returned. The cardinaliy estimate is still 2.1 times lower than actual, but that is better than being 13.96 times lower than actual with a cartesian join being passed off as a nested loops join. The T_DATA_CORP_AUTHORIZATION table is likely the greatest contributor to the execution time in the new execution plan (this may or may not be a performance problem). During the first execution it required 10,156 physical block reads (all of the physical block reads for that execution, verify that the DB_FILE_MULTIBLOCK_READ_COUNT parameter is unset in order to potentially help multiblock read performance in the future). During the second execution no physical reads were performed, so the execution time decreased slightly. 13,309 of the 27,392 consistent gets are a direct result of accessing this T_DATA_CORP_AUTHORIZATION table - you could see the number of consistent gets drop by using an index to access this table, but performance might be adversely affected if physical block reads are required.

                          There is a small chance that you could see slightly better performance by using more indexes and fewer full table scans, but I suspect that the performance might not improve much more. You could, of course, test by temporarily adding index hints to the SQL statement (where the GATHER_PLAN_STATISTICS hint is currently positioned) to see how the performance changes. Once you are satisfied with the performance of the SQL statement, be certain to remove the GATHER_PLAN_STATISTICS hint from the SQL statement.

                          Charles Hooper
                          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                          http://hoopercharles.wordpress.com/
                          IT Manager/Oracle DBA
                          K&M Machine-Fabricating, Inc.