UKOUG day 1

Well, as per normal when attending a conference in another country, it started very early….very early indeed.  4am the body clock decided it was time to get up, so eventually just gave up on trying to get back to sleep and started the day Smile

First up was Kyle Hailey’s talk on SQL Tuning.  Kyle is very passionate about his stuff, and his Visual SQL Tuning is all about graphing rather than “reading” the execution plan.  He was in Hall 1, which this year is projecting the speaker up on the big screen next to the slides…Should be fun for my talk in that room tomorrow !

Next up was my partitioning talk, which went OK, although its one of those talks that just bludgeons people with streams and streams of content and examples. So by the end of the 60 minutes, you can tell when people are “full” and can’t take much more Smile

One thing that’s interesting this year is the size of the exhibition hall.  There seems to be a lot less exhibitors here – I wonder if they this is a reflection of the fact that generally conferences seem to be attracting technical people rather than middle management.  (And technical people don’t have an operating budget, so the exhibitors are not seeing it as a worthwhile investment…who knows).

Here’s a picture of the hall in case anyone hasn’t seen how these things are mapped out:

 

P1050418

 

The afternoon sessions included Julian Dyke and Martin Bach’s talk about clusterware upgrades – both of which tend to mirror the content I’ll be doing tomorrow, namely …. its painful.

(Day 2 and Day 3 will be coming in a couple of weeks….I’m off to Rottnest (http://www.rottnestisland.com/) for a couple of weeks – no laptop, no internet…

My talks for UKOUG

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.

UKOUG day minus 1

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 Smile

UK here I come

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 🙂

Apress Deal

(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.

Triggers

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

UK conference coming up

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 Smile

Check out the agenda here:

http://conference.ukoug.org/

Descending index gotcha

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.

Post conference weather

Fellow Ace Debra Lilley stayed a day with us after the conference, and in true local style, we wanted to show off our wonderful Perth spring weather.

It didn’t really pan out as we expected Smile  (Debra is in the middle, basking in the sunshine…)

 

Img_0530

AUSOUG conference–day 2

I didn’t get to see as much content as I liked on day 2, because I had still plenty of slides to complete on my own presentation, so I locked myself away in a room at the casino (the conference venue) and worked on content and delivery.

However, once I was happy with it, I sat on a panel about public speaking which actually went quite well – some good audience participation.

After that, I went and saw Graham’s exadata Live presentation, which always blows me away.  Loading, analyzing, querying a terabyte data warehouse in 30 mins is amazing.

IMG_0527

AUSOUG conference–day 1

The AUSOUG conference program this year was bolstered by a number of international speakers on the tail end of the OTN series of talks held all over the world.  As such, speakers such as Debra Lilley, Graham Wood, Kai Yu, Chris Muir, Tim Hall and many others added some amazing content to the already impressive local content.

I watched Debra speak for the first time – coinciding with my first talk on a Fusion topic Smile

After that, I watched Ray Tindall do a talk on Weblogic tips and tricks.  It was a relief to see someone else talk about all the trials, tribulations, patch levels, certification issues, etc etc with getting the version and configuration “just right”.

Yury from the Sydney user group gave a simple but excellent talk on RMAN backups – not a “how to”, but more of a “things you need to consider” with your backup scripts.  Plenty of useful things in there to improve the robustness of your backup regime.

Then it was Kai’s clusterware talk, and finished up with Graham’s hidden features.  Both a nice insight.

A few beers, and a few drinks to round off the day, then home to work on my own presentation!

Careful in 11.2 with dbms_utility

Its common to have an instrumentation infrastructure where you might be calling DBMS_UTILITY.FORMAT_CALL_STACK to record where in the code path you may be..

But this can bite you in 11.2

A simple demo of how much slower it has become is shown below. Relevant parameters are below:


NAME                                     VALUE
---------------------------------------- -------------
plsql_ccflags
plsql_code_type                          INTERPRETED
plsql_debug                              FALSE
plsql_optimize_level                     2
plsql_v2_compatibility                   FALSE
plsql_warnings                           DISABLE:ALL

SQL> connect scott/tiger@db11107
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

SQL> connect scott/tiger@db11202
Connected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set timing on
SQL> declare
2    x varchar2(1000);
3 begin
4   for i in 1 .. 10000 loop
5     x := dbms_utility.format_call_stack;
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31