Sep 24, 2020 3:17:27 PM by Quest Software
Watch a demo and learn how the SQL Optimizer in Toad for Oracle Xpert edition works to improve your code. Live Q&A.
We’ve focused a lot recently on database performance tuning, especially during the Quest Database Training Days. In one session, Janis Griffin explained a 5-step process for performance tuning where she showed several tips and tricks for understanding how queries interact with the database.
But what if you’re on the development side and you want to make sure your queries don’t get to the point where a DBA has to figure out what went wrong? You want to write efficient queries that are well-tuned, but you don’t always know where to start your tuning efforts.
Fortunately, you can automate the SQL optimization process and maximize the performance of your SQL statements with the SQL Optimizer functionality in Toad® for Oracle Xpert edition.
If you’re not sure how your query will operate, you can run it through the SQL Optimizer, which will analyze, evaluate and actually rewrite your SQL statements to make them better. By looking at what the Optimizer does, over time you’ll learn how to write better and more efficient statements yourself.
In this session of Ask Toad on September 3, Gary Jerep, software solutions consultant, will demonstrate how the SQL Optimizer in Toad for Oracle Xpert edition works to improve your code. You will learn:
Join us to learn how the SQL Optimizer will ensure poorly-written queries don’t affect the performance of your Oracle databases.
In the September session of Ask Toad, Gary Jerep, Solutions Consultant with Quest, showed how database professionals can use the SQL Optimizer for performance tuning in Toad® for Oracle Xpert Edition.
Gary first covered the practical factors that affect database performance – these include the physical aspects of the database, its configuration, the data model in use and how the data is accessed.
· Quality and power of hardware
· Network configuration
· Storage allocation to data/logs
· Object mix on datafiles
· Disk fragmentation
· Database memory configuration
· Number of log files/groups
· Data partitioning
· Object storage parameters/sizing
· Freshness of data
· Optimizer hints
· Proper normalization
· Incorrect/mismatched data types
· No indexes
· Excessive indexes
· Selectivity of indexes
· Column order of composite indexes
· Misuse of triggers
· Search predicates/filters
· Unnecessary sorts
· Presence of LOB references in query
· Poor application/algorithmic design
· Table data volume
· Mix of DML operations
Then, to remind us that SQL is inherently complex, Gary showed a simple query with over 30,491 ways to write it to return the same results set. This is important because it shows how difficult it is for relational databases, like Oracle and SQL Server, to figure out the most optimal plan to access the data. There’s also inherent complexity in selecting a set of indexes that optimize data access for a table.
When you combine these inherent complexities, you understand why SQL optimization is so hard, and why the SQL Optimizer feature of Toad for Oracle Xpert edition is so cool. Toad basically gives you a one-click way to analyze better alternatives for your SQL statements and indexes.
The Toad SQL Optimizer (accessed using the “tuning fork” icon) unleashes a set of algorithms that quickly figure out the best alternatives for restructuring and rewriting your SQL statements to yield the same results set faster.
In his demo of a short SQL statement, Gary showed how the SQL Optimizer took about 40 seconds to return 150 different ways to improve the statement by using alternative processing paths. This is some very sophisticated work that’s being done quickly and behind the scenes, with almost zero overhead on the database. You can compare the original statement with the alternatives and select ones that you think have the most potential and want Oracle to cost out.
SQL Optimizer uses a similar sophisticated methodology to help you find the optimal set of indexes for the query. You start by providing Toad with a workload of SQL statements, and the algorithm will look at different combinations of indexes for the columns being mentioned in the WHERE clauses. Once Toad finds a useful set of indexes, they’ll be noted in the displayed graph, along with their impact on query execution.
Again, these features can save a significant amount of time, while boosting query performance with a minimal amount of work. It’s another way that Toad for Oracle Xpert edition can help you improve database operations and performance.
Be on the lookout for a new program on the first Thursday of every month. Each 30-minute session includes step-by-step guidance on an everyday challenge to simplify and streamline your work, followed by a helpful Q&A. So get ready to Ask Toad and learn something new about your favorite database management tool.
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Try Toad for Oracle now
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Hot keys are particularly useful for DBAs who take the same actions every day with Toad for Oracle and may not know that shortcuts exist to streamline them. How to create filters to remove items that you don’t use frequently or don’t need to see will also be presented.
There are plenty of other configurations you might not know about that affect how Toad looks and behaves. The right configurations can save you time, reduce frustration and tedium, and make you friendlier!
In this complimentary session, Mark will cover these settings and offer a few more tips to help you get the most from Toad for Oracle.
Watch this demo about some of the many ways to get organized, productive and collaborative and provide insight into five of the most useful DBA features in Toad for Oracle Base Edition – these will definitely make your job easier. He’ll cover how to:
We understand that developing on Oracle databases poses challenges like finding object references and dependencies in your code, validating code flow and minimizing connections without sacrificing performance.
In this 30-minute Ask Toad session, Gary Jerep, Software Solutions Consultant will demonstrate five use cases, and how Toad for Oracle Base Edition helps developers:
Toad® for Oracle exists and provides a framework for setting and enforcing coding standards as well as for optimizing queries.
In this on-demand session of Ask Toad, Mathew Phan, Software Sales Engineer, will showcase the Professional and Xpert editions of Toad for Oracle and how features like static code reviews, sensitive data awareness and SQL optimization address the issues and data governance risks that arise from bad coding practices.
In Toad Professional, you’ll learn about:
In Toad Xpert, Mathew will cover:
You can also watch the following sessions from 2019 on demand.
Written by Quest Software