DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 35

Thread: Tune SQL

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    instead of OR clause.. split it as union or better use concat.. and also collect histograms on all tables and all indexed columns with appropriate bucket size of around 100.
    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"

  2. #12
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Tamil, There already is an index on u.org_unit_id column.

    Abhaysk, Using an UNION ALL Clause brought down the execution time to 37secs but the CPU usage is still 99%. Here is a part of the query in the UNION that is using the maximum resources.

    Code:
    SELECT COUNT (cai.assessment_instance_id) AS assignments_mapped
      FROM CLG_ASSESSMENT_RESULT car,
           CLG_ITEM_RESULT cir,
           CLG_ITEM ci,
           CLG_CORRELATION CORR,
           CLG_ASSESSMENT_INSTANCE cai,
           CB_STANDARD cbs,
           CLG_USER u
     WHERE car.assessment_result_id = cir.assessment_result_id
       AND car.status = 20
       AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
       AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
       AND car.assessment_instance_id = cai.assessment_instance_id
       AND cai.assignor_id = u.user_id
       AND u.org_unit_id = 21296
       AND cir.item_locator = ci.LOCATOR
       AND CORR.content_id = ci.LOCATOR
       AND CORR.asset_type_id = 113
       AND CORR.cbs_id = cbs.cbs_id
       AND cbs.LOCATOR LIKE '1.1026%'           -- INPUT: subject Writing; grade 6
       AND BITAND (cbs.internal_grade_levels, 2) > 0
    	      	             	 
    
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	30  	 	      	             	 
      SORT AGGREGATE		1  	163  	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	1  	41  	3  	 	      	             	 
          NESTED LOOPS		1  	163  	30  	 	      	             	 
            NESTED LOOPS		1  	122  	27  	 	      	             	 
              NESTED LOOPS		1  	97  	26  	 	      	             	 
                MERGE JOIN CARTESIAN		1  	62  	22  	 	      	             	 
                  NESTED LOOPS		1  	38  	20  	 	      	             	 
                    NESTED LOOPS		3  	84  	14  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                        INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                        INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
                  BUFFER SORT		1  	24  	20  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	2  	 	      	             	 
                      INDEX RANGE SCAN	CSI.UK_CB_STANDARD_LOCATOR	1  	 	1  	 	      	             	 
                INDEX RANGE SCAN	CSI.IDX_CORR_MLO_CONTENT_ASSET	28  	980  	4  	 	      	             	 
              INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	25  	1  	 	      	             	 
            INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I still believe "MERGE JOIN CARTESIAN 1 62 " is causing for slow poor performance. Try HASH Join.

    Tamil

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    its due to merge join.. try this hint if u have index on cbs.cbs_id and it sounds to me like its a unique key?..

    /*+ use_nl(cbs) index(cbs index_on_cbs_id) */
    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"

  5. #15
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Abhaysk, Adding /*+ index(cbs index_on_cbs_id) */ hint has solved the problem. It is now executing in 2 secs and here is the explain plan.

    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	33  	 	      	             	 
      SORT AGGREGATE		1  	163  	 	 	      	             	 
        NESTED LOOPS		1  	163  	33  	 	      	             	 
          NESTED LOOPS		1  	139  	32  	 	      	             	 
            NESTED LOOPS		3  	312  	26  	 	      	             	 
              NESTED LOOPS		3  	237  	23  	 	      	             	 
                NESTED LOOPS		1  	38  	20  	 	      	             	 
                  NESTED LOOPS		3  	84  	14  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                      INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                    INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
                  INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
              INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	25  	1  	 	      	             	 
            INDEX RANGE SCAN	CSI.IDX_CORR_ASSET_CONTENT_MLO	1  	35  	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	1  	 	      	             	 
            INDEX UNIQUE SCAN	CSI.PK_CB_STANDARD	1
    Tamil, you are right - eliminating "Merge Join Cartesian" solved the problem.


    Thanks everyone.

  6. #16
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Oracle and merge join do not work well.

    Tamil

  7. #17
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    merge cartesian doesnt work with any flavour.. unless one of the tables joined return a single row..

  8. #18
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Ah !! this was a very informative thrread.
    Special thanks to abhaysk and Tamil
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  9. #19
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    The execution time is now 2 secs but CPU usage is almost 100%. Here is the updated Query, Explain Plan, Trace Stats and Wait events. Any idea why ?

    Code:
    SELECT   (SELECT /*+ index(cbs pk_cb_standard) */
                     COUNT (cai.assessment_instance_id)
                                        AS assignments_mapped
                FROM CLG_ASSESSMENT_RESULT car,
                     CLG_ITEM_RESULT cir,
                     CLG_ITEM ci,
                     CLG_CORRELATION CORR,
                     CLG_ASSESSMENT_INSTANCE cai,
                     CB_STANDARD cbs,
                     CLG_USER u
               WHERE car.assessment_result_id = cir.assessment_result_id
                 AND car.status = 20
                 AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
                 AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
                 AND car.assessment_instance_id = cai.assessment_instance_id
                 AND cai.assignor_id = u.user_id
                 AND u.org_unit_id = 21296
                 AND cir.item_locator = ci.LOCATOR
                 AND CORR.content_id = ci.LOCATOR
                 AND CORR.asset_type_id = 113
                 AND CORR.cbs_id = cbs.cbs_id
                 AND SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026'  -- INPUT: subject Writing; grade 6
                 AND BITAND (cbs.internal_grade_levels, 2) > 0)
           + (SELECT /*+ index(cbs pk_cb_standard) */
                     COUNT (cai.assessment_instance_id) AS assignments_mapped
                FROM CLG_ASSESSMENT_RESULT car,
                     CLG_ITEM_RESULT cir,
                     CLG_ITEM ci,
                     CLG_CORRELATION CORR,
                     CLG_ASSESSMENT_INSTANCE cai,
                     CB_STANDARD cbs,
                     CLG_USER u
               WHERE car.assessment_result_id = cir.assessment_result_id
                 AND car.status = 20
                 AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
                 AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
                 AND car.assessment_instance_id = cai.assessment_instance_id
                 AND cai.assignor_id = u.user_id
                 AND u.org_unit_id = 21296
                 AND cir.item_locator = ci.LOCATOR
                 AND CORR.content_id = ci.ancestor_locator
                 AND CORR.asset_type_id = 113
                 AND CORR.cbs_id = cbs.cbs_id
                 AND SUBSTR (cbs.LOCATOR, 1, 6) =  '1.1026'    -- INPUT: subject Writing; grade 6
                 AND BITAND (cbs.internal_grade_levels, 2) > 0)
                                                               assignments_mapped
      FROM DUAL;
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      6.10      28.79      32279     470910          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      6.11      28.79      32279     470910          0           1
    
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	2  	 	      	             	 
      SORT AGGREGATE		1  	163  	 	 	      	             	 
        NESTED LOOPS		1  	163  	33  	 	      	             	 
          NESTED LOOPS		1  	139  	32  	 	      	             	 
            NESTED LOOPS		3  	312  	26  	 	      	             	 
              NESTED LOOPS		3  	237  	23  	 	      	             	 
                NESTED LOOPS		1  	38  	20  	 	      	             	 
                  NESTED LOOPS		3  	84  	14  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                      INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                    INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
                  INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
              INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	25  	1  	 	      	             	 
            INDEX RANGE SCAN	CSI.IDX_CORR_ASSET_CONTENT_MLO	1  	35  	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	1  	 	      	             	 
            INDEX UNIQUE SCAN	CSI.PK_CB_STANDARD	1  	 	 	 	      	             	 
      SORT AGGREGATE		1  	205  	 	 	      	             	 
        NESTED LOOPS		1  	205  	38  	 	      	             	 
          NESTED LOOPS		3  	543  	35  	 	      	             	 
            NESTED LOOPS		3  	438  	29  	 	      	             	 
              NESTED LOOPS		3  	237  	23  	 	      	             	 
                NESTED LOOPS		1  	38  	20  	 	      	             	 
                  NESTED LOOPS		3  	84  	14  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                      INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                    INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
                  INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM	1  	67  	2  	 	      	             	 
                INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	 	1  	 	      	             	 
            INDEX RANGE SCAN	CSI.IDX_CORR_ASSET_CONTENT_MLO	1  	35  	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	1  	 	      	             	 
            INDEX UNIQUE SCAN	CSI.PK_CB_STANDARD	1  	 	 	 	      	             	 
      TABLE ACCESS FULL	SYS.DUAL	1  	 	2  	 	      	             	 
    
    
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net more data from client                   1        0.00          0.00
      SQL*Net message to client                       2        0.00          0.00
      SQL*Net message from client                     2        0.03          0.04
      db file scattered read                       2715        0.01          0.63
      db file sequential read                      1839        0.00          0.32
      direct path write                            2295        0.00          0.00
      direct path read                             2295        0.00          0.00
    The output is returning only 0 rows.

  10. #20
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Is it because of BITAND operation?

    Tamil

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