Oracle Magazine, July/August 2019
Database Developer and DBA ETL Changing the table structure for example via partitioning Adding resources to the query via parallel slaves Making more existing resources available for example running the query at a different time Adding resources to the entire database machine So even when the optimizer plan is optimal there is still value in the DBMS_ XPLAN DISPLAY_ CURSOR or Real Time SQL Monitoring output The other reporting columns such as Database Time and IO Requests can help you make decisions on where structural changes will decrease the execution time the most For example returning to the problematic EMPLOYEE query if most of the execution time is spent scanning the JOB_ HISTORY table there will be little possible benefit to compressing the ORACLE MAGAZINE JULY AUGUST 2019 155 EMPLOYEE table SUMMARY At this point in this series on SQL tuning problematic SQL statements have been identified and their true execution plans have been extracted with DBMS_ XPLAN DISPLAY_ CURSOR This is not to discount the value of a simple EXPLAIN PLAN command during the application development process as a means of getting an indicative measure of how a SQL statement may perform but always remember that the plan observed via EXPLAIN PLAN is never guaranteed to be the same plan that is used at execution time when the application is released into the wild Once the true execution plan has been identified by analysis of the estimated versus actual rows for each phase of the execution plan the correctness of the optimizer decisions can be evaluated This helps the SQL tuner narrow the focus to those elements of the plan that will likely need attention If the optimizer was mostly
You must have JavaScript enabled to view digital editions.
