SQL*Plus & Command History with Cygwin + rlwrap

Although nearly all apps today are use some sort of point-click, windowing front end, there are plenty of us who still use the command-line when available.  It's not that we're set in our ways, it's that for many repetative tasks, the command-line provides us with enough shortcuts that we're just way more efficient with a few key strokes.

This brings me to Oracle's SQL*Plus, or more specifically, running "sqlplus" from the command-line.  "sqlplus" has long provided the ability to edit the previous command in it's buffer, either pulling it up into an editor or using a few commands to change it in place (such as "CHANGE//").  But obviously this is limited.

In comes "rlwrap".  This application is also nothing new, but did you know it's available from within Cygwin?  This issue came up during Tanel Poder's recent Training Day at Hotsos Symposium 2010.  He gave some great examples of "rlwrap"'s power using it from Linux, then laid the gauntlet down by saying it should work under Cygwin, but he hasn't tried it and we should let him know if we can get it working.  That was enough for me!

I've used Cygwin for some time as a way to start an XWin server, in support of other apps that require it (such as Oracle's installer).  I reviewed the my installation source (x.cygwin.com) and ran the installation again to see if "rlwrap" is available.  Sure enough, if you change the View to be by Category, then you'll file "rlwrap" under Utils:

In the screenshot above you can see "rlwrap" is already installed, but this was taken from my current install, so you get the idea.

The installation process dumps "rlwrap" under c:\cygwin\bin by default.  Now comes the power of the tool!

From your PC, open a Cygwin XWin server to work off of, then an xterm from this XWin server session.  From the xterm session you can fire off your "rlwrap" commands.  You can run them from the XWin server session, but I prefer to instead use that as a central session, firing off separate xterm sessions per connection I plan on making.  In my $HOME directory I created a setup script which defines a series of aliases, one per color-coded xterm screen:

alias black="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg black -fg gainsboro &"

alias brown="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg brown -fg gainsboro &"

alias blue="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkblue -fg gainsboro &"

alias darkred="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkred -fg gainsboro &"

alias green="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg green4 -fg gainsboro &"

alias purple="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg purple4 -fg gainsboro &"

alias teal="xterm  -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg darkcyan -fg gainsboro &"

alias white="xterm -fn '-adobe-courier-*-r-*-*-12-*-*-*-*-*-10' -ls -geometry 140x35+50+20 -bg white -fg black &"

This way typing "blue", for example, quickly gives me a new blue background, white foreground xterm.

Back to "rlwrap" power.  A simple example of the command is as follows:

rlwrap -r sqlplus dherri@ORCL1

Within this sqlplus session I now have full command-line history using the arrow keys.  Plus with the "-r" argument, "rlwrap" will "remember", so to speak, all text that passes on the screen.  What this means is through the TAB key you can now quickly access output from previous queries.  For example, suppose you wanted to query an AWR view but couldn't remember the name.  So you enter:

SELECT view_name

  FROM dba_views

 WHERE view_name LIKE 'DBA_HIST%'

 ORDER BY 1;

Now the output list of view names is in rlwrap's buffer, which means the view names are available for command completion.  If you enter:

SELECT * FROM DBA_HIST_SQL

… and hit TAB twice, you'll get the following displayed:

DBA_HIST_SQLBIND              DBA_HIST_SQLTEXT              DBA_HIST_SQL_PLAN             DBA_HIST_SQL_WORKAREA_HSTGRM

DBA_HIST_SQLSTAT              DBA_HIST_SQL_BIND_METADATA    DBA_HIST_SQL_SUMMARY    

This is just a small sampling of what "rlwrap" can provide in terms of added efficiency within sqlplus command-line or for nearly any app for that matter (RMAN quickly comes to mind).  The main point I'm raising here is Windows users need not be excluded from the benefits of command-line completion using "rlwrap".

For more detail on "rlwrap" and excellent examples of applying its use, see Tanel Poder's blog @ blog.tanelpoder.com or his new tech site @ tech.e2sn.com.

Parallelism "Secretly" Disabled by User Function

Ever work on a problem where initial observation and past experience says "1 + 1 = 2" yet it keeps coming out "3"? I recently ran into a situation like this, where all past experience told me that the query should run with parallelism and even the xplan showed it would, yet during the execution of the query I could tell it was running serially.

A simplified version of the original query is listed below, involving 3 tables that all had a parallel degree of 8:

SELECT std_function_pkg.domain_group_fnc(dn.domain, dn.domain_suffix) 

     , MAX(CASE WHEN event_type = 'C' THEN e.event_date ELSE NULL END) latest_type

  FROM dn_tb dn LEFT OUTER JOIN vlm_sum_tb vlm

          ON (dn.member_id = vlm.member_id)

             LEFT OUTER JOIN

       event_tb PARTITION (v8) e

          ON (    dn.member_id = e.member_id

              AND e.vendor_id = 13569

              AND e.event_type IN ('O','C'))

 GROUP BY std_function_pkg.domain_group_fnc(dn.domain, dn.domain_suffix);

 

This query yields an xplan as follows:

--------------------------------------------------------------------------------------------------------------------------------------
| Id|Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  TQ  |IN-OUT| PQ Distrib
--------------------------------------------------------------------------------------------------------------------------------------
|  0|SELECT STATEMENT              |                | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |      |      |
|  1| PX COORDINATOR FORCED SERIAL |                |       |       |            |          |       |       |      |      |
|  2|  PX SEND QC (RANDOM)         | :TQ10003       | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |Q1,03 | P->S |QC (RAND)
|  3|   SORT GROUP BY              |                | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |Q1,03 | PCWP |
|  4|    PX RECEIVE                |                | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |Q1,03 | PCWP |
|  5|     PX SEND HASH             | :TQ10002       | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |Q1,02 | P->P | HASH
|  6|      SORT GROUP BY           |                | 14960 |   613K| 83941   (5)| 00:16:48 |       |       |Q1,02 | PCWP |
|  7|       NESTED LOOPS OUTER     |                |  4568K|   182M| 83713   (4)| 00:16:45 |       |       |Q1,02 | PCWP |
|* 8|        HASH JOIN RIGHT OUTER |                |   253K|  6444K|    54   (6)| 00:00:01 |       |       |Q1,02 | PCWP |
|  9|         PX RECEIVE           |                |   286K|  1676K|     7   (0)| 00:00:01 |       |       |Q1,02 | PCWP |
| 10|          PX SEND HASH        | :TQ10000       |   286K|  1676K|     7   (0)| 00:00:01 |       |       |Q1,00 | P->P | HASH
| 11|           PX BLOCK ITERATOR  |                |   286K|  1676K|     7   (0)| 00:00:01 |     1 |    16 |Q1,00 | PCWC |
| 12|            TABLE ACCESS FULL | VLM_SUM_TB     |   286K|  1676K|     7   (0)| 00:00:01 |     1 |    16 |Q1,00 | PCWP |
| 13|         PX RECEIVE           |                |   253K|  4957K|    46   (5)| 00:00:01 |       |       |Q1,02 | PCWP |
| 14|          PX SEND HASH        | :TQ10001       |   253K|  4957K|    46   (5)| 00:00:01 |       |       |Q1,01 | P->P | HASH
| 15|           PX BLOCK ITERATOR  |                |   253K|  4957K|    46   (5)| 00:00:01 |     1 |    16 |Q1,01 | PCWC |
| 16|            TABLE ACCESS FULL | DN_TB          |   253K|  4957K|    46   (5)| 00:00:01 |     1 |    16 |Q1,01 | PCWP |
| 17|        PARTITION RANGE SINGLE|                |    18 |   288 |            |          |     8 |     8 |Q1,02 | PCWP |
| 18|         PARTITION LIST INLIST|                |    18 |   288 |            |          |KEY(I) |KEY(I) |Q1,02 | PCWP |
| 19|          VIEW                |                |    18 |   288 |            |          |       |       |Q1,02 | PCWP |
|*20|           TABLE ACCESS FULL  | EVENT_TB       |    18 |   324 | 51903   (7)| 00:10:23 |KEY(I) |KEY(I) |Q1,02 | PCWP |
--------------------------------------------------------------------------------------------------------------------------------------

As you can see from the xplan, the query should use parallelism. Yet when monitoring the system while the query was running, I saw that V$PX_SESSION returned 0 rows (meaning no PX slaves were active) and that the only session involved in the query was generating "db file scattered read" waits, which is a non-parallel read wait.

I ended up taking the trial and error approach, finding a basic version of the query that ran properly with parallelism, then added back items from the column list and GROUP BY section until parallelism was removed.

It turns out that the call to the user function DOMAIN_GROUP_FNC was the culprit. From there I found out, by isolating the function from the package, that the function itself wasn't the problem but definitions for the function WITHIN the package was the problem.

Within the package STD_FUNCTION_PKG, the function declaration was followed by the directive: pragma restrict_references(domain_group_fnc, WNPS); I don't know the original intent of the programmers who added this, but for a parallel query, this won't work since the documentation clearly states that all four constraints (RNDS, RNPS, WNDS, WNPS) must be specified when using a parallel query. Plus, the clause PARALLEL_ENABLE needs to be added when the function is contained within a package (for that function to allow the query to run with parallelism).

In the end, it points out that wait events are not only key for resolving performance issues, but an excellent method for determining what the query is really doing.