Network Monitoring Experimentations 2

16 12 2009

December 16, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

The first post in this series introduced Wireshark, demonstrated some of the problems that may be detected with the tool, demonstrated what happens with various fetch array sizes in the presence of configuration problems, and compared a low latency 100Mb connection with a higher latency wireless 802.11G (54Mb) connection from the same client computer during a transfer of database table data.

The connection attempt shown in the screen capture from the previous post was of the computer connecting through the wireless connection to a server during the launch of an ERP package. That connection attempt was slowed a bit by the network latency. That screen shot is shown below.

Why introduce the same screen shot again?  Let’s look at what happens sometimes when application versions are upgraded and in the process (sometimes undocumented) features are added to applications.  Take, for instance, this ERP program’s connection attempt that required at least 10 seconds to complete on a gigabit connection, when it is able to complete in less than a second on the much slower wireless connection.  What changed?  Taking a look at a couple of the screen shots of the slow connection attempt will help (the client computer is .185, the database server is .45, and the file server is .40):

In the above we see the client computer searching the file server for a file named DNSAPI.dll that did not exist.  Then we see a DNS lookup attempt for a device named (for those following along, this was caused by the TNS: keyword on the remotedbname= entry in the SQL.INI file).  Notice that the timestamp of the request is at 17:25:14.683144.  When the client is told by the DNS server for the second time that the name does not exist, the timestamp has advanced to 17:25:14.686069.  Next, the client tried to resolve the name TNS first by a direct WINS (NBNS) lookup and then by a WINS broadcast.  The timestamp of the next client activity after the WINS lookup is 17:25:16.967553, so roughly 2.3 seconds were wasted.  But, that is not the most significant lost time interval.

The above shows the actual connection starting at 17:25:16.988701 and completing at 17:25:17.001425, which is very quick, but are we done yet?

The above screen shot shows the client looking for a file named nethasp.ini on the file server, but is not able to find that file.  Important?

The above shows that the client finally gave up looking for the nethasp.ini file at 17:25:17:209969, and at 17:25:17:232671 started sending a series of four full broadcast packets, spaced roughly one second apart, to a destination port number that Wireshark has aliased as tcpnethaspsrv.  The client finally started communicating again with the database server at 17:25:21.344038.  So, between 17:25:14.686069 and 17:25:21.344038 the client mostly just sat idle waiting for a response that never came.  Yes, Wireshark ultimately identified the cause of the problem and helped find a course of action that was fully in place at 05:30:00 the next morning, with the new version of the ERP program starting in less than a second just like the old version.

Wireshark exposes a lot of detail from the packets that it captures.  Here are two screen shots of a network packet containing a SQL statement that was transmitted in clear text:

Notice that the raw packet data appears at the bottom and is expanded into human readable text in the upper pane.  Clicking a line in the upper pane (for instance one that shows the destination MAC address) shows that information in the raw packet data at the bottom of the window.

Network Monitoring Experimentations 1

15 12 2009

December 15, 2009 

(Forward to the Next Post in the Series)

Wireshark, formerly known as Ethereal, is a free network packet capture program for several operating system platforms.  Wireshark is able to reveal a wide range of network communication problems:

The problems might include self-inflicted problems that result from implementing changes to improve one performance problem, as suggested here: 
or here:

The problems might include client computers with 100Mb network cards connected into gigabit switches, which then results in unexpected packet retransmits until the client computer is moved to a 100Mb switch that then connects to the gigabit switch (this is a rare problem, somewhere I have a Wireshark capture that shows this behavior).

The problems might include failing network equipment or bad network wiring or excessive EMF in the environment that distorts traffic on CAT 5e, CAT 6, and wireless connections.

The problems might include a client application that unexpectedly takes 10 to 20 seconds to “log in” when it should take 1 second or less.

The problems might include issues with inappropriate fetch array sizes, poor choices for SDU size, forcing jumbo TCP/IP frames through intermediate network hardware that does not support frame sizes larger than roughly 1500 bytes.

The problems might include a high latency network or WAN connections.

First, let’s look at a Wireshark capture of a successful connection attempt from a client computer connected to the network by an 802.11G (54Mb) wireless connection:

There is nothing terribly out of the ordinary with the above.  The client computer in packet 1 sent an ARP broadcast packet to the network asking for the MAC address of the network card on the network that is associated with IP address, and the response should be returned to the client computer at IP address  Roughly 0.002 seconds later the client computer attempted to connect to the database server using the TNS protocol.  Roughly 0.1 seconds later the connection completed.  Roughly 0.06 seconds after the connection attempt finished, the client computer started sending queries to the database server.  There were a couple of delays between the submission of the SQL statement and the response from the server, such as the 0.12 second delay between packet 26 and 27, but nothing significant.

It is quite possible that network problems will occur, as in the following:

In the above, notice that the server (IP address is resending packets that it assumed were lost in transit due to the long delays between packets without receiving an ACK from the client computer (the ACK packet may have been lost).  Notice also the long delays between packets that might either be a symptom of network problems, or CPU/Wait Event that could be captured in a 10046 extended SQL trace.

Next, let’s take a look at the effects of adjusting the fetch array size (number of rows retrieved in each fetch call – ARRAYSIZE setting in SQL*Plus) when executing a SQL statement in SQL*Plus that selects from a table having an average row length of 245 bytes, with the client on a wired 100Mb connection, and with the standard Oracle SDU size.  The server is still at IP address, the client computer (same as used above) is now at IP address

Fetch Array Size 1:

Fetch Array Size 15:

Fetch Array Size 100:

Fetch Array Size 1000:

In the above, you might notice that after every two packets that are sent by the server, the client computer sends back an ACK packet – this is typical behavior.  So, what happens when someone “optimizes” the network card parameters?

Fetch Array Size 15 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

In the above, notice the number of packets transmitted before the client sends an ACK packet, and typically just before the client sends the ACK, there is a delay of roughly 0.2 seconds.  OK, a little slower.  So, what happens when we switch from the table with the average row length of 245 bytes to a table containing roughly 1MB to 2MB JPEG pictures?  Compare how long it takes to reach the 35th packet in the following two screenshots:

Fetch Array Size 100:

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

(Late Additions to the Post)

Fetch Array Size 100 – Table with Pictures (802.11G):

Fetch Array Size 100 – Table with Pictures, Optimized ACK (802.11G):

SQL*Plus SELECT from the table with the average row length of 245 bytes using the 802.11G (54Mb) connection:

Fetch Array Size 1 with “Optimized” ACK frequency:

Fetch Array Size 15 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):

Fetch Array Size 1000 with “Optimized” ACK frequency (Same Data):

Statspack/AWR Report Resources

14 12 2009

December 14, 2009

On July 15, 2009, the following request appeared on the OTN forums:

Please provide me with a good link for understanding the AWR report.

One of the responders in the thread provided a link to the following document:

– –

I spent about 10 minutes (OK, maybe 30-45 minutes) skim reading the 71 pages in that chapter. I found several items in the chapter that made me ask, is that correct? The dents in my desk from my forehead indicate that the author probably should have had someone else proofread the chapter. As the book was published in 2007, it should have been written to cover Oracle 10g R2 (10.2.0.x) – it did mention Oracle 10g R2. In my skim reading I found the following interesting quotes that I posted in that OTN thread without explanation as to why I selected the quotes (I thought someone would ask):

#1 Page 23:

“Some DBAs (usually those trying to sell you a tuning product) minimize the importance of hit ratios (proactive tuning) and focus completely on waits (reactive tuning), since focusing on waits is a great way to quickly solve the current burning problems. By monitoring the Instance Efficiency section (and using all of STATSPACK and Enterprise Manager), the DBA will combine reactive and proactive tuning and will find some problems before the users scream or wait events hit the top 5 list. Hit ratios are one important piece of the puzzle (so are waits).”

#2 Page 24:

“Hit ratios are a great barometer of the health of your system. A large increase or drop from day to day is an indicator of a major change that needs to be investigated.”

#3 Page 27:

“Try to cache small tables to avoid reading them into memory over and over again. Locate the data on disk systems that have either more disk caching or are buffered by the OS file system cache. DB_FILE_MULTIBLOCK_READ_COUNT can make full scans faster (but it could also influence Oracle to do more of them).”

#4 Page 42:

“Consistent gets: The number of blocks read from the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as logical reads (all reads cached in memory). These are usually the CURRENT version of the block, but it can also be a Consistent Read (CR) version.”

#5 Page 42:

“Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads” “The buffer hit ratio should be above 95 percent. If it is less than 95 percent, you should consider increasing the size of the data cache by increasing the DB_CACHE_SIZE initialization parameter (given that physical memory is available to do this).”

#6 Page 43:

“If chained rows are indicated, the problem needs to be fixed as soon as possible. Chained rows can cause severe degradation of performance if a large number of rows are chained.”

#7 Page 44:

“The parameter that can be set in the init.ora to help improve the read time is the DB_FILE_MULTIBLOCK_READ_COUNT parameter, which controls the number of blocks that can be read in one I/O when a full table scan is being performed. This can reduce the number of I/Os needed to scan a table, thus improving the performance of the full table scan. Unfortunately, the optimizer might do more full table scans as a result of setting DB_FILE_MULTIBLOCK_READ_COUNT (you don’t want this behavior), so you may also need to set the OPTIMIZER_INDEX_COST_ADJ to a number, such as 10, to eliminate this problem and drive the use of indexes.”

I did not really look at the screenshots, nor try to see whether the majority of the information in the chapter was correct. But now looking at the first picture on page 20, I see that the author decided to create an AWR/Statspack report for a 23.5 hour time period – a very bad idea as short-term, intense problems are completely lost in the averages of the long duration report. The author apparently never mentioned that this was a bad idea.

Why did I select the quotes?

#1 Hit ratios are mostly meaningless. If the buffer cache hit ratio is very close to 100% (or any other number, for that matter), is that good, bad, or unknown? I have examples that show a 100% buffer cache hit ratio, yet very, very bad performance was present. See these threads for discussions of the buffer cache hit ratio:

#2 If the buffer cache hit ratio gets closer to 100% is that good? If it falls closer to 80% is that bad? If remains the same is that good or bad? Maybe the value predicts the weather?

#3 Small tables repeatedly read by full tablescans are more likely to remain in the Oracle buffer cache by default. Moving the tables to different disks promotes “hot” disks and “cold” disks in the system, rather than spreading the read and write access evenly across all disks, as is the principle of the Oracle SAME configuration. As of Oracle 10g R2, the DB_FILE_MULTIBLOCK_READ_COUNT parameter should not be set – instead Oracle will automatically set it to a value which attempts to maximize the read size based on the operating system, typically 1MB, or a value of 128 with an 8KB block size. As of Oracle 10g R1 (10.1.x), CPU costing (NOWORKLOAD) is enabled by default. With WORKLOAD CPU costing, the MBRC value from SYS.AUX_STATS$ determines the calculated cost of a tablescan, not DB_FILE_MULTIBLOCK_READ_COUNT (DB_FILE_MULTIBLOCK_READ_COUNT may still have an effect if the DBA has not yet collected WORKLOAD CPU statistics with a command like the following:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60) 

#4 An absolutely confusing definition of consistent gets, which are different from current mode gets. The consistent get, while coming from memory, may have involved one or more physical disk reads. The “db block gets” statistic is actually the same thing as a current mode get. A consistent get attempts to build a version of a block as of a specific time, or more accurately, as of a specific SCN through the application of zero, one, two, or more undo blocks. The specific SCN to which the block is rolled back is dependent on the current isolation level specified for the session. A current mode get will contain both committed and uncommitted data since it is the version of the block as it exists at that instant. See these blog posts:

#5 Even if the buffer cache hit ratio were useful, that is not the correct formula to calculate the value. Per the Oracle 10g R2 Performance Tuning Guide (and the 11g R2 Performance Tuning Guide), the formula is:

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')

#6 Chained rows happen when a row is too large to fit in a single database block. It would be necessary to recreate the database with a larger block size, or add a new tablespace with a larger block size and make changes in the initialization parameters to set aside memory for the non-default block size and then move the objects into the new tablespace – this is not a recommended approach: 

#7 Init.ora files started falling out of favor with Oracle 9i R1 (9.0.1.x), instead replaced with spfiles that allow initialization parameters to be permanently modified with ALTER SYSTEM commands. In addition to controlling the maximum number of blocks read during a full table scan, the DB_FILE_MULTIBLOCK_READ_COUNT parameter also controls the maximum number of blocks read during a fast full index scan. Full tablescans, especially if they complete faster, are not necessarily evil. The need to adjust OPTIMIZER_INDEX_COST_ADJ parameter decreased significantly with the introduction of CPU costing (introduced with Oracle 9i, enabled by default with 10g). Setting OPTIMIZER_INDEX_COST_ADJ to a very low value, such as 10, decreases the calculated costs of index accesses to 10% of their original value – this means that two different index access methods could then have the same rounded calculated costs, yet have very different execution speeds.

After I wrote the above quotes from the link into the thread, I remembered that someone else provided a critical review of this document. Reading this review, I see that I missed a lot of less obvious problems with the book chapter (I admit looking for the low hanging fruit):

In case you are left wondering where to look for Statspack/AWR information, I suggest starting here when trying to learn how to read AWR/Statspack reports:

Other Resources:
Metalink Doc ID 228913.1 “Systemwide Tuning using STATSPACK Reports”
Metalink Doc ID 94224.1 “FAQ- Statspack Complete Reference”

Other Topics that Might be of Interest:
Metalink Doc ID 390374.1 “Oracle Performance Diagnostic Guide”
Metalink Doc ID 438452.1 “Performance Tools Quick Reference Guide”

Toy Project for Performance Tuning 2

13 12 2009

December 13, 2009

I previously mentioned that I created a “Toy” project for performance tuning, but only provided a couple screen captures and very little descriptive text:

The program is perhaps well beyond the “Toy” stage.  My program is not available for purchase, but I thought that I would share a couple of its features as an incentive for you to create something usable for yourself.  Below are a couple more screen captures of the program (with descriptions).

Configure Data Change Logging:

 The Data Change Logging feature is used to build SQL*Plus compatible scripts that create logging tables, triggers to populate the logging tables, and optionally a database schema and tablespace for storing the logged data.


Keyword Search:

The Search feature performs a keyword search on statistics, wait events, initialization parameters, hints, and other topic categories.  Such a search may reveal relationships between wait events, initialization parameters, and workarounds/solutions for various problems.


SQL Monitor:

The SQL Monitor feature examines the shared pool for potentially high load SQL statements.  Clicking on a row in the grid displays the SQL statement, while double-clicking a row displays the execution plan for all child cursors, the reason why the child cursor was created, and the defined bind variable types.  Placing a check in Log File writes the grid contents to a file on the next refresh, and if SQL Statements is also checked, the SQL statement will also be written to the log file.  CPU Time and Elapsed time are only available on Oracle 9i R2 and above.


10046 Trace File Parser:

The Drag & Drop Extended Trace feature performs extensive analysis of 10046 trace files that were captured at levels 4, 8, and 12.  A minimum of four time coordinated analysis files are generated for each trace file that is analyzed.  Several options are available to determine what type of data is retrieved from the trace file.

The wait events output is sent to Microsoft Excel.  The Trace Time column indicates the time offset from the start of the trace file at which the wait event appeared in the trace file.  The Wait Time column indicates the duration of the wait event in 1/1000th of a second.  The Wait Event column indicates the type of wait – a description of the wait event appears at the bottom of the wait events output.  The wait events Raw Details displays the wait event line from the trace without interpretation.  The Oracle Cursor Num column displays an identifier that can be used to relate the wait event back to a specific SQL statement in the other analysis files.

The SQL Parse Order analysis file lists each SQL statement in the order in which the application requested a parse call.  Parse, execute, fetch, bind variables, and row source execution plan for each SQL statement is listed together, along with a summary per SQL statement parsed.  Cursor number is retrieved directly from the 10046 trace file, and may not be directly dependent on application coding.  Ver number indicates the number of cursors that were parsed at the Cursor number through this point in the trace file.  Parse at indicates the time offset in seconds from the start of the trace file until the parse call was issued.  TD Prev indicates the time difference between the current SQL statement and the previous parse call.  EXECs indicates the total number of execution calls from the application for the current SQL statement.  FETCHs indicates the total number of number of fetch requests to retrieve rows from the database for the current SQL statement.  CPU S indicates the number of seconds of database server CPU time required for the SQL statement.  CLOCK S indicates the elapsed time for the SQL statement – the elapsed time will frequently differ from the CPU time, unless the server’s CPU is the only bottleneck in the database.   ROWS indicates the number of rows retrieved or affected by the SQL statement.  PHY RD BLKs indicates the number of blocks that had to be read from disk to satisfy the SQL statement.  CON RD BLKs (Mem) indicates the number of blocks read from memory in consistent read mode – a logical read, which is only roughly 100 times faster than a physical read due to overhead related to consistent reads.  CUR RD BLKs (Mem) indicates the number of blocks read in current mode, which is considerably less expensive that a consistent read.  SHARED POOL MISS indicates the number of times a hard parse was required – if during an execute or fetch call, such parses are expensive and may be partially caused by setting CURSOR_SHARING to SIMILAR.

The SQL Execution order analysis file outputs the SQL statements and bind variable values in the order of execution as the trace file is read.  Minimal diagnostic data is provided in this type of analysis file, although it may help to determine the looping structure of SQL statements that are executed, where the output of one SQL statement is fed in as the input for another SQL statement.

The Grouping Similar SQL statements analysis file attempts to group together SQL statements that are parsed multiple times, rather than only being parsed once and executed many times.  This type of analysis file accumulates the statistics for the similar SQL statements, indicating the percentage of the total execution time each group represents.  While individual executions may total only 0.01 seconds for a SQL statement, if the SQL statement is executed 30,000 times, the SQL statement should probably be analyzed to determine if its execution plan is as efficient as possible.  This analysis file allows one to drill down to the root cause of the problematic SQL statement that represents the greatest percentage of the total run time.  Wait events are summarized at the beginning of the file, as well as with each group of similar SQL statements.


Configure Session Tracing:

Session tracing is an important component of database tuning.  Enabling and disabling various types of traces is possible within the Hyper-Extended Performance Monitor.  Activated tracing of sessions is automatically disabled when the Hyper-Extended Performance Monitor program is closed.
10046 Trace: Performance specific to each SQL statement executed.
10053 Trace: Cost Based Optimizer decisions during hard parses.
10032 and 10033 Trace: Sort related trace files.
10104 Trace: Hash join related trace files.


Advanced Initialization Parameters:

The Advanced Init Parameter feature retrieves all system level and session level normal as well as hidden parameters in the Oracle database instance.  As a general rule, never modify the value of a hidden parameter (those that begin with _).  A brief description of each parameter is provided, and in many cases a longer description is presented at the bottom of the window.  Session specific parameters are displayed after the system parameters, with the session’s SID displayed in the Type column in the table.  All parameters are simultaneously output to a file in the C:\Oraclelog folder, with a name such as “All Initialization Parameters 200710021321.txt”.

Most of the hidden parameters are only visible to the SYS user.  As such, the Hyper-Extended Oracle Performance Monitor will prompt for the SYS password.  If the SYS password is not provided, only the non-hidden parameters will be presented.  Oracle has several hundred initialization parameters, many of which are hidden (those that begin with _ ) and should not be modified without the guidance of Oracle support.  Following the list of system wide parameters are query optimization parameters which are specific to individual sessions.


Smart Logging Configuration:

To decrease the database server’s CPU impact due to logging, logging intervals may be specified to occur less frequently than once every 60 seconds.  Additional options are available to allow the logging capture to start in response to triggering events, such as increased CPU activity, blocking locks, etc.

The program also accepts various command line arguments to control logging and exit the program after logging for a specified number of minutes.


Logging Summary While Capturing a Smart Log:

When logging is enabled, a quick overview screen is presented that shows a portion of what was logged during the previous time intervals.  Session Wait Reasons and Session Locks appear in the tables in real-time when they happen.


System-Wide Overview of Log Data:

The top wait events for the log interval are displayed at the upper left, a graphical display of the history for the selected statistic is displayed at the top center (blue indicates the current period), and a graphical display of the top 30 sessions contributing to the statistic is displayed at the top right.  The bottom left provides a description of the statistic and possible additional tips for addressing problems.  The middle section provides a listing of various performance statistics captured during the logging interval – click one of those statistics to set it as the current statistic.  When a significant event occurs, such as a spike in CPU usage, click the Investigate button to display additional statistics.


Session-Level Drill-Down into Logged Data:

Left-clicking a session’s bar in the Review Log Stats window causes the session specific performance statistics to be displayed for the time interval.


Investigating the Log Interval:

The Investigate Log Interval window provides additional logging detail for the current log interval, but it is possible to view the data for a range of log intervals.  The top left grid shows system-wide wait events encountered during the logging interval range.  Immediately below the system-wide wait events are the session level wait events.  Below the session level wait events are the session level wait event reasons that were identified during the logging.  These statistics may be used to identify data file/block level contention between sessions, hard parsing that causes excessive wait events, etc – more information is revealed by double-clicking a row.  The bottom left grid shows details of the sessions that were blocked during the specified time intervals – more information is revealed by double-clicking a row.  The top right grid shows data file activity during the specified time intervals – summary information is revealed by double-clicking a row.  Below the file activity is the rollback/undo activity during the specified time intervals.  Below the rollback/undo activity is the data segment changes, which shows expansion and contraction of indexes, tables, and other objects in the database during the logging interval.   Below the data segment changes are the data block wait statistics, which related to buffer busy wait events – more information is revealed by double-clicking a row.  The bottom right shows latch contention that occurred during the logging interval – significant latch contention can cause performance issues – more information is revealed by double-clicking a row.

Double-clicking a couple of the grids produces the following:

I wonder what that Interpret Statistics button does?


There are of course more screen captures, but those may wait for some other time.

Use VBS to Search for Oracle Books using Google’s Book Library

13 12 2009

December 12, 2009

Below is a somewhat complicated VBS script that interacts with Internet Explorer to submit a query to  Once Google prepares the web page, the VBS script parses the raw HTML code in the web page to generate a new web page containing the list of matching books supplied by Google.  Selecting a book and then clicking the View button opens that book on the Google books site.

The search keyword is specified on the objIESource.Navigate line at the end of the website address.  A plus sign should appear between each search keyword.  To exclude a particular word from the search, prefix the word with a minus sign, for example to search for the keywords Oracle and SQL, but exclude the word dummy:

objIESource.Navigate ""

Note that there are various ways to extend this example.

Dim objIE            'For the data entry form where we present the harvested book list
Dim objShell         'To add a delay, may throw an error when executed in a Visual macro
Dim strHTML          'Holds what we are displaying on our data entry form
Dim intFlag          'Indicates if the user clicked OK or closed the browser window
Dim i                'For our counter
Dim objIESource      'Holds the data source web page
Dim strHTMLSource    'Holds the HTML contents of the source web page
Dim intStart         'The starting position of title="  in the source web page
Dim intEnd           'The position of the next " after title="  in the source web page
Dim intApproxStart   'The book titles appear after the first entry of coverthumb, so we will start there
Dim strBook(1000)    'Holds the book titles found on this page
Dim intBookCount     'Counter for the number of books found
Dim strFind          'What to find in the HTML code which indicates that the data of interest will follow
Dim intFindLen       'The length of the what to find string
Dim intLinkStart     'The starting position of title="  in the source web page
Dim intLinkEnd       'The position of the next " after title="  in the source web page
Dim strBookLink(1000)'Holds the link to the book found on this page
Dim strLinkFind      'What to find in the HTML code which indicates that the page link will follow
Dim intLinkFindLen   'The length of the what to find link string
Dim strSelectedBook  'The name of the selected book
Dim strBookAddress   'The web address of the book
Dim adsFile          'Used if we want to write the downloaded web page to the hard drive

On Error Resume Next
'Set objShell = CreateObject("WScript.Shell")
Set objIESource = CreateObject("InternetExplorer.Application")
objIESource.Navigate ""
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"

For i = 1 to 10000
    'Give it some time to prepare the objIESource
Do While objIESource.Busy <> False
    'objShell.Sleep 500 'Edit: This line was supposed to be replaced with the following line Dec 13, 2009
    Wscript.Sleep 200

intBookCount = 0
strHTMLSource = cStr(objIESource.Document.Body.InnerHTML)    'Retrieve the raw HTML code from the web page

'Uncomment to save the web page to the hard drive
'Set adsFile = CreateObject("ADODB.Stream")
'adsFile.Type = 2
'adsFile.Charset = "iso-8859-1"
'adsFile.SaveToFile "c:\InnerHTML.txt", 2
'Set adsFile = Nothing

'In the same HTML page, the first book title starts after the first entry of: 
intApproxStart = InStr(strHTMLSource, "coverthumb") + 1
strFind = "title=" & Chr(34)
intFindLen = Len(strFind)
strLinkFind = "<A href=" & Chr(34)
intLinkFindLen = Len(strLinkFind)

'Find the start of the first book title
'Might be listed like this in the HTML code:  title="Excel 2007 VBA Programming For Dummies"
intStart = InStr(intApproxStart, strHTMLSource, strFind)

Do While intStart > 0
    'Find the end of the book title
    intEnd = InStr(intStart + intFindLen, strHTMLSource, Chr(34))
    If intEnd > 0 Then
        intBookCount = intBookCount + 1
        strBook(intBookCount) = Mid(strHTMLSource, intStart + intFindLen, intEnd - (intStart + intFindLen))

        'Find the link to the book title
        intLinkStart = InStr(intEnd, strHTMLSource, strLinkFind)
        If intLinkStart > 0 Then
            intLinkEnd = InStr(intLinkStart + intLinkFindLen, strHTMLSource, Chr(34))
            If intLinkEnd > 0 Then
                strBookLink(intBookCount) = Mid(strHTMLSource, intLinkStart + intLinkFindLen, intLinkEnd - (intLinkStart + intLinkFindLen))
                strBookLink(intBookCount) = ""
            End If
            strBookLink(intBookCount) = ""
        End If

        'Find the start of the next book title
        intStart = InStr(intEnd, strHTMLSource, strFind)
        Exit Do
    End If

'Edit: place a single quote in front of the following two lines to prevent the list of books from disappearing, Dec 13, 2009
Set objIESource = Nothing

strHTML = strHTML & "<form name=""BookFind"">" & vbCrLf
strHTML = strHTML & "<input type=hidden id=""txtOK"" value="" "">" & vbCrLf
strHTML = strHTML & "Book:<br /> <select size=""23"" id=""lstBooks"" style=""width:450"">" & vbCrLf

For i = 1 To intBookCount
    strHTML = strHTML & "<option value=" & Chr(34) & strBook(i) & Chr(34) & ">" & strBook(i) & "</option>" & vbCrLf
strHTML = strHTML & "</select>" & vbCrLf
strHTML = strHTML & "<p><center><input type=button value=""View"" id=""cmdOK"" onclick=""document.getElementById('txtOK').value='OK';""></center>" & vbCrLf
strHTML = strHTML & "</form>" & vbCrLf

objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Select Book Title from Google Books"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 500
objIE.Height = 520
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False
objIE.Visible = True

For i = 1 to 10000
    'Give it some time to prepare the objIE
Do While objIE.Busy <> False
    Wscript.Sleep 200

intFlag = 0
'loop until the button is clicked
Do While intFlag = 0
    If Err <> 0 Then
        intFlag = -1
    End If
    If objIE Is Nothing Then
        'User closed ID
        intFlag = -1
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    Wscript.Sleep 250

If intFlag = 1 Then
    'Copy in the values from the web page
    strSelectedBook = objIE.Document.Body.All.lstBooks.Value

    'Try to find the associated link to the book
    For i = 1 to intBookCount
        If strBook(i) = strSelectedBook Then
            'Found the book
            strBookAddress = strBookLink(i)
            'Extra credit - display the link associated with the selected book
            'Comment out the following objIE lines and uncomment the objIE.Quit
            '  if the link associated with the book should not be displayed
            objIE.Navigate "about:blank"
            objIE.Width = 800
            objIE.Height = 600
            objIE.Statusbar = True
            objIE.Menubar = True
            objIE.Toolbar = True
            objIE.Navigate strBookLink(i)
            Exit For
        End If
End If

Set objIE = Nothing
Set objShell = Nothing

The list of results:

The selected book:

Find the DB Names on a Remote Windows Server using VBS

12 12 2009

December 12, 2009

The following question appeared in one of the OTN forums a couple months ago: 

I am new in this database field.
I have a small query that how can i find the number of database running on a particular host in windows environment by query?

 There is no query in Oracle which will show you the database instances on a Windows server. That said, here is a portion (adaptation) of a WMI script from the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table” which will do it for you as long as the database instance is started (even on a remote server if you have administrator privileges on the remote server):

Dim objWMIService
Dim strSQL
Dim strComputer
Dim colItems
Dim objItem

strSQL = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

For Each objItem in colItems
  Wscript.Echo "Instance: " & objItem.CommandLine & vbCrLf

To run the above script, save it as a text file with the extension .VBS. Open a Windows command prompt, and type cscript followed by the name of the script:

C:\> cscript c:\databases.vbs

Note that you must be an administrator on the remote computer (or a domain administrator) to remotely query the running processes on another Windows computer. 

Print Oracle Data to a Barcode Label with a Zebra Printer using VBS and Excel

12 12 2009

December 12, 2009

Here is the problem: You know how to query the database using a Windows Scripting Host VBS file, but you have no way to print a barcode label using the data to the networked Zebra label printer and there is no printer driver installed for the Zebra printer.  Fortunately, Zebra printers understand ZPL (Zebra Printer Langauge), so we are able to easily work around the issue of not having a driver for the printer installed.  The second problem is how do we send the print job to the printer?  This is where Excel comes to the rescue.  Excel is able to work with the Windows API to start and submit a print job to a printer that is not necessarily installed on the local computer.

First, the Windows Scripting Host VBS file (note that this uses a DLL that I wrote which hides the database name, username, and password, as well as simplifying the process of submitting SQL statements with bind variables to the database):

Dim intParentPartLine
Dim strRawText
Dim strSQL
Dim strWhereUsed
Dim snpData
Dim OracleSQL
Dim objExcel

On Error Resume Next

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor") 'This is my custom DLL
Set snpData = CreateObject("ADODB.Recordset")
Set objExcel = CreateObject("Excel.Application")

strOut = ""
With objExcel
    'Open the Excel file containing the macro functions
    .Workbooks.Open "C:\ExcelMacroFunction.xls"

    strRawText = "~SD25^XA" 'Set Darkness, Label start
    strRawText = strRawText & "^SZ2" 'Enable ZPL2
    strRawText = strRawText & "^JZ" 'Reprint on error
    strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    strRawText = strRawText & "^LH12,30" 'Label home position in pixels
    strRawText = strRawText & "^FO5,0^A0,40,40^FD" & ID & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,40^A0,20,20^FD" & DESCRIPTION & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,80^A0,15,15^FD**** WHERE USED ****^FS" ' 'Proportional font

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID AS TOP_PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID AS SUB_PART_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  REQUIREMENT R," & vbCrLf
    strSQL = strSQL & "  WORK_ORDER WO" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  WO.TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.PART_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE=WO.TYPE" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_LOT_ID=WO.LOT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SUB_ID=WO.SUB_ID" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID"
    OracleSQL.SQL = strSQL
    OracleSQL.SetParameter ID, "VARCHAR"

    Set snpData = OracleSQL.Execute

    If Not (snpData Is Nothing) Then
        If Not (snpData.EOF) Then
            intParentPartLine = 0
            Do While Not (snpData.EOF)
                intParentPartLine = intParentPartLine + 1
                If snpData("top_part_id") <> snpData("sub_part_id") Then
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id")) & " (Sub " & cStr(snpData("sub_part_id")) & ")"
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id"))
                End If
                strRawText = strRawText & "^FO10," & cStr(80 + 25 * intParentPartLine) & "^A0,25,25^FD" & strWhereUsed & "^FS" 'Proportional font
                strWhereUsed  = ""

        End If

    End If

    strRawText = strRawText & "^FO20,562^AF^FDMy Company Here^FS"
    strRawText = strRawText & "^XZ"  'End of label indicator

    'Excute a macro located in mdlGlobal that prints a label using API calls
                                 'Subroutine, printer device name, raw ZPL code

    'Print to a shared Zebra printer named ZEBRA on computer named KMMACH98
    'strResult = .Application.Run("ZebraPrintLabel", "\\KMMACH98\ZEBRA", strRawText)

    'Print to a "Local" printer named ZEBRA_M1 with a redirected LTP2 port with NET USE
    strResult = .Application.Run("ZebraPrintLabel", "ZEBRA_M1", strRawText)

    .DisplayAlerts = False
    .ActiveWorkbook.Saved = True
End With

Set objExcel = Nothing 
Set OracleSQL = Nothing
Set snpData = Nothing
'End of PrintLabel.vbs

The above script expects to find an Excel spreadsheet named C:\ExcelMacroFunction.xls with a public subroutine named  ZebraPrintLabel in a regular Excel code module.

The code in the Excel code module looks like this:

'mdlGlobal in C:\ExcelMacroFunction.xls - a global module
Option Explicit

'Type declaration for Zebra label printing
Public Type DOC_INFO_1
    pDocName As String
    pOutputFile As String
    pDatatype As String
End Type

'Zebra Printing API functions
Public Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndDocPrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, ByVal pDefault As Long) As Long
Public Declare Function StartDocPrinter Lib "winspool.drv" Alias "StartDocPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pDocInfo As DOC_INFO_1) As Long
Public Declare Function StartPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function WritePrinter Lib "winspool.drv" (ByVal hPrinter As Long, pBuf As Any, ByVal cdBuf As Long, pcWritten As Long) As Long

Public Sub ZebraPrintLabel(strPrinter As Variant, strRawText As Variant)
    'Variables for handling printing
    Dim i As Integer
    Dim lngPrinterHandle As Long
    Dim lngResult As Long
    Dim lngWritten As Long
    Dim lngPrinterDocHandle As Long
    Dim bytRawText() As Byte
    Dim MyDocInfo As DOC_INFO_1

    On Error Resume Next

    'In VB6 to see the list of printer device names, enter the following into the Debug window
    'For i = 0 to Printers.Count - 1:Debug.Print Printers(i).DeviceName:Next

    'Sample label for testing using just Excel
    'strPrinter = "\\KMMACH98\ZEBRA"
    'strRawText = "~SD25^XA" 'Set Darkness, Label start
    'strRawText = strRawText & "^SZ2" 'Enable ZPL2
    'strRawText = strRawText & "^JZ" 'Reprint on error
    'strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    'strRawText = strRawText & "^LH10,26" 'Label home position in pixels
    'strRawText = strRawText & "^FO2,14^A0R,20,20^FDMy Company Here^FS" 'rotated text in font A
    'strRawText = strRawText & "^FO2,480^A0R,20,20^FDSomwhere, USA^FS"
    'strRawText = strRawText & "^FO180,135^B3R,,105,N^FD" & "ABC111" & "^FS"  'Font 3 of 9 barcode
    'strRawText = strRawText & "^FO180,0^GB0,760,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO335,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO550,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO260,760^GB0,452,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO0,760^GB335,0,3^FS"  'Horizontal Line 3 pixels wide
    'strRawText = strRawText & "^XZ"  'End of label indicator
    'Convert the variant data types to strings
    strPrinter = CStr(strPrinter)
    strRawText = CStr(strRawText)

    'Terminate the string with a CRLF combination (may not be needed)
    If Right(strRawText, 2) <> vbCrLf Then
        strRawText = strRawText & vbCrLf
    End If

    'Convert the strRawText string to a byte stream
    ReDim bytRawText(1 To Len(strRawText))
    For i = 1 To Len(strRawText)
        bytRawText(i) = Asc(Mid(strRawText, i, 1))
    Next i

    'Create a connection to the printer, returns a handle to the printer
    lngResult = OpenPrinter(strPrinter, lngPrinterHandle, 0)

    If lngPrinterHandle = 0 Then
        MsgBox "Could Not Open Printer"
        Exit Sub
    End If

    'Fill in the document header structure
    MyDocInfo.pDocName = "Zebra Label from Excel"
    MyDocInfo.pOutputFile = vbNullString
    MyDocInfo.pDatatype = "RAW"
    lngPrinterDocHandle = StartDocPrinter(lngPrinterHandle, 1, MyDocInfo)

    If lngPrinterDocHandle = 0 Then
        MsgBox "Could Not Start the Document"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'Prepare to start the first page
    Call StartPagePrinter(lngPrinterHandle)

    'Write out the contents of the first page
    lngResult = WritePrinter(lngPrinterHandle, bytRawText(1), Len(strRawText), lngWritten)
    If lngResult = 0 Then
        MsgBox "Could Not Write to the Page"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'End the first page
    lngResult = EndPagePrinter(lngPrinterHandle)

    'End the document
    lngResult = EndDocPrinter(lngPrinterHandle)

    'Close the connection to the printet
    lngResult = ClosePrinter(lngPrinterHandle)
End Sub
'End of mdlGlobal

Note that while the above uses a custom DLL to connect to the database and submit SQL statements, the Simple VBS Script to Retrieve Data from Oracle blog entry shows how to do the same without using the custom DLL.  Also, it probably is not necessary to convert the Unicode string that is passes into the procedure into a byte stream for the Windows API call.

The printed label appears below: