custom Oracle search engine
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).
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 YES
So 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.
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.
is it really worth it ?
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. This was eventually tracked to the presence of not one, but two rows in the DUAL table.
Better to let sleeping dogs lie.
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.