philosophy of Oracle contractors
My. How times change. In my day, the philosophy was simple:
- Always be sick in your own time.
- Ensure a newspaper is safely cached behind the toilet cistern.
- Arrive and leave the office 30 seconds before/after your manager.
- Get an urgent pager alert whenever your round is imminent.
- Be quick to claim credit and divert blame.
But no. I have now finally discovered the true Oracle Contractors Philosophy.
probably the best feature in Oracle 10g
Regularly refreshing Oracle statistics on all tables, indexes and column histograms flushes the shared pool and can occasionally lead to some unexpected and unpleasant surprises. Thankfully, Oracle 10g automatically maintains 31 days of statistics history which means it is trivial to revert to a previous set in order to restore service to the production environment while you investigate further. A perfectly valid strategy for statistics gathering is to gather, test, validate, save and leave well alone.SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
14-NOV-06 10.26.57.421000000 AM +00:00
SQL> exec dbms_stats.restore_schema_stats(ownname=>'SIEBEL',as_of_timestamp=>trunc(sysdate)-7)
PL/SQL procedure successfully completed. in praise of CBO
Jonathan Lewis poses a very interesting question:
Have you ever wondered how hard the optimizer has to work to produce a plan ?
Well actually Jonathan, curiously enough I have. Many times, in fact.
Siebel 7.7 (released in 2004) was the first version of Siebel to support the Oracle cost based optimizer (CBO) and pleased a lot of demanding Oracle DBA's who looked contemptuously on the 10 year old rule based optimizer (RBO) technology and had long petitioned Siebel to finally add support for CBO to improve performance, support table partitioning, parallel query, bitmap indexes and lots of other features they wanted to use (i.e learn about).
Inevitably, this major architecture change brought improvements in performance and was generally welcomed by new customers. However, the use of CBO also introduced some new issues for existing, long-standing Siebel customers who upgraded to 7.7 from previous releases.
For example, if you dug very deep with tkprof and ran comparative tests, it was common to observe that parse times were increased with CBO. This was because versions of Siebel prior to 7.7 used the rule based optimizer which follows a fixed set of, 15, err, rules to determine the query plan to be used. Consequently, parsing in RBO is deterministic and relatively quick.
Academics proved that it was actually possible for a well-trained monkey to learn and recite the 15 rules and accurately predict the query plan that Oracle will select. Clever Siebel 2000 developers discovered they could influence the chosen query plan by reciting magic incantations, altering search specifications, sacrificing lambs, nullifying the use of unhelpful indexes and even modifying the ordering of WHERE clauses in the SQL statements.
However. as Siebel finally moved into the 1990's and embraced CBO, things suddenly became very different. The strategy for CBO is to simply give the optimizer as much data as possible so the optimizer can make the 'best' decision regardless. Talented Siebel developers everywhere quaked in their boots and now sacrificed their lambs, praying for a contract renewal.
So whenever Siebel customers complained to me that parse times were too high, I was often tempted to ask them:-
'How many pages of A4 did it take to print out this problematic query with the high parse time ?'
'Err, 16.' 'How many tables are joined ?'
'Well, err, 38.' 'And how many outer joins are in this query ?'
'Well, err, 12.' 'How many indexes are defined on those 38 tables ?'
'Well, err, 189 ?'
'So, if I gave you detailed statistics on those 38 tables together with even more statistics on those 189 indexes together with frequency histograms of all of the columns involved, how long would it take you to parse that query and tell me the best query plan to return the data as quickly as possible ?'
'Well, err, I'm not sure'
'And how long do you think it would it take you to write a computer program to crunch all that data and return the best query plan ?'
'Well, err, I really don't know'
'So do you still think that 3.4 seconds is really too long to parse this statement for the first time and sub-second thereafter ?'.
'Well, err no, I guess not'.
Now I am lucky enough to work for Oracle, I have visibility to a wide variety of complicated CBO related bugs including detailed analysis from Technical Support and Engineering of 10053 trace files.
Consequently, I have nothing but complete respect for the software engineers who develop, maintain, fix and seek to improve the Oracle cost based optimizer.
[ I can thoroughly recommend Jonathan Lewis' book as an excellent introduction with plenty of examples to illustrate how the CBO works. ]
suggestions for Metalink
1. Tighten security for all parties by addressing this issue:'The Password must contain only ASCII alphanumeric characters.'2. The 'Quick Links to the Latest Patchsets' is a great idea spoilt only by the absurd complexity in navigating the resulting pull-down. If you possess the hand/eye co-ordination and reflexes to successfully navigate to the correct platform and the desired version first time, you could probably make a fortune playing those impossible 'Win a cuddly lion with this crane like mechanism' kiosks at the fun fair.
recycle pool #1: Oracle
Good evening. My name is 'Google Bot'. It was a difficult (N-P complete) and very time consuming task to decide on the precise content for this article. After all, I have crawled 2.6 billion pages in the last seven days so I am a little tired. However, here is the pick of the blogosphere for November. Oracle release 10.2.0.3 patch set (currently available on Linux only). This release includes several fixes related to the cost based optimizer. Siebel 7.8 customers using Oracle 10g should note the following bug fixes:- 4370351 - High parse CPU (in qksfroInit) for large queries
- 4573396 - CBO does not account for chained rows
- 4711525 - Parsing of bitmapped plans can be slow
- 4772145 - Gathering statistics on large indexes is slow