Tosska DB Ace Enterprise for Oracle

Predicting, finding, and solving the problem with the right tool
Traditional monitor and SQL analysis tools in the market are focused on locating the high resource consumption SQL or screening SQL with basic SQL syntax and query plan analysis. Tosska DB Ace Enterprise is an innovative tool that not only shows you from the individual SQL execution behavior to the big picture of your overall database performance, but it also helps you to forecast the performance changes of a database in different environments. Furthermore, DB Ace Enterprise comes with a set of powerful A.I. SQL tuning functions to help you to solve SQL performance problems, it streamlines the process from performance prediction and problem identification to tuning resolution within a tightly integrated product.

Module Name	Functionality	Description
Inspect SQL – SQL Performance Tracker	Predicting problems	Performance Tracker helps you keep track of and forecast performance changes before and after the database environment changes such as update statistics, database migration, database upgrade, etc.
Inspect SQL	Finding problems	Analyze the database’s overall SQL healthiness, SQL syntax, query plan, and execution statistics to classify your SQL statements into different problematic areas.
Tune SQL	Solving problems	Use AI SQL Rewrite and Hints Injection and indexing technology to tune a SQL statement.
Tune SQL Plan	Solving problems	Use AI SQL Hints Injection technology to tune a SQL statement for Oracle’s SQL Patches and Plan Baselines. No source code changes are necessary to improve SQL statements.
Tune DB Link SQL	Solving problems	The Tune DB Link SQL module is embedded with tailored syntax rewrite rules and specific optimization hints injection for DB Link SQL statements in a distributed database environment.
Tune Top SQL	Finding & Solving problems	Extract high resource consumption SQL statements from Oracle AWR or SGA for SQL tuning in a batch.
Advise Global Indexes	Solving problems	Use the proprietary AI technique to recommend the best N indexes for a given set of SQL workloads captured from SGA or AWR.
Manage SQL Patches	Maintenance	Enable/disable and manage Oracle SQL Patches.
Manage SQL Plan Baselines	Maintenance	Enable/disable and manage Oracle SQL Plan Baselines.

SQL Inspector – Predicting and finding SQL performance problems
DB Ace Enterprise for Oracle is inbuilt with a SQL inspector module that can help you to track and forecast performance changes before and after the database environment changes such as update statistics, database migration, database upgrade, etc. It also tells you the database’s overall SQL healthiness by analyzing SQL syntax, query plan, and SQL execution statistics to identify the potential problem areas (click here to watch a demo).

Reveal problems that you are not aware of before
DB Ace Enterprise for Oracle comes with six innovative SQL analysis features that can inspect SQL workload from Oracle SGA and AWR.

SQL Performance Tracker
Performance Tracker helps you keep track of and forecast performance changes after the database environment changes such as update statistics, database migration, database upgrade, etc.

There is always a question of what will be the performance when you change your database environment such as a hardware upgrade, database upgrade, create and drop indexes, update statistics, database migration, etc. SQL Performance Tracker is a powerful tool that can help users to simulate and even benchmark the performance of two databases before and after specific environmental changes.

What can SQL Performance Tracker tell between two databases with environmental changes?

• Discover SQL plan changes and cost changes between two databases.
• Show indexes usage between two database environments.
• Identify SQL with performance degradation and improvement between two databases.
• Benchmark SQL performance between two databases.
• Forecast potential SQL degradation for statistics update, database upgrade, and database migration without an actual time-consuming stress test.
• Health check mission-critical SQL statements periodically to prevent application performance from unexpected degradation.

(click here to watch a demo of the SQL Performance Tracker)
SQL Resource Spectrum Analysis
Resource spectrum analysis is a new method to explore the relationship of resource consumption ratio between top M SQL statements and the entire database resource consumption. In 2002, Microsoft announced that approximately 80% of the errors and crashes in their software were caused by 20% of all bugs detected. It is the well-known eighty-twenty (Pareto analysis) that is also applicable to database SQL behavior analysis, it is assumed that 20% of SQL statements should contribute 80% resource consumption to the entire database system. Any application systems with less than 20% of SQL statements that consumed more than 80% of database resources are treated as abnormal and should be reviewed and tuned. With the SQL Resource Spectrum Analysis, users can freely define their resource threshold percentage (say 80%) to locate the corresponding M% (say 20%) of top SQL statements. The following chart of the spectrum shows a relatively small amount (<4%) of SQL that occupied more than 80% of total resource consumption. So, to maximize the performance tuning return, you should start to tune this small amount of SQL first.

Overall SQL Healthiness Assessment
It is to let users have a glance at their database SQL behavior, the scoring method is based on the twenty-eighty rule on each category of analysis. A bad score shows the overall result has deviated from eighty twenty rule a lot, which means a small percentage of SQL spends most resource consumption for the entire database. Immediate SQL tuning is needed for this small amount of SQL.

Top Resource Consumption SQL Analysis
It is to show top N (say 20) resource consumption SQL statements and their corresponding resource consumption ratio to the entire system. It is helpful for users to quickly locate top N SQL for SQL tuning.

Query Plan Analysis
It is to analyze SQL statements’ query plan to discover any potential problems that users should be aware of. For example, a SQL statement with a Cartesian join in the query plan is acceptable only for small tables, but it is dangerous if the tables are getting bigger.

SQL Syntax Analysis
The purpose of analyzing SQL statements’ syntax is to discover any potential problems before it gets serious in a database system. For example, a SQL statement with a WHERE condition “like ‘%xx’  “ may be good for a small table, but it will be dangerous to the database performance if the table is getting bigger in the future.

Download Free Trial | Buy License

World’s leading A.I. SQL Rewrite plus Hints Injection technology for Oracle database
Manually rewriting SQL syntax and query hints injection to tune a SQL statement is commonly adopted by experienced developers and DBAs, but the effort and cost to rewrite a SQL statement are quite expensive. It requires an in-depth SQL tuning knowledge developer to spend hours or even days rewriting and testing a problematic SQL statement, and the tuning result might not be the best among most potential SQL rewrites due to the limitation of manual effort.

Tosska A.I. SQL Rewrite plus the A.I. Hints Injection Engine is the world’s leading machine SQL Tuning technology for Oracle database. It saves developers time to rewrite a problematic SQL statement by exploring the potential best SQL syntax and hints injection that even an experienced developer cannot discover (click here to watch Tune SQL demo).

Uncompromised SQL index tuning for a SQL statement
For mission-critical SQL statements, you may want to add indexes to the SQL to further optimize the statement to the ultimate performance without compromising the overhead of additional indexes. Tosska DB Ace Enterprise provides an A.I. index tuning power that tunes your SQL by exploring the most effective potential new indexes (click here to watch a demo).

Intelligent Workload-Based Index Advisor
It is an intelligent and cost-aware index advice engine that helps users to review their existing database schema if any new indexes can help to improve a given SQL workload. The engine can handle up to thousands of SQL statements and give you a reasonable recommendation that even human experts cannot achieve. Users can review those new indexes and the affected SQL statements side by side. Furthermore, the SQL statement’s workload is also considered during indexes advice processing, a SQL executed many times will be weighted higher to match its daily workload contribution. The indexes advice results are recommended starting with fewer indexes to more indexes, users can select the cost-effective indexes recommendation without incurring too much overhead to their databases (click here to watch a demo).

The Tune SQL Plan module helps you to tune SQL even if you don’t have the source code
Tosska DB Ace Enterprise also comes with an innovative Tune SQL Plan function that improves your SQL performance without touching your program source code. It is especially suitable for package application users who don’t own the source code. For application developers, SQL tuning becomes easier than ever before, as a problematic SQL statement can be improved without going through the time-consuming unit test, integration test, and program implementation required in the software development cycle.

How to tune SQL when you can’t edit the source code?
What would you do with the following scenarios? If you are a package application user, how you can tune your SQL if you can’t edit a query directly? Or, if you are an application developer, what would do if you want to have a quick fix on SQL performance but without the time to go through source code change and unit test? There are multiple features provided by Oracle such as SQL Profiles, SQL Plan Baselines, and SQL Patch that you can use to tell Oracle to fix a SQL query plan. But the use of these features is limited by Hints injection to SQL only, you cannot rewrite a SQL with different syntax and ask Oracle to accept a rewritten SQL query plan. So, hints-based SQL tuning is becoming more important than ever before. Tosska DB Ace Enterprise provides the most advanced Auto-Hints-Injection solution in the market to fully automate the process from SQL tuning to plan deployment.

Ad-hoc SQL tuning becomes easier
Some SQL statements may be executed with a sudden high volume of data periodically such as month-end or year-end processes. If the Oracle database optimizer cannot generate a good plan at the right time, the process may be prolonged and the business is affected. So, preventative measures should be carried out before the SQL statements are executed, and better query plans controlled by SQL Patch for those SQL statements should be deployed/enabled before starting the processes. Users can disable/drop SQL Patch after the processes are finished and resume those query plans back to normal for better daily operations (click here to watch a demo).

The Tune DB Link SQL module helps you to tune SQL that accesses remote databases
Manual SQL tuning for SQL statements with DB Link is a difficult task for most developers and DBA since there is a lack of tools in the market that can help developers specifically for DB Link SQL statements. The Tune DB Link SQL model is embedded with tailored syntax rewrite rules and specific optimization hints injection for DB Link SQL statements. It explores the maximum potential performance of a DB Link SQL that can be achieved to access multiple databases (click here to watch a demo).

Download Free Trial | Buy License