Toy Project for Performance Tuning 2

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:
http://hoopercharles.wordpress.com/2009/11/30/toy-project-for-performance-tuning/

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

December 12, 2009

Below is a somewhat complicated VBS script that interacts with Internet Explorer to submit a query to books.google.com.  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 "http://books.google.com/books?um=1&q=oracle+sql+-dummy"

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 "http://books.google.com/books?um=1&q=oracle+tuning"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"

For i = 1 to 10000
    'Give it some time to prepare the objIESource
Next
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
Loop

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.Open
'adsFile.WriteText(strHTMLSource)
'adsFile.SaveToFile "c:\InnerHTML.txt", 2
'adsFile.close
'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))
            Else
                strBookLink(intBookCount) = ""
            End If
        Else
            strBookLink(intBookCount) = ""
        End If

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

'Edit: place a single quote in front of the following two lines to prevent the list of books from disappearing, Dec 13, 2009
objIESource.Quit
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
Next
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
Next
Do While objIE.Busy <> False
    Wscript.Sleep 200
Loop

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
    Else
        If objIE.Document.All.txtOK.Value <> " " Then
            intFlag = 1
        End If
    End If
    Wscript.Sleep 250
Loop

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
    Next
    'objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

The list of results:

The selected book: