Missed Opportunities and Getting It Right The First Time

Whenever Oracle brings out new features you are sometimes left thinking; thats nice, but I could really do with the new future allowing me to do x, y and z as well. For example:-

1. Index Organized tables in 8i not supporting secondary indexes.

2. Global indexes on partitioned tables becoming unusable after partition maintenance operations, drops, splits etc . . .

3. Being able to pass compressed blocks across the cluster interconnect, still an issue in 11g release 1 (I think).

This brings me on to cursor sharing and histograms. Histograms have been around since Oracle 7 are are used to provide the optimizer with information on skewed data, basic stuff really. Cursor sharing came along in 8i and is a way of getting the RDBMS to use bind variables when literal values are used in predicates. Up until 9i, histograms only got used when literal values were specified in a predicate on a column with a histogram. Where these two features come together is that if you are using bind variables Oracle will perform bind variable peeking, usually when a histogram exists, but also in some circumstances when histograms do not exist on the column(s) your predicates are used against.

Historically, most Oracle developers use literals against columns for which the data is skewed and binds against columns in predicates for which there is no value in forcing a re-parse of the SQL statement, typically values which are highly selective. With cursor_sharing = similar, plans should only be re-used if the parsed cursor can 'safely' re-use an existing plan. However Metalink technical note Note.377847.1 "Unsafe Peeked Bind Variables and Histograms", states that the RDBMS can mark a bind variable as being unsafe and thus generate many child cursors for statements, I have seen first at first hand the RDBMS doing this even when there is no histogram on a column (method_opt=>" . . . size = 1"). The solutions to this are:-

1. Use cursor sharing exact, this defeats the purpose of using cursor_sharing=similar somewhat.

2. 'Drop' histograms on columns that cause unsafe bind peeking, there is a good post on the Pythian blog about the overuse of histograms.

3. Check whether dynamic_sampling is causing this as per the Metalink technical note.

4. Set _optim_peek_user_binds = false to disable bind peeking full stop, if you still get this without a histogram.

v$sql_shared_cursor will not give any insight into why this happening. There is no 'fix' to this issue as such, however 11g introduces intelligence into the issue with adaptive cursor sharing. The on line Oracle documentation, Optimizer blog and Technet all provide good references on this.

The real question is why has it taken Oracle this long to introduce intelligence into cursor sharing ?. On the same theme of new features taking their time (and then some) to mature, I'd like to use the results cache in 11g in anger, but how long will it take Oracle to shave the rough edges off this feature, i.e.:-

1. Results being invalidated when none related rows in the related base tables change.

2. No explicit information in autotrace stats for knowing that results caching has been used

etc . .

I've heard whispers of new functionality appearing in 11g release 2 being related to none core RDBMS technology, perhaps Oracle should focus on the maturity of the RDBMS engine core features first.

A New Type Of Synchronization Mechanism In 10g Release 2

Up until 10g release 2, the only two locking and synchronization mechanisms available to the RDBMS engine were latches and enqueues. Oracle 10g release 2 introduces mutexs which replaces the library cache pin for shared cursors on platforms supporting atomic compare and swap operations (CAS). There is scant information on the library cache pin mutex on MetaLink or the internet at large for that matter, however Tanel Poder from Miracle A/S and the Oak Table provides some useful insights into this:-

Re: cursor: pin S wait on X
From: Tanel Põder
To: , "Mladen Gogala"
Date: Sat, 29 Apr 2006 20:10:50 +0900 I studied the mutex stuff a bit when 10.2 came out, I'd say mutexes are less intelligent than latches. But thanks to that, they are more lightweight and thanks to quite large caches on modern CPUs we can afford having tons of them - allowing very fine grained locking.On my 32bit linux installation a mutex was 28 bytes in size, while a regular latch structure was 110 bytes. In theory every library cache child should have it's own mutex, but in practice I saw one case where two different child objects were protected by the same mutex - that was what library cache dump showed me anyway.Mutex also acts as a pin structure, so there's no need to do another memory write for pinning a cursor. Mutex allows immediate shared access to resources it protects, the old approach would mean getting a library cache latch in exclusive mode and then modifying the corresponding library cache child pin structure.Mutexes also don't maintain statistics on successful gets as latches do, only sleeps are registered.Thanks to all that, an immediately succeeding mutex get operation takes several times less CPU instructions (and hits possibly less memory stalls), from a 10gR2 perf tuning course I remember it was about 250 vs 50 CPU instructions on IA32 platform.Note that mutexes don't provide much benefit for poorly written applications, for most benefit your apps should avoid any kind of reparsing - keeping cursors open and cancelling them manually when interest in query results has ended.Tanel.

Mutexs are indeed light weight and Oracle states in some information that Oracle support provided me with (the document included 'external' in the title making me presume it is for public consumption) that the rationales for their introduction into the RDBMS engine are:-

1. They are smaller and much faster to get than latches.

2. It is fast and easy to create mutexes for individual structures leading to less contention on the mechanisms that protect the actual structures.

3. The mutex implementation is highly flexible in that components can define wait policy for individual mutexes.

4. Mutexes exhibit a dual nature ideal for pin type operations, many sessions can reference a mutex in shared mode where as only one session can hold a mutex in exclusive mode.

Activity on the library cache pin can be observed through waits appearing on events prefixed by cursor: pin, this is usually followed by something denoting the type of operation, e.g. cursor: pin S wait on X, pinning a cursor for an execute. The v$ views expose library cache pin activity through the v$mutex_sleep_history and v$mutex_sleeps views. When monitoring this mutex, the most important thing to keep an eye for is the amount of time waited on cursor pin events as opposed to the number of events. Large wait times can be due to numerous child cursors being built beneath a parent cursor through cursor invalidation, bind variable variance, statements being aged out of the shared pool etc. In fact, v$sql_shared_cursor view gives the comprehensive range of scenarios that can cause cursors to be be re-parsed:-

SQL_IDADDRESSCHILD_ADDRESS

CHILD_NUMBERUN

BOUND_CURSOR

SQL_TYPE_MISMATCH

OPTIMIZER_MISMATCH

OUTLINE_MISMATCH

STATS_ROW_MISMATCH

LITERAL_MISMATCH

SEC_DEPTH_MISMATCH

EXPLAIN_PLAN_CURSOR

BUFFERED_DML_MISMATCH

PDML_ENV_MISMATCH

INST_DRTLD_MISMATCH

SLAVE_QC_MISMATCH

TYPECHECK_MISMATCH

AUTH_CHECK_MISMATCH

BIND_MISMATCH

DESCRIBE_MISMATCH

LANGUAGE_MISMATCH

TRANSLATION_MISMATCH

ROW_LEVEL_SEC_MISMATCH

NSUFF_PRIVS

INSUFF_PRIVS_REM

REMOTE_TRANS_MISMATCH

LOGMINER_SESSION_MISMATCH

INCOMP_LTRL_MISMATCH

OVERLAP_TIME_MISMATCH

SQL_REDIRECT_MISMATCH

MV_QUERY_GEN_MISMATCH

USER_BIND_PEEK_MISMATCH

TYPCHK_DEP_MISMATCH

NO_TRIGGER_MISMATCH

FLASHBACK_CURSOR

ANYDATA_TRANSFORMATION

INCOMPLETE_CURSOR

TOP_LEVEL_RPI_CURSOR

DIFFERENT_LONG_LENGTH

LOGICAL_STANDBY_APPLY

DIFF_CALL_DURN

BIND_UACS_DIFF

PLSQL_CMP_SWITCHS_DIFF

CURSOR_PARTS_MISMATCH

STB_OBJECT_MISMATCH

ROW_SHIP_MISMATCH

PQ_SLAVE_MISMATCH

TOP_LEVEL_DDL_MISMATCH

MULTI_PX_MISMATCH

BIND_PEEKED_PQ_MISMATCH

MV_REWRITE_MISMATCH

ROLL_INVALID_MISMATCH

OPTIMIZER_MODE_MISMATCH

PX_MISMATCHMV_STALE

OBJ_MISMATCH

FLASHBACK_TABLE_MISMATCH

LITREP_COMP_MISMATCH

Metalink technical note 438755.1 'Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value' contains information on how to obtain useful reports from this view, it also provides other links to articles to assist in investigating shared cursor issues further. Its a shame that the treasure trove of Oracle internals hints and tips;http://www.ixora.com.au/ is no longer kept uptodate as I'm sure that Steve Adams would have provided some excellant technical details on this mutex much more eloquantly than I have in this posting here.

The Great Oracle Diagnostic Tools Debate

There has been a lot of blogging recently over the limitations of the ADDM and time model introduced in 10g, Dan Fink has blogged on this, Richard Foote has blogged on this in response to Dan's blog. Guy Harrison made a presentation at OOW 2007 mentioning the limitiations of ADDM and the 10g time model. Cary Milsap made a presentation at OOW 2007 on 'Skew' also citing the limitations of ADDM and the time based model specifically with regard to developing application performance profiles. I agree that:-

• The "self managing database" is more a thing of marketing than technology, to quote Dan Fink.
  
  
• Extended tracing allows you to see where time is being expended on individual statements to a much finer degree of granularity than ADDM and the time model.
  

However, there are certain practicalities that need to be considered here:-

• Extended trace is of limited use for "one off" performance issues, you wouldn't really want this to be enabled all the time on a production environment.
  
  
• The use of J2EE application servers is common place today, unless your application Java code is well instrumented by using using DBMS_SUPPORT to end to end tracing, a logon trigger to trace what the JDBC client is doing may have to be used. Trace files can be tagged using EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER . . .' . Unfortunately good application instrumentation is the exception rather than the rule and only Oracle savy Java developers or teams with access to a good DBA will know about end to end tracing and trcsess. Otherwise, something such as a logon trigger may lead to a forest of trace files to wade through.


• Some IT shops (and I know this is bad practise) don't always have a test / development environment for every production environment or don't have test environments that are identical to their production environments, this is often the case with data warehouses. Therefore, if you have a very intermitant problem in production or a problem which despite being reproducible is very infrequently, reproducing this on another environment might be a problem and again you wouldn't really want SQL tracing enabled on a production environment 24/7 until a problem re-occurs. This may not be such an issue if via good instrumentation the application provides the facility for tracing to be generated on specific parts of the application and under specific circumstances, however, my experience is that applications that do this are few and far between.
  


• In his post Richard Foote uses a very good example of enabling extended tracing being analogous to putting a camera on some who journey gets held up, his wife in the example. What do you do if you don't know what part of the software to "put the camera" on ?.
  
  
• The issue you are experiencing might be time sensitive, meaning that in order to reproduce the issue you need to replay data which was loaded / processed by the application at specific dates and times. With Unix this can be done by changing the system clock, but again, this is not something you would really want to do in a production environment.

I would love to know what Grahan Wood, architect and 'father' of the ADDM and time model makes of this debate and if he has plans to modify the ADDM and time model in light of these criticisms. Furthermore, I would also be interested to know what percentage of performance issues are down to bad configuration, poor coding practises, poor design and poor architecting, as it occurs to me that some people are concerned about ADDM and the time model taking some of the art out of tuning. However, I believe that an element of tuning is identifying poor design and architectural descisions a skill that no tool will be able to replace for a long long time.