oracle查詢執(zhí)行計(jì)劃的方法有哪些

這篇文章主要介紹“oracle查詢執(zhí)行計(jì)劃的方法有哪些”，在日常操作中，相信很多人在oracle查詢執(zhí)行計(jì)劃的方法有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”oracle查詢執(zhí)行計(jì)劃的方法有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

1.explain plan for

--無需執(zhí)行，快捷方便

--沒有統(tǒng)計(jì)信息，產(chǎn)生的邏輯讀，遞歸等

--無法判斷處理了多少行

--無法判斷表被訪問了多少次

explain plan for select * from t,t1 where t.type=t1.object_name;

select * from table(dbms_xplan.display());

Plan hash value: 2914261090

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

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

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

|  0 | SELECT STATEMENT  |   | 1 |  221 |  244  (0)| 00:00:01 |

|*  1 |  HASH JOIN   |   | 1 |  221 |  244  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| T   | 1 |  142 |  122  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| T1  | 1 |    79 |  122  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  1 - access("T"."TYPE"="T1"."OBJECT_NAME")

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

2.set autotrace on

--有輸出統(tǒng)計(jì)信息

--必須要等語句執(zhí)行完之后才有結(jié)果--無法看到表被訪問了多少次

set autotrace on  ---set autotrace traceonly  不輸出結(jié)果--

select * from t,t1 where t.type=t1.object_name;

Execution Plan

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

Plan hash value: 2914261090

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

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

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

|  0 | SELECT STATEMENT  |   | 1 |  221 |  244  (0)| 00:00:01 |

|*  1 |  HASH JOIN   |   | 1 |  221 |  244  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| T   | 1 |  142 |  122  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| T1  | 1 |    79 |  122  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  1 - access("T"."TYPE"="T1"."OBJECT_NAME")

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

Statistics

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

  4  recursive calls

  0  db block gets

896  consistent gets

  0  physical reads

  0  redo size

889  bytes sent via SQL*Net to client

408  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  3  rows processed

3.statistics_level=all

--可以從Starts看出表被訪問多少次，E-Rows，A-Rows預(yù)測行數(shù)與真實(shí)行數(shù)，buffer是真實(shí)的邏輯讀

---語句執(zhí)行完后才有結(jié)果，無法控制不出結(jié)果，看不出遞歸調(diào)用和邏輯讀

alter session set statistics_level=all;

select * from t,t1 where t.type=t1.object_name;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

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

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

|  0 | SELECT STATEMENT  |   | 1 |     |   3 |00:00:00.01 | 896 |     |     |       |

|*  1 |  HASH JOIN       |   | 1 |   1 |   3 |00:00:00.01 | 896 |  1695K|  1695K|  787K (0)|

|  2 |  TABLE ACCESS FULL| T   | 1 |   1 |   4 |00:00:00.01 | 447 |     |     |       |

|  3 |  TABLE ACCESS FULL| T1  | 1 |   1 |   5 |00:00:00.01 | 449 |     |     |       |

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

Predicate Information (identified by operation id):

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

  1 - access("T"."TYPE"="T1"."OBJECT_NAME")

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

4.dbms_xplan.display_cursor

--知道sql_id可以立即得出真是的執(zhí)行計(jì)劃，且可以直接得出

--沒有相關(guān)的統(tǒng)計(jì)信息（邏輯讀等）--無法判斷執(zhí)行了多少次--無法得出表被訪問了多少次

5qn0b7zft4s04

select * from table(dbms_xplan.display_cursor('sql_id'))--共享池獲取

select * from table(dbms_xplan.display_awr('sql_id'))--awr性能視圖中獲取

select * from table(dbms_xplan.display_cursor('5qn0b7zft4s04'))

select * from table(dbms_xplan.display_awr('5qn0b7zft4s04'))

Plan hash value: 2914261090

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

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

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

|  0 | SELECT STATEMENT  |   |   |   |  244 (100)|   |

|*  1 |  HASH JOIN   |   | 1 |  221 |  244  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| T   | 1 |  142 |  122  (0)| 00:00:01 |

|  3 |  TABLE ACCESS FULL| T1  | 1 |    79 |  122  (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  1 - access("T"."TYPE"="T1"."OBJECT_NAME")

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

5.10046 trace跟蹤

--可以看出語句的等待事件，可以看出sql中的函數(shù)調(diào)用，

--可以看出處理的行數(shù)以及物理讀--解析時(shí)間以及執(zhí)行時(shí)間

--方便跟蹤整個(gè)程序包

alter session set events '10046 trace name context forever,level 12';  開啟跟蹤

執(zhí)行語句

alter session set events '10046 trace name context off';

找到文件

tkprof trc文件 目標(biāo)文件 sys=no sort=prsela,exeela,fchela

[oracle@oracle1 ~]$ cat 1.txt

TKPROF: Release 19.0.0.0.0 - Development on Wed Mar 11 10:25:48 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: NGENPR_ora_6661.trc

Sort options: prsela  exeela  fchela 

********************************************************************************

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing

elapsed  = elapsed time in seconds executing

disk    = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

rows    = number of rows processed by the fetch or execute call

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call    count      cpu    elapsed      disk      query    current        rows

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

Parse        2      0.00      0.00          0        138          0          0

Execute      2      0.00      0.00          0          0          0          0

Fetch        2      0.00      0.00          0        760          0          3

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

total        6      0.00      0.00          0        898          0          3

Misses in library cache during parse: 1

Elapsed times include waiting on following events:

  Event waited on                            Times  Max. Wait  Total Waited

  ----------------------------------------  Waited  ----------  ------------

  Disk file operations I/O                        2        0.00          0.00

  SQL*Net message to client                      3        0.00          0.00

  SQL*Net message from client                    3        8.24        13.24

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call    count      cpu    elapsed      disk      query    current        rows

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

Parse        3      0.00      0.00          0          0          0          0

Execute      3      0.00      0.00          0          0          0          0

Fetch        3      0.00      0.00          0        136          0          2

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

total        9      0.00      0.00          0        136          0          2

Misses in library cache during parse: 3

Misses in library cache during execute: 1

    2  user  SQL statements in session.

    3  internal SQL statements in session.

    5  SQL statements in session.

********************************************************************************

Trace file: NGENPR_ora_6661.trc

Trace file compatibility: 12.2.0.0

Sort options: prsela  exeela  fchela 

      1  session in tracefile.

      2  user  SQL statements in trace file.

      3  internal SQL statements in trace file.

      5  SQL statements in trace file.

      5  unique SQL statements in trace file.

    167  lines in trace file.

      5  elapsed seconds in trace file.

6 awrsqlrpt

@?/rdbms/admin/awrsqlrpt

begin  end snap

sql_id

六種方法的差異

1.如果結(jié)果出不來，只能用1

2.比較簡單的方法是1或者2

3.觀察多個(gè)執(zhí)行計(jì)劃只能用4和6

4.如果語句復(fù)雜，里面涉及到函數(shù)等，只能用5

5.真實(shí)的執(zhí)行計(jì)劃不能用1

6.想獲取表被訪問的次數(shù)，只能用3

到此，關(guān)于“oracle查詢執(zhí)行計(jì)劃的方法有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！