Explaining it better: dbms_xplan

Leave a comment

The dbms_xplan PL/SQL package arrived with Oracle 9i. It had just one function, display(). Provided you have a plan table (created with $OH/rdbms/admin/utlxplan.sql) in your schema, you can use it like this in a sqlplus session:

EXPLAIN PLAN FOR
SELECT e.*, d.dept_name
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.ename='benoit';

Rem
Rem Display the plan using the DBMS_XPLAN.DISPLAY() table function
Rem
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Why use it? It provides explain plans formatted much better than the older pre 9i methods of displaying query explain plans. DBMS_XPLAN also provides more information needed for performance tuning. I thought I was pretty slick having this at my finger tips since 9i came out.

What I didn’t realize is that DBMS_XPLAN has been enhanced in 10G and further in 11G.

The comments for this Jonathan Lewis blog post tipped me off to the new functions. They led to Rob van Wijk’s detailed post on the new display_cursor function, as well as another Jonathan Lewis post on the function.

Needless to say, these enhancements have been around for 5 plus years and I’m just getting to them. Humbling!

By the way, the plan table supports keeping data for multiple statements at the same time. Handy if multiple developers need to run explain simultaneously. Here’s how: provide arguments to the dbms_xplan.display function.


-- Begin spooling to file.
spool my_file_name.txt

-- Capture database name and current user.
select * from global_name;
select user from dual;

-- Run query.
explain plan 
SET STATEMENT_ID = 'developer_01_sql_01' 
INTO plan_table for
SELECT e.*, d.dept_name
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.ename='benoit';

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'developer_01_sql_01','TYPICAL'));

spool off
exit

Empty strings are null in Oracle

3 Comments

I didn’t realize that Oracle pl/sql and sql treat varchar2 empty strings as null values. Couldn’t figure out why an IF statement was returning a particular value. Apparently, this is common knowledge, see
Jeff Kemp and Paweł Barut on the topic.

Furthermore, within PL/SQL, you can assign an empty string to a variable, and then a test won’t recognize it.

Before I got to those sites, I had cooked up a similar test in SQL, see below. Pawel’s blog shows that PL/SQL will treat CHAR empty strings as NOT NULL, which appears to still be in the case in 11GR2 when I re-ran his snippet. So things are still inconsistent…

CHAR and VARCHAR2 in SQL

SQL> create table foo (f1 char(3), f2 varchar2(3));

Table created.

SQL> insert into foo values ('HEY', 'SAY');

1 row created.

SQL> insert into foo values (null, null);

1 row created.

SQL> insert into foo values ('', '');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo where f1 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is null;

F1  F2
--- ---



2 rows selected.

SQL> select * from foo where f1 is null;

F1  F2
--- ---



2 rows selected.

SQL>

Here are the pl/sql examples I tried.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var char(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var varchar2(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.

conversions part 2: ip addresses stored as decimal

Leave a comment

My previous conversion post covered unix epoch date conversion. Here I discuss my adventures with converting decimal ip addresses.

Internet IP v 4 addresses look like this 41.22.154.123. 4 numbers separated by periods. Each number ranges from 0 to 255. I never knew it, but these addresses are commonly stored as binary or as decimal numbers. One reason is to facilitate sorting the addresses. There’s no good way to sort the addresses in the dotted notation.

Here’s the function I ended up using with Oracle PL/SQL to convert the decimal value to dotted.

create or replacefunction inttoip(ip_address integer) return varchar2
deterministic
is
begin    
return to_char(mod(trunc(ip_address/256/256/256),256)) ||'.'||to_char(mod(trunc(ip_address/256/256),256))           ||'.'||to_char(mod(trunc(ip_address/256),256))           ||'.'||to_char(mod(ip_address,256));
end;

A colleague sent to me some SQL Server and Oracle conversions using a slightly different approach. I’ve copied these below. And of course, google searches will quickly direct you to other material in this area.

-- MS SQL Server with convert to bigint
SELECT dbo.IPADDRESS.IPADDRESS,
CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  dbo.IPADDRESS

-- MS SQL Server From non numeric datatype
 
SELECT     dbo.DEVICE.HOSTNAME, dbo.IPADDRESS.IPADDRESS,
CAST( ROUND( (dbo.IPADDRESS.IPADDRESS / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST( (dbo.IPADDRESS.IPADDRESS % 256 ) AS varchar(4)) as IPDottedNotation
FROM dbo.IPADDRESS 

-- Oracle PL/SQL
SELECT DEVICE.HOSTNAME, IPADDRESS.IPADDRESS,
cast(
CAST(ROUND((IPADDRESS.IPADDRESS / 16777215 ), 0) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 65535), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 256  ), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(IPADDRESS.IPADDRESS, 256 ) AS varchar2(3))
as char(20)) as IPDottedNotation
FROM IPADDRESS
 

conversions from unix epoch format OR loving the 1970s

Leave a comment

I recently encountered two conversion issues that are specific to working with computers. I address converting unix epoch date format to human readable date format here. My next post will address converting IP addresses stored as decimal integers to the more familiar dotted format.

There’s a popular work request management software product called Teamtrack from Serena software. The version deployed in my shop stores dates in its database using unix epoch format, which is the number of seconds since Jan 1, 1970. To convert this in a sql server t-sql query, I used:

select dateadd(s, table.date_field, '19700101') as friendlydate
from table

Interestingly, there were a few fields where I needed to do a slight modification, and I’m not sure why. Perhaps another timezone?

select dateadd(s, table.date_field,  '19691231 19:00:00') as friendlydate
from table

Forum poster jciappas from Brazil posts the Oracle PL/SQL version.

And at a dedicated web site for epoch converters, you can find code for doing the conversions in a variety of languages and databases. This web site covers going back and forth from unix epoch to human readable.

Deferrable Foreign Keys, Sequences Gone Astray

Leave a comment

When the developers for applications have moved on to other things, troubleshooting issues that arise during production support can get interesting. Here are two recent production issues with resolutions that required adjusting Oracle components and not application code.

Issue One: A developer unfamiliar with the app added a new feature so that a new child table got added to a parent table that had several existing child tables. Interestingly the child table foreign keys linked back to a unique constraint, not a primary key. The unique constraint was on a field WIDGET_NAME. Widget names sometimes change, so the app had a procedure to change this value. It invoked a stored procedure that updated the parent and child tables accordingly.

The new child table got added with a foreign key constraint. All appeared well, but it turned out the “change widget name” feature broke. This had not been regression tested. Turns out the foreign key on the new child table was not created with the clause INITIALLY DEFERRABLE. This foreign key clause cannot be changed after the fact, so it needed to be dropped and recreated. And the stored procedure needed an extra line to update the data in the new child table. After that, all worked fine.

Issue Two: Inserts suddenly stopped working on, you guessed it, the widget table. Turns out the primary key on the widget table on a number field usually populated by a sequence generator in a INSERT trigger. I say usually because the trigger would accept an INSERT with a supplied value as well. Well, somebody supplied a value that ended up colliding with values output by the sequence generator. I ended up running a PL/SQL block to move the sequence to a higher setting. I did it the brute force way, like this:

declare
i number;
curr number;
t number;
begin
        select widget_id_seq.nextval into curr from dual;
        for i in curr..200000 loop
                select asset_id_seq.nextval into t from dual;
        end loop;
end;
/

I see Dan Morgan has a nifty reset sequence routine, but I didn’t get a chance to try it out.

Bottom Line: Triggers and non-default clauses in contraints can create situations developers might not expect when they encounter for the first time an existing database that nobody has touched for awhile!