Oracle分析query plan

清空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)

Oracle分析query plan,古老的榕树,5-wow.com