Top 60 Oracle Blogs

Recent comments

October 2016

Trace Files Split in Multiple Parts

Last January, in the following tweet, I pointed out that the documentation vaguely mentions that a trace file may be split into several files.

Exceeding 1 million partitions

In my previous post we saw that the partition number in the execution plan might not align with the partition position in the data dictionary when it comes to interval partitions.  As we saw, the partition numbers are preordained based on the low boundary and the interval size.

That also creates an interesting scenario that can catch people out – you might exceed the allowable number of partitions, with an empty table !

Pending statistics and partition queries

This issue came through from an AskTom question, that turned out to be a known bug. In my talks on optimizer statistics, I’ve often mentioned the benefit of using pending statistics as a risk mitigation facility, and since this bug involved pending statistics, I thought I would bring it to your attention.  The issue occurs when optimizing a query that accesses a single partition via pending statistics.

OTN Appreciation Day- Laura Ramsey Edition

I know it’s supposed to be an Oracle Technology Network, (OTN) Appreciation day, but for me, OTN is their OTN Community Manager, Laura Ramsey.  Ever since we first were introduced and I tried to do the right thing, making sure she was networked properly, she’s always made sure to be there for me and I appreciate that as both a wo

The non-existent partition

Things get a little interesting in the data dictionary when it comes to interval partitions.  Consider the following example:

SQL> create table t
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) )
  4  as
  5  select rownum x from dual connect by level <= 5;

Table created.

SQL> select count(*)
  2  from user_tab_partitions
  3  where table_name = 'T';


So our table has 6 partitions. Let’s now check the execution plan for a sample query


To mark the OTN Appreciation Day I’d like to offer this thought:

“Our favourite feature is execution plans … execution plans and rowsource execution statistics … rowsource execution statistics and execution plans …  our two favourite features and rowsource execution stats and execution plans … and ruthless use of SQL monitoring …. Our three favourite features are rowsource execution stats, execution plans, ruthless use of SQL monitoring and an almost fanatical devotion to the Cost Based Optimizer …. Our four … no … amongst our favourite features  are such elements as rowsource execution statistics, execution plans …. I’ll come in again.”

With apologies to Monty Python.




OTN Appreciation Day – Instrument Your Damned Code. Please!

Today is OTN Appreciation Day.

This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…


Transaction subtleties

This came in from an AskTom question recently, and I thought it worth a blog mention because it could easily catch people out.

Lets set the scene with a simple procedure that commences a transaction, but then always fails

SQL> drop table test$tab purge;

Table dropped.

SQL> create table test$tab (val varchar2(1));

Table created.

SQL> create or replace  procedure foo_proc is
  2  begin
  3    dbms_output.put_line('in foo_proc');
  4    insert into test$tab values ('t');
  5    raise_application_error(-20001,'Error');
  6  end;
  7  /

Procedure created.

One of the cool things about PL/SQL is its nice handling of transaction processing, or often described with a statement along the lines of: “A PL/SQL procedure acts as a single unit of work”.