Tune SQL - Page 4
DBAsupport.com Forums - Powered by vBulletin
Page 4 of 4 FirstFirst ... 234
Results 31 to 35 of 35

Thread: Tune SQL

  1. #31
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Locator column is actually a text. Here are the Explain Plan data.

    Code:
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    | Id  | Operation                          |  Name                       | Rows	 | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT                   |                             |     1 |   205 |    40 |
    |   1 |  SORT AGGREGATE                    |                             |     1 |   205 |       |
    |   2 |   NESTED LOOPS                     |                             |     1 |   205 |    40 |
    |   3 |    NESTED LOOPS                    |                             |     3 |   543 |    37 |
    |   4 |     NESTED LOOPS                   |                             |     3 |   438 |    31 |
    |   5 |      NESTED LOOPS                  |                             |     3 |   237 |    25 |
    |   6 |       NESTED LOOPS                 |                             |     1 |    38 |    22 |
    |   7 |        NESTED LOOPS                |                             |     3 |    84 |    16 |
    |*  8 |         TABLE ACCESS BY INDEX ROWID| CLG_ASSESSMENT_RESULT       |     3 |    54 |    13 |
    |*  9 |          INDEX RANGE SCAN          | IDX_ASMNTRES_COMPDATE       |    38 |       |     3 |
    |  10 |         TABLE ACCESS BY INDEX ROWID| CLG_ASSESSMENT_INSTANCE     |     1 |    10 |     1 |
    |* 11 |          INDEX UNIQUE SCAN         | PK_ASSESSMENT_INSTANCE      |     1 |       |       |
    |* 12 |        TABLE ACCESS BY INDEX ROWID | CLG_USER                    |     1 |    10 |     2 |
    |* 13 |         INDEX UNIQUE SCAN          | PK_USER                     |     1 |       |     1 |
    |  14 |       TABLE ACCESS BY INDEX ROWID  | CLG_ITEM_RESULT             |    13 |   533 |     3 |
    |* 15 |        INDEX RANGE SCAN            | PK_ITEM_RESULT              |    13 |       |     2 |
    |* 16 |      TABLE ACCESS BY INDEX ROWID   | CLG_ITEM                    |     1 |    67 |     2 |
    |* 17 |       INDEX UNIQUE SCAN            | PK_ITEM                     |     1 |       |     1 |
    |* 18 |     INDEX RANGE SCAN               | IDX_CORR_ASSET_CONTENT_MLO  |     1 |    35 |     2 |
    |* 19 |    TABLE ACCESS BY INDEX ROWID     | CB_STANDARD                 |     1 |    24 |     1 |
    |* 20 |     INDEX UNIQUE SCAN              | PK_CB_STANDARD              |     1 |       |       |
    --------------------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       8 - filter("CAR"."STATUS"=20)
       9 - access("CAR"."COMPLETED_DATE">=TO_DATE('2006-09-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND  "CAR"."COMPLETED_DATE"<=TO_DATE('2006-10-13 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
      11 - access("CAR"."ASSESSMENT_INSTANCE_ID"="CAI"."ASSESSMENT_INSTANCE_ID")
      12 - filter("U"."ORG_UNIT_ID"=21296)
      13 - access("CAI"."ASSIGNOR_ID"="U"."USER_ID")
      15 - access("CAR"."ASSESSMENT_RESULT_ID"="CIR"."ASSESSMENT_RESULT_ID")
      16 - filter("CI"."ANCESTOR_LOCATOR" IS NOT NULL)
      17 - access("CIR"."ITEM_LOCATOR"="CI"."LOCATOR")
      18 - access("CORR"."ASSET_TYPE_ID"=113 AND "CORR"."CONTENT_ID"="CI"."ANCESTOR_LOCATOR")
      19 - filter(SUBSTR("CBS"."LOCATOR",1,6)='1.1026' AND BITAND("CBS"."INTERNAL_GRADE_LEVELS",2)>0)
      20 - access("CORR"."CBS_ID"="CBS"."CBS_ID")
    
    Note: cpu costing is off

  2. #32
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    One more thing BITAND operator would return integer only.
    See below:
    SQL> select bitand(89292.2929292928, 992.227277272) from dual;

    BITAND(89292.2929292928,992.227277272)
    --------------------------------------
    192

    But the LOCATOR is checked with 1.1026. Some logic problem?

    Tamil

  3. #33
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Tamil,

    1. We are doing a SUBSTR of Locator where Locator values are like '1', '1.1', '1.1.1', ....
    2. We are doing a BITAND for an integer column where for anything above 1st grade "BITAND (cbs.internal_grade_levels, 2) > 0" is true, for anything above 2nd grade,
    "BITAND(cbs.internal_grade_levels, 4) > 0" is true and so on..

  4. #34
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Ok. I got it.
    Code:
    Now look at the tkprof output:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4      0.01       0.01          0          0          0           0
    Execute      4      0.00       0.00          0          0          0           0
    Fetch        4      5.44       5.31          0    2585652          0           4
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      5.45       5.33          0    2585652          0           4
    The SQL was parsed 4 times and executed 4 times. The CPU time was 5.33. So the average CPU time was 1.33 seconds and it did 2.5 Miilion LIOs.

    Second, there is a huge difference between autotrace explain plan and tkprof output row source. Why? Either statistics are missing on tables and indexes or some init parameters are not set correctly. What's the value for optimizer_mode you set?

    As I said earlier, the least number of rows was returned by CB_STANDARD table. Make this as driving table.
    Use LEADING hint. Post the autotrace plan and tkprof output. Attach the entire trace file.

    Tamil

  5. #35
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by tamilselvan
    Ok. I got it.
    The SQL was parsed 4 times and executed 4 times. The CPU time was 5.33. So the average CPU time was 1.33 seconds and it did 2.5 Miilion LIOs.

    As I said earlier, the least number of rows was returned by CB_STANDARD table. Make this as driving table.
    Use LEADING hint. Post the autotrace plan and tkprof output. Attach the entire trace file.

    Tamil
    2 things..

    1) average cpu is 1.33 fare.. but average lio is not 2.5 million, but its 0.6~ million (though its too high a value).

    2) how will it help making CB_STANDARD as driving???

    PS below conditions...

    AND CORR.cbs_id = cbs.cbs_id
    AND SUBSTR(cbs.LOCATOR,1,6)= '1.1026'-- INPUT: subject Writing; grade 6

    Or even

    AND CORR.cbs_id = cbs.cbs_id
    AND cbs.LOCATOR like '1.1026%'-- INPUT: subject Writing; grade 6

    by making this driving, it will use locator as filter and we dont know how many rows it will return..

    "The row source shows that joining with CB_STANDARD returns 0 rows.
    Why don't you start this table, CB_STANDARD as a driving table. Use leading hint. "

    Well your suggestion is based CB_STANDARD returning 0 rows when joined.. yes but that because it first joins with "CORR.cbs_id = cbs.cbs_id" and then looks for locator like '1.1026%' which its not finding any.


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width