UKOUG 2009 – Wednesday

Not too late an evening for me on Tuesday, though had a really informative discussion with Bryn Llewellyn on Edition Based Redefinition. He made the point that if you have two versions of the application running, you have to be careful where you users are pointing to, if they have information in their sessions. You may need to have the control over which users connect to where.

This morning I found out I had managed to pack only 1 sock. I hope no one noticed me going sockless in a freezing cold December day in Birmingham.

The first talks of the morning started quite late, but I was quite glad of it. It was a tough choice between Larry Carpenter, Bryn, and Tanel Poder. In the end I went with Tanel.

Pratical Oracle Capacity Planning – Tanel Poder

Tanel was doing essentially 4 hours of presenting today, which is amazing in itself, but when you consider the detail of the information he provides it’s just staggering.

This was a 2 hour masterclass, and it was outstanding.

Tanel is making the point about if you have many different queries going on, on your system it’s very difficult to make a mathematical model to predict the response time using queuing theory. Much easier if you have 1 unique query – queuing theory is much more practical for this.

Data collection

statspack

awr – enable longer retention period see retention in dba_hist_wr_control

change retention with dbms_workload_repository.modify_snapshot_settings (retention => minutes)

services can be useful for finding out which application is consuming the resourcex, this can allow you to characterise your workload.

Can use V$service_stats to obtain statisics on the resources used by the various services since instance startup time.

v$servicemetric – basic metrics per service, including cpu per call, elapsed time per call, also in ASH.

Advising using dbms_application_info so you can obtain what resources each part of your application is consuming.

workload characterisation

performance/resource consumption analysis

basic utilisation forecasting

data visualisation

Tanel spent quite a large part on various examples of visualising data.

Tanel demoed his excel persheet, which allows you to automatically graph in excel various different metrics. Demo showing disk usage with a linear regression to predict futrue usage.

Tanel stating for cpu usage you might want to plot the usage at a particular time each week, as cpu flcutuates so much. You can then run linear regression on these times to make a prediction.

Gathering cpu usage data from V$osstat, and dba_hist_osstat.

Right at the start Tanel asked how many people were using queuing theory to do capacity planning. No hands went up, but I wonder how many people are doing any form of capacity planning. I found this to be a really illuminating presentation, and I’ll certainly be applying some of the ideas covered when I’m back in the office.

The lunch box was getting quite a lot of derisory comments. In particular, you could spot the Scottish person avoiding the fruit and heading straight for the KitKat. Though Pythians Paul Vallee was also rather disdainful of the fare on offer. In fact it was very interesting hearing some of the Pythian backstory. It’s an impressive thing Paul has built there (some 75 DBAs) and a fascinating story of how they have got to where they are.

The Oracle Wait Interface is useless (sometimes) – James Morle and Tanel Poder

Here they were showing that sometimes, there are problems where the Oracle wait interface can’t help. They emphasised this is the first place to look, but there are places that are not instrumented and there can be situations where Oracle itself can’t tell you what is going on, so you have to look outside the database.

Tanel showed pstack which can show which Oracle functions the process is using, while james was doing some demos with DTrace.

That was it from me, it’s been a lot shorter sojourn in Birmingham than the last few years, but it’s been great to see lots of familiar faces.

UKOUG 2009 – Tuesday

It always seems cold when it’s time for the UKOUG annual conference in Birmingham, and duly it turned out to be the coldest day of the winter so far. It seems somewhat eerily quiet at the conference today, but maybe that is to do with the ukoug holding multiple conferences throughout the year for the varios apps, unlike last year.

Tuesday was my first day this year, so with the conference being down to the 3 days, it’s going to feel especially brief.

Evaluating and Testing Storage Performance – Luca Canali

This was about how to do performance testing and ensuring the stability of new storage hardware. Luca seemed pretty excited that the LHC is finally coming back on stream, I guess it means floods of data to store.

When testing for new hw test for performance and stability often critical metric is small-read random IOPS

CERN have around 2000 disk drives and this gives them typically 1 disk drive failure per week. You have to design your systems to cope with failure.

using Oracle’s ORION for testing storage. Uses ASYNC i/O direct to storage (as ASM would do)

uses 8KB i/o sizes for random i/o

ORION does not use as much cpu as testing using oracle itself

graph showing fc and iscsi random iops fc scales linerally while the iscsi does not

also graph with sequential large i/o fc scaling well while the iscsi does not

this was 1 Gb ethernet

also tested 10 Gb (though the storage was “CERN made” rather than vendor puchased)

tested upto 42 drives. Finding ISCSI performance is variable from different linux versions

again have to test using real-world oracle workload

For capacity planning the view v$sysmetric_summary gives read/write I/O requests.

Oracle Advanced Compression in 11gR2 – Dan Morgan

Sees customers with databases in the 400-800 TB size.

Often as data volumes expand performance declines

compression can give a way out of that, and it is a trade off between cpu and disk i/o

Advanced Compression in 11g gives you:

dataguard network compression

data pump compression

rman compression

OLTP table compression

secure files 11gR2 deduplication (dan is using for storing email in DBFS

Interesting discussion about Hybrid Columnar Compression, it offers far greater compression ratios but is restricted to running on exadata storage. It appears this was pulled from the normal Enterprise Edition release at the very last minute. Dan was saying, it may be because the exadata boxes may have enough cpu to handle the compression but that having to do this on your server would be painful.

Still seemed like it may have been a bit of marketing decision.

11g for developers – Connor McDonald

This was an outstanding presentation. Connor managed to rattle through some 300+ slides many of which were extremely funny.

using a gapminder like anmiation to show database code v application code

querying from a blob/clob via sql

set errorlogging on

gives an audit trail of what has failed

Vital Statistics – Julian Dyke

In 11.1 there are 107 routines within the DBMS_STATS package

DBMS_STATS can collect in parallel, but it cannot do a validate structure.

Incremental stats gathering in 11g allows you to just collect statistics on new partitions will using the “synopsis” from older partitions that have not changed.

Compressing very large data sets – Luca canali

This was Luca’s second presentation of the day, and this was a superb one.

compressing data can mean less physical i/o, less logical i/o but will consume more cpu.

Can be useful for data that has an active part with older data made read-only

CERN tested an exadata machine for a couple weeks, particulary interested in the hybrid columnar compression (archive) . Interesting graphs showing the advantages of various levels of compression on various CERN datasets, and several levels of compression.

For basic and OLTP compression the format of the data block is the similar to a “normal” block but uses a symbol table. Luca showing some dumps of rows with compression enabled on them.

OLTP compression is not limited to direct load operations. Allows normal inserts into the table. Block is compressed when it reaches the pctfree vailue.

Hybrid columnar compression uses a completely new block layout. Utilises a compression unit (CU) and data from the same column within the compression unit is stored together. This basically increases how much compression is possible.

Doing DML on hybrid columnar compressed data, means a lock effects the whole CU.

Rows not identifiable in a block dump when compressed with hybrid columnar compresssion

index lookups use more consistent gets than with no compression.

Compression factors vary very much with the data. He seems to like both oltp and hybrid columnar compression.

Luca even mentioned the possibility of turning hybrid columnar compression on when using non-exadata storage, but emphasised that was just for playing with, not production usage.

Active Dataguard Best Practices – Larry Carpenter

This was another outstanding presentation.

apple iTunes replacing their logical standby infrastructure with an active dataguard install.

Control how much lag is acceptable to an application

have active dataguard fix corrupt blocks this works for corruptions on either primary or standby (though an application has to access the corrupt block)

need to use services to determine how your application connects to the standby/primary

note on running statspack on your standby (standby statspack) no64 454848.1

broker in 11.2 allows one command to turn on active dataguard, also when you switchover to an active dataguard instance, it will start active dataguard on the now old primary!

Query lag in 11.2

v$standby_event_histogram view

allows you to define an SLA for an application using session setting STANDBY_MAX_DATA_DELAY

use a logon trigger to set this for an application.

A good day of presentations.

After the last talk of the day, when I turned my mobile phone on, I got a message saying my hotel room had been cancelled at the copthorne, and another booked at jury’s inn. Now, I’d actually checked into the copthorne earlier, so I’m either going to end up with 2 rooms or none. If I do have to share my room at the copthorne, I do hope they don’t snore.

Increasing the Dataguard Protection Level

I thought I understood testing. Before I run anything in my production environment, I’m utterly strict that I test in a non-production environment first. Does not matter where that change comes from, it is always run into test first. This naturally includes any changes at the database level, rather than just inside a particular schema.

When I have a set of instructions or steps to take the database from one particular environment, or to install a particular feature, I don’t tend to test just 1/2 the steps, but generally, if I have a sequence of steps I tend to test the entire sequence.

Recently, I’ve been working on a project to increase the protection level of a dataguard environment from Maximum Performance to Maximum Availability. This is a 10gR2 environment, so I pull up the 10gR2 dataguard documentation. To me, the steps seem pretty clear. Let me highlight step 1:

Step 1 If you are upgrading the protection mode, perform this step.

Perform this step only if you are upgrading the protection mode (for example, from maximum performance to maximum availability mode). Otherwise, go to Step 3.

Assume this example is upgrading the Data Guard configuration from the maximum performance mode to the maximum availability mode. Shut down the primary database and restart it in mounted mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

It’s clear right? To upgrade the protection level, you have got to shutdown the instance and have it in the mount mode. I would not just run this in production, I’d always want to test these steps in my test infrastructure that was the same environment as my production setup. Question is, would anyone test upgrading the protection level, but just skip this step? Would it really occur to someone, oh, I wonder if I can just skip this first step and keep my instance up and running?

It did not occur to me, but then I read the (excellent) Oracle Data Guard 11g Handbook by Larry Carpenter, et. al. It’s pretty explicit that you don’t need to shutdown your instance!

The above was run on a 10.2.0.4 instance. I’d already set the log_archive_dest_n to a LGWR SYNC mode. One thing to note, you must explicitly set AFFIRM here as well, it’s not good enough just using LGWR SYNC, as NOAFFIRM is the default and this leads to the protection_level being in continual resynchronization.

Resynchronization occurs when you first increase the protection_mode or when there is a network outage. It means your configuration is effectively at that point running in maximum performance, and while the protection_level is not at maximum availability the potential exists for data loss.

This really does contradict the documentation so this has been a really useful find for me, as just following the documentation would have led to me having to take downtime on my RAC cluster. It has always been the case that you can drop the protection level without incurring downtime. Note to go all the way to MAXIMUM PROTECTION still requires the database to be in the mounted state.

Netbackup 6.5.4 and Oracle 11gR2

I’ve been involved in testing for upgrading a database to 11gR2 for a while, and I was quite intrigued to see this posting from Mark Bobak. This site uses Netbackup 6.5.4 and the Netbackup database agent to do the backups so this becomes a bit of a showstopper if I can’t backup the database. When you try and run a Netbackup backup against an 11gR2 instance you find the following happens:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x14] [PC:0x3BA6B70D00, strcpy()+16] [flags: 0x0, count: 1]
Errors in file /opt/oracle/diag/rdbms/test11g/TEST11g/trace/TEST11g_ora_2778.trc (incident=29067):
ORA-07445: exception encountered: core dump [strcpy()+16] [SIGSEGV] [ADDR:0x14] [PC:0x3BA6B70D00] [Address not mapped to object] []
Incident details in: /opt/oracle/diag/rdbms/test11g/TEST11g/incident/incdir_29067/TEST11g_ora_2778_i29067.trc

Searching in Metalink for this ORA-07445 error produces document 959015.1. This basically says that yep, the latest version of Netbackup is not compatible with 11gR2.

So do these guys get on the Beta programme and test their software?

Symantec have stated it will be might be fixed in 6.5.5 and this will have “first availability” from first week of November. However General Availability won’t be until second week of December.

Symantec stated it will be fixed from the next major Netbackup release which is scheduled for first week of February.

Oracle OpenWorld – Thursday

Well, a 10 hour plane journey has been undertaken since the last day of OpenWorld actually happening and me typing this up, so it’s all bit old news really. After an evening spent with Roger Daltrey, as opposed to Aerosmith, I was not entirely appreciative of several hours more of technical presentations.

Which was a shame as on Thursday I went to a couple of quite technical presentations and a cracking round table discussion.

ACFS: The Awaited Missing Feature – Matthias Pölzinger

This was quite a deep delve into the ASM Cluster File System. Quite technical for OpenWorld, and Matthias certainly knew his stuff.

Oracle and cluster file systems

Bit of a history of Oracle and cluster file systems

First clustering attempts in parallel server 6.2

RMS or raw

CFS certified in 9i

OCFS project

ASM released with 10.1, but only limited support for certain file types.

the missing component

ACFS is posix compliant can store oracle homes, application data

ACFS support NFS/CIFS built on all standard ASM storage management features

online file system expansion

snapshotting built in.

structure of ASM CFS

diagram – might have been better to fly pieces in that slapping it all at once.

ADVM = ASM Dynamic Volume Manager

now showing a nice diagram having split up some disks into an APPDATA diskgroup, some disks into a GRIDDATA (OCR/Voting) Diskgroup and finally some disks into the DATABASE datafiles diskgroup

you can create ext3 filesystem upon an ADVM volume.

3 new kernel modules

oracleacfs

responsible for all acfs file/directory operations

oracleadvm

presents block devices for advm volumes

oracleoks

memory allocation

synchronisation

There are quite a few new ASM background processes.

fields of application

share external data, bfiles, external tables, external files.

multi-node fault tolerant data loading

shared database oracle homes

licensing

server OS has valid OEL support contract

OR

one cluster node is correctly licensed according to SE or EE

advantages

no need for 3rd party CFS or NAS. Will be a uniform platform independent solution, will eventually be rolled out to other platforms, currently only linux.

I’m suspecting it might be endian-neutral

Implementation example

showing an example of going from disks to provisioned acfs. Using xml file to create the diskgroup. Using asmcmd and the mkdg command.

benchmark

bonie++ benchmakr comparing both OCFS2 and ACFS. using NettAPP filers for the storage and a 2node cluster. graphs showing comparison 74MB to 48 for reading,while 51 to 36 for input. ACFS is considerably faster according to this benchmark, that is 50% speedup. This sounds like quite a potential speedup for anyone using OCFS2

ACFS does not support directio.

There was even a good set of questions at the end, though there does seem to be a lot of confusion over what you can and what you cannot store within ACFS.

Next up for me was:

Oracle Net Services: Best Practice – Kant Patel

Quite a lot of really useful information in this one on who to get the maximum performance and availability out of your listener, and how to maximise the scalability of it. There was a lot of information, just wish it had come earlier in the week and I might have made a bitter stab at capturing more of it. I’ll certainly be going over the slides (on demand registration required) at some point.

Again there was also a mass of questions, which always seems to add a lot of value to a presentation, however, I really think it’s quite rude for about 3/4’s of the audience to stream out chatting away. At least go quietly!

Real-World Database Performance Roundtable

I missed this last year due to flying home in the early afternoon, but I was leaving a bit later this year, and I think this session was on a bit earlier than last year. The format of this is superb, basically little cards are handed round and the audience is invited to write their performance questions, which then a top-notch panel from the Real World Performance Group then answer.

There was some good discussions on Optimizer statistic collection and on the various merits of Sparc T2 series chips compared to x86. The Oracle people were saying if you want to run multiple concurrent processes doing small amounts of OLTP work then the T2 chips were fine, though each individual thread goes at a much slower clock rate than, for example your Nehalem chips. Andrew Holdsworth though, very much put in the caveat that it is a rare system that exclusively does just OLTP type lookups, and most if not all systems have a mixed workload.

Very much came out that there are no magic bullets and it is very, very often that poorly performing SQL or badly designed systems are at the root cause of 99%+ of all performance issues.

This style of event where the audience ask questions on cards is an excellent one, and this event really worked quite well.

Off Home

Had lunch with Simon Haslam, and we both noticed that OpenWorld was starting to look a little bit deserted by lunchtime on Thursday. After that it was time for me to clear off to the airport, while trying to avoid the swarming hoardes of cops and security service personnel in town for Obama’s visit. Didn’t see him myself, sadly. I’m sure it’s only a matter of time before Larry has him up on stage during the Larry Keynote, 2010 anyone?

Oracle OpenWorld – Undestanding RAC Internals

Barb Lundhild made my day today. This was a fantastic in-depth dive into 11gR2 RAC. Certainly the best RAC presentation I’ve attended this year. And to top it off, I bumped into Barb in Moscone West and had a great conversation about her presentation – she is super smart.

Barb started by asking the audience which versions of RAC they were running:

handful running 9i

vast majority running 10.2

some running 11.1

NO one running 11.2 yet

Upgrading to 11gR2

Clusterware & ASM now live in the same home “Grid Infrastucture”

This is an out of place upgrade – this home is owned by ROOT – except she is now saying that the owner of your current clusterware home should be the owner of the new grid infrastructure home

This is recommended to be on local file system

Upgrade Clusterware and ASM to 11.2 at the same time

The idea is having the sys admins to manage the infrastructure, clusterware and storage, while the DBA manages the database

seems to be saying clusterware is rolling upgradeable from 10.2.0.3 need to shutdown ASM, making the point ASM NOT rolling until 11.1

saying that Clusterware and ASM should be upgraded at the SAME TIME except you could do the clusterware part rolling, but must then have the downtime for ASM (from 10.2)

Apply DBCA patch bug 8288940

pin the node

crsctl pin css -n nodename

OPROCD has disappeared ! That did not last long

OCLSOMON, OCLSVMON no longer exist either,

hangcheck timer not required either on linux

Massive diagram Barb has claimed it was “nice”, it’s a total spaghetti mess. I guess they don’t have access to designers in Oracle.

New De-install utility

Grief when trying to clean up, in the oracle home there is now a de-install utility, because if you have failed to install oracle RAC you really need to clean up first before starting again.

stops anything running
unlocks the grid infrastructure home
resets OCR/voting disks
removes contents in system directories
cleans up various oracle directory

best not running that on the wrong system

Managing 11gR2 databases

two management styles:

administrator managed: where the dba defines what databases run on which node, and dba defines which services run where

policy managed: big thrust to policy managed clusters define resource requirements of workload, enough instances started to support workload. big thrust to grid goal is to remove hard coding of a service to a particular node. This is like virtualisation for databases, you have a pool of resources that you can run your instances on, but do you really care where your db’s are running within the pool. Currently requires nodes to be the same capacity, no way of saying one node has more resources than anbother. have ability to define the min and max number of nodes a db can run on

seems like you could define a server to be sitting round marked as free just waiting to leap into action should a node fail. Hmm, not sure that is a great licensing strategy.

generic pool used for administering the “old way”

for policy managed databases SIDs are Dynamic

SCAN

for policy managed you don’t know which node is going to be hosting your instance. SCAN allows your clients to connect to the database without having to change entries in the tns when you add/remove nodes.

define SCAN in your DNS:

RAC-scan.example.co.uk

IN A IP1
IN A IP2
IN A IP3

3 IPs are required

even in a 2 node cluster you need 3

you could instead use GNS

this could make it easier for JDBC connections to connect to any node in a cluster with a shorter connect string.

SCAN will work fine with dataguard

tns entries can now use connect_timeout and retry_count to ensure quick connections to either primary or standby

Multiple public networks

apparently many customers wanted this, each network must have a network resource defined by clusterware, init.ora parameter listener_networks

instances will register a service with all networks

Managing OCR and voting disks with ASM

OCR and vote can now be stored in ASM, compatible parameter must be 11.2.0.0

Best practice is to use same diskgroup as database

disk discovery string stored in GPnP profile

cannot stop ASM unless you use stop cluster

OCR stored in similiar way to database file

only 1 ocr in diskgroup

can have ocr in multiple diskgroups

voting disks are created on specific disks and CSS knows their location

number of voting disks depends on redundancy

if you use external will only have 1 disk group

you can’t as yet put voting disks in multiple disk groups – which seems a bit of a regression.

voting disks automtaically backed up.

taling about quorum failgroup to have a 3rd voting disk in a multi-san environment

voting disks are backed up into the OCR

do not use dd to backup voting disks

If ASM dies and your voting disk and OCR are in it, then NO the cluster does not reboot

although if it fails on the node that is the OCR master that node may reboot

grid plug ‘n’ play

makes cluster more dynamic, easier to add/remove nodes
gns – lets the cluster manage it’s own network

Oracle OpenWorld – Wednesday

A couple of first for me today at OpenWorld. First presentation I was in, I actually fell asleep! I had not done that before at OpenWorld. I’m sure it was due to the bloggers meetup, but I nodded off and was dreaming I was about to get caught in the doors of an elevator lift. I then flailed my arms both in my dream (to stop the doors closing) and in reality, and at this point I came juddering awake. I’m sure everyone else was riveted to the action so probably no-one noticed.

The guy giving the presentation (it was a RAC one) did know his stuff, but somehow his material just was dull. The presentation really came alive when the questions started, you could really tell he knew RAC, and the 11gR2 changes inside out, and that made the question and answer session fascinating. But everyone was streaming out during this, and people were just going up to the front to ask the questions! Sheesh, it’s not difficult! Go to the mike to ask the question so everyone can hear what you are saying! There is clearly a huge amount of misunderstanding about 11gR2 and RAC. C’mon Julian get your finger out and get delivering the 11gR2 version of the book? We can’t all wait until next June!

Next up was Barb Lundhild’s RAC Internals presentation, and somehow the material just flowed in this presentation so much better.

And then another first, a presentation I would have left after about 2 minutes, if I had not got a wifi signal and decided to just do some online catch up. It was basically pointy-clicky ooh, we have a pack to fix this problem. I hated it.

And then to the Larry note. Quite a lot seemed to be what he had said on the Sunday night with Scott McNealy. I’m not too sure about the OCM and getting informed of patches, I can see it’s a nice idea, I’m just skeptical about this in a production system. As for fusion, sorry, I don’t care. It’s just not my bag.

Arnold was funny though!

Oracle OpenWorld – Tuesday

So Tuesday started with gales so hard that my hotel brolly got mangled on the fairly short journey from hotel to the Moscone center. In fact by the end of the day the wooden handle had snapped clean off. It’s going to be an interesting expense claim.

Apparently, my blog postings make it sound like I’m thinking OpenWorld is rubbish. Let me be clear, OpenWorld is fantastic. If you are a database professional, or even a “data processing professional” then this is the place to be. The opportunities for discussing Oracle with your peers, and straight from the people responsible for producing all this software is second to none. And that is even with the rain. It’s well worth begging, borrowing or stowing away to get to OpenWorld.

I did skip the keynotes today though. Never forget that OpenWorld is a marketing event, so you do have to pick an choose, and there is lot to choose from.

First session of the day for me:

Next Generation Grid Overview – Barb Lundhilds

I got a little bit distracted in this one, I should have realised from the title that “overview” would have been lots of generic slides. Clearly grid is getting a big marketing drive this year, and of course the phrase clusterware is out and “Grid Infrastructure” is in. ASM of course has moved from either it’s own home, or the database home into the Grid Infrastructure home.

This was the first talk I heard the word “VMware” mentioned. There was some talk about RAC One Node, but I’m saving that up for a separate blog posting.

Saw a screenshot of something called the cluster health monitor. I had never heard of this before and it does look a quite interesting tool, for diagnosing what is going on O/S wise that might be causing you a problem. A quick google turned up this page.

Next was:

Top Five Essential New ASM Features of 11gR2 – Charles Kim

Again I was a little distracted, but picked up the following interesting bits.

OCR and voting disks into ASM is not compulsory in 11gR2 but, you can’t put them on raw, so it’s either ASM, NFS or a cluster filesystem. Not sure what the reasoning behind that is.

The recommended AU size is now 4MB in 11gR2.

You need rhel5 to use ACFS .

Lots of improvements in ASMCMD. I think everything can be done through this. Lots of posix type filesystem commands available, including iostat. You can run rbal from asmcmd.

Metalink Note: 948187.1 just been released has everything you need to know about acfs.

You can’t put anything into acfs that you can put into ASM itself.

Discussion of the snapshotting capability – sounds a bit like zfs on this.

I’m not too sure about acfs – you can’t even store archived redo on it. I can’t see where I’d be fitting it into my organisation, however I bumped into Julian Dyke and he was convinced that it would be of use in the real world, so it must have something.

Current Trends in Real World Performance – Andrew Holdsworth

Doug Burns has a good write up of this one.

Andrew has quite a good way of presenting, with a nice turn of phrase. He started off by saying that he “deals with the more agricultural end of database performance“

Saying that you should try and keep load below the 60-65% level and that you have to think in terms of the probability of getting onto cpu straight away. If you are at 50% load then you have a 1 in 2 chance of being scheduled straight away. This falls the more loaded the system is.

Interesting graph of throughput while increasing the number of processes running per core. The scalability really drops off. At first he was showing the best, massaged tuned numbers. He then shows more “real world” numbers and the scalability can go into reverse very quickly. The database should be cpu bound, but each sql request should ideally be scheduled straight away by the O/S. There is an optimal number of processes for each app/hardware config.

two basic problems:

inneficient sql consuming too many cpus

two many active or transient connections established to the db

Keeps saying performance problems are not difficult, it’s either crap SQL doing too much work, or you are trying to support too many connections for your hardware. keep the database working within a safe envelope of performance load.

interesting point about sql plans breaking – spend lots of effort on clustering/dr but little on designing plan stability – an unable stable can take out your system just as effectively as component failure.

When giving control back to user make sure you release all database resources, return connections to the pool. Claims connection pools should have minimum and maximum values set to the same value.

resource manager has been misunderstood. It only kicks in by default when you are at 100% cpu. You probably need it to kick in at the 65% mark. In a system with multiple instances you can use instance caging to insure no one instance grabs all the resources of a server.

All, in all a thought provoking presentation.

And to top it all I got to meet Gwen (Chen) Shapira. The explanation of the “name change” and the consequences had me rolling on the floor laughing!

And so to the blogger meetup, it was pretty good stuff, we don’t know where Alex Gorbachev has the energy to do half the stuff he does, but I certainly wish I had half the amount of energy he has!

Oracle OpenWorld – 11 Things about 11gR2

So this was Tom Kyte on at the end of the day, and maybe because it was the last presentation of a long day, I think the room was possibly not as busy as I’d seen it last year. However, this was another good show.

Tom’s choice of the 11 features was quite eclectic, there was some stuff I had not heard about. Not many of these were the whizz-bang features that the marketeers would like to bang on about. Some of them seemed quite small, but with every example you were left thinking oh yeah, I can see how that makes life better.

There were actually only 10 different topics. Tom felt that Edition Based Redefinition is so good, that it needed to take 2 of the 11 spots in his presentation!

I was gobsmacked by how few people have heard of this. Tom really thinks this is the one feature that if your manager asks “Why should we upgrade to 11gR2 ?” that you should respond with. I really think it has the potential to be an absolute game-changer. It’s the killer feature.

diy parallelism

This uses dbms_execute_parallel

incrementally modify a table in parallel break up table into chunks and then do several of the chunks at once – as opposed to what parallel query does which is run all parallel threads at once

each of the bite sized chunks commits independently so that an update does not lock up an entire table at once.

analytics

Tom’s really liking listagg, saves lots of lines of code from 9i and 10g

likes nth_value

execute on a directory

external tables allow for a preprocessor – program is run when you select from external table

use this to decompress a file when you perform the select on an external table that uses the execute

Tom showing how to do an ls in a directory running just select * from ls – which is using this feature to produce the listing of a directory

Recursive Subquery factoring

ANSI SQL replcement for connect by

Improved Time Travel

Tom is talking about flashback data archive setup on table by table basis. 11gR2 improves the amount of operations you can undertake on a table that has flashback data archive set on it.

You’ve got mail

file arrives in directory and you need to run a process on it. File watchers

deffered segment creation

many apps creation 1000’s of tables, lots of them empty, with deffered segment creation none of these empty tables will take any storage.

flash cache

SSD acts as level 2 cache, with SGA level 1, faster than disk but cheaper than memory, larger capacity than main memory

parallel improved

parallel query introduced in 7.1.6 1994 parallel query was designed to have 1 user use as much of the power of a machine as possible

automated degree of parallelism

a sql statement is parsed and if a serial plan takes less time than a threshold that is dba settable then the serial execution will be run, if it’s going to take longer then a parallel execution plan is attempted.

parallel statement queueing will pause a query if not resources are available

in-memory parallel execution uses an algorithim based on size of table being read to determine whether it’s data is read into memory, spread over nodes in a cluster

Edition Based Redefinition

Not many people have actually heard of this, Tom polled the audience. He thinks it’s the Killer feature. Tom went on to give a quick a demo, but did not really have time to do this justice.

Oracle OpenWorld – Monday

I was feeling really, really tired towards the end of day today, It’s hard work being at OpenWorld, despite what some people back home might think!

Thanks to Doug Burns’ advert the day for me started with Johnathan Lewis’ “How to be an Expert”. I think I might still be a couple of hundred years of top-cutting off that still, but it was a very funny anecdote filled presentation, though it was sometimes hard to hear due to the background noise – the unconference rooms are more like fenced off areas than real rooms.

Jonathan was claiming that the database has not changed architecturally from version 6.0. Also posed the question if you change 1 byte of data in a row, how much redo is generated. It’s a surprisingly large amount. His point was that if you don’t know, how can you spot if something is anomalous, or if you do spot that, how do you know where to look for the cause of the problem. It was also interesting hearing Jonathan saying that he rarely uses anything more than statspack, execution plans and some v$ views. Not using 10053 as a first tool, only if he suspects an optimizer bug.

After picking some swag from the OTN lounge and saying hello to a besuited Alex Gorbachev I headed to “introduction to OBIEE”. The Oracle presenters gave a general overview of the toolset which was useful enough for me, but I’m afraid the University at Buffalo guy maybe gave a bit too much detail on his own reports, showing screen after screen of blurred out live reports.

Next up was:

High Availability Vison: What’s new in 11gR2 – Juan Loaiza

This was billed as what is new in 11gR2 but, I’m afraid a some of slides did seem quite familiar. It was a high level overview of various new features that you can utilise for increasing the availability of your systems.

Couple of things that stood out for me. I finally think I understand the concept behind RAC one node. Juan was explaining it in terms of being able to perform rolling patches, or rolling OS migrations – he made the comparison with virtualisation where you can seamlessly migrate a VM from one host to another, well this is migrating an oracle instance from one node to another, which in some ways gives you more flexibility than virtualisation because you can change things like the OS version while you move.

Next was quite a strong statement upon the GoldenGate acquisition. In very red letters Juan stated quite categorically that:

GoldenGate is Oracle’s Strategic replication solution

He was quick to add that streams replication continues to be supported as an Oracle database feature, but the best of streams technology will be intergrated into GoldenGate.

Ended up missing out on lunch, so note to self it may say 12:00-14:30 for lunch, but make sure you go early they seemed to close up way before the advertised time. After (non)-lunch I went to a bit of a disappointment presentation on active-active DR:

True Active/Active Configurations with Oracle Active Data Guard – Aris Prassinos,

I think the presenter actually had a really good story to tell, but I’m afraid he really did not present his message well. You could see people leaving in droves, which is a shame. Also he finished after about 25 minutes (of an hour session). He was using active dataguard with BIG-IP’s to send read-only queries to the standby location, while routing read-write to the primary. He was also using a method to detect when a standby is too far behind so you don’t send queries to stale data – I think 11gR2 can do this for you a bit easier. This is an architecture I’ve been planning on using for a little while now, but the style of the presentation kind of made it less interesting.

After this I was squashed into

Interpreting Execution Plans – Chris Antognini

This room was a total nightmare for about 1/3 of the audience. The projector screen was on the far right side of a very wide but shallow room. Sadly I was in the obscured 1/3. However, this talk was based on Chapter 6 of his book, and I’d seen most of this stuff at UKOUG last december.

I’ve already blogged about the final presentation I attended on Edition Based Redefinition.

Like many I also seem to be struggling with jet lag, I had thought staying up later, and later would mean I’d start sleeping later and later, but I can’t seem to stay asleep past 04:00am. I expect to be shattered by the end of today.