background preloader

Optimizer/Plan

Facebook Twitter

The Tom Kyte Blog: Something about nothing... Something about nothing.

The Tom Kyte Blog: Something about nothing...

A frequently misunderstood concept with NULLS and indexes is the myth that NULL values are not tracked in “normal” indexes such as a conventional B*Tree index. Therefore, many people conclude erroneously that “WHERE COLUMN IS NULL” will not use, in fact cannot, use an index. The facts are: Entirely NULL keys are not entered into a ‘normal’ B*Tree in OracleTherefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.As long as you have some not null column in your set of indexed columns, you will find NULLs in the index.

Additionally – you always have a NOT NULL (virtual) column to use no matter what! First, consider this example, supposed we want to leave OBJECT_ID as NULLABLE, but did have a column OWNER that was NOT NULL: ops$tkyte@ORA9IR2> alter table t modify (owner NOT NULL);Table altered. Optimizer Access Paths. The database stores rows in data blocks.

Optimizer Access Paths

In tables, the database can write a row anywhere in the bottom part of the block. Oracle Database uses the block overhead, which contains the row directory and table directory, to manage the block itself. An extent is made up of logically contiguous data blocks. The blocks may not be physically contiguous on disk. A segment is a set of extents that contains all the data for a logical storage structure within a tablespace. By default, the database uses automatic segment space management (ASSM) for permanent, locally managed tablespaces. Tanel Poder's Performance & Troubleshooting blog. ORACLE-BASE - Stored Outlines and Plan Stability.

Tracking Performance Problems – Inserting a Hint into SQL in a Compiled Program. December 18, 2009 What follows is part of a presentation that I gave in 2008.

Tracking Performance Problems – Inserting a Hint into SQL in a Compiled Program

The lead into this slide is that a report in an ERP package was running far slower than expected following an upgrade of the ERP package. Of course the SQL statements are hard coded into the ERP package, so there is not much that can be done, right? I created a 10046 extended SQL trace file at level 12, and then passed the trace file through my Toy Project for Performance Tuning ( One of the outputs of my program’s 10046 trace file parser provides an overview of the trace file, as well as an overview of each SQL statement. A screen shot of that output follows: The screen shot shows that there were 10,399 execute calls in the trace file that consumed 47.67 seconds of the server’s CPU time, and that the elapsed time/wall clock time from the server’s perspective for executing the SQL statements is 50.32 seconds for the executions.

We could then search the remainder of this file to locate those identifiers. Le blog Oracle d'Ahmed AANGOUR: Forcer un plan d'exécution via un SQL profile. Avec la 10g est arrivée la notion de SQL profile, un objet contenant des informations par rapport à une requête donnée (définie par son SQL_ID) et qui permet au CBO de choisir un plan optimal lors du parsing.

Le blog Oracle d'Ahmed AANGOUR: Forcer un plan d'exécution via un SQL profile

Ce SQL profile est en générale défini lorsqu'on fait appel au Tuning advisor pour analyser une requête non performante (un article plus détaillé est à venir sur ce sujet). Le SQL profile, à l'inverse des OUTLINES apparus avec la 8i ou du SQL Plan Management apparu en 11g, n'a pas pour but de forcer un plan d'exécution mais plutôt d'augmenter la flexibilité de l'optimiseur. Toutefois, une astuce géniale découverte sur le blog de Randolf GEIST permet de forcer pour une requête donnée un plan existant dans le référentiel AWR ou dans la shared pool en utilisant un SQL profile. Imaginons que vous ayez une requête sensible exécutée tous les soirs pendant un process batch et que cette requête met en général 10 minutes pour s'exécuter.

What is the difference between SQL Profiles and SQL Plan Baselines? (Oracle Optimizer) Since Oracle Database 11g was released I have gotten a lot of questions about the difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can't.

What is the difference between SQL Profiles and SQL Plan Baselines? (Oracle Optimizer)

So I thought it would be a good idea to write a post explaining the differences between them and how they interact. But first let's briefly recap each feature. The query optimizer normally uses information like object and system statistics, compilation environment, bind values and so on to determine the best plan for a SQL statement. Interpreting Explain plan. What's an explain plan?

Interpreting Explain plan

An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Query processing can be divided into 7 phases: Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement. Time Model. Tasks that are performed via jobs in the database will be double accounted in the system time model that has been introduced with Oracle 10g.

Time Model

So if you execute significant workload via DBMS_JOB or DBMS_SCHEDULER any system time model related statistic like DB Time, DB CPU etc. that gets recorded for that workload gets double accounted. This bug is not particularly relevant since your top workloads will still be the same top workloads, because all other statistics (like Elapsed Time, CPU, Buffer Gets etc.) are not affected by the bug. I mention it only here since the bug (see below for details) as of the time of writing can't yet be found on My Oracle Support in the bug database but I recently came across several AWR reports where the majority of workload was generated via job processes and therefore the time model statistics were effectively doubled.

The following simple test case can be used to reproduce the issue at will. DBMS_XPLAN. The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats.

DBMS_XPLAN

Oracle 12c Hints. Conn hr/hr@pdborcl set autotrace traceonly exp SELECT employee_id, last_name FROM employees e WHERE last_name = 'Smith'; SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name FROM employees e WHERE last_name = 'Smith';

Oracle 12c Hints

Influencing the Optimizer. Use hints to influence the optimizer mode, query transformation, access path, join order, and join methods.

Influencing the Optimizer

For example, The following figure shows how you can use a hint to tell the optimizer to use a specific index for a specific statement. The advantage of hints is that they enable you to make decisions normally made by the optimizer. In a test environment, hints are useful for testing the performance of a specific access path. For example, you may know that an index is more selective for certain queries, as in Figure 14-2. In this case, the hint may cause the optimizer to generate a better plan. The disadvantage of hints is the extra code that you must manage, check, and control. Oracle provides several tools, including SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer. All These Oracle SQL Statement IDs. By Sergei Romanenko Edited by Gary Gordhamer Submitted to SELECT Journal in January, 2013 Published by SELECT Journal in vol. 20, Number 2, 2013 Abstract Different IDs have been used in Oracle database to refer a particular SQL statement.

How many different IDs do you know for a SQL statement? Look into the data provided by the v$sql view: HASH_VALUE, ADDRESS, CHILD_ADDRESS, SQL_ID, EXACT_MATCHING_SIGNATURE, FORCE_MATCHING_SIGNATURE... It may be difficult to know if one ID is the same, will another ID be same as well?