Posts in category "oracle"

how bind variables made me a demigod

In a previous life, I was a development DBA. Sorry thats not quite true. My job title was Persistence Architect for a J2EE application. The Oracle database wasnt really considered to be a database. In fact, it wasnt even considered to be a repository either. The database was merely a means of persisting objects.

There was a swear box for the terms 'Table', 'Column', 'Database', 'record, 'schema', 'SELECT' and bizarrely, 'PMON'. This meant I had to resurrect the word 'tuple'.

Anyway, I was responsible for the production system which was lightly loaded and adequately configured so life was easy and I could blog and surf all day. Well, actually I couldn't because I didn't blog back then and I was behind a corporate firewall so I just had some EJB's and the complete Oracle 9i documentation set for company.

One Friday, a developer came along and asked me to restart the DEV database. I asked 'Why ?'. He replied 'Because it is the second Friday in the month'. I asked 'Why ?'. 'Because we restart it every two weeks at Friday lunchtime'. 'Why ?'. 'Because that's what Bryan used to do' 'Why ?'. 'To fix the Oracle bug where we can't instantiate any more objects'. 'What Oracle bug ?'. 'Look - I really don't know - some stupid low level Oracle error. Please just restart it'.

Ten minutes investigations revealed he was indeed speaking the truth. The Oracle listener was failing with 'Unable to fork process' due to a lack of resources. Just like the objects, database connections were also persisted. This was a development Solaris server with lots of components co-hosted with 2 CPU, 512 MB memory and a paltry 128 MB swap space. Configured more swap space, ordered more memory. Job done.

Everyone was happy. All objects could be instantiated successfully and I didn't have to set a fortnightly reminder to restart Oracle on Friday lunchtime.

So, I was able to return to the 'Concepts' guide and my status was elevated from 'quiet man in corner' to 'Hero'.

A couple of months later, I was asked to look into the 'appalling lack of performance scalability' of the database on UAT. Apparently, during load tests, the J2EE application could only process 800 transactions per hour. Well, they weren't actually conventional database transactions but rather complex, involved business processes.

UAT had a 128 MB buffer cache and a 1 GB shared pool. Odd. The shared pool was littered with lots of almost identical SQL statements with embedded literals. I suspect the shared pool was originally 128 MB, the library cache hit ratio was low and some performance tuning wizard (human or otherwise) recommended 'Low library cache hit ratio means increase shared pool immediately'. Repeat ad infinitum.

I summoned up the courage and talked to the developers. 'Would it be possible to modify the application to use bind variables ?' 'No. Listen. We just use objects. We don't make database calls'. Some more investigations. The J2EE application uses TopLink which is an interface layer translating the objects into database accesses.

I decided to read the TopLink manual and suggested setting the TopLink configuration parameter 'should-bind-all-parameters' to True and repeat the test. While the development team made the changes, I reduced the shared pool to 128MB and increased the buffer cache to 1GB.

Repeat test. Staggering improvement to over 3,000 'transactions' per hour.

My status is immediately elevated to 'demigod'. Why, they were so grateful, a Java developer finally divulged the proxy they were all using to access the internet.

I created my own private swear box for the terms 'EJB', 'J2EE', 'Container Managed Persistence', 'multiple inheritance', 'classes', 'methods', 'destructors' and 'Persistence Architect' and returned to quietly reading the fine Oracle manuals.

introduction to ETL for Oracle

There are a variety of methods to extract, transform and load (ETL) data in and out of an Oracle database. This short article provides an overview of some of the basic options that are supplied with Oracle.

L is for Load

Firstly, we need a table to load data into. This SQL script creates a table called PERSON.

    create table person
    (person_id number(12),
    forename varchar2(200),
    surname varchar2(200),
    title varchar2(10),
    email varchar2(100),
    gender varchar2(1),
    ni_number varchar(30),
    address1 varchar2(100),
    address2 varchar2(100),
    town varchar2(100),
    county varchar2(100),
    postcode varchar2(10),
    country varchar2(100),
    phone varchar2(100),
    mobile varchar2(100),
    salary number(12),
    date_of_birth date);

The following PL/SQL procedure may be used to populate this table with an arbitrary amount of data.

    create or replace
    procedure load_person(p_records in pls_integer)
    is
    v_person_id person.person_id%type;
    begin
    for i in 1 .. p_records
    loop
    v_person_id := to_char(i);
    insert into person
    (person_id,
    forename,
    surname,
    title,
    email,
    gender,
    ni_number,
    address1,
    address2,
    town,
    county,
    postcode,
    country,
    phone,
    mobile,
    salary,
    date_of_birth)
    values
    (v_person_id,   /* person_id */
    'Norman',     /* forename */
    'Brightside',    /* surname */
    'Mr',    /* title */
    'norman@brightside.com',  /* email */
    'M',    /* gender */
    'AA123456B',   /* ni_number */
    'Leafy Cottage',    /* address1 */
    '1 New Street',   /* address2 */
    'Anytown',    /* town */
    'Anycounty',    /* county */
    'NN12 9ZZ',    /* postcode */
    'UK',     /* country */
    '0208444555',   /* phone */
    '07789123321',   /* mobile */
    20000,    /* salary */
    '01-JAN-1970'   /* DOB */
    );
    end loop;
    commit;
    end;

Loading 1,000,000 records took 1 min 54 secs running Oracle 10g (10.2.1) on my home PC.

One obvious deficiency in the PL/SQL is that all records (and the SQL statement) are identical. In practice, this will not be the case. Therefore, it may be beneficial to replace the literal SQL with bind variables using using native, dynamic SQL.

    create or replace
    procedure load_bind(p_records in pls_integer)
    is
    sql_stmt varchar2(2000);
    v_person_id person.person_id%type;
    v_forename person.forename%type := 'Norman';
    v_surname person.surname%type := 'Brightside';
    v_title person.title%type := 'Mr';
    v_email person.email%type := 'norman@brightside.com';
    v_gender person.gender%type := 'M';
    v_ni_number person.ni_number%type := 'AA123456B';
    v_address1 person.address1%type := 'Leafy Cottage';
    v_address2 person.address2%type := '1 New Street';
    v_town person.town%type := 'Anytown';
    v_county person.county%type := 'Anycounty';
    v_postcode person.postcode%type := 'NN12 9ZZ';
    v_country person.country%type := 'UK';
    v_phone person.phone%type := '0208444555';
    v_mobile person.mobile%type := '07789123321';
    v_salary person.salary%type := 20000;
    v_date_of_birth person.date_of_birth%type := to_date('01-JAN-1970', 'DD-MON-YYYY');
    begin
    sql_stmt :=
    'insert into person
    (person_id,
    forename,
    surname,
    title,
    email,
    gender,
    ni_number,
    address1,
    address2,
    town,
    county,
    postcode,
    country,
    phone,
    mobile,
    salary,
    date_of_birth)
    values (:v_person_id,
    :v_forename,
    :v_surname,
    :v_title,
    :v_email,
    :v_gender,
    :v_ni_number,
    :v_address1,
    :v_address2,
    :v_town,
    :v_county,
    :v_postcode,
    :v_country,
    :v_phone,
    :v_mobile,
    :v_salary,
    :v_date_of_birth)';
    for i in 1 .. p_records
    loop
    v_person_id := to_char(i);
    execute immediate sql_stmt using
    v_person_id,
    v_forename,
    v_surname,
    v_title,
    v_email,
    v_gender,
    v_ni_number,
    v_address1,
    v_address2,
    v_town,
    v_county,
    v_postcode,
    v_country,
    v_phone,
    v_mobile,
    v_salary,
    v_date_of_birth;
    end loop;
    commit;
    end;
    /
    show errors

The modified PL/SQL procedure took 2 mins 1 sec for 1 million records. This doesn't imply that bind variables are worse than literals. More likely, that parsing overhead is relatively small for insert statements.

E is for Extract

People often ask why Oracle do not provide an equivalent SQL*Unloader to dump tables to delimited files. Well, the standard SQL*Plus utility may be used for such a purpose. Tom Kyte created some generic wrapper scripts (for both Unix and Windows) to dump an Oracle table to a delimited text file.

The 'flat' utility was used to dump the contents of the PERSON table into a comma separated value (CSV) file containing 1 million (identical) records.

SQL*Loader was then used to immediately load the data back in to the PERSON table again using this control file.

    LOAD DATA
    TRUNCATE
    INTO TABLE person
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (person_id,
    forename,
    surname,
    title,
    email,
    gender,
    ni_number,
    address1,
    address2,
    town,
    county,
    postcode,
    country,
    phone,
    mobile,
    salary,
    date_of_birth
    )

The time taken was 1 min 25 secs for conventional mode and 28 seconds when specifying DIRECT=TRUE for a direct path load.

Another interesting and often overlooked method for loading data is the use of external tables. This allows the creation of a virtual read-only table which is mapped directly to the host file using a CREATE TABLE syntax (similar to that for SQL*Loader control file).

This SQL script creates an external table which can then be analysed using standard SQL query tools without having to actually load a database table.

    create table person_ext
    (person_id number(12),
    forename varchar2(200),
    surname varchar2(200),
    title varchar2(10),
    email varchar2(100),
    gender varchar2(1),
    ni_number varchar(30),
    address1 varchar2(100),
    address2 varchar2(100),
    town varchar2(100),
    county varchar2(100),
    postcode varchar2(10),
    country varchar2(100),
    phone varchar2(100),
    mobile varchar2(100),
    salary number(12),
    date_of_birth date)
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_dir
    ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
    LOCATION ('person.dat')
    );

It is then trivial to load data from the external table into a conventional Oracle table as follows:

SQL> insert into person select * from person_ext;

This method of data loading took 39 secs.

T is for Transform

And deliberately left until last. Extracting and loading data is relatively easy. Often the time consuming and complicated task in ETL is to identify, transform and potentially clean the data items from the source system and map them to tables in the target database.

interviewing stories

A few years ago, I worked for a small internet company and was a successful dot com millionaire. Then the confounded Italians went and devalued the Lira.

We spent (wasted) lots of time interviewing Oracle developers. Interviewing was time consuming and we had a fairly rapid turnover of people with varying technical abilities and personality disorders.

After a while, we decided to set a short technical test in an effort to save time and improve the quality of the technical people we hired.

The test was a trivial piece of PL/SQL. Count all the rows in all the tables in a specified schema. The consequences of introducing of this test were very revealing.

Some candidates (with years of Oracle and PL/SQL experience) flatly refused to take the test at all - 'the agent didn't say anything about a technical test'.? Some candidates asked if they could have access to our embryonic Oracle technical library (a couple of O'Reilly books). Some candidates asked it they could use their own technical books for reference. We flatly refused.

It was interesting to see whether people elected for Linux/vi or Windows/notepad.

It was interesting to see whether people could deliver under pressure.

It was interesting to see whether people asked questions.

It was interesting to see whether people actually compiled and executed the code. Some candidates thought, incorrectly, that the test was purely to 'write the code'.

It was interesting to see whether people visited 'http://tahiti.oracle.com/'. We said you couldn't use books. However, we didn't say you couldn't use the internet.

It was interesting to see whether people commented a trivial procedure.

It was interesting to see whether people counted the rows or checked for the presence of recent statistics first.

It was interesting to see whether people used native, dynamic SQL or tortured themselves with DBMS_SQL.

It was interesting to see whether people favoured SQL*Plus, Pro*C, PL/SQL or Perl/DBI.

The standard of candidates improved. If the technical test was unsatisfactory, we skipped the interview formalities to save time (theirs and ours).

One outstanding candidate delivered an excellent test. Perfect, commented PL/SQL. He even included a file header with CVS macros for version control. The results were correct. He finished the assigned task in minutes. He even used the singular if a table had '1 row'. He was an excellent Oracle PL/SQL developer.

However, we didn't hire him because there was an indefinable, intangible concern, a nagging worry that we couldn't articulate or put our finger on precisely. It just didn't feel right. Hard to explain to the agent. Even harder to explain to him but sometimes you have to go with your instincts.

a short history of Oracle blogging

I was just reviewing the list of Oracle related blogs I subscribe to. Here they are in approximate chronological order with the blogging platform. Apologies for any inaccuracies.

A couple of things struck me...

  • Mark Rittman was one of the first Oracle bloggers (if not the first) and uses the Movable Type platform. Not one Oracle blogger has chosen to follow suit (ever). I wonder why.
  • Most Oracle bloggers use blogger.com which is a hosted platform but most are not afraid to tweak their templates with varying degrees of success. Think garish colours, think 'Changing Rooms'.
  • The birth of Tom Kyte's blog seemed to inspire a lot of Oracle bloggers to follow suit.

Anyway, thanks to all of you for the information, the technical content, the thoughts, the ramblings and perhaps most importantly, the humour.

curious case of the missing spool file

Bit of a ghostly theme today.

One great aspect of my job is that I write SQL scripts that are reviewed and executed by other people. This suits me just fine. The less work for me, the better.

Obviously, I never add 'spool off' to the tail of a SQL*Plus script. This is a whole ten characters to type (including newline). Multiply this by all the SQL scripts you might produce in a lifetime and just think of how much time that would waste. In any case, everyone knows that 'exit' implies a 'spool off' anyway.

A while ago, a friendly Oracle DBA ran my SQL script and called me to tell me it had finished. I asked him to email the spooled output for review. He said there was no spooled output generated. I asked him to check the current directory (no), %ORACLE_HOME%\BIN directory (no), the Recycle Bin (no), search all local hard drives for 'patch_prod.lst' (no).

I asked him if the SQL*Plus session was still open and, if so, to simply type 'spool off'. He replied that the SQL*Plus session had already been ended. I was puzzled until I asked 'Is this the GUI version of SQL*Plus on Windows ?' 'Yes', he replied. 'Did you end the session by hitting the 'X' button ?' 'Yes'.

So, it turns out that the spool file is lost when Windows SQL*Plus is terminated using the 'X' button. I didn't know this fact because I normally use 'exit' to close any SQL*Plus session on Unix, Dos Command Window or Cygwin.

I had a suspicion that this gentleman was privately quite enjoying this moment and my discomfort so I said 'Hmm - that's a shame. I really wanted to check the spool to ensure that I had deleted the correct 7.9 million invoices from production'.

Later on, I asked myself what type of ~~mindless idio~~ person uses 'X' to terminate a Windows SQL*Plus session.

Then the answer struck me. A lazy one. Like me.

Raptor is out of the cage

Oracle have released Raptor (a freely available GUI SQL query tool) which may be of interest to those of you who use Toad or are frustrated with the terseness of the SQL*Plus interface.

Raptor is primarily a cross platform, PL/SQL developer tool, written in Java (60 MB download). The SQL output is a scrollable grid (like Toad) and there is the standard schema browser, PL/SQL debugger, SQL pretty printer, DDL generator and a graphical interface to explain plan for query tuning.

All in all, a very promising release which fills a surprising (to me) gap in Oracle's range of database products.

Tom Kyte makes mistake shock

London, near England - Wednesday 2 November 2005

The Oracle community was reeling yesterday from the revelation that the universally respected, internationally renown technical expert and long standing Oracle employee, Thomas Kyte, had made a mistake.

The error was discovered by a Senior Oracle DBA based in Solihull in the UK, Mr. N. Brightside who explained:

"I was dearly looking forward to attending the UK Oracle User Group in Birmingham and hoped to get my copy of Tom Kyte's latest book, (Expert Oracle - Database Architecture) signed by Tom personally. However, at the last minute, my manager told me that the end of Q3 was imminent and there was no budget available for the daily 4.50GBP return rail fare. So, I decided to 'work from home' and sit down to savour Tom's excellent book. Imagine my surprise when I found what appeared, at first sight, to be a typographical error on page 38. In a paragraph discussing other relational database systems, Tom incorrectly refers to 'Ingress' instead of 'Ingres'. Then to my horror, a quick search on AskTom revealed that Tom makes this identical misspelling elsewhere. It was not a typo, it was a genuine mistake."

A spokesperson for Apress (the publisher of Kyte's book) commented:

"Thank you for the feedback on Tom Kyte's book. We were unaware of this problem and will update the Errata section accordingly in due course. However, we did note Tom had a tendency to use a mixture of 'Apres', 'Apress' and 'Apresss' in communications while writing the book. We attributed this to the long hours he was working on the book, the tight deadlines or a faulty auto-repeating 'S' key.

When we politely brought this to his attention, he was absolutely mortified and apologised profusely for the inconvenience (additional hard parses, increased latching and CPU utilisation) caused. Thereafter, he would only refer to the company as ':B1 Publishing'."

Tom Kyte was quick to acknowledge the error and said he was only too pleased to correct the problem although he thought all the fuss and extra work involved was rather a 'bind'.

Don Burleson was unavailable for comment but sources reported he was 'absolutely ecstatic at the news'.

Oracle newbies, wizards and gurus

I recently stumbled upon Lisa Dobsons Oracle Newbies blog. Although I have never met Lisa, I admire her for two reasons. Firstly, she is about to give a presentation at the UK Oracle User Group in Birmingham. I am a good deal older than Lisa and the prospect of presenting to a large number of 'grumpy old men' would fill me with absolute dread.

Secondly, I really like Lisa's rather self deprecating, modest statement on her profile

I've been an Oracle DBA for 3 years, so still consider myself a 'Newbie'!

In my various jobs, I have met several people working with Oracle with less than three years experience who are variously described (either by others or themselves) as gurus and would take serious offence if anyone ever had the temerity to describe them as a 'newbie'.

Some of these people are indeed experienced, knowledgeable, approachable, flexible, adaptable and eager to learn and perhaps, just as importantly, to share their knowledge and teach others.

Others are undoubtedly very clever people but are somewhat narrow minded in their outlook and suffer from tunnel vision. These types are normally the self-appointed wizards but viewed as insular and unapproachable by others. They will improve performance almost instantly by adding or dropping an index, or gathering statistics but fail to communicate the fact to anyone but merely bask in the glory and yet higher, even more elevated status.

One aspect of my job that I enjoy is that I often visit a client for a short period (2-3 days) so I am blissfully ignorant of the politics, history and personalities involved. In addition, I simply do not have the time (or access) to use email to communicate. Instead I find I get more achieved quicker if I meet people face to face or call them on the telephone to find out information.

This can lead to some interesting situations. Often, the same questions couched in a slightly different way from a fresh face can lead to a different response. For example, a simple request to patch Oracle to 9.2.0.7 or to simply start the OEM Agent that has been repeatedly requested but ignored (or denied) for many months can miraculously become feasible almost immediately.

It is surprising how many so called technical issues actually turn out to be about communication.