Making life hard for yourself.

Sometimes it seems like a developer has gone out of their way to make life difficult for themselves (and us poor long suffering DBAs). For example we have an audit trail of user actions in the database that contains xml. This almost makes sense except that the xml is stored (more...)

Bind Variable mismatch, nulls and oci

A short post on a frustrating issue that took a while to find the solution to. The developers made a small change to a table allowing nulls in 23 columns previously configured as not null. This resulted in a large number of bind mismatches and child cursors being generated, eventually giving rise to a serious performance issue.

After much discussion of the issue test cases, being told to tune the database flushing the shared pool finally a developer opened the code and took a look at the problem and found the reason for the bind mismatch was the oci null (more...)

How expensive are literals?

As a follow-up to my last post I finally got around to writing a small program to demonstrate the difference between bind variables and literals, the test is a simple c++ program the reads a text file and updates 27,000 rows in a single table. The code runs the updates in three different ways.

Method 1: Bulk bind 200 rows at a time into the update statement.

Method 2: Bind 1 row at a time

Method 3: Use literals in the updates.

Tkprof output from the test looks like this.

Method 1
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count        (more...)

Bind Variables, almost right.

Sometimes I get a bit sarcastic with and about developers, this is unfair as for the most part the ones I have worked closely have either been very good oracle developers or very good c++/java/VB6/whatever developers with a real interest in doing things the right way and have been receptive to constructive (but grumpy feedback).

This gem in a vendors application however brought out the sarcasm.

Update
  bigwidetable
set
  column_1 = :1, column2 = :2, ..............
  column_125 = :125, column_126 = :126
where
   column_127 = 'GotBoredWithBindVariablesDidWe?';

I can’t work the logic out here, the code is already written to use (more...)

SQL Injection

Sometimes I wonder why I still find stuff like this, there are so many articles/blogs/documents about sql injection how does a developer come up with the following…

create or replace function row_count (p_table varchar2)
return number
is
v_str VARCHAR2(1000);
v_row NUMBER;
BEGIN
v_str := ‘select count(*) from ‘||p_table;
execute immediate v_str into v_row;
return v_row;
end;
/

My simple explanation of why this is bad was that passing in the parameter ‘all_objects,all_objects,all_objects’ will result in a fairly nasty cartesian join and an attacker who knew the names of the database tables could potentially pick a really large table. Run enough (more...)

A single procedure to “hang” the database.

I really enjoy it when people don’t understand the features they are using it makes life really interesting, automatic parallelism is a great new feature of 11.2 but we found a new and interesting way to break things. As part of the application we are implementing customers may re-request fairly large volumes of data to limit the impact on the system as a whole the design is such that a cursor is opened and the data returned in chunks of between  500 rows, if two customers execute re-requests at the same time then two cursors are opened cutomer1 gets (more...)

Flashback logging only has a 2% performance decrease…or not.

Firstly apologies to both people who have previously read my blog, things have been a little hectic over the past 12 months working away from home in the Netherlands has taken its toll on my spare time, and weekends are to be spent either with the family or working, things have settled into a more stable pattern now so here goes..

Some background, I’ve been assigned into a project to configure dataguard on a very busy production system performing a mixed bag of batch processing, messaging and DSS loads. Performance and availability of the system is critical for the business (more...)

Index Fragmentation – more thoughts.

Looking at my last post I’m somewhat unsatisfied about the script to create the “fragmented” index so I thought I’d have another go, modifying it to be like this:

drop table TEST_FRAG2;
create table TEST_FRAG2 (id number not null, value varchar2(10));
insert into TEST_FRAG2 select id,’stuff’ from
(select rownum as id from dual connect by level <=400000)
where mod(trunc(id/400),2)=0;
create unique index TEST_FRAG2_IDX on TEST_FRAG2(id);
insert into TEST_FRAG2 select id,’stuff’ from
(select rownum as id from dual connect by level <=400000)
where mod(trunc(id/400),2)=1;
commit;

begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘CHRIS’,tabname => ‘TEST_FRAG2′,
estimate_percent => 100,method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
(more...)

Index fragmentation – Just like SQL Server

My current project is working for a client consolidating a bunch of SQL server databases into a properly licenced and managed sql serve cluster, Its been a while since I did a lot in SQL Server and it seemed like a good way to refresh some skills I hadn’t used for  while. One of the big differences is sql server best practices recommends running a maintenance job to rebuild fragmented indexes, current thinking in Oracle is that frequent (and most of the time any) index rebuilds are not required. Index fragmentation in SQL Server has a two specific meanings Internal (more...)