Infrastructure at your Service
Plan stability preserves execution plans in stored outlines. An outline is implemented as a set of optimizer hints that are associated with the SQL statement. If the use of the outline is enabled for the statement, then Oracle Database automatically considers the stored hints and tries to generate an execution plan in accordance with those hints (Oracle documentation). Oracle Database can create a public or private stored outline for one or all SQL statements. The…
This is a question that comes up quite often: How can I use optimizer hints in PostgreSQL as I can do it in Oracle? Well, you cant, and the reasons are this: Poor application code maintainability: hints in queries require massive refactoring. Interference with upgrades: today’s helpful hints become anti-performance after an upgrade. Encouraging bad DBA habits slap a hint on instead of figuring out the real issue. Does not scale with data size: the…
By Franck Pachot . 11gR2 started to have the optimizer react at execution time when a misestimate is encountered. Then the next executions are re-optimized with more accurate estimation, derived from the execution statistics. This was called cardinality feedback. Unfortunately, in rare cases we had a fast execution plan with bad estimations, and better estimations lead to worse execution plan. This is rare, but even when 9999 queries are faster, the one that takes too…
By Franck Pachot . This follows the comments on Clemens Bleile post about SQL Plan Directives in Oracle 12.2 where he discovered a new behavior that is not documented. The feature is good: bypass the cardinality feedback overhead when the query is fast anyway.
By Franck Pachot . Today at #ukoug_tech16 12:30 in hall 11A I’ll talk about Statistics Gathering Best Practice & 12cR2 Statistics Advisor Rather than taking the Optimizer Statistics Advisor rules one by one I’ll show the things to take care (some people may call it best practices) when gathering statistics and they mention the Statistics Advisor Rule. If you need a reference about all rules, you can get it from V$STATS_ADVISOR_RULES
By Franck Pachot . Short summary of this post is that rolling invalidation do not occur when you gather statistics on Global Temporary Tables in 12c that have session statistic scope (which is the default) and this may cause too many hard parses. I’m sharing all details and comments are welcome.
La 3ème édition du SQL Saturday à Paris est terminée et je ne peux qu’être enthousiaste à la vue du nombre de personnes que cet événement attire à chaque fois et ceci un samedi (à peu près 150 personnes). Comme à chaque fois, cela a été une bonne expérience pour ma part et je la renouvellerai à coup sûr l’année prochaine dans la mesure du possible. Vous trouverez les slides des sessions que j’ai (co)animées avec Christophe Laporte et Sarah…
By Franck Pachot . What is the most important part in an execution plan? It’s probably the predicate section. It helps to see implicit conversions. It helps to understand why an index is used or not. Or to see additional predicates coming from constraints. When you get an execution plan from shared pool, the dbms_xplan.display_cursor() shows the predicates. But when you retrieve a plan from the past, dbms_xplan.display_awr() does not show them. Statspack With Statspack,…
By Franck Pachot . In a previous blog I’v shared my script to retrieve the OPT_ESTIMATE hints from a SQL Profile. In the example I made, I had two lines for each table: — PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak: /*+ OPT_ESTIMATE(@”SEL$2CBA5DDD”, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$6AE97DF7″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) */ The reason is that when the optimizer do some transformations to the query, then the query…
By Franck Pachot . People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer…
