程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle分析queryplan

Oracle分析queryplan

編輯:Oracle教程

清空Shared pool:

alter system flush shared_pool;

但為了整個內存的清空,只好將整個Oracle RAC環境的實例和數據庫都關機,再重新啟動。

可以采用清空buffer cache的方式來做:

alter system flush buffer_cache;

分析query plan,

explain plan for select * fromHZCZRK_JBXXB,HZCZRK_ZPXXB WHERE HZCZRK_JBXXB.RYID = HZCZRK_ZPXXB.RYID;

select plan_table_output fromtable(dbms_xplan.display());

得到結果:

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

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

| 0| SELECT STATEMENT | | 2898K| 572M| | 240K (1)| 00:48:06 |

| 1| MERGE JOIN | | 2898K| 572M| | 240K (1)| 00:48:06 |

| 2| TABLE ACCESS BY INDEX ROWID|HZCZRK_ZPXXB | 2898K| 146M| | 120K (1)| 00:24:07 |

| 3| INDEX FULL SCAN | INDEX_HZCZRK_ZPXXB | 2898K| | | 6771 (1)| 00:01:22 |

|* 4| SORT JOIN | | 2898K| 425M| 2156M| 119K (1)| 00:24:00 |

| 5| TABLE ACCESS FULL | HZCZRK_JBXXB | 2898K| 425M| | 21194 (3)| 00:04:15 |

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

Predicate Information (identified byoperation id):

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

4-access("HZCZRK_JBXXB"."RYID"="HZCZRK_ZPXXB"."RYID")

filter("HZCZRK_JBXXB"."RYID"="HZCZRK_ZPXXB"."RYID")

使用set autotrace traceonly後可以查看執行計劃、統計信息,如query:

SELECT COUNT(*) FROM HZCZRK_JBXXB;,執行計劃、統計信息如下:

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

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

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

| 0| SELECT STATEMENT | | 1 | 9643 (2)| 00:01:56 |

| 1| SORT AGGREGATE | | 1 | | |

| 2| INDEX FAST FULL SCAN| SYS_C0010867| 17M| 9643 (2)| 00:01:56 |

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

統計信息

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

1 recursive calls

0 db block gets

37509 consistent gets

37485 physical reads

0 redo size

537 bytes sent via SQL*Net toclient

524 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed

consistent gets代表內存消耗,physicalreads代表磁盤I/O消耗,單位都是數據塊(DB_BLOCK_SIZE)

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved