Optimizing working environment. Part I, GUI

Working with database performance troubleshooting means dealing with challenging and exciting problems. But it also means doing certain things over and over. And over. Like typing the same query or clicking the same button zillion times a day. And when you’re doing something very frequently, anything that can be done to simplify or automate such tasks becomes very important. So I decided to share some of the tricks I use to optimize my working experience in a short mini-series. This post will be on GUI, and the part II will be on command line.

My GUI tool of choice is SQL developer. I’ve tried several tools before (in particular, I spent a lot of time with Toad), I also tried using sqlplus exclusively, and eventually I’ve come to understanding that combination of command-line tools with SQL Developer is the best choice for someone in my line of work. An important consideration is that SQL developer is the only Oracle database IDE tool that is developed and supported by Oracle corportation itself. It’s free, simple, fast, stable, intuitive and highly customizable. If you’ve worked with a GUI tool like Toad for a long time, switching to SQL developer might take some getting used to, but it’s worth it.

Currently I’m using SQL Developer 4.1 “early adopter edition” (i.e. essentially beta). Despite it’s beta status, it has proven to be stable, at least for the tasks that I do.

Top SQL developer features/customizations for me are:

1) Completion insight: as you type, you’re offered completion choices. It is a useful feature, but not in the default automatic mode (where it seems to obey all sorts of Murphy laws and to pop up in exactly least appropriate moment). Go to Tools => Preferences => Code Editor => Completion Insight to change this behavior (you can disable it altogether or set a more comfortable delay, like a couple of seconds). Use Ctrl + space to call autocomplete choices manually. Keep in mind that it only works properly inside what SQL Developer considers to be syntactically correct (although incomplete) block. So if the feature is not working, check that the previous SQL statement in the worksheet was ended with a semicolumn (or a slash), all keywords were spelled correctly etc.

2) Autotrace: unlike sqlplus where you’re only offered 10 or so most basic statistics, SQL Developer can dump pretty much all stats (from V$MYSTAT dictionary view) for you. It’s very useful, but it can also be very overwhelming. Go to Tools => Preferences => Database => Autotrace/Explain Plan. There are 3 checkboxes on the bottom of the window: “V$MYSTAT”, “fetch all rows” and “skip 0-values statistics”. I keep all 3 checked. The last one makes sure that I don’t need to scroll through tens of zero valued statistics when evaluating autotrace results — very handy.

3) Unshared connections: when a task (e.g. a SQL query) is taking too long to complete, you can always open another tab with an unshared connection to continue working while waiting for the task to complete. Just click on the shiny icon with “SQL” in the upper left area of the worksheet. You also have an option of making every new tab use an unshared connection (Tools => Preferences => Worksheet, “New worksheet to use unshared connection”), but I wouldn’t recommend that to avoid creating too many database sessions (especially if you use SQL Developer to work with production or high-profile shared UAT environments).

4) I like to open lots of worksheets against the same connection — e.g. I use worksheet 1 to run a query, then I open an unshared connection to keep myself busy while this query is still running, then I open yet another tab to run some queries to see where worksheet 1 is at, then someone calls and asks me to do something on this database which I do on worksheet 4, etc. etc. Well it doesn’t happen literally this way, but I think you get the general idea. Worksheets are scratch areas, where you scribble some code that you most likely won’t need to preserve (and if eventually you decide that you do need to preserve it, you save it into a file, which is an entirely different thing from a worksheet). Because of that, I keep “save file” prompts disabled: Tools => Preferences => Worksheet, uncheck “prompt for Save file on close”.

5) Finding objects by browsing schema object tree isn’t always convenient. “Find database object” window is faster (go to View => Find DB Object if it’s not displaying). An alternative to this is to type the object name in the spreadsheet and then right-click it and choose “Open Declaration”. Yet another way to do it is by hovering mouse over object name while holding “Ctrl” button — the object name should change color and become underlined (like a link) — then you can double-click it to open the definition.

6) Use reports for routinely run SQL. Reports are the key to using SQL Developer efficiently. Of course you can also save frequently used SQL into scripts, it won’t be as efficient. For one thing, you’ll be getting script (i.e. textual) output instead of the standard data grid. But if you create a report, running it is just a matter of one click! Moreover, you can have input parameters (bind variables), and the values of parameters used last will be remembered. Apart from the default data grid, you can use several different output formats, e.g. you can visualize your report results as a chart. You can have parent-child reports, which is a very powerful feature for performance troubleshooting (you zoom in on the session of interest in the main window, and then you can see various details of what’s going on in it in the tabs produced by child reports).

7) Customize pre-installed reports. SQL developer comes with many pre-installed reports. Some of them can be used the way they are, others I cannot use without customization. These reports cannot be edited, but they can be copied to the “User defined reports” folder, where you can customize them any way you like. For performance troubleshooting, the most useful report is “Sessions” (All reports => Data dictionary reports => Database Administration => Sessions => Sessions). Right-click on it, choose paste, go to “User-defined reports” folder, right-click, chose “Paste”. Now you can change the main report (and/or child reports), or you can create child reports of your own. For example, I don’t like the default way SQL Developer provides to monitor SQL (Tools => Monitor SQL => right-click on SQL statement of interest and chose “Show SQL details”), so I added a “SQL monitoring” tab to session details (for some reason, I had to change :SID bind variable name to uppercase to make it work correctly). I still have to copy-paste the SQL monitor output to Notepad or similar text editor to read it properly, but even so it’s easier for me than other ways.

8) sqlplus commands and scripts. SQL Developer understands some sqlplus command, and in particular, and it’s possible to run sqlplus scripts from its worksheets — just type @scriptname.sql and hit Ctrl+Enter. You can specify the default path for scripts in Tools => Preferences => Worksheet (it hasn’t always worked for me in the past, but at least it’s worth trying).

If you have your own SQL Developer tips and tricks for performance troubleshooting that are not listed here, I’ll be happy to hear them in the comments!