dbms_xplan.display_cursor STARTS, E-ROWS, A-ROWS question on partitioned table with gather_plan_statistics hint

  • From: <Christopher.Taylor2@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 10 Sep 2012 10:51:59 -0500

Is there any special consideration when looking at Starts,E-Rows and A-Rows for 
Partitioned Tables when using dbms_xplan?
In the example below, there is an organization table that returns 17 
organizations, for each organization, access this partitioned table.

This partitioned table has 0 sub partitions so it is only partitioned on 
organization ids - no subsequent subpartitions [yet].

Is Oracle telling me that it actually received 405K rows but only expected 731 
rows (17 starts * 43 rows)?

Could this be indicative of gathering statistics incorrectly for partitioned 
tables?  I saw that David Kurtz had a presentation on gathering statistics for 
partitioned tables but I think his example went into subpartitions.

What considerations should I be considering here (other than just flat out 
re-gathering statistics)?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                   
        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | 
A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
...
...
|  33 |                         PARTITION LIST ITERATOR|                        
        |     17 |     43 |  4257 |   128   (2)| 00:00:02 |   KEY |   KEY |    
405K|00:00:02.85 |
|* 34 |                          TABLE ACCESS FULL     | MON_ACCOUNT            
        |     17 |     43 |  4257 |   128   (2)| 00:00:02 |   KEY |   KEY |    
405K|00:00:02.44 |
...
...

(Again this goes back to my partitioning newbieness)


Chris


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


Other related posts: