Watch a demo and learn how the SQL Optimizer in Toad for Oracle Xpert edition works to improve your code. Live Q&A.
Don’t let poorly-optimized SQL queries slow you down
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.
Free, on-demand webcast:
Boost Database Performance with SQL Optimizer in Toad for Oracle Xpert Edition
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:
- Why you should use SQL Optimizer
- Optimization steps and results
- How to scan existing code for performance problems
- Capturing running SQL and identifying issues
Join us to learn how the SQL Optimizer will ensure poorly-written queries don’t affect the performance of your Oracle databases.
What you can expect to learn:
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.
|
Physical · Quality and power of hardware · Network configuration · Storage allocation to data/logs · Object mix on datafiles · Disk fragmentation |
Configuration · Database memory configuration · Number of log files/groups · Data partitioning · Object storage parameters/sizing · Freshness of data · Optimizer hints |
|
Data Model · Proper normalization · Incorrect/mismatched data types · No indexes · Excessive indexes · Selectivity of indexes · Column order of composite indexes · Misuse of triggers |
Access · Search predicates/filters · Parallelization · 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.
New tutorials the first Thursday of every month
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.
Tell your colleagues about the webinar
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 nowTry Toad for Oracle free for 30 days. Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now. |
View more Ask Toad 2020 sessions on demand at your convenience
Session 1: Hot Keys, Preferences and Filters, Oh My: Customize Toad to Suit your Style
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.
Session 2: Five of the most useful DBA features on 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:
- Automate many Toad and non-Toad functions
- Manage all your scripts from a central location
- Browse, view and edit data across tables regardless of the referential integrity (RI)
- Manage and view all your sessions in a single view
- Generate database documentation easily for all the database objects in multiple databases
Session 3: Top Five Use Cases for Developers for Toad for Oracle Base Edition
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:
- Quickly find all object references and dependencies in their code
- Guarantee that code is following the correct flows of control
- Identify the most expensive lines in their code
- See how program connections affect database performance
- Automate tasks for greater efficiency and accuracy
Session 4: Banish Bad Code with Toad® for Oracle Pro and Xpert Edition
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:
- Code Analysis
- Generate Test Data
- Sensitive Data Awareness
In Toad Xpert, Mathew will cover:
- SQL Optimization
- Auto Optimizer
- Advanced Optimizer
Get more from Toad in our "Ask Toad" webcast series – 2019
You can also watch the following sessions from 2019 on demand.
- How Do You Enforce Coding Best Practices in Toad for Oracle? – Ask Toad
- How Can You Simplify & Automate SQL Optimization For Developers in Toad for Oracle? – Ask Toad
- How Do I Reduce Risk by Automating Database Changes? – Ask Toad
- The simple way to convert an Oracle database to a new database – Ask Toad
- Integrate your version control system with Toad – Ask Toad
- How can I recover from an unplanned transaction? – Ask Toad'
- Mapping your PL/SQL dependencies – Ask Toad
Start the discussion at forums.toadworld.com