At Hotsos Symposium Training Day I used rlwrap with sqlplus – which gives nice command line editing and history capabilities for tools like sqlplus. Additionally I pre-generated commonly used Oracle keywords, data dictionary view and package names into rlwrap wordfile, so I got nice tab-completion too. Sqlplus sucks much less with rlwrap ;-)

It’s relatively easy to install rlwrap on Unix (there are rlwrap RPMs out there, Solaris freeware packages and I installed it on Mac via macports.org). Just google around…

You can have rlwrap on Windows too – As rlwrap has been coded for Unix flavors, then on Windows you need to run it on a Unix library environment emulator – like Cygwin.

Dave Herring and Michael Paddock have both written an article about how to get rlwrap & sqlplus running on Windows, check out the articles here. It’s worth reading both as they have different additions…

So, if you want command line history, search and tab completion for sqlplus on Unix flavors or Windows, check these articles out!

http://daveherringsdbablog.blogspot.com/2010/03/sqlplus-command-history-with-cygwin.html

http://blog.enkitec.com/2010/04/29/using-sqlplus-with-rlwrap-on-ms-windows/

Tanel Poder Administration, Cool stuff, Oracle, Productivity, Tools

In early January I wrote that I’m gonna start organizing the more serious and practical Oracle content into my new website and I’ll leave my blog for Oracle hacks, my (IT) observations and philosophy, general thoughts and just fun.

It’s time to publish the newsite now with an application demo rototype which gives some clue of what kind of features will there be in the secret project I’ve been working on for several months with my friend and business partner.

The website is located here:

http://tech.e2sn.com

E2SN does have a meaning, but I’ll leave it a secret for now ( you are free to guess ;-)

So, there’s not much technical content at the site yet, but there’s a cool online app which you should check if you deal with SQL tuning and execution plan analysis much.

It’s called PlanViz, Oracle Execution Plan Visualization app, you can check it out here:

http://tech.e2sn.com/apps/planviz

Oh, I’ve also created something called a “Living Book” into my website, where I will write about Oracle, performance, troubleshooting, etc. There is also a place where people can request what I should write about there!

And that’s all for today!

Tanel Poder Administration, Cool stuff, Internals, Oracle, Performance, Productivity, SQL, Tools

What you see below is a common problem. Someone sends you (or posts to a forum) a wide execution plan, which is unreadable because of wrapped lines. For example, this one below:

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

| Id  | Operation                   | Name                    | E-Rows |  OMem |
 1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT            |                         |        |       |
 |          |

|   1 |  SORT AGGREGATE             |                         |      1 |       |
 |          |

|*  2 |   HASH JOIN                 |                         |     13 |  1102K|
 1102K|  355K (0)|

|*  3 |    HASH JOIN                |                         |     13 |   988K|
 988K|  367K (0)|

|*  4 |     HASH JOIN               |                         |     13 |   921K|
 921K|  621K (0)|

|*  5 |      HASH JOIN OUTER        |                         |     13 |   836K|
 836K| 1224K (0)|

|*  6 |       HASH JOIN             |                         |     13 |   821K|
 821K|  501K (0)|

|*  7 |        HASH JOIN            |                         |     13 |  1102K|
 1102K|  501K (0)|

|   8 |         MERGE JOIN CARTESIAN|                         |      1 |       |
 |          |

|*  9 |          TABLE ACCESS FULL  | PROFILE$                |      1 |       |
 |          |

|  10 |          BUFFER SORT        |                         |      1 | 73728 |
 73728 |          |

|* 11 |           TABLE ACCESS FULL | PROFILE$                |      1 |       |
 |          |

|* 12 |         TABLE ACCESS FULL   | USER$                   |     36 |       |
 |          |

|  13 |        TABLE ACCESS FULL    | PROFNAME$               |      1 |       |
 |          |

|* 14 |       TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |      1 |       |
 |          |

|  15 |      TABLE ACCESS FULL      | TS$                     |      7 |       |
 |          |

|  16 |     TABLE ACCESS FULL       | TS$                     |      7 |       |
 |          |

|  17 |    TABLE ACCESS FULL        | USER_ASTATUS_MAP        |      9 |       |
 |          |

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

So now you either try to manually edit and fix the execution plan text so you could read it or ask the developer to send the execution plan again. Both approaches take time.

Well, sometimes things are easy – in this particular case I saved the above into a file called /tmp/x and ran the following command:

$ cat /tmp/x | awk '{ printf "%s", $0 ; if (NR % 3 == 0) print } END { print }'
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | E-Rows |  OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |        |       | |          |
|   1 |  SORT AGGREGATE             |                         |      1 |       | |          |
|*  2 |   HASH JOIN                 |                         |     13 |  1102K| 1102K|  355K (0)|
|*  3 |    HASH JOIN                |                         |     13 |   988K| 988K|  367K (0)|
|*  4 |     HASH JOIN               |                         |     13 |   921K| 921K|  621K (0)|
|*  5 |      HASH JOIN OUTER        |                         |     13 |   836K| 836K| 1224K (0)|
|*  6 |       HASH JOIN             |                         |     13 |   821K| 821K|  501K (0)|
|*  7 |        HASH JOIN            |                         |     13 |  1102K| 1102K|  501K (0)|
|   8 |         MERGE JOIN CARTESIAN|                         |      1 |       | |          |
|*  9 |          TABLE ACCESS FULL  | PROFILE$                |      1 |       | |          |
|  10 |          BUFFER SORT        |                         |      1 | 73728 | 73728 |          |
|* 11 |           TABLE ACCESS FULL | PROFILE$                |      1 |       | |          |
|* 12 |         TABLE ACCESS FULL   | USER$                   |     36 |       | |          |
|  13 |        TABLE ACCESS FULL    | PROFNAME$               |      1 |       | |          |
|* 14 |       TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |      1 |       | |          |
|  15 |      TABLE ACCESS FULL      | TS$                     |      7 |       | |          |
|  16 |     TABLE ACCESS FULL       | TS$                     |      7 |       | |          |
|  17 |    TABLE ACCESS FULL        | USER_ASTATUS_MAP        |      9 |       | |          |
---------------------------------------------------------------------------------------------------

All I did here was that I stripped out line feeds from all lines except every 3rd line (which is the real end of the original line).

Note that if your linesize is very wide (and trimspool/trimout settings are ON) then this script would need some adjustment…

I’m sure this trivial approach doesn’t work in all situations, but with this article I wanted to illustrate that sometimes things which seem hard can be made much easier with a little scripting knowledge. If you are thinking which technology you should learn next – then better check out a Perl, Python or some shell+AWK book :)

By the way, if you want real flexibility displaying your execution plans (from library cache), then check this out:

http://blog.tanelpoder.com/2009/05/26/scripts-for-showing-execution-plans-via-plain-sql-and-also-in-oracle-9i/

Tanel Poder Administration, Oracle, Productivity, Tools, Unix/Linux

Cary Millsap’s recent post prompted me to write down some of the related thoughts in my head.

Here are few of my mantras for systematic troubleshooting and performance tuning, which have materialized in my head over the years of work:

• Picking the right starting point to troubleshooting and performance tuning is the most important decision in that process.
• Pick the wrong starting point and you end up going in circles.
• The scope of your performance data needs to match the scope of your problem, otherwise you end up going in circles.
• If you don’t measure what matters, you may end up fixing what doesn’t matter.
• If you’re not systematic in your troubleshooting, you may get lucky, but you don’t want to be dependent on luck! Moreover, you wont’t need to be lucky if you are systematic in your work!
• Performance tuning is overrated. Fixing fundamental design and coding flaws via changing a magic configuration parameter is a dream just like is getting slim and healthy via eating magic diet pills bought from TV shop.
• Your response times are too long for only two reasons:

1. You are doing too much work
2. You are waiting for too much

…both of the above things can be measured in Oracle…

• There’s no such thing as slow database or slow system. How can it be slow independently, without anyone experiencing this slowness?
  • If users say that a database is slow, they must be experiencing that somehow! The only way to experience database slowness is via a connection to it, in which case you’ll have a session (to measure).
  • If a monitoring system says that a database is slow, then it must be running and measuring response time of some task just like users do, otherwise it can not reliably say something is slow.
• Performance is about one thing and one thing only – time. And time is measured in seconds, not in CPU utilization, number of physical IOs or looks of an execution plan.

Here’s a link to a Cary Millsap’s awesome post, read it!

• http://carymillsap.blogspot.com/2009/12/my-whole-system-is-slow-now-what.html

By the way, as far as database design and writing SQL is concerned here’s a good chance to learn how to write SQL right from C. J. Date:

• http://method-r.com/education/107-cj-date-course

Tanel Poder Design, Oracle, Performance, Productivity, Troubleshooting

Sometimes when an attendee describes me some totally weird problem during a seminar, I am immediately able to answer something like “Hey this looks like a bug related to this Oracle configuration and can be influenced by xyz”.

And then people ask me “How the hell do you know all this stuff?”

Well, I haven’t been bitten by all of these bugs myself, but I have been doing something for many years, almost every day… reading my email!

Oh, and additionally I have configured Metalink to send me daily updates about new/updated notes, forum articles and… bug descriptions!

The last part is very important. Bug descriptions tell you something about new bugs found (and old bugs rediscovered) and sometimes their details tell you an interesting piece or two about Oracle internals related to them.

Read more…

Tanel Poder Administration, Cool stuff, Internals, Oracle, Performance, Productivity, Troubleshooting

First, Happy New Year to you all!

This year I promise to organize my blog a little better, to have some index of my articles, scripts etc :)

Here are the news…

I’ve moved my blog…

In December, when visiting Shanghai, I noticed that my blog was not accessible from there. Apparently the state firewall blocks all access to wordpress.com IPs.

So I have moved my blog to a virtual private server, off wordpress.com – my blog should be accessible from China now as well.

My Advanced Oracle Troubleshooting Seminars in year 2009

If you like the contents of my blog or conference presentations, you’ll sure like my 2-day seminar!

Here are the dates and links for more info:

3-4. February – NYOUG @ New York City – http://www.nyoug.org/etc/training/htm/NYOUG_Training_Session.htm

NB! As this is my first public seminar in US, you’ll be able to attend it for a very very good price! And the early bird registration (which gives you even better price) is open until end of today! More seminars in other US cities are planned for 2nd half of 2009.

2-3. April – Miracle @ Netherlands – http://www.miraclebv.nl/

Probably happens in Utrecht, I’ll keep you updated if it changes.

13-14. April – Oracle @ Singapore – http://www.oracle.com/education/apac/sg_tanel_poder.html

16-17. April – Oracle @ Sydney – http://www.oracle.com/education/apac/au_tanel_poder.html

20-21. April – Oracle @ Melbourne – http://www.oracle.com/education/apac/au_tanel_poder.html

23-24. April Oracle @ Auckland – http://www.oracle.com/education/apac/nz_tanel_poder.html

27-28. April – PiSec Ltd @ Edinburgh – http://www.pisec.org/index.php?option=com_content&view=article&id=6&Itemid=12

11-12. May – Oracle @ Spain – http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D70365_1060245&p_org_id=51&lang=E&source_call=

18-19. May – Miracle @ Denmark – http://www.miracleas.dk/index.php?option=com_content&view=article&id=100:advanced-oracle-troubleshooting&catid=19:info&Itemid=71

Note that the Advanced Oracle Troubleshooting seminar is meant mainly for experienced DBAs and performance engineers as I go very deep in Oracle internals and OS/hardware details.

See more details about my seminar here

Conferences in 2009

11-12. February – RMOUG Conference @ Denver, Colorado

I will be delivering two presentations about advanced Oracle troubleshooting, tuning and internals:

• Advanced Oracle Troubleshooting: No Magic is Needed, Systematic Approach Will Do
• Understanding Oracle Execution Plans: How SQL is Really Executed

8-12. March – Hotsos Symposium @ Dallas, Texas

I will be delivering two presentations:

• Understanding Oracle SQL Plan Execution: How It Really Works
• Zero Slides: The Scripts and Tools which Will Make Your Life Easier and Allow You to Troubleshoot Better

As the second title says, I don’t use any slides at that presentation, I will just demo some of the scripts and tools I use every day plus few case studies. It will be fun!

Also, I look forward attending the Training Day by Jonathan Lewis on Oracle troubleshooting, on last day of the Symposium.

Tanel Poder Administration, Networking, Oracle, Oracle 11g, Performance, Productivity, Tools, Training, Troubleshooting

If you haven’t seen Dan Morgan’s Oracle library yet at http://www.psoug.org/library.html then now it’s time to do so!

I think what he’s done is awesome and I use his library almost every day when I don’t remember some syntax off the top of my head. I normally just google for keywords like “create hash cluster psoug” so I get the wanted page first in search results.

I’ve added the link into my blogroll.

Tanel Poder Administration, Blogroll, Cool stuff, Oracle, Oracle 11g, Productivity

I haven’t written a non-Oracle post into my blog yet, so here’s one for you :)

I recently developed a little web service with a friend. Shortly, check out http://n.otepad.com and any feedback is appreciated (especially about the parts which suck, so we could improve those :)

The longer story is that for years I used to have a notes.txt file on my Windows desktop (or Linux desktop, whatever I happened to use at that time) for writing down my notes, addresses, code snippets, URLs etc etc. I created a keyboard shortcut CTRL+ALT+N for my notes file, so I could easily open up the file without needing to navigate around with mouse or switching between applications. I could open the file and search its contents in matter of 2-3 seconds.

Then this notes file got too big, Windows XP’s notepad.exe started getting slow when I had more than 10000 lines of text in the file. So I split my notes up to notes.txt, oracle.txt, unix.txt, etc. Each had a different keyboard shortcut, like CTRL+ALT+O for Oracle stuff (damn, I just realized this post is loosely related to Oracle :)

Anyway, the obvious problem which came from that split was that often I didn’t remember into which note file I had put a particular note (e.g. some Unix script for Oracle could have been in Unix file or Oracle file). So I ended up looking through multiple files, getting frustrated and sometimes giving up.

Oh, did I mention that I also used to send emails with notes to myself, just to keep them (or maybe deal with them later). And then I went to client’s office and realized I couldn’t access any webmail through their proxies, so had to rewrite couple of scripts from scratch.

I guess you get the picture. Finding my old notes got ineffective, time consuming (and lame!). I wanted to find my notes in matter of couple seconds, not give up after minutes.

So we decided to write a solution for ourselves with a friend.

Read more…

Tanel Poder Cool stuff, Productivity, Tools