Infrastructure at your Service

Optimizer Archives - Blog dbi services

Daniel Westermann

Can I do it with PostgreSQL? – 14 – optimizer hints

By | Database Administration & Monitoring | No Comments

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…

 
Read More
Franck Pachot

When automatic reoptimization plan is less efficient

By | Database Administration & Monitoring | 3 Comments

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 long will gives a…

 
Read More
Franck Pachot

Oracle 12cR2: Statistics Advisor

By | Database Administration & Monitoring | No Comments

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  

 
Read More
David Barbarin

SQL Saturday Paris le 05 septembre 2015 – Slides

By | Technology Survey | No Comments

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…

 
Read More
Franck Pachot

AWR does not store explain plan predicates

By | Database management | One Comment

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, you can store execution…

 
Read More
Franck Pachot

Oracle SQL Profile: why multiple OPT_ESTIMATE?

By | Database management | 5 Comments

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 block identifiers can change….

 
Read More
Franck Pachot

Oracle SQL Profiles: Check what they do before accepting them blindly

By | Database management | 8 Comments

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 calls me, they expect…

 
Read More
Franck Pachot

Oracle system statistics: Display AUX_STATS$ with calculated values and formulas

By | Database Administration & Monitoring | 7 Comments

System statistics can be gathered in NOWORKLOAD or WORKLOAD mode. Different values will be set depending on that and the others will be calculated – derived from them. We can see defined values from SYS.AUX_STATS$ but here is a script that shows the calculated ones as well. With no system statistics or NOWORKLOAD the values of IOSEEKTIM (latency in ms) and IOTFRSPEED (transfer in bytes/ms) are set and the SREADTIM (time to read 1 block…

 
Read More