Skip to content

Optimizer

Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?

There was a dicsussion a couple of years ago about a mssing feature in Oracle that the Optimizer is not peeking values when SYS_CONTEXT is being used in a predicate. I.e. specifically when using SYS_CONTEXT in Views to simulate passing parameters to views. See the following links concerning this topic: https://connor-mcdonald.com/2016/10/20/taking-a-peek-at-sys_context https://blog.jooq.org/2016/10/20/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle https://community.oracle.com/ideas/15826 E.g. take the example of Connor McDonald… 

Create a primary database using the backup of a standby database on 12cR2

The scope of this blog will be to show how to create a primary role database based on a backup of a standby database on 12cR2. Step1: We are assuming that an auxiliary instance has been created and started in nomount mode. rman target / restore primary controlfile from ‘backup_location_directory/control_.bkp’; exit; By specifying “restore primary” , will modify the flag… 

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

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… 

When automatic reoptimization plan is less efficient

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.… 

12cR2: no cardinality feedback for small queries

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. Feature In Clemens Bleile post about SQL Plan Directives changes in 12cR2 he determined than the… 

Oracle 12cR2: Statistics Advisor

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… 

12c GTT private statistics and cursor invalidation

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. Update28-OCT-16 I’ve written this having in mind a specific… 

AWR does not store explain plan predicates

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… 

Oracle SQL Profile: why multiple OPT_ESTIMATE?

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… 

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

By Franck Pachot . 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… 

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

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…