Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use dbms_xplan to view the implementation plan, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Overview

The dbms_xplan package provides a variety of predefined ways to interpret execution plans. The sql execution plan, execution time and other information are stored in the view of vinventsqlplans and vaccounsqlplans statistics all. The permission for this package to run is the execution user, not the package owner sys.

When executing diplay_awr, the query permissions of DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT and and V$DATABASE are required.

Query permissions of V$SQL_PLAN and V$SESSION and V$SQL_PLAN_STATISTICS_ALL are required when executing display_cursor

When executing display_sql_plan_baseline, the query permission of DBA_SQL_PLAN_BASELINES is required

When executing display_sqlset, the query permission of ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS is required

All of the above permissions are in the SELECT_CATALOG role, so it's OK to directly authorize SELECT_CATALOG.

There are many functions in dbms_xplan, and I'll focus on a few of them.

SQL > desc dbms_xplan

...

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER (38) IN DEFAULT

DB_ID NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_PLAN RETURNS CLOB

Argument Name Type In/Out Default?

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQLSET_NAME VARCHAR2 IN

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

SQLSET_OWNER VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_HANDLE VARCHAR2 IN DEFAULT

PLAN_NAME VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

...

Dbms_xplan.display

Show the contents of the implementation plan

Simply execute display once

SQL > explain plan for insert into scott.emp values (11 / 11 / 7 / 11 / 5 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11 / 11

Explained.

SQL > select * from table (dbms_xplan.display)

7 rows selected.

Execution Plan

Plan hash value: 2137789089

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0) | 00:00:01 |

| | 1 | COLLECTION ITERATOR PICKLER FETCH | DISPLAY | 8168 | 16336 | 29 (0) | 00:00:01 |

-

Statistics

1890 recursive calls

40 db block gets

3635 consistent gets

0 physical reads

0 redo size

1102 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

38 sorts (memory)

0 sorts (disk)

7 rows processed

SQL > select * from scott.emp where empno=1111

No rows selected

-explain plan for and dbms_xplan.display do not execute sql. Explain plan for is the execution plan that generates the sql and is saved in the PLAN_ table, and dbms_xplan.display extracts the plan from the PLAN_TABLE and displays it.

Define

DBMS_XPLAN.DISPLAY (

Table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE'

Statement_id IN VARCHAR2 DEFAULT NULL

Format IN VARCHAR2 DEFAULT 'TYPICAL'

Filter_preds IN VARCHAR2 DEFAULT NULL)

Table_name: specifies the name of the planned storage table (not the name of the execution table). The default is PLAN_TABLE.

Statement_id: specifies the statement_id of the plan, if not specified, the statement_id of explain plan, or the last interpreted execution plan if there is no expain plan.

Format: explain the level of the plan

? BASIC: Displays the minimum information in the plan-the operation ID, the operation name and its option. Show the least amount of information

? TYPICAL: This is the default. Displays 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. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below). Default value

? SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. No concurrency information

? ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed). Displays the most information, including remote sql execution information for distributed operations

Filter_preds:sql filtering to limit the rows returned from the schedule.

Dbms_xplan.display_awr

Show the execution plan of the sql stored in awr

Define

DBMS_XPLAN.DISPLAY_AWR (

Sql_id IN VARCHAR2

Plan_hash_value IN NUMBER DEFAULT NULL

Db_id IN NUMBER DEFAULT NULL

Format IN VARCHAR2 DEFAULT TYPICAL)

Sql_id: sql_id can be found in dba_hist_sqltext

The hash value of the plan_hash_value:sql execution plan. If this value is ignored, the function returns all execution plans in sql_id

Db_id:database_id . If not, the value is the database_id in the V$DATABASE view, that is, the local database.

Format: similar to display. There are 4 level:BASIC,TYPICAL,SERIAL,ALL in total.

SQL > select * from scott.emp where rownum EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ()

PL/SQL procedure successfully completed.

SQL > select sql_id,sql_text from dba_hist_sqltext where sql_text like'% rownum%'

SQL_ID

-

SQL_TEXT

6yzbcy3x0yr1j

Insert into wrh$_dispatcher (snap_id, dbid, instance_number, name, serial#

Fsbqktj5vw6n9

Select next_run_date, obj#, run_job, sch_job from (select decode (bitand (a.flags)

Cv959u044n88s

Select 1 from sys.aq$_subscriber_table where rownum

< 2 and subscriber_id 0 a … SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------- bd3tcy3ar02px select * from scott.emp where rownum -指定awr中存在sql SQL>

Select * from table (dbms_xplan.display_awr ('bd3tcy3ar02px'))

PLAN_TABLE_OUTPUT

SQL_ID bd3tcy3ar02px

-

Select * from scott.emp where rownum

Dbms_xplan.display_cursor

Show the execution plan in cursor

Define

DBMS_XPLAN.DISPLAY_CURSOR (

Sql_id IN VARCHAR2 DEFAULT NULL

Cursor_child_no IN NUMBER DEFAULT 0

Format IN VARCHAR2 DEFAULT 'TYPICAL')

Sql_id

Cursor_child_no: child cursor flag. If not specified, show all execution plans under sql_id

Format

Default execution

SQL > select * from scott.emp where rownum select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID 90ud69jbjz75c, child number 0

-

Select * from scott.emp where rownum select sql_id,sql_text from v$sqlarea where sql_text like'% rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.