Film is dead, long live film

Have you ever witnessed a piece of technology so grand that you knew it would change the world eventually? When digital cameras hit the mass market, I knew your traditional 35mm camera was dead. Sure, the quality of film photography was better in the beginning, and I still think film takes better pictures. But for everyday snapshots, you just can't beat that little digital camera.

During Christmas, my Mother-in-Law mentioned that she couldn't find 35mm film at the grocery store anymore. I thought nah, people buy film for the Holidays, they're just out. So on our shopping adventures yesterday, I stopped in the photography section of my local Wal-Mart to pick her up some film. They had a couple rolls of Advantix film and a four pack of 800 speed 35mm and that was it. Lots of throw-away cameras, but no film. Three do-it-yourself kiosks for downloading your pictures, but no film.

Huh, maybe the mega-grocery store would have some. Nope, same deal. Lots of disposable cameras, a couple rolls of Advantix, and a few four-packs of 800 speed 35mm. Not even an empty peg where the normal speed film would go.

Now we were on a mission to find either 200 or 400 speed film. Our next stop took us to our local mega-drug store and sure enough they had film. Not a wide selection, mind you, but they had enough to choose.

So what's the significance? Certainly film's not going away. But in the very near future, I'm expecting that the only place you'll be able to pickup film is at a camera shop. I think everything film related becomes a botique item and may actually revive many camera shops that have been wondering about how to survive in a digital world. In the mean time, I wonder if there is a market for Cannon AE-1's on ebay....

Picking a WYSIWYG Editor for Drupal

I've been doing a lot of research lately into setting up Drupal as a Content Management System. One of Drupal's shortcomings (by design) is the current production version doesn't have a WYSIWYG editor. The lack of a visual editor is not a big deal since there are several editors you can just plug-in (more or less) as a module.


The first editor I tried was TinyMCE by Moxicode. I originally got this editor setup in short time with help from Howard Rogers. It was easy to install and did most of what I wanted. However, as I got a little more sophisticated, I started using grids and images more and more. Most of the time my entries came out OK, but every now and then my theme was all messed up because of a misplaced HTML tag. Not a major hassle for me, but if I was going to open my website up to non-technical users, I couldn't risk them unintentionally screwing it up.

So I searched a little more and came up with FCKEditor. It's a little more difficult to install than TinyMCE as you have to go in and modify a PHP file, but the instructions are clear and simple. It also comes with everything you need out of the box and there is little extra configuration that you have to do. However, with that ease of use comes a lack of granularity. So it's a trade-off. Best of all, the grids and images work great with my chosen theme.

For now, I'm sticking with FCKEditor.

Upgrading to R12

Upgrading Oracle Applications is never an easy process. I like to test my upgrades both for completeness as well as performance so I can give my users a semi-accurate picture of how long things will take.

I'm doing a direct upgrade from 11.5.9.CU2 to R12 (probably CU3). I got to the point where the db objects are upgraded and received the following message:

There are now 126521 jobs remaining (current phase=A3):
 6 running, 951 ready to run and 125564 waiting

OK, then. When I upgraded from 11.5.3 to 11.5.9, the object upgrade portion took about 26 hours, but I was on a slower box (Sun 2x900 with 8G RAM) and Oracle 9.2.0.7. Now I'm on a Sun 4x1200 with 16G of RAM and Oracle 10.2.0.3. This is only my first attempt at upgrading and I'll look at the AWR stats after to see where my bottlenecks were during the upgrade.

Any guesses on how long this upgrade run will take?

A properly constructed voicemail, Part I

"Hi, this is Fred, the DB is down. Call me.", is not an appropriate message.

First of all, Fred who? Fred in accounting or Fred in customer service. It makes a difference to me.

Which DB is down? OK, so you don't know the exact database, tell me the application you are using.

Oh, and Fred, leave me a number where I can get back to you. It's 2:00 in the morning and I'm nowhere near a computer to lookup your number.

A properly constructed message would have been:
"Hi, this is Fred in accounting. I'm having a problem logging into Oracle Financials and am getting a 404 error message. Please call me at extension 1234."

Bugs, bugs, and more bugs

You may have noticed that my blog postings have been pretty sparse lately. I get that way when I have to work on Oracle Applications. Whenever I am doing an Apps project I end up having to read about 2000 pages of documentation that point to another 100 documents on Metalink. Not much to blog about there.

By the time I am done reading, the whole process is so utterly complex that I end up putting my own process together and pulling the parts relavent to my installation into a single document.

So I've been working on my master document for about two weeks now and I'm finally ready to install a fresh version of R12 on my pre-test box. Now, I fully expect something to crash and burn in this environment and I'll have to wipe it out and start over in a couple days.

I fired up the installer and right away it encountered a java exception; "java.lang.NullPointerException".

Hmm, maybe I have the CD's staged in the wrong location. So I take another two hours and restage the CDs.

"java.lang.NullPointerException" again.

So I check my DISPLAY and make sure I can start xclock. No problem.

Then I look on Metalink and sure enough, I ran into a bug. The install software that shipped has a bug in it. Good old bug 5972626.

Did anyone even test this stuff before it went on CD? It's bad enough that my standard 10gR2 install has 23 patches applied, but a bug so it doesn't even install?

Why am I surprised?

Posting Pictures to a phpBB forum

I run across this situation almost daily. Somebody needs to post either a screen shot or a picture (same thing) to a phpBB forum and they can't figure out how to do it.

First, you need a third party picture hosting service. I personally prefer Flickr, but there are others like Photobucket and SmugMug out there that allow you to host your pictures for free. You can even host it on your blog on blogger.com! My example will be using Flickr, but really all you need is a URL to the actual image file.

Upload your photo to Flickr. If you don't have a Yahoo account, you can create one for free.


Click "Upload Photo" and either type in the name of your picture file (complete path) or use the "Browse" button to find it. Make sure the "Public" radio button is selected and click "Upload".


Your picture will be uploaded and displayed when it is done. Now, click "Save this Batch".



All your pictures are now displayed.



Click on the picture you just uploaded and then click on the "All Sizes" button:



At the bottom of that page, you will see "2. Grab the photo's URL". Copy this URL into your buffer by highlighting it and pressing CTRL+C.



Now, you're done with Flickr. Next, go to the forum you want to post in and compose a message. Type in the text you wish. When you get to the point you want to insert your image, you will enclose the URL from the step above within [img][/img] tags:


Post your message and the picture will show up inline:

There's more than one way to skin a cat

We're off on an adventure trying to figure out why one of our 9.2.0.5 databases has poor response time during a certain period. During the investigation, I came across a process that was executing a query that had a function call in it. It wasn't our problem, but was interesting none the less.

I started tracing the user's session and found that for every row in table T the query was executing function getCount (the names have been changed and the function simplified to clarify the example).

SELECT x, getcount()
FROM t

OK, seams reasonable enough, right. But inside getCount() was a query:

create or replace function getcount return integer
as
   c integer;
begin
   select count(*) into c from xyz where (code1='7' or code2='3');
   return c;
end;

Ah, here's the query that showed up in the trace as being the top dog.

SELECT COUNT(*)
FROM
XYZ WHERE (CODE1='7' OR CODE2='3')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      2.47       8.55      57428      63059          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.47       8.55      57428      63059          0           1

"Hmm, let me just fix this and let the developer know how to fix his problem" I thought to myself, "he'll appreciate it."

So I suggested the following query for the fix:

select count(*) from (
select id from xyz where code1='7'
union
select id from xyz where code2='3'
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.05       0.05          0        666          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.05          0        666          0           1

The number of logical reads was much better and the wall clock response time was much better as well. Perfectly suitable solution.

Well, proving that DBAs don't know anything about how to write code, the fix ended up being:

create or replace function getcount return integer
as
   cursor c1 is select count(*) cnt from xyz where code1='7';
   cursor c2 is select count(*) cnt from xyz where code2='3';
   t integer := 0;
begin
   for i in c1 loop
      t := t+i.cnt;
   end loop;
   for i in c2 loop
      t := t+i.cnt;
   end loop;
   return t;
end;
/

SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE2='3'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03          0        387          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0        387          0           1

SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE1='7'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.02          0        279          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.02          0        279          0           1

OK, that works. No reason to do something the easy way when you can write code and do it the cool way.

Addition: As Gary points out in the coments, there is a potential in the new function for records with both a '3' AND a '7' to be picked up twice. In my actual data, there is a business rule that makes sure that doesn't happen, but in general it is an issue.

Inexcusable Response

Back in April I filed a TAR on an ORA-00904 error I was getting when I called dbms_stats.gather_index_stats() in 9.2.0.8:

SQL> exec  dbms_stats.gather_index_stats('SCOTT','TIGER_CK');
BEGIN  dbms_stats.gather_index_stats('SCOTT','TIGER_CK'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS",  line 8557
ORA-06512: at "SYS.DBMS_STATS", line 8577
ORA-06512: at line 1

No problem, I already figured out that if I used ANALYZE TABLE I could work around the error. After about 3 weeks of traces and going back and forth, Oracle acknowledge it was bug 3078144 and was fixed on 10.2.

I requested a backport to my version. After all, I was on a supported version so I figured it wouldn't even be an issue. While I was planning on going to 10.2 eventually, it wasn't in the immediate plans. After the first month, I pinged support to see where my backport was.

"Oh, development denied it because it is a Level 3 SR."

Hmm, that's the game we're playing, eh?

"Well, it's happening every day now and affecting my business", I replied. OK, maybe a little fib, but lets see where it goes.

We agreed to raise it to a Level 2 SR.

Another month went by so I followed up again. Still with development. At this point we started planning the migration to 10.2 (not because of this TAR, but we are gluttons for punishment). It would be a race to see if I got my bug fix or my db upgraded to 10.2 first.

About two weeks ago, I decided to ratchet up the pressure; I "duty managed" the TAR. I got the nicest duty manager who knew how to talk the talk, but I wasn't buyin' what he was sellin'.

"I will give you a call within three business days to let you know the status of the bug fix." was his final reply. A week went by with no update.

Well, this past weekend I won - the database was upgraded to 10.2.0.3+. While I was tempted to keep the TAR open just to see how long it took to resolve, I decided honor was the better part of valor and closed the tar indicating we had upgraded.

I bet I don't get a satisfaction survey on this one.

Very Addictive

Only start if you've got plenty of time on your hands.

Cat with Bow Golf

A dying breed?

I usually get a feel for "what's hot" in the marketplace by the number of books on the shelves at my favorite bookstore. A couple years ago there was six shelves at my Borders store filled with books on Oracle. Oracle for Dummies, PL/SQL, and scores of Certification books. I was quite surprised to find only three books on Oracle at the same store this past weekend.

MySQL still has some respect with about a 1/2 shelf. .Net clocked in with about 5 or 6 shelves with a smattering here and there of Java and the associated technologies. Good old Perl shared half a shelf with PHP. But I feel left behind because I don't know Excel Macros (about 12 shelves).

Maybe Oracle has really achieved a self-tuning, self-managing database and we don't need books anymore.

Please excuse me while I apply four more patches to my "up-to-date" 10.2.0.3 installation.

Deadlocks

Good old ORA-00060: deadlock detected while waiting for resource. There's really two main causes for an ORA-00060 error; bad application logic or unindexed foreign keys.

The classic example of a deadlock is userA updating table X and then table Y while userB updates table Y and then table X. Oracle kills one of the users, rolls back his transaction, throws an error to the alert.log and everybody else moves on. As DBAs, we look at these errors, forward them to the appropriate developer and forget about them.

The second most prevalent cause of ORA-00060 errors is unindexed foreign keys. You can check out Oracle's explanation and there are a ton of scripts that will help you diagnose this situation. I personally use a variation of Tom Kyte's script but there are others such as this one.

I know, you're asking yourself "So What? Everybody knows about deadlocks."

Be patient grasshopper, there's more to the story.

A couple weeks ago we started getting ORA-00060 error messages every now and then on a 10.2.0.3 database that rarely received this type of message before. And then some of our critical processes slowed down. And then we started getting about 12 ORA-00060 errors a day.

At first, we attacked it as two separate problems; the ORA-00060 error and performance. One DBA started decoding the trace files from the deadlocks informing the developers of which objects were involved and the data involved. The weird part was the deadlock was occurring on an INSERT statement.

At the same time, I started tracing the slow processes and found that a great majority of time was being spent on "enq: TM - contention" waits. We then ran an AWR report and found that the top wait was "enq: TM - contention" and we knew we had a problem.

The wait event and the ORA-00060 together lead us to an unindexed FK. However, all the scripts we used indicated the fks were indexed. We started disabling the FKs one by one (for the two tables involved in the majority of the deadlocks). After one particular FK was disabled, all our "enq: TM - contention" waits went away and things were back to normal.

Oracle support directed us to bug 6053354 which isn't really a bug because of bug 6066587. Sure enough, the conditions in 6053354 existed in our database. We turned the tablespaces to read-write and re-enabled the FK with no adverse effects.

The moral of the story is follow your instincts up to a point, but every now and then something really freaky is going on.

Back when the internet was fun...

Do you remember way back to 2005 when the internet was fun?

Wanted to check the progress of the NCAA tournament during the day? Just go to http://www.espn.com and your browser refreshes automatically as the action happens.

Read some industry chit-chat during your lunch hour? Open up your favorite news aggregator and read away.

Two years ago I wouldn't have thought twice about forwarding a funny email to friends & family.

Try to go to http://www.espn.com today and you'll likely have to "justify the business need" to three levels of management to get your scores.

My RSS newsfeeds quit working a couple weeks ago. Doesn't matter that 90% of them are industry related, they're not business appropriate.

I stopped forwarding emails to some friends and family because I was tired of getting the bounce messages from their email filter. It's just not the same when you have to tell the joke over the phone and explain why 18 goes into 54 more times than 54 goes into 18.

Ah, the price of progress.

Materialized View Tricks, NOT

While working on another problem, I found this interesting little tidbit with Materialized Views querying views on Oracle 10.2.0.3.

One one db, I have a table called xyz_rtab. The table structure doesn't really matter, it's just any table with a primary key. Then, I create a view on top of the table:

db2> create view xyz_v1 as select * from xyz_rtab;

View created.

Why do that? Well, my table is not really a table in my environment. The view on top of the table is really refactoring a business object that is used by several systems but whose time has come to change. But it doesn't matter, for the purposes of this demonstration.

Next, I change dbs and create the database link I'll use to get this data.

db1> create database link db2 using 'db2';

Database link created.

Now, I query across that dblink to verify that I can actually see the data:

db1> select count(*) from xyz_v1@db2;

COUNT(*)
----------
     10


db1> select count(*) from xyz_rtab@db2;

COUNT(*)
----------
     10

If I can query the data I should be able to create a mview, right? Lets try from the base table:

db1> create materialized view xyz_rtab_mview as select * from xyz_rtab@db2;

Materialized view created.

db1> exec dbms_mview.refresh(list=>'xyz_rtab_mview',method=>'C',atomic_refresh=>true);

PL/SQL procedure successfully completed.

No problem, that's what we expect. Now lets try from the view:

db1> create materialized view xyz_mv1 as select * from xyz_v1@db2;
create materialized view xyz_mv1 as select * from xyz_v1@db2
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1

Nice.

No soup for you!

Have you read about Sprint cutting people off?

Seems as though Sprint is taking on the attitude that if you call them too much, you'll get dropped. Sounds like they think they're an insurance company not a phone carrier. I wonder if you only call twice in the life of your contract they'll give you a refund.

I deal with customer service departments every day. Lets face it, most customer service agents are great reading off a script, but the second you go off script they're clueless. And that's if you can even understand them.

Another point is that the way most customer service departments are setup, there's no incentive for the Customer Service Agent to actually solve your problem. They get financial incentives for closing problems, not solving problems. Ever open a trouble ticket with a certain database vendor and they work on it for a while and then say you have to open a new trouble ticket because they can't solve the problem?

Everybody knows (I know Tom, there are exceptions, but in general) that it costs more to attract customers than to keep them. I can't see many people actually wanting to talk to somebody half a world away on the phone 50 times a month. But that's just me.

Extra credit: Where does "No soup for you!" come from?

I think the database is broken

Below is a somewhat fictional somewhat realistic view of upgrades.

"I think the database is broken" was my first call this morning.

"OK, what error message are you getting?" was my reply. Since I knew we changed some things over the weekend, I figured something got left out and needed fixing.

"My process that normally takes 38 minutes is done in 3."

"OK, does it error out?"

"No."

"Do the results look right?"

"Yes."

"So why do you think it's broken?"

"Because it finished in 3 minutes."

"Well, we upgraded the database version this past weekend and put the database on a faster host, so that's probably the difference."

"But it used to take 38 minutes."

...long pause on the phone by me, expecting something else...

"I can slow it back down if that's what you want...."

On the flip side, I go to my developers to see how things are going and they report increases anywhere from 4-10 times faster depending on how much the db is being used. One savvy developer corners me and says something along the lines of "Can't we move this db to a faster host?"

"Huh? This is the almost latest, greatest, fastest host there is. You've got 3.0Ghz of Dual Core processing power underneath your db where before you had some clunky Sparc processors."

"You know there's 3.2Ghz processors out, right?"

"So, let me get this straight. You're 10 times faster than Friday, and you want to be 10.1 times faster?"

"Yup, that extra speed will really help."

"I need to get back to my office, call me if you have any problems."

Why is it always the database?

When something is slow, why do we always point to the database? A computer system is so much more than "the application" and "the database". It's networks, routers, disks, memory, array controllers, middle-tiers, JDBC Drivers, Operating Systems, and client versions (which, usually comes round to "It's Oracle/MySQL, fix it"). Come to think of it, it's pretty amazing that we get decent performance at all.

Everybody is guilty of it from the novice to the experience person who knows better. Heck, I am sometimes guilty of it myself.

I'll get a call, "Application XYZ is slow, is the database OK?"

"Let me check."

A couple minutes later I indicate everything is fine on my end. I hear three days later that "XYZ was slow because of the database". Argh. Next time that person needs help tuning a query he'll get pointed to the documentation for EXPLAIN PLAN.

Why do we always point to the database first? Is it because the database is usually the guilty party? Or do we point fingers at what we don't understand?

Spike

Got a spike in hits on the blog in the last couple of days. I dug through the hits and found that a lot of people were coming from SearchOracle.com. I followed the most frequent URL and discovered that my post on Multiple Listeners had been linked to an article by Bill Cullen.

What is going on in Oracle's QA department?

I've been an Oracle DBA since the days of 7.0.16 (on Netware, but that's a story for a different day). I'd like to think I have a decent amount of experience with Oracle and it's nuances. Sure, some things are quirky and don't work the way you think they should, but that's just getting used to the software. I can count on one hand the number of one-off patches I had to install over a base patchset in 7.x to 9.2. (I'm talking RDBMS here, not Oracle Apps.)

I specifically waited to move to 10g until it had been out a while. Our move to 10.2.0.3 has been quite disappointing. Sure, we did a lot of testing. But production is different than testing. After about 30 days of one db being live, we've had to install three one-off patches. And we just got slammed with two more in the last couple days to bring the total up to five. If we were on 10.2.0.0, I'd understand, but 10.2.0.3? Five freaking one-off patches.

C'mon Oracle, get your act together. We're trying to run a business here.

Oracle 10g upgrade gotcha

Everybody knows the Cost Based Optimizer needs decent statistics to figure out what his query plan should be. Sure, it takes time, but everybody does it. Some estimate, some compute, some think they have a better way.

After upgrading from 9.2.0.7 to 10.2.0.3, one of our systems suddenly slowed down. Not directly after the upgrade, but three days later. After some investigation, we found that some of the statistics had been estimated and not computed. That was pretty much a shock because my analyze method usually specifies estimate_percent=>null to make sure we compute. (Note, I already figured out that the default estimate_percent changed from 9i to 10g, so that wasn't it.)

I checked my custom analyze method and it reported that the table was never analyzed by me.

Hmmm, now it's getting real interesting. Just for kicks, I reran dbms_stats.gather_table_stats without an estimate_percent parameter and it estimated at about 8.3%. Aha! Must be using the AUTO_SAMPLE_SIZE for some reason.

So why?

Then I looked at the last_analyzed time of the tables that had been estimated. They all were run between 22:00 and 01:00. Aha, a schedule. Then I looked in dba_scheduler_jobs and there it was. A new job that Oracle automatically sets up to analyze stale objects.

When I checked in the morning, the objects didn't report as being stale. Then, after the day's data, Oracle decided the statistics were stale and analyzed the tables. When I went to gather statistics for stale objects the next morning, they had already been analyzed and weren't stale anymore. Except the statistics weren't optimal which made our queries "go bad".

Fortunately, there is a way you can disable this job just by tweaking the schedule.

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

Keywords

One thing that Statcounter provides me is how often certain keywords are found in my blog. Some of the more popular are:

• ORA-27054: I assume this is by people trying to backup using RMAN over NFS. I hope they found my problem and most importantly, my solution.
• Some combination of DB Links and ORA-01017: I'm sure people found my distributed issues with 9i and 10g working together. But that's not always the issue. If you are using what I call "unauthenticated" database links (no password in the definition) then the user authentication on both the source and remote database need to be the same. If we're talking an OS Authenticated user, then that user has to be OS Authenticated on both. If it's a regular db user, the passwords have to match.
• Lots of people looking for some varient of backup and methodology. I hope they found lots of info in the Backup Top 11, but I don't think I've ever described my backup methodology for Oracle. Maybe in a blog post in the future.
• I get lots of hits on the Quick and Dirty Backup for MySQL. Seems like a lot of people are looking for an easy solution to MySQL without having to fork over some cash for backup software.
• I also get lots of hits on The MySQL to an Oracle DBA series, but not too many through keyword searches.
• For the guy who's searching "mysql php round differently", I'd look at your datatype in the database to make sure you're using the proper number type.
• Looking for information on "_db_writer_flush_imu"? Better know what you're doing.
• Oh, and the person looking for "ingres porn server"...good luck on that.