The expert DBA tried this new execution plan, which obtained good results. The DBA eliminated the synonym, which was obsolete to the business, and, looking at the execution plan, now it did have access to ELECT PCE_HANDLE, CLU_ACTIVATION_DATEĪND CLU_CELLULAR_NUMBER = PCE_CLU_CELLULAR_NUMBER Clearly, we could replace the access to the synonym with a query to the cellulars table, without going through a view, which adds execution time to the query. The execution plan showed that when accessing what it actually did was to access the public synonym called CELLULARS, which in turn accessed a v_pp_cellular view on the PROD database, and this view was formed with a Select cellulars from the same database. The query identified as problematic was a query distributed between two databases, CCARD and PROD, whose tables were prepay_cellulars (CCARD) and cellulars (PROD) running on the database CCARD. I want to present a successful case of a DBA’s using this tool, in which a process that originally took over six hours, including the total time of thousands of executions of a query, was reduced to taking 23 minutes after optimization.Ī trace was made to detect which SQL was causing the most delay. Manage Plans organizes baselines and stored outlines used to improve SQL statement performance.
Inspect SGA captures runtime statistics for running or executed SQL statements from the SGA to identify the statements using the most system resources.Īnalyze Impact allows us to determine the performance impact of proposed changes to our database before physically making the changes. It then analyzes the problematic statements and categorizes them according to performance levels.
MYSQL OPTIMIZER TRACE CODE
Scan SQL identifies problematic SQL statements in our source code and database objects without execution. It also generates optimized SQL statements that we can use to replace problematic SQL statements in our database environment.Īdvise Indexes generates an index set for a group of SQL statements and tests the index set to determine performance improvement. This feature is particularly useful for third-party applications where we don’t have access to the source code.īatch Optimize automatically identifies and optimizes problematic SQL statements using batch processing. Plan Control mode lets us take advantage of the SQL Plan Management feature introduced in Oracle 11g to optimize execution plans and deploy plan baselines for SQL statements without changing the original source code. SQL Rewrite mode allows us to generate SQL statements and index alternatives for problematic SQL statements. Optimize SQL offers SQL Rewrite mode and Plan Control mode. If we want to deactivate this window, we uncheck the "Show Welcome on next startup" option at the bottom of window. When we open this tool, we can see the Welcome window that shows us a list of tasks. It provides index recommendations for multiple SQL statements, simulates index impact analysis, and generates SQL execution plan alternatives. SQL Optimizer also provides a complete solution for index optimization and analysis of plan changes.
Once SQL Optimizer identifies problematic SQL statements, it optimizes the SQL and provides replacement code that includes the optimized statement.
SQL Optimizer analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA). Here's a SQL Optimizer success story! SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of our SQL statements.