SQL: different execution plans at different times

From: Nirmalya Das <nirmalya_at_hln.com>
Date: Wed, 1 Nov 2006 14:06:12 -0800
Message-ID: <20061101140612.sofqko0oook4gg8k@www.hln.com>

RDBMS : 10.1.0.4 on RHEL 4

Query:

SELECT COUNT (*) AS ROWCOUNT FROM contact contact WHERE (contact.status IN (:1) AND contact.visible = :2) AND contact.cny#(+)=:3

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=112 Card=1 Bytes=39)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=112 Card=5 Bytes=195)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CONTACTHEAD' (TABLE)
           (Cost=103 Card=5 Bytes=125)
   4    3         INDEX (RANGE SCAN) OF 'IX_CONTACTHEAD_DEPTKEY' (INDEX) (Cost=4
Card=459)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'CONTACTVERSION' (TABLE)
(Cost=2 Card=1 Bytes=14)
   6    5         INDEX (UNIQUE SCAN) OF 'PK_CONTACTVERSION' (INDEX (UNIQUE))

(Cost=1 Card=1)

BUT RIGHT AFTER THE NIGHTLY STATISTICS GETS COLECTED THE Execution Plan CHANGES:

Operation	                                                                      
                           Object	    Object Type	Order	Number of Rows       
KB	      Cost	Time (seconds)	CPU Cost	IO Cost	       Object Node
SELECT STATEMENT                                                                
                                                                5	              
       0          0	                 6662	                    0	             0	 
       0
   SORT AGGREGATE                                                               
                                                                4	              
       1	     0.023           	0	                    0	             0	         0
               HASH JOIN	                                                       
                                                                     3	         
     15900  372.656                6661	                  32	982247396	    6324
                   TABLE ACCESS FULL     ACCT_OWNER_01.CONTACTVERSION	          
                              1	                7964	  108.883               
6487	                  31	967478198	    6155
                    INDEX RANGE SCAN      ACCT_OWNER_01.IX_CONTACTHEAD_VRECKEY	 
               2	               36571	  357.139	       171	                   
1	   8463123	      169

Statistics collected by the following:
begin

   dbms_stats.gather_schema_stats (

          ownname=>'ACCT_OWNER_01',
          options=>'GATHER',estimate_percent=>dbms_stats.auto_sample_size,
          method_opt=>'for all columns size 1',
          degree=>15,
          cascade => TRUE);

end;

I found the hash_value of the SQL by doing:

select * from stats$sql_summary where snap_id = 2516 and TEXT_SUBSET like 'SELECT COUNT (*) AS ROWCOUNT FROM contact contact WHERE (contact.status IN (:1) AND contact.visible = :2) AND contact.cny#(+)=:3%';

I found the following by running @?/rdbms/admin/sprepsql.sql using the hash_value found above:

Known Optimizer Plan(s) for this Old Hash Value

---

Shows all known Optimizer Plans for this database instance, and the Snap Id's they were first found in the shared pool. A Plan Hash Value will appear multiple times if the cost has changed
-> ordered by Snap Id

  First        First          Plan
 Snap Id     Snap Time     Hash Value        Cost
--------- --------------- ------------ ----------
     2431 30 Oct 06 10:37    164717973       318
     2431 30 Oct 06 10:37   2249602920       113
     2500 31 Oct 06 23:33   1531340256      3006 (exact finish time of stats)
     2505 01 Nov 06 02:03   1182291339      8976
     2521 01 Nov 06 08:04    164717973       316 (after shared pool flush)
     2521 01 Nov 06 08:04   2249602920       126

Very much puzzled. Any insight will be appreciated of why the plan changes after statics gathering.

TIA Nirmalya

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 01 2006 - 16:06:12 CST