Yet Another Oracle DBA

OCM - MOS - Metalink

2010-05-18T17:46:00.000-07:00

Well,

Being removed from using oracle support for a period of time, I was spared the implementation pains that others experienced. Too bad those pains haven't subsided. Come on... Flash?!?!?

If you put the fact that they chose flash (come on flash?!?!?) aside, the system is actually very good. I can see the immediate benefit of using a collector and sending the data to them. I was able to raise an SR with all of the particulars in about 2 minutes, would have been less if the !@$#%! backspace button worked, but it's 2010, why do we still have backspace buttons.

I don't have any of the searching issues that other's have had, the power search is actually pretty powerful when you figure it out and having a 3rd party list of missing patches has already proven to be a great asset in getting things up to date. I generally feel that given enough time, MOS will be a good system, assuming they go to something else other than flash.

Come on Flash!?!?!?!?

I am back

2010-03-13T12:16:00.002-08:00

Sorry for the delay, economy, company I worked for was bought, then shutdown with pretty much everybody - including me - laid off.

I have been working as a technical analyst for a media firm until just recently, it was a great job, with great pay and even better people, but the term is over and I am back to being a DBA with a smallish internet based company.

I will be posting again, I promise.

Documentation

2007-08-27T10:15:00.001-07:00

The "you have to document code" theory has cropped up again, I have resisted writing about this in the past, but I am in the correct mood today. I thoroughly respect the writers that are all for heavy documentation of code, people like Eddie Awad, he is great but I do disagree with him and others.

I am not picking on Eddie, he just has the latest post concerning this that I have come across. Eddie has had a few posts about comments and documentation, with the most recent being Self Documenting Code is Not Enough where he references an article titled Comments Are More Important Than code, I have read in the past by Jef Raskin. I do believe with some of the writings, but where I heartily disagree is basically, comments are more important than code. My view on the matter is pretty darn simple. If you need comments to follow the code, get another job because you don't deserve to be a programmer. If you can't spend 5 minutes and follow the flow of the code, even hundreds of lines, your local fast food outlet is always hiring.

Now, where comments do belong is describing the business logic behind the code, but not the code itself, code itself is self documenting because, well it is written!! Yes you always use proper naming conventions because everybody has their own naming conventions that are the better than everybody else's. Using conventions is not documentation, it is job preservation.

The documentation belongs as normal human readable sections at the beginning of the code segment and in a word document everybody has access to and only 1 person (and a backup person) can update. I hate to say it, but flow charts and other visual items are the best way to get a point across. All the staff that look at the visio can go "ooh, ahh, look at the pretty pictures... ahhh, look at the pretty colors.".

My top 5 reasons why database software projects fail.

You have a scrum master.
You follow agile programming, extreme programming or any other cluster f*ck methodology
The comment "we want to be database independent" is heard.
The comment "We don't need source control." is heard.
The number of minutes spent in weekly meetings by a developer is larger than the number of minutes spent in the bathroom by the same developer.

One of the reasons why a developer is let go in our organization, they say something to the effect of

"I was writing the documentation, I didn't have time to finish the program that is why I am X days late"

In their past companies, they have found that saying "I was doing documentation instead of code" is a perfectly acceptable excuse for being late. We translate that comment into "Please fire me because I am lazy and don't respect deadlines.".

We have been doing a bunch of hiring lately - as normal - we just can't keep up with the oracle development positions, I might be just a little hard on them. I have recently streamlined my end of the hiring process greatly. First 3 questions on the interview

Who is Thomas (Tom) Kyte?
Where can you get all of the oracle documentation online?
Have you read the application developer fundamentals guide for oracle <latest version here>?

Without a quick and positive proper answer to at least 2 of the three questions, I basically say "Thanks, but no thanks" and hang up.

Oracle 11g

2007-08-21T18:22:00.001-07:00

Well. Since everybody is talking about oracle 11g, and I have been really quiet lately... I took today and installed oracle 11g, then I upgraded a test database to 11g with zero problems.

I ran some of our testing scripts and performance and results matched what had been documented earlier in the month. So, I bit the bullet, I upgraded a production database to 11g. Now, when I say "production" I mean a 3 user database coming in at just under 2 gig in size including system tablespace, so awfully small. It is simply a way for users to search for files that were created before 2000 and was simply 5 spreadsheets compiled to a common format and loaded. It is a recent addition to our systems and I have all of the base data that can be loaded again.

So, we are successfully running an oracle 11g database in production.

Woot Woot.

SPAM

2007-08-13T12:04:00.003-07:00

Due to the LARGE amount of spam this blog is getting, I am going to switch comments to registered bloggers only. Sorry all, but I have had enough of deleting the SPAM posts. Death to all spammers.

Don and Jonathan at it again

2007-08-03T11:13:00.001-07:00

Once again they are at it.

http://forums.oracle.com/forums/thread.jspa?threadID=527201&start=0&tstart=0

All I have to say on the matter is, Don Burleson and his employee comments, scripts, "how to's" and expert advice have screwed up more than one thing mostly due to me trusting them without actually paying attention to what was going on. Nothing from Jonathan Lewis has ever failed me.
Don Burleson has an interesting outlook on life - check out his personal blog - I won't link to it, but just google it "don burleson blog personal" and it is the first hit.

Rather enlightening to see a his view on life.

BAAG

2007-07-04T10:24:00.001-07:00

I joined the BAAG party awhile back - Battle Against Any Guess.

Go and give it a read, especially you folks that send emails that have a subject of PLZ HELP or URGENT PLZ or something similiar.

User tracking

2007-06-18T12:10:00.001-07:00

This is so obvious - at least to us here - that it really kind of shook us up that we were not doing it. I was talking to ,y friend Jeremy, and he mentioned his company had rolled out an internally built APEX app for tracking users and their accounts across all of the applications in the company. It took him awhile of explaining before it dawned on me how good of an idea this is.

They (as do we) have a database that is used for tracking employee phone numbers, with the multitude of payroll systems acquired through acquisitions having a single repository of all the active employees in the company that can be referenced even if those employees do not have network accounts is very important. We use our central database basically primarily for a place to allow employees to store their phone numbers and other contact information that is then access via a few JP pages. Jeremy and crew went a few steps farther. They actually replicate the employee information into this central database from all their various payroll systems, so that the information in the central database for where the employee is working, name changes, terminations, new hires etc is automatically looked after, so there is no out of date information when somebody transfers or quits. They then (and this is the brilliant part) pull in all of the user information from every single application in the company into this central database giving them user lists from every application. That user list is then compared to their service desk software (incident tracking stuff) and then all of that information is compared against the employee information from the payroll systems.

With all of the data in a central spot, now, they can issue a simple query on a person, and every application tied to that person is displayed and if their account is active or not. They also flag new hires and terminations and automatically generate tickets in their service desk software to have a help desk person investigate if an account is needed, verify the request for a new account is actually a live person according to payroll, and termination notices no longer have to rely on a manager at some location filling in the proper paperwork and forwarding to the IT department. Now the IT department is aware of the termination and what applications the user had access to and the IT department can now pursue the site to make sure the paper work is filled out.

In the first few weeks they were able to reduce their user count by almost 10% due to duplicate accounts, people that were no longer with the company etc. May not seem like much, but he said it was $20K alone in Novell licensing. Some reports have been put together to give them detailed user counts by application and server for licensing and usage reports.

Very simple idea but yields so much information. Jeremy did it all in APEX and oracle 10gR2 on Linux on an old piece of hardware. I will see if I can get him to put together something technical for this, I can almost smell a business opportunity here.

Good show!

SOA

2007-06-12T18:39:00.001-07:00

We have been working on implementing Oracle Fusion middleware for our SOA solution, ESB and BPEL and all the other bells and whistles, BAM and BPA. I have to say, even for an old dog like me, this is an amazing concept. We had IBM and Oracle come and visit numerous times flogging their software and we decided on oracle simply because their products seemed to be more tightly integrated and basically looked better. The kicker was that everything is done via JDeveloper instead of 3 or 4 different products like IBM's SOA stuff (Websphere).

I have built a handful of adapters of some interfaces I know very well. I was able to retrieve data without a problem, I then took a crack at creating ESB and BPEL services to then "listen" for changes and move the data around. All I can say is wow, the ease at which data can be mapped and moved around is astounding. The largest time saving is an adapter only has to be made once per system and then a simple BPEL assign to do the mapping and presto, data acquired, transformed to a common format, and then transformed to what the destination system requires. Then the ESB process sits and waits for more data to come in. Wonderful stuff, no wonder everybody is talking about SOA, even though it is in hushed voices and darkened hallways. The learning curve is extreme and my knowledge of XML,XSD and XSLT has increased a hundred fold, but it is all worth it.

After this I am off to update by CV ;)

We have a few training classes scheduled to try and get the rest of the department some exposure to the products. BPA - Business Process Analyzer I believe is what the acronym is, there are just a few to try and remember. BPA is a product that I have just started to look at, we bought a lot of licenses for it, and as the marketing for it says, it allows business "super users" to actually map the work flow in an almost Visio environment, pass that to a developer and with a lot less work than I expected, the developer can turn that into a BPEL flow and deploy the application. The best part is the changes to the flow the developer needed (if any) are automatically passed back to the user that created the original document.

Really cool stuff.

How to get fired

2007-06-04T06:55:00.000-07:00

http://freelanceswitch.com/humour/competition-results-fourteen-surefire-ways-to-getting-yourself-fired/

Well, I made a list

2007-05-10T07:51:00.001-07:00

I read Eddie Awad's blog all the time. Once again, I was quite surprised when I went scrolling through the listing of the Top Oracle Blogs Ranked by Technorati Authority and, low and behold. I am on the list.

#124 out of 130 but, on the list none the less.

Date columns

2007-05-08T11:02:00.001-07:00

I have a good friend from a previous job who is an Oracle DBA, he is kind of shy and doesn't like a big presence on the net. I have blatantly stolen work from him in the past, including some excerpts from this particular document (always with his knowledge). But the poor bugger had the lack of foresight to send me this complete PDF document. So, I quickly decided I had better post it just to show him blogging isn't all evil and invitation to flaming.

That said, Jeremy's quick write up on oracle date columns that he did for his internal support staff. Absolutely nothing earth shattering or ground breaking, but I can't see any errors with it and it is good to give to people who can't speak "DBA". Yes, I have used this in the past elsewhere as well. But this is the complete PDF.

More APEX

2007-05-07T07:24:00.001-07:00

APEX is great, APEX is a life saver... OK, job saver?

As I mentioned before I like APEX, but not having the time to develop in it and learn it well, I get a little lost in the product. The APEX forums are a good place to pick up information, like most forums if you use them properly. I read Dimitri Gielis blog on a regular basis, imagine my surprise when he posts about answering one of my questions.

Thanks again Dimitri!

Fire Fighting

2007-05-04T20:47:00.001-07:00

With the conversion of '07, staffing issues, hardware failures, fires, and the day to day grind, my job satisfaction over the last year has well, been at an all time low. I can never seem to get anything done properly, everything is patched together and done in haste to move onto the next fire. One of the fine ladies that works with me actually bought everybody in the group a little fireman's hat and a tiny little working fire hydrant and hose squirt gun, because it seems to be how we are viewed by the company as of late, simply running around putting out fires. A necessary, expensive evil entity who never has time to talk, and is only around when there is an emergency, or a fresh pot of coffee.

Well, today was a little different. 4 of us IT people had taken a group trek to the lunch room for a refill, we bumped into a few of our sales staff, once we all realized we were on hallowed neutral ground and no marking of territory was needed we started to chit chat, the weather around here has been record breaking and that is what we were talking about, all relaxing nicely. Then one of the sales guys pipes up, completely out of the blue and says "So, any of you ever look at our order processing and shipping system?". Well the system is a bolt on system to our oracle e-business suite and is relatively new. Development was outsourced and then the application support was laid in the lap of our haggard sales support team. None us IT people at that time were part of the sales support team, so the answer was a resounding "No". I monitor the database, but it is quiet and never complains about anything.

The sales guy goes on to talk about how over the last 2 months, it takes longer and longer to process an order, to the point of the sales guys queue up their orders and process let the system process them over night, because if they process the order manually, it locks up the application for as long as the order takes to process. The sales guy, lets call him Henry, goes on to say when the system was first in place, an order would process in about 1-2 seconds, now an order he processed this morning while a customer was on the other end of the phone took 7 minutes. I know his time is valid because the screen actually tells the sales person how long the processing took.

Henry continues on saying all of the sales folks have raised the issue with their manager, who was supposed to pass this along to the IT group, but it appears never has. I decide to return to my cube and take a look. I do the normal thing of right off the bat of starting a snapshot every 10 minutes and dig up some of the baseline snapshots so I have something to compare too. I call Henry and ask him to process an order so I can trace his session. I find his session and start a trace, he processes an order, and his session goes inactive after only a couple of seconds. I have Henry on the other end of the phone, and he says the process is still running. I can plainly see, that according to the DB, the process is over.

The app is a Delphi fat client, so I can rule out any connection pooling or anything like that. I go up to Henry's desk, the process is still going. I wait until it finishes, it takes almost 7 minutes, and he reviews the PDF that pop's up of the order and then he clicks the forward button and lets the work flow take over and push the order off to the next people in line. Those people happen to be in the cube right next to Henry, so I check with them. They say yes, they see they have an alert that the order has come in, but the lady is not wanting to open the order because at this time of day, it can take almost 10 minutes for the form to open., she says, first thing in the morning she can process a hundred forms in just a few minutes and I should come back tomorrow. I push the issue and she opens the form, and yes it takes - get this, 7 minutes.

Speculating to myself on what the problem might be, I go down to the sales support group and talk to their team lead, they know about the problem and it has been a ticket in the help desk for awhile but they are short staffed by 3 and do not have time to deal with it any time soon. I ask if I can, and of course am promptly handed the keys to the system and told to "have fun, don't change anything". I take a look and I find the PL/SQL that generates the PDF, it is a simply external call to a java program via the client that reads the DB and generates the PDF. I can run it in test no problem, generates a PDF in a couple of seconds. I ask Henry to try test, he does, takes just a few seconds.

Well, this is turning into a really long story, but I needed to lay the groundwork, sorry about that. Continuing the story, I decide to check out the DB server. lots of free space, minimal CPU usage, our reporting software shows that the machine only averages about 70% CPU usage over the course of a few days. Well that isn't it. I then cd into the temporary holding location for the PDF files and do an ls. Then I wait, and wait, and wait, and wait. I open another session and check the server. No CPU usage, no nothing. Being the server is a Linux box I run top and take a look, nothing out of the ordinary, I run iostat, nothing out of the ordinary. Very confused I check the other session and it is completed, I run "time ls" and wait. I am sure you can guess it, time comes back to just under 7 minutes. I check the scroll back on my terminal, it goes back to the max of the buffer, 2000 lines. I do a ls | wc -l. I wait the 7 minutes and get the outstanding number of 532,932. Wow, I didn't think that was possible, that is a lot of files. Then I realize with the file name sizes, ls is 4 columns wide. That is 2,131,728 files in that one directory assuming they are all the same file name length. I go back up one directory, do a ls -lrt and the directory is only 2 bytes in size. That doesn't seem right, with that many files in there, the directory header should be massive.

I call the SA group and have them check. They come back, surprisingly about 10 minutes later saying "Yeah, the directory is f*cked" it is a mount point on the NAS and we can't even see it from console. The NAS is mounted to the DB server and as a share that the sales people can access. A few phone calls later, and we confirm that the PDF's are temporary and not needed, we down the DB with the user's consent, the SA's unmount the drive and remount a new empty one immediately. Once we bring it back, we call Henry and he tries it and an order processes in under 2 seconds. We look at the directory and there is a single PDF in there. We know we haven't written 2+ million orders in 2 months (we wish!!), so the SA's work on getting some files out of the directory so we can take a look. While waiting on the SA's I monitor the directory, there are now 3 files, but Henry is the only user in the system still, we haven't released it to the public yet. I call Henry, he processed 1 other order, not 2 more - he swears by it. I asked Henry to process another, he does. now 7 files, and another one, now 15 files. I tell Henry to stop and call the support team and tell them about the problem.

The sales support group immediately drop everything and dig into the issue. Takes the team lead about 10 minutes to find the problem. The Delphi, just in case, backs up all files submitted by that user before it processes the current order. It assigns a sequence number and copies the files, effectively doubling the number of orders issued by the sales person before the new order is generated. The user's PC does all the work copying the files. The code to remove the backup files is instead a simple comment saying something to the effect of "delete was taking too long, investigate using a cron task to delete, the directory can fill up fast", made by one of the sales team support people who left awhile back. A quick change, and bingo, the code will delete order's older than 24 hours and not make any backups. Since the app is ran from a network drive, the sales group compiles a new version and pushes it out. After letting a few more users in, we verify the problem is fixed and go home for the day.

This made me feel good. Nothing to do with problems in an oracle database, no meetings, no hours of planning and discussion. Just suit up, get to work, and put the fire out...

sigh.

Migration from Access

2007-05-02T10:45:00.001-07:00

A few months ago, we hired a consultant to work with a group of users and start to migrate from the numerous access database applications and excel spreadsheet "applications" to APEX. At the time it was APEX 2.0. We created them a database on a little used server and gave the consultant about 100 gig of storage and a list of items the consultant must do and must not do written into the contract.

We have heard very little about the project, the manager in charge pretty much left them on their own. Today we had a meeting which turned out to be the "wrap-up" meeting for the project. The consultant and the users, in under 4 months converted 16 MS Access databases and 44 excel spreadsheets to APEX applications. LDAP authentication and centrally managed user list as all the apps are in a single APEX workspace. The project is magnificent! The users are happy, all the rules have been adhered to and the consultant came in under the time limit getting the bonus money that was part of the contract. Everybody is happy all around. I looked into the DB, proper referential integrity, very few bad SQL, the schema is organized well with non shared tables prefixed with a TLA that they came up for that particular MS access or excel application. Applications talk to each other when necessary and the users are reporting much better productivity and no missing transactions or data.

Every application is snappy and efficient. The consultant and the project user lead also signed off on retiring the 155 MS Access licenses, which will be a nice thing to stop paying for when we renew.

All and all, a good day!

APEX is amazing... I still struggle with some basics on it... but I am a DBA, not a developer. We already have another meeting with the consultant to put them onto another APEX project we have waiting in the wings.

The big leap

2007-04-23T08:03:00.001-07:00

In the last 8 months we have cycled through 4 MS SQL Server DBA's at one of our partner/remote sites, 11 servers, 11 databases. We are responsible for the IT services at that site and hiring SQL Server DBA's has become a 4 letter word. The original long term fellow had health problems and had to go on permanent medical at 40, I wish him luck. The first fellow we hired was a complete and utter useless twat who we fired after 2 months. The next fellow we hired was an OK fellow, a little too Microsoft branded, he had pretty much every MS certification known to the universe and made sure you knew it. He only lasted about 3 weeks when it was discovered he lied on his application and had a criminal background. We don't care if you do, just don't lie about it because the position requires being bonded. We also changed our company we use to check things like that because they were a little slow. This last lady, well, lets just say, she fell somewhere in between utterly useless and waste of skin and we let her go 2 weeks ago when 2 of the servers went down hard and it was discovered she hadn't been doing backup's since not long after she started. Her supervisor at the site was give a good dragging over the coals as well.

I was asked if I would like to learn to be a SQL Server DBA right around the timeline of the first replacement. I said "No thank you". When we let Mr. MS go, I was asked again. I said "Do not have time". When the 3rd guy was let go, I was asked again and I said "Starting to look that way isn't it". And now, that the last (and only) lady is gone, I was begged to learn SQL Server, so I said "OK, but only in a backup capacity", of course, if I believe that, I am sure the tooth fairy and Santa are going to come visiting bearing gifts of great winged pigs. I know enough about SQL server to get around, start, stop, run some scripts. Nothing to do with performance or optimization or anything like that.

So, this past weekend, I started to read the documentation for SQL Server, and immediately began to miss Oracle documentation. MS doc's suck. Nothing else to say, than they suck. I read what passes as the concepts guide, and started to wonder why does MS charge for SQL Server? it should be given away for free because it isn't worth any money to anybody at all.

I have put myself completely at the mercy of a company we use for training purposes, and they are coming up with a complete MS certified training course for SQL server with all of the prerequisites needed over the next year. I shudder to think of it. The admin at the site is also making me a VM of the test server so I can play with it on my laptop.

Conversion of 07 is done!

2007-04-16T08:54:00.001-07:00

Required a marathon 36 hours at work for 3 of us, but it is complete. The users have been using the new production system for almost 24 hours now. New system is magnitudes faster than the old one, the users are ecstatic.

Only issue we had was about 2 hours into the data transfer some chuckle head at the remote site accidental stepped on the power bar for 2 of the 4 new internet routers, but the error checking took care of it. Once the modem's were powered back on, the process realized it, and started the transfers again.

Application Express 3.0

2007-03-26T07:03:00.001-07:00

We have eagerly been waiting for the release of APEX 3.0 , Last week, APEX 3.0 was made available for download. We use APEX extensively for internal application development and are porting many oracle forms applications that were internally developed to APEX. The users love APEX, the IT people love APEX, I as a DBA love APEX. We were looking forward to the PDF printing most of all, the ability to build PDF reports in APEX was going to be a huge feather in the cap for oracle and there was serious talk about having a vendor port a major application from a Delphi fat client to APEX, the vendor was on board, everything seemed to line up.

Then we installed APEX 3.0, the upgrade and installation on our test environment went perfectly, no problems at all. We start to read the documentation about the PDF printing, and we realize. We need to install XML/BI publisher to take advantage of the robust PDF printing that was all the rage. Well, that burnt us. Yes we can install the FOP stuff and do really basic reporting, but we had hoped the PDF generation was part of APEX 3.0.

We have had XML/BI publisher on the radar for awhile, knowing we will have to go to it with our Oracle E-Business suite upgrade later this year. I guess I had upgrade our evaluation copy to the newest version and see what has changed and try and figure out some business cases for spending the C$45,872 per CPU for XML/BI publisher.

We still love APEX 3.0, wonderful product, if your not using it, you should figure out a reason to.

Oracle sues SAP

2007-03-22T20:26:00.001-07:00

Interesting

http://www.theregister.co.uk/2007/03/22/oracle_sues_sap/

I had seen some rumours floating around that Oracle was going to try and take over parts of SAP, guess the rumours were wrong, but some legal work was brewing. Makes you wonder who stated the rumours to begin with.

Ramblings

2007-03-12T21:12:00.001-07:00

I am bored, I am sitting at my desk staring at a very slowly moving tail -f on an rman log copying a production database to test, 52 gig of data, the fun never ends here. It is almost 8pm and I have been here since 7am this morning, let the ramblings begin.

I am an avid follower of Jonathan Lewis and his articles on the oracle optimizer. I have his book "Cost-Based Oracle Fundamentals", and I have actually managed to read the entire thing from cover to cover. I can guarantee to you that most of it went straight in, and straight out leaving little behind. I hope at least that enough of it remains behind to have a positive effect at a later date.

I had an actual case to use Tom Kyte and Jonathan Lewis blog entries on ordering a query, showing a developer he can't rely on the order in a table, because there is no order in a normal heap table. Didn't take much, simply forwarded him the links and let him try to find a way to prove them wrong. I haven't heard back from him.

We have 2 ISV's, lets call the Bob and Doug that have really been causing me grief lately, these are small shops that unfortunately have developed two systems that have become integral to our production. It is amazing, these two companies are located within a few kilometers of each other, they have no idea of each other's existence, but they cause me the same troubles. The troubles are always the same, no apparent in house testing of patches or upgrades, they appear to be under the impression that is what our IT staff is for - testing the ISV's code. Bob is database happy, they keep asking for more and more databases on our side. In our environment (they VPN in) they have a production, a test, a dev, and a QA instance for each province we operate in - total of 12 instances each around 50 gig in size. Now, that doesn't seem like much, but we do absolutely no development internally - none at all. These instances are here basically because they don't have the server space available to have what they think they need so they burden us with the responsibility of keeping the databases backed up and in good working order. The copy I mentioned at the opening is being done on their behalf. We pay them support, yet we maintain their support environment.

Doug on the other hand is amazingly skilled at stalling problem fixes long enough so that the users simply forget and develop work around. When Doug does release patches or upgrades, something always, consistently goes wrong, never during our testing of course. They give us a list of what they changed, we test that, and do a general test of everything else, this particular example is year end stuff. In October they release an update that worked pretty well, they only had to release the update to us for testing 4 times which is a new minimal record, with the maximum being 21 times. We test, users signed off and away we went. January comes along, users do their month end and everything works great. February rolls in and we are doing an internal audit between Doug's system and our financial system, and the auditors notice a rather minor $60K variance. Tracking it back, turns out that in the update in October the developers at Doug's company slightly modified a view that is only used at month/year end, "for performance purposes" and never told us about it. Their solution to the performance problems was removing a rather important table from the query which tracked and accounted for user manual changes in the data.

The user that sent the data should have read the reports and caught it early, so Doug made sure the fault landed solely on the users shoulder. So, the users had to make two correcting entries in the GL. Luckily the discrepancy was small and we didn't have to change our year end results. We are still waiting on Doug to give us a document on any changes necessary to their application for DST.

We are hiring an oracle applications support person and 2 IBM Lotus Notes (shudder) developers. I haven't quite figured out where the management plans to seat them, our cube farm is pretty packed together with us each only getting about a 9 foot square of space. Maybe they think we don't need that easy access to the fire escape, they can cram one in there. What they plan to do with the other two, I do not know. Possibly stack us up, lay some flooring across the tops of the cubes and put the cubes two high. We do have a very tall ceiling.

As for my "shudder" about IBM Notes, I have no bad feelings toward the developers that use Notes, I just hate IBM Lotus Notes, it is simply one of the worst programs ever created. We have the newest version of it (7x) and it still sucks. The only time the IBM team that develops Lotus Notes stops building something that sucks is when they start to build vacuum cleaners.

Oh boy, we need to work on the I/O on this test system, I swear there are gerbils in that server running back and forth with some floppies in their mouths transferring the data between disks. 52% done.

I managed to get management sign off on upgrading an oracle 7 database to oracle 10gR2. Apparently the company that supports the application uses 10gR2 internally, even though the majority of their customers are still on oracle 7. The application is 100% web based using some web language I can't remember so the upgrade is apparently really easy and they are going to supply us with the necessary scripts. That will only leave us one oracle 7 database in production.

uggg... I have to type slower, the RMAN log hasn't moved in minutes.

I have been keeping up with what was happening at Hotsos this year by reading Doug Burn's blog. His house mate of the month is amusing and his technical knowledge and writing style are well above average. I came across Don Burleson's personal blog, I follow the forum he hosts. Well, lets just say that starts a whole new chapter on that fellow for me. I know he needs to plug his and his fellow Rampant press author's books, but come on :).

I see my RSS reader is showing me that David Aldridge has posted again, finally after a very long time.

The Conversion of '07 continues later this week, this is the final test before we have to do it in production. I will write a note or two on how it goes.

Well, enough rambling for now.

DST over

2007-03-11T07:51:00.001-07:00

Well, the time is passed, no problems related to DST have cropped up. We all breathed a sigh of relief. Some frantic last minute patches were put in on our large JSP application, when I say last minute, I mean just after midnight this morning, a few hours before the time switch.

The only major casualty, which is out of our hands, is our cell phone provider seems to have had some issues. All of the cell phones switch just fine, but none of the blackberries did. Oh well, manual switch of the time and good to go.

DST is over...

for now.

OEM 10gR3

2007-03-08T07:28:00.001-08:00

For those of you that remember, about a year ago, we installed and got OEM 10gV2 installed and running, and I was hopeful. Well, about the only thing OEM was used for was downtime reporting by a manager. The occasional email from the system when something came down, but not much more. No new agents installed on new servers, nothing kept up to date, basically OEM 10gV2 was a dismal failure.

Well, we have decided to upgrade to OEM 10gV3 and take another kick at the can and see if we can get OEM configured the way it should be and use it the way it should be. Our big push in this came from our Oracle Technical Sales representative. He came by (for free) and learned our environment over 2 days and then he presented some very compelling reasons for using OEM during a 7 hour presentation. Every single one of the reasons was expensive, but he got the managers convinced.

We will be upgrading (or reinstalling) to V3 by the end of March. Our sales rep gave us a 60 day free trial of all of the OEM packs on all of our servers to get me hooked, as well as 5 full days of the technical sales rep here helping out configuring and the proper way to use it. For the estimated $900K bill (before discount) to have the OEM packs on every database and the non-oracle database servers monitored as well, and our SQL Server databases plugged in too. Oracle is willing to spend some time.

We are also looking at purchasing Oracle Fusion Middleware as our SOA solution, so later this year, oracle is going to make some money on us.

I am now off to a presentation where I am going to try my darndest to convince management that we really and truly need to upgrade our oracle 7 and oracle 8 production databases.

DST... Ready?

2007-03-08T07:13:00.001-08:00

Well,

We appear to be ready for the big bad early DST. Yesterday we rolled every single one of our test servers forward and waited for the OS to do the switch. No databases came down (yeah!), unfortunately some of our vendor supplied patches for our large JSP based application seemed to have failed badly as the application simply refused to allow data to be entered "PO create date can not be past PO update date" or something like that.

Now, for the databases where test and production are on the same server - well, that is going to be a "fingers crossed" type of fix.There will still be a large number of IT people in on the morning of the 11th for the old 'just in case'.

Insert into multiple tables from a single query

2007-03-03T19:52:00.001-08:00

A friend who does not blog wrote this up for his co-workers, it is straight forward but useful. Enjoy.

A few days ago someone asked if it was possible in an oracle DB to insert into multiple different tables from a single query. I said "Yes of course", they asked "So, How?", I of course said "RTFM". Well, here it is a little easier to read than in "The Fine Manual"

This works all the way back to Oracle 8 so feel free to test it out. But, since it does drop objects, please do it in a test location. Personally I recommend everybody download and install oracle XE ( http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html ) on your PC, gives you a nice safe place to work, play and learn, and as an added bonus Oracle XE comes with Application Express (APEX) already installed and ready to go. Now that I said that, I don't support PC's so who knows what it will change on the configuration for on your PC. Do so at your own risk.

Simply creating some test tables and a sequence for later use in this example.

SQL> CREATE TABLE BASETABLE (BASEID NUMBER PRIMARY KEY,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL> CREATE TABLE DEST1 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.01

SQL> CREATE TABLE DEST2 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL> CREATE TABLE DEST3 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.06

SQL> CREATE TABLE DEST4 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL>

SQL> CREATE SEQUENCE DESTID_SEQ;

Sequence created.

Elapsed: 00:00:00.00

SQL>

Insert some data into the base table for use later

SQL> INSERT INTO BASETABLE SELECT ROWNUM*-1,DBMS_RANDOM.STRING('A',30) FROM DUAL CONNECT BY LEVEL <=500;

500 rows created.

Elapsed: 00:00:00.09

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

Now the actual insert, you can see the WHEN and ELSE clause of the INSERT statement. You can have as many of those as you want, each inserting different combination of columns for the VALUES section. In this case, I am using a sequence to satisfy the primary key of the DESTx table and then the two column names from the select clause at the end.

SQL>

SQL> INSERT ALL

2 WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

3 WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

4 WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

5 ELSE INTO DEST4 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

6 SELECT BASEID,BASEDATA FROM BASETABLE ORDER BY BASEID DESC;

500 rows created.

Elapsed: 00:00:00.01

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

Now to show what happened. From the following query you can see that the BASEID of -1 was inserted and the DESTID was the very first record in the insert as shown by the sequence value of 1.

This following data was inserted based on the

WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)line in the insert statement.

SQL> SELECT * FROM DEST1;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

1 -1 uzvIPoJevGslWNzcsEULVsOIHrWtkA

Elapsed: 00:00:00.00

From the following query you can see that the BASEID of -10 was inserted, and was the 10th line in the select query return result. This was inserted based on the line

WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.

SQL> SELECT * FROM DEST2;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

10 -10 AzRwrjLpzvxtacxBOitYhGDGDuKmaU

Elapsed: 00:00:00.01

From the following query you can see that the BASEIDs of -100,-200,-300 and -400 were inserted.This was inserted based on the

line WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.

SQL> SELECT * FROM DEST3;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

100 -100 uJixIEqFTeZEBDOCPYkJgyipInuTdt

200 -200 ikmTNgdjGTjkINEGbxEFifWAetPBMt

300 -300 gKcFyianMOtGzdJzVlkjqaLPiwBkic

400 -400 prucyUxTqhPhUTzarsJRyFQYlOUlWz

Elapsed: 00:00:00.01

From the following query you can see the remainder of the records in the BASETABLE were inserted into the DEST4 table. If you look you can see that BASEID of -1,-10,-100 and -200 are missing. You will have to trust me that -300 and -400 are missing in the result set as well, but I didn't want this running too long.

SQL> SELECT * FROM DEST4 ORDER BY DESTID;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

2 -2 fPNMkRbJAEoeaWejzrAigZjKqZVzUl

3 -3 NDmRQNKmPhAnzfuWhLQDnWIcRVpjLF

4 -4 DoNnVEskItQAfANavQVHdJWdOeZbAc

5 -5 SNacUWsrPCPyLwDBxEtndSsiiSTmPW

6 -6 gLxiVlWXsdcLPhDgLThISCutKBfuOj

7 -7 sZCNlljiTveZPIUgyEBPalpJPrMdck

8 -8 UOwvqNxyPXcpsxRmjsxLQGfEsHQOqO

9 -9 WDwQqUnMHjDautMrYYBMCcjIoNWMKg

11 -11 BOfKwqtFZWQuLVEHFhMRHrfBGyeTfQ

<SNIP>

99 -99 VjmavGgzdQroTHutlhcOQjiqlTiLHW

101 -101 cjuHxrklWRaQmRJZyVShliswLRCgBm

<SNIP>

199 -199 xvaXYHPkexmFOkXCDBOODqjEatyMwY

201 -201 fXwQaaSTWAEDrYDqnRHVxLqcQEkbCZ

<SNIP>

500 -500 eLqsjEKEzWTmQUTsEtHFcRVEkEiQZz

494 rows selected.

Elapsed: 00:00:01.06

Now simply the cleanup.

SQL> DROP SEQUENCE DESTID_SEQ;

Sequence dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE BASETABLE;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST1;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST2;

Table dropped.

Elapsed: 00:00:00.04

SQL> DROP TABLE DEST3;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST4;

Table dropped.

Elapsed: 00:00:00.01

SQL>

SQL> SPOOL OFF

New Look

2007-03-03T19:09:00.001-08:00

I decided to finally allow Google to move my blog to the new now no longer beta blogger.

It looks good. New "spot", I decided on a different look.

If you care, let me know if you have any issues with it.

Thanks.

More on DST

2007-03-02T06:04:00.000-08:00

When did oracle start being ran by a bunch of morons?

This DST patch is simply a joke. There must be a punch line in here someplace, because there should be no way that a company the size and past capability of oracle can screw something this simple up so many times and on so many levels.

One of Oracle's marketing slogans

I guess only if your information is not time stamped.

What a crock... SQL Server simply looks just a little better now, but I am sure after the next 9 days of being awake at work patching databases again, I won't remember I said that.

DST

2007-02-26T07:18:00.000-08:00

Is it just me, or is oracle's effort to wards the DST seem to be a convoluted mess of notes, readme's, more notes and superseding items. Download this, this, this, this, and this, and possibly this but only if you have this and this. Version 4 of a patch? So, we go ahead and patch take the necessary downtime, and then version 5 comes out, we go ahead and patch, and version 6 comes out, we go ahead and ... well you get the point. Easier to shut everything down over the time change, manually set the time on the server and continue from there. Luckily we have only one system that uses timezones and the user group took this opportunity to punt it and purchase a newer system that does not.

Sigh... Rant over.

On a good note, the conversion is going along nicely. The consultants found some other consultants to help with the work load. All of the hardware and new network lines are in place and tested and we have a target date of April the 7th for the final move. The second test of the database move will happen 2nd week in March as that is when the consultants say they will have the first ready for user testing front end working.

We Love RMAN

2007-02-14T07:13:00.000-08:00

Say "We love RMAN, RMAN is great"

Say it again. And again, and one more time for good luck. RMAN coupled with a good backup strategy saved my... "our" butts this morning. Get an email from my monitoring stuff that a production database is down, about 2 minutes later get one from OEM saying can't connect. Was sitting at a coffee shop having breakfast, so go across the street to the office and take a look, yes, that production database is down, crashed hard. All 3 incoming lines on my phone are flashing away. Connect to the server, take a look, hmmm... that's funny, we are missing the entire /PROD mount point. Completely gone. Try my limited knowledge to see whats up, give up after about 1 minute and call an SA. After a quick conversation convincing them the mount point is gone they go and look. This is a DAS box with good mirroring and RAID, I am not concerned at all. On /PROD are the tablespace files and a control file copy.

15 minutes later, all 3 SA's come back, all 12 disks on the mount point are gone, dead, no lights, no comforting whirring noise. Nothing. No explanation why, but they will plug the enclosure into another server to see what they can see. We wait another 20 minutes and they can't get any of the disks to spin up, even plugged a disk plugged in on its own, nothing, finished like last nights dinner.

They have more disks and rapidly rebuild the enclosure and plug it back into the server and start up the whole shebang. They get the mount point /PROD created and accessible after 45 minutes or so and then wipe their hands of the matter by telling the managers all they can do is done, it is in my hands. By this time managers (vultures) have been circling, now they have landed and fighting amongst themselves to who will have the privilege of the first juicy eyeball to be plucked from the assumed to be near death DBA.

copy a control file from another mount point on that server to /PROD.

$rman / target

>restore database;

{wait about 15 minutes as files come off of our tape array online storage, go and get a coffee, mingle and socialize while the vultures (managers) eyeing me the entire time asking me why I am not at my desk}

>recover database;

{wait about 9 minutes, finish coffee and chat with the folks just coming into work}

>alter database open;

> exit

Few quick sanity checks.

Tell everybody it is back and start a backup just to have it.

Smile brightly and continue on with my day.

Conversion of '07 - First stage finished

2007-01-26T11:17:00.000-08:00

The first export & load process finished. 304 gig of data transfered and loaded in 188 hours, only 15 tables had problems and were all easily corrected and scripted around for the next test. We had some network problems too that caused us to rewrite the process script on the linux server to pull the file size that was created on the old server and verify it against what actually appeared on disk.We are going to take a different approach to our transfers for the next test, we are going to have 5 "pipes" to transfer data over as the network guys promise to have 4 VPN's connected by the end of next week, simply will be 5 different IP addresses to transfer too so we can manually split the transfer up over the dedicated pipes and the shared pipe. The network guys estimate we should be able to cut our network transfer times by about 45%. The first shipment of new drives for the SAN has been shipped, the SA says it will be installed and functional by the 2nd week of February. I have assigned the PL/SQL verifying to 2 applications support people who had the unfortunate bad luck to look bored at a meeting, and I have delegated the rewriting of the .sh that creates the scripts to precreate the tables before the import to a developer here. I put an SA in charge of co-ordinating the hardware and OS installations so I don't have to. I have come to realize, being the project leader is a good thing, I don't have to do it, I can get someone else to do it and just verify they did it right.

More good news on the hardware front. Our broker found us hardware sooner than expected and HP came up with a supported OS structure that will run what is necessary for the contractors to move/port the application to a different apps server. Timing is going to be an issue though, our "soft" deadline is quickly approaching and already the MS project estimated finish date is 12 days behind our deadline. The consultants rewriting the front end have hit a major roadblock pushing their time estimates out over a week. As I said though, it is a "soft" deadline as the old system will still be functional but the contractors will become extremely expensive to continue on.

Things are still going good though and I confident that the oracle end of the transfer will be smooth.

Conversion of '07 - Still Processing

2007-01-22T19:45:00.000-08:00

Well the ftp and load process is still going at 130 hours, but so far very few errors, and the few that are happening are easily by passable or fixable. I am very pleasantly surprised. Our estimates have greatly increased due to the higher than expected bandwidth use by the users during business hours. If everything continues on track the last file should be sent via FTP on this coming Wednesday morning.

I used a combination of DBMS_META_DATA and CTAS , extracted the create DDL and created the entire database sans data on my laptop and have gotten all of the DDL for the PL/SQL ran and the majority of the objects created. A boatload of errors and uncompiled PL/SQL, but at least it is all there.The project from the oracle end is ticking along nicely.

From the hardware end, well that is another story. The hardware to replace the VMS system oh man, people that say "disk is cheap" need to have their heads examined! To increase the capacity on our SAN to hold this new DB, this includes the drives themselves, a new drive enclosure, cabling, more room on the DAS (more drives) on the tape array to hold near line backup, another 25 tapes for the tape array for the offline/offsite and various odds and ends to make all that happen - initial estimate, $425,000. We almost fell off of our chairs at that. That was just the storage for the DB. The application server estimate from HP via our broker is in, $375,000 and we can't have it for 45 days.

The conversion of '07

2007-01-21T08:56:00.000-08:00

5 days straight on this project, averaging 15 hours a day for 6 "in house" IT staff and 5 consultants. Took today off as it is Sunday, but I had to come into the office to do the "real" work that has been missed. This coming week is not going to show much work internally on this as other projects are taking priority. I used sqlplus and extracted all of the DDL for the programs and triggers, 109.302 megabytes of text DDL files (uncompressed). That was a shocker, even with almost 6,000 PL/SQL that is a huge amount.

I was made the official "Project Lead", that means that today, after spending 6-8 hours to find out why backups were failing last week and delegate the user requests that flowed in, I get to transpose the project task list and milestones into a Gant chart for our status meeting on Friday. I have to devise a project time line that will have this system running on a Linux back-ended database and the front end running on an ancient HP-UX server that was slated for the trash bin no later than the middle of April 2007. One of our SA's called HP to see about getting some warranty and parts coverage on the ancient server and the possibility of adding a CPU. He was literally laughed at, HP is looking into it and will get back to him with some options, we hope they have some newer hardware that can run the older OS and we will simply purchase the hardware. On the purchase note, we were given an almost blank check for this project on Friday, simply "keep it under 6 million", that dollar figure does not count internal IT time ("free") or the consultants who's time gets divided up and charged to the sites. The sites had came back some what appears to be valid numbers that for every day they do not have this system the chance of a processing line failure increases by 1.2%, even the smallest plant going down will cost the company $125,000 a day with the largest plant showing a net loss of $1.1 million a day. There are 17 sites that will be exposed, that woke the management up. The customer interaction and shipping data retrieved from the system is another story and can not be quantified easily into a dollar figure, the note was something to the effect of

How much is it worth when a customer who buys a million a month worth of product asks "When will my order by completed?" and the companies answer is "We don't know, our system is down".

I called our Oracle sales rep on Thursday, to talk about the licensing implications of this move, I loved the answer I got back on Friday morning - "Won't cost you any more oracle licensing as long as the old system is turned off within 60 days of the new system coming online, your covered - need any consulting?".

As I sat here writing this, 4 other IT staff showed up without being asked to catch up on missed work and work on this project. I like the people I work with.

Some good progress

2007-01-18T19:01:00.000-08:00

Well, we made some really good progress today with the DW conversion. We carved off 400 gig section of our SAN even before the new disks have arrived, and created a 10gR2 DB there in noarchive mode. I built a DCL script that runs on the VMS cluster and exports one table at a time from a list that we compiled to a distinct file name, then FTP's the export file to the Linux server with the new LUN mounted. Then the Linux server imports those tables into the new DB and sends a notification if there are any failures. We have created all of the tables already in the DB and spread out the tables over multiple tablespaces. This is more for our ease of management than anything performance based. The process has been running for about 7 hours now and has sent 1,079 tables with very few failures, but the big tables are still to come, with the throughput we are getting, are estimates are about 120-130 hours. The import process imports 4 files at a time as any more than that saturates the IO on the linux box. This is of course simply a test to see what will fail and give us an idea on how long we will have to book downtime for. The largest slow down is the network link between the 2 sites, we 100% utilize the network link. The network folks are going to investigate some point to point VPN's as they are confident they can get 3 or 4 dedicated ADSL lines into the remote site and our internet pipe is much larger (x10) than the dedicated network link.

I think our downtime will be limited though, The data import process can be stopped and only a select few users have rights assigned by the application to make data changes. We will remove those rights via the application, then stop the data load process and start the transfer when we do this for real. That will mean the users will be able to query the old system while the data is being transfered. The consultants have been very successful in "fooling" the application to connect to the test database on the linux server, took some work on their side though. They knew they were able to compile the application on an older HP-UX PA-RISC series running V10 of the OS, luckily we had one. On that server we were able to install an oracle 9 client which their application recognizes and will still connect to the 10g database. They have about 3 weeks of work to finish the application move to the HP-UX server though and that does not include any testing or recoding the inevitable failures. We have a consulting company coming up next week to work on converting the VMS DCL script portion of the system to unix shell scripts. That is going to be interesting working through that conversion.

Tomorrow we tackle the triggers and PL/SQL code and see what fails there. 15 hours at the office today is enough for me.

OCP certification and knowledge

2007-01-16T08:17:00.000-08:00

I have mentioned before that Howard Rogers is a great man, and this post proves it. Absolutely no argument from me on this.

I too have written in many places about the "dead from the neck up" job candidates I have been through over the years, OCP certification is a joke and a waste of money for companies. The people who have proclaimed themselves experts "OCP Certified" who can't answer the simplest of database questions.

I will put the list of questions we ask up on a post very soon.

New project, time for a new job?

2007-01-16T08:02:00.000-08:00

A few years ago the company I work for purchased another company of almost the same size effectively doubling our size.

During this acquisition, we in the IT group were told about some of the systems that were in place, but luckily other than the core applications we didn't have to do anything about the older systems at all. There were consultants who managed the systems and the servers were housed far from the IT group. An "Out of Site, Out of Mind" deal.

Well, yesterday, that changed for one particular system. Our manager got a call that the 3 person consulting company that looks after the "data warehouse database" for 17 of our sites has put decided to not renew the contract which expires in 90 days, effectively dropping the management of the database into the IT group's laps. This has happened before, we took it in stride. Until the DBA's got access to take a look at the DB yesterday afternoon. Oh boy... I checked to make sure the headhunter firm I use had an up to date CV and I removed my restriction on not wanting to work in the United States.

Oracle database version 7, with an astounding 3,319 tables in a single schema with only one primary key constraint created on one table. No database referential integrity at all. 891 procedures, 4319 triggers, 771 functions (no packages). OS is OpenVMS running on some really old Digital Equipment hardware. The front end uses proprietary VMS DCL scripting coupled with a Fortran VT100 terminal user interface. Total database storage across the 8 Vax/VMS machines in the cluster - 636 gigabytes with 432 gigabytes of that being the database (according to dba_segments). Apparently backups are cold backup's that start on Friday night at 10pm and finish early Monday morning if there were no problems, it is noted that sometimes the database can go 4-6 weeks without a backup during the heavy work season because the data is needed 24 hours a day 7 days a week and the database can not be shut down. At least it is running in archive log mode. Sites send data to the database via file transfer every morning and the data is loaded some what manually by some clerks at one site.

Management asked us how long it would take to convert the data to an oracle 10g database (or 11g) on linux so a development team can be put together to build a new front end because apparently they sites can not live without that data that goes into that database, once the data is converted to 10g on Linux then we can look at redesigning the entire system but downtime has to be minimal. I was handed the "documentation" that the consulting firm has done to date, and was promised the bulk of the remaining time the consultants have on contract will be dedicated to writing better documentation. I would hope so, because the documentation I tried to read last night looks like it was written by a 11 year old in a hurry to complete a school work assignment. We also got a 3 inch binder stuffed full of about the last years worth of user requests, user filed problems and "bug" tracking that the consultants have - nothing electronic at all, all done by hand. I have until this coming Monday to come up with a realistic plan on converting the data to oracle 10g.

This project scares me, this is far past the "take it as a challenge" or "show off your skills" that the management is throwing around, I see this becoming a career ruining and stress related heart attack inducing project. Anybody out there looking for an experienced level headed Oracle DBA with years of experience in both DBA skills and development skills? :)

I think this project will be the seed of hundreds of blog posts.

Blogger and formatting

2007-01-05T13:05:00.000-08:00

I forgot just how hard it is to get something to format the way I want it on blogger, I think I will have to do some research on another blogspace.

Old Questions refreshed again

2007-01-05T09:50:00.000-08:00

Saw somebody bumped one of the many "is select count(1) faster than select count(*)" threads on AskTom .

I believe Tom, but I just had to go and look AGAIN to see if anything changed in 10gR2:

SQL> select count(1) from dbtesting.t; 

COUNT(1) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
0 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
413 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(*) from dbtesting.t; 

COUNT(*) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
0 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
413 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(66666) from dbtesting.t; 

COUNT(66666) 
------------ 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
1 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
417 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> select count(dbms_random.value(1,1000)) from dbtesting.t; 

COUNT(DBMS_RANDOM.VALUE(1,1000)) 
-------------------------------- 
20413 

Elapsed: 00:00:00.17 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


Statistics 
---------------------------------------------------------- 
1 recursive calls 
0 db block gets 
315 consistent gets 
0 physical reads 
0 redo size 
437 bytes sent via SQL*Net to client 
381 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 

SQL> spool off

> >

There, now if somebody somebody is wondering, the argument is solved. They are the same when selecting from a table without a predicate, I even threw in the dbms_random to show any number in the count() is the same.

Dizwell is Back

2007-01-02T09:37:00.000-08:00

Ahhh... there we go.

Howard relented and opened his site again. Howard is a great guy!

http://www.dizwell.com

ORA-07445

2006-12-30T19:03:00.000-08:00

It is interesting to actually listen to users occasionally, down right extraordinary. Take this for example.

I was surprised to see on a brand new oracle 10gR2 DB which sole purpose s to test the application for upgrading from 9iR2 this error come through our monitoring script:

ORA-07445: exception encountered: core dump [kddlkr()+748] [SIGBUS] [unknown code] [0x000000010] [] []

Within 1 minute the 9i database on the same server,

ORA-07445: exception encountered: core dump [kddlkr()+748] [SIGBUS] [unknown code] [0x000000010] [] []

Remarkable, two different homes, two different databases, two different application installs with the same error.

Search on metalink comes back with the dreaded:

A Description for this ORA-7445 error is not available.
Your request has been recorded and will be used for publishing prioritization.
Doing an Advanced Search on ORA-7445 'kddlkr' which may help to provide additional information on this error...

After about an hour of fruitless searching on metalink and trace file diving, I decided to call around to the users and see if anybody had received an error message. I found a very nice lady who had recently received an error in the application, she explained to me in great detail on exactly what she was doing to test the new version of the application. I was taken aback by her attention to detail and since she was just 2 floors up, I decided to visit her. She had almost 30 pages of checklists and notes that she had written over the years and the many different versions of this application of things she has to test every time a new version is released. She explained that this one module consistently gives an error when the user first opens the screen, and has done so for 3 years and 4 versions of the application, she had diligently written down the exact application error code in her notes for each of her testing sessions on the new applications all with the exact date and time noted.

I was properly impressed and told her so, I had her go into the module and show me the error, and sure enough, the error box pops up and exactly matches her hand written notes, it felt like the world had stopped spinning and I was wondering why I had never heard from this magnificent lady before, surely she was next in line for the "Power User" member of the application team. I asked her if the vendor or our local application support people had given her a work around or a valid excuse to this error. Her answer set the world spinning again after it's brief stoppage - her words "I have never told anybody about it, because it has always had an error since I started, I just tested to see if it had started to work with the new version, we don't even know what the module does."

So close, but yet so far. I will task the local application support people to investigate this error and open a ticket with the vendor.

Dizwell is gone

2006-12-29T11:12:00.000-08:00

I take a few months of reading off and Howard Rogers leaves in that time, talk about horrible timing on my part.

There is some discussion of the closing of the site here

http://oraclesponge.wordpress.com/2006/12/27/so-farewell-then-dizwell/

I wish Howard well, and hope to bump into him on-line.

Cheers Howard!

What happened to Dizwell?

2006-12-29T10:45:00.000-08:00

I grabbed a cup of tea, settled in for a good long read of Dizwell to catch up on what I missed... and its gone.

http://www.dizwell.com/

Comes back to

Welcome to dizwell.com !

This is a place-holder for the dizwell.com home page.

He can't be gone? can he?

Back (again)

2006-12-29T08:00:00.000-08:00

Well, after a lengthy period of time, I am back, and planning on blogging again. Normal things got in the way - like work, divestitures, purchases and mergers, and family life which caused blogging to be dropped right off of the to-do list.

I haven't even been reading or participating in any forums or other blogs until yesterday. So I am back and ready to roll.

So if there is still anybody out there reading this, thanks for sticking around and I promise some stuff soon.

Primary And Unique Keys

2006-11-01T07:27:00.000-08:00

Unique and Primary Constraints

There was some confusion on if a primary key constraint allowed multiple null values, or if a

unique constraint actually enforced the uniqueness of null values. So, here is the answer to those

questions.

Primary Keys

Let us start with primary key constraints. I will build a few objects to help us out.

SQL> CREATE SEQUENCE SOMETEST;

Sequence created.

SQL>

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER NOT NULL);

Table created.

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER PRIMARY KEY);

Table created.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER PRIMARY KEY NOT NULL);

Table created.

SQL> CREATE TABLE TEST4

2 (DATACOLUMN NUMBER);

Table created.

To standardize the testing data, here I will create a table and populate it so that we can use as a base

for the upcoming inserts. I will put in 5 rows of data at creation and two rows of null.

SQL> CREATE TABLE SOMEDATA AS SELECT SOMETEST.NEXTVAL DATACOLUMN FROM DUAL CONNECT BY

LEVEL <= 5;

Table created.

SQL> INSERT INTO SOMEDATA (DATACOLUMN) VALUES (NULL);

1 row created.

SQL> INSERT INTO SOMEDATA (DATACOLUMN) VALUES (NULL);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM SOMEDATA;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

7 rows selected.

You can see there are 7 rows of data, all unique except for two rows of null values.

First we will do a simple insert into from a full query of the SOMEDATA table.

SQL> INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST1"."DATACOLUMN")

The above results were pretty much expected, the column is marked as NOT NULL meaning of

course, no null values at all are allowed.

Now to try the same insert into the TEST2 table with the primary key.

SQL> INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST2"."DATACOLUMN")

So, a primary key is automatically NOT NULL. So when we created the table TEST3 we simply did

not need to add the NOT NULL parameter to the column. For completeness here is the same insert

carried out on the TEST3 table.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

No surprises there. The insert failed.

Now for TABLE4 the table was created with no constraints at all. So now if we do the insert, all 7

of the rows will go into the table.

SQL> INSERT INTO TEST4 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

SQL> COMMIT;

Commit complete.

If we are now to add a primary key constraint to the table

SQL>

SQL> ALTER TABLE TEST4 ADD CONSTRAINT TEST4PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

ERROR at line 3:

ORA-01449: column contains NULL values; cannot alter to NOT NULL

The database automatically tries to put a NOT NULL check constraint on the column, adding the

primary key fails.

If we try to add the primary key with the command option of NOVALIDATE, the table is altered

and the primary key is added.

1 ALTER TABLE TEST4 ADD CONSTRAINT TEST4PK

2 PRIMARY KEY (

3 DATACOLUMN

4* ) NOVALIDATE

SQL> /

Table altered.

After the primary key is on with the NOVALIDATE we still are unable to insert a null value into

TEST4.

SQL> INSERT INTO TEST4 (DATACOLUMN) VALUES (NULL);

INSERT INTO TEST4 (DATACOLUMN) VALUES (NULL)

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST4"."DATACOLUMN")

Even though TEST4 contains null values

1* SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST4

SQL> /

DATACOLUMN I

---------- -

1 N

2 N

3 N

4 N

5 N

7 rows selected.

That situation is one you should be aware of. Even though the primary key is in place, there is bad

data in the table that could throw a large wrench into a well running application.

Unique Keys

Now for unique keys, we will create the same base data table as for primary keys and create new

tables with testing unique keys.

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER);

Table created.

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER UNIQUE);

Table created.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER UNIQUE NOT NULL);

Table created.

Now for the inserts, first start with TEST1, no constraints at all

SQL> INSERT INTO TEST1 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

The TEST2 table has a unique constraint so most people will expect the insert to fail.

SQL> INSERT INTO TEST2 SELECT DATACOLUMN FROM SOMEDATA;

7 rows created.

But no, all 7 rows go in. Unique constraints do not count nulls when checking for uniqueness.

Now, TABLE3 has an unique key and has been set as NOT NULL

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

SQL> COMMIT;

Commit complete.

A quick couple of queries to verify the data

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST1;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

7 rows selected.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST2;

DATACOLUMN ISNULL

---------- ----------

1 N

2 N

3 N

4 N

5 N

7 rows selected.

SQL> SELECT DATACOLUMN,DECODE(DATACOLUMN,NULL,'Y','N') ISNULL FROM TEST3;

no rows selected

Constraint Objects

The objects that are created when a constraint is created are worthwhile mentioning as well. You

know what this means, time for more test objects, I can feel your joy at this prospect from here.

SQL> CREATE TABLE TEST1

2 (DATACOLUMN NUMBER PRIMARY KEY);

Table created.

SQL> desc test1

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NOT NULL NUMBER

Simple table you have seen before with the primary key defined at creation. The system will

automatically make up a name for the constraint and apply it to the table. If you try this on your

own, your constraint and index name will be different. You can create the constraints with

whatever name you choose, check the documentation on how to do that.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST1';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

SYS_C006436 P SYS_C006436

You can see, to efficiently check for violations of the constraint, the database also automatically

creates an index on the table. The INDEX_NAME column clearly shows this. A quick query into

the USER_INDEXES view will confirm this.

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST1';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

SYS_C006436 NORMAL UNIQUE

You will notice that the DATACOLUMN is marked as NOT NULL and there is a constraint on the

table saying so. Observe

SQL> CREATE TABLE TEST2

2 (DATACOLUMN NUMBER PRIMARY KEY NOT NULL);

Table created.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST2';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

SYS_C006437 C

SYS_C006438 P SYS_C006438

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST2';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

SYS_C006438 NORMAL UNIQUE

SQL> desc test2

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NOT NULL NUMBER

The database created a CHECK constraint called SYS_C006437 in this case on the table. One

reason this is done is to allow you to drop the primary key constraint and still keep the NOT NULL

constraint. A quick run of the DBMS_METADATA.GET_DDL function will show us what the

database did.

SQL> SELECT DBMS_METADATA.get_ddl('CONSTRAINT','SYS_C006437') FROM DUAL;

DBMS_METADATA.GET_DDL('CONSTRAINT','SYS_C006437')

--------------------------------------------------------------------------------

ALTER TABLE "DBTESTING"."TEST2" MODIFY ("DATACOLUMN" NOT NULL ENABLE)

Something to note is if there is already an index on the column and you add a constraint the

database will "hijack" that index instead of creating a new one. You can explicitly create an index

if you so desire.

SQL> CREATE TABLE TEST3

2 (DATACOLUMN NUMBER);

Table created.

SQL>

SQL> CREATE INDEX MYINDEX ON TEST3(DATACOLUMN);

Index created.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

SQL> DESC TEST3;

Name Null? Type

------------------------------------------------------- -------- ---------------

DATACOLUMN NUMBER

No constraints and only the one index. Add a primary constraint to the table.

SQL> ALTER TABLE TEST3 ADD CONSTRAINT TEST3_PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

Table altered.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

CONSTRAINT_NAME C INDEX_NAME

------------------------------ - ------------------------------

TEST3_PK P MYINDEX

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

SQL> DESC TEST3;

Name Null? Type

-------------------------------------------------------- -------- --------

DATACOLUMN NOT NULL NUMBER

You can see the index is marked as NONUNIQUE even though there is an obvious primary key on

the table. The index name of the primary key is MYINDEX the index we created on the table. We

can now attempt to insert some bad data into the table.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBTESTING"."TEST3"."DATACOLUMN")

So the NOT NULL is of course enforced.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL;

5 rows created.

SQL> INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL;

INSERT INTO TEST3 SELECT DATACOLUMN FROM SOMEDATA WHERE DATACOLUMN IS NOT NULL

ERROR at line 1:

ORA-00001: unique constraint (DBTESTING.TEST3_PK) violated

SQL> ROLLBACK;

Rollback complete.

The uniqueness of the primary key is enforced, even though as you can plainly see there is no

unique index on the table. When the constraint is dropped, the index is not dropped as well, but be

careful there is syntax in the drop constraint command to allow the index to be dropped at the same

time.

SQL> ALTER TABLE dbtesting.test3

2 DROP CONSTRAINT test3_pk

3 /

Table altered.

SQL>

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3';

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

MYINDEX NORMAL NONUNIQUE

The constraint is gone, the original index is still there, all is right in the world. Now, some very

thorough person or more importantly a very thorough GUI comes through with that handy right

click drop ability and the results are very disturbing.

SQL> ALTER TABLE TEST3 ADD CONSTRAINT TEST3_PK

2 PRIMARY KEY (

3 DATACOLUMN

4 )

5 /

Table altered.

SQL> ALTER TABLE dbtesting.test3

2 DROP CONSTRAINT test3_pk DROP INDEX

3 /

Table altered.

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME='TEST3'

;

no rows selected

SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='TEST3';

no rows selected

Conclusion

Every table should have a primary key it is plain good RDBMS design. Be aware of the fact the

database does give you the ability to bypass constraints but still make sure you use constraints in

your system, database referential integrity is the best, it is the fastest and no outside code can be as

efficient as the built in database code. Use the database, we are paying for it and why reinvent the

wheel when it rolls along so nice.

Wow!

2006-11-01T07:18:00.000-08:00

That is all I can say and not have content blocked.

The company decided to sell off a small portion to interested buyers. Seems easy enough, every thing is stored in orginisational units, we can shave off that portion, provide it in a easy to load format for the purchasers and we are ready to go, only 5 systems need to have data transferred.. The team of managers and "in the know" people put together to analyse what what was needed said "1 week to develop a plan, another week to extract the data, 1 week of quality control", 3 weeks of work and then all done. We were steaming along steadily well into week two with no major problems, when we started to get calls to our help desk, "Systems is hung", "I can't get logged in".

The help desk guys ran through their normal list of things to check, well, they tried to. Our knowledge base was down, that triggered pretty much every help desk person to pick up the phone and call somebody. Most of the non help desk IT people were in a meeting about the sale and didn't notice anything. It was very funny though, almost like you see in those movies where all of the stars are in a room and all of their cell phones start going off at once to say they had better do something before the world as they know it comes to an end. I was up at the front showing some interesting data I had found in the financials system that was not orginised properly but needed to be extracted when my blackberry started ringing. The IT managers blackberry was vibrating away on the table, and the two SA's cell phones beeping.

After only a few seconds, we all started to file out of the meeting room in a straight line for the server room. I was about 10 feet from the server room at the back of the line, when, wait for it. The fire alarm goes off, the main building fire alarm is whooping and ringing. The hallway to the server room has one of those red fire alarm bells in it. I don't think there was a dry pair of pants in that hallway when that bell went off, those things are freaking loud and the hallway has a door at one end from the lobby and the server room door on the other end, and nothing but concrete, linoleum and at this particular point in time 8 highly trained IT folks to absorb the sound. Despite the fire alarm going off, our manager had been opening the door to the server room and we continue into the server room with the screaming ringing racket of the fire bell going off about 8 feet behind us, as we all came into the server room we found out where the fire that triggered the fire alarm was- in our server room. There was a thick layer of smoke along the roof of the server room and the back of our our tape array rack was billowing smoke, the smell was almost enough to knock one out. Well, being the highly trained IT proffesionals we are, we were stepping on one another trying to get back out of the server room in a mess of arms and legs as 8 of us fit through the standard sized door pretty much at once.

After a very undignified exit into the lobby scaring the people in the lobby pretty much out of their wits we composed ourselves a bit, realized we were still alive and were moving quickly out of the building when one of the SA's spoke up and said "What about the fire suppression system in the room?". My answer, in a moment of pure brilliance at stating the obvious was "It isn't working". We loitered around outside waiting for the billowing smoke to consume the entire building, after what seemed like an hour the fire department showed up. It was really only 7 minutes from the time the alarm went off to the time the FD pulled up. The IT manager and the building manager spoke with the fireman in charge and explained where the fire was.

Us IT folks stood outside with the rest of the building population and waited and wondered. The two SA's were bickering back and forth on who's day it was to send the offsite tapes offsite and wondering if our DR site was in good enough shape to run the company while this place was rebuilt. After another 10 minutes or so the firemen came out and said it was just mostly smoke and they had put the fire out and after a few minutes we can go in and inspect what was up. The building manager would only let one SA and the IT manager into the room for insurance reasons. They didn't touch anything until they had taken about 3,000 pictures and the insurance company over the phone said we could do what was needed to get our business running again.

The Fire inspector figured out what the problem was, it was pretty obvious once you could see it. A power bar that comes built into the rack had ignited into a slow smoldering burn, causing all 8 power cords plugged into it to start to smolder and put off smoke too. It didn't aparranty get hot enough to trigger the fire suppression system in the room. It got plenty hot enough to melt all of the plastic off of all of the power cords, damage the rack and a fibre network hub thing too and generate vast quantities of smoke. All told, under $6,000 dollars damage. Not including the rooms new paint job, contractors to clean the room and the IT departments time to inspect all of the equipment since running it in a smokey environment is apparently bad for it.

The downtime was the remaining portion of the day it happened, and the entire following day but we were up and running at full capacity by 6am on day 3. One of the most junior help desk people we have, a great guy, summed up the entire thing into two words "Mother F***er!" when he was told what was going on. I will let you fill in the bleeped out section.

I promise to post some documentation on primary and unique keys I had been working on later this week. I also have a document on the pitfalls I have ran into using CURSOR_SHARING of SIMILAR or FORCE, but that is a week or two away.

Well even longer

2006-10-15T09:40:00.000-07:00

Still no posts technical or otherwise from me.

Sale is still ongoing and work is mounting. We had a person quit and another go on long term medical leave cutting our team from 5 to 3. That hurt the project and we are now scrambling for consultants who know our apps.

Good news is I got a new laptop, one of those new Dell duo core laptops, the 820 series, 4 gig of RAM and the 15.4 widescreen. It is a bit of a brick to pack around but the 3 hours of battery life and the fact I can put all copies of the databases we are extracting data from on my laptop now and work on the scripts and testing locally is wonderful.

Long Time

2006-09-28T11:19:00.000-07:00

It has been awhile, sorry to all of you.

We just have just sold a small portion of the company and to our surprise, since we haven't done it before, to carve off a section of the company is many times more work than it is to add to the company.

Which SCI FI character

2006-09-08T12:53:00.000-07:00

I did the survey and came up with Agent Smith from the Matrix series.
Tom Kyte came up with James T. Kirk.

The oracle sponge

2006-09-08T07:09:00.000-07:00

If you haven't you must read David Aldridge stories of his most recent holiday, you have to read all parts, this link is to part 1.

http://oraclesponge.wordpress.com/2006/09/05/three-days-two-hospitals-part-i/

Wonderfully well written, entertaining and enlightening. Being a ex-motorcycle enthusiast I know exactly what he is talking about.

Monitor Alert Log

2006-09-08T06:13:00.000-07:00

UNIX shell script to monitor and email errors found in the alert log. Is ran as the oracle OS owner. Make sure you change the "emailaddresshere" entries to the email you want and put the check_alert.awk someplace. I have chosen $HOME for this example, in real life I put it on as mounted directory on the NAS.


if test $# -lt 1
        then
 echo You must pass a SID
        exit 
 fi
#
# ensure environment variables set
#
#set your environment here
export ORACLE_SID=$1
export ORACLE_HOME=/home/oracle/orahome
export MACHINE=`hostname`
export PATH=$ORACLE_HOME/bin:$PATH

# check if the database is running, if not exit

ckdb ${ORACLE_SID} -s
if [ "$?" -ne 0 ]
then
  echo " $ORACLE_SID is not running!!!"
 echo "${ORACLE_SID is not running!" | mailx -m -s "Oracle sid ${ORACLE_SID} is not running!" "
|emailaddresshere|" 
  exit 1
fi;

#Search the alert log, and email all of the errors
#move the alert_log to a backup copy
#cat the existing alert_log onto the backup copy

#oracle 8 or higher DB's only.
sqlplus '/ as sysdba' << EOF > /tmp/${ORACLE_SID}_monitor_temp.txt
column xxxx format a10
column value format a80
set lines 132
SELECT 'xxxx' ,value FROM  v\$parameter WHERE  name = 'background_dump_dest'
/
exit
EOF


cat /tmp/${ORACLE_SID}_monitor_temp.txt | awk '$1 ~ /xxxx/ {print $2}' > /tmp/${ORACLE_SID}_monitor_location.txt
read ALERT_DIR < /tmp/${ORACLE_SID}_monitor_location.txt
ORIG_ALERT_LOG=${ALERT_DIR}/alert_${ORACLE_SID}.log
NEW_ALERT_LOG=${ORIG_ALERT_LOG}.monitored
TEMP_ALERT_LOG=${ORIG_ALERT_LOG}.temp
cat ${ORIG_ALERT_LOG} | awk -f $HOME/check_alert.awk > /tmp/${ORACLE_SID}_check_monitor_log.log
rm /tmp/${ORACLE_SID}_monitor_temp.txt 2>/dev/null
if [ -s /tmp/${ORACLE_SID}_check_monitor_log.log ]
   then 
     echo "Found errors in sid ${ORACLE_SID}, mailed errors"
     echo "The following errors were found in the alert log for ${ORACLE_SID}" > /tmp/${ORACLE_SID}_check_monitor_log.mail
     echo "Alert log was copied into ${NEW_ALERT_LOG}" >> /tmp/${ORACLE_SID}_check_monitor_log.mail
     echo " "
     date >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo "--------------------------------------------------------------">>/tmp/${ORACLE_SID}_check_monitor_log.mail
     echo " "
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     echo " " >> /tmp/${ORACLE_SID}_check_monitor_log.mail 
     cat /tmp/${ORACLE_SID}_check_monitor_log.log >>  /tmp/${ORACLE_SID}_check_monitor_log.mail

 cat /tmp/${ORACLE_SID}_check_monitor_log.mail | mailx -m -s "on ${MACHINE}, MONITOR of Alert Log for ${ORACLE_SID} found errors" "
|emailaddresshere|" 

     mv ${ORIG_ALERT_LOG} ${TEMP_ALERT_LOG}
     cat ${TEMP_ALERT_LOG} >> ${NEW_ALERT_LOG}
     touch ${ORIG_ALERT_LOG}
     rm /tmp/${ORACLE_SID}_monitor_temp.txt 2> /dev/null
     rm /tmp/${ORACLE_SID}_check_monitor_log.log 
     rm /tmp/${ORACLE_SID}_check_monitor_log.mail 
exit
fi;

rm /tmp/${ORACLE_SID}_check_monitor_log.log > /dev/null
rm /tmp/${ORACLE_SID}_monitor_location.txt > /dev/null

The referenced awk script (check_alert.awk). You can modify it as needed to add or remove things you wish to look for. The ERROR_AUDIT is a custom entry that a trigger on DB error writes in our environment.


$0 ~ /Errors in file/ {print $0}
$0 ~ /PMON: terminating instance due to error 600/ {print $0}
$0 ~ /Started recovery/{print $0}
$0 ~ /Archival required/{print $0}
$0 ~ /Instance terminated/ {print $0}
$0 ~ /Checkpoint not complete/ {print $0}
$1 ~ /ORA-/ { print $0; flag=1 }
$0 !~ /ORA-/ {if (flag==1){print $0; flag=0;print " "} }
$0 ~ /ERROR_AUDIT/ {print $0}

I simply put this script into cron to run every 5 minutes passing the SID of the DB I want to monitor.

SQL*plus in windows

2006-09-04T09:26:00.000-07:00

As you probably know from reading my blog, I don't like windows. I know how to use windows. I use windows, I have had the misfortune of administrating databases on windows, and most of our applications are made to run on windows. Windows runs the world from the everyday user.

I have windows on my laptop, but I have as little as possible running on windows and pretty much my first task in the morning is to startup my SUSE vm client on my laptop and work from there. This is of course resource intensive and with only having a D600 with the possibility of flaming batteries, resources are scarce. There are times on my 1.3ghz w/1 gig of ram laptop I am trying to run 3 oracle databases, 1 EE for windows, 1 EE for Suse in a VM client,and 1 SE for Suse in a VM client. When I do that, lets just say my laptop has difficulties keeping up with the simplest tasks, like keeping the clock in the taskbar up to date.

I was reading one of the threads going on flaming XE at Doug Burns blog -An Oracle XE user speaks and checked out the link from William Robertson. William Robertson is now my hero. I knew the capabilities existed, I had set up things like this for users. But I had never thought of doing something this simple for myself. I am talking about his wonderfully in depth but amazingly simple to do article on setting up SQL*plus on windows . I immediately went a head and configured up my windows sqlplus as he has explained.

Man... it is sometimes amazing how much the simple little things can make your life so much easier.

Oh, and for those of you waiting for an update. The fellows did their duty and dressed as described in earlier posts. I have requested pictures and I have been assured I will receive them shortly. I will of course post them as soon as I can. I was told, it was a banner day at the office, everybody that could be at the office was at the office.

Contest over

2006-08-25T06:10:00.000-07:00

Regarding I am in for it now the contest is over. Well, I won. The fine young gentlemen have chosen August 31st as the day they will live up to their end of the bargain. The final myth was explained to them with the following

SQL> SET TRIMSPOOL ON
SQL> SET LINES 120
SQL> DROP TABLE TABLE1;
DROP TABLE TABLE1
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> DROP SEQUENCE TABLE1_PK_SEQ;

Sequence dropped.

SQL> CREATE SEQUENCE TABLE1_PK_SEQ CACHE 500;

Sequence created.

SQL> CREATE TABLE TABLE1 AS SELECT OBJECT_NAME,LAST_DDL_TIME,FLOOR(DBMS_RANDOM.VALUE(1,2)) DATA FROM ALL_OBJECTS;

Table created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX TABLE1IDX1 ON TABLE1 (DATA);

Index created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE1',CASCADE =>TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SET TIMING ON;
SQL> SELECT /*+INDEX (TABLE1 TABLE1IDX1) */ * FROM TABLE1 WHERE DATA=1;

50574 rows selected.

Elapsed: 00:00:00.52

Execution Plan
----------------------------------------------------------
Plan hash value: 809506743

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 50574 |  1679K|   384   (2)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1     | 50574 |  1679K|   384   (2)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | TABLE1IDX1 | 50574 |       |   101   (2)| 00:00:02 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATA"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7106  consistent gets
          0  physical reads
          0  redo size
    2463263  bytes sent via SQL*Net to client
      37462  bytes received via SQL*Net from client
       3373  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50574  rows processed

SQL> SELECT * FROM TABLE1 WHERE DATA=1;

50574 rows selected.

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
Plan hash value: 963482612

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 50574 |  1679K|    68   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TABLE1 | 50574 |  1679K|    68   (3)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DATA"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3638  consistent gets
          0  physical reads
          0  redo size
    1548409  bytes sent via SQL*Net to client
      37462  bytes received via SQL*Net from client
       3373  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50574  rows processed

SQL> 
SQL> SPOOL OFF

They capitulated with almost no fight, they had run similar tests and scripts and had gone through the online information and come up with the same answers. I do feel for the fellows... well just a little :)

Myth #3

2006-08-19T17:57:00.000-07:00

I know I am going about working on the myth's my colleagues came up with in a strange order. I was working on myth #3 and was once again stumped.

No matter what I did, it appeared that using a varchar date field or a function based index field was preferable to a normal B*tree index on a normal heap table.

For my testing I had come up with the following test:


SQL> CREATE TABLE TABLE1 (PK NUMBER PRIMARY KEY NOT NULL
,DATA1 VARCHAR2(30) ,DATA2 VARCHAR2(30) 
,DATA3 DATE,DATA4 NUMBER
,FAKEDATE VARCHAR2(20));

Table created.

SQL> DROP SEQUENCE TABLE1_PK_SEQ;

Sequence dropped.

SQL> CREATE SEQUENCE TABLE1_PK_SEQ CACHE 500;

Sequence created.

SQL> INSERT INTO TABLE1 SELECT TABLE1_PK_SEQ.NEXTVAL
  2    ,DBMS_RANDOM.STRING('A',30),DBMS_RANDOM.STRING('A',30)
  3    ,SYSDATE - DBMS_RANDOM.VALUE(1,365) ,TRUNC(DBMS_RANDOM.VALUE(1,10000)),NULL
  4    FROM DUAL CONNECT BY LEVEL <=  50000;

50000 rows created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TABLE1 SET FAKEDATE=TO_CHAR(DATA3,'DD-MON-YYYY');

50000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX DATEIDX ON TABLE1 (DATA3);

Index created.

SQL> CREATE INDEX FAKEDATEIDX ON TABLE1 (FAKEDATE);

Index created.

SQL> CREATE INDEX FBIIDX ON TABLE1 (TO_CHAR(DATA3,'DD-MON-YYYY'));

Index created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE1',CASCADE =>TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

The results of the queries were unsettling to say the least. I have edited the following to try and shorten up the post a little.
I will put a link to the full output files at the end of the post.


SQL> SELECT * FROM TABLE1 WHERE DATA3 >= TO_DATE('24-JUN-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
  2    AND DATA3 <= TO_DATE('24-JUN-2006 23:59:59','DD-MON-YYYY HH24:MI:SS');

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   139 | 12649 |   141   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TABLE1 |   139 | 12649 |   141   (3)| 00:00:02 |
----------------------------------------------------------------------------


SQL> SELECT /*+ INDEX(TABLE1 DATEIDX) */ * FROM TABLE1 WHERE DATA3 >= TO_DATE('24-JUN-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
  2    AND DATA3 <= TO_DATE('24-JUN-2006 23:59:59','DD-MON-YYYY HH24:MI:SS');
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   139 | 12649 |   142   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1  |   139 | 12649 |   142   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | DATEIDX |   139 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


SQL> SELECT * FROM TABLE1 WHERE FAKEDATE='24-JUN-2006';
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   137 | 12467 |   123   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1      |   137 | 12467 |   123   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | FAKEDATEIDX |   137 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


SQL> SELECT * FROM TABLE1 WHERE TO_CHAR(DATA3,'DD-MON-YYYY')='24-JUN-2006';
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   137 | 12467 |   123   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1 |   137 | 12467 |   123   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | FBIIDX |   137 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

I scratched my head, thought about this, researched on the web a little bit. Even considered submitting a question to asktom. Then I thought. Data sizes, that has to be it. So I made the table bigger, 3,000,000 rows to be exact but I came up with the same access paths. Well I thought, they won this one... but it doesn't make sense. Why isn't oracle considering a index. I checked for bugs, I couldn't find any. Then I went back to data sizes. I decided to create a "wider" table and try it again. Now we are talking.


SQL> CREATE TABLE TABLE1 (PK NUMBER PRIMARY KEY NOT NULL
  2  ,DATA1 VARCHAR2(30)
  3  ,DATA2 VARCHAR2(30)
  4  ,DATA3 DATE
  5  ,DATA4 NUMBER
  6  ,FAKEDATE VARCHAR2(20)
  7  ,DATA5 VARCHAR2(30)
  8  ,DATA6 VARCHAR2(30)
  9  ,DATA7 VARCHAR2(30)
 10  ,DATA8 VARCHAR2(30)
 11  );

Table created.

SQL> DROP SEQUENCE TABLE1_PK_SEQ;

Sequence dropped.

SQL> CREATE SEQUENCE TABLE1_PK_SEQ CACHE 500;

Sequence created.

SQL> INSERT INTO TABLE1 SELECT TABLE1_PK_SEQ.NEXTVAL,DBMS_RANDOM.STRING('A',30),DBMS_RANDOM.STRING('A',30)
  2    ,SYSDATE - DBMS_RANDOM.VALUE(1,365) ,TRUNC(DBMS_RANDOM.VALUE(1,10000)),NULL,DBMS_RANDOM.STRING('A',30)
  3    ,DBMS_RANDOM.STRING('A',30),DBMS_RANDOM.STRING('A',30),DBMS_RANDOM.STRING('A',30)
  4  FROM DUAL CONNECT BY LEVEL <=  50000;

50000 rows created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE TABLE1 SET FAKEDATE=TO_CHAR(DATA3,'DD-MON-YYYY');

50000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX DATEIDX ON TABLE1 (DATA3);

Index created.

SQL> CREATE INDEX FAKEDATEIDX ON TABLE1 (FAKEDATE);

Index created.

SQL> CREATE INDEX FBIIDX ON TABLE1 (TO_CHAR(DATA3,'DD-MON-YYYY'));

Index created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE1',CASCADE =>TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

The access paths now look more like what I was thinking they would be:



SQL> SELECT * FROM TABLE1 WHERE DATA3 >= TO_DATE('24-JUN-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
  2    AND DATA3 <= TO_DATE('24-JUN-2006 23:59:59','DD-MON-YYYY HH24:MI:SS');

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   139 | 29885 |   142   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1  |   139 | 29885 |   142   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | DATEIDX |   139 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> SELECT * FROM TABLE1 WHERE FAKEDATE='24-JUN-2006';

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   137 | 29455 |   132   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1      |   137 | 29455 |   132   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | FAKEDATEIDX |   137 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

SQL> SELECT * FROM TABLE1 WHERE TO_CHAR(DATA3,'DD-MON-YYYY')='24-JUN-2006';

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   137 | 29455 |   132   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE1 |   137 | 29455 |   132   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | FBIIDX |   137 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Apparently the fake varchar2 column or the FBI is more efficient to pick out individual days with. I am going to expand this test to do entire months, just a small period of time, a year to see if a single normal b*tree index on a date is the best solution when you are not targeting such a specific predicate.

So now a question to you readers, did I do anything wrong?. Can you point me in the direction of some documents on indexing dates that I must have missed or slept through that day in class?

The links to the "skinny table" and the "wide table" complete tests and logfiles.

Proving them wrong 1 of 3

2006-08-13T12:27:00.000-07:00

From my earlier post "I am in for it now" I started working on myth #2 as it seemed to be the easiest to debunk.

I ran into a snag though. I had put together my test for them, easily proving that their thought that if you index every column in a table the optimizer will join multiple indexes together and follow that access path to the data. I was in for a bit of a surprise. I had heard of the optimizer building bitmap indexes on the fly but I had never delved any deeper into it and had truthfully forgotten all about it until today. I can see the Jonathon Lewis's book "Cost Based Oracle: Fundamentals" is going to cracked open again and given a very thorough going over.

The whole story, I put together the following test SQL:


SPOOL RUN1.LOG
SET ECHO ON
SET LINES 120
COLUMN NAME FORMAT A30 TRUNCATE
COLUMN VALUE FORMAT A30 TRUNCATE
DROP TABLE A;
CREATE TABLE A
(
PK NUMBER PRIMARY KEY NOT NULL
,DATA1 VARCHAR2(20)
,DATA2 VARCHAR2(20)
,DATA3 VARCHAR2(20)
,DATA4 DATE
);
DROP SEQUENCE PK_SEQ;
CREATE SEQUENCE PK_SEQ CACHE 500; 
CREATE INDEX IDX1 ON A (DATA1);
CREATE INDEX IDX2 ON A (DATA2);
CREATE INDEX IDX3 ON A (DATA3);
CREATE INDEX IDX4 ON A (DATA4);
INSERT INTO A
(PK,DATA1,DATA2,DATA3,DATA4)
SELECT 
PK_SEQ.NEXTVAL PK
,OBJECT_TYPE DATA1
,STATUS DATA2
,DBMS_RANDOM.STRING('A',2) DATA3
,SYSDATE-DBMS_RANDOM.VALUE(1,50) DATA4 
FROM ALL_OBJECTS;
COMMIT;
SELECT NAME,VALUE FROM V$PARAMETER WHERE UPPER(NAME)='OPTIMIZER_MODE';
SELECT * FROM V$VERSION;
SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
SET TIMING ON
SELECT * FROM A WHERE PK=12;
SELECT * FROM A WHERE PK=53 AND DATA2='BDSA';
SELECT * FROM A WHERE DATA1='SDD' AND DATA2='DKDK' AND DATA3='433';
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'A',CASCADE=>TRUE);
END;
/
SELECT * FROM A WHERE PK=12;
SELECT * FROM A WHERE PK=53 AND DATA2='BDSA';
SELECT * FROM A WHERE DATA1='SDD' AND DATA2='DKDK' AND DATA3='433';

The explain plans via autotrace from the 3 queries after statistics were gathered:


| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                             
-------------------------------------------------------------------------------------------                             
|   0 | SELECT STATEMENT            |             |     1 |    30 |     2   (0)| 00:00:01 |                             
|   1 |  TABLE ACCESS BY INDEX ROWID| A           |     1 |    30 |     2   (0)| 00:00:01 |                             
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007595 |     1 |       |     1   (0)| 00:00:01 |                             

------------------------------------------------------------------------------------                                    
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                    
------------------------------------------------------------------------------------                                    
|   0 | SELECT STATEMENT            |      |     1 |    30 |     2   (0)| 00:00:01 |                                    
|*  1 |  TABLE ACCESS BY INDEX ROWID| A    |     1 |    30 |     2   (0)| 00:00:01 |                                    
|*  2 |   INDEX RANGE SCAN          | IDX2 |     1 |       |     1   (0)| 00:00:01 |                                    
------------------------------------------------------------------------------------                                    

------------------------------------------------------------------------------------                                    
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                    
------------------------------------------------------------------------------------                                    
|   0 | SELECT STATEMENT            |      |     1 |    30 |     2   (0)| 00:00:01 |                                    
|*  1 |  TABLE ACCESS BY INDEX ROWID| A    |     1 |    30 |     2   (0)| 00:00:01 |                                    
|*  2 |   INDEX RANGE SCAN          | IDX2 |     1 |       |     1   (0)| 00:00:01 |                                    
------------------------------------------------------------------------------------

Those explains plans where exactly what I was looking for.

The snag was encountered when I decided that I harp on all of the developers to bind their SQL, and in this case even though binding was not necessary, the two developers would inflate any little oversight on my part to earth shattering levels.

So I set up variables and bound my SQL and reran my test. I had surprising results. I see multiple indexes being used on a single table - Bitmap indexes of all things.

I rebuilt my test creating a new table "B" as a copy of the previous "A" Here is the complete SQL:


SPOOL RUNb.LOG
SET ECHO ON
SET LINES 120
COLUMN NAME FORMAT A30 TRUNCATE
COLUMN VALUE FORMAT A30 TRUNCATE
SELECT NAME,VALUE FROM V$PARAMETER WHERE UPPER(NAME)='OPTIMIZER_MODE';
SELECT * FROM V$VERSION;
SET AUTOTRACE TRACEONLY EXPLAIN
SET TIMING ON
VARIABLE BPK NUMBER;
VARIABLE BD1 VARCHAR2(20);
VARIABLE BD2 VARCHAR2(20);
VARIABLE BD3 VARCHAR2(20);
DROP TABLE B;
CREATE TABLE B AS SELECT * FROM A;
ALTER TABLE B ADD CHECK ("PK" IS NOT NULL);
ALTER TABLE B ADD PRIMARY KEY (pk) USING INDEX;
CREATE INDEX BIDX1 ON B (DATA1);
CREATE INDEX BIDX2 ON B (DATA2);
CREATE INDEX BIDX3 ON B (DATA3);
CREATE INDEX BIDX4 ON B (DATA4);
SELECT * FROM B WHERE PK=12;
SELECT * FROM B WHERE PK=53 AND DATA2='BDSA';
SELECT * FROM B WHERE DATA1='SDD' AND DATA2='DKDK' AND DATA3='433';
EXEC :BPK := 99;
EXEC :BD1 := 'BFSS';
EXEC :BD2 := 'JGJD';
EXEC :BD3 := 'JJGJG';
SELECT * FROM B WHERE PK=:BPK;
SELECT * FROM B WHERE PK=:BPK AND DATA2=:BD2;
SELECT * FROM B WHERE DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;
SELECT * FROM B WHERE PK=:BPK AND DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'B',CASCADE=>TRUE);
END;
/
SELECT * FROM B WHERE PK=:BPK;
SELECT * FROM B WHERE PK=:BPK AND DATA2=:BD2;
SELECT * FROM B WHERE DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;
SELECT * FROM B WHERE PK=:BPK AND DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;
SPOOL OFF
EXIT

The output for the following query really did take me by surprise, this was the query after the analyze of "B":


SELECT * FROM B WHERE DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;

Here is the plan:


Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 849905550                                                                                              
                                                                                                                        
------------------------------------------------------------------------------------------                              
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                 |       |     1 |    30 |     7   (0)| 00:00:01 |                              
|*  1 |  TABLE ACCESS BY INDEX ROWID     | B     |     1 |    30 |     7   (0)| 00:00:01 |                              
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |          |                              
|   3 |    BITMAP AND                    |       |       |       |            |          |                              
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |                              
|*  5 |      INDEX RANGE SCAN            | BIDX3 |    19 |       |     1   (0)| 00:00:01 |                              
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |                              
|*  7 |      INDEX RANGE SCAN            | BIDX1 |    19 |       |     6   (0)| 00:00:01 |                              
------------------------------------------------------------------------------------------                              
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   1 - filter("DATA2"=:BD2)                                                                                             
   5 - access("DATA3"=:BD3)                                                                                             
   7 - access("DATA1"=:BD1)

Multiple indexes (BIDX3 and BIDX1) on the same table in one query, surprising to say the least.
This particular part of the challenge may end up being a tie.

So I do some more testing


SQL> column PLAN_TABLE_OUTPUT format a120 truncate
SQL> explain plan for SELECT * FROM B WHERE DATA1=:BD1 AND DATA2=:BD2 AND DATA3=:BD3;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 849905550                                                                                              
                                                                                                                        
------------------------------------------------------------------------------------------                              
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
------------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                 |       |     1 |    30 |     7   (0)| 00:00:01 |                              
|*  1 |  TABLE ACCESS BY INDEX ROWID     | B     |     1 |    30 |     7   (0)| 00:00:01 |                              
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |          |                              
|   3 |    BITMAP AND                    |       |       |       |            |          |                              
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |                              
|*  5 |      INDEX RANGE SCAN            | BIDX3 |    19 |       |     1   (0)| 00:00:01 |                              
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |                              
|*  7 |      INDEX RANGE SCAN            | BIDX1 |    19 |       |     6   (0)| 00:00:01 |                              
------------------------------------------------------------------------------------------                              
                                                                                                                        
Predicate Information (identified by operation id):                                                                     
---------------------------------------------------                                                                     
                                                                                                                        
   1 - filter("DATA2"=:BD2)                                                                                             
   5 - access("DATA3"=:BD3)                                                                                             
   7 - access("DATA1"=:BD1)                                                                                             

21 rows selected.

SQL> 
PLAN_TABLE_OUTPUT                                                                                                       
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2496799660                                                                                             
                                                                                                                        
-------------------------------------------------------------------------------------                                   
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                   
-------------------------------------------------------------------------------------                                   
|   0 | SELECT STATEMENT            |       |     1 |    30 |     2   (0)| 00:00:01 |                                   
|*  1 |  TABLE ACCESS BY INDEX ROWID| B     |     1 |    30 |     2   (0)| 00:00:01 |                                   
|*  2 |   INDEX RANGE SCAN          | BIDX2 |     1 |       |     1   (0)| 00:00:01 |                                   
-------------------------------------------------------------------------------------                                   
                                                                                                                        
Predicate Information (identified by operation id):                                                                     

PLAN_TABLE_OUTPUT                                                                                                       
----------------------------------------------------------------------------------------

   1 - filter("DATA1"='DFDF' AND "DATA3"='KDWD')                                                                        
   2 - access("DATA2"='SDFVS')                                                                                          

15 rows selected.

SQL> 
SQL> 
SQL> spool off

Only does the bitmap conversion when bind variables are used. A case of bad "bind variable peeking" here I think.

Multiple Oracle clients

2006-08-11T11:23:00.000-07:00

Interesting few phone calls this morning, the back story:

We upgraded a database from 9.2.0.5 to 10gr1.

The core group of users tested it thoroughly with no problems, everything was signed off on, the upgrade went with a hitch and everything worked great. For less than 1 business day.

The system has an export facility to the local government body of data we collect. The GUI creates an XML file that the users then upload to the government via a web site. Some users started complaining of getting ORA-0600 errors when they try to create their XML file. Other users were not having any problems at all. This had been tested and signed off on. The application support people had tried and could not find a problem and they could successfully create the files for the users. I started going through metalink trying to find any related items to the upgrade that we might have missed. I could find nothing, but I stumbled across a similar ORA-0600 error 16608 with a oracle 8 client trying to connect to a oracle 10g database. I didn't think that was applicable, but I looked into it anyway. Every single person getting the problem has the oracle 817 client installed because they use an older application that requires it. The 10g application GUI defaults to this home. After working with the vendor, we could not force the GUI to use the oracle 9i home on the PC's. We installed the instant client, to no avail. The application works great with the 9i client so we had decided to slowly install the 10g client as it was needed.

I read through forum entries and the like on using multiple oracle clients on the same PC and none of the suggestions worked. If I removed the Oracle 8 settings from the path, the 10g application worked, but the 8i application did not work. If I moved the oracle 8 settings to the end of the path, the 10g application worked, but the oracle 8i application did not.

I did some head scratching, and came up with the following for starting the 10g application.

set ORACLE_HOME=c:\oracle\ora92 set TNS_ADMIN=c:\oracle\ora92\network\admin set path=c:\oracle\ora92 call "c:\program files\10gapp\10gapp.exe"

That works very well.

If anybody out there has a better way, please comment as this is now in use on about 100 PC's throughout the company.

I am in for it now

2006-08-10T19:46:00.000-07:00

I have been having, "heated discussions" with a few (male, read on) OCP's from a subsidiary company. Arguing over some Oracle B*tree index myths and other things they "know" about oracle. These fellows are 10g OCP, but all straight out of university and very gung ho. ADF over oracle, .NET over oracle (shudder) and no need for any business logic in the database. Very smart and knowledgeable fellows who are an asset to the company. I just keep slamming them on their database interaction work as I do the performance monitoring on their databases and they are a little sick and tired of ticket after ticket after ticket coming their way of "Bad SQL" or "Inefficient SQL" and the one that started this "Unused Indexes" and thought they would speak up. All very friendly of course.

We came to a agreement that I would put together a nice document blowing away their index theories. I will work on the rest of their theories later. When I have successfully debunked their myths, they will wear skirts,bobby socks, a blouse and makeup to work for a period of no less than 4 straight hours covering at minimum of two of the following - Morning arrival,Morning coffee break, lunch, afternoon coffee break or 5pm departure. If I fail, I will have to wear the same outfit. We have manager approval and backing for this and odds are it will end up in the company newsletter. Not entirely fair as they have 9-12 people at their location. I have 200+ people in this office, but I think I have the legs to pull it off :).

I personally believe these to be myths, well one because Thomas Kyte says so, and two I did some testing and checked it out in the past.

The following are the 3 index myths we decided on, well statements I told them were wrong and they argued the point. They are going to put together tests to prove me wrong. In a event of a tie or unbreakable test cases, a simple toss of a coin will decide our fates. Best out of 3 wins.

Myth #1

You must use a index, full tablescans are always less efficient and slower.

Myth #2

If you index each individual column the optimizer will use multiple indexes in a query if the predicate has any combination of the columns, so build a index on every column.

Myth #3

You must build a calculated column to properly index a date field. So to store a date, the best way to do it is with a VARCHAR field to improve DML to the table.

Over the next few days, I will be putting together the tests and posting the results. Since we never spoke about the ability to look for outside corroboration or help, I thought I would do the tests, and then run them by my blog to see if any readers can find fault in them. Due date for the tests is the end of the month. Management didn't want us using too much company time for this childishness. I know I have 2 and 3 in the bag.. #1 is going to be hard to prove, I think in one of Tom's books he does something similar. I will be reading those chapters over again tonight.

Parameters are Oracle 10gR2 on windows (shudder) with the following somewhat abbreviated init.ora (some parameters removed):

orcl.__db_cache_size=192937984 orcl.__java_pool_size=12582912 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=75497472 orcl.__streams_pool_size=0 *.compatible='10.2.0.1.0' *.db_block_size=8192 *.optimizer_mode=ALL_ROWS *.db_file_multiblock_read_count=16 *.db_recovery_file_dest_size=2147483648 *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=96468992 *.processes=150 *.sga_target=289406976 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'

I am probably going to regret this is some way. But WTF you only live once. I think my wife is still laughing, she offered to shave my legs for me. Evil, Evil lady :)

Stay tuned, I am going to spend some time on building these tests.

Statspack

2006-08-09T19:55:00.000-07:00

This entry is more so I have a easy to reference link for answering "how do I monitor my DB" questions that always crop up.

Using and installing

statspack for oracle9i release 2

statspack for oracle10g Now called AWR but the statspack stuff is still there and is included, AWR is a extra cost option I believe.

I can't find any online documentation for installing statspack in 8i.

But in 8.1.6+ if you read $ORACLE_HOME/rdbms/admin/spdoc.txt it tells you pretty much everything you need to know about statspack.

The spdoc.txt script exists in all versions at the same location in the oracle home.

If you don't use statspack - learn it and use it.

Over at the Pythian groupShervin Sheidaei has started a thread on a New STATSPACK Methodology It looks promising, he says he has more instore for us.

Coworker

2006-08-06T13:30:00.000-07:00

One of our developers is moving on. He is pursuing a dream, he got a job with a company in the UK. He and his wife had been wishing to take the family to the UK for a visit, but this job presented itself and he grabbed at it. So off he and his entire family go.

The entire company will miss him, he may not have been the team leader, but he was the center of this particular applications development and support team and many times more useful than the team leader. Already the impact of his two weeks notice is being felt throughout the company. He is a University trained developer who actually learned what he was taught and who has specialized in one application and has become one of the best in his field of expertise.

I wish him and his family good luck and safe travels.

As you may have noticed, my job in France didn't go through, it was just too expensive for the company to move me from northern Canada to southern France. They went with somebody from the UK.

I have decided to start to actively look for work, I think I will look in places like the UK, Australia, New Zealand and Western Europe. If I am going to move I will leave Canada and I can't stand the United States, so I have limited locations I can find work where it isn't under active air raids or a crumbling government.

Retrieving disk space

2006-08-05T15:06:00.000-07:00

There are always questions from folks asking how to retrieve disk space from database datafiles that have grown too large usually by accident.

This can be a time consuming task, usually involving using import and export and can lead to large headaches. For the really large jobs, that is probably still the best way to go. For the smaller jobs I use the following method.

I put it together into a hopefully complete test. Sorry for the use of a Windoze DB as a test bed. But VM on my laptop took a nose dive the other day and I haven't been able to recover my Suse VM guest from the bowels of WindowsXP.


SQL> SET TIMING ON
SQL> COLUMN MEG FORMAT 999,999,999
SQL> COLUMN SEGMENT_NAME FORMAT A25 TRUNCATE
SQL> COLUMN FILE_NAME FORMAT A30 TRUNCATE
SQL> COLUMN TABLESPACE_NAME FORMAT A25 TRUNCATE
SQL> SET LINES 130
SQL> set echo on
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> DROP TABLESPACE SIZETESTING1 INCLUDING CONTENTS;

Tablespace dropped.

Elapsed: 00:00:02.15
SQL> CREATE TABLESPACE SIZETESTING1 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
  2  SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M SEGMENT SPACE MANAGEMENT AUTO AUTOALLOCATE;

Tablespace created.

Elapsed: 00:00:04.34
SQL> CREATE TABLE THEBIGONE (THEDATA VARCHAR2(3000)) TABLESPACE SIZETESTING1;

Table created.

Elapsed: 00:00:00.03
SQL> CREATE TABLE THESMALLONE (THEDATA VARCHAR2(3000)) TABLESPACE SIZETESTING1;

Table created.

Elapsed: 00:00:00.00

I just want to put some data into the big table then insert some data into the small table.


SQL> 
SQL> 
SQL> DECLARE
  2  L_THERECORD VARCHAR2(3000);
  3  
  4  BEGIN
  5  
  6  L_THERECORD := DBMS_RANDOM.STRING('U',2999);
  7  DBMS_OUTPUT.PUT_LINE(L_THERECORD);
  8  FOR Y IN 1..3 LOOP
  9  
 10  FOR X IN 1..10000 LOOP
 11  INSERT INTO THEBIGONE (THEDATA) VALUES (L_THERECORD);
 12  END LOOP;
 13  COMMIT;
 14  END LOOP;
 15  COMMIT;
 16  END;
 17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.63
SQL> 
SQL> SELECT COUNT(1) FROM THEBIGONE;

  COUNT(1)                                                                                                                        
----------                                                                                                                        
     30000                                                                                                                        

Elapsed: 00:00:03.62
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;

SEGMENT_NAME                       MEG                                                                                            
------------------------- ------------                                                                                            
THESMALLONE                          0                                                                                            
THEBIGONE                          120                                                                                            

Elapsed: 00:00:00.14
SQL> 
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;

49787 rows created.

Elapsed: 00:00:03.39
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> 
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;

SEGMENT_NAME                       MEG                                                                                            
------------------------- ------------                                                                                            
THESMALLONE                          2                                                                                            
THEBIGONE                          120                                                                                            

Elapsed: 00:00:00.03
SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';

FILE_NAME                               MEG                                                                                       
------------------------------ ------------                                                                                       
C:\ORACLE\PRODUCT\10.2.0\ORADA          130                                                                                       

Elapsed: 00:00:00.05

Now, lets fill the big table up so it blows the storage parameters.


SQL> 
SQL> DECLARE
  2  L_THERECORD VARCHAR2(3000);
  3  
  4  BEGIN
  5  
  6  L_THERECORD := DBMS_RANDOM.STRING('U',2999);
  7  DBMS_OUTPUT.PUT_LINE(L_THERECORD);
  8  FOR Y IN 1..1000 LOOP
  9  
 10  FOR X IN 1..1000 LOOP
 11  INSERT INTO THEBIGONE (THEDATA) VALUES (L_THERECORD);
 12  END LOOP;
 13  COMMIT;
 14  END LOOP;
 15  COMMIT;
 16  END;
 17  /
DECLARE
*
ERROR at line 1:
ORA-01653: unable to extend table SYSTEM.THEBIGONE by 1024 in tablespace SIZETESTING1 
ORA-06512: at line 11 


Elapsed: 00:00:08.76
SQL> 
SQL> SELECT COUNT(1) FROM THEBIGONE;

  COUNT(1)                                                                                                                        
----------                                                                                                                        
     48000                                                                                                                        

Elapsed: 00:00:04.35
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;

SEGMENT_NAME                       MEG                                                                                            
------------------------- ------------                                                                                            
THESMALLONE                          2                                                                                            
THEBIGONE                          192                                                                                            

Elapsed: 00:00:00.26
SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';

FILE_NAME                               MEG                                                                                       
------------------------------ ------------                                                                                       
C:\ORACLE\PRODUCT\10.2.0\ORADA          200                                                                                       

Elapsed: 00:00:00.03

Lets get rid of the big table and add some more rows to the small table.




SQL> TRUNCATE TABLE THEBIGONE;

Table truncated.

Elapsed: 00:00:03.16
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;

49787 rows created.

Elapsed: 00:00:03.13
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.01
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;

49787 rows created.

Elapsed: 00:00:03.11
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.02
SQL> INSERT INTO THESMALLONE SELECT OBJECT_NAME FROM ALL_OBJECTS;

49787 rows created.

Elapsed: 00:00:03.19
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.02
SQL> SELECT SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY SEGMENT_NAME;

SEGMENT_NAME                       MEG                                                                                            
------------------------- ------------                                                                                            
THESMALLONE                          7                                                                                            
THEBIGONE                            0                                                                                            

Elapsed: 00:00:00.03
SQL> 
SQL> SELECT * FROM( SELECT file_name,     ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) smallest,    ceil( blocks*C.VALUE/1024/1024) currsize,
  2       ceil( blocks*C.VALUE/1024/1024) -     ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) savings
  3       ,a.autoextensible  ,a.maxbytes from dba_data_files a,      ( select file_id, max(block_id+blocks-1) hwm
  4         from dba_extents     group by file_id ) b    ,(select value from v$parameter where name = 'db_block_size') C
  5  where a.file_id = b.file_id(+) AND a.file_name='C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
  6  ) WHERE 1=1 ;

FILE_NAME                        SMALLEST   CURRSIZE    SAVINGS AUT   MAXBYTES                                                    
------------------------------ ---------- ---------- ---------- --- ----------                                                    
C:\ORACLE\PRODUCT\10.2.0\ORADA        123        200         77 YES  209715200                                                    

Elapsed: 00:00:16.80

Our smallest we can shrink the datafile is down to 123m due to the small table having segments. So lets set up a tablespace to move the small table too while we clean up.



SQL> 
SQL> 
SQL> DROP TABLESPACE HOLDING INCLUDING CONTENTS;

Tablespace dropped.

Elapsed: 00:00:00.88
SQL> CREATE TABLESPACE HOLDING DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\HOLDING.DBF'
  2  SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M SEGMENT SPACE MANAGEMENT AUTO AUTOALLOCATE;

Tablespace created.

Elapsed: 00:00:01.10

And lets move it.




SQL> 
SQL> ALTER TABLE THESMALLONE MOVE TABLESPACE HOLDING;

Table altered.

Elapsed: 00:00:00.67
SQL> 
SQL> SELECT * FROM( SELECT file_name,     ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) smallest,    ceil( blocks*C.VALUE/1024/1024) currsize,
  2       ceil( blocks*C.VALUE/1024/1024) -     ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) savings
  3       ,a.autoextensible  ,a.maxbytes from dba_data_files a,      ( select file_id, max(block_id+blocks-1) hwm
  4         from dba_extents     group by file_id ) b    ,(select value from v$parameter where name = 'db_block_size') C
  5  where a.file_id = b.file_id(+) AND a.file_name='C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF'
  6  ) WHERE 1=1 ;

FILE_NAME                        SMALLEST   CURRSIZE    SAVINGS AUT   MAXBYTES                                                    
------------------------------ ---------- ---------- ---------- --- ----------                                                    
C:\ORACLE\PRODUCT\10.2.0\ORADA          1        200        199 YES  209715200                                                    

Elapsed: 00:00:16.68

There we go, shrink it down to 1M. So, lets do it.




SQL> ALTER database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LAPTOP\SIZETESTING1.DBF' resize 1m;

Database altered.

Elapsed: 00:00:00.49

Move the table back .



SQL> ALTER TABLE THESMALLONE MOVE TABLESPACE SIZETESTING1;

Table altered.

Elapsed: 00:00:01.29
SQL> SELECT TABLESPACE_NAME,SEGMENT_NAME,SUM(BYTES/1024/1024) MEG FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('THEBIGONE','THESMALLONE') GROUP BY TABLESPACE_NAME,SEGMENT_NAME;

TABLESPACE_NAME           SEGMENT_NAME                       MEG                                                                  
------------------------- ------------------------- ------------                                                                  
SIZETESTING1              THEBIGONE                            0                                                                  
SIZETESTING1              THESMALLONE                          7                                                                  

Elapsed: 00:00:00.03

SQL> SELECT FILE_NAME,BYTES/1024/1024 MEG FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SIZETESTING1';

FILE_NAME                             MEG
------------------------------ ----------
C:\ORACLE\PRODUCT\10.2.0\ORADA         11

Elapsed: 00:00:00.03

SQL> SPOOL OFF;

Lets take a look at what it looks like on disk



C:\oracle\product\10.2.0\oradata\LAPTOP>DIR SIZE*
 Volume in drive C has no label.
 
 Directory of C:\oracle\product\10.2.0\oradata\LAPTOP

08/05/2006  02:46 PM        11,542,528 SIZETESTING1.DBF
               1 File(s)     11,542,528 bytes
               0 Dir(s)  85,625,917,440 bytes free

This works well and can be expanded to cover more tables similar to "THESMALLONE". Be careful moving tables online and check the documents first, and don't take my word for it. Test Test Test.

Writing an external audit file

2006-08-05T09:36:00.000-07:00

Awhile back I answered a question from somebody I believe on Dizwell on how to write a custom trigger that would write a record to an external datafile with every insert. I stumbled across the script here and thought I would post it.



CREATE OR REPLACE TRIGGER audtest_Bi
 BEFORE
  INSERT
 ON audtest
REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE

JOBOUT NUMBER;

BEGIN

dbms_job.submit( JOBOUT, 'BEGIN AUDWRITE('||:NEW.PK||'); END;',SYSDATE );
:NEW.JOBNUM := JOBOUT;

END;
/

CREATE OR REPLACE PROCEDURE AUDWRITE(I_PK NUMBER) AS

L_OUTPUT        UTL_FILE.FILE_TYPE;

BEGIN
    L_OUTPUT := UTL_FILE.FOPEN( 'AUDTEST_DIR', 'AUDTEST_AUDIT.CSV', 'A' );

FOR LINE IN (SELECT * FROM AUDTEST WHERE PK=I_PK) LOOP
    UTL_FILE.PUT( L_OUTPUT, LINE.PK||','||LINE.SOMEDATA);
    UTL_FILE.NEW_LINE( L_OUTPUT );
    END LOOP;

    UTL_FILE.FCLOSE( L_OUTPUT );

END;
/


INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));
INSERT INTO AUDTEST (PK,SOMEDATA) VALUES (AUDTEST_SEQ.NEXTVAL,DBMS_RANDOM.string('A',ROUND(DBMS_RANDOM.VALUE(1,45))));


--SHOULD BE 5 JOBS
SELECT * FROM USER_JOBS;

--SHOULD BE 5 ROWS WITH JOBNUM FILLED IN
SELECT * FROM AUDTEST;


--WON'T BE A FILE IN C:\TEMP
COMMIT;
--NOW THERE WILL BE A FILE IN C:\TEMP

Some work and a lot of testing will need to be done if this goes into production. But might have some use to somebody.

Dizwell Almost back in the saddle again

2006-08-02T06:04:00.000-07:00

Howard has started on the long trek to getting his amazing website back.

The new URL (for now?) is http://www.dizwell.com/prod/

I wish him luck

Poor Dizwell

2006-08-01T14:17:00.000-07:00

He has given up on Joomla and shut his site down for a spell while he finds something else:

Dizwell Informatics

This site has closed. Joomla has driven me insane, and the flood of emails from people saying, 'Do you know your links are broken' is making me even insaner. Time to clear the decks and start afresh. I have no idea how long it will take me to spec out a replacement CMS and convert everything across. Sorry to everyone for the inconvenience in the meantime.

Good luck and I look forward to having your site back.

Moving an IOT

2006-07-26T08:26:00.000-07:00

There was a question on OTN DB general on how to move an IOT ( index organized table ) from one tablespace to another.

Pretty easy answer, but I realized I had never done it and was assuming it was a simple MOVE command. So what the heck, I am sick and tired of reading old emails and have finished laughing over the Chronicles of George. So lets go see what damage I can do and possibly learn something.


SQL> SET LINES 132
SQL> SELECT * FROM V$VERSION;

BANNER                                                                                                                              
----------------------------------------------------------------                                                                    
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production                                                                    
PL/SQL Release 9.2.0.7.0 - Production                                                                                               
CORE 9.2.0.7.0 Production                                                                                                           
TNS for HPUX: Version 9.2.0.7.0 - Production                                                                                        
NLSRTL Version 9.2.0.7.0 - Production                                                                                               

SQL> --CREATE TABLESPACE TEST1 DATAFILE 'namehere' SIZE 16M ;
SQL> --CREATE TABLESPACE TEST2 DATAFILE 'namehere' SIZE 16M;
SQL> DROP TABLE TIOT;

Table dropped.

SQL> CREATE TABLE TIOT
  2  (ID NUMBER
  3  ,DATA VARCHAR2(30)
  4  ,CONSTRAINT IDPK PRIMARY KEY(ID)
  5  )
  6  ORGANIZATION INDEX
  7  TABLESPACE TEST1
  8  OVERFLOW TABLESPACE TEST2
  9  ;

Table created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (1,'A');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (2,'B');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (3,'C');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (4,'D');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (5,'E');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        

SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';

IOT_TYPE     TABLESPACE_NAME                                                                                                        
------------ ------------------------------                                                                                         
IOT                                                                                                                                 

SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';

SEGMENT_TYPE       TABLESPACE_NAME                                                                                                  
------------------ ------------------------------                                                                                   
INDEX              TEST1                                                                                                            

SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';

INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                STATUS                                    
--------------------------- ------------------------------ ------------------------------ --------                                  
IOT - TOP                   TIOT                           TEST1                          VALID                                     

SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST2;

Table altered.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        

SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';

IOT_TYPE     TABLESPACE_NAME                                                                                                        
------------ ------------------------------                                                                                         
IOT                                                                                                                                 

SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';

SEGMENT_TYPE       TABLESPACE_NAME                                                                                                  
------------------ ------------------------------                                                                                   
INDEX              TEST2                                                                                                            

SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';

INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                STATUS                                    
--------------------------- ------------------------------ ------------------------------ --------                                  
IOT - TOP                   TIOT                           TEST2                          VALID                                     

SQL> INSERT INTO TIOT (ID,DATA) VALUES (99,'Z');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        
        99 Z                                                                                                                        

6 rows selected.

SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST1 OVERFLOW TABLESPACE TEST1;

Table altered.

SQL> SELECT * FROM TIOT;

        ID DATA
---------- ------------------------------
         1 A
         2 B
         3 C
         4 D
         5 E
        99 Z

6 rows selected.


SQL> SPOOL OFF

Questions

2006-07-25T19:43:00.000-07:00

Question from Burelsons forum . Poster wanted to do a select from dba_segments and get a row count of the table without doing an analyze.

I can only fathom why.

"But who are we to ask why? Our task is to simply do, or die"

That is a quote as best I remember it, I wish I could remember it properly. Even google can't find it I have mangled it so bad.

The answer is in the link, but to have it here too:

CREATE OR REPLACE TYPE COUNTROWSTYPE AS OBJECT ( OWNER VARCHAR2(50), SEGMENT_NAME VARCHAR2(100), SEGMENT_TYPE VARCHAR2(100), PARTITION_NAME VARCHAR2(100), BYTES NUMBER, NUMROWS NUMBER ) /

CREATE OR REPLACE TYPE COUNTROWS AS TABLE OF COUNTROWSTYPE /

CREATE OR REPLACE PACKAGE COUNTROWSPKG AS FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2)RETURN COUNTROWS PIPELINED; END; /

CREATE OR REPLACE PACKAGE BODY COUNTROWSPKG AS FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2) RETURN COUNTROWS PIPELINED IS O_ROWCOUNT NUMBER; BEGIN FOR X IN (SELECT OWNER, SEGMENT_NAME , SEGMENT_TYPE,PARTITION_NAME, BYTES FROM DBA_SEGMENTS WHERE OWNER = UPPER( P_SCHEMANAME ) AND ROWNUM<=30 ORDER BY SEGMENT_NAME) LOOP IF X.SEGMENT_TYPE='TABLE' THEN EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||X.OWNER||'.'||X.SEGMENT_NAME INTO O_ROWCOUNT ; END IF; PIPE ROW ( COUNTROWSTYPE( X.OWNER,X.SEGMENT_NAME,X.SEGMENT_TYPE,X.PARTITION_NAME,X.BYTES,O_ROWCOUNT)); END LOOP; RETURN; END; END; /

select * from table( COUNTROWSPKG.DOCOUNT('SYSTEM') );

You can see I limited the cursor and warned the poster a couple of times. Dangerous package. I could just imagine if some unknowning person ran this on say a schema with 300-400 million records in each table and a few hundred tables.

Thomas Kyte is on the ball this week

2006-07-25T19:17:00.000-07:00

asktom.oracle.com has been accepting questions 2 nights in a row!

Too bad I can never think of something interesting and relevant to ask him.

Backups

2006-07-25T14:15:00.000-07:00

Get back from vacation. Check into the failed backups as per the previous post.

1 server with 4 databases, 2 24x7 high usage systems (200-300 users), 1 reporting DB (combining the previous two) and 1 sunsetted system DB (almost zero users). No backups (rman) for 16 days.

6 different people get the emails, including a manager and the lead system administrator. Nobody looked at the problem, everybody read the emails. Ignoring 16 obvious FAILED emails.
Fixed the problem which was the scratch backup disk was simply out of space on the server, took about 3 minutes. The best answer was "I thought it could wait until you get back."

I need a vacation.

Back to work

2006-07-23T19:21:00.000-07:00

I am back to work tomorrow (sigh).

393 emails waiting for me. All work related.

20 voice mail messages - but I do believe that is the maximum number of messages our system can hold.

I am going to need a vacation to recover from amount of work needed to catch up on work from my vacation.

I am more worried about the large number of failed back up messages with no visible or documented attempt to fix the problems than anything else that might have cropped up.

Learn Something Every Day

2006-07-19T15:31:00.000-07:00

This once again was brought on by a question on OTN.

In a large migration process one of my steps is to drop and rebuild indexes. Do I still need to ALSO do a ANALYZE INDEX to make sure the index is up to date with data and good to go with the best performance possible? Doesn't drop and recreating force it to be most recent and I would basically be doing same work twice?
This is Oracle 8i I am running on

My initial answer nope, statistics are not created when you create an index.

I posted it, then thought - well, I posted without actually checking - and low AND behold. It doesn't do it for version 8 or 9. But it appears to for version 10.

You learn something every day.

To try it for yourself you can see the following:

The conclusion is - on oracle 8 or 9, if you drop and recreate an index the statistics are not gathered at create. In V10, the statistics are gathered at create. I guess I will have to stuff this away for an AskTom question because I can't find any documentation relating to this. But it is something difficult to search for.

Changing column order

2006-07-19T14:46:00.000-07:00

Somebody on OTN asked the question:

I would like to know if there is an Oracle feature to change the order of columns in an existing table. The table is quite huge (several 100 millions rows). I want to avoid to re-create the table and migrate the data.

I have seen this question a couple of time on the net over the last little while. I posted the answer below, and thought I might as well put it on the blog as well. The thread continued on after the initial question to include a bunch of more detail that made my answer not the correct answer. I guess I should have simply asked "Why" instead of trying to figure out what the person really wanted.

Unfortunately I have had to do this in the past:

Don't drop your table of course, but I wanted this to be complete:

DROP TABLE REORDER;

CREATE TABLE REORDER (COLUMN4 NUMBER ,COLUMN3 NUMBER ,COLUMN2 NUMBER ,COLUMN5 NUMBER ,COLUMN1 NUMBER ,COLUMN6 NUMBER );

Now if you select it the columns come up in the order it was created
SELECT * FROM REORDER;

You can simply select the columns in the order you want:

SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

You can create a view that the users can use if you wish:

CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

SELECT * FROM ORDERED_REORDER;

clean up
DROP VIEW ORDERED_REORDER;

I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn't do this without some thorough testing.

change the name of the table

RENAME REORDER TO ORIG_REORDER;

create a view to look like the original table

CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER;

Now the view looks the way you want:

SELECT * FROM REORDER;

You can insert etc:
INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6) VALUES (1,2,3,4,5,6); COMMIT;

Some apps won't allow this, they need tables and won't allow views.
Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn't a better way to handle the request.

Zoundry is pretty good

2006-07-18T23:44:00.000-07:00

I am getting more familiar with Zoundry and learning the work arounds and proper way of posting to a blog. I had been designing in MS word and then exporting as HTML. Well, I should have known better. Now I have learned to compose in Zoundry and export from Zoundry to MS word if I want the information in a different format.

I am learning. I went through most of the old posts and reformatted them to look MUCH better than before.

Zoundry is a good tool.

Oracle Trace Analyzer

2006-07-18T10:29:00.000-07:00

Eddie Awad recently posted about Oracle Trace Analyzer here. An excellent post, the quality of posts I hope to achieve in the future. I use the trace analyzer quite a bit, it is an excellent tool and it is one of those questions "doesn't everybody use that? " I ask myself about.

If you don't use trace analyzer you should read Eddie's post and investigate it. The amount of information supplied by the tool is better than TKPROF and is well worth the extra initial installation time.

I have become completely disheartened with the ability of the mainstream blogs to display even the most simple HTML/CSS in the format that I desire. I have tested wordpress as well and just can not get any of the systems to display posts the way I want. Very disappointing.

I used Zoundry and reformatted my post on creating a UNIX script to trace your current session from March 10/2006.

Analytics

2006-07-18T10:28:00.000-07:00

I put together a "test paper" on some SQL analytics I use a lot. The document was done for internal use with only positive feedback, so I thought I would share it here. Nothing ground breaking just some hopefully real world examples.

You can view it in HTML or PDF. The script used to generate the test invoice data is here.

As the "part 1" suggests I am planning on writing at least one companion to this.

Vacation is almost over

2006-07-15T14:10:00.000-07:00

My vacation is almost over. 2 of the 3 weeks vacation is over. Seems like I was working only yesterday. Starting to get bored though. The weather hasn't been the best for camping with cool nights and damp days so we have given up on that. I have been working around the house, that type of work never seems to end. I put built some shelves for my daughters room. They turned out much better than I thought they would. She was very excited at having more place to put her stuff. Actually, she is a lot like my wife, she was more excited at more room to put MORE stuff as the stuff she has already has its own spot. I have now started on a desk made out of MDF for my son's room to replace the small students desk he has. He would like to have a computer in his room instead of all of the computers in the office downstairs. We had his computer on his desk in his room, but there was no room for him to do his homework so we moved it into the office. Now, we will be moving it back. I already have network ran to his room, so once the desk is finished it should be a quick job of plugging it all in. I think for Christmas this year new computers for the kids with be the only present they get. My daughter uses a laptop , I bought her a toshiba 4090xDVD laptop last year (used) and she just loves having it. So I think a new Dell laptop for her, and possible a new Dell PC for my son as he is an avid gamer.

I checked my work email the other night, enough to get my blood boiling, the bad decisions that were made that I will have to deal with. I guess I will be rested and relaxed when I get back and will be able to deal with the problems as they rear their heads.

The new DBA starts next month, I am looking forward to that but he and I will work very little together unfortunately, he will probably be on a different floor. His job is going to be 100% dedicated to our Oracle Financial Applications suite. My junior DBA has realized he was in way over his head and took a transfer to a analysts position until he can learn more. It was a good choice all around. His DBA skills were good, but green, he was assumed to have the same skills and experience as me by others and he was just getting lost in the workload. I still will be giving him DBA related work so he can learn his desired career path. He needs to learn more about the company and how the systems he is working on relate to how the company does business. He was at the center of the company and getting bombarded from all sides with no time to sit and learn. The management finally went through (while I was on holidays) and removed the DBA job title from everybody who was just a DBA in title and no more. So, now, that means I am the only person with the DBA title in the company. All 10,000+ of us. Doesn't really mean anything to the company and it was like this before, but now I can be referred to as THE DBA instead of A DBA or THE SENIOR DBA. Can you be in a senior position with no juniors?

Random Data Generation

2006-07-14T22:19:00.000-07:00

I was asked by a friend at another company if Oracle provides a tool for generating random data so he can fill up some test tables to test their index strategy with a large amount of data in the system. He is a "Developer" but I do not hold that against him, he is an upstanding guy and committed to his job. Instead of simply answering him, I decided to post what I was going to send. After fighting with blogger to get the layout the way I wanted, and moving from a post, to a linked HTML file, back to a post and trying out various free blog writing tools. I basically gave up and started using Zoundry, it does a fine job but I still can't get blogger to display the output the way I wanted. So be it...

Oracle provides the DBMS_RANDOM package for generating random numbers and even random strings. The package is available in oracle 9 or higher.

Random Numbers
The function DBMS_RANDOM.VALUE can be used for generating random numbers. If you do not pass any parameters the number generated is between 0 and 1.

SELECT DBMS_RANDOM.VALUE VALUE FROM DUAL;

This will generate a random number between 1 and 100.

SELECT DBMS_RANDOM.VALUE(1,100) VALUE FROM DUAL;

You can use ROUND or TRUNC to remove the decimals.

SELECT ROUND(DBMS_RANDOM.VALUE(1,100)) VALUE FROM DUAL;

This will generate a random number between 1000 and 9999.

SELECT ROUND(DBMS_RANDOM.VALUE(1000, 9999)) VALUE FROM DUAL;

Random Strings
You can use DBMS_RANDOM.STRING to generate random strings. The first parameter is OPT which allows you some control over the type of characters being generated. The following are the allowable values to be passed as the OPT parameter.

'u','U' : upper case alpha characters only 'l','L' : lower case alpha characters only 'a','A' : alpha characters only (mixed case) 'x','X' : any alpha-numeric characters (upper) 'p','P' : any printable characters

The second parameter is the number of characters to be generated. The maximum value is 2000.
The following will generate 30 random uppercase characters.

SELECT DBMS_RANDOM.STRING('U',30) VALUE FROM DUAL;

Generate 30 random printable characters

SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL;

Lets say we want to generate a little more real world data for testing. We can generate a table of 50 rows of random data very easily, I tend to use the 'P' parameter because you get all of the printable characters so your test data is loaded with ampersands '&' and comma's ','.

CREATE TABLE TEST_DATA AS SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL CONNECT BY LEVEL<=50; SELECT * FROM TEST_DATA WHERE ROWNUM <= 5;

VALUE ------------------------------------------------------------------ 71E/BQh;3[WU&f -d7IV22ZA".mNDi j$ZG|(A^'YHL6A1N!n"q$|HID:A3^g <T?n$U]GF(f.c_xC`[/%!qLm40>{[[ o]3FHL#shWVh|fR|A*&S?B&+VPd^j_ 2M%YN0[gTa{7D-kTomx:c^>bgF.w%3 5 row(s) retrieved

Lets generate random lengths of data by combining the functions together

CREATE TABLE TEST_DATA_RAND_LENGTHS AS SELECT DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)) VALUE FROM DUAL CONNECT BY LEVEL<=50; SELECT * FROM TEST_DATA_RAND_LENGTHS WHERE ROWNUM <= 5;

VALUE                          LENGTH(VALUE)
------------------------------ --------------------------
}+7ym-LI%DqHq                  13
Oki\!<4)UVDAubJ4Y?P^o          21
7(KFuAVN{v<_,XJefe             19
L-\9B&iV#b%*                   12
z2_|Wk1!q7PxAH2zYb|kMuZ        23

Ok, That is great you say, but what can you really do with that? If you want to see a SQL script that creates some tables and generates a few million rows of data, please click here.
That was just a quick introduction to the random number and character generation that is provided by oracle. Nothing new or innovative there, but something to plant the seed in your memory to grow and be used at a later time.To clone and fill an existing table, Thomas Kyte has an excellent procedure for doing this. Please See generate test data automagically

Zoundry

2006-07-14T19:51:00.000-07:00

Well after trying the built in Blogger post editor and the Blogger word plugin, w.blogger and then finally Zoundry. I have decided I will give Zoundry a test for a few posts and see how it works. Sorry to those that might have seen the posts come and go throughout the day, but I was testing, and Blogger doesn't provide a test or development system, so production was the only place to try it out :)

I will rebuild the random data entry I did and see how Zoundry does for posting it.

You can find Zoundry at http://www.zoundry.com/download.html

It does appear to be the best so far today, but they do say it is in beta mode, so I have a feeling the "free" nature of the product may go away.

I really need a vacation

2006-06-26T11:17:00.000-07:00

Especially when I see developers coming up with SQL like the following snippet from an autotrace. I just want to give up and go home.

Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE ( Cost=60278 Card=2488086119
Bytes=25341157122015 )
1 0 HASH JOIN (OUTER) (Cost=60278 Card=2488086119 Bytes=253411
57122015)
2 1 HASH JOIN (OUTER) (Cost=512 Card=435925 Bytes=4422895050
)
3 2 HASH JOIN (OUTER) (Cost=172 Card=1690 Bytes=17080830)
4 3 HASH JOIN (OUTER) (Cost=104 Card=13 Bytes=130884)

Vacation

2006-06-26T06:19:00.000-07:00

Only a few more days until I go on vacation. I just can not wait. I haven't had more than 3 days off in a row since July of last year. It has been a long rough year. In preparation for the planned camping trips during my holidays, I set up the tent and the fly, pulled all of the camping gear out and basically set up camp in the back yard to make sure everything is there and in good working order. My daughter is so looking forward to camping she was ready to burst with excitement and simply would not stop talking during her “helping” me with getting everything setup. I am going to do my best to completely forget about oracle, backups, servers, anything to do with work for 3 straight weeks. I am even going to leave my blackberry behind as where we are going camping the first time, there is no coverage for kilometers in any direction.

Ding Dong!

2006-06-26T06:04:00.000-07:00

To the tune of "The wicked witch is dead" from the Wizard of Oz.

Technojunkies

Ding Dong! The windows database server is dead. Which old windows
server? The Wicked windows server!
Ding Dong! The Wicked windows server is dead.
Wake up - sleepy head, rub your eyes, get out of bed.
Wake up, the windows server is dead. It's gone where the goblins go,
Below - below - below. Yo-ho, let's open up and sing and ring the bells
out.
Ding Dong' the merry-oh, sing it high, sing it low.
Let them know
The Wicked windows database server is dead!

CIO:

As CIO of the
TechnoDepartment In the County of the Land of Oz, I welcome you most
regally.

Manager:

But we've got to verify it legally, to see

CIO:

To see?

Manager:

If it

CIO:

If it?

Manager:

Is morally, ethic'lly

DBA No.1

Spiritually, physically

Sys Admin:

Positively, absolutely
Technojunkies
Undeniably and reliably Dead

System Admin

As System Admin I must aver, I thoroughly examined it.
And it's not only merely dead, it's really most sincerely dead.

CIO:

Then this is a day of
Independence For all the Technojunkies and their descendants

Manager:

If any.

CIO:

Yes, let the joyous news
be spread The wicked Old windows server at last is dead!

Well as you may have guessed, I am not musically inclined, but I had to do something to celebrate the end of Oracle databases on windows servers in the company! Not one production database on windows!

Been awhile

2006-06-22T19:26:00.000-07:00

Sorry all.. well all 12 of you regular readers :) Statscounter says I have 12 readers that come back and about 130 a week on average visits. Not great, but I am not that entertaining. I don’t know if that counts RSS etc, so I have my fingers crossed there are more of you out there. It has been a busy time at work and at home, preparation for upcoming holidays and a plethora of new databases coming online has been consuming most of my time. The mass exodus from blogger by many people of late has made me wonder if I missed a memo someplace J. I have been consumed with reading all about DW databases as our big DW is about to almost quadruple in size as the user community has asked for and received approval for much more data to be loaded for reporting purposes. We will actually have to buy more disks for our brand new SAN that was supposed to last until the end of 2007 without any expansion. The DW is going to be almost 4 terabytes of data soon. I know, not big, but big enough for me. I have been using ‘off time’ at work to setup a proper oracle based training environment. This has become a pet project of mine and I am enthusiastic at how it is progressing. Thank goodness for VM! We had a VM server that we were upgrading as it was undersized, the cost to upgrade it was only a few dollars short of a new server, so we ordered a new server and the existing VM was given to me… minus a few hundred gigabytes of storage, but still a wonderful “gift” from the system admin folks. I spent a few hours configuring a VM client with suse, fully patched oracle 10g2, apex and some other tidbits of internal stuff installed, and then copied it to 4 other clients, giving me a total of 5 I can configure at will. The first one has been setup with just the example schema’s and I have written up a training doc for an introduction to SQL, a short 2 hour seminar which is almost 100% hands on. I set up an intermediate level SQL seminar also, 2 hours as well. Which I have configured so that I show the basics of things like some analytics and writing SQL for indexes instead of writing indexes for SQL and other things like that. The trainee’s each have a user account with everything they need and they have the ability to connect from their work PC’s after the seminar’s are over so they can reference what they did in the seminar and redo the training to focus on area’s they missed. This is based on the outcome of me being asked to train our already trained people with more detail and depth. So far I have only “trained” a few senior people and myself many many, many times as I write the seminars so I can target what we (the company) wants them to know and understand. I probably won’t be able to train the masses until September. No replacement for professional based training of course, but as a VP said – Hey its “free”. I had been working on a “what not to do” seminar, but I see that Tom Kyte has come up with the same idea. So I guess I will have to make sure I don’t make it seem like I am plagiarizing him.

Interviews

2006-06-06T19:51:00.000-07:00

This has been a week for interviews. I had a head hunter give me a jingle early last week with a few positions that he felt were right up my alley. One of them is, a job in France, just outside of Paris. I am pursuing that one, a few years in France would make me very happy. I had to be on the opposite side of the table for the first time in many years. Even though it was only phone interview, it was a learning experience, I did very well and think I started to develop a good working relationship with the fellows on the other side. Other than the manager, they have all contacted me personally with information on the "village" the office is located in. I think, one of the key factors why they liked me so much, we spent about 20 minutes discussing Tom Kytes newest book and some of his blog entries, as well as David Alridge. Facts like that really impress upon you how small the world really is. I have to be on the "good" side of the table this week, interviewing for a Senior Oracle Applications DBA position, this is a new position in the company. The powers that be have decided that our oracle applications database and application deserves a lot more TLC than I am able to give it, so I pushed for a dedicated DBA. I was very surprised when they agreed, then tasked me with going through applicants to get it to a short list of 5. I did 23 phone interviews, of those, I had a list of 9 to pare down to 5. The rest... boy some of them reminded me of watching a monkey trying to get into a coconut when I was younger. Lots of noise, lots of action, lots of blood sweat and fecal matter flying everywhere, alas in the end nothing but a dent in the ground, a bad odour in the air and a hungry monkey. Some of these applicants blatantly lied on their resumes, some of these folks were easy ones to spot - they didn't know what was on their resume. Others tried to "baffle me with BS" assuming I was an HR person or an non-oracle type... even though I introduced myself as the Senior Corporate DBA, and they all received at least one email with my title on the footer. Easy way to waste few hours reading resumes. The poor unsuspecting fellow that is coming up is in for a surpise though, I have a small book of questions for him to go through and 3 brand new dry erase markers for him to write his answers out with. I even have crashed a development instance of our OA that is being retired and I will get him to diagnose and see if he can give me the answer to fix it. He is getting lunch with the department too. And a free weekend in town.

Availablity

2006-05-31T09:58:00.000-07:00

Not really oracle related, other than it runs on an oracle database and I don't like to pick on a company in public, but.... We have a product that uses ESRI's SDE software for mapping. We get most of our support through a vendor who has a very good working knowledge of ESRI. We have been getting some pressure to make the software available 24x7 due to changes in the staffing model. For maintance, SDE requires no users accessing the software, that doesn't make 24x7 very easy to do. We spoke with the vendor with no success. So I thought I would contact ESRI direct. I go and look up the page, find the ESRI developer site. I really and truly started to laugh out loud as I read the home page at http://edn.esri.com/index.cfm?fa=home.welcome . To make it easy to find, here is a copy/paste: Announcements Developer Summit—More Developer Summit session information has been posted. The information can be found under the Media Center tab. We apologize for the delay in posting these items. Limited Availability on Sunday—Each Sunday EDN's availability between approximately 8:00 a.m. and 1:30 p.m. (Los Angeles Time) may be interrupted intermittently due to routine maintenance. We apologize for any inconvenience this may cause you. I have a feeling trying to convince them that downtime for maintenance is not acceptable is going to be a very, very hard sell.

Another for the WTF

2006-05-30T13:42:00.000-07:00

Question from a developer: "Why does this query take so long to process?"

SELECT 1 FROM HEADER WHERE 1 IN (SELECT 1 FROM DETAIL WHERE 1=1 AND DETAIL.HEADERID = '3432223' AND DETAIL.SITEID='OVA3' AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR')) ;

Can anybody spot the problem? They are looking for a simple return, they do not look at the value of the return as long as one row is returned, the java returns TRUE and they continue on. They run the above SQL for every record in HEADER that they are looking to update to look to see if there are corresponding detail lines. The HEADER table has 200K + records. I suggested this one to them.

SELECT 1 FROM HEADER WHERE 1=1 AND HEADERID IN (SELECT HEADERID FROM DETAIL WHERE 1=1 AND DETAIL.HEADERID = '200504505' AND DETAIL.SITEID='OVA3' AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR') ) ;

There are much better ways to do this, I will let the developer work on those, he is very embaressed and is spending the needed time on the issue and has since this one found other queries that were along the similiar lines.

Not to start a war here or anything

2006-05-24T12:56:00.000-07:00

BUT, tomorrow is the beginning of the end for Windows Oracle Database servers in our company. The very LAST oracle database that runs on windows is in the pipe to be converted to HPUX, due date - June 4th, but this might get pushed back a couple of weeks due to factors I can't control. Testing is ongoing, but it looks good, users are impressed by the performance and the vendor is happy. 132 Databases in the company, 131 on a flavour of unix (HPUX,Linux many types, AIX and Solaris) and ONE windows. Free of windows at last... Now, if I could only convince management to allow me to run linux on my laptop. Now... to start the conversion process off of HPUX RISC onto something else... I am thinking AIX. :)

Multiple Failures

2006-05-21T10:21:00.000-07:00

Yesterday (Saturday) was a very bad day... today isn't much better, and tomorrow is looking pretty gloomy too. Yesterday, had a major power failure at our main data center. That was the first failure, UPS all kicked in and started working, but notification system to tell everybody that the UPS were working failed, nobody knew we were on UPS. Second failure. Generator kicks in as it is supposed to, but fuel line from the main tank disconnects from the generator, generator runs out of petrol and stops. Third Failure. UPS systems ran out after a few hours and everything went down as hard as hard can be. As it was the weekend, user load was low, but when the systems went "blank" and didnt' come back, the users noticed. They go to our online emergency contact page to get numbers - gone, servers have no power. They go to the fancy "in-phone" directory listing for our new VOIP phones, gone, server has no power. They can't send emails, email system is in the data center. New system, the redundant backup system is still sitting beside the production system for final testing. Somebody looks up the managers home phone via 411 and gives him a call, and the word goes out. Holiday long weekend, NOBODY around. Manager is calling relatives looking for people. Generator gets repaired and fired up. We are at about 4 hours of downtime now. Everything comes back and the UPS start to charge. We do nothing until the UPS charge fully... 2 hours more hours, start to bring everything back up. Our new SAN won't come back. Our new NAS won't come back. Both have been rigoursly tested and guarenteed by the manufacturer. Won't come back. A manager is driving around the city looking for the senior system administrator's car hoping he is out for dinner or shopping instead of gone for the weekend. His cell phone died last week and it hasn't been fixed yet. Believe it or not, he finds him and his family. Only takes an hour more and everything including the SAN and NAS is back and functioning. Time for the DBA's to take over. 23 databases come back no problem, automatic crash recovery, no issues. Three don't. We get one back up and running with a rman restore via control files (MAN I love RMAN). The second one is actually our primary rman catalog database, it is gone, history. It of course doesn't back up to a catalog server, entire blade is corrupted. No hope of fixing. Rebuild it and restore from an export on tape, back and running in 3 hours. The third one is easily restored via rman. Find out during the restore of the export, that the brand new very expensive tape array is not functioning. That is being rebuilt as I type. New lesson learned. When you are configuring and testing your redundant systems. Don't build them at your local site and move them when done, instead ship them direct to where they are going and build them there. Turns out the mail notifications on the UPS being kicked on didn't get sent because the UPS on the network gear the data center's SMTP server is on failed and the server went down hard. UPS has a "certified working" sticker from 22 days ago. No data loss to any of the databases thank goodness for that. The meetings next week on this are going to be very bad indeed.

Use what you learn

2006-05-09T12:50:00.000-07:00

Way back, way way back I remembered reading about the "WITH query_name (select ...) subq select * from subq", which oracle calls Subquery Factoring I remember thinking at the time "that's neat" and then of course promptly forgot about it. Well, today I spent about 3 hours trying to optmize an audit report on changes to employees in our payroll system. A huge nasty bit of SQL that made numerous FTS into the audit table and the employee table to gather the audit information the way the users demanded it. I tried materialized views, rewriting the queries, even considered pipelined functions, then for some weird reason the old noodle remembered the subqueries - 1 minute to find it in the SQL reference guide, another 5 to rebuilt the SQL. Cost on the query cut from over 30,000 to 543. The time of the query, cut from over 8 minutes to under 5 seconds. This is a direct copy/paste straight out of the 10G SQL reference manual:

 Subquery Factoring: 
Example The following statement creates the query names dept_costs 
and avg_cost for the initial query block containing a join, and then 
uses the query names in the body of the main query. 

WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total 
 FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name)
 , avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) 

SELECT * FROM dept_costs 
 WHERE dept_total > (SELECT avg FROM avg_cost) 
 ORDER BY department_name; 

DEPARTMENT_NAME                DEPT_TOTAL 
------------------------------ ---------- 
Sales                          313800 
Shipping                       156400

Nothing new or earth shattering here... just a comment on how the brain works.

Children

2006-04-24T19:32:00.000-07:00

Nothing work or oracle related here... I have been lucky enough to have two wonderful children, a great daughter who I am proud of , a very artistic and quiet girl with many friends. Then there is my son, Honour roll 3 years in a row, principles roll, achievement awards and generally a wonderful boy who I am proud of as well. I tend to talk about my kids far too much, I am sure my coworkers are thoroughly tired of hearing about me going on about my children. But all things must come to an end. Nothing horrible here, or tragic. Just my son even though he is 12, has decided that being a teenager is the thing for him. With the "teen angst" to go with it. I was hoping it would be put off for at least a few more years. But, I have a "wannabe" teen in the house... I hope it is worse than having a real teen, I can't see how it can really be any worse. My mother is probably STILL laughing, it is amazing how cruel parents can be. ;)

Google does it again

2006-04-20T20:55:00.000-07:00

at http://pages.google.com Now that is easy to use web page development. Simply, easy. And I found a great use for it, storing files that can be referenced from this blog. I modified the testing I did with Jonathon Lewis's script testing of FTS or not with an impossible predicate and instead of having a huge blog post, now it is a short blog post with a link to the output - much much cleaner. Good for posting scripts too. You can expect more scripts to be posted shortly. http://herodt.googlepages.com/home is the page I banged together.

Contractors

2006-04-20T20:29:00.000-07:00

We have a contractor working remotely helping us with performance problems with one of our off the shelf applications, nice enough fellow, very knowledgeable about the application as well as the business logic on why the application does what it does. This was to a particular module in the application, not application wide. The database isn't the performance issue, it is of course the first thing blamed. But luckily management has been proven the fact that the database isn't the issue, instead it was bad design from the vendor in the application and database - hence the good, but brutily expense contractor. I had a bit of a run in with him today, he must have had a bad day and I somehow earned the right to have him rip a strip off of me. A few weeks ago I had started monitoring on all of the indexes in the production database (built in 9i monitoring) and had earlier in the week passed him a spreadsheet of the data. This was being done because the module in production was just not performing well. Well, he was very upset at the results as it appeared about $8K worth of his consulting time of creating and modifying indexes had shown very little result in the production database - even though the results had been stellar in the test database. He started in on how some configuration differences had to exist between the two databases and I had better find the differences and fix them immediately. Not his exact words, but I wouldn't communicate language like that to others sensitive ears. After a few minutes of this, I calmly hung up on him in mid sentence and went about my business trying hard to not resist the urge to pop into the gas guzzler and pay him a visit (only about 2 hours away) and have a nice face to face conversation with him. A period of time passed, and one of our application support people came by asking what I had done to upset the contractor who was now currently on the phone with a 3 managers painting a unlikable picture of me. I explained the contractors issues to the support person, who looked at me with a twinkle in his eye and asked me to follow him. At his cube dwelling he popped open a change request, from about 2 months ago from the contractor asking the in the production database, an application wide parameter be modified to not append a site code defination to all of the SQL being passed to the database. There was a fair bit of communication on the subject on the change, but in the end it was approved and implemented. I am sure you can guess where this is going. He had in his original effort used the site code to reduce the selectivity on the driving tables in his index creating. We bypassed the change protocols and put the parameter back into place the way it had been. The support person started to use the module to generate reports and generally move about the application to start to execute queries. I trundled on back to my cube and monitored what the kindly support person was doing. Low and behold - the indexes where being used and the SQL was executing effeciently and quickly. The application person took a print out of the change and went to visit with the managers on my behalf. I felt that would be better to do instead of it looking like I was trying to cover my own behind. Much better when others have the capability of doing it for you. A short time passed - under 10 minutes and I receive a call from the contractor. Very apologetic for his own stupidity and promises of un-expensed lunchs for me when he is at the office next week. sigh.

Mr. Jonathan Lewis's article "When 2+2=5"

2006-04-14T13:23:00.000-07:00

"From web page of Jonathan Lewis comes his debunking of the myth (Urban legend?) that using the predicate of 1=2 causes a full table scan. On the page When 2 +2=5 Mr. Lewis goes through in detail his tests to prove this myth wrong. This is simply a continuation of his tests on versions 7 through 10xe of oracle to validate or invalidate his conclusions as Mr. Lewis does state this is the same on many versions of oracle but doesn’t provide proof. I did this because a colleague of mine disagreed with Mr. Lewis’s findings and just “knew” that Mr. Lewis was wrong and I had some time at home to kill. I will not draw any conclusions, or make any statements. I have simply provided the complete result sets and let you come to your own conclusions. All of these tests were done in production databases that were busy doing other work at the time, I thought this an appropriate environment instead of a sterile environment. I have provided the output from V$VERSION in every log. The entire output is here I moved the post to a file on my googlepages site as the post was massive and difficult to read. Draw your own conclusions based on the information supplied above.

Oracle Enterprise Manager 10g V2

2006-04-13T13:39:00.000-07:00

Finally Oracle has a decent tool for administering the oracle enterprise - and so much more. I have been a staunch well documented and card carrying member of the anti-oem group for many years now. We just got OEM installed onto a linux server and agents on our big HPUX iron and our windoze box. I was concerned when I saw that the agent download for HPUX was 600+ meg. But it installed quick and easy. The install blew up our one and only windows server, but to me, that is a good thing - one more nail in the coffin for us getting rid of the server. It didn't blow up the test windows box, so we were a little surprised. The information is correct, the overhead is minimal, and it just plain works. We are in the process of reducing the number of checking scripts that run and populate a home grown monitoring database and are beginning to rely on OEM. One of the items that pushed me over the edge to allow it to be installed was the appartenly vapourware OEM2GO product. I should have guessed it wasn't as documented since the documentation reads like a marketing brochure. If anybody has managed to use OEM2GO on their PDA's please speak up any useful tips or hidden documentation would be wonderful. I can connect to OEM with my PDA, but it is the full blown version of OEM and the poor PDA can't handle it.

Almost an entry for Oracle WTF

2006-04-03T19:50:00.000-07:00

Ok, I know "why" but WHY!! Names have been changed to protect the guilty. SELECT * FROM T1 WHERE (STATUS IN ('STATUS1', 'STATUS2', 'STATUS3') AND T1TYPE NOT IN ('PRICE','BLANK') AND 'SOMETEXTVALUE' IN ((SELECT COMPANY FROM COMPANIES WHERE COMPANY = 'SOMETEXTVALUE' ) UNION (SELECT COMPANY FROM COMPANIES WHERE PARENTCOMPANY = 'SOMETEXTVALUE' )) AND ORGID= 'SOMEORGID') AND ( ((T1TYPE NOT IN ('PRICE', 'BLANK') OR PRIVATE= 'Y' ) AND SITEID= 'SOMESITE' ) OR (T1TYPE IN ('PRICE', 'BLANK') AND PRIVATE= 'N' ) ) / PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------------------------------- 0 SELECT STATEMENT 49362 7230K 334 1 MERGE JOIN CARTESIAN 49362 7230K 334 2 VIEW VW_NSO_1 2 100 9 3 SORT UNIQUE 2 90 9 4 UNION-ALL * 5 INDEX SKIP SCAN COMPANY_NDX 1 20 2 * 6 TABLE ACCESS BY INDEX ROWID COMPANIES 1 70 3 * 7 INDEX SKIP SCAN COMPANY_NDX 1 2 8 BUFFER SORT 24681 2410K 334 * 9 TABLE ACCESS FULL T1 24681 2410K 163 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 6 - filter("COMPANIES"."PARENTCOMPANY"='SOMECOMPANY') 7 - access("COMPANIES"."COMPANY"='SOMECOMPANY') filter("COMPANIES"."COMPANY"='SOMECOMPANY') 9 - filter(("T1"."STATUS"='STATUS1' OR "T1"."STATUS"='STATUS2' OR "T1"."STATUS"='STATUS3') AND "T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' AND "T1"."ORGID"='SOMEORIGID' AND (("T1"."T1TYPE"<>'PRICE' AND "T1"."T1TYPE"<>'BLANK' OR "T1"."PRIVATE"='Y') AND "T1"."SITEID"='SOMESITE' OR ("T1"."T1TYPE"='BLANK' OR "T1"."T1TYPE"='PRICE') AND "T1"."PRIVATE"='N')) Get a call from a really nice application support person - "Did YOU do anything, our "T1" reports are taking a long time." After a bit of digging - "We applied a patch on the weekend". Took about 30 seconds to track down SQL like the one above. Was ran for every entry (about 1,000 rows) in another table to "validate" the presence of data in T1. My answer "Back the patch out, call the vendor, remind them what a bind variable is, send this nice explanation of a cartesion join to them too." I love my job! (really I do)

Scripts

2006-03-27T18:35:00.000-08:00

This is a repost of a post I made earlier in March, I just simply could not convince blogspot to properly display the scripts with the fonts etc that I wanted. The first script (current_users.sql) gives you the SID,SERIAL# for every session. So, now got a session that has been active for too long, or using a lot of CPU? Then trace the session remotely.

SQL> exec sys.dbms_system.set_ev(:SID,:SERIAL, 10046,12,'');

I have another script I use that you simply pass a SPID to and it figures everything out for you. I will post it later. You will then have a trace file sitting on the server in the UDUMP location. You can then use tkprof to create an output file you can read. Using the parameter sort=prsela,fchela,exeela sorts the statements from longest running to shortest running, so hopefully the offender is at or near the top. To get the file name (dedicated server) you can run the following:

SELECT PAR.VALUE||'/'|| 'ora_'||P.SPID||'_'||lower(I.INSTANCE_NAME)||'.trc' FROM V$SESSION S, V$PROCESS P, V$INSTANCE I, V$PARAMETER PAR WHERE 1=1 AND S.PADDR = P.ADDR AND P.SPID IN (&THESPID) AND PAR.NAME = 'user_dump_dest' ;

You will have to edit the output in take into consideration the case sensitivity of your parameters. Don't want to trace.. or the session is "locked", then I also have a another script in this for you.
current_sql.sql
Simply call this script and pass the SPID to it, or more than one SPID with them comma separated (hence the IN). I choose SPID because in a UNIX environment they are easy to find and relate the session to the OS. I haven't attempted this script in the Windows world.

DEFINE THESPID = &1 SET VERIFY OFF SET LINES 132 SET PAGES 40 COLUMN SIDSER FORMAT A12 COLUMN SQL_TEXT FORMAT A40 COLUMN STATUS FORMAT A10 TRUNCATE COLUMN SPID FORMAT A10 COLUMN USERNAME FORMAT A10 TRUNCATE SELECT P.SPID ,S.SID','S.SERIAL# SIDSER ,T.SQL_TEXT ,S.STATUS ,S.USERNAME FROM V$SESSION S, V$SQLAREA T , V$PROCESS P, V$SESSION_LONGOPS L WHERE 1=1 AND S.PADDR = P.ADDR (+) AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE AND P.SPID IN (&THESPID) AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE GROUP BY P.SPID,S.SID,S.SERIAL#,T.SQL_TEXT ,S.STATUS,S.USERNAME /

COLUMN OPNAME FORMAT A10 TRUNC COLUMN TARGET FORMAT A15 TRUNC COLUMN MESSAGE FORMAT A40 WRAP COLUMN SEC_REM FORMAT A10 COLUMN MINS_REM FORMAT A10 COLUMN WAIT NEWLINE FORMAT A60 SELECT DISTINCT L.OPNAME,L.TARGET,L.MESSAGE ,TO_CHAR(TIME_REMAINING) SEC_REM ,TO_CHAR(ROUND((TIME_REMAINING/60),2)) MINS_REM FROM V$SESSION S, V$SQLAREA T, V$PROCESS P , V$SESSION_LONGOPS L WHERE 1=1 AND S.PADDR = P.ADDR (+) AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE AND P.SPID IN (&THESPID) AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE ;

You get the SQL current executing, as well you get anything in v$session_longops. Very helpful in tracking down problems.

Newsgroups

2006-03-26T14:47:00.000-08:00

Here are some interesting posts on
comp.databases.oracle.server

Follow up (new post) .

This person is complaining to the news group that the information provided by the helpful folks in group was not appropriate and actually caused the company the person is employed with to pay a fine. I would assume because the person wasn't able to get the instant answer they were looking for so it is the communities fault.

This person really flabbergasts me. I would love to have a long conversation with this person going over the basics of problem solving and the importance of testing and benchmarking thoroughly. I have a feeling it would be a waste of time and effort on my part, something akin to talking to a lamp post.

News groups are great, forums are great. Excellent way to get information. But always check that information out first.

After reading a few other posts by the same author. I would recommend a few hundred hours of professional oracle training for the individual.

Statspack

2006-03-22T07:23:00.000-08:00

Quick SQL to submit a statspack snapshot ever 10 minutes for 12 hours... or so.
set lines 132 show parameter job DECLARE I NUMBER; A NUMBER; START_TIME DATE; BEGIN EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING SYSDATE; FOR I IN 1 ..72 LOOP A := I*10; START_TIME := SYSDATE+A/(24*60); EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING START_TIME; NULL; END LOOP; END; / SELECT JOB ,to_char(next_date,'DD-MON-YYYY HH24:MI:SS') next_date from user_jobs order by next_date / exit

Oh no... no no no no no

2006-03-21T18:12:00.000-08:00

I was "ordered" today to do an Application Express presentation and demonstration tomorrow during the lunch break of a meeting. TOMORROW... yeah.. lucky me. What a nightmare, less than 24 hours notice. First presentation in... ummm... 9 years... yeah, thats nine years. Only 15 people, but my boss (May his soul rest in peace, he is the one asking for the presentation), IT managers from 4 other sites and some senior developers. All knowledgable and have relatively powerful influence in the company. I have been busy making data, building a demonstration and stealing power point slides from Oracle Corporation. I am to show its benefits and why we should be using it. I have to show why it is better than Lotus Notes "databases" and "applications".

Script to find all the files

2006-03-20T18:58:00.000-08:00

I have built this over the years.. stolen pieces, added pieces, removed pieces. I am sure the baseline came from some public source. If you can identify it let me know. This is a script I run every 24 hours on every one of my databases and store the results.

SELECT A.* FROM ( SELECT DECODE(D.TABLESPACE_NAME,NULL,DECODE(FILES.TYPE,'CONTROL','CONTROL_FILE','LOG','REDO_LOG_FILE' ,'ARCHIVE_LOG_DEST','ARCHIVE_LOG_DEST','DUMP_DEST ' ,'DUMP_DEST','TEMP','TRUE_TEMP'),D.TABLESPACe_NAME) "TABLESPACE_NAME" ,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME ,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )"MOUNT_POINT" ,FILES.FILE_NAME "FILE_NAME_PATH" ,tO_CHAR(FILES.BYTES / 1024/1024,'999,999,999.99') "MEG" ,COUNT "BUFFER_WAITS" , TIME "SECONDS_WAITED" ,D.AUTOEXTENSIBLE ,D.MAXBYTES/1024/1024 "MAX_MEG" ,tO_CHAR(FILE_FREE.FREE_BYTES / 1024/1024,'999,999,999.99') "FILE_FREE_MEG" ,HIGH.HIGHWATERM FROM ( SELECT FILE_NAME,BYTES "BYTES",'DATA' "TYPE",FILE_ID FROM DBA_DATA_FILES UNION ALL SELECT MEMBER "FILE_NAME",BYTES "BYTES",'LOG' "DATA",0 "FILE_ID" FROM V$LOGFILE F, V$LOG L WHERE F.GROUP#=L.GROUP# UNION ALL SELECT NAME "FILE_NAME",SUM(RECORD_SIZE) "BYTES",'CONTROL' "TYPE",0 FILE_ID FROM V$CONTROLFILE,V$CONTROLFILE_RECORD_SECTION GROUP BY NAME union all select REPLACE(VALUE,'location=','') "FILE_NAME",0 "BYTES",'ARCHIVE_LOG_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 and vp.name like 'log_archive_dest%' and vp.value is not null and vp.isdefault='FALSE' UNION ALL SELECT VALUE "FILE_NAME",0 "BYTES",'DUMP_DEST' "TYPE",0 FILE_ID FROM V$PARAMETER VP WHERE 1=1 AND UPPER(NAME) LIKE '%DUMP%DEST%' UNION ALL SELECT NAME "FILE_NAME",BYTES,'TEMP' "TYPE",0 FILE_ID FROM V$TEMPFILE ) FILES ,V$INSTANCE V ,DBA_DATA_FILES D ,(SELECT * FROM v$datafile df,x$kcbfwait fw WHERE 1=1 AND FW.INDX+1 = DF.FILE#) WAITS ,(SELECT FILE_ID,SUM(BYTES) "FREE_BYTES" FROM DBA_FREE_SPACE GROUP BY FILE_ID) FILE_FREE ,(SELECT A.TABLESPACE_NAME ,A.FILE_NAME ,A.FILE_ID, ROUND(((B.MAXIMUM+C.BLOCKS-1)*D.DB_BLOCK_SIZE)/1024/1024) HIGHWATERM FROM DBA_DATA_FILES A ,(SELECT FILE_ID,MAX(BLOCK_ID) MAXIMUM FROM DBA_EXTENTS GROUP BY FILE_ID) B ,DBA_EXTENTS C ,(SELECT VALUE DB_BLOCK_SIZE FROM V$PARAMETER WHERE UPPER(NAME)='DB_BLOCK_SIZE') D WHERE 1=1 AND A.FILE_ID = B.FILE_ID AND C.FILE_ID = B.FILE_ID AND C.BLOCK_ID = B.MAXIMUM ORDER BY A.TABLESPACE_NAME ,A.FILE_NAME ) HIGH WHERE 1=1 AND D.FILE_NAME (+)=FILES.FILE_NAME AND D.FILE_ID= WAITS.FILE# (+) AND FILE_FREE.FILE_ID(+) = FILES.FILE_ID AND HIGH.FILE_ID (+) = FILES.FILE_ID ORDER BY SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') ),REPLACE(REPLACE(FILES.FILE_NAME,SUBSTR(FILES.FILE_NAME,0,INSTR(SUBSTR(FILES.FILE_NAME,INSTR(FILES.FILE_NAME,'/')+1,LENGTH(FILES.FILE_NAME)),'/') )'/',NULL),'oracle/'V.INSTANCE_NAME'/',NULL) ) a WHERE 1=1 ORDER BY 1,2,3,4 ;Certainly isn't the fastest or the prettiest script out there. But the information it provides is beneficial. You will need to modify the mount point parsing to match your environment. I couldn't find the one I built for windows which takes that into account.

Application upgrade

2006-03-19T16:07:00.000-08:00

Further to my post about project management and notification. I get myself invited to a planning meeting for the application upgrade. They have done vast amounts of testing, they have 4 databases that they have tested the application upgrade in all of them and everything works smoothly. Takes about 8 hours for the patching scripts to run, then another 12-13 hours for the data load of the old data extracted into the newly patched application. I simply ask one question "Do you think you have enough room in production for all that data?". I know I am in for problems when I get a bunch of blank stares and open mouths. "There should be" and "yeah probably" are the common answers.. then the answer that almost cost me my job as I almost couldn't restrain myself telling this senior fellow exactly what I thought of him. The answer "Well thats YOUR job isn't it". I dang near lost it on the fellow. So I simply snapped back something to the effect of you should have told me sometime in the last two months, not less than a week from go live, your not my wife and I can't read your mind. So, after the meeting I go and take a look at the 2 dev databases I built for them. I know how big they were when I built them, and I know how big they are now. Final numbers, 23 gig larger. 23 gig for an application patch, 23 gig. I am stunned. 23 gig. I can't get over it. I check the other database, yeah 23 gig. 23 gig. More than a 50% increase over the size that is there. I talk with the support people, they once again give the blank stares and open mouth answers I hate. Lots of room in production for the data. I make sure the UNDO is set to grow and there is enough room for the archive logs. I am thinking of approaching them with some hints and suggestions on speeding up the data load as I have caught a snippet or two of their insert scripts. Pure insert scripts into tables with triggers and indexes active. If they are happy with sitting in the office for 24 hours. So be it. I recommend scheduling a full RMAN backup while the users are in, then after the users are out a full export. Then to top it all off a cold backup of the database as it has to go down for the patching anyway. I have a export shell script that splits the database up into chunks based on dba_segments, aiming for 1 gig files, actually building export parameter files listing the tables to keep the files under 1 gig. The objects over one gig, I create parameter files that use the export functionality to split the file into 1 gig files. Then it creates the pipe files for compressing on the fly, and groups the exports into 4 shells scripts so I can run the exports as 4 seperate processes on the server. Took me about 1 week to make the script, and proved its usefullness the first time I used it. I will post it here if enough people ask.

Experience and Exuberance

2006-03-19T14:52:00.000-08:00

I have every DBA's dream, a new developer, fresh out of school (overseas). Trained and experienced in Java, JSP, .NET, PL/SQL and SQL. He started about 2 months ago and was first learning one of our large apps to be an application support person before being turned over to me for training in everything oracle. Great guy, full of ideas. Nothing but good comments about him.

I am under instruction to teach him how in my mind to properly code and develop oracle based applications in our environment. Suddenly, this dream turned into a nightmare. Being a lean shop, we have normally outsource our big applications or have gotten them "of the shelf". We have a mess of different versions of the database, different contractors supporting little bits and pieces of some applications, and not others. Home grown integration programs, ETL utilities that date back to DOS 5.0 days converting data from a mainframe system that was to be replaced in 1998 (commissioned 1990) and is still ticking today. The only standard we have right now, is we have no standards but are working towards one. From someone that has been around for ages, it all makes sense - until you try to explain it to someone new. Oh Boy.

I spent about 3 hours with the fellow, going over basics. Most of which he knew, but he was polite and let me ramble on to him anyway. When I say basics - I mean basics. Don't use weak passwords, name your accounts descriptively, use packages instead of procedures/functions, primary keys, foreign keys, do the work in the database- not the front end, bind variables, bind variables, benchmark and test and document before moving from dev/test to production. I am here to help.

I started to focus in on a single system, one that interfaces with our Oracle Financials system and always has a list of changes wanted/needed to the interface. It is, based on storage and user base, the 3rd largest application in our company. The owner of the software vendor actually used to work for our company, he quit and started selling us software. He and his people write good stuff, very focused, no fluff.

The particular change I started to describe to him, is one I thought would a good introduction to the thousands of lines of interface code, we interface accounts payables invoices, fixed asset information, general ledger and even accounts receivables into and from our financials system. The users are constantly choosing the wrong financials period when they post their data. They must choose a period because they can post into many periods so we can't figure it out for them.

When they pick a wrong period - specifically a period that is closed - it involves IT support to go in, clean up the source system and the financials system and let the users send it into the proper period again. Only about 30 minutes work, but one month end we had 9 users do it wrong at different times of the day. We need something to look across into the financials system and see what period is opened and closed. Easy enough.

We go into the financials system, and work out a view on the tables that show what period is opened per module. He comprehends that all with great speed.

We then go into the source system and start to work out the best place to put the check. We decide it had better go at the very beginning of the generation phase as not to waste too much time of the users, so I show him where it should be, he understands and I let him get to work. About 2 - 3 hours worth of coding work to get it all completed on the backend and on the front end.

He sends me an email early the next morning, he documented everything, he had decided to put the code into a trigger on the table that holds the batch information instead of in the PL/SQL that does the generation because the PL/SQL is controlled by the application vendor, everything links to that table for the header/details, a single record is created no matter how much data is being sent. Seems to be a reasonable place to put it. I go over his documentation (not the code) and it seems to make sense. I have him clear it with one of the "power users" and then give the go ahead to put it into test and let some users at it.

A couple of days go by and I inquire with him on why I haven't heard from him.
He says he can't figure it out, he runs his code in an anonymous block and returns in about 2-3 seconds. Yet he puts it into the trigger on the batch table, creates a batch and he has waited hours for the batch to return. Interesting, ok, well I ask him to create a test schema, take a copy of the tables and lets work on it in there. We spend an hour or so and I can't see any problems. I watch him put it into the test database, looks good. We generate a batch and well... minutes turn to tens of minutes. I look at the code... looks great, copy it out, works great.

WTF? I look into it it, I just can't figure it out. So far we have spent about a 5 days on these 16 lines of code. Everything works. I take a step back. Think about things. Stop thinking like developer, go to DBA mode.

Go to DBA_objects and search for objects changed in the last 5 days. Find the before insert for each row trigger on the batch table, and what's this, changes to the before insert for each row table on the invoice detail table. Take a look at the trigger. There is an exact duplicate of his perfectly working code in the trigger. So, basically, for every record put into this invoice detail table, it queries our financials system to make sure the period attached to the invoice is valid and open. Take a look, the test batch he was trying to generate would have over one hundred thousand detail lines.

"Whoops there it is".

Quick fix and a recompile and his code now works wonderfully well. No perceptible difference in speed to the users and they now can't generate to a closed period.

My experience should have told me to keep better track of his exuberance.

He is still a great guy, and now really focused on what he has open in what window.

Notification and project management

2006-03-16T07:01:00.000-08:00

Always seems to be a lack of that. Can't get over it. I was told yesterday of a huge 3 day outage in 10 days. I have apparently been scheduled to do a bunch of late night/early morning work over the weekend. I am out of town that weekend (already planned) and that seemed to throw a huge wrench into their plans. DBA work critical to the application patching... no DBA. They have known for 2 months. First word I got of it was an email from a contractor asking me if I could pick her up at the airport since I will be at the office in the morning on Saturday. I had even booked the monday off and the manager who is supposedly managing this project missed that.

ORA-04031 errors

2006-03-13T19:11:00.000-08:00

Starting getting ORA-04031 today in one of our 24x7 databases. That is a good way to ruin your day, but this was a 9i database - that isn't supposed to happen. I run scripts from our central database that monitors possible shared pool fragmentation (among other things) every ½ hour, charts it all and more importantly sends an email when things are looking like it could be a problem. I was amazed, the utility never reported a problem via email. I started looking at the issue, had 15 phone calls in 5 minutes, so I flushed the shared pool to at least get people into the system. I checked the shared pool right after I flushed it, in only a few seconds, was already up to 70% usage (450meg shared pool).

No way, that just isn't right. The application designers have never heard of the term "bind variable" before coming to our shop, one of these "database independent" , middle tier independent, we just run java server pages applications. A horror show and a constant source of "its too slow" complaints from all 500+ users. After a few minutes investigation, I found a session that had been running for 105 minutes and was still going. No way, my utility would have told me that, it looks for things like that. I am getting phone calls again that people are getting 04031 errors when opening screens. Take a look, 100% shared pool, so I flush it again.

I can now deal with the session, turns out it is pulling massive amounts of data over a DB link from our financials systems (a couple million rows) with thousands and thousands of queries. I hate integration done by developers. I kill both sessions from both databases and flush the shared pool again. 10 minutes later, 55% usage, the balance is restored. No more complaints. Turns out the guy that started the report, went HOME.

Now, onto my utility, it can't be broken, I wrote it, and I normally write really good stuff.It is accessed via an APEX front end using LDAP authentication… thing of beauty. I pop straight to the logging screen, yup, there are the connections to the problem database. Drill into the results, yup, there is a possible shared pool fragmentation alert and a long running query alert. Go to the outgoing email logs, yup 3 emails sent 12 minutes before all of the phone calls.

Check my inbox, and my blackberry no messages. So, I think email server problems. Go check the email server, I can ping it and send an email from my desktop, check the SMTP servers, they are all working, I can connect and do a POP command. Now what… go to the machine the utility DB runs on, manually send an email, wait, wait, wait Hmmm not getting the email. Try from another machine. Not getting the email. Try from gmail, no email.

I get one of our tech guys to check the mail server. Find out that the lotus notes mail server has decided to stop forwarding email, actually turns out all 3 notes servers decide that forwarding mail is a bad thing, no reason why just "Failure to process message", over 3000 messages lost, gone, poof but luckily reboot fixes it (must be a Microsoft server).

Those emails appear to be gone forever though, I certainly hope the CEO wasn't expecting anything important. I then proceed to calmly explain to my manager the he is going to have dozens of phone calls, and other managers coming to him for the next week complaining that their site lost money and people can't do their work because the "database crashed" and how could we let this happen, why do they pay so much of their monthly department earnings to the IT department?

The managers of course haven't a foggiest idea what a "database" is, or care when it is explained to them it was the fact some goober accountant decided that he really and truly needed all of the detail data in our financials system over the last 7 years in his crystal report, so he actually caused all of the problems. My poor manager is going to get raked over the coals, he is going to get all of this, because, LOTUS NOTES SUCKS.

Tracing

2006-03-10T21:44:00.000-08:00

Another SQL script:

set echo off set heading off column trace new_val tracefilename set autotrace off select c.value '/' d.instance_name '_ora_' a.spid '.trc' trace from v$process a, v$session b, v$parameter c, v$instance d where a.addr = b.paddr and b.audsid = userenv('sessionid') and c.name = 'user_dump_dest' /set heading on set echo on alter session set events '10046 trace name context forever, level 12' /set timing on set autotrace on set echo off prompt set autotrace traceonly prompt if do not want rows returned

I have the file name set as an environment parameter in unix:
export traceme=/sql/traceme

I just run

SQL> @$traceme

This is a modification on the getrace.sql from Tom Kyte.
Boy... I am beginning to wonder if I am a Kyte Stalker??

Oracle financials

2006-03-10T07:18:00.000-08:00

I also have to administer a mid sized oracle financial's (e-business suite or whatever they call it today) database. It is still stuck on 8.1.7.4 and is a thorn in my side. What a conveluted mess of technology, and since it is a tool from oracle, they of course use very little of the databases capabilities or technologies. We are planning a upgrade to 10g this year. So, after all that work I hope they have improved their application. But the users love it, it is their baby. The amount of paper that database generates via the reports is freaking amazing.