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")