This discussion is archived
5 Replies Latest reply: Mar 5, 2013 6:02 AM by Jonathan Lewis RSS

analyze table & dbms_stats (chained rows)

Chewy Newbie
Currently Being Moderated
Hi Guys,

Understand that dbms_stats doesn't gather statistics about (chained rows).
To get chained rows, we may use one of the below:
1. analyze table <tab_name> list chained rows;
2. analyze table <tab_name> compute statistics;

the first will list out the chained rows information to the CHAINED_ROWS table.
the 2nd is better in such a way that the information of chain_cnt is populated into dba_tables. i can easily get the percentage of chain count by diviiding it with the num_rows (eg if there is a need to list out tables with > 10 percent chain cnt it would be easy).

Right now i'm leaving the need to gather stats to the default nightly oracle job (that will gather for table with stale or > 10 percent change).. however it won't gather for chain cnts.
Can advise how can I get the chain cnt without risking my existing statistis?

And also will analyze table <tab_name> estimate statistics; overwrite my existing histograms?

thanks

Edited by: Chewy on Mar 4, 2013 2:35 AM
  • 1. Re: analyze table & dbms_stats (chained rows)
    Mihael Pro
    Currently Being Moderated
    Can advise how can I get the chain cnt without risking my existing statistis?
    analyze table <TABLE_NAME> list chained rows into <CHAINED_ROWS_TABLE>;

    select owner_name, table_name,count(*) from <CHAINED_ROWS_TABLE> group by owner_name, table_name;

    CHAINED_ROWS_TABLE should be created with UTLCHAIN.SQL script
  • 2. Re: analyze table & dbms_stats (chained rows)
    mtefft Journeyer
    Currently Being Moderated
    Can advise how can I get the chain cnt without risking my existing statistis?
    Use the Segment Advisor: http://docs.oracle.com/cd/E11882_01/server.112/e25494/schema003.htm#sthref2054
  • 3. Re: analyze table & dbms_stats (chained rows)
    Jan-Marten Spit Explorer
    Currently Being Moderated
    "Can advise how can I get the chain cnt without risking my existing statistis?"

    see the 'table fetch continued row' system or session statistic. by evaluating that first, you migth avoid looking for something that is not there.

    alternatively, you might snap this value with a explicit FTS (that selects all columns).
  • 4. Re: analyze table & dbms_stats (chained rows)
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You should not need to gather chained rows statistics daily. So you could have special "one-off" ANALYZE TABLE runs once a month or once a quarter for only those tables where you suspect or know row chaining is occurring and is impacting performance. Before you run the ANALYZE you can backup the table statistics using DBMS_STATS.EXPORT_TABLE_STATS and then restore the statistics later (after you've generated your own report on Row Chaining) using DBMS_STATS.IMPORT_TABLE_STATS.


    Hemant K Chitale
  • 5. Re: analyze table & dbms_stats (chained rows)
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Chewy wrote:

    Can advise how can I get the chain cnt without risking my existing statistis?

    And also will analyze table <tab_name> estimate statistics; overwrite my existing histograms?
    It would be useful to know why you want this information, and whether you are aware of the three ways in which chained rows may get reported (and it varies with versions of Oracle). You have row migration - which is an update anomaly that can usually be addressed, "proper" row chaining when the content a row is too long to fit into a single block, and "intra-block" row chaining when the the number of columns in a row exceeds 255 (and a column past 255 has been populated).

    If you're just keen to keep an eye on possible threats then Mihael's suggestion is probably the most sensible thing you can do.

    Using the estimate statistics option to populate the chain_cnt would overwrite your histograms - more signiflcantly, even in the absence of histograms it could affect execution plans because the optimizer will factor the chain_cnt into the cost calculations if it has been set. (Update: I forgot I'd done a little demo in this blog item: http://jonathanlewis.wordpress.com/2009/04/30/analyze-this/ )

    Regards
    Jonathan Lewis

    Edited by: Jonathan Lewis on Mar 5, 2013 2:01 PM

Legend

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