Optimizer behaviour
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Optimizer behaviour

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

    Optimizer behaviour

    I need the help of the gurus out here in understanding how this execution plan is changing :
    For the following query :
    Code:
    SELECT ais.standard_type_id AS STD_TYPE, 
           ais.ss_id AS STD_ID, 
           std.title AS TITLE, 
           std.description AS DESCRIPTION
      FROM mv_assessment_hierarchy mv, 
           clg_assessment_result ar, 
           clg_item_result ir,
           as_item_standard ais, 
           CLG_STANDARD std
      WHERE mv.ASSESSMENT_LOCATOR = 'M125Xassess-1::AS' and
    	to_char(ar.completed_date,'YYYY-MM-DD hh24:mi:ss') =  '2004-04-30 13:09:59'  and
    	mv.ASSESSMENT_LOCATOR = ar.ASSESSMENT_LOCATOR and
    	ar.status = 20 and
    	ar.taker_id = 59 and
    	ir.ASSESSMENT_RESULT_ID = ar.ASSESSMENT_RESULT_ID and
    	mv.item_locator = ir.ITEM_LOCATOR and	
    	ir.item_locator = ais.item_locator AND
    	ais.ss_id = std.ss_id
    this is the explain plan :
    Code:
     
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	9  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MV_ASSESSMENT_HIERARCHY	28  	868  	3  	 	      	             	 
        NESTED LOOPS		1  	224  	9  	 	      	             	 
          NESTED LOOPS		1  	193  	6  	 	      	             	 
            NESTED LOOPS		1  	77  	5  	 	      	             	 
              NESTED LOOPS		1  	56  	4  	 	      	             	 
                TABLE ACCESS FULL	CLG_ASSESSMENT_RESULT	1  	35  	2  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CLG_ITEM_RESULT	70  	1 K	2  	 	      	             	 
                  INDEX RANGE SCAN	PK_ITEM_RESULT	49  	 	1  	 	      	             	 
              INDEX RANGE SCAN	AS_ITEM_STANDARD_AK1	20  	420  	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CLG_STANDARD	1  	116  	1  	 	      	             	 
              INDEX UNIQUE SCAN	PK_STANDARD	1  	 	 	 	      	             	 
          INDEX RANGE SCAN	MV_ASS_LOCATOR	28  	 	1
    When I change the query to take away my check on ar.completed_date as below :
    Code:
    SELECT  ais.standard_type_id AS STD_TYPE, 
            ais.ss_id AS STD_ID, 
            std.title AS TITLE, 
    	std.description AS DESCRIPTION
      FROM mv_assessment_hierarchy mv, 
           clg_assessment_result ar, 
           clg_item_result ir,
           as_item_standard ais, 
           CLG_STANDARD std
      WHERE mv.ASSESSMENT_LOCATOR = 'M125Xassess-1::AS' and
    	mv.ASSESSMENT_LOCATOR = ar.ASSESSMENT_LOCATOR and
    	ar.status = 20 and
    	ar.taker_id = 59 and
    	ir.ASSESSMENT_RESULT_ID = ar.ASSESSMENT_RESULT_ID and
    	mv.item_locator = ir.ITEM_LOCATOR and	
    	ir.item_locator = ais.item_locator AND
    	ais.ss_id = std.ss_id
    here is the explain plan :
    Code:
     	 
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		9  	 	19  	 	      	             	 
      HASH JOIN		9  	1 K	19  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	MV_ASSESSMENT_HIERARCHY	28  	868  	3  	 	      	             	 
          INDEX RANGE SCAN	MV_ASS_LOCATOR	28  	 	1  	 	      	             	 
        HASH JOIN		76  	14 K	15  	 	      	             	 
          HASH JOIN		76  	5 K	7  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CLG_ITEM_RESULT	70  	1 K	2  	 	      	             	 
              NESTED LOOPS		25  	1 K	4  	 	      	             	 
                TABLE ACCESS FULL	CLG_ASSESSMENT_RESULT	1  	31  	2  	 	      	             	 
                INDEX RANGE SCAN	PK_ITEM_RESULT	49  	 	1  	 	      	             	 
            INDEX FAST FULL SCAN	AS_ITEM_STANDARD_AK1	979  	20 K	2  	 	      	             	 
          TABLE ACCESS FULL	CLG_STANDARD	48  	5 K	7
    How does the usage of the column COMPLETED_DATE of table CLG_ASSESSMENT_RESULT affect the usage of the PrimaryKey on CLG_STANDARD ? There is no relation or no constraint between them whatsoever...
    Thanks.
    Last edited by rshivagami; 05-27-2004 at 05:24 PM.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Question

    Your problem seems to be the date conversion in:
    Code:
    to_char(ar.completed_date,'YYYY-MM-DD hh24:mi:ss') =  '2004-04-30 13:09:59'
    Try reversing this to:
    Code:
    ar.completed_date = TO_DATE('2004-04-30 13:09:59','YYYY-MM-DD hh24:mi:ss')
    Good luck!

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Optimizer behaviour

    Originally posted by rshivagami
    How does the usage of the column COMPLETED_DATE of table CLG_ASSESSMENT_RESULT affect the usage of the PrimaryKey on CLG_STANDARD ? There is no relation or no constraint between them whatsoever
    There is ofcourse a relation, not direct though, that what number of rows (to be specific blocks) will be filtered with and without COMPLETED_DATE & that inturn affecting the amount to joining required..

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

  4. #4
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    This is kind of hard to understand. My explain plan is not using the index though I am explicitly doing the join using my PK only... why - because some other indirectly related filter is not present - I still do not get it...
    Thanks.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Choose the driving table correctly.

    Which table among mv_assessment_hierarchy mv,
    clg_assessment_result ar will produce less number of rows after filter?

    And verify indexes are created on the joined columns.

    Tamil

  6. #6
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    I changed the FROM clause and now have the tables in the decreasing order of the number of rows they return, still no difference.
    I have indexes on all joined columns on mv_assessment_hierarchy, but not on clg_assessment_result as it is an OLTP table.

    Shiva.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you use ordered hint?

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