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

From: <Christopher.Taylor2_at_parallon.net>
Date: Mon, 10 Sep 2012 10:51:59 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851576282C_at_NADCWPMSGCMS10.hca.corpad.net>

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

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

Received on Mon Sep 10 2012 - 10:51:59 CDT