Excel – Session Viewer with Query Capability

January 16, 2010

This is a simple example that shows how to query an Oracle database using user input, passing in the user specified values with bind variables.  While this example just queries V$SESSION, it is possible to expand this demonstration considerably to allow Excel to act as a command center for viewing SQL statements executed by sessions (with their execution plans), enable 10046 traces, and more.

To begin, we need to create two ActiveX command buttons in cells A1 through A3.  Name the top command button cmdInitialize, and the bottom button cmdFind.

Next, name the worksheet as DatabaseInfo, then right-click the worksheet DatabaseInfo tab name and select View Code.  Once in the Visual Basic editor, add a reference to the Microsoft ActiveX Data Objects, as demonstrated here.

Now, we need to add the code to make the cmdInitialize button work:

Option Explicit 'Forces all variables to be declared

Dim dbDatabase As New ADODB.Connection
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String

Dim intColumns As Integer
Dim strLastColumn As String

Private Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbDatabase.State <> 1 Then
        'Connection to the database if closed
        strDatabase = "MyDB"
        strUserName = "MyUser"
        strPassword = "MyPassword"

        'Connect to the database
        'Oracle connection string
        dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

        dbDatabase.ConnectionTimeout = 40
        dbDatabase.CursorLocation = adUseClient
        dbDatabase.Open

        If (dbDatabase.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")

            ConnectDatabase = False
        Else
            ConnectDatabase = True
        End If
    Else
        ConnectDatabase = True
    End If
End Function

Private Sub cmdInitialize_Click()
    Dim i As Integer
    Dim intResult As Integer
    Dim strSQL As String
    Dim snpData As ADODB.Recordset

    'Don't allow Excel to display an error message on the screen if an error happens while executing this
    '  procedure, we will handle the problem in-line in the code
    On Error Resume Next

    'Jump to our ConnectDatabase function which returns a value of True if we are connected to the database
    '  or False if the connection attempt failed
    intResult = ConnectDatabase

    'If we could not connect to the database, display a message for the user that something is wrong and stop
    '  the execution of the code in this module
    If intResult = False Then
        Exit Sub
    End If

    'Create the ADO object which will be used to retrieve the data from the database
    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf    'Retrieve all columns from the table without listing the columns
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  V$SESSION" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
  'ROWNUM is an Oracle only function - each row returned is assigned an increasing sequential value,
  '  essentially, I am telling Oracle to not retrieve any rows, as I am just interested in the column name
  '  and the data types in the PART table.  Using 0=1  in place of ROWNUM<1 will likely work on other database
  '  platforms.
    strSQL = strSQL & "  ROWNUM<1"

    'Pass the SQL statement into our database connection to return the matching rows
    snpData.Open strSQL, dbDatabase

    'Always verify that the SQL statement was able to be executed, and that the database server did not simply
    '  return an error message.  Failing to perform the check could result in a situation where the macro
    '  becomes stuck in an infinite loop.  State = 1 indicates that the SQL statement was executed, and that
    '  the recordset is available for use, but does not necessarily mean that there are any rows in the
    '  recordset
    If snpData.State = 1 Then
        For i = 0 To snpData.Fields.Count - 1
            'Let's try to determine the type of data that may be stored in the database column and output that
            '  to the Excel spreadsheet.  Doing so will help the user, and it will help the cmdFind_Click
            '  procedure determine how the bind variables should be set up
            Select Case snpData.Fields(i).Type
                Case adVarChar
                    'A string of characters
                    ActiveSheet.Cells(1, i + 2).Value = "String"
                Case adChar
                    'A fixed length string of characters, where values are padded with spaces as needed
                    ActiveSheet.Cells(1, i + 2).Value = "Character"
                Case adDate, 135
                    'A column which may contain date and time information
                    ActiveSheet.Cells(1, i + 2).Value = "Date"
                Case adNumeric, adSingle, adInteger, adDouble, 139
                    'A column which may contain integers, floating point numbers, but not imaginary numbers
                    ActiveSheet.Cells(1, i + 2).Value = "Number"
                Case Else
                    'What should we do with these types of columns, are they BLOBs, RAWs?
                    ActiveSheet.Cells(1, i + 2).Value = snpData.Fields(i).Type
            End Select
            'Output the name of the column on the second row in the spreadsheet
            ActiveSheet.Cells(2, i + 2).Value = snpData.Fields(i).Name
            'Blank out the third row in the spreadsheet so that the user may specify how the rows returned
            '  by the SQL statement should be restricted on that row
            ActiveSheet.Cells(3, i + 2).Value = ""
        Next i
        'Record the number of columns in the PART table for future reference
        intColumns = snpData.Fields.Count

        'Just for fun, output to the Debug window (View menu - Immediate Window the Excel column names
        '  for the 26th through the 100th columns in the spreadsheet just to make certain that our interesting
        '  looking formula below is working correctly
        For i = 26 To 100
            Debug.Print i, Chr(64 + Int((i + 2) / 26)); Chr(64 + ((i + 2) Mod 26 + 1))
        Next i
        'Chr returns the character represented by the ASCII/ANSI value specified.  An uppercase letter A has
        '  an ASCII/ANSI value of 65, so the first column based on the formula would be Chr(64 + 1) = A
        'Mod is a function which returns the remainder after a number is divided by another number
        '  28 Mod 26 would equal 2  as 28/26 = 1 with a remainder of 2, are to be mathematically fancy:
        '  (28 / 26 - Int(28 / 26)) * 26
        '  Thus Mod produces a repeating sequence from 0 to one less than the number following the word Mod
        strLastColumn = Chr(64 + Int((intColumns + 2) / 26)) & Chr(64 + ((intColumns + 2) Mod 26 + 1))

        'Close the ADO recordset to free up memory on the database server since we are done using the data
        snpData.Close

        'Make certain that the full column names of the various columns in the PART table are visible in the
        '  spreadsheet
        ActiveSheet.Columns("A:" & strLastColumn).AutoFit
    End If

    'Erase any rows in the spreadsheet that may have been left by a previous execution of the cmdFind code
    Worksheets("DatabaseInfo").Range("4:50000").Delete Shift:=xlUp

    'Remove the ADO recordset object that we created earlier from memory - in theory this happens automatically
    '  but it is good practice to explicitly perform the operation
    Set snpData = Nothing
End Sub 

Now, switch back to the Excel window to verify that the cmdInitialize button works correctly.  You should see something like this:

Notice that row 1 in the worksheet shows the data type of the columns in the V$SESSION view (204 is a RAW data type) and row 2 in the worksheet shows the column names.  Row 3 will be used to allow the user to restrict the rows that will be returned.  Next, we need to add the code to the cmdFind button.  Switch back to the Visual Basic editor and add the following code:

Private Sub cmdFind_Click()
    Dim i As Integer
    Dim lngRow As Long
    Dim strSQL As String
    Dim snpData As ADODB.Recordset
    Dim comData As ADODB.Command

    'Create the in-memory ADO objects that will be used to return the data from the PART table
    Set snpData = New ADODB.Recordset
    Set comData = New ADODB.Command

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  V$SESSION" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  1=1" & vbCrLf

        'Walk through the columns to determine which have restrictions placed on them by the user
        For i = 1 To intColumns
            If ActiveSheet.Cells(3, i).Value <> "" Then
                'The user placed a striction on this column
                If (InStr(ActiveSheet.Cells(3, i).Value, "%") > 0) Or (InStr(ActiveSheet.Cells(3, i).Value, "_") > 0) Then
                    'Partial match, the column name is in row 2 of the spreadsheet
                    strSQL = strSQL & "  AND " & ActiveSheet.Cells(2, i).Value & " LIKE ?" & vbCrLf
                    'We need to look in row 1 for the data type of the column and set up an appropriate bind
                    '  variable data type to pass in the restriction requested by the user
                    'Each bind variable must have a unique name, so we generate one as  ValueCol#
                    Select Case ActiveSheet.Cells(1, i).Value
                        Case "String"
                            .Parameters.Append .CreateParameter("value" & Format(i), adVarChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Character"
                            .Parameters.Append .CreateParameter("value" & Format(i), adChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Number"
                            'A partial match on a number is not possible, just including to see what happens
                            .Parameters.Append .CreateParameter("value" & Format(i), adNumeric, adParamInput, 12, ActiveSheet.Cells(3, i).Value)
                        Case "Date"
                            'A partial match on a date is not possible, just including to see what happens
                            .Parameters.Append .CreateParameter("value" & Format(i), adDate, adParamInput, 8, CDate(ActiveSheet.Cells(3, i).Value))
                    End Select
                Else
                    'Full match, the column name is in row 2 of the spreadsheet
                    strSQL = strSQL & "  AND " & ActiveSheet.Cells(2, i).Value & " = ?" & vbCrLf
                    'We need to look in row 1 for the data type of the column and set up an appropriate bind
                    '  variable data type to pass in the restriction requested by the user
                    'Each bind variable must have a unique name, so we generate one as  ValueCol#
                    Select Case ActiveSheet.Cells(1, i).Value
                        Case "String"
                            .Parameters.Append .CreateParameter("value" & Format(i), adVarChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Character"
                            .Parameters.Append .CreateParameter("value" & Format(i), adChar, adParamInput, Len(ActiveSheet.Cells(3, i).Value), ActiveSheet.Cells(3, i).Value)
                        Case "Number"
                            .Parameters.Append .CreateParameter("value" & Format(i), adNumeric, adParamInput, 12, ActiveSheet.Cells(3, i).Value)
                        Case "Date"
                            .Parameters.Append .CreateParameter("value" & Format(i), adDate, adParamInput, 8, CDate(ActiveSheet.Cells(3, i).Value))
                    End Select
                End If
            End If
        Next i
        'We will sort the rows by the part ID
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  SID"

        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30

        .ActiveConnection = dbDatabase
    End With

    Set snpData = comData.Execute

    lngRow = 3 'We will start outputting at row 4, so 3 is our "0" line - the starting point

    'The slow way to populate the cells
'    If Not (snpData Is Nothing) Then
'        Do While Not snpData.EOF
'            'Increase the row number so that we do not output all of the information on the same row of the
'            '  spreadsheet
'            lngRow = lngRow + 1
'            'Output the data returned by the SQL statement, one column at a time.  The first column is in the
'            '  0 position, and the last column is one less than the total number of columns returned
'            For i = 0 To snpData.Fields.Count - 1
'                ActiveSheet.Cells(lngRow, i + 2).Value = snpData.Fields(i)
'            Next i
'            snpData.MoveNext
'        Loop
'
'        snpData.Close
'    End If
'
'    'Do we have extra rows left over from the last run?  If so, delete all rows below the last row that we output
'    Worksheets("DatabaseInfo").Range(Format(lngRow + 1) & ":50000").Delete Shift:=xlUp

    'The fast way to place the query results into cells   
    Worksheets("DatabaseInfo").Range(Format(lngRow + 1) & ":50000").Delete Shift:=xlUp
    If Not (snpData Is Nothing) Then

        ActiveSheet.Range("B4").CopyFromRecordset snpData

        ActiveSheet.Range("B4").Select

        snpData.Close
    End If

    'Tell Excel to fix the column widths so that all of the data returned in each column is visible
    'We recorded the value of strLastColumn in the initialize procedure
    ActiveSheet.Columns("B:" & strLastColumn).AutoFit

    'Memory clean up
    Set snpData = Nothing
    Set comData = Nothing
End Sub

Switch back to the Excel worksheet and test the cmdFind button.  You should see something like this:

Next, try to enter a search keyword in row 3 – if a wildcard character ( % or _ ) is used, the query will use a LIKE keyword, rather than an = operator.  After entering the search criteria, click the Find button:

There is no need to stop at this point.  It is easy to add a UserForm to the Excel workbook, for example something like this from another demonstration: 

For example, to enable a trace for a session, you could create a function like this:

Sub SetTraceInSession(lngSID As Long, lngSerial As Long, lngTrace As Long, lngTraceLevel As Long)
    Dim cmdTrace As New ADODB.Command
    Dim strSQL As String

    On Error Resume Next

    With cmdTrace
        strSQL = "SYS.DBMS_SYSTEM.SET_EV(" & Format(lngSID) & "," & Format(lngSerial) & "," & Format(lngTrace) & "," & Format(lngTraceLevel) & ",'')"
        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .ActiveConnection = dbDatabase
    End With

    cmdTrace.Execute

    Set cmdTrace = Nothing
End Sub

Book Library – Finding Motivation

January 16, 2010

I occasionally see requests for book suggestions in various Internet forums, blogs, and various other websites.  I am typically very careful when preparing to buy a book, since it my money that will be wasted if the book’s contents are worthless.  I try to read the reviews for books on Amazon.com, blogs, and various other sites to try to determine if the book’s contents should have been retired with the release of Oracle Database 8.0, whether or not the book’s authors emphasize volume over technical quality, and whether or not people are willing to rely on a particular author’s advice.

I thought that it might be interesting to look at my book library to see if I would be able to offer any advice.  My book library at home looks like the following picture (I intentionally or unintentionally left out about 6 to 10 books) – click the picture to see a larger version.

 If you closely examine the books, you probably will be able to tell that I need to spend a little more time digging through the books on the right (and the four years of related magazines that have been sitting mostly unread).

Here is my personal book library at work – click the picture to see a larger version.

As I wrote in this OTN thread, a small number of Oracle books marked distinct turning points in my knowledge of Oracle.  Additionally, it is necessary to find motivation to continue learning whatever subject falls at your feet.  In the OTN thread, I stated the following:

I have been very fortunate to buy and read several very high quality Oracle books which not only correctly state the way something works, but also manage to provide a logical, reasoned explanation for why things happen as they do, when it is appropriate, and when it is not. While not the first book I read on the topic of Oracle, the book “Oracle Performance Tuning 101” by Gaja Vaidyanatha marked the start of logical reasoning in performance tuning exercises for me. A couple years later I learned that Gaja was a member of the Oaktable Network. I read the book “Expert Oracle One on One” by Tom Kyte and was impressed with the test cases presented in the book which help readers understand the logic of why Oracle behaves as it does, and I also enjoyed the performance tuning stories in the book. A couple years later I found Tom Kyte’s “Expert Oracle Database Architecture” book at a book store and bought it without a second thought; some repetition from his previous book, fewer performance tuning stories, but a lot of great, logically reasoned information. A couple years later I learned that Tom was a member of the Oaktable Network. I read the book “Optimizing Oracle Performance” by Cary Millsap, a book that once again marked a distinct turning point in the method I used for performance tuning – the logic made all of the book easy to understand. A couple years later I learned that Cary was a member of the Oaktable Network. I read the book “Cost-Based Oracle Fundamentals” by Jonathan Lewis, a book by its title seemed to be too much of a beginner’s book until I read the review by Tom Kyte. Needless to say, the book also marked a turning point in the way I approach problem solving through logical reasoning, asking and answering the question – “What is Oracle thinking”. Jonathan is a member of the Oaktable Network, a pattern is starting to develop here. At this point I started looking for anything written in book or blog form by members of the Oaktable Network. I found Richard Foote’s blog, which some how managed to make Oracle indexes interesting for me – probably through the use of logic and test cases which allowed me to reproduce what I reading about. I found Jonathan Lewis’ blog, which covers so many interesting topics about Oracle, all of which leverage logical approaches to help understanding. I also found the blogs of Kevin Closson, Greg Rahn, Tanel Poder, and a number of other members of the Oaktable Network. The draw to the performance tuning side of Oracle administration was primarily for a search for the elusive condition known as Compulsive Tuning Disorder, which was coined in the book written by Gaja. There were, of course, many other books which contributed to my knowledge – I reviewed at least 8 of the Oracle related books on the Amazon.com website

The above was written before I set up this blog – there are more book reviews on this blog here: https://hoopercharles.wordpress.com/category/book-review/.  In the above pictures you will see all of the books that I referenced in the OTN post, as well as the book that I had the opportunity to co-author with a fairly large number of OakTable Network members (top photo – I have not yet received my printed copy of the book from Amazon, so the picture shows a printed copy of the electronic version from Apress).  There are of course a large number of books in my personal library at work – as you can see, I have the opportunity to dig into much more than Oracle Database.  I have read most of the books cover to cover, and a very small number of the books have been read cover to cover twice.

My post in the OTN thread continues:

Motivation… it is interesting to read what people write about Oracle. Sometimes what is written directly contradicts what one knows about Oracle. In such cases, it may be a fun exercise to determine if what was written is correct (and why it is logically correct), or why it is wrong (and why it is logically incorrect). Take, for example, the “Top 5 Timed Events” seen in this book …

The text of the book states that the “Top 5 Timed Events” shown indicates a CPU Constrained Database (side note: if a database is a series of files stored physically on a disk, can it ever be CPU constrained?). From the “Top 5 Timed Events”, we see that there were 4,851 waits on the CPU for a total time of 4,042 seconds, and this represented 55.76% of the wait time. Someone reading the book might be left thinking one of:

• “That obviously means that the CPU is overwhelmed!”
• “Wow 4,851 wait events on the CPU, that sure is a lot!”
• “Wow wait events on the CPU, I didn’t know that was possible?”
• “Hey, something is wrong with this ‘Top 5 Timed Events’ output as Oracle never reports the number of waits on CPU.”
• “Something is really wrong with this ‘Top 5 Timed Events’ output as we do not know the number of CPUs in the server (what if there are 32 CPUs), the time range of the statics, and why the average time for a single block read is more than a second!”

Another page from the same book shows this command:

alter system set optimizer_index_cost_adj=20 scope = pfile;

Someone reading the book might be left thinking one of:

• That looks like an easy to implement solution.
• I thought that it was only possible to alter parameters in the spfile with an ALTER SYSTEM command, neat.
• That command will never execute, and should return an “ORA-00922: missing or invalid option” error.
• Why would the author suggest a value of 20 for OPTIMIZER_INDEX_COST_ADJ and not 1, 5, 10, 12, 50, or 100? Are there any side effects? Why isn’t the author recommending the use of system (CPU) statistics to correct the cost of full table scans? 

I suggest that you try reading an old Oracle book, such as “Practical Oracle 8i”, and see if you are able to pick out anything that is:

• Obviously wrong, and was never correct.
• Obviously wrong since Oracle 10.1.0.1 (or some other release version), but was 100% correct at the time the book was written.
• Obviously correct now, just as it was when the book was originally written.
• Grossly over applying a fix that worked in a finite set of conditions (possibly due to false correlation) to situations with nearly infinite scope.

Someone posted a comment on this blog asking for a sequenced list of book recommendations for learning Oracle Database.  I suggested that the list of books might be a bit different depending on whether the person had an interest in general DBA work or performance tuning (or development, or …).  The suggestions that I provided to the comment follow:

Quick suggestions:

• A solid foundation of Oracle specific SQL is needed. I enjoyed reading “Mastering Oracle SQL and SQL*Plus“, and I believe that book provides a solid foundation. That book appears to be in the process of being updated, and might even include page numbers this time (http://www.apress.com/book/view/9781430271970). I am currently reading “Oracle SQL Recipes: A Problem-Solution Approach” (http://www.apress.com/book/view/1430225092), probably about 30 pages into the book now – and I believe that I have already found a small handful of minor errors/issues with the book that would make it difficult to use as a starting point.
• A solid foundation of understanding Oracle’s behavior is needed. I believe that Tom Kyte’s “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” book (http://www.apress.com/book/view/9781590595305) is one of the best sources. I understand that Tom Kyte also re-wrote the Oracle 11.2.0.1 “Concepts Guide” (http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/toc.htm), so that might be a decent substitute for his book.
• If you are planning to do general DBA work, probably the next book should be on the topic of RMAN. The books in the Oracle documentation library are good, and you will find two reviews of other RMAN books on this blog.
• Next, I would suggest reading a book that provides a solid foundation of the Oracle wait interface. “Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning” seems to be the best source of that information, but it would be nice to see an update of the book that covers more recent releases of Oracle.
• Next, the “Oracle Performance Tuning Guide” from the Oracle documentation library.
• Next, I suggest the book “Troubleshooting Oracle Performance” – the book is great for not only introducing people to various approaches for troubleshooting problems, but also provides foundation knowledge that is needed in order to understand why an approach worked.
• Next, I suggest digging deeper into troubleshooting with 10046 trace files – Cary Millsap’s “Optimizing Oracle Performance” is the best source for this information.
• Next, I suggest digging deeper into troubleshooting with 10053 trace files – Jonathan Lewis’ “Cost-Based Oracle Fundamentals” is the best source for this information.

+ If queueing theory, introduced in “Optimizing Oracle Performance“, is of interest, take a look at “Forecasting Oracle Performance”

+ If Statspack/AWR report reading, introduced in the “Performance Tuning Guide” is of interest, see the excellent series of articles on Jonathan Lewis’ blog.

+ If you want your jaw to drop, take a look at Tanel Poder’s blog. I also recommend reading all of the blog entries on Jonathan Lewis’ blog and Richard Foote’s blog.

+ I have now read most of the chapters in the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book.  The book contains theory, tuning philosophy, tuning/troubleshooting logic, test cases, and up to date information that cannot be found in any other book.  It is my opinion that this book belongs in the “Quick suggestions” list above.  Disclaimer: Just estimating here, for every 17 copies of this book that are sold, I think that I will have enough royalty money to buy a soda drink from the vending machine (my recommendation has nothing to do with how thirsty I am right now 🙂 ).  It is my belief that the motivation for all of the authors of this book was simply to help readers improve their skills well beyond the basics.