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.