This discussion is archived
5 Replies Latest reply: Mar 8, 2013 3:32 PM by wzhang RSS

Estimated size of tables for DMU scan

bencol Pro
Currently Being Moderated
The DMU estimates the size of tables, and uses this to determine how to parallelise the scans. I have a problem where the estimates are low, meaing that big tables (by bytes in dba_segments) are not parallelised and the scan takes longer than I think is necessary.

Some of the table have inaccurate statistics, but I can determine any correlation between anything in dba_tables (blocks?) and the size shown in the DMU.

I would like to know if I could help this my setting something using DBMS_STATS.SET_TABLE_STATS, or anyother method.

Thanks,

Ben
  • 1. Re: Estimated size of tables for DMU scan
    damorgan Oracle ACE Director
    Currently Being Moderated
    No version number, no DML statement, no data ... no help is possible at this time.

    What is the basis of your belief system for the following statements?
    1. "The DMU estimates the size of tables, and uses this to determine how to parallelise the scans"
    2. " I have a problem where the estimates are low"
    3. "the scan takes longer than I think is necessary."
    4. "Some of the table have inaccurate statistics"

    And you want this for an unstated version of the Oracle database?

    Without a basis for providing specific advice I would be unwilling to provide any.

    And, in case you think I am being argumentative, I am not, try this query of the Oracle docs:
    http://www.oracle.com/pls/db112/search?remark=quick_search&word=%22DMU+estimates%22
  • 2. Re: Estimated size of tables for DMU scan
    bencol Pro
    Currently Being Moderated
    Dan,

    Apologies for the lack of information, was posting in a hurry at the end of a long day.
    Oracle version 11.2.0.2, dmu version 1.1, running on windows.

    When the dmu does a scan it lists the tables on descending order of size, and the bigger tables are split into more chunks than the smaller. There are tables in my schema that are several Gb in dba_segments that are showing as 0 bytes in the size column. Many of these tables have not had statistics gathered on them (they are mainly backup tables created before upgrades) so I linked this to the seemingly inaccurate size estimates.

    I did try a docs search, but have had trouble today, both from my work pc and now on my phone at home ( connecting in the UK). The link below says " Your search term "DMU estimates" did not match any topics. ", but ill try again tomorrow.

    Ben
  • 3. Re: Estimated size of tables for DMU scan
    damorgan Oracle ACE Director
    Currently Being Moderated
    That was the point ... the search returns nothing. So I am asking you, perhaps I should have been clearer, what you read (post the link) what you are running (be specific) and what you are seeing.

    When someone says my stats are accurate I can not know whether they are seeing a 0.001% discrepancy or a 67.8% discrepancy and you posted nothing I, or anyone else, could use to have any sense of what you are doing and what you are seeing.

    Based on experience I am skeptical you have anything here other than "compulsive tuning disorder" so you need to explain why that isn't an appropriate response to your request for help.
  • 4. Re: Estimated size of tables for DMU scan
    bencol Pro
    Currently Being Moderated
    Apologies again for not being clear I thought DMU was a well know abbreviation for Database Migration Assistant for Unicode and would be known on this forum. I've been so involved with it I may have been unclear with my terms.

    I am running the Scan Wizard in the Database Migration Assistant for Unicode v1.1 (DMU). After I've completed the Object Selection, the Scan Details http://docs.oracle.com/cd/E26101_01/doc/doc.11/e26097/ch4scenarios.htm#BACEFAIG shows a list of table with a table size. The DMU then decides to split the scan of each table, based on some function of the table size. I would like to know where this Table Size is calculated from. It is not from dba_segments, as I have a table that is 7GB in DBA segments, that is showing as 0B in the Table Size column. The option to split can be deselected where the DMU has chosen to split the scan into chunks, but not selected where it has not.

    If the Table Size is calculated from the table statistics then I would like to know if it is possible to set this via dbms_stats.set_table_stats, to improve the performance of the scans.

    Ben
  • 5. Re: Estimated size of tables for DMU scan
    wzhang Newbie
    Currently Being Moderated
    Ben, please log a SR. We would need to collect more diagnostics about the table in question in order to provide a recommendation.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points