Month: August 2008

Home!

Seemed to have spent a lot of August travelling. A short-term engagement in a Nordic country and the family vacation let me put on more than my fair share of air miles. The final flight (I hope!) of the month was yesterday when I took my 17-year old to Aberdeen to visit the university. It […]

stop fixing the symptom!

Long time no post!Sorry folks: been busy with a lot at work and at home. It’s Winter outdoor sports season here so all spare time is spent ferrying kids from venue to venue.Anyways: attended a recent Oracle seminar on high availability.Good stuff. And for once, we didn’t get another "injection" of "Larry Ellison did or said this or that or the other": Hallelujah!!!Interesting also to see some

stop fixing the symptom!

Long time no post!Sorry folks: been busy with a lot at work and at home. It’s Winter outdoor sports season here so all spare time is spent ferrying kids from venue to venue.Anyways: attended a recent Oracle seminar on high availability.Good stuff. And for once, we didn’t get another "injection" of "Larry Ellison did or said this or that or the other": Hallelujah!!!Interesting also to see some

Index block split bug in 9i

In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.While I was t…

Index block split bug in 9i

In his famous index internals presentation Richard Foote mentions a bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically.

While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G.

Here is the test case Richard presents in his paper.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME VALUE
------------------------------ ----------
leaf node splits 0
leaf node 90-10 splits 0
branch node splits 0

SQL> ed
Wrote file afiedt.buf

1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 commit;
5 end loop;
6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 35 35
leaf node 90-10 splits 0 0
branch node splits 0 0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

LF_BLKS PCT_USED
---------- ----------
36 51

SQL> drop table t;

Table dropped.

I am trying to insert the rows in the order of the primary key column, so what I expect to see is that when an index block fills there will be a 90-10 split and the index will grow in size. But as the number of leaf block splits show there are 35 block splits and none of them are 90-10 splits meaning all are 50-50 block splits. I have 36 leaf blocks but half of each one is empty.

If we try the same inserts but commit after the loop the result changes.


SQL> create table t(id number,value varchar2(10));

Table created.

SQL> create index t_ind on t(id);

Index created.

SQL> @mystat split

NAME VALUE
------------------------------ ----------
leaf node splits 35
leaf node 90-10 splits 0
branch node splits 0

SQL> ed
Wrote file afiedt.buf

1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;
SQL> r
1 begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6* end;

PL/SQL procedure successfully completed.

SQL> @mystat2 split

NAME VALUE DIFF
------------------------------ ---------- ----------
leaf node splits 53 53
leaf node 90-10 splits 18 18
branch node splits 0 0

SQL> analyze index t_ind validate structure;

Index analyzed.

SQL> select lf_blks, pct_used from index_stats;

LF_BLKS PCT_USED
---------- ----------
19 94

In this case we see that there have been 18 block splits and all were 90-10 splits as expected. We have 19 leaf blocks and all are nearly full. Depending on where the commit is we can get an index twice the size it has to be. When I ran the same test in 10G it did not matter where the commit was. I got 19 leaf blocks in both cases.

I did not test if this problem happens when several sessions insert a single row and commit just like in an OLTP system but I think it is likely because we have indexes showing this behavior in OLTP systems.

An unusual cause of ORA-12154

The ORA-12154 (and its cousin ORA-12514) have been the bane of many a novice DBA.

This error is essentially telling you that you have messed up your database naming configuration, whether it be tnsnames, oracle names or OID. The fix is normally quite simple – just correct the naming.

This is usually quite easily done with netmgr, a tool that is fairly good at its job. The syntax for setting up a tnsname is a little convoluted, and I’ve fallen back on netmgr a number of times when I can’t seem to get it just right by typing the entries in the file.

There’s at least one other way to cause ORA-12154 to pop up and consume more time than you may like to admit. I won’t tell how long I played with this…

The cause is actually due to security policy. While the characters !@#$%^&*()_-=+~` are not normally allowed in Oracle passwords, it is actually quite simple to include them. Simply enclose the password in double quotes.

alter user scott identified by "th!sat^st";

This was done on a number of accounts on our databases, all in the name of security.
These types of passwords have been used without issue for some time now.

Today was a notable exception.

After spending some time fiddling with tnsnames.ora files, I realized what the problem actually was.

Suppose you were to have a password with an ‘@’ in it? Yep, that was the problem.
First let’s see what it looks like from within sqlplus:


15:41:52 SQL> alter user scott identified by "what@mistake";

User altered.

15:42:03 SQL> connect scott/"what@mistake";
Connected.

scott SQL> select user from dual;

USER
------------------------------
SCOTT

SQL> connect scott/what@mistake
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


As long as the connect string was in double quotes, no problem.

Now let’s try it from a linux command line:


Linux> sqlplus scott/'what@mistake'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:42:20 2008

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Linux> sqlplus scott/'what\@mistake'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:14 2008

ERROR:
ORA-01017: invalid username/password; logon denied


Linux> sqlplus scott/"what\@mistake"

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:46:21 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Linux> sqlplus 'scott/what\@mistake'

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:47:23 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Linux > sqlplus scott/what\@mistake

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Aug 1 15:48:52 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

There doesn’t seem to be any method to use a password with the @ character in it, at least not from sqlplus. The same password works fine when used on the command line with perl script that logs on to the same database and account:


Linux> DBI_template.pl -database dv11 -username scott -password 'what@mistake'
X

The ‘X’ is the correct output as this script simply selects * from dual.

Lesson learned, do not ever, under any circumstances, use the @ character in the password for an Oracle database account.

You might think that goes without saying, but it’s actually pretty easy to get a password with ‘@’ in it when the passwords are generated random characters.

FWIW, this problem was manifested in Windows as well

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569