Tosska DB Ace Enterprise (DBAO™) for Oracle® – System Requirements

Before installing Tosska DB Ace Enterprise (DBAO™) for Oracle®, please make sure your system meets the following minimum hardware and software requirements:

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 400 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5
Database Client Oracle® 9i Client
Oracle® 10G Client
Oracle® 11G Client
Oracle® 12c Client
Oracle® client is not required when Basic Type is used for Connection Mode
Database Server Oracle database 11.2 or higher version (11.2, 12.1 and 12.2)

Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server® – System Requirements

Before installing Tosska SQL Tuning Expert Pro (TSES Pro™) for SQL Server®, please make sure your system meets the following minimum hardware and software requirements:
 

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 500 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5.0
Database Server SQL Server database 2005 or higher

Tosska SQL Tuning Expert (TSES™) for SQL Server® – System Requirements

Before installing Tosska SQL Tuning Expert (TSES™) for SQL Server®, please make sure your system meets the following minimum hardware and software requirements:
 

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 500 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5.0
Database Server SQL Server database 2005 or higher

Tosska SQL Tuning Expert (TSEM5™) for MySQL (5.1-5.5)® – System Requirements

Before installing Tosska SQL Tuning Expert (TSEM5™) for MySQL (5.1-5.5)®, please make sure your system meets the following minimum hardware and software requirements:
 

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 400 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 64-bit
Microsoft Windows® 10 64-bit
.NET Framework Microsoft .NET Framework 4.5.2
Database Server MySQL database 5.1 to 5.5

Improve SQL Queries & Database for Better Efficiency: Part 2

This is the second blog in our two-part series to explain the best ways to optimize your database, which is best done by enhancing the SQL queries being used. Without much ado, let’s pick up where we left off –

Give Preference to WHERE, instead of HAVING (when defining filters)

A query is efficient when it saves resources by fetching only what’s needed from the database. According to the Order of Operations defined in SQL, WHERE queries are calculated before HAVING statements.

Therefore, it is advisable to give preference to WHERE over HAVING when the goal is to filter a query on the basis of conditions for greater efficiency. 

For instance, let us suppose a hundred sales have been made during the year 2019, and a user wishes to put in a query to determine what the number of sales was for the same time period. They may write something like this:

SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)

FROM Clients

   INNER JOIN Sales

   ON Clients.ClientID = Sales.ClientID

GROUP BY Clients.ClientID, Clients.Name

HAVING Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#

This statement would return at least a thousand sales records from the Sales table, then filter these thousand records to find the hundred records generated in the year 2019, and lastly, tally the data in the dataset.

If we compare the above with the same instance using the WHERE clause instead, there is a limit placed on the number of records fetched:

SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)

FROM Clients

  INNER JOIN Sales

  ON Clients.ClientID = Sales.CustomerID

WHERE Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#

GROUP BY Clients.ClientID, Clients.Name

This statement would return the hundred records from the year 2019, after which it would count the records in the dataset, thereby getting rid of the first step in the HAVING clause.

Keep wildcards strictly at the end of a statement

A wildcard creates the largest search possible when looking for plaintext information like names or designations. However, the wider a search, the less efficient it is, and a leading wildcard worsen the performance – particularly when it’s used with an ending wildcard.

That’s because the database has to find every single record that remotely matches the selected field. Take this query to fetch cities beginning with ‘Ch’, for instance:

SELECT Cities FROM Clients

WHERE Cities LIKE ‘%Ch%’

This statement will not just fetch the expected results of Chicago, Chester, and Chelsea, but will also return unintended results, like Richardson, Canal Winchester, and Cannon Beach.

A more productive statement would be:

SELECT Cities FROM Clients

WHERE Cities LIKE ‘Ch%’

This query will lead only to the expected results of Chicago, Chester, and Chelsea.

Use LIMIT to sample query results

The use of a LIMIT query will make sure the results of new SQL queries are relevant and desirable. As the name suggests, its function is to limit the quantity of records to the number mentioned, saving a lot of resources in the process.

Considering the 2019 sales query from above, let us suppose a limit of 15 records:

SELECT Clients.ClientID, Clients.Name, Count(Sales.SalesID)

FROM Clients

  INNER JOIN Sales

  ON Clients.ClientID = Sales.ClientID

WHERE Sales.LastSaleDate BETWEEN #1/1/2019# AND #12/31/2019#

GROUP BY Clients.ClientID, Clients.Name

LIMIT 15

The results will indicate if the data set is worth using or not.

Adjust Your Timing a Bit

If you’re looking to minimize the impact of your analytical queries on the production database, consult with an Oracle Database Administrator regarding the scheduling of your SQL queries so that they can be run during off-peak hours.

Specific hours when there are fewest concurrent users, generally in the middle of the night, should be chosen to run such resource consuming queries. If your SQL queries are more likely to include the following criteria, consider running it during off-peak timings:

  • Selecting from huge tables (where there are over a million records)
  • Queries with Cartesian or Cross Joins
  • Looping queries
  • SELECT DISTINCT queries
  • Subqueries that are nested
  • Search queries involving wildcards in long text or memo areas
  • Numerous schema statements

Query with Confidence!

Keeping these and other SQL tips into consideration will certainly enable you to construct efficient, smart queries that will operate swiftly and fetch your team the game-changing insights it needs.

Tosska SQL Tuning Expert (TSEM™) for MySQL® – System Requirements

Before installing Tosska SQL Tuning Expert (TSEM™) for MySQL®, please make sure your system meets the following minimum hardware and software requirements:
 

CPU 1.8 GHz Processor
Memory 2 GB of RAM minimum, 4 GB of RAM recommended
Hard Disk Space 200 MB of disk space for 32-bit installation
400 MB of disk space for 64-bit installation
Operating System Microsoft Windows® 7 32-bit and 64-bit
Microsoft Windows® 10 32-bit and 64-bit
.NET Framework Microsoft .NET Framework 4.5.2
Database Server MySQL database 5.6 or higher

What’s new in Tosska SQL Tuning Expert Pro for Oracle Version 2 ?

Bring the automatic SQL tuning technology to new heights

With almost doubled the number of hints supported in version 2, Tosska SQL Tuning Expert Pro (TSE Pro) V2 SQL tuning ability is brought to new heights. It creates a new standard of Hints-injection-based SQL tuning ability that no other tools can achieve in the market. More problematic SQL can be improved with the new Hints and bigger Quota allocation of each Intelligent Level in TSE Pro version 2. The actual test result shows that even very simple SQL statements still have chance to be improved in various environments.

Options Window shows new Quota and Hints setting

Streamline from problematic SQL identification to optimization

A new Tune Top SQLs module is added to TSE Pro version 2, it is a powerful tool that can help user to extract high workload SQL statements from SGA and AWR into a Top SQL Repository and store in local PC. It is a very useful function for DBA to identify problematic SQL statements without the need of going through thousands of lines in program source codes. The layout of the captured SQL statements can be ordered by various resource consumptions, users can easily review and locate problematic SQL statements and prioritize them for later SQL tuning exercise. Each identified problematic SQL statement can be tuned by just a point and click in the Top SQL Repository window, and it is tightly integrated with the Tune SQL function to streamline the flow from problematic SQL identification to SQL optimization. Each tuned SQL will be recorded with improvement statistics and tuned SQL hints solutions. Users can also make any remarks on specific SQL statement as a reminder for follow-up actions.

SQL statements are captured from SGA and to be tuned one by one according to user’s priority

Specific SQL’s tuning details can be shown right next to SQL list tag page