Different query COST depending on data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Different query COST depending on data

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

    Different query COST depending on data

    I have 2 different DBs but with the same data strucutures and indexes, but different set of data (one is empty and the other has some test data in it) - my explain plan for the same query between the 2 Databases is different and so is the Cost of the plan. Why is it so ?

    This DB has some data :
    Code:
     
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	6  	 	      	             	 
      NESTED LOOPS		1  	57  	6  	 	      	             	 
        NESTED LOOPS		1  	51  	5  	 	      	             	 
          NESTED LOOPS		1  	43  	4  	 	      	             	 
            TABLE ACCESS FULL	CLG_CLASS_USER_MAP	1  	13  	3  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CLG_USER	1  	30  	1  	 	      	             	 
              INDEX UNIQUE SCAN	PK_USER	1  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_GRADE_USER_MAP	882  	6 K	1  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_GRADE	1  	6  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PK_GRADE	1
    This DB is empty :
    Code:
     
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	2  	 	      	             	 
      NESTED LOOPS		1  	113  	2  	 	      	             	 
        NESTED LOOPS		1  	107  	1  	 	      	             	 
          MERGE JOIN CARTESIAN		1  	78  	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	CLG_CLASS_USER_MAP	1  	52  	1  	 	      	             	 
              INDEX SKIP SCAN	PK_CLASS_TEACHER_MAP	1  	 	 	 	      	             	 
            BUFFER SORT		1  	26  	 	 	      	             	 
              INDEX FULL SCAN	PK_GRADE_USER_MAP	1  	26  	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CLG_USER	1  	29  	 	 	      	             	 
            INDEX UNIQUE SCAN	PK_USER	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CLG_GRADE	13  	78  	1  	 	      	             	 
          INDEX UNIQUE SCAN	PK_GRADE	1
    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Are you joking?

    Oh well . . . the CBO takes account of the statistics on the tables (from running ANALYZE) to see how much "work" has to be done => COST. Big tables cost more!

    For example a Cartesian Join can v.efficient for tiny (empty) tables but not for tables with 100'000 rows. Full Table Scan ditto.

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks. So one cannot rightly tune a query during development other than follow the 'good practices' unless there is simulated production kind of data....

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    or you can bring your production stats to development to simulate the execution plan IMHO however that is not realistic

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Well the system is not into Production yet...Thanks for the help.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    It is possible to import the statistics from (say) production to development. But if the tables are small, you may get acceptable performance no matter which plan is chosen.

    The simplest and cheapest in person-time is to create development as a clone of production. If your db is v.big then you may have to expend a lot of person-time in creating a sufficiently big sub-set of the data (e.g. selecting 200'000 rows from a 10'000'000 row table, over and over - while keeping the db self-consistant!).

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