Что такое план запроса в sql oracle

Приемы работы с планами выполнения запросов в Oracle

Это как гвоздь в подошве любимого ботинка. Ходить можно, но все чаще ловишь себя на желании остаться на месте или перепоручить дело другим. Мелкие неудобства не только замедляют нашу работу, но и снижают мотивацию, вносят помехи в процесс, снижают качество результата. И если нашелся друг, который научил вас взять молоток и забить этот гвоздь, вы не только будете благодарны ему за помощь, но и сами поможете другим, избавив их от мелкой, но очень раздражающей помехи. Для этого и нужно общаться, делиться не только глубокими и сокровенными знаниями в форумах и на сайтах вроде Хабра, но и своими простыми трюками и «маленькими хитростями»

Как и любой текст, запросы и программы на SQL можно создавать в любом текстовом редакторе. Но если вы профессионал, вы очень много и часто работаете с SQL, то вам уже не будет достаточно наличия подсветки синтаксиса и автоматического переформатирования кода, особенно, если вам приходится переключаться между различными версиями одной СУБД или разными платформами СУБД.

Недавно мне случилось общаться с одним из ведущих профессионалов СУБД Oracle. Он рассказал много интересного про работу с планами выполнения запросов в различных версиях этой СУБД и не постеснялся рассказать всем об используемых им инструментах, приемах и дать немного полезных мелких советов. Я сделал перевод одной из статей в его блоге и хотел бы предложить его вниманию Хабравчан. Несмотря на то, что описанный прием применялся для работы с Oracle, я теперь с успехом применяю тот же подход для MS SQL и Sybase.

Меня зовут Дан Хотка (Dan Hotka). Я директор Oracle ACE. Одной из моих привилегий в этой группе является помощь в распространении информации и полезных технических знаний, связанных с СУБД Oracle. Меня хорошо знают после моих 12 (скоро 14) опубликованных книг и буквально сотен статей. Я регулярно пишу в блоге и собираюсь делать это в дальнейшем. Мы даже могли встречаться на одном из событий или встреч группы пользователей. Я регулярно выступаю на эти темы по всему миру.
Я собираюсь поделиться с вами как техническими знаниями про Oracle, так и тем, как эти знания применяются в решениях Embarcadero.

Я скачал себе «большую тройку» продуктов Embarcadero: Rapid Sql, DBArtisan, DB PowerStudio. Сейчас я хотел бы рассказать о первом впечатлении и некоторых приемах работы с планами выполнения запросов в RapidSQL. (Я установил версию 8.6.1)
Я покажу пару приемчиков для планов выполнения запросов в и вокруг Rapid SQL.
Мне нравится инструмент. Конечно, это прекрасный инструмент, если у вас есть разные типы СУБД различных производителей, поскольку этот инструмент поддерживает около дюжины разных СУБД. Единый интерфейс для освоения всех БД! Мои приемчики относятся к Oracle. Но приемы для инструментов Embarcadero должны сработать вне зависимости от того, к какой СУБД вы подключились.
При просмотре планов выполнения я люблю видеть план выполнения и сам запрос одновременно.
Этого легко достигнуть.
Для начала, загрузите свой SQL запрос в окно редактора ISQL (используя кнопку Open), затем включите кнопку Explain Plan (отмечена в красном круге). Кнопка останется активированной.

Запустите запрос на выполнение, и появится закладка Query Plan, заполненная планом выполнения.

Поместите курсор мыши на любой из узлов на диаграмме и появится дополнительная полезная информация, относящаяся к этому шагу выполнения из плана запроса!
По умолчанию, Rapid SQL показывает план выполнения в графическом виде. Я вышел из старого мира оптимизации…. Предпочитаю текстовую версию, поэтому нажимаю правую кнопку мыши в окне с планом и выбираю “View as Text”.
Предпочитаю видеть текст запроса и план одновременно.

Это легко сделать. Видите закладки окон ISQL внизу главного окна? Для начала мы должны настроить Rapid SQL, чтобы он выдавал план в отдельном окне.

Нажмите кнопку Options (левый красный кружок) и затем установите опцию ‘Unattached’ для Result window. Это приведет к созданию двух отдельных закладок внизу Rapid SQL, после запуска запроса на выполнение. Просто протащите немного это окно за закладку и появится прямоугольник, куда можно переместить это окно.
Или можно воспользоваться пунктом Tile windows из главного меню программы

И еще: все это так же работает и в DBArtisan — решении для администраторов баз данных.

Что такое план запроса в sql oracle

Knowledge of how to explain a statement and display its plan is essential to SQL tuning.

6.1 Introduction to Execution Plans

An execution plan is the sequence of operations that the database performs to run a SQL statement.

6.1.1 Contents of an Execution Plan

The execution plan operation alone cannot differentiate between well-tuned statements and those that perform suboptimally.

The plan consists of a series of steps. Every step either retrieves rows of data physically from the database or prepares them for the user issuing the statement. The following plan shows a join of the employees and departments tables:

The row source tree is the core of the execution plan. The tree shows the following information:

The join order of the tables referenced by the statement

In the preceding plan, employees is the outer row source and departments is the inner row source.

An access path for each table mentioned in the statement

In the preceding plan, the optimizer chooses to access employees using an index scan and departments using a full scan.

A join method for tables affected by join operations in the statement

In the preceding plan, the optimizer chooses a nested loops join.

Data operations like filter, sort, or aggregation

In the preceding plan, the optimizer filters on last names that begin with T and matches on department_id .

In addition to the row source tree, the plan table contains information about the following:

Optimization, such as the cost and cardinality of each operation

Partitioning, such as the set of accessed partitions

Parallel execution, such as the distribution method of join inputs

6.1.2 Why Execution Plans Change

Execution plans can and do change as the underlying optimizer inputs change.

To avoid possible SQL performance regression that may result from execution plan changes, consider using SQL plan management.

6.1.2.1 Different Schemas

Schemas can differ for various reasons.

Principal reasons include the following:

The execution and explain plan occur on different databases.

The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.

Schema changes (often changes in indexes) between the two operations.

6.1.2.2 Different Costs

Even if the schemas are the same, the optimizer can choose different execution plans when the costs are different.

Some factors that affect the costs include the following:

Data volume and statistics

Bind variable types and values

Initialization parameters set globally or at session level

6.2 Generating Plan Output Using the EXPLAIN PLAN Statement

The EXPLAIN PLAN statement enables you to examine the execution plan that the optimizer chose for a SQL statement.

6.2.1 About the EXPLAIN PLAN Statement

The EXPLAIN PLAN statement displays execution plans that the optimizer chooses for SELECT , UPDATE , INSERT , and DELETE statements.

EXPLAIN PLAN output shows how the database would have run the SQL statement when the statement was explained. Because of differences in the execution environment and explain plan environment, the explained plan can differ from the actual plan used during statement execution.

When the EXPLAIN PLAN statement is issued, the optimizer chooses an execution plan and then inserts a row describing each step of the execution plan into a specified plan table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.

The EXPLAIN PLAN statement is a DML statement rather than a DDL statement. Therefore, Oracle Database does not implicitly commit the changes made by an EXPLAIN PLAN statement.

Oracle Database SQL Language Reference to learn about the EXPLAIN PLAN statement

6.2.1.1 About PLAN_TABLE

PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

Oracle Database automatically creates a global temporary table PLAN_TABLE$ in the SYS schema, and creates PLAN_TABLE as a synonym. All necessary privileges to PLAN_TABLE are granted to PUBLIC . Consequently, every session gets its own private copy of PLAN_TABLE in its temporary tablespace.

You can use the SQL script catplan.sql to manually create the global temporary table and the PLAN_TABLE synonym. The name and location of this script depends on your operating system. On UNIX and Linux, the script is located in the $ORACLE_HOME/rdbms/admin directory. For example, start a SQL*Plus session, connect with SYSDBA privileges, and run the script as follows:

The definition of a sample output table PLAN_TABLE is available in a SQL script on your distribution media. Your output table must have the same column names and data types as this table. The common name of this script is utlxplan.sql . The exact name and location depend on your operating system.

Oracle Database SQL Language Reference for a complete description of EXPLAIN PLAN syntax.

6.2.1.2 EXPLAIN PLAN Restrictions

Oracle Database does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables.

With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.

From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is VARCHAR , and gives an error message otherwise. You can avoid this limitation by putting appropriate type conversions in the SQL statement.

6.2.2 Explaining a SQL Statement: Basic Steps

Use EXPLAIN PLAN to store the plan for a SQL statement in PLAN_TABLE .

This task assumes that a sample output table named PLAN_TABLE exists in your schema. If this table does not exist, then run the SQL script catplan.sql .

To execute EXPLAIN PLAN , you must have the following privileges:

You must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan

You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, then you must have privileges to access both the other view and its underlying table.

To examine the execution plan produced by an EXPLAIN PLAN statement, you must have the privileges necessary to query the output table.

To explain a statement:

Start SQL*Plus or SQL Developer, and log in to the database as a user with the requisite permissions.

Include the EXPLAIN PLAN FOR clause immediately before the SQL statement.

The following example explains the plan for a query of the employees table:

After issuing the EXPLAIN PLAN statement, use a script or package provided by Oracle Database to display the most recent plan table output.

The following example uses the DBMS_XPLAN.DISPLAY function:

Review the plan output.

For example, the following plan shows a hash join:

The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

The steps in the EXPLAIN PLAN output in this chapter may be different on your database. The optimizer may choose different execution plans, depending on database configurations.

Oracle Database SQL Language Reference for the syntax and semantics of EXPLAIN PLAN

6.2.3 Specifying a Statement ID in EXPLAIN PLAN: Example

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan.

Before using SET STATEMENT ID , remove any existing rows for that statement ID. In the following example, st1 is specified as the statement identifier.

Example 6-1 Using EXPLAIN PLAN with the STATEMENT ID Clause

6.2.4 Specifying a Different Location for EXPLAIN PLAN Output: Example

The INTO clause of EXPLAIN PLAN specifies a different table in which to store the output.

If you do not want to use the name PLAN_TABLE , create a new synonym after running the catplan.sql script. For example:

The following statement directs output to my_plan_table :

You can specify a statement ID when using the INTO clause, as in the following statement:

«PLAN_TABLE Columns» for a description of the columns in PLAN_TABLE

6.2.5 EXPLAIN PLAN Output for a CONTAINERS Query: Example

The CONTAINERS clause can be used to query both user-created and Oracle-supplied tables and views. It enables you to query these tables and views across all containers.

The following example illustrates the output of an EXPLAIN PLAN for a query using the CONTAINERS clause.

At Row 8 of this plan, CONTAINERS is shown in the Operation column as the value CONTAINERS FULL . The Name column in the same row shows the argument to CONTAINERS .

A query using the CONTAINERS clause is partitioned by default. At Row 7 in the plan, the PX PARTITION LIST ITERATOR in the Operation column indicates that the query is partitioned. Iteration over containers is implemented in this partition iterator. On the same row, the Pstart and Pstop values 1 and 9 are derived from the con_id < 10 predicate in the query.

A query using the CONTAINERS clause uses parallel execution servers by default. In Row 1 of the plan above, PX COORDINATOR in the Operation column indicates that parallel execution servers will be used. Each container is assigned to a parallel execution process ( P00* ). When the parallel execution process executes the part of the query EXECUTION PLAN that corresponds to CONTAINERS FULL , then the process switches into the container it has been assigned to work on. It retrieves rows from the base object by executing a recursive SQL statement.

6.3 Displaying Execution Plans

The easiest way to display execution plans is to use DBMS_XPLAN display functions or V$ views.

6.3.1 About the Display of PLAN_TABLE Output

To display the plan table output, you can use either SQL scripts or the DBMS_XPLAN package.

After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle Database to display the most recent plan table output:

DBMS_XPLAN.DISPLAY table function

This function accepts options for displaying the plan table output. You can specify:

A plan table name if you are using a table different than PLAN_TABLE

A statement ID if you have set a statement ID with the EXPLAIN PLAN

A format option that determines the level of detail: BASIC , SERIAL , TYPICAL , and ALL

Examples of using DBMS_XPLAN to display PLAN_TABLE output are:

This script displays the plan table output for serial processing

This script displays the plan table output including parallel execution columns.

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

6.3.1.1 DBMS_XPLAN Display Functions

You can use the DBMS_XPLAN display functions to show plans.

The display functions accept options for displaying the plan table output. You can specify:

A plan table name if you are using a table different from PLAN_TABLE

A statement ID if you have set a statement ID with the EXPLAIN PLAN

A format option that determines the level of detail: BASIC , SERIAL , TYPICAL , ALL , and in some cases ADAPTIVE

Table 6-1 DBMS_XPLAN Display Functions

This table function displays the contents of the plan table.

In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the specified table to select rows of the plan to display.

The format parameter controls the level of the plan. It accepts the values BASIC , TYPICAL , SERIAL , and ALL .

This table function displays the contents of an execution plan stored in AWR.

The format parameter controls the level of the plan. It accepts the values BASIC , TYPICAL , SERIAL , and ALL .

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS ).

The format parameter controls the level of the plan. It accepts the values BASIC , TYPICAL , SERIAL , ALL , and ADAPTIVE . When you specify ADAPTIVE , the output includes:

The final plan. If the execution has not completed, then the output shows the current plan. This section also includes notes about run-time optimizations that affect the plan.

Recommended plan. In reporting mode, the output includes the plan that would be chosen based on execution statistics.

Dynamic plan. The output summarizes the portions of the plan that differ from the default plan chosen by the optimizer.

Reoptimization. The output displays the plan that would be chosen on a subsequent execution because of reoptimization.

This table function displays the contents of the plan table in a variety of formats with CLOB output type.

The format parameter controls the level of the plan. It accepts the values BASIC , TYPICAL , SERIAL , ALL , and ADAPTIVE . When you specify ADAPTIVE , the output includes the default plan. For each dynamic subplan, the plan shows a list of the row sources from the original that may be replaced, and the row sources that would replace them.

If the format argument specifies the outline display, then the function displays the hints for each option in the dynamic subplan. If the plan is not an adaptive query plan, then the function displays the default plan. When you do not specify ADAPTIVE , the plan is shown as-is, but with additional comments in the Note section that show any row sources that are dynamic.

This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

This function uses plan information stored in the plan baseline to explain and display the plans. The plan_id stored in the SQL management base may not match the plan_id of the generated plan. A mismatch between the stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.

This table function displays the execution plan of a given statement stored in a SQL tuning set.

The format parameter controls the level of the plan. It accepts the values BASIC , TYPICAL , SERIAL , and ALL .

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_XPLAN display functions

6.3.1.2 Plan-Related Views

You can obtain information about execution plans by querying dynamic performance and data dictionary views.

Table 6-2 Execution Plan Views

Lists statistics for cursors and contains one row for each child of the original SQL text entered.

Starting in Oracle Database 19c , V$SQL.QUARANTINED indicates whether a statement has been terminated by the Resource Manager because the statement consumed too many resources. Oracle Database records and marks the quarantined plans and prevents the execution of statements using these plans from executing. The AVOIDED_EXECUTIONS column indicates the number of executions attempted but prevented because of the quarantined statement.

Explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

The USE_FEEDBACK_STATS column shows whether a child cursor fails to match because of reoptimization.

Contains the plan for every statement stored in the shared SQL area.

The view definition is similar to PLAN_TABLE . The view includes a superset of all rows appearing in all final plans. PLAN_LINE_ID is consecutively numbered, but for a single final plan, the IDs may not be consecutive.

As an alternative to EXPLAIN PLAN , you can display the plan by querying V$SQL_PLAN . The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN , you would need to set up an identical environment to get the same plan when executing the statement.

Provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs. The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL .

Contains memory usage statistics for row sources that use SQL memory (sort or hash join). This view concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA .

V$SQL_PLAN_STATISTICS_ALL enables side-by-side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

«Monitoring Database Operations» for information about the V$SQL_PLAN_MONITOR view

Oracle Database Reference for more information about V$SQL_PLAN views

Oracle Database Reference for information about the STATISTICS_LEVEL initialization parameter

6.3.2 Displaying Execution Plans: Basic Steps

The DBMS_XPLAN.DISPLAY function is a simple way to display an explained plan.

By default, the DISPLAY function uses the format setting of TYPICAL . In this case, the plan the most relevant information in the plan: operation id, name and option, rows, bytes and optimizer cost. Pruning, parallel and predicate information are only displayed when applicable.

To display an execution plan:

Start SQL*Plus or SQL Developer and log in to the session in which you explained the plan.

Query PLAN_TABLE using DBMS_XPLAN.DISPLAY .

Specify the query as follows:

Alternatively, specify the statement ID using the statement_id parameter:

Example 6-2 EXPLAIN PLAN for Statement ID ex_plan1

This example explains a query of employees that uses the statement ID ex_plan1 , and then queries PLAN_TABLE :

Sample output appears below:

Example 6-3 EXPLAIN PLAN for Statement ID ex_plan2

This example explains a query of employees that uses the statement ID ex_plan2 , and then displays the plan using the BASIC format:

Sample output appears below:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

6.3.3 Displaying Adaptive Query Plans: Tutorial

The adaptive optimizer is a feature of the optimizer that enables it to adapt plans based on run-time statistics. All adaptive mechanisms can execute a final plan for a statement that differs from the default plan.

An adaptive query plan chooses among subplans during the current statement execution. In contrast, automatic reoptimization changes a plan only on executions that occur after the current statement execution.

You can determine whether the database used adaptive query optimization for a SQL statement based on the comments in the Notes section of plan. The comments indicate whether row sources are dynamic, or whether automatic reoptimization adapted a plan.

This tutorial assumes the following:

The STATISTICS_LEVEL initialization parameter is set to ALL .

The database uses the default settings for adaptive execution.

As user oe , you want to issue the following separate queries:

Before executing each query, you want to query DBMS_XPLAN.DISPLAY_PLAN to see the default plan, that is, the plan that the optimizer chose before applying its adaptive mechanism.

After executing each query, you want to query DBMS_XPLAN.DISPLAY_CURSOR to see the final plan and adaptive query plan.

SYS has granted oe the following privileges:

GRANT SELECT ON V_$SESSION TO oe

GRANT SELECT ON V_$SQL TO oe

GRANT SELECT ON V_$SQL_PLAN TO oe

GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO oe

To see the results of adaptive optimization:

Start SQL*Plus, and then connect to the database as user oe .

For example, use the following statement:

View the plan in the cursor.

For example, run the following commands:

The following sample output has been reformatted to fit on the page. In this plan, the optimizer chooses a nested loops join. The original optimizer estimates are shown in the E-Rows column, whereas the actual statistics gathered during execution are shown in the A-Rows column. In the MERGE JOIN operation, the difference between the estimated and actual number of rows is significant.

Run the same query of orders that you ran in Step 2.

View the execution plan in the cursor by using the same SELECT statement that you ran in Step 3.

The following example shows that the optimizer has chosen a different plan, using a hash join. The Note section shows that the optimizer used statistics feedback to adjust its cost estimates for the second execution of the query, thus illustrating automatic reoptimization.

Query V$SQL to verify the performance improvement.

The following query shows the performance of the two statements (sample output included).

The second statement executed, which is child number 1 , used statistics feedback. CPU time, elapsed time, and buffer gets are all significantly lower.

Explain the plan for the query of order_items .

For example, use the following statement:

View the plan in the plan table.

For example, run the following statement:

Sample output appears below:

In this plan, the optimizer chooses a nested loops join.

Run the query that you previously explained.

For example, use the following statement:

View the plan in the cursor.

For example, run the following commands:

Sample output appears below. Based on statistics collected at run time (Step 4), the optimizer chose a hash join rather than the nested loops join. The dashes ( — ) indicate the steps in the nested loops plan that the optimizer considered but do not ultimately choose. The switch illustrates the adaptive query plan feature.

Oracle Database Reference to learn about the STATISTICS_LEVEL initialization parameter

6.3.4 Display Execution Plans: Examples

These examples show different ways of displaying execution plans.

6.3.4.1 Customizing PLAN_TABLE Output

If you have specified a statement identifier, then you can write your own script to query the PLAN_TABLE .

Start with and given STATEMENT_ID .

Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATMENT_ID and PARENT_ID = PRIOR ID .

Use the pseudo-column LEVEL (associated with CONNECT BY ) to indent the children.

The NULL in the Rows column indicates that the optimizer does not have any statistics on the table. Analyzing the table shows the following:

You can also select the COST . This is useful for comparing execution plans or for understanding why the optimizer chooses one execution plan over another.

These simplified examples are not valid for recursive SQL.

6.3.4.2 Displaying Parallel Execution Plans: Example

Plans for parallel queries differ in important ways from plans for serial queries.

6.3.4.2.1 About EXPLAIN PLAN and Parallel Queries

Tuning a parallel query begins much like a non-parallel query tuning exercise by choosing the driving table. However, the rules governing the choice are different.

In the serial case, the best driving table produces the fewest numbers of rows after applying limiting conditions. The database joins a small number of rows to larger tables using non-unique indexes.

For example, consider a table hierarchy consisting of customer , account , and transaction .

Figure 6-1 A Table Hierarchy

In this example, customer is the smallest table, whereas transaction is the largest table. A typical OLTP query retrieves transaction information about a specific customer account. The query drives from the customer table. The goal is to minimize logical I/O, which typically minimizes other critical resources including physical I/O and CPU time.

For parallel queries, the driving table is usually the largest table. It would not be efficient to use parallel query in this case because only a few rows from each table are accessed. However, what if it were necessary to identify all customers who had transactions of a certain type last month? It would be more efficient to drive from the transaction table because no limiting conditions exist on the customer table. The database would join rows from the transaction table to the account table, and then finally join the result set to the customer table. In this case, the used on the account and customer table are probably highly selective primary key or unique indexes rather than the non-unique indexes used in the first query. Because the transaction table is large and the column is not selective, it would be beneficial to use parallel query driving from the transaction table.

Parallel operations include the following:

A PARALLEL_TO_SERIAL operation is always the step that occurs when the query coordinator consumes rows from a parallel operation. Another type of operation that does not occur in this query is a SERIAL operation. If these types of operations occur, then consider making them parallel operations to improve performance because they too are potential bottlenecks.

If the workloads in each step are relatively equivalent, then the PARALLEL_TO_PARALLEL operations generally produce the best performance.

A PARALLEL_COMBINED_WITH_PARENT operation occurs when the database performs the step simultaneously with the parent step.

If a parallel step produces many rows, then the QC may not be able to consume the rows as fast as they are produced. Little can be done to improve this situation.

The OTHER_TAG column in «PLAN_TABLE Columns»

6.3.4.2.2 Viewing Parallel Queries with EXPLAIN PLAN: Example

When using EXPLAIN PLAN with parallel queries, the database compiles and executes one parallel plan. This plan is derived from the serial plan by allocating row sources specific to the parallel support in the QC plan.

The table queue row sources ( PX Send and PX Receive ), the granule iterator, and buffer sorts, required by the two parallel execution server set PQ model, are directly inserted into the parallel plan. This plan is the same plan for all parallel execution servers when executed in parallel or for the QC when executed serially.

Example 6-4 Parallel Query Explain Plan

The following simple example illustrates an EXPLAIN PLAN for a parallel query:

One set of parallel execution servers scans EMP2 in parallel, while the second set performs the aggregation for the GROUP BY operation. The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces to divide the scan workload between the parallel execution servers. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two sets of parallel execution servers as rows flow up from the parallel scan, get repartitioned through the HASH table queue, and then read by and aggregated on the top set. The PX SEND QC row source represents the aggregated values being sent to the QC in random (RAND) order. The PX COORDINATOR row source represents the QC or Query Coordinator which controls and schedules the parallel plan appearing below it in the plan tree.

6.3.4.3 Displaying Bitmap Index Plans: Example

Index row sources using bitmap indexes appear in the EXPLAIN PLAN output with the word BITMAP indicating the type of the index.

Example 6-5 EXPLAIN PLAN with Bitmap Indexes

In this example, the predicate c1=2 yields a bitmap from which a subtraction can take place. From this bitmap, the bits in the bitmap for c2=6 are subtracted. Also, the bits in the bitmap for c2 IS NULL are subtracted, explaining why there are two MINUS row sources in the plan. The NULL subtraction is necessary for semantic correctness unless the column has a NOT NULL constraint. The TO ROWIDS option generates the rowids necessary for the table access.

Queries using bitmap join index indicate the bitmap join index access path. The operation for bitmap join index is the same as bitmap index.

6.3.4.4 Displaying Result Cache Plans: Example

When your query contains the result_cache hint, the ResultCache operator is inserted into the execution plan.

For example, consider the following query:

To view the EXPLAIN PLAN for this query, use the following command:

The EXPLAIN PLAN output for this query should look similar to the following:

In this EXPLAIN PLAN , the ResultCache operator is identified by its CacheId , which is b06ppfz9pxzstbttpbqyqnfbmy . You can now run a query on the V$RESULT_CACHE_OBJECTS view by using this CacheId .

6.3.4.5 Displaying Plans for Partitioned Objects: Example

Use EXPLAIN PLAN to determine how Oracle Database accesses partitioned objects for specific queries.

Partitions accessed after pruning are shown in the PARTITION START and PARTITION STOP columns. The row source name for the range partition is PARTITION RANGE . For hash partitions, the row source name is PARTITION HASH .

A join is implemented using partial partition-wise join if the DISTRIBUTION column of the plan table of one of the joined tables contains PARTITION(KEY) . Partial partition-wise join is possible if one of the joined tables is partitioned on its join column and the table is parallelized.

A join is implemented using full partition-wise join if the partition row source appears before the join row source in the EXPLAIN PLAN output. Full partition-wise joins are possible only if both joined tables are equipartitioned on their respective join columns. Examples of execution plans for several types of partitioning follow.

6.3.4.5.1 Displaying Range and Hash Partitioning with EXPLAIN PLAN: Examples

This example illustrates pruning by using the emp_range table, which partitioned by range on hire_date .

Assume that the tables employees and departments from the Oracle Database sample schema exist.

For the first example, consider the following statement:

Oracle Database displays something similar to the following:

The database creates a partition row source on top of the table access row source. It iterates over the set of partitions to be accessed. In this example, the partition iterator covers all partitions (option ALL ), because a predicate was not used for pruning. The PARTITION_START and PARTITION_STOP columns of the PLAN_TABLE show access to all partitions from 1 to 5.

For the next example, consider the following statement:

In the previous example, the partition row source iterates from partition 4 to 5 because the database prunes the other partitions using a predicate on hire_date .

Finally, consider the following statement:

In the previous example, only partition 1 is accessed and known at compile time; thus, there is no need for a partition row source.

Oracle Database displays the same information for hash partitioned objects, except the partition row source name is PARTITION HASH instead of PARTITION RANGE . Also, with hash partitioning, pruning is only possible using equality or IN -list predicates.

6.3.4.5.2 Pruning Information with Composite Partitioned Objects: Examples

To illustrate how Oracle Database displays pruning information for composite partitioned objects, consider the table emp_comp . It is range-partitioned on hiredate and subpartitioned by hash on deptno .

For the first example, consider the following statement:

This example shows the plan when Oracle Database accesses all subpartitions of all partitions of a composite object. The database uses two partition row sources for this purpose: a range partition row source to iterate over the partitions, and a hash partition row source to iterate over the subpartitions of each accessed partition.

In the following example, the range partition row source iterates from partition 1 to 5, because the database performs no pruning. Within each partition, the hash partition row source iterates over subpartitions 1 to 3 of the current partition. As a result, the table access row source accesses subpartitions 1 to 15. In other words, the database accesses all subpartitions of the composite object.

In the previous example, only the last partition, partition 5, is accessed. This partition is known at compile time, so the database does not need to show it in the plan. The hash partition row source shows accessing of all subpartitions within that partition; that is, subpartitions 1 to 3, which translates into subpartitions 13 to 15 of the emp_comp table.

Now consider the following statement:

In the previous example, the predicate deptno=20 enables pruning on the hash dimension within each partition. Therefore, Oracle Database only needs to access a single subpartition. The number of this subpartition is known at compile time, so the hash partition row source is not needed.

Finally, consider the following statement:

The last two examples are the same, except that department_id = : dno replaces deptno=20 . In this last case, the subpartition number is unknown at compile time, and a hash partition row source is allocated. The option is SINGLE for this row source because Oracle Database accesses only one subpartition within each partition. In Step 2, both PARTITION _ START and PARTITION _ STOP are set to KEY . This value means that Oracle Database determines the number of subpartitions at run time.

6.3.4.5.3 Examples of Partial Partition-Wise Joins

In these examples, the PQ_DISTRIBUTE hint explicitly forces a partial partition-wise join because the query optimizer could have chosen a different plan based on cost in this query.

Example 6-6 Partial Partition-Wise Join with Range Partition

In the following example, the database joins emp_range_did on the partitioning column department_id and parallelizes it. The database can use a partial partition-wise join because the dept2 table is not partitioned. Oracle Database dynamically partitions the dept2 table before the join.

The execution plan shows that the table dept2 is scanned serially and all rows with the same partitioning column value of emp_range_did (department_id) are sent through a PART (KEY) , or partition key, table queue to the same parallel execution server doing the partial partition-wise join.

Example 6-7 Partial Partition-Wise Join with Composite Partition

In the following example, emp_comp is joined on the partitioning column and is parallelized, enabling use of a partial partition-wise join because dept2 is not partitioned. The database dynamically partitions dept2 before the join.

The plan shows that the optimizer selects partial partition-wise join from one of two columns. The PX SEND node type is PARTITION (KEY) and the PQ Distrib column contains the text PART (KEY) , or partition key. This implies that the table dept2 is re-partitioned based on the join column department_id to be sent to the parallel execution servers executing the scan of EMP_COMP and the join.

6.3.4.5.4 Example of Full Partition-Wise Join

In this example, emp_comp and dept_hash are joined on their hash partitioning columns, enabling use of a full partition-wise join.

The PARTITION HASH row source appears on top of the join row source in the plan table output.

The PX PARTITION HASH row source appears on top of the join row source in the plan table output while the PX PARTITION RANGE row source appears over the scan of emp_comp . Each parallel execution server performs the join of an entire hash partition of emp_comp with an entire partition of dept_hash .

6.3.4.5.5 Examples of INLIST ITERATOR and EXPLAIN PLAN

An INLIST ITERATOR operation appears in the EXPLAIN PLAN output if an index implements an IN -list predicate.

Consider the following statement:

The EXPLAIN PLAN output appears as follows:

The INLIST ITERATOR operation iterates over the next operation in the plan for each value in the IN -list predicate. The following sections describe the three possible types of IN -list columns for partitioned tables and indexes.

6.3.4.5.5.1 When the IN-List Column is an Index Column: Example

If the IN -list column empno is an index column but not a partition column, then the IN -list operator appears before the table operation but after the partition operation in the plan.

The KEY(INLIST) designation for the partition start and stop keys specifies that an IN -list predicate appears on the index start and stop keys.

6.3.4.5.5.2 When the IN-List Column is an Index and a Partition Column: Example

If empno is an indexed and a partition column, then the plan contains an INLIST ITERATOR operation before the partition operation.

6.3.4.5.5.3 When the IN-List Column is a Partition Column: Example

If empno is a partition column and no indexes exist, then no INLIST ITERATOR operation is allocated.

If emp_empno is a bitmap index, then the plan is as follows:

6.3.4.5.6 Example of Domain Indexes and EXPLAIN PLAN

You can use EXPLAIN PLAN to derive user-defined CPU and I/O costs for domain indexes.

EXPLAIN PLAN displays domain index statistics in the OTHER column of PLAN_TABLE . For example, assume table emp has user-defined operator CONTAINS with a domain index emp_resume on the resume column, and the index type of emp_resume supports the operator CONTAINS . You explain the plan for the following query:

The database could display the following plan:

6.4 Comparing Execution Plans

The plan comparison tool takes a reference plan and an arbitrary list of test plans and highlights the differences between them. The plan comparison is logical rather than line by line.

6.4.1 Purpose of Plan Comparison

The plan comparison report identifies the source of differences, which helps users triage plan reproducibility issues.

The plan comparison report is particularly useful in the following scenarios:

You want to compare the current plan of a query whose performance is regressing with an old plan captured in AWR.

A SQL plan baseline fails to reproduce the originally intended plan, and you want to determine the difference between the new plan and the intended plan.

You want to determine how adding a hint, changing a parameter, or creating an index will affect a plan.

You want to determine how a plan generated based on a SQL profile or by SQL Performance Analyzer differs from the original plan.

6.4.2 User Interface for Plan Comparison

You can use DBMS_XPLAN.COMPARE_PLANS to generate a report in text, XML, or HTML format.

Compare Plans Report Format

The report begins with a summary. The COMPARE PLANS REPORT section includes information such as the user who ran the report and the number of plans compared, as shown in the following example:

The COMPARISON DETAILS section of the report contains the following information:

The information includes the plan number, the plan source, plan attributes (which differ depending on the source), parsing schema, and SQL text.

This section displays the plan rows, including the predicates and notes.

This section summarizes the comparison findings, highlighting logical differences such as join order, join methods, access paths, and parallel distribution method. The findings start at number 1 . For findings that relate to a particular query block, the text starts with the name of the block. For findings that relate to a particular object alias, the text starts with the name of the query block and the object alias. The following

DBMS_XPLAN.PLAN_OBJECT_LIST Table Type

The plan_object_list type allows for a list of generic objects as input to the DBMS_XPLAN.COMPARE_PLANS function. The syntax is as follows:

The generic object abstracts the common attributes of plans from all plan sources. Every plan source is a subclass of the plan_object_list superclass. The following table summarizes the different plan sources. Note that when an optional parameter is null, it can correspond to multiple objects. For example, if you do not specify a child number for cursor_cache_object , then it matches all cursor cache statements with the specified SQL ID.

Table 6-3 Plan Sources for PLAN_OBJECT_LIST

The parameters are as follows:

owner —The owner of the plan table

plan_table_name —The name of the plan table

statement_id —The ID of the statement (optional)

plan_id —The ID of the plan (optional)

The parameters are as follows:

sql_id —The SQL ID of the plan

child_number —The child number of the plan in the cursor cache (optional)

The parameters are as follows:

sql_id —The SQL ID of the plan

dbid —The database ID (optional)

con_dbid —The CDB ID (optional)

plan_hash_value —The hash value of the plan (optional)

The parameters are as follows:

sqlset_owner —The owner of the SQL tuning set

sqlset_name —The name of the SQL tuning set

sql_id —The SQL ID of the plan

plan_hash_value —The hash value of the plan (optional)

SQL plan management

The parameters are as follows:

sql_handle —The SQL handle of plans protected by SQL plan management

plan_name —The name of the SQL plan baseline (optional)

The profile_name parameter specifies the name of the SQL profile.

The parameters are as follows:

task_name —The name of the advisor task

execution_name —The name of the task execution

sql_id —The SQL ID of the plan

plan_id —The advisor plan ID (optional)

The interface for the compare plan tools is the following function:

The following table describes the parameters that specify that plans to be compared.

Table 6-4 Parameters for the COMPARE_PLANS Function

Specifies a single plan of type generic_plan_object .

Specifies a list of plan objects. An object might correspond to one or more plans.

Example 6-8 Comparing Plans from Child Cursors

This example compares the plan of child cursor number 2 for the SQL ID 8mkxm7ur07za0 with the plan for child cursor number 4 for the same SQL ID.

Example 6-9 Comparing Plan from Child Cursor with Plan from SQL Plan Baseline

This example compares the plan of child cursor number 2 for the SQL ID 8mkxm7ur07za0 with the plan from the SQL plan baseline. The baseline query has a SQL handle of SQL_024d0f7d21351f5d and a plan name of SQL_PLAN_sdfjkd .

Example 6-10 Comparing a Plan with Plans from Multiple Sources

This example prints the summary section only. The program compares the plan of child cursor number 2 for the SQL ID 8mkxm7ur07za0 with every plan in the following list:

All plans in the shared SQL area that are generated for the SQL ID 8mkxm7ur07za0

All plans generated in the SQL tuning set SH. SQLT_WORKLOAD for the SQL ID 6vfqvav0rgyad

All plans in AWR that are captured for database ID 5 and SQL ID 6vfqvav0rgyad

The plan baseline for the query with handle SQL_024d0f7d21351f5d with name SQL_PLAN_sdfjkd

The plan stored in sh.plan_table identified by plan_id=38

The plan identified by the SQL profile name pe3r3ejsfd

All plans stored in SQL advisor identified by task name TASK_1228 , execution name EXEC_1928 , and SQL ID 8mkxm7ur07za0

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

6.4.3 Comparing Execution Plans: Tutorial

To compare plans, use the DBMS_XPLAN.COMPARE_PLANS function.

This tutorial assumes that user sh issued the following queries:

To compare execution plans:

Start SQL*Plus, and log in to the database with administrative privileges.

Query V$SQL to determine the SQL IDs of the two queries.

The following query queries V$SQL for queries that contain the string products :

Log in to the database as user sh .

Execute the DBMS_XPLAN.COMPARE_PLANS function, specifying the SQL IDs obtained in the previous step.

For example, execute the following program:

Print the report.

For example, run the following query:

The Comparison Results section of the following sample report shows that only the first query used a join elimination transformation:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

6.4.4 Comparing Execution Plans: Examples

These examples demonstrate how to generate compare plans reports for queries of tables in the sh schema.

Example 6-11 Comparing an Explained Plan with a Plan in a Cursor

This example explains a plan for a query of tables in the sh schema, and then executes the query:

Assume that the SQL ID of the executed query is 9mp7z6qq83k5y . The following PL/SQL program compares the plan in PLAN_TABLE and the plan in the shared SQL area:

The following sample report shows that the plans are the same:

Example 6-12 Comparing Plans in a Baseline and SQL Tuning Set

Assume that you want to compare the plans for the following queries, which differ only in the NO_MERGE hint contained in the subquery:

The plan for the first query is captured in a SQL plan management baseline with SQL handle SQL_c522f5888cc4613e . The plan for the second query is stored in a SQL tuning set named MYSTS1 and has a SQL ID of d07p7qmrm13nc . You run the following PL/SQL program to compare the plans:

The following output shows that the only the reference plan, which corresponds to the query without the hint, used a view merge:

Example 6-13 Comparing Plans Before and After Adding an Index

In this example, you test the effect of an index on a query plan:

You execute the following PL/SQL program to generate the report:

The following report indicates that the operations in the two plans are different:

Example 6-14 Comparing Plans with Visible and Invisible Indexes

In this example, an application executes the following query:

The plan for this query uses two indexes: sales_prod_bix and products_prod_status_bix . The database generates four plans, using all combinations of visible and invisible for both indexes. Assume that SQL plan management accepts the following plans in the baseline for the query:

sales_prod_bix visible and products_prod_status_bix visible

sales_prod_bix visible and products_prod_status_bix invisible

sales_prod_bix invisible and products_prod_status_bix visible

You make both indexes invisible, and then execute the query again. The optimizer, unable to use the invisible indexes, generates a new plan. The three baseline plans, all of which rely on at least one index being visible, fail to reproduce. Therefore, the optimizer uses the new plan and adds it to the SQL plan baseline for the query. To compare the plan currently in the shared SQL area, which is the reference plan, with all four plans in the baseline, you execute the following PL/SQL code:

The following report compares all five plans:

The preceding report shows the following:

Plan 1 is the reference plan from the shared SQL area. The plan does not use the indexes, which are both invisible, and does not reproduce a baseline plan.

Plan 2 is in the baseline and assumes sales_prod_bix is invisible and products_prod_status_bix is visible.

Plan 3 is in the baseline and assumes both indexes are invisible. Plan 1 and Plan 3 are the same.

Plan 4 is in the baseline and assumes sales_prod_bix is visible and products_prod_status_bix is invisible.

Plan 5 is in the baseline and assumes that both indexes are visible.

The comparison report shows that Plan 1 could not reproduce a plan from that baseline. The reason is that the plan in the cursor (Plan 1) was added to the baseline because no baseline plan was available at the time of execution, so the database performed a soft parse of the statement and generated the no-index plan. If the current cursor were to be invalidated, and if the query were to be executed again, then a comparison report would show that the cursor plan did reproduce a baseline plan.

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

Example 6-15 Comparing a Baseline That Fails to Reproduce

One use case is to compare a cost-based plan with a SQL plan baseline. In this example, you create a unique index. The database captures a plan baseline that uses this index. You then make the index invisible and execute the query again. The baseline plan fails to reproduce because the index is not visible, forcing the optimizer to choose a different plan. A compare plans report between the baseline plan and the cost-based plan shows the difference in the access path between the two plans.

Log in to the database as user hr , and then create a plan table:

Execute the following DDL statements, which create a table named staff and an index on the staff.employee_id column:

Execute the following statements to place a query of staff under the protection of SQL Plan Management, and then make the index invisible:

Explain the plan, and then query the plan table (sample output included):

As the preceding output shows, the optimizer chooses a full table scan because the index is invisible. Because the SQL plan baseline uses an index, the optimizer cannot reproduce the plan.

In a separate session, log in as SYS and query the handle and plan name of the SQL plan baseline (sample output included):

Compare the plans, specifying the SQL handle and plan baseline name obtained from the previous step:

Об Oracle на русском

Oracle Database 11g Настройка производительности.

Как читать планы выполнения запросов

• Сортировка таблиц, на которые ссылается оператор
• Метод доступа для каждой таблицы, указанной в операторе
• Метод соединения для таблиц, применяемый операторами соединения в выражении
• Операции с данными, такие как фильтр, сортировка или агрегирование

• Данные оптимизации, такие как стоимость и кардинальность каждой операции
• Данные секционирования, такие как набор партиций к которым выполнялся доступ
• Данные параллельного выполнения, такие как метод распределения операций соединения

• Команда EXPLAIN PLAN позволяет вам просматривать план исполнения, который оптимизатор может использовать для выполнения выражения. Эта команда очень полезна, поскольку она строит план выполнения и записывает его в таблицу, называемую PLAN_TABLE не сохраняя при этом SQL выражение.
• V$SQL_PLAN предоставляет возможность просмотреть планы выполнения для курсоров, которые были недавно выполнены. Информация хранящаяся в V$SQL_LAN очень похожа на информацию, которую выдает команда EXPLAIN PLAN. Однако Explain Plan показывает потенциальный план выполнения, а V$SQL_PLAN хранит планы уже выполнявшихся запросов.
• V$SQL_PLAN_MONITOR содержит статистику мониторинга на уровне планов для каждого SQL выражения, найденного в V$SQL_MONITOR. Каждая строка содержащаяся в V$SQL_PLAN_MONITOR соответствует определенной операции плана исполнения.
• Инфраструктура AWR и Statspack хранят планы выполнения наиболее часто вызываемых SQL. Планы помещаются в представление dBA_HIST_SQL_PLAN или STATS$SQL_PLAN.
• Планы выполнения и источники строк записываются также в фалы трассировки, генерируемые DBMS_MONITOR.
• SQL Management Base — это часть словаря данных, хранящегося в табличном пространстве SYSAUX. Здесь хранится журнальная информация об операциях, история планов выполнения и опорные линии так же как и профили для SQL выражений.
• Событие диагностики 10053 используемое для записи вычислений оптимизатора стоимости также может генерировать планы выполнения запросов.
• Начиная с версии 10.2 когда вы получаете dump состояния процесса, план выполнения также включается в сгенерированый файл трассировки.

• DISPLAY: Используется для форматированного вывода плана выполнения.
• DISPLAY_AWR: Используется для форматированного вывода плана выполнения SQL выражений, хранящихся в репозитории AWR.
• DISPLAY_CURSOR: Используется для форматированного вывода плана выполнения из любого загруженного курсора
• DISPLAY_SQL_PLAN_BASELINE: Используется для форматированного вывода одного и более планов выполнения SQL выражений идентифицируемых по заголовкам.
• DISPLAY_SQLSET: Используется для форматированного вывода плана выполнения хранящегося в SQL Tuning set.

• Команда EXPLAIN PLAN используется для генерации плана выполнения запроса.
• После того как план сгенерирован, его можно посмотреть, запросив информацию из таблицы PLAN_TABLE

• ROWS если это уместно, показывает количество строк, предположительно посчитанное оптимизатором.
• ROWS если это уместно, показывает количество байт, предположительно посчитанное оптимизатором.
• COST если это уместно, показывает стоимость, предположительно посчитанную оптимизатором
• PARTITION если это уместно, показывает отброс патриций оптимизатором
• PARALLEL или это уместно, показывает информацию PX (метод распределения информации и информацию о очередях доступа к таблице )
• PREDICATE или это уместно, показывает информацию о предикате
• PROJECTION или это уместно, показывает секцию проекции

• OFF Отключает использование трассировки
• ON Включает использование автоматической трассировки
• TRACE [ONLY] Включает автоматическую трассировку и подавляет вывод SQL
• EXPLAIN Показывает план выполнения но не показывает сатистику
• STATISTICS Показывает статистику без плана выполнения

Статистика записываемая сервером при выполнении выражения отражает обьем системных ресурсов, затраченных на выполнение выражения и включает в себя следующие основные показатели:

• recursive calls — количество рекурсивных вызовов, сгенерированных на клиентской и серверной стороне.Oracle Database поддерживает таблицы, используемые для внутренней обработки. Когда Oracle Database необходимо внести изменения в этих таблицах, она генерирует внутренний оператор SQL, который, в свою очередь, порождает рекурсивный вызов.
• db block gets — количество раз, которое запрошен блок CURRENT
• consistent gets — количество раз, которое запрошена операция целостного чтения блоков данных.
• physical reads — количество блоков данных, прочитанных с диска. Это число представляет сумму значений physical reads direct и всех чтений из буферного кэша.
• redo size — общее количество генерированного redo в блоках
• bytes sent via SQL*Net to client — общее количество байт переданных клиенту от фонового процесса.
• bytes received via SQL*Net from client — общее количество байт, полученных от клиента Oracle*Net
• SQL*Net roundtrips to/from client — Общее количество сообщений Oracle NET отправленных и полученных от клиента.
• sort (memory) — количество операций сортировки, успешно выполненных в памяти и не потребовали записи на диск.
• sort (disk) — количество операций сортировки которое потребовало выполнения как минимум одной дисковой операции.
• row processed — количество строк, обработанных в процессе выполнения операции.

• ADDRESS
• HASH_VALUE

• SQL_ID — идентификатор SQL родительского курсора в библиотечном кэше
• VERSION_COUNT количество дочерних курсоров которые представлены в кэше для заданного родительского курсора

• ADDRESS представляет адрес заголовка родительского курсора для данного курсора
• HASH_VALUE -значение родительского выражения в библиотечном кэше
• SQL_ID — SQL идентификатор родительского курсора в библиотечном кэше
• PLAN_HASH_VALUE — числовое представление SQL плана для данного курсора
• CHILD_NUMBER — номер дочернего курсора

Используя данное представление можно получить ответы на следующие вопросы:

• Топ-10 рабочих областей, которые требуют наибольшее количество памяти для кэша
• Для рабочих областей, работающих в режиме AUTO, какой процент рабочих областей выполняется с использованием максимального количества памяти?

V$SQLSTATS отображает основную статистику производительности для курсоров SQL, с каждой строкой, представляющей данные сочетающие текст SQL выражения и план выполнения SQL (сочетание SQL_ID и PLAN_HASH_VALUE). Столбцы в V$SQLSTATS идентичны , V$SQL и V$SQLAREA. Тем не менее, представление V$SQLSTATS отличается от V$SQL и V$SQLAREA скоростью обработки, масштабируемостью, большим сроком хранения данных (статистические данные могут храниться в представлении, даже после того, как курсор был вытеснен из разделяемого пула).

Пример запроса данных из представления V$SQL_PLAN

• IOSTATS: Предполагая что в процессе выполнения SQL собирается базовая статистика для планов исполнения параметр STATISTICS_LEVEL установлен в ALL или используется HINT GATHER_PLAN_STATISTICS) этот формат отображает статистику ввода/вывода для всех при указании ALL ( или только для последнего при указании LAST) выполнений курсора.
• MEMSTATS: Предполагая что используется автоматическое управление PGA (параметр pga_aggregate_target установлен в не нулевое значение) этот формат позволяет показать статистику использования памяти данный вид статистики применим только к операциям, интенсивно использующим память, таким как например HASH Join, сортировка или некоторые из bitmap операторов.
• ALLSTATS: Синоним для ‘IOSTATS MEMSTATS’
• LAST: По умолчанию, статистика планов выполнения показывается для всех выполнений курсора. Используя ключевое слово LAST можно просмотреть статистику плана, генерированную после его последнего выполнения.

• V$ACTIVE_SESSION_HISTORY — данное представление показывает информацию о последней активности сессий, пополняемую каждую секунду.
• Представления V$ metric представляют данные метрик для отслеживания производительности системы. Список представлений метрик можно просмотреть обратившись к представлению V$METRICGROUP.
• Представления DBA_HIST содержат исторические данные, хранящиеся в базе данных. Эта группа представлений включает в себя:

1. DBA_HIST_ACTIVE_SESS_HISTORY содержит содержимое отобранной из памяти истории активного сеанса по недавней активности системы
2. DBA_HIST_BASELINE содержит информацию о опорных линиях, хранящуюся в базе данных.
3. DBA_HIST_DATABASE_INSTANCE содержит информацию об окружении БД
4. DBA_HIST_SNAPSHOT содержит информацию о снэпшотах хранимых в системе
5. DBA_HIST_SQL_PLAN содержит информацию о планах выполнения
6. DBA_HIST_WR_CONTROL содержит информацию о настройках AWR

• SQL_ID
• Внутренне генерированный идентификатор для того чтобы убедиться, что данный первичный ключ на самом деле уникален (SQL_EXEC_ID)
• Временная метка начала исполнения выражения (SQL_EXEC_START)

1. Возьмите ID с самым низким значением и поместите его вверху дерева.
2. Определите строки, имеющие PID (parrent id) равные этому значению.
3. Поместите их в дерево ниже родительской записи в соответствии с их POS значениями от меньшего к большему слева на право.
4. После того как все ID родителя будут найдены, переместитесь на уровень вниз к следующему ID и повторите процесс, находя новые строки с одним PID.

1. Начните сверху
2. Переместитесь вниз по операциям, пока не дойдете до той, которая производит данные, но при этом не ничего потребляет. Это начало операции.
3. Посмотрите на дочерние операции которые есть у этого родителя. Дочерние операции будут выполняться следующими
4. После того как выполнятся потомки, выполнение родительской операции продолжится
5. Теперь, после того как эта операция и все её потомки выполнены переместитесь вверх по дереву, и смотрите на потомков исходного ряда операций и его родителей. Выполняется, по тому же принципу.
6. Перемещайтесь вверх по дереву до тех пор, пока все операции не будут просмотрены.

1. Начните сверху
2. Переместитесь вниз и влево по дереву пока не достигните левого узла он выполняется первым
3. Посмотрите на потомков этого узла. эти потомки будут выполняться далее.
4. После того как выполнятся потомки, выполнение родительской операции продолжится
5. Теперь, после того как эта операция и все её потомки выполнены переместитесь вверх по дереву, и смотрите на потомков исходного ряда операций и его родителей. Выполняется, по тому же принципу.
6. Перемещайтесь вверх по дереву до тех пор, пока все операции не будут просмотрены

На рисунке выше представлена интерпретация плана выполнения для выражения. Запрос указанный на рисунке пытается найти сотрудников, чья зарплата выбивается и сетки зарплат. Запрос выбирает данные из двух таблиц и включает в себя подзапрос основаный на выборке из другой таблицы для проверки размеров зарплат.
Посмотрим порядок выполнения для этого запроса. Исходя из данного и предыдущего рисунка порядок выполнения будет следующий: 3-5-4-2-6-1:

• 3: выполнение плана начнется с полного сканирования таблицы EMP (ID=3)
• 5: строки передаются шагу, контролирующему объединение nested loop (ID=2), который использует их, чтобы выполнить поиск строк в индексе PK_DEPT (ID=5)
• 4: ROWID строк, полученные после сканирования PK_DEPT используются для получения остальной информации из таблицы DEPT (ID=4)
• 2: ID=2, процесс объединения nested loop будет продолжен до его выполнения
• 6: После того как обработает все исходные строки для объединения, выполнится полное сканирование таблицы SALGRADE (ID=6)
• 1: Данные, полученные после выполнения будут использованы для фильтра строк из и потомки выполняются перед родительскими процессами, не смотря на то что структуры соединений должны быть сформированы до выполнения процессов-потомков. Возможно, самый простой способ объяснить порядок выполнения — для выполнения операции соединения NESTED LOOPS с два потомка должны завершить свое выполнение перед тем как будет выполнен.

How to get the SQL execution plan on Oracle

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

In this article, we are going to see how we can get the SQL execution plan on Oracle.

When tuning performance, the execution plan is indispensable as it allows you to understand the operations done by the database when executing a certain SQL query.

What is the SQL execution plan

As I explained in this article, SQL described the What and not the How, and this is one of the major reasons why SQL has been the most popular way to query a database for over 40 years.

When running a SQL query, the database will first parse it and generate an AST (Abstract Syntax Tree) for the incoming query. The Parser also validates the SQL query syntax and checks whether the referenced database resources exist. The Parser may rewrite the incoming query to optimize its execution without affecting the result set fetching logic.

The generated Query Tree is sent to the Optimizer, which needs to figure out the best way to fetch the data needed by the current SQL query. The Optimizer, therefore, creates an execution plan, which is the list of operations needed to be executed in order to satisfy the SQL query requirements.

The resulting execution plan is sent to the Executor, which runs it and sents the fetched data back to the database client.

The SQL processing and execution steps can be visualized in the following diagram.

The estimated SQL execution plan

The estimated execution plan is generated by the Optimizer without executing the SQL query. You can generate the estimated execution plan from any SQL client using EXPLAIN PLAN FOR or you can use Oracle SQL Developer for this task.

EXPLAIN PLAN FOR

When using Oracle, if you prepend the EXPLAIN PLAN FOR command to a given SQL query, the database will store the estimated execution plan in the associated PLAN_TABLE :

To view the estimated execution plan, you need to use DBMS_XPLAN.DISPLAY , as illustrated in the following example:

The ALL +OUTLINE formatting option allows you to get more details about the estimated execution plan than using the default formatting option.

The estimated plan will look something like this:

Note that some columns were removed to

Oracle SQL Developer

If you have installed SQL Developer, you can easily get the estimated execution plan for any SQL query without having to prepend the EXPLAIN PLAN FOR command:

The actual SQL execution plan

The actual SQL execution plan is generated by the Optimizer when running the SQL query. So, unlike the estimated Execution Plan, you need to execute the SQL query in order to get its actual execution plan.

The actual plan should not differ significantly from the estimated one, as long as the table statistics have been properly collected by the underlying relational database.

GATHER_PLAN_STATISTICS query hint

To get the actual execution plan for a given SQL query, one option is to use the GATHER_PLAN_STATISTICS query hint on the query we want to analyze:

Afterward, to visualize the actual execution plan, we need to query the DBMS_XPLAN.DISPLAY_CURSOR :

And the actual plan will look like this:

You can also use the GATHER_PLAN_STATISTICS query hint to inspect the SQL execution plan associated with a given JPQL or Criteria API query.

For more details about this topic, check out this article.

Enable STATISTICS at the DB connection level

If you want to get the execution plans for all queries generated within a given session, you can set the STATISTICS_LEVEL session setting to ALL :

This will have the same effect as setting the GATHER_PLAN_STATISTICS query hint on every execution query. So, just like with the GATHER_PLAN_STATISTICS query hint, you can use DBMS_XPLAN.DISPLAY_CURSOR to view the actual execution plan.

You should reset the STATISTICS_LEVEL setting to the default mode once you are done collecting the execution plans you were interested in. This is very important, especially if you are using connection pooling, and database connections get reused.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

And there is more!

You can earn a significant passive income stream from promoting all these amazing products that I have been creating.

If you’re interested in supplementing your income, then join my affiliate program.

Conclusion

Knowing the difference between the estimated and the actual SQL execution plan is very important when using a relational database system.

If you want to speed up a certain SQL query, you need to inspect the execution plan to determine what exactly is causing the query to run slowly. Without the SQL execution plan, it’s not possible to tell what the database might be doing under the hood when executing a given SQL query.