Oracle調優看什麽

获取执行计划：

select * from table(dbms_xplan.display);

select * from v$sql;//sql_id取得，以及下面第二个参数0或者其他数字

select * from table(dbms_xplan.display_cursor('sql_id',0,format=>'ALL'));

執行計劃解析順序

    ◆從内到外

    ◆從上到下

The order of the operations is 4,5,3,6,2,9,10,8,7,1,0.

看什麽

1.Full Table Scans

The entire table is read up to the high water mark(HWM).The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows in a table you will still read up to the HWM.

2.Join Methods, Join Order

     2-1.Nested Loop 

          First return all the rows from row source 1(Driving Table), typically the smaller of the two row sources.

        Then Probe row source 2(Inner Table) once for each row returned from row source 1.

       Good for joining smaller row sources.

       Best used with indexed foreign key columns.

     2-2.Hash Join

        Smallest row source is chosen and used to build a hash table(in memory) and a bitmap.

        The second row source is hashed and checked against the hash table looking for joins. The bit map is used as a quick lookup to check if rows are in the hash table.

        Good for joining larger row sources.

         Needs PGA memory.

     2-3.Sort Merge Join

           Rows are produced by row source 1 and are then sorted.

           Rows from row source 2 are then produced and sorted by the sort key as Row Source 1.

          Row source 1 and 2 are not accessed concurrently. Sorted rows from both sides are then merged together.

           Needs PGA Memory.

     2-4.Cartesian Join

           Every row from one row source is joined to every row from the second row source.

          Usually the result of a poorly written join.

3.Index Access Methods

Index Range Scan

Index Unique Scan

Index Full Scan

Index Fast Full Scan

Index Skip Scan

4.Filters

   Restriction in the where clause.

    try to filter rows before performing joins and this the goal.

5.Parallel Operations

     Some SQL processing can be broken into separate parallel processing steps.

     Parallel processing can be hinted.

     Some operations that might be seen in the execution plan:

         PX COORDINATOR

         PX BLOCK ITERATOR

         PX SEND

        PX RECEIVE

6.Partition Processing

     Common practice to partition tables that are expected to contain a large volume of rows.

     The optimizer is partition-smart, and its plans should reflect this(Partition Pruning)

     Partition access in the execution plan:

          PARTITION LIST SINGLE

          PARTITION LIST ALL

7.Dynamic Statistics

    Indicates object statistics are missing.

8.Cost

     Used to compare different plans for the same query.

    IOs,CPU,and network..