Learning is not a spectator sport

UKOUG talks for me if you’re keen

Monday 11:05, Hall 9
Partitioning 101

A look at the partitioning options at your disposal in Enterprise Edition from its inception in Oracle 8 up to array of improvements that have arrived in 11g. We’ll look at the various types of partitioning, how to decide whether you should be using it, how to get the best out of it, and where it might trip you up.

Tuesday 11:15, Hall 1
Clusterware upgrade diary

In 2010, a performance issue in the version of the database that a client was using, marked the commencement of a year long struggle to get the desired results, including numerous patches and upgrades. Come for a ride on the journey we’ve just completed – hear about this client’s experiences, and get some advice on how to diagnose performance problems, tackle upgrades, deal effectively with support, how to select the right platform, whilst keeping your sanity at the time

Wednesday 12:30, Hall 5
Optimizer Stats – a fresh approach

The optimizer must try to be all things to all people, and similarly, the collection of optimizer statistics must try to satisfy the needs of all. And many DBA’s just leave it at that. But with a little more effort and discipline, we can achieve much more than a "one-size-fits-all" policy. We’ll look at the tools now available under DBMS_STATS in Oracle 11g to get more stability and better performance with optimizer statistics.

There is nothing better than

a) bumpy flight from Perth to Dubai

b) bumpy flight from Dubai to Birmingham

then to open the laptop bag and find

a) a laptop

b) NO laptop power supply

So this morning was mad panic, getting taxis to PC-World to buy a universal power supply, which beeps when the PC is started with “Warning Warning – power supply is not up to par”…..

But power is power….at least the week can only get better than this !

OakTable sunday about to come to a close – some great talks as always.  Its almost beer o’clock

Well, its off to the UK for one of the best conferences going…the UKOUG conference in Birmingham.  If you’re coming, please join me at my talks – I’m giving one every single day, including Sunday, so you’re spoilt for choice 🙂

(Disclaimer: I’ve published titles through Apress, but they are not paying me to post this …)

Just noticed on the Apress site that you can buy ebooks today for $15.

I’ve dragged this little demo up from the past, because friend Toon (http://thehelsinkideclaration.blogspot.com) has been blogging about triggers. This demo shows the overhead of triggers in terms of doing things that can be easily done without triggers. But I do stress – I’m with Toon on this one – this is not the same as having a blanket ban on triggers.

Someone asked why having triggers to populate primary keys with a sequence was a bad idea…so a simple demo is called for.

SQL> create table T ( x number );

Table created.

SQL> create sequence S cache 1000;

Sequence created.

SQL> create or replace
  2  trigger TRG before insert on T
  3  for each row
  4  begin
  5    :new.x := s.nextval;
  6  end;
  7  /

Trigger created.

SQL> set timing on

SQL> insert into T
  2  select rownum from dual
  3  connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:08.79

So it takes 9 seconds to load 100,000 rows – and everyone starts asking for more CPU, more RAM, faster servers, etc etc etc….

But lets try it without the trigger, and just code the insert using the sequence directly

SQL> insert into T
  2  select s.nextval from dual
  3  connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:01.81

Bottom line – if you want a 400% performance penalty on insert, then triggers for sequences are just what you are looking for 🙂

Cheers

Connor

If you’re in Birmingham in the first week of December, (and you’re reading this blog), chances are you are coming to the UKOUG conference.

This is one of my favourite conferences – the organisers treat the speakers brilliantly, the venue is perfect (even if the weather is not), and the quality of the presentations is always amazing.

This year, the Oak Table are having some fun – you can come play Ping Pong (table tennis), but instead of bats, you’ll be using an Oracle technical reference of your choice

Check out the agenda here:

http://conference.ukoug.org/

Sometime things just leap out at you when you least expect it.

Let’s say I’ve got a table that I’d like to shrink the space on. No problems there.

SQL> drop table T purge;

Table dropped.

 

SQL> create table T as
  2  select rownum x from dual
  3  connect by level  create index IX on T ( x );

Index created.

 

SQL> alter table T enable row movement;

Table altered.

 

SQL> alter table T shrink space;

Table altered.

So far so good. But then I decide that I’d like to scan this table in descending order through the index column. So I recreate my index as DESCENDING.

SQL> drop index IX;

Index dropped.

 

SQL> create index IX on T ( x desc);

Index created.

 

SQL> alter table T shrink space;

alter table T shrink space

*

ERROR at line 1:

ORA-10631: SHRINK clause should not be specified for this object

So what happened ? Well, a descending index is implemented as function based index, and shrink space on tables that have function based indexes is prohibited.

As with all things, prudent testing to find the boundary cases in Oracle is important.