Infrastructure at your Service

Optimizer Archives - Blog dbi services

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
Nicolas Jardot

SQL Patch: Another way to change the plan without changing the code

By | Application integration & Middleware | 3 Comments

Recently, at a customer site, I faced a performance issue. However, as often the statement is embedded in the application so it’s not possible to rewrite the query. In this blog post, we’ll change the execution plan to solve the problem without changing the code – thanks to SQL Patch. The faulty statement was part of a daily job that was configured and started from the application during the night. Every night, the statement failed…

 
Read More
Franck Pachot

Oracle 12c Adaptive Plan & inflection point

By | Database management | One Comment

The Oracle 12c Adaptive Plan feature was already presented by Nicolas Jardot in OOW 2013: Solving customer issues with the 12c Optimizer. I recently had to answer several questions about its behavior at execution time. Maybe the term ‘adaptive’ is misleading. It’s not that a join will stop and restart to another join method. Even with adaptive plan there will only be one join method to be applied. The feature only defers a decision that…

 
Read More