Oracle Development

Divide and Conquer

2012-01-05T13:41:00.001-08:00

My customer is doing an acceptance test of my latest code changes. Things are not working well. Initiially I found that they were getting blocked by one of the business rules in the system. Then I determined they were running the wrong scripts. Finally the code was getting an Oracle error.

The pressure was mounting. I got on a conference call with a bunch of managers. I needed to figure this out fast. The script that was bombing was not huge. But the SQL in it was massive. Lots of joins and unions and large predicates. Ouch. How was I going to figure this out?

Then I decided to try to find a shortcut. The call stack said a function in a PL/SQL package was actually generating the exception. I checked and found we got lucky. There were only two places where the script accessed the package. Bamm. We were trying to construct a date with a value that is sometimes aritificially too large.

Sometimes you can get saved by a little divide and conquery.

The Usual Suspects

2011-12-14T10:24:00.000-08:00

We had a big database job that runs slowly at night. Sometimes it needs to be killed to keep the show running. My boss wanted me to work on this problem. He had a lot of ideas. So did I. My plan was to isolate what is slow, then figure out how to make it fast. I got overruled by the boss.

My first task was to document what this big database job does. Done. I got a document that explains in English what the purpose of each part of the job is. Then I was told to find out how each of the parts is related.

I did a bunch of analysis. I determined what pieces depend on which other ones. After some deep analysis, I figured out what are the inputs and outputs for each of the jobs. This was hard because the package that runs the job is 7k lines of code. That package makes use of a lot of other helper packages that are a lot more than 7k lines.

Now my new tasking is to figure out if we can roll any of the many cursors up into a small number of cursors. I have have to determine whether some database indexes could make the job run faster. I have the option to isolate some SQL and pass it off to some DBAs for analysis and tuning.

Who knows where all this is going to end up. We might get some performance gains. Then again, I might be just running around in circles. I would love to be given the mission to just make this thing fast. But somebody else higher up on the food chain wants to drive the task with their ideas. Frustrating. Let's hope this does not turn out to be a fail. My time is money - literally.

Record Types

2011-12-09T14:42:00.000-08:00

Today is the last day for a developer on our project. He moved his family away, and has a new job far away. The company asked him to fix one troubling bug before he left. He was going crazy and asked me for some help. He set up some debugging in a PL/SQL package where he dumped out some contents to a log file. But they were just not making sense.

I took a look at his log. One of the variables was zero. He insisted that the source column for that variable was non-zero in the database. Then he started randomly asking whether this or that was the cause of the problem. I told him we should not guess, and inspect the place where the variable is initialized.

I found a nice routine which loaded the data from a table. It puts it into an object which is a custom record type. We select a bunch of fields into this object. The order of the select clause is supposed to match the order of records in the field. So I checked the one field in question. Oh oh. It was out of alignment.

Somebody added a new field at the end of the record. However the put the query for this new field in the middle of the SQL statement. Bamn. That's the problem. This was problably not a great design. We should make the select more explicit to put the value into fields of the record type. Maybe an idead for a redesign.

Package Analysis

2011-12-06T11:24:00.000-08:00

We got this big PL/SQL package that contains code which runs every night. The thing does a lot of stuff such as probem detection/correction, business rule implementation, and metadata regeneration. The code has been growing over the years. This past year the job start running too long. We tried some hacks but did not get very far. We are still attempting some hacks to get the darn thing to complete on time.

Luckily I got assigned the task to analyze this monster. I started by documenting what each part of the massive job does. It was a reverse requirements analysis task. Then I started to catalog which tables each of the procedures used, and which tables got modified by the procedures. I am starting to come up with an understanding of what procedures depend on what other ones. Isn't there some software that can do this automatically for me? If not, might be time to write some.

Slow Install

2011-10-18T14:19:00.000-07:00

My customer gave me a new laptop today. Even though I do most of my work in a virtual machine, I sometimes need to access the database right off the laptop. So I decide to install the Oracle 10g client. We are currently at 10.2.0.1.

I found the install media on a network drive. When I launched the executable, it was a while for the Universal installer to come up. Then each step of the installer was painfully slow. Here is the real sad part. I have been in the middle of this install for over two hours now. Progress bar reads 92% complete.

Not sure if this is due to the media being on a network drive. I plan to downloaded the media to my desktop if I ever need to do this again. I also chose a customer install, and checked a bunch of things that looked cool in the client. I might try that again, but will do the bare bones install first. Then I came come back later in install the goodies.

Script Failure

2011-09-22T11:27:00.000-07:00

A developer was writing a script to set up some configuration data via script. He complained that the script was completing successfully, but the data was not getting inserted. He asked for help. His thought was that the success should mean that the records should be inserted.

Immediately I pointed out that running the script and seeing success means that the script did not throw any exceptions that were not caught. We looked at his script and saw that it was eating any exceptions. The developer floudered around some more. I later advised him to forget about the script, move over to SQL*Plus, and manually execute the SQL statements one at a time.

Bingo. In SQL*Plus, he found that some constraints were being violated and causing exceptions to be thrown. This taught me two things. You shouuld not jump to the script until you are sure your raw SQL is good. You should also not rely on some tool to execute your script if you are not in full command of what it is doing.

SQL Loader to the Rescue

2011-09-15T10:13:00.000-07:00

I gave myself an action item to try to do some hands on SQL Loader practice. It turns out it is not that hard for default loading from file. I specified the filename, used absolute positioning, and loaded stuff into my database table.

The reference I used to learn how to do this was the SQL Loader FAQ. You should check it out. The FAQ goes over a lot of common scenarios, and how you use SQL Loader to accomplish your work. Good stuff I tell you.

So far I only tried SQL loading files with 10 to 15 records. The next step is to try to load 10k to 15k records and beyong. I got a good feeling about it. They say that SQL Loader is optimized to handle the big jobs. I should have already known this. The team on my project that loads huge files from the mainframe primarily uses SQL Loader to do the job.

Huge Script

2011-09-14T22:20:00.000-07:00

I had a task to deal with a large amount of data for our customer. Being the programmer that I am, I got a couple files with the data. Then I ran them through a custome Java program to extract out the data of interest. Finally I stuck the data in a massive SQL script to do some work.

I did all my tests with some dummy data. Then when I got the actual data in the script, I ran one last smoke test to ensure the thing worked. It did not. The script kept hanging without inserting a single record. I called my DBA. He told me I should have used SQL*Loader. Well I prefer custom PL/SQL. Plus I needed to implement some logic for each of the inserts.

There was a tight deadline for this work to be completed. I decided to split the script up into a number of smaller scripts that actually did complete. The original script had about 300,000 lines in it. I found that 20,000 and 30,000 line scripts worked fine. So my plan is to break the big mamma up into 10 separate script. I will test them out and let you know how I fare.

New action item for self - learn how to do stuff with SQL*Loader.

Oracle 11g Express Edition

2011-07-27T10:50:00.000-07:00

Recently I had to upgrade my company laptop to Windows 7. This was a painful ordeal that lasted a whole weekend. The upgrade consisted of a reimaging of my machine. One byproduct was that my Oracle 10g database was wiped off the machine. Ooops. I forgot to back that thing up.

I decided to reinstall Oracle after the upgrade. It was interesting to see that Oracle had an 11g version of their Express Edition free database. The latest version is 11.2, which is currently in Beta.

I was pleased with how fast I could install the Express Edition database. This thing was actually at Express speeds. I was also reminded that Express Edition does not come with much. You get a command line SQL tool. You get tools to start, stop, backup, and restore the database. That is it. Looks like I need to polish off my command line DBA command line programming.

The Trigger Project

2011-07-12T20:24:00.000-07:00

There has been a lot of drama on our project based on performance problems we are having in production. Some people are attributing the performance issues to the number of triggers we have in the database. That sounds like nonsense to me. I deal in hard facts. There was a total lack of evidence to back up this theory. So I have decided to run some tests in our database to measure the overhead of adding triggers to DML.

I wrote a harness script that updates 10,000 records. It commits the change every update. The thing runs at a good speed. It takes around a minute to complete. The table currently has no triggers on it. Then I added one trigger that did nothing in the body of the trigger. I was shocked to find the harness took over 20% longer to run with the trigger.

I had another developer present when I ran the test. He could not believe the results either. Then he had some suggestions to vet the results. I ran the tests a couple more time. Looks like the first run was a fluke. He also suggested I run the tests locally on a database on my machine to eliminate irrelevant variables.

So next I plan to move the tests over to a local database on my machine. This way the network is not a factor. Other uses on the database are also eliminated from the equation. Next I am going to time having a lot of triggers on a table. I will implement some cascading triggers. When I am done, I will have a lot of empirical evidence to back up my opinoin that triggers in and of themselves have no impact on performance.

Triggers Did It

2011-07-08T07:13:00.000-07:00

A job that runs at night has been taking too long every night this week. This causes pain as the system administrators call up our DBAs or help desk in the middle of the night. Obviously we have a problem. I would call it a performance problem. What makes this unique is the response I heard from this. Developers told me a manager decided there would be no new database triggers implemented due to the performance problem.

WTF? This made little sense. Who correlated our performance problem to the presence of triggers? I attended a meeting hosted by this very manager. So I asked about this new "no triggers" edict. He said that he has inspected the PL/SQL source code. He found many database triggers. He also saw triggers firings that caused other triggers to fire as well. Therefore they were suspects in the performance problem.

I said that by this logic, we would find many stored procedures in our source code. The stored procedures get exectured. The code runs slow. Therefore we should not use stored procedures. Obviously this argument does not hold up.

Rather than being an obnoxious developer who shoots down ideas, I decided to later pitch a plan to get to the actual root cause of the performance problem. We should find out what pieces of the code are taking a long time to execute. Then would should drill down into the slow pieces and find out why. If we do that and find some database triggers to blame, I agree we should optimize them.

I come to find that there is a proposal to do the very thing I recommend. Our manager clarified that we cannot add database triggers without analyzing the performance impact. I am all for that. I run timing studies all the time on slow code. We need to use logic and hard evidence to deal with tough and frustrating problems such as performance. If not, we might as well chirp "add more indexes" like any other lackey and get nowhere.

From the Trenches

2011-07-06T13:38:00.000-07:00

I have been following a seminar on how to be a DBA. These talks give you hands on advice. They are geared towards individuals who find themselves appointed as a DBA by accident. While I am not formally a DBA, I often do some light DBA tasks. So I thought I would study up.

When you start a DBA job, you obviously need to find out what servers and databases you are responsible for. But another important discovery is to find which databases you are not supposed to manage. You should gain info on your systems by talking to actual users. If they are not available, rely on knowledge from developers and testers of the system.

I found out about a nice option in TOAD where you are execute a query against many databases at once. My tool of choice is an old copy of PL/SQL Developer. So far I only log into one database at a time to run queries.

Make sure you find out the cycles of use by the business. This will help you scope out the best time for backups of the database. Speaking of backups, make sure you can recover using the backups. Test out a restore to a different system. It is no good to do backups if in the end they do not work.

Global Temporary Tables

2011-07-01T11:02:00.000-07:00

Our system uses temporary tables at times. It makes the data access simpler across different processes such as apps, procedures, and reports. Temporary in this sense means that a table is created, popoulated, used, and then dropped. However there is another type of temporary table in Oracle.

Global temporary tables (GTT) are implemented by Oracle. The are fast in that they have no redo or rollback. They operate in one of two ways. They either lose their data at the end of a transaction, or the end of a session. Which way is determined when you create the table.

GTTs themselves are static. The table structure is created once and remains in place always. It is the data that is transient. The data is private per session. No statistics are gathered on these tables. The data resides in the TEMP tablespace. They are cached in the buffer cache.

GTTs have been available since Oracle 8i. I would recommend you be familiar with them. You never know when you might find a table in your database that is a GTT.

Password and Login Woes

2011-06-16T00:28:00.000-07:00

A couple things have changed in our system. The big change is a move to Oracle 11g on the back end. The security folks also changed our password validation function. The end result is that new accounts and old ones that get their password changed get locked. Doh.

Our apps rely on the Oracle 10g client on the workstation. We use the Oracle provider to manage accounts whose passwords expire. The password change dialog comes up automatically when the password has expired. New accounts get set with a temporary password which starts out expired.

Now when our users try to change their password in these scenarios, the password change hangs. Dang. Some DBAs and testers tried to do some debugging of this problem. At first they thought it was the new password verification function. However the problem persisted when those changes were backed out. Now a developer needs to get in there and figure out what is wrong.

Directory Assistance

2011-05-17T11:01:00.000-07:00

I got a new project that I am developing. And I needed to crunch some data for it. What better way to do that than using an Oracle database? So I installed the Oracle 10g Express Edition on my laptop. I did not need any great power.

One of the first tasks I needed to complete was to load in some data from a file. The logic to parse the data was a bit involved. Therefore I decided to write it in PL/SQL. To use the UTL_FILE pacakge to access the file, I needed to create a directory object.

The web user management tool provided by Oracle 10g Express edition did not list CREATE ANY DIRECTORY as one of the grants it could assign. Therefore I dropped down to the command line to get that job done. Next my stored procedure could not see UTL_FILE. I had to log in as SYS and grant access to this package. Wow that is a lot of work to read a little file. I guess this is not your standard use of an Oracle database.

Oracle Not Available

2011-05-11T07:27:00.000-07:00

I had to return to a project I worked on last year. The first order of business was to ensure I could still run the program. Immediately the thing died with an ORA-01034: ORACLE not available.

The program was just running sqlplus, having it execute a script that had a single EXIT statement in it. I was able to successfully execute the sqlplus statement from the UNIX command prompt. Why was the program not able to do the same without error?

I googled around and found that this error happens when your ORACLE_HOME and ORACLE_SID do not match. These environment variables seemed good. I was pulling my teeth trying to figure this out. Eventually I found that the program was sourcing a different profile. This profile pointed to the wrong Oracle home. Dang.

Adaptive Cursor Sharing

2011-05-09T08:09:00.001-07:00

We are going to Oracle 11g in our customer's system. One of our DBAs took a look at the new features for 11g. The developers were supposed to see whether we could take advantage of these features. So far I only took a look at the first feature mentioned. That is Adaptive Cursor Sharing (ACS).

There are specific requirements for ACS to kick in. You need to be doing an EXECUTE IMMEDIATE in your PL/SQL. You also need to be using bind variables. Finally I hear that if you use too many bind variables (15 or more), then ACS will be skipped.

The optimizer may use different execution paths based on the values in the bind variables. Skewed column data may cause the execution to be suboptimal. Normally the optimizer makes use of histogram data generated from gathering statics.

With ACS, the database tries to determine which values for bind variables work best with certain execution paths. Then it would use this information in the future to choose the best execution path. We get this benefit automatically in 11g. It can be disabled. I am thinking we shall not and try to get the benefit.

Database Costs

2011-05-06T12:31:00.000-07:00

I just looked a the pricing structure of Microsoft's SQL Server platform. Some versions mimic the Oracle cost. There is a free express version of SQL Server. The developer edition also has no cost. The minimal web version of the database costs $3k. Workgroup goes for $7k. Enterprise comes in at $27k. And the Datacenter edition is a whopping $54k.

There are other fees that might bring the total cost a lot higher. If you install a separate copy on a virtual machine, you might need another license. Here is one good way to save costs. Use multi instancing. This is similar to Oracle database instances. They all have their own CPU, memory, and disk allocated. However the SQL Server instances all share the same operating system and physcial machine. There is no extra cost for an additional instance in terms of licensing.

Here is a final tip that I learned at a seminar I watched today. Do not keep the default install options for Microsoft's SQL Server. This is especially important if you are installing the production database. I wonder if the same holds true for Oracle.

Roles of the DBA

2011-05-05T12:26:00.000-07:00

Today I continue sharing some things I learned watching a seminar about becoming a DBA. There are two main types of DBAs: (1) the development DBA and (2) the operational DBA. The seminar I watched focues on the operational DBA.

This type of DBA needs strong customer service skills. You will be talking to a lot of people. In fact, you will be the liason between many groups. Consider yourself the point of contact for all things support related.

Take responsibility. A DBA is the guardian of the data. You must own your platform like you own a house. Uptime is your responsibility.

One major part of a DBA's responsibility is to guard against SQL injection attacks. Apps must sanitize user input. Many web apps do not. The result is that hackers can send in SQL statements, and maybe even compromise your database box.

Surprise. You are the DBA.

2011-05-04T12:20:00.000-07:00

I just finished watching a seminar on becoming a DBA. It seems a lot of times people get drafted into the DBA role. This is not a bad thing always. The DBA is a good career path. But I also saw a lot of potential for DBA pain.

You need to keep your boss happy in any job. But the DBA must keep the user happy. Otherwise you will suffer. There are ways to set up your environment to increase the chance of succes.
Have a sandbox to try out new technologies. Put your development database on a separate physical machine. Be ready to restore a production backup to the test environment. And have a staging database that mimics everything you see in production if your project has the cash.

Performance Redux

2011-05-02T07:34:00.000-07:00

We continue to have performance problems with our nightly processes. I extracted some SQL that could run during the day. However a manager shot that idea down. It would look too suspicious to the customer. My next idea is to replicate the performance problem.

The production operations are taking a whopping 5 to 6 seconds per action. Each action has a number of updates and inserts. I got a small (500 record) sample in development. There is was only taking a tenth of a second to complete.

I ramped up the volume to 5000 records. Things slowed down a little bit to three tenths of a second per action. Now I want to try just a bit more data to see if preformance in develpment degrades futher.

Once I can recreate the problem<, I can start with the performance debug. I think I might log the different parts of the action to see where we are spending most of the time. Then it will be time for that magic SQL performance work.

Performance Problems

2011-04-18T20:08:00.000-07:00

We have a database job that runs every night. It takes care of all kinds of tasks. For example, it detects and resolves data problems. It also implements certain time based business requirements. Lately this job has been taking a lot of time to complete.

I traced one certain part of the job that was taking all night. It was looping through a ton of records, then doing a bunch of updates on them. The algorithm to do the updates was painfully inefficient. I replaced all of this with single SQL updates of all the records at once. The job that took all night completed in 10 minutes.

The job was doing well for a while. Then it got slow again. Another developer started to look at it. All he came up with was that there were a lot of triggers on the tables involved.

I hate when somebody tries to solve a performance problem and asks if we can add some indexes to make it faster. Ouch. The latest analysis felt like this type of solution. Sure you don't want to do a lot of work in triggers for updates that happen frequently. But triggers, lots of them, or complicated ones do not always mean slow performance.

Performance tuning is quite a deep subject. Maybe we need to send some of our developers to training on performance analysis and tuning. That might get us past the old "add indexes" solutions.

Pains from a Hack

2011-04-10T02:17:00.000-07:00

I got assigned to work a curious bug in the system. The customer said a bunch of data was in the wrong state. I called our customer and got some more info. The best help they gave me was some examples in production.

I studied the audits and found the bad state transition. Then I poured over our database packages to find out why this was happening. Then I found the guilty party.

Somebody was making a call to some existing procedures in our packages. They found out they needed to set some data to an intermediate value first for the code to work. However when the code encountered an error, the package code left the data in the bad intermediate state.

Fail. I took out the hack. then I modified the packages to work with the new data pattern. Couldn't the original programmer have done this? Do the hard work up front. It will save everybody some pain. Next I started looking into why there was an error in the first place. That's a story for another time.

Making the Connection

2011-03-31T11:02:00.000-07:00

I got a call for help from a fellow developer. She had installed the Oracle 11g client. And she could not connect via SQL*Plus or SQL Developer. So I went over to see what was wrong.

The first problem was that she was using the SQL*Plus that comes with the client. This is different than the old Windows sqlplusw. You only get asked for username and password. If you only enter those two, SQL*Plus assumes you are trying to connect to a local database on your workstation. It turns out she was getting a TNS protocol adaptor error.

I had her type in username@Dbname. That did the trick. She was able to get into SQL*Plus. Then we looked at her connection in SQL Developer. She was using the default that also tried to connect to localhost on port 1521. She typed in the actual host name and port. Then she was good to go for SQL Developer. She could have alternatively changed the connection type to TNS. We set up a TNS_ADMIN variable that points to a "tnsnames.ora" file. That would have made life easier.

Just now I got another email for help from a different developer. He was wondering whether we have PL/SQL Developer on our new machines. I told him to try out Oracle's SQL Developer. Let's see how he fares.

No Place Like Home

2011-03-30T06:59:00.001-07:00

After having installed the new Oracle 11g client, I wanted to put PL/SQL Developer on my machine. Ooops. I forgot that my PL/SQL Developer required a bunch of passwords for installation. I dug up my printout with all those passwords. Once I installed PL/SQL Developer, I launched it hoping to execute some queries. No luck. It kept complaining that SQL*Net was not installed.

This seemed vaguely familiar. I googled this problem. Some people recommended that I hack up the Oracle entries in the Windows registry. That did not seem too encouraging. Instead I tried applying some settings in the PL/SQL Preferences tab. I hard coded the Oracle Home directory in there. Still no help.

Finally I did some more googling on the problem. Someone smart advised me to put in the full path for the OCI Library. Bamm. That did the trick. I am recording my experience here so I can come back the next time I install PL/SQL Developer and maybe a new Oracle client.

First Look at 11g

2011-03-25T16:18:00.000-07:00

We finally got around to installing the Oracle 11g client for our new development. This is an upgrade from Oracle 10g. Our first test was to try to log into the 10g database uses the 11g client. But we could not find the Windows SQL*Plus client (sqlplusw.exe).

Doh. Oracle is no longer shipping this product. They want you to use SQL Developer. Weak. I am contemplating bringing the one from the 10g client. Come on Oracle. We like sqlplusw. Yeah they ship the normal SQL*Plus. The DBAs use it. But us developers want our Windows version.

Script Mystery

2011-03-18T21:04:00.001-07:00

I got to work and had a lot of email to deal with. Another developer dropped back and said he was having problem with a script he wrote. It was supposed to insert 180+ records. But only 15 records were working. I told him I was busy, but he should run the thing from SQL*Plus to ensure some tool as not confusing him.

Later the developer returned and said he needed help badly. People were calling him asking him for the completed script. I took a look and indeed only 15 records were in the table after he ran the script. I went through he whole script and found an exception handler at the end. It logged and error and exited.

The developer thought the script was running successfully. But the exception handler was masking the true error. A primary key constraint was being violated. As soon as he got rid of the duplicate inserts in his code, the thing worked fine. You can start with another sample script to speed development. But make sure you know what you are starting with.

Could Not Find Program Unit Being Called

2011-03-16T19:54:00.001-07:00

I was told to write a stand alone script to clear out a backlog of work that needed done. It was caused by a slow nightly process that had to get aborted. I coded a new procedure in one of our packages. My script was very simple. It logged the start and end times of the script. The only other thing it did was call the stored procedure with the correct parameters.

Now I wanted to make sure the performance of the stand alone script was acceptable. So I looked in the log to see the start and end times. I was shocked to find tons of ORA-06508 errors being logged by one of the triggers in the database. Now this error means some stored procedure could not be found, or was invalid. This darn trigger was calling all kinds of procedures in many different packages. I guess I could have recompiled them all.

I did find a gem of an idea on Akdora's blog. Instead of just logged the error message in the exception handler, you can store the output of DMBS_UTLITY.FORMAT_ERROR_STACK. This tells you exactly what object is missing or invalid. Thanks Akdora. You helped me get the problem solved and still get home in time.

PL/SQL Review

2011-03-16T07:55:00.000-07:00

I just went through a recap of the PL/SQL programming language. It was good. PL/SQL is the programming language for the Oracle database. It can improve application performance but compiling stored procedure code into the database.

PL/SQL is arranged in blocks. Blocks with names are procedures or functions. You can run PL/SQL from the Oracle SQL*Plus tool. You can also run it through an IDE like Oracle SQL Developer.

I hear that new developers continue to come into the PL/SQL programming community.

Oracle Database Firewall

2011-03-14T12:09:00.001-07:00

Oracle has released a database firewall product. It analyzes SQL to determine whether to block or log the activity. This functionality comes from F5 Corporation, with which Oracle has struck a partnership.

Normally this is the territory of database activity monitoring products. Companies with these products are downplaying the security of the Oracle database firewall.

There are ways to get around the firewall. For example, our UNIX guys log into their UNIX accounts on the machine that hosts the database. Then they use a local connection to the database. Once they do that, all they SQL bypasses the firewall. Ooops.

Users are Blocked

2011-03-04T21:29:00.000-08:00

I was working on some low priority customer problems for a manager. Then all holy heck broke loose. I was getting emails and calls like crazy. The system supporting all our customers was slowing down to a crawl. That should not be possible as we have massive hardware.

The DBA team was on hand to monitor what was going on. User row-level locks were in contention. Our applications were hanging. The DBA teams used some tools to identify one of the first locks that happened in the morning. I knew the business logic behind the SQL.

We got in touch with the user. She killed her application that had been hung all morning. This automatically released the locks and the system returned to a normal state. The lead DBA asked us developers to look at the locking strategy used by the application. He recommended a potential redesign of the app. I took a note, then ran off to the next set of emergencies.

VirtualBox

2011-02-28T08:19:00.000-08:00

A friend of mine asked me if I knew about Oracle's VirtualBox software. I was ashamed that I did not. I consider myself an Oracle developer.

VirtualBox is a free open source product released under a GNU license. It does operating system virtualization. You can run multiple copies of virtual operating systems at the same time.

To start with, you need a host operating system such as Windows XP or Windows 7 for the Windows platform. Then you can run all kinds of virtual guest operating systems with VirtualBox.

Why would you want to do this? Well you can test many platforms with just one physical host platform. VirtualBox also can simulate SMP up to 32 cores (which are all virtual). You can also take snapshots of the o/s state and restore to them.

I like the idea of having a snapshot I can revert to if my o/s gets corrupted by some nasty virus.

Keep Business Rules out of Triggers

2011-01-27T11:15:00.000-08:00

My old team needed some assistance with a problem that was happening in production. Rows from a database table were mysteriously disappearing when the users ran a certain application. The developers swore that their code did not delete these records.

Since I trust nobody, I scanned the source code of all the database triggers in the system. Sure enough, there was a trigger on another table that deleted the records when a certain field got set to a value. Oh snap. This is just poor design. That's not what triggers are for.

There are two lessons to learn from this. One is to practice solid design and keep business rules out of the database triggers. The second is that the client server developers need to keep an eye on the code in the database triggers.

Promotion Woes

2011-01-21T11:43:00.000-08:00

I had to fix a problem they were having using a back end process in production. Seems a processing command was resulting in an Oracle error. I investigated and found that the key stored procedure was commented out of the package. So I reenabled the procedure and thought we were good to go.

I got a call from our DBA. He said my code changes would not work in some of the other database schemas. When I got in to work, I found that the other schemas were missing a key column in the staging database table. That should never happen. We got a database configuration management issue.

So I thought we could just add the column everywhere. Some more analysis showed me this would not correct the whole problem. In the end I decided to recode the stored procedure to use another technique to derive the necessary data using some assumptions. This whole episode made me a bit leery about the state of our databases.

The Dreaded ORA-01848

2010-12-01T12:16:00.000-08:00

I got a phone call and email from the customer this morning. The new version of our software was bombing. The error was ORA-01848: day of year must be between 1 and 365. Ouch. That looks like such an amateur programming error.

So I traced where we were doing the date stuff in our code. Turns out we were trying to compose a DATE value. The input to the date was some data in the database. Yep. The customer test data was invalid. I pointed this out to them. Some of their days were well over 365 in value.

I got another call in the afternoon. They were still getting the ORA-01848. I walked them through to check whether the bay dates were fixed. They were. Must be something else. Made sure they were going to the right database. Yep. But there was only one place in the code where we did this date stuff.

Finally it hit me. Maybe some of their dates were too small. Yep. Had some records where the day was 000 (minimum must be at least 001). Now I am crossing my fingers. The moral of the story might be for me to validate all the data before using it in any date calculations.

Block Safety

2010-11-10T11:51:00.000-08:00

I got a task to go find all transactions that have failed due to a bug, and retry them at night. It sounds simple enough. The retry really means to reimplement the logic that the application would have done at the hands of a user.

My only real concern was what would happen if one of those transactions ran into a problem at night. Sounds like a job for exception processing. At first I just stuck an EXCEPTION handler in the middle of the loop. Of course that resulted in compilation errors.

Well what was I to do? Had to recall that the EXCEPTION is a part of a BEGIN-END pair. So I slapped a BEGIN-END block in the middle of my loop. Within that I added my exception handler. Now the code is rock solid. That is, even if there are some errors, this script will go on.

Home Made Locks

2010-10-28T19:43:00.000-07:00

I am working on some high priority problems my previous team is having. The customers are mad and want a fix yesterday. The applications keep aborting with the same error messages. I traced this down in the code to some object locking code.

This was Oracle Pro*C code that implements locks with records in a locks database table. When the users need to obtain a lock, they try to insert a record in this table. If anybody else has already inserted a similar record (has a lock), then the second lock immediately fails.

When the lock fails, the application displays an error message and aborts. This really irks our customer. So the first order of business is to make sure the application does not abort in this scenario. Next I figure we need the application to wait around a while until the lock might become free. why not let the Oracle database manage this contention? I figure somebody has already solved the queued lock problem. Why try to implement it again in our own code, especially when our implementation will be substandard.

Database Links

2010-10-02T21:19:00.000-07:00

I need to create a lot of test data for unit testing some new code. Now I already had some good data in another database. So I used a tool to create some scripts that insert the data into the new database. There were some problems with this technique. My database tables have a lot of columns in them. The creation of insert scripts by the tool puts the whole insert statement on one line. This line turns out to be much too long for SQL*Plus to process. So I started to manually break up the lines. There had to be a better way.

Database links came to the rescue. You create the objects by specifying which database to connect to. You also need to provide login credentials when creating the link. Then you can perform queries that access the tables from the other database through the link. This was awesome. The link itself becomes an object in your schema. I hear you can even connect to non-Oracle database. I have no need for that feature though.

You do need to make sure the remote database you are linking to is in the tnsnames file on your server. Remember to do a COMMIT after you run the query through the link to release the rollback segment in the other database. The link operates like a pointer. If you really wanted to, a link can be used as a poor man's replication technology. If you want to get some information on your links, check out the DBA_DB_LINKS view.

Commitment Woes

2010-10-01T20:37:00.001-07:00

I ran a bunch of routines in a stored procedures to generate some data. Then I queried that database from the SQL*Plus tool I previously used to create the data. The checks showed the new data was there. Finally I logged into my operating system account, and ran some shell scripts that ran my whole test suite.

The funny thing is that I could not see the results over in my SQL*Plus session. That was strange. The shell scripts call code that commit the changes. I had to take a break to analyze this oddity. Then it came to me. My SQL*Plus work had not been committed in the first place. Therefore the test suite in a separate session could not see the uncommitted changes. Doh!

If you thought that one was bad, listen to this story. I made some changes to the database. Then I called a stored procedure to run some jobs to process that data. Worked good the first time. Then I repeated the process. Changed up some data, called the exact stored procedure. This time the changes I made did not take. WTF? I even did a commit but got wrong results. A lot of inspection got me the answer. The stored procedure was in a package that initialized some internal variables. This package initialization happens once per session. My jobs were being run by one SQL*Plus session. I needed to log out and log back in to get the package initialization to rerun and process the new data. Ouch. That's a tricky one.

Trouble with Groups

2010-09-27T11:44:00.000-07:00

I inherited some back end code. Most of it is written in PL/SQL. That's the part I like. The problem is that I cannot get the darn thing to run to completion without errors. The latest issue is with a file that is generated.

The developer uses the UTL_FILE built in package to create a file and write to it. That sounds reasonable. However the Oracle database is hosted on a UNIX server. So when it creates the file, the thing is owned by user oracle and by the oinstall group. I am not oracle. And I do not belong to the oinstall group.

Guess what? That means I cannot do anything with the file that got created by UTIL_FILE. I cannot view the file contents. I cannot delete the file. Nor can I move it. This is just no fun. What am I supposed to do? My first reaction was to just become a member of the oinstall group. However that raised some flags with the DBAs and system administrators. Although I agree this sounds wrong, what is a developer to do? I have put in a request for assistance with one of the DBAs. Hopefully help is on the way.

Mysterious Foreign Key Issue

2010-09-17T10:45:00.000-07:00

I need to do a lot of unit tests of some database code soon. So I wrote a script that would reset all the data. This script had to remove a lot of records from some tables. I figured the quickest way to do that would be to truncate the tables. I carefully chose the order of the truncation to ensure that the tables with foreign key references would be chopped first.

When I ran my script, I kept getting an ORA-02266 error message. This is defined as "unique/primary keys in table referenced by enabled foreign keys". This seemed strange. This was happening even when there were no records in the foreign and primary key tables. At first I thought maybe some synonyms were resolving to the wrong tables. But even when I prefaced table names with their schemas, I got the error.

In the end I could not figure out what was going on. I replaced the TRUNCATE commands with DELETE statements. Those worked fine. Is this some sort of database error? Or maybe you just are not allowed to TRUNCATE tables with foreign key references. Who knows?

SQL Detective

2010-08-25T09:55:00.001-07:00

I saw some full page ads from Conquest Corporation in the latest issue of Oracle Magazine. They were compelling advertisements. So I decided to try and download two of their new products.

Unfortunately, one of the download links did not work. The other one downloaded fine. However after installation, I could not figure out how to work the product. Was not sure what the heck the thing did either.

This exercise was almost a bust. However I was given the opportunity to downloaded Conquest's flagship product. It is SQL Detective. Once again I was unsure from the hype what this tool did exactly. After downloading I found it to be like PL/SQL Developer or Toad.

Here are my first impressions. Logon took a long time. The default layout is not intuitive. You type you SQL statement below the results window. You have to use a window scroller to get more query results (I prefer a button). There were some positive features though. I like having an explain plan tab. You can also click the users node in the tree and figure out who has accounts in your database.

The pricing page on the web was not intuitive either. I determined that the bare bones edition cost $136. For that price, I did not find any SQL Detective features which would make me abandon PL/SQL Developer, which I use for my customer's work. At least Conquest had a compelling enough marketing in their ad to get me to give it a try.

Competition for MySQL

2010-08-19T11:47:00.000-07:00

MySQL was previously owned by Sun Microsystems. Then Oracle bought Sun. So now it is managed by Oracle. This is an open source product. However there are other offerings out there. Let look at one or two of them.

The most direct competition from Microsoft would be their SQL Server Express. However that product is not as simple as it sounds. I want to call attention to another product. That is SQLLite.

SQLLite is actually a library. There is no configuration required. You link in a database engine. The contents of the database are stored in a single file.

SQLLite is implemented in C++. The API to the database is low level. This thing was made with C programmers in mind. Good luck if you want to access it through some .NET code.

You should realize that this is a scaled down database. It tries to implement most of the SQL-92 standard. But it is still low power. You don't even want to try to access the database from multiple threads. Things will not go well. I must confess that I am not a MySQL expert. So I cannot as of yet compare SQLLite with it. However I do have a personal project which is outgrowing Oracle Express Edition. MySQL may be my next bet.

Tuning

2010-08-18T11:12:00.000-07:00

I have written about Oracle database tuning in the past. How about we see how other players conquer this issue. Let us assume that we have Microsoft technologies on the front and back end. Microsoft provides tier interaction profiling (TIP) to sort out performance issue.

TIP adds code to profile your application. It records timing information. The actions timed are all those that result in a database hit. These include SQL, ODBC, and OLE DB. Those are all Microsoft database access technologies. Developers use frameworks that often abstract them from actual database code. TIP helps get the developer back in touch with the actual database interaction.

Oracle XE and JDBC Performance

2010-08-06T11:16:00.000-07:00

I am using an Oracle Express Edition database to prototype a very large data set application. My programming language is Java. I am using JDBC to get to the database.

For starters I thought I needed to download the
JDBC drivers from Oracle. This required me to sign into the Oracle Technology Network. However the login kept saying my password was wrong. It was a pain.

I got the drivers. Now there are several flavors of JDBC drivers. Oracle says you should stay away from the OCI version. Ok. There is also a JDBC driver that bridges to an ODBC driver. This is a slow configuration. I chose a thin JDBC driver. By default it is auto committing each insert statement I make.

So I did a quick timing test. I inserted 10,000 records into a small table. I stored the timestamp for each insert into the table. The whole thing took a total of 19 seconds. That is 500 records per second inserted. That feels pretty good since I a doing a commit after each insert, and this is the free edition of the Oracle database. The only other test I might try is to see whether a PL/SQL script can load the data faster.

Query Tools

2010-08-05T11:08:00.000-07:00

I am working on a new project that has a lot of data. So I know I will need some good tools. My database is Oracle XE, which comes with a limited command line tool. I decided to look around to see what other tools I could get.

My current tool of choice is PL/SQL Developer. I checked their web site. A single user license goes for $180. Plus there is a $50 charge to installation media and documentation. There is a 30-day trial period version available. But there is no free option. I passed on it.

Next I tried SQL Developer form Oracle. It is free of charge. The thing requires the Java Development Kit. I already have that. So the downloaded was smaller. It installed fine. However when I queried a bunch of records, only the first 50 were shown. I had to keep sliding down the control on the side of the results window to get more data. Also the default display for the date columns was MM/DD/YYYY. This product was also out.

Finally I tried Toad for Oracle Freeware. It is, as the name implies, totally free. The install states that it has the same functionality as Toad For Oracle Base Edition. I found it odd that I downloaded the installer. After installation, it said that there was a more recent version available. What? Luckily there was a button to get the whole result set in one swoop. Nice. I did not like that the query results did not append a record number to the left of each record. But I can live with that. Toad you are my tool of choice for now.

Choosing an Oracle Version

2010-08-04T11:01:00.000-07:00

I've got a hot new side project. There is a list of 170 million URLs I want to scrape from the web. I want to use Oracle to hold my data. What version of the database do I need? My first choice was Oracle Express Edition, also known as Oracle XE. It is free. However it has a limit of 4 Gigabytes of user data. My first database table with 170 million records will blow past that limit.

Next I took a quick look at the different Oracle Standard Edition offerings. They go from $180 to $350 per user, with a 5 user minimum. This cost is not astronomical. But my project is in a research mode right now. I don't want t0 shell out that kind of cash unless I have a money making opportunity. So I will do a prototype with Oracle XE.

Oracle XE comes with a Run SQL Command Line tool. It looks like a DOS box, and behaves like SQL*Plus. That is too bare bones for me. Let's see what kind of tools there are out there that can help me. I am used to PL/SQL Developer myself. However others swear by Toad. And Oracle has SQL Developer. Next time I will let you know my initial experience with these tools for my new project.

Oracle Sun Exadata Database Machine

2010-07-14T19:50:00.000-07:00

Larry Ellison recently bragged about the Exadata Database Machine v2. This appliance was previously aimed at database warehousing use in version 1. Now it sets its target as online transaction processing systems. Version 2 is supposed to be twice as fast as v1. Larry says the thing outperforms the best machines from IBM. He seems to be taking personal initiative on this project.

The Exadata Database Machine has a number of parts. Of course is hosts an Oracle 11g database. It also has an Exadata Storage Server from Sun. The storage server has 12 disks and 2 CPUs. However the real power comes from the huge amount of flash storage in the storage server. We are talking a couple hundred Gigabytes here.

The storage server component in this system is a smart one. It makes use of highly parallel operations. It can also do query processing itself. There is a speed gain from doing the processing close to where the data resides. The box also supports compression to drastically reduce data size.

Although the system comes preconfigured, you still need to do some fine tuning on the device. The cost can be a little more than $1M. However when you factor all the licensing and support costs, and get a bigger box, the cost can be a few million. This is obviously the high end OLTP market. Oracle is going after the market previously dominated by manufacturers such as IBM and EMC.

Performance Problems

2010-07-09T21:22:00.000-07:00

There have been times recently when our production system has been painfully slow. The DBA Team took the lead to find out what was wrong. They could not isolate the problem. So they brought up the matter with Oracle Corporation.

Oracle could find no defect with the database software. Their analysis resulted them in saying that there must be a defect in our source code. The only value add they provided was some queries which were taking a long time on average.

This was most disappointing. Any monkey with a tool can identify the worst performing queries in the system. Now this is not to say that a query might not be poor. However these SQL statements were doing updates using only a primary key in the WHERE clause.

Sooner or later this problem will come around to our team. I told our project manager that I expected we would need a dedicated Oracle performance engineer. We used to have such individuals assigned to our project. They have since been replaced by consultants who are competent, but not performance experts.

NULLs and OUT Params

2010-07-06T19:49:00.001-07:00

Recently I came across a big stored procedure in our code. I needed to make some changes to it. That's when I decided this procedure could not go on with its size and complexity. It was time to refactor it.

After I broke the procedure into many procedures and functions, I ran some regression tests. Initially I broke the functionality. I went back to good old debugging techniques. I wrote out a bunch of variables to a table. The values were always NULL.

Then it hit me. I had a bunch of OUT params that were not initialized. Therefore they were NULL by default. You can avoid this by always initializing OUT parameters. These were numeric variables. Incrementing NULL lefts the values NULL when I had the bug. Explicitly setting them to zero solved my problem.

Out of Sequence

2010-07-01T12:04:00.000-07:00

I want to follow up on my post from yesterday with more details. A junior developer had to write a PL/SQL script that would update upwards of 10 million rows in the database. To make things run fast, she put FOR UPDATE in the cursor SQL. Then the actual update used WHERE CURRENT OF. This sounds like a solid design.

The problem was that a batching commit mechanism was added in the middle of the loop through the cursor. The result was that after the first batch was committed, the next iteration through the loop caused an ORA-01002: fetch out of sequence. The thing to know is that the FOR UPDATE works up until the next commit. After that you are done with the FOR UPDATE cursor. But the script was coded so that it kept on trying to loop after the commit. Is there a solution for batch commits with a cursor? Yes. Don't use FOR UPDATE. Or break up your cursor into many cursors, each of which does a single COMMIT.

Hidden Errors

2010-06-29T19:31:00.000-07:00

We recently released a new version of our system to the customer. They needed us to fix a bug. The bug involved the application displaying too many records on some screens. The fix involved adding a new column to a table to remove the ambiguity of records. There was one final touch that was required. We needed to populate this column for all the existing records.

One of our junior developers coded up a PL/SQL script to go through all the records and determine the value for the new column. With some guidance from a senior developer, she even made the script fast by using FOR UPDATE and WHERE CURRENT OF. Good stuff. There was just one problem. The customer complained that the darn thing did not work. There was a flurry of emails trying to figure out what was wrong.

Finally I got dragged into a conference call to discuss this problem. At first a lot of people were taken aback that most of the records did not get the column populated. Finally I could not take it any more and I chimed in that we were required to not fill in all records, but only the ones where we could reasonably determined the value. The rest were to remain NULL. Another developer got ahold of the script, checked out how it handled exceptions, and searched the production database for evidence of any problems. Sure enough an exception was raised. The script caught the exception, but handled it by logging the error to a table and exiting. The shell script that called the PL/SQL script reported success and we were none the wiser.

Now we get into the blame game as to why testers and developers did not detect the problem. A DBA chimed in with some sage advice. If we are going to trap errors, then we should use DBMS_OUTPUT to pipe a message to the screen informing a DBA that the thing bombed. Then we could deal with the problem immediately. In fact, it might have been better to leave the script bomb and report the exception to the command line. Then the customer would not have been the one to discover the error.

NoSQL on the Rise

2010-05-27T08:15:00.000-07:00

There is a relatively new movement called NoSQL. It involves high performance databases for little to no cost. Social Networks have been using NoSQL. You might be able to use them too, depending on your specific database requirements.

The NoSQL movement has different products from different vendors. Amazon created SimpleDB. It is data storage for key value pairs. And Google created BigTable, open sourcing the data model for it.

Some NoSQL databases focus on storing JSON objects. Examples of these databases are CouchDB and MongoDB. CouchDB let's you use a REST API to access the JSON data.

These NoSQL databases often have limitations. Some impose a time limit for queries to complete. Others return only partial data sets. These databases can hold a lot of data. But they often are poor for complicated queries.

Script Performance

2010-05-20T10:04:00.001-07:00

I had this script to write to correct some data. The script was going to affect about 20k records. One part of the script needed to mine through a table with 1B records. This needed to be done for each of the affected 20k records. So I knew that a brute force approach would never complete.

So I thought perhaps I could preprocess those 1B records. There were only a few thousand of those records that were of interest. If I could just load the couple thousand relevant records into memory, I could use that data to go though my batch.

This seemed to be a good candidate for some type of collection. But what kind should I use? I don't do enough PL/SQL to be very comfortable with collections. I read around and though maybe it could be a VARRAY. But I only had experience with PL/SQL tables. What is a developer to do?

In the end I needed to get this script coded ASAP. So I did the simplest programmatic approach. I created a temporary table with a few thousand records. This table represented the important data mined from the 1B record raw table. I am hoping that SQL against this small temporary table will be very fast. We shall see when I ship the fix out to the production environment where the volumes are huge.

MongoDB

2010-05-12T21:50:00.001-07:00

Previously I had written an overview of the NoSQL movement. Today I want to talk a little more about a specification implementation. I will cover MongoDB.

NoSQL has some niche applications. Sometimes your information does not fit into a database schema. MongoDB tries to provide high performance. This is in contrast to other NoSQL implementations which are gerared toward high concurrency.

There are no transactions in MonogoDB. You can't recover from an abrupt shutdown. MongoDB is good if you need data quickly, and you access the data frequently. JSON is used as the default data notation. Internally MongoDB uses BSON (binary JSON). MongoDB itself is written in C++.

The use of MongoDB is very similar to using a traditional relational database. I have not played with it yet. Not sure that I will. You still need to know what the competition is using though.

Chunking for Performance

2010-05-10T10:44:00.000-07:00

So you have a lot of data you want to update. I am talking about the whole big table. How do you do it? Well in the good old days you found a way to divide and conquer. Pretty much you put some limiter in the WHERE clause of the update to do a small bunch at a time. That way the whole darn table did not get locked.

The idea is similar in Oracle 11g Rel 2. However the bunching is supported in the system. You make use of the DBMS_PARALLEL_EXECUTE package. You choose a way to chunk up your data. That is, you divide up the data by some characteristic. However the new package does the work of splitting up your DML between different statements which operate in parallel.

The procedures in the package do their own COMMIT on their parts. You also need to have the CREATE JOB privilege, as a bunch of jobs will be scheduled on your behalf to get the job some in parallel. A common way to chunk up your data is by ROWID. However you can choose some other attribute of your data. On our project we use a generated ID to do the chunking. And previously we had used partitions to break up the chunks. Maybe it is time to rethink that plan.

Index Rebuild

2010-04-27T19:29:00.000-07:00

Yesterday our loading software came to a halt in the production environment. The DBA team came to the rescue and determined some tables had indexes that were just out of date. A quick real time rebuild got things rolling again.

Now our DBA team is looking to be more proactive. We had a weekly job that rebuilds the indexes for some major tables. However we started looking for other tables to rebuild indexes for.

The DBAs told us that they were looking for tables that were frequently updated. I found one such example. A DBA chimed in and agreed the table I identified was a trouble table from a prior performance issue.

You would think that Oracle would have some built in support to keep indexes up to date.

Virtual Private Database

2010-04-15T19:59:00.001-07:00

Oracle has a behind the scenes functionality called the virtual private database. It is also known as fine grained access control (FGAC). The database will modify queries automatically to present a partial view of a database table. There is policy_function which returns the WHERE clause that masks the query results.

This functionality can be as simple as hiding certain rows from a result set using a more restrictive WHERE clause. It can also mask out some columns. This effectively creates a customized virtual object that is generated on the fly. This is very similar to a view. It is just that each user gets a custom view.

The FGAC can implement row level security. As such you can lock down data as you see fit. An application context is set at sign on. This is accomplished via a LOGON trigger. The rest is plug and chug by the Oracle database. Essentially you are getting logical security with the virtual private database. Good stuff. We might be using this on our project at work to implement some of the customer’s data hiding business needs.

Global Temporary Tables

2010-04-14T17:09:00.000-07:00

Global Temporary Tables are a feature from Oracle 8i. The tables are managed by the database server. They hold data which is private per session. The data is automatically deleted at the end of the session.

These tables can improve query speed. This is because there is no redo or rollback information collected. They also prevent private data sets from interfering with each other. You can consider these tables the ultimate work tables.

Tom Kyte advises against their use. However there is a difference between a temp table and a global temporary table. A temp table is one you create on the fly to do some complex work on. These global temporary tables are permanent tables which users can only see a portion of the data.

The Rise of NoSQL

2010-03-17T17:23:00.001-07:00

Recently I read about the Cassandra project. It is a distributed database. The goal is to store a large amount of data. It uses the Google BigTable model (more on BigTable later). The databases stores key value pairs.

Cassandra was created by FaceBook. It is used by FaceBook, Twitter, and Digg. It is an open source project managed by Apache. The intended use is large web applications. There are other projects similar to Cassandra. They are part of the NoSQL movement. Are these databases poised to replace the relational ones like Oracle, DB2, and SQL Server? Let’s see what these NoSQL databases have to offer.

NoSQL in general are data stores that do not impose a fixed structure for the data. Access to the NoSQL databases tries to avoid joins. The technical term for these databases is structured storage. They have weak consistency. That means if you update a copy on your server, the update is not guaranteed to be propagated everywhere immediately. These type of databases also have very simple interfaces.

Let’s go over some other NoSQL databases. Hadoop is a project with many subprojects. One such subproject is MapReduce, which is a framework for large data set distributed processing. Then there is Google’s BigTable. It is a distributed storage system that can scale to a large size.

Next we have MemCacheDB. It is a distributed key value storage system. Despite the name, it is not for caching. It is a persistent storage mechanism that uses the memcache protocol to access a Berkeley database on the back end. Another NoSQL database is project Voldemort. It is a distributed database providing key value storage. LinkedIn uses it.

CouchDB is an Apache project. It is a document oriented database. You query it using MapReduce. It has a RESTful JSON API. Note that CouchDB is written in the Erlang functional programming language. A similar offering is MongoDB. The name is a play on humongous. It is also a document oriented database. This one was written in C++. It collects JSON documents and stored them in a binary BSON format.

I have covered a lot of NoSQL implementation. They mostly provided distributed storage. Although they scale very well for large data sets, their functionality is limited. They are not a replacement for relational databases. You still need RDBs for transaction processing. Nonetheless it is good to know a bit about the NoSQL movement, and the problems it tries to solve.

Edition Based Redefinition

2010-03-09T12:20:00.001-08:00

Previously you could not compile any PL/SQL that was currently being executed. If you did, the result would be an ORA-04068 “Existing state of packages has been discarded”. This restriction is lifted in Oracle 11g rel 2. You can now perform an online upgrade of an application. In other words you can patch code while users are executing the old version. You can also do some schema changes while users access the old version. The goal of this new capability is to ensure that downtime to users is minimized.

Objects in general fall into two categories. They are either editionable or noneditionable. Editionable objects includes functions, procedures, package, and so on. They can be easily upgraded online. Noneditionable objects are things like database tables. Normally you cannot upgrade them online. However with a new object called an editioning view, you can even simulate changes to database tables while users are on the system running your apps.

An editioning view has specific limitations. You can only select from this view. Furthermore you can only reference one database table (and thus no joins are permitted). This view acts like a synonym in that it projects attributes from the underlying table. This view can have triggers. The trick is that this view can effectively hide columns from the user. This hiding operation is similar to dropping a column a table. The new kind of column drop is a virtual one. The column in the underlying table remains, but access to it has been shut off.

Here is the operational practice to use such editioning views. You first rename the underlying table. Then you create an editioning view that has the same name as the old table. You drop the triggers on the original table. Recreate the triggers on the new view. Revoke any privs on the base table. This is what we call a hot rollover. There are other similar features in Oracle 11g rel 2. Perhaps I will cover them in a future post.

SQL Developer

2010-03-08T14:35:00.001-08:00

There is a new release of Oracle SQL Developer available. It is version 2.1. This has two new big features. They are PL/SQL unit testing and a Data Modeler/Viewer.

The PL/SQL unit testing part is a unit test framework. It allows you to share unit tests. It also let’s you run regression tests.

The Data Modeler/Viewer gives you read only access to a database schema. You can view the schema objects graphically. A benefit is the ability to browse the foreign keys between tables.

To tell the truth, there is even a version 2.1.1 patch that is available now. The big v2.1 release is 94M large for the install. It requires the Java Development Kit v1.6 update 11..

This app is still free of charge. You can still edit PL/SQL just like the older versions of the tool. And it still integrates with all kinds of source code control.

ORA-12154 Blues

2010-02-21T17:57:00.000-08:00

I assigned a developer a trouble ticket from the production environment. He said he was getting errors when trying to connect and reset his password. The specific error was ORA-12154, which translates to "TNS: Could not resolve service name."

This looked like a simple problem to correct. I ensured he had the TNS_ADMIN environment variable set. I also ensured he had the right "tnsnames.ora" file on the network. That seemed strange.

After I gave up, I called in our lead DBA. He wanted to see how many Oracle homes there were on the guy's machine. There were multiple ones due to the multiple products installed. The developer could not find an Oracle Home selector. But this did not turn out to be the problem either.

The lead DBA and myself went through different ideas to get this guy running. Then I had a strange thought. What if this guy had a weird password that was screwing things up. I thought that could not be possible since we have a trigger that prevents bad passwords. However the DBA told me his profile did not enforces the password trigger. Sure enough the developer had an "at sign" character in his password. So the part of his password after the password gets treated as the host string. His logon would never work.

In the end we had a DBA reset his password. This just goes to show that an Oracle error message does not always identify the exact problem being encountered. You might need to team up with some competent individuals to figure out the problem. It might also involve thinking outside the box and trying things out.

Sun Microsystems and the MySQL Community

2010-02-09T14:24:00.000-08:00

Oracle continues to work on acquiring Sun Microsystems. The MySQL community has some opposition to this move. The complaints are led by the creator of MySQL. He sold out his share to Sun. But he thinks Oracle will work to dismantle the MySQL database in order to increase their profit share.

The European Commission is still debating whether to block the acquisition. Many current Oracle customers have written to the EU to encourage the approval of the merger. MySQL can fork a copy of the code because the project is open source.

Oracle promises to spend money on MySQL development. It also is committed to not forcing customers to pay for a support contract for MySQL. The MySQL creator says he saw that Oracle bought out InnoDB before, and the project development became stagnant.

SQL Communications Area

2010-01-07T14:46:00.001-08:00

There is a structure in Pro*C called the SQL Communications Area. The structure variable is named sqlca. The most useful field in the structure is sqlcode, which is the status variable. You access this variable with the fully qualified name like this "sqlca.sqlcode". The structure is defined in file sqlca.h.

The status variable (sqlcode) has three different types of values. Zero means the operation was a success. A positive value means the SQL statement executed but threw an exception. And a negative value means the statement did not execute. This variable is updated by Oracle after each SQL statement.

Normally you will get a sqlcode of 1403 if there is a No Data Found exception. However you can set the MODE to ANSI in your Pro*C precompiler. This will result in sqlcode being set to 100 (instead of 1403) on No Data Found.

The developer is able to independently define other status variables such as SQLSTATE and SQLCODE. This SQLCODE is different than the sqlca.sqlcode. In addition to the SQL Communication Area, there is an Oracle Communications Area. It has the variable name oraca.

You can trap Pro*C errors using the WHENEVER clause. There are different values used in association with the clause. Some of these are SQLERROR, SQL WARNING, and NOT FOUND. SQLERROR equates to the sqlcode being negative. SQL WARNING is when sqlcode is positive or sql.sqlwarn[0] equals ‘W’. And NOT FOUND is a sqlcode of either 1403 or 100, depending on the precompiler MODE.

It is possible to have more than one sqlca. However there is only one active one at a time. My recent research into the SQL Communications Area was when the sqlcode was non zero for a trivial SQL statement. It turned out that the value was 1403 which means No Data Found. It took a while for me to discover that one of the bind variables had an extra space at the end causing an update to fail.

SQL Review and Native Compilation

2009-12-16T13:57:00.001-08:00

I just started reading a new web site on PL/SQL programming. It is actually hosted by Oracle. Today I read a review of the different ways to execute SQL in PL/.SQL. They are embedded SQL, Native Dynamic SQL, and the DBMS_SQL API.

Embedded SQL is easy. You just code up the SQL like normal. There are some caveats like specifying the variable into which you select some data. But it is very natural. Here is a hint. Make sure you define aliases for the table you select from. Then use these aliases to qualify the columns you select.

Native Dynamic SQL is when you run the commands with an EXECUTE IMMEDAITE. You should exercise care to protect against SQL injection. I have written about this before. This style is fast than DBMS_SQL. Note that DBMS_SQL is the old way of executing dynamic SQL. It is not entirely obsolete. If you do not know the return values until run time, this is the only option. But most times you can skip it.

Now let’s talk briefly about native compilation. PL/SQL is interpreted. Normally it gets compiled down to M-code. The M-code is executed by the PL/SQL Virtual Machine (PVM). However for speed purposes you can go native. In the Oracle 9i and 10g days, native compilation would transform your PL/SQL to code in the C programming language. This C code would be compiled by a C compiler on the operating system. The result was a library that could run the code very fast on the machine.

Things change up a little bit for native compilation in Oracle 11g. But let’s leave that for a future blog post.

Pro*C Mystery Revealed

2009-12-11T20:27:00.001-08:00

Our customer said there were too many problems with our system. And development was not resolving problems fast enough. The customer shared their concerns with management. Management put the heat on team leads. Now my team lead wants more and more bugs fixed faster. I had to push back and say problems will be resolved when they get resolved. However I did attempt to be diligent in problem resolution. There was one last problem to resolve. The fix sounded simple when I determined the cause. But I was encountering some strange Pro*C behavior. Here is the code I was trying to use:

void getType(char *pszType)
{
EXEC SQL BEGIN DECLARE SECTION;
char *pszLocalType = pszType;
END SQL BEGIN DECLARE SECTION;

EXEC SQL
SELECT type
INTO :pszLocalType
FROM myTable;
}

No matter how many times I ran the code, I always ended up with an empty string. However I got an actual value when I ran the SQL manually from SQL*Plus. What gives? Can you spot the problem? It took a while for me to realize what I was doing wrong. The variable into which I was trying to retrieve the data was defined as a pointer to the memory passed into the function. I really needed to have some space defined within the DECLARE section to store the SQL results. Then I could copy the value to the calling function. Here is a slightly updated result that worked:

void getType(char *pszType)
{
EXEC SQL BEGIN DECLARE SECTION;
char szLocalType[10+1];
END SQL BEGIN DECLARE SECTION;

EXEC SQL
SELECT type
INTO :szLocalType
FROM myTable;

Strcpy(pszType,szLocalType);
}

SQL Injection

2009-12-04T14:32:00.000-08:00

SQL injection is an attack whereby the user gets to execute some command via a trick. You can prevent such behavior by using bind variables for arguments to SQL. You should also valid all inputs.

There are some other techniques which limit the ability for a SQL injection attack. Limit the privs you give to any given user. Also use invoker rights via AUTHID CURRENT_USER to ensure that commands that do get through run at the right priority.

Limit the number of routines that are exposed in a package specification. This will close the gap that a SQL injection hack can penetrate. Stay away from dynamic SQL. Get rid of debug messages and tracing. You don't want a hacker to know anything about the system you have developed.

You can employ fuzzing to test for SQL injection vulnerabilities. This will not prove you are secure, but can uncover some weaknesses. Test each parameter to your functions separately.

Curse of the Trigger

2009-12-03T13:47:00.000-08:00

A tester found a problem in our application. I traced it back to some test configuration data in the database. That seemed like an easy fix. I wrote a one-time PL/SQL script to delete the test data. I had our DBA team implement the fix in the tester's database.

Luckily our DBA did a little quality assurance himself. He inspected the contents of my script. Then he checked whether the test records got deleted. They did not. It seems a trigger fired and prevent the delete. Doh. I should have checked that.

I like writing PL/SQL code. So I was not worried that the results of promoting my fix were a failure. That just meant I could write more code. I researched the logic of the trigger. Then I added some more code to detect child records of the test data. I wrote code to move those record to depend on another foreign key. Bingo. I even stayed late at work knocking out the updated fix.

Tracing and Profiling

2009-12-02T19:37:00.001-08:00

You can employ an Oracle trace to view the execution path of your PL/SQL. This can be enabled either by a session parameter or a compile switch within your code. The data is accessible through some Oracle views. You must run the "tracetab.sql" file in order to set up these views.

A related topic is that of profiling. This is where you find out which portions of your code get executed the most. You can use this to choose which code you need to optimize. Profiling gets turned on and off with the DBMS_HPROF built in package.

Just like with trace, you need to run the "dbmshprof.sql" script to set up the tables for profiling. There is even an option to view the profiling output in HTML form. Next time I am going to talk about the hot topic of SQL injection attacks. Until then, be good.

Use the Source Luke

2009-12-01T14:45:00.000-08:00

PL/SQL starts out as ASCII text files. Then you compile that source code into the database. Once it is there you can inspect what has been compiled. The old school way to do this is to inspect the USER_SOURCE view. Specifically you can query the TEXT column. It has your source code.

There are more advanced ways to check up on the code in the database. The SQL Developer tool has a function to search PL/SQL source code. You can also check out the ALL_ARGUMENTS view. It has parameter information for packages.

One setting that helps control your ability to analyze source code is PLSCOPE_SETTING. This can be set at either the session of system level. This allows you to analyze identifiers. You access this data using views such as ALL_IDENTIFIERS. Finally there is the DBMS_METADATA built in package. You can use it to get object definitions. The output is in XML format. Enjoy.

Result Cache

2009-11-30T13:10:00.001-08:00

The result cache is a place in memory where Oracle stores results. Subsequent queries can then obtain these results quickly. This cache encompasses both SQL queries and PL/SQL functions. Note that the cache can become invalid when the source data of the queries changes.

The SQL query result cache is stored in the SGA. The results can be from just fragments of a query. You use the hint /*+ result_cache */ to advise Oracle that you would like to make use of the cache. Check out the DBMS_RESULT_CACHE built-in package to clear or inspect the contents of the cache.

The PL/SQL function result cache stores data across sessions. It also is stored in the SGA. When defining a function, use the RELIES_ON clause to identify tables whose changes may invalidate the results of the cache.

Next time I will start by explaining how you can extract your PL/SQL source code from the database.

Keeping It Simple

2009-11-23T22:47:00.000-08:00

My boss told me to help another team out on our project. They are behind schedule. And just recently, a review of their code showed they were missing some important stuff. Their team was clueless about this. Since I knew what was going on, I decided to help.

First I helped out requirement team figure out what changes needed to be made. Then I went into design mode. The existing code in a PL/SQL package that implements a ton of business logic. We needed to store parameters for the logic in the database instead of hard coding a lot of the values.

How does one get this done? This package gets called a lot. In other words, it needs to be fast. I can't have the code look up the values each time it needs them. I could be fancy and have the package load the values on initialization. But I want to make this simple as possible.

Therefore I created a new record type that stores all the lookup values. Then I created a new procedure which loads the lookup values from the database. Then I pass this record around to the different functions that need the values. This should be simple enough for any developer to follow. I just want to code the stuff. I don't want to inherit another team's code. Good enough for now. The real fun comes when I need to thoroughly test this stuff.

Bulk Binding and other Tuning Tricks

2009-11-20T13:34:00.000-08:00

As promised, here is a discussion on bulk binding and other misc performance tuning tips. The goal of bulk binding is to reduce the amount of times Oracle has to do a context switch between the PL/SQL and SQL engines.

There are two directions that bulk binding operates in. The first is when you want to put the data from a collection into the database. To do this you employ the FORALL statement. It will make the whole collection of elements be transferred to the database with one SQL context switch.

The converse bulk binding technique is when you have data in the database you want to extract into a collection. This is where you use BULK COLLECT INTO a collection. Again the PL/SQL engine only has to make one switch to the SQL engine.

Here are some other ways to make PL/SQL run fast. Take advantage of the short circuit evaluation of expressions. If you know one part of an IF statement is sufficient for the whole decision, and this part occurs often, put that as the first check in your IF statement. Oracle will skip the rest of the checks for you. Another feature you can enable is inline subroutines. Instead of having your code jump to a separate subroutine, Oracle can place the full code of the subroutine right in your function/procedure. This can be controlled globally with the PLSQL_OPTIMIZE_LEVEL parameter, or done a line at a time with PRAGMA INLINE.

Tuning Tips

2009-11-19T22:25:00.000-08:00

Here are some good tips to make your PL/SQL fast. Do not rely on implicit data type conversions. Also choose the smallest data type that will fit your data. The PLS_INTEGER data type is good. The SIMPLE_INTEGER type is better. Smaller types are better.

You should code less code per module for a small gain. The big boost will come from using a SQL statement to do the work instead of a whole procedure worth of PL/SQL. I struggle with this. Our data sets are huge. I am sometimes afraid to do something huge with one SQL statement. The thing may never complete. Or it may bomb in the middle and get rolled back. At least my PL/SQL routines can commit often and avoid redoing work on errors.

I thought I could fit all of this into one post. But I have more to say. Look for me to cover bulk binding next time, along with some other misc tips.

Interpreted and Native Compilation

2009-11-19T22:19:00.000-08:00

There are two types of PL/SQL compilation. The first is interpreted compilation, which is the default. Interpreted compilation is done at run time. It is good for development.

The other type of compilation is native compilation. It has better performance. Therefore it is better suited for a production setting. Compiled code is stored in the SYSTEM schema. You can enable this by setting PLSQL_CODE_TYPE to NATIVE on a session or system level.

Next time I will review some PL/SQL tuning techniques.

LOBs

2009-11-17T20:22:00.000-08:00

The plain old LOB is a new data type for the Oracle 11g database. It supports compression of the data. And it sports better performance. You get a LOB column when you define a column with the SECUREFILE keyword.

Even with compression, LOBs may take up a lot of space. That is why Oracle has a feature called deduplication. This will detect when there is more than one copy of a LOB in the database. It will then cause these to reference just one copy of the LOB. You enable this feature by modifying the column using the DEDUPLICATE LOB keywords.

Oracle now provides encryption which is done at the block level. You get to choose your encryption algorithm such as 3DES or AES. Note that you can specify the key length of the encryption (128, 192, or 256 bits). You enable encryption on a column by modifying it and using the ENCRYPT USING keywords.

There is a promotion strategy to get old large object to use the new Oracle 11g type. This option allows the table with the old types to be available while the promotion is taking place. The promotion is accomplished with calls to the DBMS_REDEFINITION built in package.

ORA-01403 Even With NVL

2009-11-11T12:32:00.000-08:00

It was the night before the big release to our customer. Our internal test team was still finding some bugs in our software. Ouch that’s painful. One such error being reported was an ORA-01403 when they brought up a new screen. This was perplexing. I thought every single column in the SELECT statement was wrapped with the NVL function. And I knew there was a record in the database that we were selecting from. What the heck was going on?

Well the best way to debug weird bugs like this is to try things out. I knew there were some NULL column values in the record the testers were selecting. So I updated some test data and filled in some values. The problem went away. Was I losing my mind? Or perhaps the NVL was broken. I did not really write this code from scratch. I copy and pasted it from another application. It should have worked.

That’s when it hit me. The guilty column was defined as evilCol CHAR(1). However the column being selected was written as NVL(evilCol, ‘NA’). Do you see the problem? The default for NULL values of the column was one which was too large to fit in the column given its definition. Oracle won’t replace a NULL with something that is too large to fit in the source column. Thus I got a SQL warning that we had a NULL issue.

Here is the real shame about this bug. I had previously fixed a bad NVL default for another column being selected in this SQL statement. However I did not go through every column to check whether there were other bad defaults. Shame on me. Luckily I went through all the column and their NVL defaults this time. The hard lesson was learned. Be careful of the size (and type) of defaults you return using the NVL function. They might not match the column type you are selecting from.

The Microsoft Connection

2009-11-10T16:58:00.000-08:00

I used to be an exclusive Microsoft developer. That meant I normally interacted with Microsoft SQL Server. Yes I know. It is sacrilege. Just recently I subscribed to the MSDN magazine to read up on the latest Microsoft technologies. There were a lot of articles that referenced programming APIs to access databases. I thought I would share some of them here to mix things up.

The first topic is ADO.NET. We use it in some of the newer applications in our system. This is included with the .NET framework from Microsoft. It is used to access a relational database like Oracle. The technology started as an evolution of Active X Data Objects (ADO) from Microsoft. A data provider provides access to a data source. In the case of Oracle, the data provider is the Oracle Data Provider for .NET.

A related topic is EF, which stands for Entity Framework. That is short for the ADO.NET Entity Framework. This is a set of data access APIs. It is similar to the Java Persistence API. An entity has a primary key. An entity data model maps data to a database schema. The programmer then uses either Entity SQL or LINQ to Entities to access the entity data model. This abstraction allows the programmer to access the data independent of where the data is actually stored.

Next we have DTO, which stands for Data Transfer Objects. These were previously called VO, or Value Objects. They implement a simple design pattern. They are used with DAO, which is short for Data Access Objects, another Microsoft technology. The main idea behind DTO is that they just provide accessors and mutators. There is no functionality provided other than get and set.

Finally we have POCO, which stands for Plain Old CLR Objects. The CLR is the Common Language Run time from Microsoft. This term is a play on POJO, or Plain Old Java Objects. POCO pokes fun at the complexity of accessing data using Java. POCO are simple access objects compared to hard to use ORM objects.

LOBs

2009-11-02T21:37:00.000-08:00

I want to start by talking about the LONG data type in Oracle. You can have at most one column of type LONG per table. This data is stored inline (which means it is in the table, not referenced by the table). This column can store up to 2G of data.

A LOB is a large unstructured data type. An example of a LOB is an image. Use the DBMS_LOB built-in package to deal with this data type.

A related data type is the BFILE. This represents an external LOB. You initialize columns of type BFILE using the BFILENAME function to point to the actual file that is backed by the BFILE.

Finally let me mention temporary LOBs. They last as long as your session. These types of LOBs are not associated with tables. They are good for transforming data. Next time I will continue to discuss LOBs, with emphasis on performance and encryption.

Fine Grained Access

2009-10-31T09:17:00.000-07:00

Oracle allows you to set general access to functions in the database. You can also take the access privs down to the table level. This is fine grained access (FGA). When it is in place, SQL statements get dynamically modified to restrict the results.

Setting up conditional access requires the use of an application context and policy. The application context is a pair of attribute and value pairs. These can be set with the SET_CONTEXT procedure of the DBMS_SESSION package. You then define a policy on a table using the DBMS_RLS package. The policy grants or revokes access to table data based on one or more attributes of the application context.

On Packages

2009-10-29T15:19:00.000-07:00

How do you test a function from the SQL*Plus command line? Well first you must define a variable. Then you can run the function, assigning its value to the variable as such:

EXECUTE :my_var := my_proc(param);

Note the semicolon in front of the variable name. When writing the function, you must remember that you can only use SQL types. They cannot be PL/SQL specific types. You must also use positional notation for parameters. Finally you must be logged in as the owner, or be granted EXECUTE privilege on the function.

Here is a rule that is not specific to calling functions from SQL*Plus. The functions called from the SELECT statement are not allowed to do any DML. That includes anything called by the function, including triggers. This is just like another rule enforced by Oracle. You cannot query the table that is changing in an UPDATE/DELETE statement.

Now let's talk a little more about variables. A package body can have its own variables. These are private to the package. Variables exported in the specification are automatically initialized to NULL by Oracle.

I will end with dependencies. Suppose procedure A depends on procedure B. Also assume that procedure B depends on procedure C. Then we say that procedure A indirectly depends on procedure C. You can use "utldtree.sql" to set up the environment to trace dependencies.

There is still a lot more to learn before I become an Oracle Certified Professional. Come along as I bring you on my journey.

All About Cursors

2009-10-24T10:34:00.000-07:00

I am trying to study up to be an Oracle Certified Professional. It is difficult with all the craziness at work these days. To tell the truth, I am also finding that just reading training materials is not enough. I am not learning that much. It might be time to attend some more in person training.

On to the subject for today. That is cursors. You can use a FOR LOOP to make cursor use really easy. However if you do not, and make use of explicit cursors, just make sure you close your cursors when you are done with them.

There are two type of REF CURSORs. These are the strong and weak cursors. Strong cursors return a specific type. Weak cursors can be used with any query. Both REF CURSOR types can be passed to functions and procedures. You can think of the cursor as a pointer.

Let me close by briefly mentioning subtypes. A subtype is a subset of an existing type with some constraints applied. It can be used any place the more general type is it based on can be used. Here is an example of a subtype:

SUBTYPE small_num_type IS NUMBER(4) NOT NULL;

Types of Collections

2009-10-20T19:16:00.001-07:00

I continue to read up on advanced PL/SQL features. This is to get to the next level of Oracle certification. A recent chapter I read was about collections. These includes associative arrays, nested tables, and varrays.

An associative array is faster than a database table. You create one define as a TABLE OF INDEXED BY PLS_INTEGER. You can loop through it with the FIRST and NEXT functions. Access individual records and column like this: my_var(i).my_field.

A nested table is a table within a table. This does seem a bit weird at first. You make a column in the outer table of type TABLE OF . The outer table must be created with the NESTED TABLE keyword. You can insert many records in the sub table. This is done at the time of inserting recording in the outer table.

Finally a VARRAY is created in a two stage process. First you define a type such as CREATE TYPE m_type AS VARRAY(10) OF . Then you can create a table with a column of your newly defined VARRAY type.

There are a couple ways to set the data in these collections. You can use a constructor. Maybe I will give you an example of that later. You can also get the data from the database. Finally you can assign one collection to another one.

Get ready as next time I am going to cover cursors. This includes REFCURSORS.

Java and C Integration with Oracle

2009-10-12T22:56:00.001-07:00

I am brushing up to eventually take the exams to become an Oracle Certified Professional. One thing I need to learn is the techniques to mix C and Java code with my PL/SQL.

There are two ways a cross language call can go. One is the callout, where PL/SQL calls with a C or Java function. The converse is the callback, where C/Java calls PL/SQL.

An extproc is one process per session that manages such calls. The extproc needs to be configured with the listener for it all to work correctly. I am just speaking at a high level now and will not delve into the details.

Let me briefly focus on Java here as it seems to be more popular. Java is stored and runs in the Oracle database. You use the loadjava command at the operating system level. You can specify either the Java class or source file with loadjava.

Next time I will briefly review good stuff like associative arrays, nested tables, and varrays.

Materialized View

2009-10-09T20:42:00.001-07:00

The final topic I read about in my Oracle 11g book was materialized views. These were originally referred to as snapshots. They improve performance. They are like views in that they are built using a query of one or more tables. However they retain the query results from a specific moment in time. Unlike normal views, they do not get refreshed automatically every time you access the view.

We use materialized views on my project. There is a lot of data to roll up that our applications cannot do in real time. However the customers do not need an up to the minute accurate rollup. They want a reasonable approximation. Therefore we have two separate materialized views. The application will use one or the other, depending on which one is fresher. We alternate refreshing these two materialized views twice a day. It really works.

I am contemplating going for my Oracle Certified Professional certification. That will require a lot of learning. Specifically I want to be certified in Advanced PL/SQL. So I will be sharing what I learn with you.

ASM and Partitioning

2009-10-08T16:29:00.000-07:00

I have to confess that I skipped a lot of chapter in the last Oracle 11g book that I read. This is because I am not a DBA. Most topics I defer to our actual DBA team. But it is good to know a thing or two about Oracle database internals.

One example is ASM. It stands for Automatic Storage Management. This is a file and disk manager for Oracle files. Note that you don't need Real Application Clusters (RAC) to take advantage of ASM.

Partitioning is not a new topic. We have been using table partitioning in my project for many years now. This is the process of breaking a table into smaller chunks to improve performance. There are difference types of partitioning such as range, list, hash, composite, reference, and more.

Range partitioning is where you choose a column, and the values in the column determine which partition the records are placed. You define a range of values that the column takes on for each partition.

Important Users

2009-10-07T17:00:00.000-07:00

I am not a DBA. So I have never logged into a database as SYS or SYSTEM. And I probably never will. That does not mean I should not know anything about them.

In Oracle 11g, both the SYS and SYSTEM accounts have the DBA role. In addition, SYS has the SYSDBA role. Did that help you? Me neither. Time to get certified as an Oracle DBA.

Here is something I can relate to. Oracle 11g has a technology called streams. This is used to replicate data. You have the database data in a server and it needs to get into another remote location. Stream it over there. Obviously there is more to it than that. But this is the idea.

Another area of weakness in my Oracle knowledge is shared server processes. Oracle recommends that you have one shared server process for every 10 connections into the database. We have a lot of users (connections). Therefore we will most likely want a lot of shared server processes. Luckily we have a lot of hardware to back the database.

Next time I will provide an overview of ASM.

Passing My Test

2009-10-06T21:00:00.000-07:00

We interview a lot of candidates for developer positions at my company. I only look for three technical competencies: C++ programming, Windows framework programming, and SQL skills. Let me focus a little on the third trait.

Now I don't need an Oracle expert for my team. We already have that. But I do need somebody who knows how to construct basic SQL statements. I need to see evidence of hands-on experience in this area. So I asked about the clauses in a SELECT statement.

I want to hear that a candidate knows that GROUP BY treats the rows selected as one record or group. I also get impressed if the candidate can explain that HAVING restricts the results of GROUP BY.

With that I have given away one third of the keys to our kingdom. Study up your C++ and Microsoft Foundation Classes programming, and you have yourself a job.

Oracle Certified Professional

2009-10-05T21:52:00.000-07:00

Today I started reading up to study to be an OCP. My plan is to be an Oracle Certified Profession in PL/SQL programming. I read the first and second chapters in the Oracle training for this exam. I will be sharing what I learned later.

For now I continue discussing some things I learned reading a book on Oracle 11g. Triggers are associated with a single table. The trigger gets dropped automatically if the table is dropped.

An Oracle user have some other names. It is called an account. It is also called a schema. This means the user has a password for authentication. The user potentially owns some database tables, as well as other database objects.

Next time I will discuss some standard power user accounts in Oracle, as well as the clauses in a SELECT statement.

11g Background Processes

2009-10-04T06:48:00.000-07:00

The Oracle 11g database has familiar processes running in the background. Most of these processes were there in prior version of the database. However it seems like there are new names for these well known processes. Let's go over them.

There is the database writer called dbw0. Previously this was known as dbwr. There is also the log writer which writes to the redo logs. Pmon is the process monitor. Finally the archiver copies online redo logs to secondary storage (disk).

Obviously there are a lot of other background processes that make up the database instance. I am not as interested in them as I am a PL/SQL programmer, not a DBA. Next time I will go over simple ideas such as triggers and users.

Oracle Database 11g

2009-10-03T17:35:00.000-07:00

I recently picked up a book from the library titled Oracle Database 11g A Beginner's Guide. I was excited because I thought I could learn the new PL/SQL features in 11g. Well this book did not specialize in PL/SQL. It also did not specialize in just 11g technologies. The book covered everything in Oracle 11g. That is just too broad. But I did learn a thing or two.

Oracle first ran on a Digital VAX system. That is strange. I assumed it first ran on some flavor of UNIX. An instance is a set of processes in memory. I frequently hear about the Redo Logs from DBAs. These are essentially transaction logs.

Another common term DBAs use is the tablespace. That is a fancy way of describes a disk file the databases uses. Another file of interest is the Spfile. It is the server parameter file. This file has startup parameters in it.

I will be blogging about this 11g book for a little bit. Next time I will cover the new names for the background processes in Oracle 11g.

Five Become One

2009-09-19T12:00:00.001-07:00

I got a call from my team lead yesterday. He said he was swamped with the task that is due in a few days. He needed help writing a stored procedure to create a temporary table. The query was apparently all ready. It just needed to do it in PL/SQL. Although I was busy, I jumped at the opportunity.

Later I got the specs for the task. It was more complicated than I thought. there were a total of 5 queries. This was tricky because each of the 5 queries produced different columns of results. However they all needed to be combined into a big table.

I decided there was no way to CREATE a table AS SELECT FROM because there were multiple queries. So I determined the list of columns, and manually created the table with code. The create statement was so huge that I had to break the table creation down into one CREATE TABLE and multiple ALTER TABLE dynamic statements.

It was quite a chore to handle all the cases where a row in the resultant big table was sparse. Then I ran into more trouble when my dynamic statements to update pieces of the resultant table choked on some NULL inputs. That required some custom logic. By the time I was done, my script was around 1000 lines long. I did not leave work until midnight. What a chore.

Back To Basics

2009-09-16T13:38:00.001-07:00

We have a new developer on our team. He is smart. He has a PhD in computer science. But there were some concepts he was not getting. So I had to break it down for him.

There is a text file that contains the source code for an Oracle PL/SQL package. You need to log in as the schema owner. Then you can compile the source code into the database. Once that is done, there is no relation between the source code file and the stored procedure in the database. The code gets stored within the database at compile time.

My peer thought that he could somehow step through the source code file on the hard drive. That's not how you do thing. I recommended he just add some logging to his new functions. He later came back saying he encountered problems. It did not take long to diagnose the problem.

I mentioned earlier that the source code upon compilation generates a package in the schema of the user logged in during compilation. We have users which access tables and packages in the main schema by use of synonyms. However you need to log in as the schema owner to modify that package. The code is not written to explicitly reference the schema owner. I have been doing these things for a long time and take them all for granted. Sometimes you need to step back and understand another person's perspective to help them see the light.

Oracle 11g Release 2

2009-09-08T21:24:00.000-07:00

I have read some blurbs about the Oracle 11g Release 2 of the database. So I thought I would mention some things I have learned that are going to be in this release.

You will be able to run 11g on a cluster of non-Unix machines. For example you could string together a couple Intel machines and spend less money. Conversely you can run 11g RAC on a single machine, and still take advantage of RAC features.

11g R2 has new and improved data compression. Anything that saves disk space is a good thing in my book as long as it works correctly. Finally you will be able to do a hot application upgrade in 11g without shutting down the system. That's hot.

Database Design

2009-08-17T18:28:00.000-07:00

We are getting down to the physical design of the new changes in our system. Here is where we need to figure out which database tables get the new columns. We may also create a new table or two.

I am working with another team on a back end piece of the system. We will tie the front end to the back with some new database columns. They back end developers just thought we would add a column to an existing table.

This approach normally makes sense. You add the column to the table that represents the object that is getting a new property. However you need to look deeper to see how the new data will be used. You might want to separate some data by creating a new table. It might make you queries easier and the system faster. We are going to have the design debate later this week. This is a just a specific instance of the greater problem of good database design.

Speedy GROUP BY

2009-07-30T19:25:00.000-07:00

We had a big problem with our production system today. The customer determined we had not been transferring some files for a couple week. And those are some of our most crucial files. We had a big TO DO list to work this problem. I signed up to analyze the damage that was done to the business.

At first I ran some queries on a pretty large set of data. Our server is a massive one with tons of CPUs and memory. However these queries took a long time. I published the results. The managers wanted more details. They were looking for a way to spin the data to do the least amount of damage to our image. I told them this would take a long time. So I went home and brought my computer with me.

Here was the surprising thing. When I ran the SQL and divided up the result with a GROUP BY function, the queries seemed to run faster. Perhaps the database was able to split the pieces of the query amongst the CPUs better. Or maybe my prior query results were cached, and the database could take advantage of the result set. Whatever the reason, I was happy that my homework was not taking too long. I was able to knock out the tasks and move on to the next emergency. I even got time to eat dinner and watch a movie this evening. Thanks Oracle10g.

Back End Development

2009-07-28T17:48:00.000-07:00

My project at work seems to be loaded with too much to do with little resources. Yes I know that happens to everybody. But there is no way were are going to even be close to completing everything on time. My team leader has been tasked with trying to come up with a plan to get everything done. Today he had a brilliant idea. We could carve out the pieces of the newest changes that are for the back end. Then we could assign those to a junior PL/SQL developer that we have.

I gave this idea some thought. At first I thought there was maybe 10% of the work that needed to be done in PL/SQL packages and database triggers. However on second thought, the number might be closer to 15-20%. Out PL/SQL developer has some work to do. However she could help out this latest development task.

Here is the difficulty in this arrangement. The actual PL/SQL coding is not difficult. The hard part is understanding the business. This is why we cannot give this part to some new developers on the team. The business of our customer takes a couple years to understand. I guess we could give the junior developer some hints, and let her code away. We shall see how this would pan out. I was hoping that I would get to do the back end work. I like writing PL/SQL code. And I know the business inside out.

Definer and Invoker Rights

2009-07-21T14:23:00.001-07:00

In our system, we have the stored procedures owned by a central schema. Users have their own database accounts (schemas) where we stored temporary tables. The stored procs frequently create these tables. One developer reported that a rewrite of some stored procedures broke this pattern. The stored procedure, owned by the central schema, was creating temp tables in the central schema. That is no good, as each user needs their own copy of the temp table.

When I was asked for help, I said that we have the technology to make this work. We have it set up in the old stored procedures. However I referred the developer to our DBA Team. The lead DBA told us to set up invoker’s rights for the procedure. Normally we seemed to use whatever the default was. And it seemed to work before. We tried all kinds of debugging, but could not get this to work without using invoker’s rights.

Definer’s rights s the default in Oracle. Whoever owns the stored procedure (the definer) has the credentials that determine what other code the stored proc can call, and what tables and objects are used by default. This is not what we want on our project.

You have the ability to override the default behavior and specify invoker’s rights. This is done by specifying the AUTHID CURRENT_USER keywords when creating the stored procedure. This causes the context of the current user to be used when a stored procedure is called. Sure enough when we made this change, the temporary tables started getting created in the calling users’ schemas. Now we got some new stored proc code to update.

Automatic Documenation

2009-06-25T18:28:00.001-07:00

How can you create an Entity Relationship diagram automatically from the structure of the Oracle database? Other databases have tools to do this such as PostgresSQL Autodoc and MySQL Workbench.

I got some advice from some people for products to try. One of them is Schema Spy. Another is Enterprise Architect. A final one is DB Visualizer. Right now I use tools to execute SQL. Somebody else on the database team generates documentation from the database. However I don’t think they use any specialized tools. I think they either user Oracle tools, or some custom written scripts to do the work.

Our documentation is mainly text information on the database. This is normally based on the data dictionary. But it would be nice to have some fancier ER diagrams to use and show off. It might be time to ask for some new software to be purchased.