oracle dba  - table analyze %

Posted on 2005-05-03
Last Modified: 2012-05-05
Hi ,
I have oracle 9.2 database. it's datawarehousing environment which has 500gb database size. i have some very large tables which has 30 - 40 columns with lil of records. we do have some indexes on this table. indexes will be dropped an drecreated during loading process.

My question is ;
1)Currently we are doing table analysis with estimate of 1200 rows or some table with 2% . I am new to this company. Could you please tell me what percentage is really good for large table of 1 mil records . does it nanalyze index also or only table. should i use stat pack or analyze table statment.

Please give step by step process.

2) Do we need all tables with primery key? They do have some unique indexes on table but not primery key again this is datawarehouse environment. if primery key is not available then what impact will be on table?

Thanks a lot in advance.
Question by:dcmumbai
    LVL 4

    Assisted Solution

    which one you are using for analyzing??? ?


    I am also working in a same kind of environment....

    Oracle will take a default sample size of the first 1064 rows default... But default value will not be good always... But if you give anlayze command with estimate statistics with 50% then it is as good as analyze table compute statistics....

    There is no hard and fast rules how much percentage you need to give...... It all depends on the dataload you exepect......

    In our environment we give 10%.

    If you are using DBMS_STATS package there is one method for collecting statistics.....

    DBMS_STATS.Gather_SCHEMA_STATS('shemaname',estimate percentage value')

    there is one oracle defined parameter for estimate percentage....

    DBMS_STATS.AUTO_SAMPLE_SIZE  you can use , Oracle determine the best sample size for good statistics.

    LVL 34

    Assisted Solution

    by:Mark Geerlings
    1. "Should I use stat pack or analyze table statment?"  Use the procedures in the dbms_stats package.  The "analyze table" command is older, and is still supported for backwards compatibility, but all new databases should use dbms_stats.

    2. "Do we need all tables with primary key?"  No.  There is usually no performance difference between a primary key and a unique key.  The difference is, a unique key allows nulls, a primary key does not.  So, if your data has nulls, and if the application is searching for records with nulls, those searches will be slower since nulls are not indexed.
    LVL 31

    Accepted Solution


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    Via a live example, show how to take different types of Oracle backups using RMAN.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now