UKOUG Technical and E-Business Suite Conference 2010 – The Controversial Stuff

So the UKOUG Technical and E-Business Suite Conference is over for another year. Considering the adverse external factors, the event was incredibly successful (in my opinion). We had expected that the recession and imminent public sector cutbacks would affect attendance this year. However, despite the inclement weather, attendances were surprisingly resilient this year. This is undoubtedly due to the quality of speakers that we attract from around the world and the quality of our audience, both domestic and international.

In this post I will try to explain one or two unexpected issues that we faced this year.

First of all we experienced an unprecedented number of issues with the agenda. Many of these were caused by late speaker cancellations due to the unexpectedly adverse weather often preventing speakers from travelling. There is not much we can do about the weather. However, it was suggested to me by several attendees that we could copy some of the larger US conferences by asking regular speakers to bring along a reserve presentation in order to avoid gaps in the agenda.

The remaining agenda issues were almost entirely caused by the retrospectively bad decision to hold judging and agenda planning later in the year. The main motivation for this decision was to ensure papers selected for conference were as current as possible. In particular we would like to have been able to consider material and announcements from Oracle OpenWorld. As it happened the latest date it was possible to hold the agenda planning meeting was early September, too early for OpenWorld anyway. However shifting this meeting back put a huge amount of pressure on UKOUG staff for whom the conference is the largest single operation. Speakers were notified during OpenWorld and it takes at least six weeks to get confirmations and to source suitable replacements in the event the speaker can no longer take up the slot. Apparently the office dealt with over 600 e-mails regarding agenda changes following the original planning meeting.

As a director I can only apologise for the inconvenience the resulting agenda changes have caused to speakers, session chairs and delegates and the unnecessary pressure inflicted on the staff.

As it happens I was not present at the meeting where the decision to delay paper selection was made; I would have strongly opposed it, but not because I have some crystal ball; As a regular speaker I know it takes at least one month for me to prepare a new presentation and I would have been selfishly concerned that I would not have time to develop new material for two presentations. I also recognize that the late acceptance of sessions puts unnecessary pressure on other speakers making it difficult to schedule time and potentially leading to increased travel and accommodation costs.

I also have to take some of the blame for the agenda as I am a member of the agenda planning team. I do not to participate in abstract judging, but I am involved in scheduling the server technology stream. We try to select papers based on judges scores but with some sensitivity to the material so, for example, we don’t get eight presentations on the same topic. Server technology is the largest stream usually with at least three concurrent sessions often allocated to the three largest halls available. During the planning meeting we produced a reasonably attractive and consistent agenda. However, subsequent changes are made in response to speaker cancellations, replacements, dependencies on travel arrangements etc. In past years this process has worked reasonably well; this year due to the compressed timetable we needed to respond much more rapidly which meant it was difficult to retain an overview of the entire agenda. We recognize that a better process is needed for next year.

One of the main victims of this process was myself. I originally submitted three abstracts, of which two were selected in the planning meeting. The third presentation was discarded under our rule that most presenters have a maximum of two sessions. In retrospect I should have torn up the card for the third session at the time but it somehow sneaked back onto the agenda as a late replacement. Unfortunately I only discovered this on the Thursday before the event at which point it was far too late to develop a new presentation and there was no alternative but to cancel it. I must apologize again to anyone who turned up to “The Statistics Never Lie?”; they might not but on this occasion the printed agenda definitely did.

I would like to thank Dan Morgan for stepping in at the last moment with a really excellent replacement presentation on Very Large Databases – it was much better than mine would have been and it was a pity that the confusion over scheduling resulted in a smaller audience than the presentation deserved.

In order to ensure the continued financial viability of the event, we made some changes to the structure of the conference. A couple of these worked incredibly well in my opinion:

• We moved the early evening social events from a dedicated hall into the exhibition hall. I thought this was an inspired decision; the exhibitors were pleased with the additional footfall and for delegates like myself, it was great to be able to have conversations without intrusive music or other distractions. My kids used to like jugglers and unicyclists; I’m not sure I need them around when I am trying to discuss RAC upgrades or to close a consultancy deal.
• We moved the speaker lounge to the balcony of the exhibition hall. Again this worked well as the speakers were more centrally located and had to traverse the reception area to access the lounge making them more accessible to other delegates. Previously they were located in the Media Suite which segregated them completely from other attendees.

So all in all I think this was a successful event despite the weather which affected the travel plans of many delegates. When I left on Wednesday evening there were quite a large number of delegates stranded in Birmingham because of flight delays, rail cancellations or road closures. There’s not much we can do about the weather, but there are definitely worse places in the world to get stuck.

I will discuss the individual sessions that I attended in separate posts. In the meantime, thanks to everyone who contributed to such an enjoyable and productive event.

Oracle OpenWorld 2010 – Day 4

The final day of Oracle OpenWorld always starts slowly as it follows the Appreciation event the night before. This year the Appreciation Event was at Treasure Island which is an island in the middle of the Bay connected to the mainland by the Bay Bridge. Headline act this year was the Black Eyed Peas with another five reasonably well-known acts also playing on the two stages. Coaches run to the event from the conference hotels.

The main impact of the Appreciation Event is that attendances at early morning sessions on the final day can be quite low. I missed the 9:00 session, but managed to fit in another four before attending yet another party to celebrate the end of the event.

Active Data Guard

This was an hour really well spent with Larry Carpenter and his team. I have a couple of customers using Active Data Guard and it can be a very effective solution particularly for reporting and read-only access on the standby database. The only downside is the license which must be paid for both the primary and standby sites (not unreasonable since a switchover can be performed at any time).

Useful information included:

Data Lag

In Oracle 11.2 and above a lag can be specified between the primary and standby database; if the lag is exceeded when a query is executed on the standby, an error will be raised.

data lag = transport lag + apply lag

Each session can specify its own maximum data lag using the STANDBY_MAX_DATA_DELAY parameter. The value of this parameter can be set to zero if synchronous (SYNC) transport is configured. Timestamps are taken when redo is written, delivered and applied to help measure the lag. In 11.1 and above redo is shipped in parallel to all destinations; prior to this redo was written to the primary database first.

For example to specify the lag for the HR user in the standby database create a trigger in the primary (the syntax may not be correct here)

CREATE OR REPLACE TRIGGER hr_lag
AFTER LOGON ON hr.schema
BEGIN
   IF (SYS_CONTEXT (‘USERENV’,'DATABASE_ROLE’)
  IN (‘PHYSICAL STANDBY’)
  THEN 
      EXECUTE IMMEDIATE
     ‘ALTER SESSION SET standby_max_data_delay = 30′;
  END IF;
END;
/

DML Redirection

In Oracle 11.2 DML can be performed by sessions connected to the standby database. This is achieved by creating a ghost user (e.g. HR_SYN) on the primary database and within that user creating a synonym for every table in the original schema (e.g. HR). For tables that are accessed read-only on the standby database, the synonym will point to the equivalent table in the HR standby schema; for tables that are updated the synonym will use a database link to reference a table in the primary database HR schema.

After creating the synonyms, the HR user must be configured to log into the HR_SYN schema when the database is running  in standby mode. This is achieved using a logon trigger. For example:

CREATE OR REPLACE TRIGGER hr_lag
AFTER LOGON ON hr.schema
BEGIN
   IF (SYS_CONTEXT (‘USERENV’,'DATABASE_ROLE’)
  IN (‘PHYSICAL STANDBY’)
  THEN 
      EXECUTE IMMEDIATE
     ‘ALTER SESSION SET SCHEMA = HR_SYN;
  END IF;
END;
/

The purpose of the DML changes is to alow packaged applications to run reports on a standby database running in read-only with apply mode. Many otherwise read-only reports require write capabilities to create intermediate tables, log progress or perform auditing. EBS reports use the triggers to redirect writes to the primary database. Version 12.1.3 will work with Active Data Guard in 11.1.0.7 and above. 137 reports have been tested; 72 were successful in Active Data Guard using DML redirection including 9 of the 13 most resource-intensive.

Event 3177 can be enabled to track read-write SQL modules that will fail on the active standby (see Note 1206774.1)

Block Change Tracking

In Oracle 11.1 and above (check version) block change tracking can be enabled on the standby, so the standby database can be incrementally backed up using RMAN. Previously only full backups could be performed on the standby.

Automatic Block Repair

Oracle 11.2 also supports automatic block repair; if a corrupt block is discovered on either the primay or the standby database, the equivalent uncorrupted block will be copied across from the other database. I guess this protects against physical, but not logical errors.

Standby STATSPACK

All versions of Active Standby (11.1+) include a version of STATSPACK that can run in the Active Standby database.  A different set of scripts is required to install the STATSPACK tables which are presumably created on the primary using the DML redirection. For example sbcreate.sql creates the STATSPACK tables. Snapshots can be initiated on the standby. At present there are no plans for Active Data Guard to support AWR on the standby.

See Note 454848.1 for more information about Standby STATSPACK

Role-Based Services

Role based services can be configured in Oracle 11.2 and above. Services are managed by Clusterware and the database trigger to enable/disable services based on the database role is no longer requried.

Design Criteria

The design criteria applied by the development team are interesting. These included:

• No compromize on ACID properties. There are no dirty reads; sessions can only see committed transactions. Repeatable reads (e.g SET TRANSACTION READ ONLY) return consistent results.
• A query on the standby must yield identical results to a query on the primary at a given SCN. Undo is applied to rows on the standby to produce read-consistent snapshots.
• Metadata caches are synchronized at a query SCN. For example on the standby the data dictionary cache is synchronized after a package invalidation

All data types are supported for Active Data Guard including XML, LOB and user-defined types.

All versions of Active Data Guard support sorting and temporary tables. c Additional TEMPFILES can be created in the standby database for sorting. Active Data Guard also supports cursor-duration temporary table transformations such as the WITH clause, sub-query factoring, grouping sets, star joins etc.

Miscellaneous

An Active Data Guard database can be transitioned into a standby database and vice versa.

In Oracle 11.2 there can be up to 30 active standby databases.

Cascaded standbys do not work with with the Data Guard Broker. Cascaded standbys do not work with RAC prior to Oracle 11.2.0.2. In previous versions it was possible to have unresolvable gaps in RAC cascaded standbys.

Materialized view refreshes must always be performed on the primary; Oracle Streams cannot capture from the standby database. This will never change as Goldengate is now the strategic direction.

Active Data Guard handles skewed activity in RAC where one redo log thread performs more frequent log switches than the others.

Query Transformations

I have seen this presentation by Jože Senegačnik a couple of times before, but it just keeps getting better and better. If you are interested in optimizer internals, don’t miss it.  Jože starts with the list of query transformations that appears in 10053 trace and works through about the 10 most popular transformations describing the purpose of each and, where possible,  showing an example.

The list of transformations covered includes:

•  Subquery unnesting (both anti-joins and semi-joins)
• Filter push-down
• View merging (both simple and complex)
• Predicate move around
• Join predicate push down
• Join factorization
• Join elimination
• Set join conversion
• Order by elimination
• Count(col) to count(*) transformation

Join factorization is a new transformation introduced in Oracle 11.2.0.1. It allows subqueries in a UNION statement to be merged using an inline view with a system-generated name in the format VW_JF<name>. More information on this transformation can be discovered by searching Google for the patent.

Set join conversion is controlled by the hidden parameter “_convert_set_to_join”. The default for this parameter is still FALSE in Oracle 11.2.  The transformation can be enabled by setting the parameter at SESSION level, in an OPT_PARAM hint or using the SET_TO_JOIN hint.

This presentation can be downloaded from the OOW 2010 site. I have looked on Jože’s site (http://www.dbprof.comf) , but at the time of writing  his presentations were not available for download from that site.

Summary

So that’s it – Oracle OpenWorld over for another year in San Francisco at least. Oracle are also planning to run OOW in Latin America and China  this year. I guess it is only a matter of time before they are running OOW in India.

Highlights – well as always the chance to see old friends again and some new networking opportunities. I know a lot of people complain about the presentations at OOW, but at any time there are at least 10 server technology presentations running concurrently; the problem is working out which to attend. Unlike UKOUG there are only five one hour slots for sessions a day; other slots are reserved for the keynotes. This can be a bit frustrating, but I suspect five hours of education a day for five days is sufficient.

I attended around 20 sessions over the five days; so why did I not blog about all of them. Well a couple were sales oriented,  a couple had misleading titles and  a  couple had verysimilar content to last years presentations. But that still leaves about 3 hours of useful material per day; enough to keep me happy.

My ticket was free because I am an Oracle Ace Expert (thank you Lillian). The flight cost around £800 and the hotel another £1200, so the entire trip cost a little over £2000. Difficult to justify in terms of the content alone; easy to justify when you consider the networking and social aspects. As I am heavily involved in the UKOUG Conference, it is nice to go to an event where I have no speaking or other responsibilities.

The dates for OOW in San Francisco are 02-06 Oct 2011. Please check these dates on http://www.oracle.com before planning your trip.

Oracle OpenWorld – Day 3

Wednesday was by far the most unproductive day, mainly due to the Infosys and Oracle keynotes overrunning.  A message was sent to all delegates saying that the final session of the day was delayed by 45 minutes. Unfortunately this message was not received by the presenters of the session I attended, so I missed the first part.

DB Time

In the morning I attended a presentation on DB Time with John Beresniewicz, Kurt Engeleiter and Cecilia Gervasio. Unlike other presentations at OOW, this was a dialogue between the three presenters which I think is a really good technique to use on the fourth day at a conference.

Oracle sees DB Time as the culmination of instrumentation in the database, starting with the system and session statistics, YAPP, wait events and ultimately wait events. Much of this material is already in the public domain, but it was good to see it again.

The standout slide though was one which perfectly describes how the instrumention of I/O behaves when the system is suffering from CPU starvation.

When the system is suffering from CPU starvation, I/O wait times will be overstated. This is because, when the I/O wait completes, the process has to wait on the run-queue until it can be scheduled again, at which point it issues the system call to check the end time for the I/O.

A couple of other points of interest:

• Real time SQL monitoring is available in Oracle 11.1.0.7 and above
• In Oracle 11.2 and above, the AWR report now includes the top five FOREGROUND events; in previous versions this report listed the top five timed events which could include waits by background processes.

RAC Performance Management

This presentation by Michael Zoll and Sanjay Singh included a detailed investigation of RAC internals (much more so than the mis-named RAC internals presentation on the previous day).  This is a presentation that needs to be downloaded from the conference website and examined carefully as there is a lot of detailed content particularly on latencies.

A couple of additional nuggets included:

• Storing CRS files (OCR and voting disk) on block devices and raw devices is deprecated in Oracle 11.2.0.2. This assertion needs checking simply because I did not hear it anywhere else during the event. I guess deprecated means that they are still supported when the 11.2.0.2 patchset is applied to an existing 11.2.0.1 cluster.  I have always advised my customers to create the OCR and voting disk in ASM (if configured) when installing 11.2.0.1 because I think there is a higher level of risk when they are moved later on
• Set event 10899 system-wide to alert when the threshold for global cache latencies is exceeded. I have never experimented with this event, but it must be useful to get included in an OOW presentation (where hidden parameters and internal events are rarely mentioned)

Oracle OpenWorld 2010 – Day 2

Tuesday at Oracle Openworld.

Execution Plans

I attended two presentations on execution plans by Maria Colgan. I already understood most of the content, but it is great to see it presented in such a clear and concise way. My only complaint is that Maria repeatedy referred to the optimizer as “he”. Quite clearly the Cost Based Optimizer is a female; it is difficult to understand, unpredictable and it makes some really strange decisions.

Maria gave two presentations, but with similar structures. The second presentation was basically an advanced version of the first, so I have summarized then both here.

The optimizer team maintains a blog : blogs.oracle.com/optimizer

Optimizer decisions depend on the execution type. For serial execution optimizer decisions are based on cost and cheaper plans are preferred. For parallel execution optimzer decisions are based on performance and faster plans are preferred, irrespective of cost.

Four main concepts apply to execution plans:

• Cardinality
• Access Paths
• Join Type
• Join Order

Cardinality

Cardinality is an estimate of the number of rows returned by an operation, based on statistics. At its simplest cardinality equals  total number of rows divided by number of distinct values.

By default Oracle execution plans report expected cardinalities. Actual cardinalities can be reported by including the GATHER_PLAN_STATISTICS hint in the statement and executing it.

Output can be displayed using:

SELECT * FROM TABLE (
DBMS_XPLAN.DISPLAY_CURSOR (format =>’ALLSTATSLIST’)):

The output from this query includes both the number of expected rows and the number of actual rows.

Actual cardinalities are also reported in SQL Monitor which is part of the SQL Tuning Pack (and therefore a cost option).

When Oracle generates a bad execution plan it is usually due to incorrect cardinalities. Common causes iof bad cardinalities include:

• No statistics or stale statistics
• Data skew
• Multiple single column predicates on a table
• Multiple columns used in a join
• Function wrapped columns
• Complex expressions

These are discussed in more detail below:

• No statistics or stale statistics. This can be overcome by dynamic sampling; better to gather statistics if possible. Watch for out of range issues caused by stale statistics (particularly affecting dates and sequence generated columns).  The DBMS_STATS COPY_TABLE_STATS procedure copies a partition and automatically updates the minimum and maximum values of the partitioning column. It does not however update global indexes
• Data Skew – Use histograms. In Oracle 10g and above statistics will automatically be gathered for a column unless either the METHOD_OPT parameter specifies different behaviour or the column has not been used (COL_USAGE$)
• Multiple single column predicates. For example a statement might include the predicate WHERE state = ‘CA’ and country = ‘USA’. As California must be in the USA, the second predicate is adding no value, yet by default the optimizer will assume that it does. The solution to this problem is to gather extended statistics (introduced in Oracle 11.1)
• Multiple columns used in joins. For example WHERE a.state = b.state AND a.country = b.country. Again the solution to this problem is to gather extended statistics
• Function wrapped columns e.g. UPPER(name) The optimizer does not know how the function affects values in the column so it guesses the cardinality to be 1% of rows. Once again the solution is to create extended statistics.
• Complex expressions. The optimizer will not use extended statistics if the predicates include non-equality expressions (such as < > <= >= or !=). Dynamic sampling is the only solution.

Note that extended column statistics can be used if all columns appear in the leading edge of the column groups. So for example if a column group consists of columns A, B and C then extended statistics can be used statements including predicates for columns A and B, but cannot be used for statements includig predicates for columns A and C.

Some recent optimizer enhancements include:

In Oracle 11gR2, if dynamic sampling is enabled, then for parallel execution only, the optimizer will select an appropriate level of dynamic sampling. In a Real World Performance Group example the OPTIMIZER_DYNAMIC_SAMPLING parameter was set to 2, but the optimizer increased this value to 6 for a specific query. The level used is reported in the notes section of the output of DBMS_XPLAN DISPLAY_CURSOR

In Oracle 11.2.0.2 and above column groups can be automatically created. Initially the optimizer must be instructed to gather information about possible column groups. This is achieved using

DBMS_STATS.SEED_COL_USAGE (NULL,NULL,300);

The above command will collect data about column groups for 300 seconds.

A report can be generated about column usage in the CUSTOMERS table using

SELECT dbms_stats.report_col_usage (USER,’CUSTOMERS’) FROM dual;

Extended statistics can be created on column groups identified for the CUSTOMERS table using:

DBMS_STATS.CREATE_EXTENDED_STATS (USER,’CUSTOMERS’);

Extended statistics will automatically be gathered for the new column groups whenever statistics are gathered for the table.

Access Paths

There are nine main access paths

• Full Table Scan
• Table Access By ROWID
• Index Unique Scan
• Index Range Scan
• Index Skip Scan
• Full Index Scan
• Fast Full Index Scan
• Index Join
• Bitmap Indexes

This is a slightly simplified view as almost each access path has a number of options. A large number of these are described on my website (http://www.juliandyke.com/Optimisation/Operations/Operations.html)

A couple of Oracle recommdations

• In Oracle 10.2 and above do not modify the DB_FILE_MULTIBLOCK_READ_COUNT parameter. The optimizer will select the most appropriate value.
• Do not set the OPTIMIZER_INDEX_COST_ADJ parameter as this affects all statements executed against the database which is rarely appropriate.
• Remove any underscore parameters or non-default parameters prior to testing when upgrading to a new platform or Oracle version

If Oracle appears to be selecting the wrong access path for a table, check the cardinalities.

Join Types

There are three join types

• Nested Loops Joins
• Hash Joins
• Sort Merge Joins

In addition there are two join methods

• Cartesian Joins
• Outer Joins

Again this is an oversimplification; see my website for a more comprehensive list of join methods.

Cartesian joins are a problem when a statement has missing predicates in the WHERE clause. However Cartesian joins can be acceptable or even optimal in DSS environments where there is a large fact table together with a number of smaller dimension tables. In this case the most efficient plan is often to perform a cartesian join between the dimension tables and then to join the resulting rows to the fact table.

A common cause of incorrect paths is the degree of parallelism on a table; sometimes this is set during batch jobs, but not subsequently unset due to errors or failures. An alternative solution is to use the following command to set the degree of parallelism for the current session:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 100;

Join Orders

Join order affects performance of an execution plan

• Joins guaranteed to produce at most one row are always performed first
• Outer join tables must come after inner join tables in the same predicate(s)
• If view merging is not possible, all tables in the view will be joined before joining to tables outside the view.

I use the latter as a method of controlling SQL execution, including the NO_MERGE hint in subqueries that I want to execute first.

In Oracle 10g and above there is a new feature called optimizer secure view merging. When this feature is enabled I believe that view merging will only be performed where the owner of a view is the same as the owner of the tables.  This feature is controlled by a hidden parameter which may account for some differences in behaviour when upgrading to Oracle 10g+ from earlier versions

To find the join order use

SELECT * FROM TABLE
(DBMS_XPLAN (format=> ‘TYPICAL +OUTLINE’));

Look for the LEADING hint in the output; tables will be joined in the order of this list reading from left to right.

Oracle OpenWorld 2010 – Day 1

Monday is the first full day at OOW. The first presentation I attended was a keynote by Andy Mendlesohn who is in charge of Server Technology. Worth attending to see where Oracle are currently placing emphasis.

The major new announcement so far at OOW is Exalogic which as the name suggests is effectively Exadata for the application layer.

However the Exadata Database Machine has undergone a couple of changes. Firstly the name for the existing product has changed from Exadata Database Machine V2 to Exadata Database Machine X2-2. I believe that the suffix indicates the number of  processors in each server. The x2-2 continues to be available in quarter, half and full rack configurations (2, 4 and 8 database servers respectively).

A new product has been announced called Exadata Database Machine x2-8. This consists of two servers each with eight 8-core Intel processors (a maximum of 128 cores) and is aimed at the high end of the market. Each server contains 1TB of RAM. Storage consists of 14 storage servers connected by an Infiniband fabric. The storage servers now use Intel 6-core CPUs.  In this version the operating system can be Linux or Solaris. A performance comparison between the two operating systems on identical hardware would be very interesting. Of course these machines are going to be extremely expensive and incredibly rare, but someone has to be paying for OOW.

Other features covered included the OCFS – Cloud Edition. The slide claims that this supports database files, operating system files and Oracle binaries; if this is true then OCFS – Cloud Edition is more than just a renaming of ACFS as the latter does not support database files for performance reasons.

Oracle have also introduced a Database Firewall. In principle this sounds quite similar in concept to Sentrigo Hedgehog as it allows statements to be monitored and trapped prior to execution.

There have been several mentions by Oracle about Exadata Hybrid Columnar Compression. Obviously there is less interest from the community in this feature as it is difficult to test and not applicable in non-database environments. Oracle suggest it works in two modes:

• Warehouse Compression – optimized for speed. Example  10x average storage savings, 10x reduction in scan I/O
• Archive Compression – optimized for space. Example 15x average storage savings – up to 50x for some data.  Some access overhead. Intended for cold or historic data.

Somehow DBFS has slipped under my radar. It is a database file system that is based on Oracle Securefiles. I have yet to establish whether it is a cost option. Only available on Linux it is a shared file system providing storage for ETL, staging, scripts,  reports and other applicaiton files (so very similar to ACFS). Files are stored as Securefiles in database tables, so can be backed up, propagated to standby environments etc. Perhaps the most interesting feature is that DBFS provides a very fast way for the database to ingest data by using ftp/scp to load flat files into external tables. Good for environments where NFS is proscribed.

In the afternoon I attended a couple of presentations by the Real World Performance team which is led by Andrew Holdsworth and includes Mike Hallas, Bjorn Engsig and Greg Rahn amongst others.  Their presentations are always worth attending as there is no spin and no marketing content, just advice on maximizing investment in the database.

Andrew Holdsworth’s starting point this year was that the database should always be CPU-constrained; any other state represents a waste of Oracle licences. His closing point was that “good enough is not good enough”  and that performance specialists should attempt to exceed Moore’s law to consider their efforts successful. This is a very good objective though I suspect that for most customers the realities differ mainly because of constraints of changing all components in a system at the same time. For example, one might upgrade servers to have faster CPUs, but it is unlikely that the network or storage will be upgraded at the same time in an existing. system.

A demonstration had been prepared which introduced different performance issues (too many sessions, bad SQL, cursor leaks, connection leaks) and was intended to demonstrate the impact on an Exadata server. In the event none of the issues really had any impact on the server though the testing methodology was very impressive.

They must have expected problems with the Exadata server (or more likely that it would get shipped to a customer) so they had a canned demo in the Powerpoint pack. The most interesting thing these slides showed was that the wait events generated by each type of failure had almost no direct connection with the induced issue.

For example too many processes resulted in very high buffer busy waits; too many logins resulted in high cache buffer chains latch waits. I have seen both of these wait events recently at customers and it is very difficult to diagnose the root cause based just on wait event analysis.

Some other observations during this presentation and the subsequent panel session included:

• Exadata is efficient because the storage server filters columns and only returned those required by the query; this is in addition to the application of predicates in the storage server to filter rows. Therefore the minimum amount of data is returned over the network.
• In Exadata (and possibly non-Exadata) use CTAS or IAS instead of UPDATE, MERGE or DELETE commands during ETL
• DSS – memory may be better allocated to PGA rather than buffer cache.
• Use connection pools – use a few sessions to support multiple concurrent transactions. The demonstration used 48 session to support 12000 transactions per second for a cached workload. Increase the number of sessions as the amount of I/O required to support the workload increases. The number of processes is the most important factor when Oracle are performing benchmarks.
• The RULE hint will not be deprectated in the foreseeable future as the data dictionary still depends on it.
• The same optimizer is used for Exadata and non-Exadata. Exadata uses the cost model. However, the best plan may differ between the two environments. In Exadata full table scans and hash joins appear more frequently in execution plans. Exadata still needs valid statistics.
• There is no DBA_HIST_SESSTAT view because of data volumes, but also because if there are hourly snapshots and sessions are volatile, only a subset of sessions would be captured in the snapshot (actually I think ASH is intended to provide this data).
• Instance caging where the CPU_COUNT parameter is used to restrict the number of CPUs that an instance can use has been testedby the team and does work. Instance caging was introduced in 11.1 and has been backported to some versions of 10.2
• However in 11.2+ the Resource Manager has a maximum CPU utilization parameter that is much more granular as a percentage value can be specified (1%-100%). This is recommended for 11.2 and above.
• Inactive sessions do have a small negative impact on CPU and also consume memory, so should be avoided if possible
• Cardinality feedback – in 11.2 the optimizer gets feedback from each row source. These statistics are used the next time the query is parsed.

Oracle OpenWorld 2010 – Day 0

Sunday at OpenWorld is IOUG day; the presentations are selected by IOUG but there is a lot of Oracle input and co-operation. So what did I learn?

Oracle 11.2.0.2 was released last week (mid September) for both Grid Infrastructure and RDBMS. The documentation was updated at around the same time.

Oracle 11.2.0.2 is an out-of-place upgrade. This means that a new Oracle home will be created for Grid Infrastructure and another for the RDBMS software. Obviously it may be necessary to free up or add some disk space on some servers prior to installation. I am not yet sure how configuration files in 11.2.0.1 will get copied across to the new homes.

There is a lot of new Grid Infrastructure functionality “under the covers” in 11.2.0.2 including:

• QOS Management – Quality of Service. This is a feature Oracle have been talking about for a couple of years. It is basically a resource manager for clusters.  Last year I was told that server pools etc were the foundation for QOS which did not make 11.2.0.1. Initially I understand that QOS will only make recommendations which the DBA can choose to act upon. In subsequent releases the process may become more automated. The big shock this year is that Oracle has announced that QOS will only be available on Exadata / Oracle Database Machine.
• Redundant Network Interfaces. Oracle will support multiple private interconnects without the need for bonding.  An additional VIP will be created on the private interface to support this feature. I guess Linux users will not be particularly interested in this feature as bonding works OK at operating system level; Solaris users might get a little more excited as there have been some nasty problems with IPMP in 11.2.0.1 requiring patches from MOS.
• Node Evictions following loss of interconnect. Clusterware will now attempt to avoid rebooting a node following an interconnect failure. If possible the Clusterware stack will be stopped; until some conditions a reboot is still unavoidable. This change is intended to improve diagnostics of node failures. It has also been requested by users running software other than Oracle on their cluster nodes.
• Node Evictions – IPD/OS (the Instantaneous Problem Detector) previously available on MOS has been incorporated into the Cluster Health Monitor which is distributed with 11.2.0.2. I noticed last week that a standalone version could be downloaded from www.oracle.com/downloads. The Cluster Health Monitor requires an additional 1GB of space in each node to store a Berkeley database which it presumably uses for data.
• Node evictions – in 11.2.0.2 the LMON process can ask the local CSSD daemon to evict an instance on another node; the local CSSD daemon will send a request to the remote CSSD daemon which presumably performs the eviction. This is known as a “member kill” (Alex Gorbachev)
• ACFS – the ASM Cluster File System has been renamed the Oracle Cluster File System – Cloud Edition. Perhaps the word Cloud is going to replace the word Flash as the most overused one in the Oracle lexicon
• ASM – Asynchronous rebalance. In 11.2.0.1 and below ASM kicks off up to 11 rebalance processes (the number of processes is specified by the ASM_POWER_LIMIT parameter). In Oracle 11.2.0.2 there is only one ASM rebalance process which uses asynchronous I/O to perform the rebalance. The ASM_POWER_LIMIT parameter controls the number of parallel threads and the limit has been raised from11 to 64.
• ASM – The copy command has also been enhanced and is now multi-thread. Apparently it is now faster than the dd command.
• ASM – The file allocation algorithm has been optimized; no details on this yet
• ACFS has been enhanced to include encryption, security realms and tagging.

During the RAC SIG expert panel a few informal recommendations were made by the panel including:

• Extended Clusters – max successful distance appears to be 50-60 miles though latency and bandwidth are more important than distance.
• Interconnect speeds – OLTP 1 or 2 GB is almost always sufficient; for DSS / Mixed workloads 10GB or faster is recommended (including Infiniband). Exadata uses 40GB Infiniband.
• Number of nodes – Oracle recommended more than 2 and less than 100. The largest known cluster currently contains 36 node in an extended cluster configuration. Sandesh Rao (RAC Assurance team) told me that they rarely see clusters with less than four nodes now

Application readiness for RAC. Things to look for in a single-instance application include:

• Right growing indexes – prepend instance ID to ensure instances use different blocks
• Sequences – increase cache sizes; defaults have been increased in 11.2

GPNP profile versus OLR – The GPNP profile is used during cluster startup to identify the location of the voting disks. The OLR is used for single instance clusters (Oracle Restart). CRSD also uses the OLR during startup.

SCAN Listeners – There have been no changes to the way load balancing is performed in Oracle 11.2. The only difference is that it is now done by the SCAN listener in addition to the local listener. The REMOTE_LISTENER database parameter should point to the SCAN listener. Oracle recommend use of Oracle-instrumented connection pools over the use of direct connections because this allows them to use the in-built FAN mechanism to manage load balancing.