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. ]
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
dose of your own medicine
I had the misfortune to visit a very angry customer today. He was having problems loading customer data into Siebel. I sat him down with a nice cup of tea and invited him to tell me all about his woes. 'Well Norman - it's like this you see. We used to be able to load 400,000 contacts per hour. Now performance is absolutely abysmal and it takes 12 minutes to load just 1,000.' 'Hold on. What exactly have you changed since you last loaded 400,000 per hour.' 'Nothing. Nothing at all.' 'Hang on. When were you last able to load 400,000 per hour - exactly ?' 'Err well. It was back in February, you see.' 'And you expect me to believe that you have changed nothing since then ?' 'Yes.' 'OK. I want hard copies of all change controls raised against this system since February and access to Visual Source Safe. I also want the DBA and Siebel administrator here. Now.' 'We haven't got any change controls. We don't use configuration management and I am the DBA, Siebel Admin, systems admin, Web server guru and I also make the tea. We are a small company, you see.' 'I see. That's fine.' (sigh and roll eyes upwards). This could be a long day. And so the detailed analysis commenced. The control file (IFB) used to load the data. The key Oracle parameters. The hardware of the various servers. All identical to the configuration used back in February. And he did load 400,000 records per hour. He had the logs to prove it. And now he could type the data in quicker. I know because I saw it with my own eyes. A real mystery. Siebel's data loading utility is called EIM and uses CBO so time to check the statistics. This client uses Oracle 10g so statistics are automatically gathered by default (essentially gather stale) so all tables have current statistics. Hold on a minute. All tables apart from EIM_CONTACT and a load of others. 'Why have you dropped statistics on EIM_CONTACT ?' 'We haven't.' 'Oh yes you have.' 'Oh yeah. I remember now. I was playing with a custom script to drop stats on all empty tables. Siebel told me to do this because it affects performance (Alert 1162).' 'Oh I see. How very interesting.' It transpires that because the interface table is (correctly) truncated post-load, the statistics subsequently got deleted by this script. Kudos to this customer though for humourously naming the script 'dropem.sql'. Quickly compute statistics on EIM_CONTACT and indexes and performance is miraculously restored. Well almost. The client forgot to tell me that he had dropped unused indexes on the base tables to accelerate the data load still further. The 'ddlsync' utility that synchronises the Siebel Repository with the physical Oracle schema had subsequently been run which reinstated all the indexes slowing performance down slightly. The indexes are dropped and the original throughput is restored. Well almost. The client forgot to tell me he had enabled mobile web clients as an experiment for Olof Ericcsson (VP Sales - Scandinavia). So we disable remote transaction logging to eliminate that minor overhead. Throughput is now restored. So I said my goodbyes and left for the long drive home. Only, on this occasion, the journey home was very short. On this occasion, the client happened to be sitting at my kitchen table. On this occasion, the rack of expensive application/Web/database servers were actually all co-hosted on my very own laptop. On this occasion, the client who made all the mistakes was me.the never ending quest for the truth
Modern versions of Siebel use the Cost Based Optimizer (CBO) so the strategy used for gathering (and refreshing) statistics and histograms becomes increasingly important. Sometimes, the strategy can be articulated by the Oracle DBA or may even be documented or SQL scripts may be supplied that define the strategy. Normally, however, the definitive version of the truth lies in the data dictionary.SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON',cascade=>TRUE,
method_opt=>'FOR ALL COLUMNS SIZE AUTO');
SQL> select column_name,
to_char(last_analyzed, 'mm/dd/yyyy hh24:mi:ss') as last_analyzed, num_nulls,
global_stats, user_stats
from user_tab_col_statistics where table_name='PERSON' and column_name = 'SALARY';
COLUMN_NAME LAST_ANALYZED NUM_NULLS GLOBAL USER
SALARY 10/23/2006 14:55:15 0 YES NO
SQL> exec dbms_stats.set_column_stats(ownname=>user,tabname=>'PERSON',colname=>'SALARY',
nullcnt=>1000000);
SQL> /
COLUMN_NAME LAST_ANALYZED NUM_NULLS GLOBAL USER
SALARY 10/23/2006 14:55:18 1000000 YES YESSo when trying to answer to question: 'Have you ever had cause to manually populate column statistics ?', although the Oracle DBA is your friend, the USER_STATS column in the DBA_TAB_COL_STATISTICS view is your best friend.