DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: Tune SQL

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Tune SQL

    Hi, Here is my SQL that is executing in 3.7mins and is using 100%CPU. Any suggestions to tune it ?
    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 OR 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
    
    Explain Plan
    ***********************
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	61  	 	      	             	 
      SORT AGGREGATE		1  	205  	 	 	      	             	 
        NESTED LOOPS		1  	205  	61  	 	      	             	 
          MERGE JOIN CARTESIAN		2  	340  	53  	 	      	             	 
            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_PROD.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                      INDEX RANGE SCAN	CSI_PROD.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI_PROD.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI_PROD.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CSI_PROD.CLG_USER	1  	10  	2  	 	      	             	 
                    INDEX UNIQUE SCAN	CSI_PROD.PK_USER	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI_PROD.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
                  INDEX RANGE SCAN	CSI_PROD.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CSI_PROD.CLG_ITEM	1  	67  	2  	 	      	             	 
                INDEX UNIQUE SCAN	CSI_PROD.PK_ITEM	1  	 	1  	 	      	             	 
            BUFFER SORT		1  	24  	51  	 	      	             	 
              TABLE ACCESS FULL	CSI_PROD.CB_STANDARD	1  	24  	8  	 	      	             	 
          INDEX RANGE SCAN	CSI_PROD.IDX_CORR_MLO_CONTENT_ASSET	1  	35  	4
    Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think " MERGE JOIN CARTESIAN" is causing problem.

    Change the driving table to CLG_USER.

    Select /*+ LEADING(U) */

    With the hint, you can trace sql with level 8.

    Tamil

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Making either CLG_USER or CLG_ASSESSMENT_RESULT as leading tables is increasing the execution time to 4.1mins.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You could try changing ...

    SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026'

    ... to ...

    cbs.LOCATOR like '1.1026%'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by rshivagami
    Making either CLG_USER or CLG_ASSESSMENT_RESULT as leading tables is increasing the execution time to 4.1mins.
    Do you see MERGE JOIN CARTESIAN operation in the plan?

    Otherwise, try another hint:
    USE_NL(U, cai, car, cir, ci, corr, cbs)

    Tamil

  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    The execution time is 4.1mins by replacing SUBSTR with LIKE. Here is the plan :

    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	39  	 	      	             	 
      SORT AGGREGATE		1  	205  	 	 	      	             	 
        NESTED LOOPS		1  	205  	39  	 	      	             	 
          MERGE JOIN CARTESIAN		1  	170  	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  	 	      	             	 
            BUFFER SORT		1  	24  	33  	 	      	             	 
              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	1  	35  	4
    The "merge cartesian join" is still there..

    Thanks.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What's the execution plan for USE_NL(U, cai, car, cir, ci, corr, cbs)?

    Tamil

  8. #8
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Quote Originally Posted by tamilselvan
    Do you see MERGE JOIN CARTESIAN operation in the plan?

    Otherwise, try another hint:
    USE_NL(U, cai, car, cir, ci, corr, cbs)

    Tamil

    Here is the expalin plan with the above hint.
    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	39  	 	      	             	 
      SORT AGGREGATE		1  	205  	 	 	      	             	 
        NESTED LOOPS		1  	205  	39  	 	      	             	 
          NESTED LOOPS		1  	170  	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  	 	      	             	 
            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	1  	35  	4
    The Merge Cartesian Join is gone but the execution time has increased to 4.4 mins.

    Thanks.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Do you have an index on ORG_UNIT_ID col?
    If not. create it and see the plan.

    Tamil

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Try with optimizer_mode=ALL_ROWS

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