Oracle Magazine, July/August 2019
Database Developer and DBA ETL resulted at SQL statement runtime respectively In the plan output line 4 of the Predicate Information section shows that the scan of the EMPLOYEES table would be filtering those rows where the HIRE_ DATE value was more recent than 1985 The E Rows and A Rows information in the last line of the SELECT result shows that the optimizer expected to find only a single row from EMPLOYEES for this predicate E Rows 1 but in fact 107 rows A Rows 107 were found This does not necessarily mean that the execution plan is a poor one but it does help guide the developer to focus on the part of the SQL statement that most probably needs attention The information leads to hypotheses the developer can explore such as The optimizer statistics on the EMPLOYEE table might be incorrect Perhaps the data on HIRE_ DATE is skewed and requires a statistics histogram to provide the optimizer with more information Maybe an index is needed on the HIRE_ DATE column Maybe the SQL predicate extract year from e hire_ date 1985 needs to be altered so that there is no expression around the HIRE_ DATE column The key thing is that now the developer has a more granular area to focus on to tune the SQL Sometimes it is not possible to modify the candidate SQL statement to add the GATHER_ PLAN_ STATISTICS hint In such instances if you have access to the database session itself for example via a login trigger you will be able to temporarily set the parameter STATISTICS_ LEVEL value to ALL for that session and that will also collect the additional statistics DBMS_ XPLAN DISPLAY_ CURSOR can then be used in a similar fashion to obtain the estimated versus actual SQL statement runtime comparison Because it is unlikely in this circumstance that DBMS_ XPLAN will be run in the same session as the SQL state ORACLE MAGAZINE JULY AUGUST 2019 149
You must have JavaScript enabled to view digital editions.
