Tune SQL

**abhaysk** · 12-22-2006 06:48 AM

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.

**rshivagami** · 12-26-2006 01:13 PM

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

**tamilselvan** · 12-26-2006 06:17 PM

I still believe "MERGE JOIN CARTESIAN 1 62 " is causing for slow poor performance. Try HASH Join.

Tamil

**abhaysk** · 12-27-2006 02:18 AM

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) */

**rshivagami** · 12-27-2006 10:27 AM

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.

**tamilselvan** · 12-27-2006 07:21 PM

Oracle and merge join do not work well.

Tamil

**abhaysk** · 12-28-2006 01:04 AM

merge cartesian doesnt work with any flavour.. unless one of the tables joined return a single row..

**simply_dba** · 12-28-2006 02:43 AM

Ah !! this was a very informative thrread.
Special thanks to abhaysk and Tamil

**rshivagami** · 01-12-2007 04:35 PM

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.

**tamilselvan** · 01-15-2007 07:33 AM

Is it because of BITAND operation?

Tamil

Thread: Tune SQL

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions