Quote Originally Posted by hrishy
Hi

Can you please collect stats on all the tables using dbms_stats and then post the plan here without any changes to the sql and without any hints.

regards
Hrishy

Hrishy,

I gathered stats on the schema, and ran the simplified version of the query without any hints. This is still using 90% CPU. Here are the details from the trace file.

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.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

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

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 127  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0   NESTED LOOPS  
  38620    NESTED LOOPS  
  38620     NESTED LOOPS  
  49034      NESTED LOOPS  
   2870       NESTED LOOPS  
  61129        NESTED LOOPS  
  61129         TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_RESULT 
  61318          INDEX RANGE SCAN IDX_ASMNTRES_COMPDATE (object id 54920)
  61129         TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_INSTANCE 
  61129          INDEX UNIQUE SCAN PK_ASSESSMENT_INSTANCE (object id 54912)
   2870        TABLE ACCESS BY INDEX ROWID CLG_USER 
  61129         INDEX UNIQUE SCAN PK_USER (object id 55127)
  49034       TABLE ACCESS BY INDEX ROWID CLG_ITEM_RESULT 
  49034        INDEX RANGE SCAN PK_ITEM_RESULT (object id 55033)
  38620      TABLE ACCESS BY INDEX ROWID CLG_ITEM 
  49034       INDEX UNIQUE SCAN PK_ITEM (object id 55017)
  38620     INDEX RANGE SCAN IDX_CORR_ASSET_CONTENT_MLO (object id 54995)
      0    TABLE ACCESS BY INDEX ROWID CB_STANDARD 
  38620     INDEX UNIQUE SCAN PK_CB_STANDARD (object id 54866)

********************************************************************************