One special behavior I found in Oracle (11.2.0.3) only on
Sun-Solaris platform.
In 11.2. + There is a bug 13549808 which deals with
improving performance of all_synonyms queries. But on Solaris system even after
applying the bug fix; the query related to ALL_SYNONYMS didn't improved though
it is fine on Linux Box without even gathering statistics.
After deployment of this bug fix; tried many aspects like
gather fixed stats; gather dictionary stats etc; system stats; but it didn't
work.
Finally; came to know that from 10g+ on wards automatically
Histograms are created; which may hamper the execution of the queries.
|
Preference
|
Description
|
Default (11gR+)
|
Version
|
|
METHOD_OPT
|
Controls column statistics collection and histogram creation.
|
FOR ALL COLUMNS SIZE AUTO
|
10gR1+
|
I tested the same for all_synonyms queries: with and without
histograms.
SQL> EXEC
DBMS_STATS.delete_dictionary_stats;------------------(Deleted the Stats and
started with fresh)
PL/SQL procedure successfully completed.
SQL> exec
dbms_stats.gather_dictionary_stats;---------------------(It will automatically
create histogram as it will take default settings)
PL/SQL procedure successfully completed.
Elapsed: 00:11:27.74
SQL> SELECT SYNONYM_NAME, TABLE_NAME FROM ALL_SYNONYMS
WHERE OWNER = 'PUBLIC' AND TABLE_OWNER = 'TEST' AND SYNONYM_NAME =
UPPER('TABLE_NAME');
SYNONYM_NAME
TABLE_NAME
------------------------------ ------------------------------
TABLE_NAME TABLE_NAME
Elapsed: 00:00:04.65
Now without histogram; to suppress the histogram creation I
used below statement
SQL> BEGIN
DBMS_STATS.GATHER_DICTIONARY_STATS (
method_opt
=> 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE,
options => 'GATHER' );
END;
/
PL/SQL procedure successfully completed.
And again fired the query and look the response time
dramatically improved.
SQL> SELECT SYNONYM_NAME, TABLE_NAME FROM ALL_SYNONYMS
WHERE OWNER = 'PUBLIC' AND TABLE_OWNER = 'TEST' AND SYNONYM_NAME =
UPPER('TABLE_NAME');
SYNONYM_NAME
TABLE_NAME
------------------------------
------------------------------
TABLE_NAME TABLE_NAME
Elapsed:
00:00:00.68
Just to add more: In default case estimate_percent won’t
make much difference as default value take auto effort which is close to 100%
and moreover we don’t have skew-ness in data so not to worry with this
parameter.
Enjoy Learning!!!
