By Franck Pachot . In a previous post https://www.dbi-services.com/blog/awr-dont-store-explain-plan-predicates/ I explained this limitation in gathering filter and access predicates by Statspack and then AWR because of old bugs about reverse parsing of predicates. Oracle listens to its customers through support (enhancement requests), though the community (votes on database ideas), and through the product managers who participate in User Groups and…
By Franck Pachot . The DBMS_XPLAN format accepts a lot of options, which are not all documented. Here is a small recap of available information. The minimum that is displayed is the Plan Line Id, the Operation, and the Object Name. You can add columns and/or sections with options, such as ‘rows’, optionally starting with a ‘+’ like ‘+rows’. Some…
By Franck Pachot . Here is a query I use when I’m on a system that has Diagnostic Pack (ASH) but no tuning Pack (SQL Monitor). It displays the execution plan with dbms_xplan.display_cursor and adds the % of ASH samples in front of each plan operation. Here is a small output example. Usual dbms_xplan output but showing the most active…
By Franck Pachot . You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if…
