This discussion is archived
9 Replies Latest reply: Oct 29, 2010 5:22 AM by CharlesHooper RSS

SQL Tuning questions

santi Newbie
Currently Being Moderated
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
    704135 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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
    704135 Expert
    Currently Being Moderated
    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
    704135 Expert
    Currently Being Moderated
    And so so sorry for my English,
  • 6. Re: SQL Tuning questions
    santi Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    704135 Expert
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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.

Legend

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