Solved

How to use  DBMS_STATS

Posted on 2002-04-22
7
1,693 Views
Last Modified: 2012-06-21
We are planning to use dbms_stats package instead of ANALYZE statement .

We are collecting statistics by using the follwing statements with ANALYZE ;

1) ANALYZE TABLE table_name ESTIMATE STATISTICS ;

2) ANALYZE TABLE table_name ESTIMATE STATISTICS
  FOR TABLE
  FOR ALL INDEXES
  FOR ALL INDEXED COLUMN SIZE 100 ;

3) ANALYZE TABLE table_name PARTITION (partition_name) ESTIMATE STATISTICS ;

My question is if I use DBMS_STATS.GATHER_TABLE_STATS package and pass ownname tabname and estimate_percent
then will it be same as "ANALYZE TABLE table_name ESTIMATE STATISTICS " or
" ANALYZE TABLE table_name ESTIMATE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMN SIZE 1 "

How can I use the above statement to execute 1 & 2 .
I think if I set cascade to TRUE will run the 2nd statement and if I don't pass will take default value
which is FALSE will run the 1st statement . Please clarify .
0
Comment
Question by:bperumandla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Accepted Solution

by:
sora earned 50 total points
ID: 6959775
dbms_stats.gather_table_stats gets the table, index and column statistics for the specified table and partition. Parition is optional. If not specified it gathers stats for all partitions into which this table is divided.

So, to answer your question, DBMS_STATS.GATHER_TABLE_STATS is similar to

" ANALYZE TABLE table_name ESTIMATE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMN SIZE 1 "

But note that with DBMS_STATS, you can EXTIMATE only by a % (percentage) and not a specific number of rows - whereas with ANALYZE you can estimate for a specific number of rows as well.


For more on DBMS_STATS package, see this URL:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76936/dbms_sta.htm#999107

You need a technet account, but it is free.


sora
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6960404
The dbms_stats package can not accomplish the 2) to gather the column statistics, the histograms. Although its parallel implementation can speedup the statistics
gathering (if implemented correctly), in reality,
ANALYZE is still indispensible  
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 6962175
Hi!

Sora is not completely right (see p.2)!
_____________________________________________________
1. For statement "ANALYZE TABLE table_name ESTIMATE STATISTICS;"
------------------------
If you specify ANALYZE TABLE ESTIMATE
STATISTICS without a sample size, Oracle will only gather statistics based on 1064 rows.  This is important to note because depending on how large the table is, you may get inadequate statistics for the object being analyzed.
------------

In dbms_stats package you may set estimate_percent parameter by analogy with "ANALYZE TABLE ESTIMATE STATISTICS SAMPLE SIZE 20 PERCENT".

dbms_stats.gather_schema_stats('OWNER','TABLE_NAME',estimate_percent=>20).
________________________________________________________

2. For statement "ANALYZE TABLE table_name ESTIMATE STATISTICS
 FOR TABLE
 FOR ALL INDEXES
 FOR ALL INDEXED COLUMN SIZE 100 ;"

dbms_stats.gather_schema_stats('OWNER','TABLE_NAME',estimate_percent=>20,cascade='TRUE',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 100').

cascade: default value is FALSE!
method_opt: default value is 'FOR ALL COLUMNS SIZE 1'

 method_opt - method options of the following format (the phrase 'SIZE 1' is required to ensure gathering statistics in parallel and for use with the phrase hidden): FOR ALL [INDEXED | HIDDEN] COLUMNS [SIZE integer] FOR COLUMNS [SIZE integer] column|attribute [,column|attribute ...]



Best regards!
Yaroslav


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:sora
ID: 6962425
Yaroslav - Thanks for the correction.

Yes, I missed to mention about the DEFAULT=FALSE part

Cheers!

sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6962896
bperumandla - just out of curiosity, may I know why you want to switch from ANALYZE to DBMS_STATS

sora
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7063174
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9203575
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
points spto be given to Sora
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle dataguard 5 61
Oracle Distributed Transaction Lock Error ORA-01591 8 69
Oracle Nested table uses ? 2 45
PL/SQl Expanding the WHERE statement in query 3 34
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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

726 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