Guest Blogger: Jeff Smith
I am a tools freak. Wait, that didn’t sound quite right. I’m extremely passionate about tools in the database space. After a short stint in the real world, I decided to make a career of it. I’ve been an advocate for database tools since 2001 and recently became the Product Manager for Oracle in the database tools group.
So why am I so obsessed by tools? Like any good IT professional I am extremely lazy. If I can do the job just as well but in fewer steps using a tool, I’ll jump on that with both feet. So when ODTUG asked me to share my favorite tools, I was like a pig in slop. Hmm, I’m not sure how that translates outside the US, so let’s just say that ‘I was very happy.’
Everyone loves a good Top 10 list, but remember that I am lazy, so let’s do a Top 5 instead (in no particular order).
1. The Internet, 2.0
Message boards, Yahoo! Groups, Stack Overflow, and the Oracle Technology Networkforums are all great examples of what I would call Internet 1.0 resources. You should find a group that speaks to you and take advantage of it. Many of you are already there or else you wouldn’t be reading this ODTUG blog post. So I am going to challenge you to join…wait for it…Twitter!
Twitter is social network that works for me. It’s instant access to the smartest and most helpful database professionals in the world. Folks like Cary Millsap and Mike Riley are there – you know those guys, right? Find tuning a challenge? Follow people like Maria Colgan. All she does is manage the database optimizer and answer tough tuning questions in her spare time.
Want to get to know the folks hanging out at Kscope12 before you show up? Most of the presenters are on Twitter. ( Follow @ODTUG and use #Kscope for conference updates.) We love meeting our followers in person, so don’t be shy at the user group conferences!
Ready for that next step in your career? The best jobs are never posted. They get leaked and filled on Twitter. Want to stand out in a crowd? I asked for a show of hands at my last presentation of those who were on Twitter. About 5 hands out of 100 were raised. That’s only 5 percent. Be a “five-percenter” and help us take over the database world! Oh, and how do you think I got asked to write this blog post?
If I’ve convinced you and you’re ready to give Twitter a try, I recommend you read this free Twitter book. Then you’ll want to follow me and check out my OraclePeeps list of interesting folks to follow.
2. Regular Expressions (RegEx)
Sometimes the data doesn’t look the way you need it to. But you know there’s a pattern there. Regular Expressions are what might be holding you back from taking your code to the next level. Don’t be afraid of RegEx. I was first exposed to RegEx when I started writing some Perl scripts. And then of course I noticed how much easier UNIX scripting got when I understood RegEx. But here’s my dirty secret – I don’t have RegEx memorized. Instead I know enough to know when I need them, then cheat and jump onto Google for the rest. How many times have you noticed a table that just has a big junk VARCHAR or CLOB column? A lazy architect decided to let you parse out what you need instead of breaking it up. This would be a nightmare without RegEx!
And of course you already know about Oracle’s functions for RegEx, right?
3. DBMS_ Support Packages
There’s something like 500 of these packages. No one expects you to know all of them. I once was asked to help review a tools book that had an entire set of chapters dedicated to all the SYS.DBMS_ packages. It was not an exciting read, but it did open my eyes to some really helpful tools I had overlooked. Now when I need to tackle a new task, the first thing I ask is ‘Maybe there is a package out there that already does what I need?’ There are a few overlooked packages that I think any developer should be not only aware of, but should be using on a regular basis:
- DBMS_PROFILER – shows lines executed in your stored procedures, with number of executions per line and amount of time spent per line and execution. The #1 tuning tool for a stored procedure?
- DBMS_MONITOR – trace a session. You know how to trace, right? I should probably split this out as its own tool, but I’m greedy and promised to stick to only 5. If you don’t have the ability to trace your own sessions in your development environments and access the resulting trace files, go talk to your DBA RIGHT NOW.
- DBMS_SCHEDULER – DBMS_JOBS is so yesterday. Scheduling tasks in the database can get complicated. If you’re on an 11g database but still using an 8i package to handle it, you’re really missing out. I could say the same thing about continuing to use EXP instead of EXPDP!
4. A Great Offline Text Editor
Notepad is a popular utility for many reasons. It doesn’t do anything you don’t ask for. Have you ever tried to author an HTML document in Word? Now some of you are going to laugh and shake your head in amazement that I am wasting an entire entry in this list to ask people to stop using Notepad. But how many of you notice your co-workers still using it when there are awesome free utilities like Notepad++ out there? If you’re a *NIX person, I don’t even have to mention vi, right?
5. A Database IDE That Works for You
You saw this coming a million miles away, right? Of course you know what my favorite IDE is, but I really just want to make sure that the tool you ARE using does what you need it to do. Here’s what I look for when evaluating a database tool:
- Platform support – does it run on Windows, OSX, and *NIX? Windows is still the dominate desktop OS, but most techies lean towards Apple. You shouldn’t have to compromise your choice of tools based on your OS. And you shouldn’t have to license Windows and spin up a VM on your MAC just so you can work with your database.
- Ease of use – the tool is there to help you, not make you feel stupid. You’re debugging a stored procedure, not practicing your Flight Simulator skills, right?
- Extensibility – no tool can give you everything, so it should allow you to extend its capabilities. Love Cary’s Method-R tools? Run them right inside my favorite IDE!
- Cost – if you don’t have to pay any extra money to use the tool, then why would you want to? I’ll break down and buy a tool from time to time – SnagIt comes to mind –but only after I exhaust what comes out-of-the-box for free.
- Platform Support – How soon can the tool support the latest and greatest database features and products? While I might not need to use version. Next in production environments, I am constantly evaluating and building the ‘next best thing.’ As an example, SQL Developer will support Oracle’s new Database Public Cloud offering on Day1.
Again, tools are here to help us. If you are using a tool and it is not saving you time and effort, then you are probably using it incorrectly, or it is just not the right tool for you.
I’ll be at ODTUG’s Kscope12 event with two presentations featuring my favorite database tool and IDE:
- SQL Developer Tips and Tricks
- Debugging PL/SQL With SQL Developer
Can’t wait? Head on over to my blog and catch up on my favorite tutorials, tips, and did-you-know posts dedicated to Oracle SQL Developer.