oracle dba  - table analyze %

Posted on 2005-05-03
Medium Priority
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

Assisted Solution

kripa_odba earned 100 total points
ID: 13917030
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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 13917396
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 32

Accepted Solution

awking00 earned 50 total points
ID: 13918068

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month17 days, 3 hours left to enroll

862 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