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

From: Martin Klier <usn_at_usn-it.de>
Date: Tue, 11 Sep 2012 09:35:59 +0200
Message-ID: <504EE9DF.4050609_at_usn-it.de>



Hi Christopher,

it's the same as for an unpartitioned table: E-Rows is the CBO estimate for the line's operation. A-Rows is the real access number. Starts is the factor, you have to multiplicate E-Rows with it.

If you have a vast discrepancy between E-Rows*Starts vs. A-ROws, then it's likely to have optimizer statistics that don't represent your data good enough for this query's cardinality.

Regards
Martin

Christopher.Taylor2_at_parallon.net schrieb:
> 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
>
>
>

-- 
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 11 2012 - 02:35:59 CDT

Original text of this message