Drupal supports Oracle database
Just installed and configured Drupal 4.7.3 and noted an announcement asking for volunteers to test newly added support for the Oracle database.
Most open source, content management systems (WordPress, Joomla et al) use MySQL so it will be interesting to see whether there is much demand for a CMS running on an Oracle database.
On a similar note, Oracle are likely to confirm that the next major release of Siebel (8.0) will be available on the Linux platform. The official announcement is expected at Oracle OpenWorld later this month. This isn't wholly unexpected as support for Linux in Siebel 8.0 was included in the public Statement of Direction (May 2006).
The availability of the Siebel on Linux is is likely to be an attractive option for smaller companies, some of whom who already deploy the Siebel database on a Linux platform.
back seat driver
'Who is the SQL Server DBA ?' 'I am' 'Who is the Siebel Administrator ?' 'Err, me' 'Who is the keeper of DNS ?' 'I am' 'Do you want a screenshot of that ?' 'No. I am only noting down areas where we diverge from the default OOTB configuration.' 'Who do we ask about adding entries to the Active Directory ?' 'Him' 'Who is the Web meister ?' 'Me' And so, when working with such intelligent people (Jack of All Trades), it is entirely possible to install, patch and demonstrate the features of a highly available, resilient Siebel 7.8 Enterprise, within one day, featuring:-Two Web Servers (IIS 6.0) load balanced by Cisco CSS Active-Active MSCS cluster (Gateway, EIM, Workflow, Assignment Manager, Data Quality) Two Siebel Servers (Object Manager) using native load balancing Clustered SQL Server databaseA thoroughly satisfying and productive day with plenty of intelligent, probing questions from technical people eager to learn and willing to read books. A day that should have ended in handshakes all round, a premature end to the engagement, a glowing testimonial from the customer about yours truly to their account manager that he, in turn, forwarded to my manager. But no. This customer now wants to deploy SSL, authenticate using Active Directory and would like to see how Single Sign On (SSO) could help the business.
30 second guide to tuning Siebel
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. There is no point tuning a query that is only executed monthly whereas shaving fractions of a second from a fundamental query executed thousands of times may prove more worthwhile.
The disadvantages of this approach are that the bind variables and username who issued the query are not available which may be important. Secondly, it is not obvious which Siebel screen is associated with the problematic SQL statement(s).
Middle out
Increase Object Manager logging to dump the SQL statements together with timing information for prepare, fetch and execute. Search the log files for any SQL taking more than one second. The advantage here is that the username for the session. the sequence of screens visited and all bind variables are included so it is easier to reproduce in SQL*Plus.
Patterns of usages and resource intensive queries should start to appear. You may even identify groups of users (by job role or geographic location) who have different types of problems.
Top down
A little radical for most DBA's (developers, integrators and highly paid consultants for that matter). Seek out the users, sit down and talk with them.
Watch how they actually use the application for 30 minutes. This is often most enlightening. You witness first hand how users actually use the system. Sometimes it turns out that they are not always using the application efficiently (for various reasons).
You can see the typical business scenarios, the frequently used views (tables), the type of searches users specify, what they like, what they hate, what is fast, what is slow. In fact, your visit may well end up lasting a lot more than 30 minutes.
Or you may need a combination of all three.
Rule is dead, long live Rule
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.
We opened an SR with Oracle Technical Support.
We opened an SR with Siebel Technical Support.
We used a stored outline.
We went home.
helping people read books
Someone recently asked me at a dinner party: 'So, Norman, tell me what you do in life ?'. I spontaneously replied: 'I help people read books'. The lady (for it was a she) exclaimed: 'Oh how absolutely fabulous. You are a teacher'. 'Err, well, no. I actually work in IT'. 'Oh I see. You work in training. Why didn't you just say so ?' 'Err, well no. I am a sort of IT consultant'.
Anyway, after an embarassing stony silence, thankfully I managed to steer the conversation to the safer domain of the wide range of choices for secondary school education in our locality. This fascinating subject occupied us right through until the desert and coffee were served.
But the point I was trying to make was that Siebel and Oracle are incredibly large, complicated, wide ranging software products. I have worked with Siebel for three years and Oracle for a little longer but there are still so many areas and modules in both products that I have no practical experience of whatsoever.
I remember once reading Tom Kyte stating that he did not have access to the Oracle source code nor did he did not have a hotline to RDBMS engineering. The basis of his wealth of extensive Oracle knowledge was primarily the documentation set. I remember being hugely impressed by this simple statement. [ Sorry I did look but failed to locate the reference ]
I am a Siebel 'consultant' trying to help people use Siebel more effectively. Most of the information needed to help customers use Siebel more effectively is actually contained in the documentation. The only problem is that the 'documentation' is simply overwhelming as it includes the manuals, FAQ's, Alerts, Release Notes, Service Requests etc etc.
I have a couple of advantages: Firstly, I am continually exposed to a wide variety of different Siebel related issues day after day so I so have a degree of experience of real-world problems (and hopefully the resolution).
Secondly, and more importantly, I do have access to a network of highly talented, intelligent individuals with far more experience and intelligence than yours truly. Now this wouldn't be an advantage unless that group of people were prepared to share their knowledge and I am pleased to say that they are. This isn't necessarily true at all companies I have worked for.
Normally, I lug my heavy laptop, hanging over my shoulder, attached to my body like a young helpless infant, all around Europe. Today I was in Stockholm and the weather was unusually hot (30'C). To reach the office, I had to take a train and a tube in the morning rush hour. Consequently, I left the laptop behind in the hotel and arrived onsite free from back pain and feeling blissfully liberated.
I told the customer that we would purely be using the public documentation that is freely available to me and him. No hidden cheat-sheets, no private internal emails, no top tips from engineering. He was impressed (I think).
Then, of course, inevitably, we hit a very obtuse, bizarre problem, neither of us had encountered before so it was time to make another call on that network.