oracle dbms_xplan.display_cursor,dbms_xplan.display_cursor

最新推荐文章于 2021-04-13 19:18:27 发布
最新推荐文章于 2021-04-13 19:18:27 发布
阅读量365 收藏
点赞数 1
文章标签： oracle dbms_xplan.display_cursor

1) sql tunning asking01

Note: example for reading execution plan,

dbms_xplan.display_cursor('sql_id',child_number,'mode')

1@@@@ sql script

[oracle@station78 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 3 13:48:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ocm11g> !cat tmp.sql

--Test on 10R2, 11gR2

--

--1) Create the test table t.

set echo on

set feedback on

DROP TABLE t;

CREATE TABLE t

AS

SELECT rownum AS n, lpad('*',1000,'*') AS pad

FROM dual

CONNECT BY level <= 1000;

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t');

CREATE INDEX i ON t(n);

ALTER SESSION SET workarea_size_policy = manual;

ALTER SESSION SET sort_area_size = 65536;

PAUSE

--

--2) Change session environment, generate child cursor

ALTER SYSTEM FLUSH shared_pool;

ALTER SESSION SET optimizer_mode = all_rows;

set termout off

SELECT * FROM t WHERE n > 19;

set termout on

ALTER SESSION SET optimizer_mode = first_rows_10;

set termout off

SELECT * FROM t WHERE n > 19;

set termout on

PAUSE

--

--3) Display info about last execution

--default child_number is 0

col sql_text for a40 word_wrapped

SELECT sql_id, child_number, plan_hash_value, sql_text

FROM v$sql

WHERE sql_text LIKE '%n > 19%';

SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0));

SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1));

PAUSE

--

--4) Display execution statistics (I/O)

--reflash buffer cache, see the physical read

ALTER SYSTEM FLUSH buffer_cache;

SELECT /*+ gather_plan_statistics */ count(*)

FROM t

WHERE mod(n,19) = 0;

col sql_text for a40 word_wrapped

SELECT sql_id, child_number, plan_hash_value, sql_text

FROM v$sql

WHERE sql_text LIKE '%mod(n,19)%';

SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'));

PAUSE

--

--5) Use the parameter FORMAT to get different execution statistics

--UNDEFINE sql_id

--UNDEFINE child_number

DROP INDEX i;

SELECT /*+ gather_plan_statistics */ count(pad)

FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)

WHERE rn = 1;

SELECT sql_id, child_number, plan_hash_value, sql_text

FROM v$sql

WHERE sql_text LIKE '%rn = 1%';

SELECT * FROM

table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'));

PAUSE

SELECT * FROM

table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'));

PAUSE

SELECT * FROM

table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'));

PAUSE

SELECT * FROM

table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'));

-- PAUSE

-- SELECT * FROM

--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats'));

--

-- PAUSE

-- SELECT * FROM

--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_tot'));

--

-- PAUSE

-- SELECT * FROM

--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats'));

--

-- PAUSE

-- SELECT * FROM

--   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats'));

2@@@@ Explain Option:

Explain:

Table 6-1. Columns of the Table Containing the Execution Plan

Column        Description

------------------------------------------------------------------------------

[1]Basics (Always Available)

Id            The identifier of each operation (line) in the execution plan. If the number

is prefixed by an asterisk, it means that predicate information for that line

is available later.

Operation     The operation to be executed. This is also known as the row source operation.

Name          The object on which the operation is executed.

------------------------------------------------------------------------------

[2]Query Optimizer Estimations

Rows (E-Rows)   The estimated number of rows returned by the operation.

Bytes (E-Bytes) The estimated amount of data returned by the operation.

TempSpc         The estimated amount of temporary space used by the operation.

Cost (%CPU)     The estimated cost of the operation. The percentage of CPU cost is given

in parentheses. Note that this value is cumulated through the execution

plan. In other words, the cost of parent operations contains the cost of

their child operations.

Time            The estimated amount of time needed to execute the operation (HH:MM:SS).

------------------------------------------------------------------------------

[3]Partitioning

Pstart          The number of the first partition to be accessed. Set to either KEY, KEY(I),

KEY(MC), KEY(OR), or KEY(SQ) if unknown at parse time.

Pstop           The number of the last partition to be accessed. Set to either KEY, KEY(I),

KEY(MC), KEY(OR), or KEY(SQ) if unknown at parse time.

------------------------------------------------------------------------------

[4]Parallel and Distributed Processing

Inst            For distributed processing, the name of the database link used by

the operation.

TQ              For parallel processing, the table queue used for the communication

between slaves.

IN-OUT          The relationship between parallel or distributed operations.

PQ Distrib      For parallel processing, the distribution used by producers to send data

to consumers.

------------------------------------------------------------------------------

[5]Runtime Statistics*

Starts         The number of times a specific operation was executed.

A-Rows         The actual number of rows returned by the operation.

A-Time         The actual amount of time spent executing the operation (HH:MM:SS.FF).

------------------------------------------------------------------------------

[6]I/O Statistics*

Buffers        The number of logical read operations performed during the execution.

Reads          The number of physical reads performed during the execution.

Writes         The number of physical writes performed during the execution.

------------------------------------------------------------------------------

[7]Memory Utilization Statistics

OMem           The estimated amount of memory needed for an optimal execution.

1Mem           The estimated amount of memory needed for one-pass execution.

O/1/M          The number of times the execution was performed in optimal/one-pass/

multipass mode.

Used-Mem       The amount of memory used by the operation during the last execution.

Used-Tmp       The amount of temporary space used by the operation during the last

execution. This value must be multiplied by 1,024 to be consistent with

the other memory utilization columns (for example, 32K means 32MB).

Max-Tmp        The maximum amount of temporary space used by the operation. This

value has to be multiplied by 1,024 to be consistent with the other memory

utilization columns (for example, 32K means 32MB).

------------------------------------------------------------------------------

* Available only when execution statistics are enabled.

3@@@@ output

[oracle@station78 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 3 13:49:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ocm11g> @tmp.sql

SYS@ocm11g> set feedback on

SYS@ocm11g> DROP TABLE t;

Table dropped.

SYS@ocm11g> CREATE TABLE t

2  AS

3  SELECT rownum AS n, lpad('*',1000,'*') AS pad

4    FROM dual

5  CONNECT BY level <= 1000;

Table created.

SYS@ocm11g>

SYS@ocm11g> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t');

PL/SQL procedure successfully completed.

SYS@ocm11g>

SYS@ocm11g> CREATE INDEX i ON t(n);

Index created.

SYS@ocm11g>

SYS@ocm11g> ALTER SESSION SET workarea_size_policy = manual;

Session altered.

SYS@ocm11g> ALTER SESSION SET sort_area_size = 65536;

Session altered.

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> --

SYS@ocm11g> --2) Change session environment, generate child cursor

SYS@ocm11g> ALTER SYSTEM FLUSH shared_pool;

System altered.

SYS@ocm11g> ALTER SESSION SET optimizer_mode = all_rows;

Session altered.

SYS@ocm11g> set termout off

SYS@ocm11g>

SYS@ocm11g> ALTER SESSION SET optimizer_mode = first_rows_10;

Session altered.

SYS@ocm11g> set termout off

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> --

SYS@ocm11g> --3) Display info about last execution

SYS@ocm11g> --default child_number is 0

SYS@ocm11g> col sql_text for a40 word_wrapped

SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text

2    FROM v$sql

3   WHERE sql_text LIKE '%n > 19%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT

------------- ------------ --------------- ----------------------------------------

16vk8p8cmwbk8         0    1601196873 SELECT * FROM t WHERE n > 19

16vk8p8cmwbk8         1     242607798 SELECT * FROM t WHERE n > 19

3s7f8gwf2bxw7         0     903671040 SELECT sql_id, child_number,

plan_hash_value, sql_text   FROM v$sql

WHERE sql_text LIKE '%n > 19%'

3 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0));

Enter value for 03_sql_id: 16vk8p8cmwbk8

old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',0))

new   1: SELECT * FROM table(dbms_xplan.display_cursor('16vk8p8cmwbk8',0))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    16vk8p8cmwbk8, child number 0

-------------------------------------

SELECT * FROM t WHERE n > 19

Plan hash value: 1601196873

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |     |     |     |    40 (100)|      |

|*  1 |  TABLE ACCESS FULL| T     |   982 |   963K|    40   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("N">19)

18 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1));

old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&03_sql_id',1))

new   1: SELECT * FROM table(dbms_xplan.display_cursor('16vk8p8cmwbk8',1))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    16vk8p8cmwbk8, child number 1

-------------------------------------

SELECT * FROM t WHERE n > 19

Plan hash value: 242607798

------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |       |       |       |     4 (100)|       |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |    11 | 11055 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN        | I    |       |       |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("N">19)

19 rows selected.

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> --

SYS@ocm11g> --4) Display execution statistics (I/O)

SYS@ocm11g> --reflash buffer cache, see the physical read

SYS@ocm11g> ALTER SYSTEM FLUSH buffer_cache;

System altered.

SYS@ocm11g> SELECT /*+ gather_plan_statistics */ count(*)

2    FROM t

3   WHERE mod(n,19) = 0;

COUNT(*)

----------

52

1 row selected.

SYS@ocm11g> col sql_text for a40 word_wrapped

SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text

2    FROM v$sql

3   WHERE sql_text LIKE '%mod(n,19)%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT

------------- ------------ --------------- ----------------------------------------

50zr0t09k80w3         0    2966233522 SELECT /*+ gather_plan_statistics */

count(*)   FROM t  WHERE mod(n,19) = 0

91307uh53jr72         0     903671040 SELECT sql_id, child_number,

plan_hash_value, sql_text   FROM v$sql

WHERE sql_text LIKE '%mod(n,19)%'

2 rows selected.

SYS@ocm11g> SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'));

Enter value for 04_sql_id: 50zr0t09k80w3

old   1: SELECT * FROM table(dbms_xplan.display_cursor('&&04_sql_id',0,'runstats_last'))

new   1: SELECT * FROM table(dbms_xplan.display_cursor('50zr0t09k80w3',0,'runstats_last'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    50zr0t09k80w3, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */ count(*)    FROM t    WHERE mod(n,19)

= 0

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------

| Id  | Operation       | Name | Starts | E-Rows | A-Rows |     A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |        |       1 |00:00:00.01 |    147 |     144 |

|   1 |  SORT AGGREGATE    |      |     1 |      1 |       1 |00:00:00.01 |    147 |     144 |

|*  2 |   TABLE ACCESS FULL| T      |     1 |     10 |      52 |00:00:00.01 |    147 |     144 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(MOD("N",19)=0)

20 rows selected.

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> --

SYS@ocm11g> --5) Use the parameter FORMAT to get different execution statistics

SYS@ocm11g> --UNDEFINE sql_id

SYS@ocm11g> --UNDEFINE child_number

SYS@ocm11g> DROP INDEX i;

Index dropped.

SYS@ocm11g>

SYS@ocm11g> SELECT /*+ gather_plan_statistics */ count(pad)

2    FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)

3   WHERE rn = 1;

COUNT(PAD)

----------

1

1 row selected.

SYS@ocm11g>

SYS@ocm11g> SELECT sql_id, child_number, plan_hash_value, sql_text

2    FROM v$sql

3   WHERE sql_text LIKE '%rn = 1%';

SQL_ID          CHILD_NUMBER PLAN_HASH_VALUE SQL_TEXT

------------- ------------ --------------- ----------------------------------------

2nqg014kk20xm         0    2545006537 SELECT /*+ gather_plan_statistics */

count(pad)    FROM (SELECT rownum AS rn,

pad FROM t ORDER BY n)  WHERE rn = 1

2dyc3byvhk8d3         0     903671040 SELECT sql_id, child_number,

plan_hash_value, sql_text   FROM v$sql

WHERE sql_text LIKE '%rn = 1%'

2 rows selected.

SYS@ocm11g>

SYS@ocm11g> SELECT * FROM

2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'));

Enter value for 05_sql_id: 2nqg014kk20xm

Enter value for 05_child_number: 0

old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats last'))

new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'iostats last'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    2nqg014kk20xm, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum

AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

----------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |

|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |

|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |

|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |

|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |

|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("RN"=1)

23 rows selected.

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM

2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'));

old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_last'))

new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'runstats_last'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    2nqg014kk20xm, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum

AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

----------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |

|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |

|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |

|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |

|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |

|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("RN"=1)

23 rows selected.

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM

2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'));

old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats last'))

new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'memstats last'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    2nqg014kk20xm, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum

AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

-------------------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |    OMem |    1Mem | Used-Mem | Used-Tmp|

-------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |         |         |        |      |

|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |         |         |        |      |

|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |         |         |        |      |

|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |    1125K|     558K|49152  (0)|    1024 |

|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |         |         |        |      |

|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |         |         |        |      |

-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("RN"=1)

23 rows selected.

SYS@ocm11g>

SYS@ocm11g> PAUSE

SYS@ocm11g> SELECT * FROM

2    table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'));

old   2:   table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats last'))

new   2:   table(dbms_xplan.display_cursor('2nqg014kk20xm',0,'allstats last'))

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID    2nqg014kk20xm, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */ count(pad)   FROM (SELECT rownum

AS rn, pad FROM t ORDER BY n)  WHERE rn = 1

Plan hash value: 2545006537

------------------------------------------------------------------------------------------------------------------------

-----------------------

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads    | Writes |  OMem |  1Mem

| Used-Mem | Used-Tmp|

------------------------------------------------------------------------------------------------------------------------

-----------------------

|   0 | SELECT STATEMENT      |      |        1 |        |      1 |00:00:01.09 |       149 |    125 |    125 |     |

|        |          |

|   1 |  SORT AGGREGATE       |      |        1 |      1 |      1 |00:00:01.09 |       149 |    125 |    125 |     |

|        |          |

|*  2 |   VIEW              |      |        1 |   1000 |      1 |00:00:01.09 |       149 |    125 |    125 |     |

|        |          |

|   3 |    SORT ORDER BY      |      |        1 |   1000 |   1000 |00:00:01.10 |       149 |    125 |    125 |  1125K|   558

K|49152  (0)|     1024 |

|   4 |     COUNT          |      |        1 |        |   1000 |00:00:00.01 |       147 |      0 |      0 |     |

|        |          |

|   5 |      TABLE ACCESS FULL| T    |        1 |   1000 |   1000 |00:00:00.01 |       147 |      0 |      0 |     |

|        |          |

------------------------------------------------------------------------------------------------------------------------

-----------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("RN"=1)

23 rows selected.

SYS@ocm11g>

SYS@ocm11g> -- PAUSE

SYS@ocm11g> -- SELECT * FROM

SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'iostats'));

SYS@ocm11g> --

SYS@ocm11g> -- PAUSE

SYS@ocm11g> -- SELECT * FROM

SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'runstats_tot'));

SYS@ocm11g> --

SYS@ocm11g> -- PAUSE

SYS@ocm11g> -- SELECT * FROM

SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'memstats'));

SYS@ocm11g> --

SYS@ocm11g> -- PAUSE

SYS@ocm11g> -- SELECT * FROM

SYS@ocm11g> --     table(dbms_xplan.display_cursor('&&05_sql_id',&&05_child_number,'allstats'));

SYS@ocm11g>

谭押沙龙
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
dbms_xplan.display_cursor查看低效的执行计划
u012298337的专栏
10-14 938
dbms_xplan.display_cursor定义： function display_cursor(sql_id                                 varchar2 default  null,                                                          cursor_child_no  integ
Oracle中使用DBMS_XPLAN查看执行计划的方法
weixiaohuai的博客
06-03 3672
一、简介 DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包.DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 DBMS_XPLAN包不仅可以获取解释计划，还可以用来输出存储在AWR、SQL调试集、缓存的SQL游标，以及SQL基线中的语句计划。下面将重点讨论关于DBMS_XPLAN包在解释计划和执行计划上的应用。 首先看一个经常使用的查看某条语句的解释计划的示例： //登录oracle sqlplus / as sysdba //查看执行计划 ex
dbms_xplan.display_cursor
有教无类的专栏
11-12 1921
执行示例如下，以下模拟你执行过的语句 SQL> BEGIN 2 FOR cur IN (SELECT deptno FROM dept) LOOP 3 EXECUTE IMMEDIATE 'select /*1234*/ /*+ GATHER_PLAN_STATISTICS*/ * from emp where deptno = :deptno' 4
【Oracle】获取执行计划之 dbms_xplan.display_cursor()
As before 如初
04-04 653
前提 SQL执行计划仍在Shared Pool中 函数体 DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL'); 参数 sql_id 指位于库缓存执行计划中SQL
[20151021]理解dbms_xplan.display_cursor的format参数all.txt
10-21 110
[20151021]理解dbms_xplan.display_cursor的format参数all.txt --今天才理解dbms_xplan.display_cursor的format参数all，看来看书与看文档不够仔细...
oracle dbms_xplan display,DBMS_XPLAN.DISPLAY_CURSOR()看执行计划 | 学步园
weixin_28869849的博客
04-13 422
我们可以很容易的得到一个SQL的执行计划。如果一个SQL已经执行过了，我们怎么查看他真实的执行计划呢。如果知道已执行SQL的 SQL_ID，或HASH_VALUE.在10g中利用DBMS_XPLAN.DISPLAY_CURSOR()可以很方便地查询到已执行SQL的执行计划。SQL> desc dbms_xplanFUNCTION DISPLAY_CURSOR RETURNS DBMS_XPL...
Oracle中使用DBMS_XPLAN处理执行计划详解
12-16
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是使用set autotrace命令，不过现在看来，DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
Re-post: DBMS_XPLAN : Display Oracle Execution Plans
04-23
3. `xplan.display_cursor.sql` - 这个文件可能用于显示共享游标（也称为SQL语句的软解析结果）的执行计划，有助于了解当前会话中的SQL性能。 4. `xplan.display.sql` - 这可能是通用的DBMS_XPLAN显示函数的调用...
oracle_sql_tuning.rar_oracle
最新发布
09-22
通过EXPLAIN PLAN或使用DBMS_XPLAN可以查看查询的执行步骤，包括表扫描、索引访问、连接方法等。 - **成本基优化器**：Oracle使用成本基优化器(CBO)来选择执行计划，它基于统计信息估算执行不同操作的成本。 - **...
Oracle_sql_adn.rar_ADN_oracle
09-22
2. **执行计划分析**：通过EXPLAIN PLAN或DBMS_XPLAN可以查看SQL语句的执行计划，理解查询如何在数据库中执行，包括表的访问方式、连接顺序、索引使用情况等，从而找出性能瓶颈。 3. **绑定变量**：使用绑定变量...
在Oracle 9i下的display_cursor脚本
07-30
NULL 博文链接：https://dingjun1.iteye.com/blog/836605
DBMS_XPLAN.DISPLAY_CURSOR&DISPLAY
jc_benben的专栏
06-15 1085
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包,很不错的一个功能 不仅如此，它还可以用来输出存储在AWR，SQL调试集，缓存的SQL游标，以及SQL基线中的语句计划 比如：DISPLAY，DISPLAY_CURSOR,DISPLAY_AWR,DISPLAY_PLAN,DISPLAY_SQL_PLAN_BASELINE, DISPLAY_SQLSET 这里
oracle获取顺序函数,执行计划__获取方法、查看执行顺序、统计信息详解
weixin_39616547的博客
04-03 233
个人常用的获取执行计划的方法1.select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced')); --只有执行计划2.set autotrace traceonly exp --只有执行计划1与2的区别在于当SQL有绑定变量时，1更准确(毕竟sql先执行再获取的执行计划)，...
dbms_xplan.display_cursor的用法
yh_zeng2的博客
08-01 1429
转:Oracle中查看已执行sql的执行计划---dbms_xplan.display_cursor
cuizijian2878的博客
11-13 183
有时候我们可能会希望查看一条已经执行过的sql的执行计划，常用的方式有两种：a,set autotrace后再重新执行一遍，不过重新执行可能会浪费时间，而且有些语句也不允许(例如修改操作的语句)，或者查询v$sql_plan视图...
dbmsxplan oracle_DBMS_XPLAN.DISPLAY_AWR
weixin_35600177的博客
12-29 623
之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划，于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询，但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录，一般来说这些语句已经从shared pool共享池中被替换出去了。这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Ora...
Oracle 查看 SQL执行计划 SQL性能分析
热门推荐
许恒的博客
01-05 3万+
    https://tuozixuan.iteye.com/blog/2333605 使用Oracle执行计划分析SQL性能 博客分类：  db oracle执行计划sql性能解释  执行计划可以用来分析SQL的性能   一、查看执行计划的方法 1. 设置autotrace     set autotrace off: 此为默认值，即关闭autotrace     set...
Oracle 获取SQL执行计划方法
ckth47350的博客
07-31 420
Oracle 获取SQL执行计划方法 方法一：DBMS_XPLAN.DISPLAY_CURSOR 方法二：explain plan for ...... 方法三：Tuning SQL*Plus(auto...
Oracle查看执行计划的几种方法
coco3600的博客
04-09 876
Oracle查看执行计划的几种方法 一般来说，有如下几种获取执行计划的方式： 1、AUTO...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值