Home » RDBMS Server » Server Administration » dbms_stats.set_param
| dbms_stats.set_param [message #157632] |
Fri, 03 February 2006 10:40 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Can anyone confirm if setting estimate_percent to 0 is the same as setting it to null? Will it do a compute in both cases?
I ask because:
MYDBA > exec dbms_stats.set_param('estimate_percent',null);
PL/SQL procedure successfully completed.
MYDBA > select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
MYDBA > exec dbms_stats.set_param('estimate_percent',0);
PL/SQL procedure successfully completed.
MYDBA > select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------
0
When you set parameters in dbms_stats, the values actually set when you specify a null is determined by oracle. This is all in the 10gR2 docs and the above test seems to verify this.
But the docs also say, that in order to do a compute, you need to pass null as the "value" to the estimate_percent argument of gather_table_stats. The docs don't say "pass null or 0". They just say pass null. Hence my desire to clarify.
Furthermore, if I create a table and gather stats explicitly setting the parameter in the call to null, the sample size is a compute as it is the same as the row count:
MYDBA > create table test as select level a from dual connect by level <= 1000000;
Table created.
MYDBA > exec dbms_stats.gather_table_stats(user,'TEST',estimate_percent => null);
PL/SQL procedure successfully completed.
MYDBA > select sample_size from user_tables where table_name = 'TEST';
SAMPLE_SIZE
-----------
1000000
However, and this leaves me in doubt as to how to specify using the set_param function the ability to do a compute, if I try setting a value of 0 instead, the sample size is much less than the number of rows, indicating that it did not do a compute but an estimate instead:
MYDBA > exec dbms_stats.set_param('estimate_percent',0);
PL/SQL procedure successfully completed.
MYDBA > select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
------------------------------------------------------------------------
0
MYDBA > exec dbms_stats.gather_table_stats(user,'test');
PL/SQL procedure successfully completed.
MYDBA > select sample_size from user_tables where table_name = 'TEST';
SAMPLE_SIZE
-----------
250266
Where it gets 25% from I'm not sure. Is it the same as AUTO_SAMPLE_SIZE, since according to the docs the estimate_percent must be in a range from .01 to 100, or be null? So it sees the invalid value of 0 (why doesn't the set_param catch this invalid value) and just defaults to auto_sample_size?
MYDBA > exec dbms_stats.set_param('estimate_percent');
BEGIN dbms_stats.set_param('estimate_percent'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SET_PARAM'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
MYDBA > exec dbms_Stats.set_param('estimate_percent',null);
PL/SQL procedure successfully completed.
MYDBA > prompt note that you had to actually specify null as a parameter...
note that you had to actually specify null as a parameter...
MYDBA > select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
MYDBA > exec dbms_stats.gather_table_stats(user,'test');
PL/SQL procedure successfully completed.
MYDBA > select sample_size from user_tables where table_name = 'TEST';
SAMPLE_SIZE
-----------
50949
So nope, that blows that theory out of the water because in this case auto_sample_size comes up with 50%...
(note also that sample size, I'm guessing, is similar to the sample syntax in the from clause of the select statement, in that it won't get exactly 25% or 50% of the rows. Although whether you are using block sampling in the statisics gathering should affect this. But all of this is another matter to be covered elsewhere, and detracts from the point. Block sampling, according to docs -- and would make logical sense, only comes into play when estimating to begin with rather than computing).
So...in summary, if you use set_param without a value, it errors out. If you pass it a null, it changes it to auto_sample_size. If you set it to 0, it uses some other sample size. How does one compute, without explicitly specifying the estimate_percent parameter in the gather_stats call and not relying on the default parameter mechanism using set_param and get_param?
WAIT...since the value parameter is supposed to be a character, what if I put the null in single quotes in the set_param call?
MYDBA > exec dbms_stats.set_param('estimate_percent','null');
PL/SQL procedure successfully completed.
MYDBA > select dbms_stats.get_param('estimate_percent') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
----------------------------------------------------------------------------
NULL
MYDBA > exec dbms_stats.gather_table_stats(user,'test');
PL/SQL procedure successfully completed.
MYDBA > select sample_size from user_tables where table_name = 'TEST';
SAMPLE_SIZE
-----------
1000000
AHA! And I bet you use the same technique to specify true in the set_param call to specify to cascade by default.
Take that you goofy new feature that:
a) could have used a bit more clarification in the documentation
b) has questionable benefit, and unquestionable complexity
c) forces someone to explicitly show all gather_table_stats parameters on any future demo scripts and sessions to post because there is no longer a single and consistent default value for everyone's systems
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 14:35:19 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
