This content has been marked as final. Show 4 replies
Py wrote:By saying SKEWONLY you are letting oracle make the decision, maybe oracle made the decision to NOT collect histograms. Why not set it to FOR ALL INDEXED COLUMNS SIZE AUTO (or other option) in your test environment and verify? You dont have to run the ETL job, just set the pref, run the same command that ETL job would run and then verify via dictionary.
exec DBMS_STATS.SET_DATABASE_PREFS (pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY');
If it is specific schema you have issue with, then you ought to be careful and use SCHEMA_PREFS, DATABASE_PREFS means all schemas excluding SYS. So, you may end up impacting other schema without intending to.
>1 person found this helpful
Does anyone know a method how to set system wide optimizer defaults even after dropping and recreating a table?
Yes. Without discussing if the parameter of your choice is the best possible solution for your problem you can always use
This setting will also affect all the subsequently created tables.
SQL> exec dbms_stats.set_global_prefs('METHOD_OPT', 'FOR ALL COLUMNS SIZE SKEWONLY');
There're a few alternative solutions on top of it.
- If your tables have the same structure once recreated, you can truncate them instead of dropping and creating.
- Since you're collecting the stats anyway you can explicitly specify any supported parameter right in the procedure:
Hope it helps.
SQL> EXEC dbms_stats.gather_table_stats('SCHEMA', 'TABLE', cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');
Thank you for your answer.
The issue is that the application is located in a validated environment. This means that there is a hugh amount of validation, testing and documentation effort if we would change the ETL routine. This would have to be done by our debelopers.
From my side of view (DBA) it seems to be less effort to "only" change DB parameters.
Therefore I first tried to change the parameter on the three relevant schemas with following statement
But the result was that it was only applied for existing tables and not for these ones that I created after the statement. (Bahaviour is tested.)
After this I tried the same thing on next level with dbms_stats.set_database_prefs();
I did not try to use the "set_global_prefs" method.
Will discuss this with my developers tomorrow if it is an option to adapt the ETL job.
Your situation sounds familiar, but there's no progress without the change.
The set_global_prefs procedure works in my environment (126.96.36.199 on RH5.6).