bperumandla
asked on
How to use DBMS_STATS
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_ST ATS 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 .
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_ST
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 .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_s tats('OWNE R','TABLE_ NAME',esti mate_perce nt=>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_s tats('OWNE R','TABLE_ NAME',esti mate_perce nt=>20,cas cade='TRUE ',method_o pt=>'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
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_s
__________________________
2. For statement "ANALYZE TABLE table_name ESTIMATE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMN SIZE 100 ;"
dbms_stats.gather_schema_s
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
Yaroslav - Thanks for the correction.
Yes, I missed to mention about the DEFAULT=FALSE part
Cheers!
sora
Yes, I missed to mention about the DEFAULT=FALSE part
Cheers!
sora
bperumandla - just out of curiosity, may I know why you want to switch from ANALYZE to DBMS_STATS
sora
sora
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 **
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 **
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
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
gathering (if implemented correctly), in reality,
ANALYZE is still indispensible