Queryplan for Oracle Analysis

Clear Shared pool:

Alter system flush shared_pool;

However, to clear the entire memory, we had to shut down the instances and databases in the entire Oracle RAC environment and restart them.

You can clear the buffer cache:

Alter system flush buffer_cache;

Analyze 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 ());

Expected result:

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

Bytes -----------------------------------------------------------------------------------------------------------

| 0 | select statement | 2898K | 572M | 240 K (1) | 00:48:06 |

| 1 | merge join | 2898K | 572M | 240 K (1) | 00:48:06 |

| 2 | table access by index rowid | HZCZRK_ZPXXB | 2898K | 146M | 120 K (1) | 00:24:07 |

| 3 | index full scan | INDEX_HZCZRK_ZPXXB | 2898K | 6771 (1) | 00:01:22 |

| * 4 | sort join | 2898K | 1200m | 2156M | 119 K (1) | 00:24:00 |

| 5 | table access full | HZCZRK_JBXXB | 2898K | medium M | 21194 (3) | 00:04:15 |

Bytes -----------------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id ):

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

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

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

After using set autotrace traceonly, you can view the execution plan and statistics, such as query:

Select count (*) FROM HZCZRK_JBXXB;. The execution plan and statistics are as follows:

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

| 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_c00000067 | 17M | 9643 (2) | 00:01:56 |

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

Statistics

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

1 recursive cballs

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 indicates memory consumption, physicalreads indicates disk I/O consumption, and the Unit is data block (DB_BLOCK_SIZE)