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....
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....
Google Co-op is a customisable Google search engine and I just created a personalised, custom search for Oracle that scours AskTom, Jonathan Lewis’ site and www.oracle.com (including TechNet). This targetted search is useful and will undoubtedly save me time. However, I was unable to get any results from ‘tahiti.oracle.com’ (Oracle documentation set) which is normally my first port of call. Also, I assume Metalink is similarly off limits (account required).
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....
I have never heard customers complaining about the abysmal performance of the Oracle pseudo-table DUAL. Nor have I ever encountered a real-life performance issue that was attributed to slow access to DUAL. Rarely have I been asked to tune an SQL statement that was sub-second and took a massive 3 consistent gets. Another reason I won’t be using this tip is that, a few years ago, I did encounter some unexpected, weird, obtuse behaviour in an Oracle system that completely baffled everyone....
Bottom up Identify resource intensive SQL statements using Statspack (ADDM, custom scripts, Spotlight). Siebel is a black box that pumps out lengthy, complicated SQL statements with lots of (outer) joins. Level 7 snapshots are useful as ‘sprepsql’ may be used to reveal the full query text which is often tantalisingly truncated in the summary reports. In addition, the associated query plan for any statement can retrospectively dumped. The DBA can determine which queries are executed and how many times....
Many years ago, my horrid manager refused me a wonderful opportunity to go on an all expenses paid training course all about data warehousing in some exotic location. I was moaning about this to a colleague over lunch. She was an ex-teacher and happened to work in the prestigious data warehousing consultancy group. ‘There, there Norman. Don’t cry. Tell me exactly what you wanted to learn from this course ?’...
A long day tuning SQL queries using Siebel 7.8 and Oracle 10gR2… We used the Siebel recommended settings (TechNote 582). We used the Oracle recommended settings. We gathered table statistics. We gathered index statistics. We gathered column histograms. We dropped statistics on empty tables (Alert 1162). We set some miscellaneous (magic) underscore parameters to encourage CBO to use the correct index. We poured over 10053 trace files. We used a 15 year old, deprecated, desupported optimizer technology to reduce a complex 27 table (outer) join query with a subquery to subsecond from an hourglass....
A couple of people stumble across this blog searching for the ‘History of Oracle’ but ultimately go away disappointed. For those people, there is a brief but interesting timeline (covering 1977 to 2001) detailing the development of Oracle Corporation in this freely available screensaver available from Club Oracle. The screensaver is the one titled ‘Oracle Defining Moments - 25 Years of Technology Innovation’.
A Gartner/IDC report summarising the state of the database market in 2005 contains some interesting nuggets of information. The database market is still growing at 9.4% (which surprised me a little). OpenSource databases account for less than 1% of the market but are growing fast (47%). The Linux platform (thanks mainly to Oracle) is showing the strongest growth (84%). Despite these two statements of fact, Oracle are not perturbed by the threat of OpenSource (pass the salt cellar)....