Challenge your Database – Oracle (part. 2)
The Oracle version of my previous script described in Challenge your Database – MS SQL (part. 1)
NB: The SQL queries are (still) integrated in the script, in an array ‘$Queries=@(“SELECT COUNT(*) AS STAT FROM Table;”,”SELECT * FROM Table;”)’, but it would be pretty easy to get them from a txt file…
<# .SYNOPSIS OracleLoopBenchmark.ps1 - Runs a few SQL queries against an Oracle database .DESCRIPTION OracleLoopBenchmark.ps1 - Runs a few SQL queries against an Oracle database during 10 seconds for each (default value, but the parameter 'QueriesDuration' allows to change the default duration) and generate an HTML report. The purpose of this script is to determine how many queries can be runned during one minute (nb queries * $QueriesDuration) in order to test response times. .PARAMETER QueriesDuration Defines how long each SQL query will be run Default is 10 seconds. .PARAMETER TNSNAMES Switch parameter: that may, or may not, be specified when the command is run No value. .NOTES File Name : OracleLoopBenchmark.ps1 Author : Fabrice ZERROUKI - fabricezerrouki@hotmail.com .EXAMPLE PS D:\> .\OracleLoopBenchmark.ps1 -TNSNAMES As the switch parameter -TNSNAMES is enabled, the script will use the $env:ORACLE_HOME/network/admin/tnsnames.ora configuration file to resolve databases addresses and establish connections to them. In this case, the script will only ask for 3 parameters: 1. the datasource name (as named in the tnsnames.ora file) 2. the user id to connect with 3. the password associated with the account indicated above .EXAMPLE PS D:\> .\OracleLoopBenchmark.ps1 As the switch parameter -TNSNAMES is NOT enabled, the script will ask for 5 parameters: 1. the database server hostname (or IP address) to connect to 2. the database listener port (if blank, default value "1521" is used.) 3. the database service name (database name) to use to run the queries 4. the user id to connect with 5. the password associated with the account indicated above .EXAMPLE PS D:\> .\OracleLoopBenchmark.ps1 -QueriesDuration 100 Alter the default value for the QueriesDuration parameter (10 seconds) to run each query during 100 seconds. #> param( $QueriesDuration=10, [switch]$TNSNAMES ) $ErrorActionPreference="SilentlyContinue" $ScriptVersion="1.0" $ScriptName=($MyInvocation.MyCommand).Name $ScriptName=$ScriptName -replace ".ps1","" $Logfile=".\${ScriptName}.log" Function LogWrite { Param ([string]$logstring) Add-Content $Logfile -Value $logstring } function Check-SessionArch { if ([System.IntPtr]::Size -eq 8) { return "x64" } else { return "x86" } } $SessionArch=Check-SessionArch # Basic customizing the PowerShell console $Shell=$Host.UI.RawUI $Shell.WindowTitle="Benchmark - Oracle Loop ($SessionArch)" $size=$Shell.BufferSize $size.width=120 $size.height=3000 $Shell.BufferSize=$size $size=$Shell.WindowSize $size.width=120 $size.height=30 $Shell.WindowSize=$size function Test-Port { $tcpclient=New-Object System.Net.Sockets.TcpClient $iar=$tcpclient.BeginConnect($DbHost, $DbPort, $null, $null) $wait=$iar.AsyncWaitHandle.WaitOne(5000, $false) if (!$wait) { $tcpclient.Close() $failed=$true } else { $Error.Clear() $tcpclient.EndConnect($iar) | Out-Null if ($Error[0]) { Write-Host $Error[0] -ForegroundColor DarkYellow $failed=$true } $tcpclient.Close() } if($failed){$false} else {$true} } #EndOf function Test-Port Write-Host "`nConnection parameters definition" -ForegroundColor Yellow if ($TNSNAMES -eq $true) { # Getting the DataSource name to connect to the database Write-Host "Please provide the " -NoNewline Write-Host "DataSource name" -ForegroundColor Yellow -NoNewline Write-Host " you would like to connect to. (As its named in your tnsnames.ora file): " -NoNewline $DataSource=Read-Host # Getting the userid to connect to the database Write-Host "Please provide the " -NoNewline Write-Host "User Id" -ForegroundColor Yellow -NoNewline Write-Host " you would like to use to connect with for DataSource `"$DataSource`": " -NoNewline $DbUser=Read-Host # Getting the password to connect to the database Write-Host "Please provide the " -NoNewline Write-Host "Password" -ForegroundColor Yellow -NoNewline Write-Host " you would like to use with the `"$DbUser`" account: " -NoNewline $DbPassword=Read-Host -AsSecureString $Ptr=[System.Runtime.InteropServices.Marshal]::SecureStringToCoTaskMemUnicode($DbPassword) $DbSecurePassword=[System.Runtime.InteropServices.Marshal]::PtrToStringUni($Ptr) [System.Runtime.InteropServices.Marshal]::ZeroFreeCoTaskMemUnicode($Ptr) Write-Host "`n" # Building the connection string $ConnectionString="Data Source=$DataSource;Password=$DbSecurePassword;User Id=$DbUser;" $ConnectionType="tnsnames.ora file" $DatabaseInfo="DataSource: $DataSource<br />" } else { Write-Host "Please provide the " -NoNewline Write-Host "Database server hostname" -ForegroundColor Yellow -NoNewline Write-Host " (or IP Address) you would like to connect to: " -NoNewline $DbHost=Read-Host Write-Host "Please provide the " -NoNewline Write-Host "Oracle listener port" -ForegroundColor Yellow -NoNewline Write-Host " on `"$DbHost`". (Leave blank to use `"1521`"): " -NoNewline $DbPort=Read-Host if (!($DbPort)) {$DbPort="1521"} # Checking if the specified (or default value is not specified) DbHost answers on the DbPort if(!(Test-Port)) {Write-Host "`nUnable to connect to ${DbHost}:${DbPort}. Exiting now." ; break} Write-Host "Please provide the " -NoNewline Write-Host "Oracle database name" -ForegroundColor Yellow -NoNewline Write-Host " to connect to (SERVICE_NAME): " -NoNewline $ServiceName=Read-Host Write-Host "Please provide the " -NoNewline Write-Host "User Id" -ForegroundColor Yellow -NoNewline Write-Host " you would like to use to connect to database `"$ServiceName`": " -NoNewline $DbUser=Read-Host # Getting the password to connect to the database Write-Host "Please provide the " -NoNewline Write-Host "Password" -ForegroundColor Yellow -NoNewline Write-Host " you would like to use with the `"$DbUser`" account: " -NoNewline $DbPassword=Read-Host -AsSecureString $Ptr=[System.Runtime.InteropServices.Marshal]::SecureStringToCoTaskMemUnicode($DbPassword) $DbSecurePassword=[System.Runtime.InteropServices.Marshal]::PtrToStringUni($Ptr) [System.Runtime.InteropServices.Marshal]::ZeroFreeCoTaskMemUnicode($Ptr) Write-Host "`n" # Building the connection string $ConnectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) ` (HOST=$DbHost)(PORT=$DbPort)) ` (CONNECT_DATA=(SERVICE_NAME=$ServiceName))) ` ;User Id=$DbUser;Password=$DbSecurePassword;" $ConnectionType="connection string" $DatabaseInfo=@" Database Host: ${DbHost}:${DbPort}<br /> Database Name: $ServiceName<br /> "@ } # Writing the used parameters in the logfile $StartScript=Get-Date -format "MM-dd-yyyy %H:mm:ss" LogWrite ("******************************") LogWrite ("$StartScript - [START]") LogWrite ("Architecture: $SessionArch") LogWrite ("Queries duration: $QueriesDuration") LogWrite ("Connection type: $ConnectionType") LogWrite (($DatabaseInfo -replace "<br />","`r")) LogWrite ("Database UserId: $DbUser") LogWrite ("`r") LogWrite ("-------------------") function OracleQuery ($Query) { [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null $OracleConnection=New-Object -TypeName Oracle.DataAccess.Client.OracleConnection $OracleConnection.ConnectionString=$ConnectionString $OracleConnection.Open() $OracleCommand=New-Object -TypeName Oracle.DataAccess.Client.OracleCommand $OracleCommand.CommandText=$Query $OracleCommand.Connection=$OracleConnection $OracleDataAdapter=New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter $OracleDataAdapter.SelectCommand=$OracleCommand $DataSet=New-Object -TypeName System.Data.DataSet $OracleDataAdapter.Fill($DataSet) | Out-Null $OracleDataAdapter.Dispose() $OracleCommand.Dispose() $OracleConnection.Close() $DataSet.Tables[0].Rows.Count } # EndOf function OracleQuery ($Query) # Defining the SQL queries $Queries=@("SELECT COUNT(*) AS STAT FROM Table;","SELECT * FROM Table;") $QueriesLength=$Queries.Length $TestDuration="$QueriesLength queries, executed for at least $QueriesDuration seconds each" $TotalQueries=0 # Initializing the HTML report $date=(Get-Date).ToString('dd/MM/yyyy') $ReportHeader=@" <html> <head> <meta http-equiv='Content-Type' content='text/html;'> <meta name='description' content='OracleLoopBenchmark.ps1 - Runs a few SQL queries against an Oracle database in order to test reponse times.'> <meta name='author' content='Fabrice ZERROUKI, fabricezerrouki@hotmail.com'> <title>Benchmark - Oracle Loop</title> <style type="text/css"> body {font-family: Verdana, Arial, Helvetica, sans-serif; color:#666666; text-align:center; line-height:90%;} p {font-size: 13px; line-height: 1em;} #pagewidth{width:80%; text-align:left; margin:0 auto;} #maincol{background-color: #FFFFFF; position: relative;} .clearfix:after {content: "."; display: block; height: 0; clear: both; visibility: hidden;} .clearfix{display: inline-block;} /* Hides from IE-mac \*/ * html .clearfix{height: 1%;} .clearfix{display: block;} /* End hide from IE-mac */ table.tableizer-table {border: 1px solid #53A9FF; font-family: Verdana, Verdana, Geneva, sans-serif; font-size: 11px; word-wrap:break-word;} .tableizer-table td {padding: 2px; margin: 1px; border: 1px solid #53A9FF;} .tableizer-table th {padding: 2px; margin: 1px; border: 1px solid #53A9FF; background-color: #53A9FF; color: #FFF; font-weight: bold;} </style> "@ $TestReport=@" <div id='pagewidth' > <div id='wrapper' class='clearfix'> <div id='maincol'> <span style=' float:left; font-weight:normal; color:#FF7444; font-size:24px; margin:0px; padding:5px 0 5px 0;'>Benchmark</span><br /> <br /> <span style=' float:left; font-weight:normal; color:#FF7444; font-size:20px; margin:0px; padding:5px 0 5px 0;'>Oracle Loop - $date</span> <br /> <br /> <p><span style='font-size:11px;'><strong>Parameters</strong><br /> Script Version: $ScriptVersion<br /> PowerShell Session: $SessionArch<br /> Test duration: $TestDuration <br /> <br /> Connection Type: $ConnectionType<br /> $DatabaseInfo Database UserId: $DbUser</span></p> <br /> <table class='tableizer-table' width='100%' rules='cols'> <tr class='tableizer-firstrow'> <th align='left' valign='top' width='30%'><strong>QUERY</strong></th> <th align='left' valign='top'><strong>NB PASSED</strong></th> <th align='left' valign='top'><strong>LINES RETURNED</strong></th> <th align='left' valign='top'><strong>START TIME</strong></th> <th align='left' valign='top'><strong>END TIME</strong></th> <th align='left' valign='top'><strong>DURATION</strong></th> <th align='left' valign='top'><strong>AVERAGE</strong></th> </tr> "@ # Launching the test $j=1 $TotalStartTime=Get-Date ForEach($Query in $Queries) { Write-Progress -Activity "Running the SQL queries. Please wait..." -Status "Progress ->" -PercentComplete ($j/$Queries.Length*100) -CurrentOperation "Currently querying: $Query" $j++ # Let's run the loop $StartTime=Get-Date $TimeOut=New-TimeSpan -Seconds $QueriesDuration $Sw=[Diagnostics.Stopwatch]::StartNew() $i=0 While ($Sw.Elapsed -lt $TimeOut) { $i++ Try { $linesCount=OracleQuery $Query } Catch [system.exception] { # Writing the exception in the console Write-Host $_.Exception # Writing the exception in the logfile $ErrorTime=Get-Date -format "MM-dd-yyyy %H:mm:ss" LogWrite ("$ErrorTime - [ERROR] $_") LogWrite ("******************************") LogWrite ("`r`n") Exit; } } #EndOf While ($Sw.Elapsed -lt $TimeOut) $EndTime=Get-Date $Duration=$EndTime-$StartTime # Counting the average rate queries/second $stat=(New-TimeSpan -Start $StartTime -End $EndTime).TotalMilliseconds $stats=[System.Math]::Round(($i/$stat)*1000,3) # Writing the query and the loop results in the logfile $InfoTime=Get-Date -format "MM-dd-yyyy %H:mm:ss" LogWrite ("$InfoTime - [INFO] Query: $Query") LogWrite ("$InfoTime - [INFO] $i queries executed returning $linesCount lines`t|`tAvg: $stats queries/second") LogWrite ("$InfoTime - [INFO] Start: $StartTime`t`t`t|`tEnd: $EndTime`t|`tDuration=$Duration`n") # Displaying the query and the loop results in the console Write-Host "$Query" Write-Host "Test finished! $i queries executed returning $linesCount lines. (Avg: $stats queries/second)" -ForegroundColor DarkGreen Write-Host "Start: $StartTime`t|`tEnd: $EndTime`t|`tDuration=$Duration`n" -ForegroundColor Yellow $TotalQueries+=$i $TotalLines+=$linesCount $TestReport+=@" <tr> <td align='left' valign='top' cellpadding='2'>$Query</td> <td align='left' valign='top' cellpadding='2'>$i</td> <td align='left' valign='top' cellpadding='2'>$linesCount</td> <td align='left' valign='top' cellpadding='2'>$StartTime</td> <td align='left' valign='top' cellpadding='2'>$EndTime</td> <td align='left' valign='top' cellpadding='2'>$Duration</td> <td align='left' valign='top' cellpadding='2'>$stats queries/s</td> </tr> "@ } # EndOf ForEach($Query in $Queries) $TotalEndTime=Get-Date $TotalDuration=$TotalEndTime-$TotalStartTime # Counting the average rate queries/second $Totalstat=(New-TimeSpan -Start $TotalStartTime -End $TotalEndTime).TotalMilliseconds $Totalstats=[System.Math]::Round(($TotalQueries/$Totalstat)*1000,3) # Let's do the sums $TestReport+=@" <tr height='1'> <th align='left' valign='top'></th> <th align='left' valign='top'></th> <th align='left' valign='top'></th> <th align='left' valign='top'></th> <th align='left' valign='top'></th> <th align='left' valign='top'></th> <th align='left' valign='top'></th> </tr> <tr> <td align='left' valign='top' cellpadding='2'>TOTAL</td> <td align='left' valign='top' cellpadding='2'>$TotalQueries</td> <td align='left' valign='top' cellpadding='2'>$TotalLines</td> <td align='left' valign='top' cellpadding='2'>$TotalStartTime</td> <td align='left' valign='top' cellpadding='2'>$TotalEndTime</td> <td align='left' valign='top' cellpadding='2'>$TotalDuration</td> <td align='left' valign='top' cellpadding='2'>$Totalstats queries/s</td> </tr> "@ $TestReport+="</table><span style='font-size:10px; float:right;'>Copyright © 2013 PS FAB:\>. All Rights Reserved</span></div></div></div>" # Generating the HTML report ConvertTo-HTML -Body $TestReport -Head $ReportHeader -Title "Benchmark - Oracle Loop Report" | Out-File OracleLoopBenchmark.html -encoding UTF8 # Opening the HTML report in the default browser Invoke-Item OracleLoopBenchmark.html $CompletionTime=Get-Date -format "MM-dd-yyyy %H:mm:ss" LogWrite ("-------------------`r") LogWrite ("$CompletionTime - [INFO] Script execution completed successfully!") LogWrite ("$CompletionTime - [END]") LogWrite ("******************************") LogWrite ("`r`n")