Learning is not a spectator sport

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.

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   (Debra is in the middle, basking in the sunshine…)

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.

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

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!

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