The 95^th edition of Log Buffer, the weekly review of database blogs, has been published by Mark Schoonover on his Mark’s IT Blog.

We can look forward to LB#98 Jeff Smith’s Jeff’s SQL Server Blog on May 23^rd. There’s always plenty of room for more editors, so don’t waste another minute — send an email to me, the Log Buffer coordinator, and get started!

Without further ado, here is Mark Schoonover’s Log Buffer #95.

Log Buffer, the weekly review of database blogs, welcomes back for his record-breaking record-tying (Sheeri, are you reading?) third edition Ronald Bradford of Opinions, Expertise, Passion.

Why does Ronald write Log Buffer? Perhaps it’s because he knows that LB is and established and widely read feature, and hence likely to bring his own blog some new readers and improve its ranking. Or maybe he enjoys the fun and challenge of comprehending and presenting the entire DBA blog scene, not just the part that deals with his own favoured technologies. (Or maybe he just likes me? Ronald?)

Since Log Buffer is open to anyone, I encourage you also to join in. If you’d like to edit and publish an edition yourself, take a look at LB’s homepage, read the few guidelines, and then get in touch with me, the Log Buffer coordinator.

You can also contribute by emailing your favourite blog items to the editor.

And now, here’s Ronald Bradford’s Log Buffer #94.

Here is the question that was posted on OTN Forum Grid Control Extensibility (not the topic of the forum!) yesterday:

I recently moved south to Bangalore and I am working for a large software integrator. My project team is working on a JEE application project that uses test driven development methodologies. We are planning to use a host of new breed technologies such as Enterprise 2.0, Ajax, Drools, JDO, Hibernate,and mashups. Are you able to point me to online and offline resources/trainings that can help our team get up to speed with these latest technologies.

All the help that group members can provide in this regard is much appreciated.

My first reaction was — how stupid one should be to post such absolutely irrelevant question in that forum? I do see from time to time some questions that are about Grid Control but not about Extensibility and, assuming I have a minute, I might suggest to ask it in the neighboring forum instead. However, this time it seemed absurd to choose this place.

I was even more surprised that someone actually tried to provide a sensible response, I was thinking to blog about it to re-iterate the point that Tom Kyte expressed recently but I couldn’t find the thread anymore. I thought it was moved by a wise admin to a more appropriate place. I did a quick search on OTN forums and found it in… well, many other OTN forums. So it seems that it was simply deleted from Extensibility forum.

Google showed that OTN forums are not the only one appearance of this request. Is this an absolute stupidity or an example of plain scam?

Well, I just needed that rant.

A few days ago, a developer and I had an interesting conversation. The developer was trying to tune an expensive SQL statement, using following trial-and-error method:

loop until acceptable performance
    explain plan -> execute SQL with sql trace -> tkprof -> rewrite
end loop;

After looking at his method in amusement, I showed him how to identify and tune SQL statements scientifically, and decided to blog about it.

Let’s look at a simple case and then proceed to slightly more complex versions. The following code fragment creates test tables, indices, and collects statistics on those tables.

 create table t1_vc as
 select trunc(n/10000) n1, mod(n, 1000) n2 ,
          lpad( n,255) c_filler
 from (select level n from dual connect by level <= 100001);
 create index t1_vc_i1 on t1_vc (n1);
 create table t2_vc as
 select trunc(n/ 100) n1, mod(n, 10000) n2 ,
          lpad( n,255) c_filler
 from (select level n from dual connect by level   null, cascade => true);
 exec dbms_stats.gather_table_stats(user, 't2_vc',estimate_percent => null, cascade => true);
 null, cascade => true);
  exec dbms_stats.gather_table_stats(user, 't2_vc',estimate_percent => null, cascade => true);

Simple SQL, but I had to use hints to illustrate the point I’m driving at. Let’s do an explain plan on this SQL.

explain plan for
select /*+ use_nl (t1_vc, t2_vc ) */
t1_vc.n1 , t2_vc.n2
from  t1_vc, t2_vc where
t1_vc.n1 = t2_vc.n1 and t1_vc.n2 between 101 and 105 and t1_vc.n1=1
/
select * from table(dbms_xplan.display)
/

Plan hash value: 3808913109

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |  5453 | 81795 |   643   (0)| 00:00:08 |
|   1 |  NESTED LOOPS                 |          |       |       |            |          |
|   2 |   NESTED LOOPS                |          |  5453 | 81795 |   643   (0)| 00:00:08 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1_VC    |    55 |   385 |   368   (0)| 00:00:05 |
|*  4 |     INDEX RANGE SCAN          | T1_VC_I1 |  9091 |       |    18   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T2_VC_I1 |   100 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T2_VC    |   100 |   800 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(”T1_VC”.”N2″=101)
   4 - access(”T1_VC”.”N1″=1)
   5 - access(”T2_VC”.”N1″=1)

20 rows selected.

The execution plan looks okay, but this statement is executed millions of times, so we need to reduce time as much as possible. Can this SQL be tuned further?

(more…)

I have just managed to get DBD::Oracle to successfully compile, install, and test with the 11.1.0.6.0 Instant Client (IC) on a Linux (32bit) OS. It seems Oracle, in its wisdom, has changed the folder structure yet again, so to get it to compile, try this.

The table structure of the IC out of the RPMs is:

/usr/lib/oracle/11.1.0.1/client/ (Instant Client Package Basic)
/usr/share/oracle/11.1.0.1/client/ (SQL*Plus)
/usr/include/oracle/11.1.0.1/client/ (Instant Client - SDK)

So, doing the following . . .

export ORACLE_HOME=/usr/lib/oracle/11.1.0.1/client
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$PATH

perl Makefile.PL -m /usr/share/oracle/11.1.0.1/client/demo.mk -h /usr/include/oracle/11.1.0.1/client/

. . . will work.

I have also updated the Makefile.PL, which can be found here: http://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL.

Cheers, John Scoles.

Welcome to the 93^th edition of Log Buffer, the weekly review of database blogs.

Conference season is upon us, so it’s been a busy week. There was the MySQL Conference & Expo, so let’s look at that.

Arjen Lentz posts about Sunday’s community dinner, including the arrival of an unexpected guest. Two photos: one of Pythian’s Paul Vallée getting some Sun; the second from the pre-conference dinner.

Zack Urlocker has a couple pieces with both photos and links to video of the keynote addresses from Marten Mickos, Jonathan Schwartz, and Rich Green. From Wednesday, and from Thursday.

Congratulations are due to Baron Schwartz, Diego Medina, and Sheeri Cabral. Baron reports from the conference that the three of them were awarded the 2008 MySQL Community Awards, and his piece makes for a very apt acceptance speech. Here’s Kaj Arnö’s more official post on the Community Awards.

Baron also has good summaries of the conference course: day one, and day two.

Elsewhere on the MySQL scene, much ado about the immediate roadmap for the DBMS, as introduced at the conference. Jeremy Cole got things going, writing, MySQL to launch new features only in MySQL Enterprise: “MySQL will start offering some features . . . only in MySQL Enterprise. This represents a substantive change to their development model — previously they have been developing features in both MySQL Community and MySQL Enterprise. However, with a shift to offering some features only in MySQL Enterprise, this means a shift to development of those features occurring . . . only in MySQL Enterprise.” This post got a lot of comments, including from MySQL boss Marten Mickos.

(more…)

I recently implemented OID/SSO with E-Business Suite 11.5.10 CU2, and experienced some issues after the entire setup went smoothly. I hope this note might help others troubleshoot, as it took me a while to figure out the root cause of the problem.

Facts

1. E-Business Suite Version 11.5.10 CU2
2. 10G Version 10.1.2.2

You have done the install and everything went fine. After the bounce you see the following:

1. Login from a remote location
2. Navigate to Administrator System –> Security –> User –> create
3. Enter username and save. The error occurs.

Unable to call fnd_ldap_wrapper.create_user due to the following reason:
ORA-20001: Unable to call fnd_ldap_wrapper.create_user due to the following reason:
An unexpected error occured . Please contact System Administrator..(USER_NAME=OIDTEST)

(more…)

Hello everyone,

Reading PlanetMySQL today, I discovered that Alex Gorbachev’s announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn’t on planet.

This plugin has been under development since 2006 and this is a major achievement.

Knowing that my feed is aggregated, and not willing to let this news and this amazing work go unnoticed by the MySQL community during the conference (I am at MySQLConf listening to Amazon.com’s CTO speak right now!)

In any event, if you missed them inline up there, here’s a link to Alex’s announcement with some impressive screenshots, and here’s a link to the product’s home page.

And check out the very positive comments from the first testers already on the announcement post.

Congratulations and thanks, Alex!

For the past few months I’ve been using a query that I refer to as “ash report - recent spike”. That’s the second thing I do when I get a call of the “the system is slow” type. The first thing I do is run “top” (or whichever alternative for the OS) and check the overall CPU usage.

The script is fully RAC-aware, and although it’s not 100% perfect, I use this imperfection to see if any particular node is doing something stupid. Although it is primarily targeted for OLTP systems, it can be useful for data warehouses as well, especially if they use the parallel option.

The query is to the database what “load” (uptime) for a Linux/Unix machine is, except it has much more detail. It is basically a summarization query of the v$active_session_history table. NOTE: you need to have the performance pack license to use it. It is not designed to be aligned, or read. The best is to leave it on just a few lines and concentrate on the results.

It has two “variables” that you can adjust: how far back to look (I use two hours), and how aggressively to look for problems (having count(*) >= 2).

An explanation of how to make sense of the results follows the query.

(more…)