Thursday, October 7, 2010

ANALYZE & compute statistics


%Execute(/) ANALYZE TABLE PS_CLASS_TBL compute statistics/ ANALYZE TABLE PS_CLASS_ATTRIBUTE compute statistics/ ANALYZE TABLE PS_CLASS_MTG_PAT compute statistics/ ANALYZE TABLE PS_CLASS_CHRSTC compute statistics/ ANALYZE TABLE PS_CLASS_COMPONENT compute statistics/ ANALYZE TABLE PS_CLASS_ASSOC compute statistics/ ANALYZE TABLE PS_CRSE_OFFER compute statistics/ ANALYZE TABLE PS_CU_CLAS_NBR_STG compute statistics/

I didnt get it first when I saw the above SQL. It had a clue that, its something realted to the performance tuning. Given a search, got the below info from the Stanford site.

ANALYZE
Purpose
--Use the ANALYZE statement to collect statistics, for example, to:
--Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
--Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

--Identify migrated and chained rows of a table or cluster.

More indepth info on the same.

Lokesh Madnavat

2 comments:

  1. You would probably noticed the warning from the doc linked :
    "Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package"
    ANALYZE should be used only to validate the table/index structure in case of corruption.

    Nicolas.

    ReplyDelete
  2. Merci d'avoir un blog interessant

    ReplyDelete

Total Pageviews

Copyright © Tech & Management Notes