有關效能調整的查詢和pub上的一個sql調優！

檢視資料字典命中率

Select 1-sum(gets)/sum(getmisses) from v$rowcache

資料字典命中率應該在95%以上，如果命中率過低要考慮增加shared_pool,簡單介紹一下v$rowcache資料字典檢視的column：

Parameter 快取名

Count：快取項總數

Gets：請求總數

Getmisses:請求失敗次數

Scans：掃描請求數

Scanmisses：掃描請求失敗數

Usage：包含有效資料的快取項數

檢視共享池中sql語句或pl/sql命中率

select sum(gethits)/sum(gets),(sum(pinhits)-sum(reloads))/sum(pins) from v$librarycache

檢視buffer命中率

Select name,1-physical_reads/(db_block_gets+consistent_gets) buffer+hits from v$buffer_pool_statistics;

Sort排序率:

排序是一個很消耗資源的操作，sql語句排序時會先在pga的記憶體中排序，當記憶體無法滿足時才會在磁碟中進行排序，而磁碟比記憶體慢很多。

Select values, name from v$sysstat where name like ‘%sort%’;

sorts (memory) 21584

sorts (disk) 1

sorts (rows) 223033

記憶體的排序率：sorts(memory)/(sorts(memory)+sorts(disk))

檢視與log_buffer有關的資訊

Select name,value from v$sysstat where name=’redo buffer allocation retries’

該參數列示等待獲取log buffer space，接近0，如果過大需要增加log_buffer日誌快取區

與log_buffer有關的等待事件

Select name,value from v$sysstat where name=’redo log space requests’

日誌緩衝空間分配重試等待

1 lgwr寫出redo entries的速度慢於服務程式產生的redo entries。

2 發生日誌切換，在log switch 這個過程中，任何程式將不能嘗試去log buffer 中分配空間。

Pub上的聽說淘寶面試題

create table test34 as select * from dba_objects;

create index index_test34 on test34(object_id);

最佳化下列sql

select max(object_Id),min(object_id) from test34

SQL> select max(object_Id),min(object_id) from test34;

執行計劃

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

Plan hash value: 3450461665

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

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 144 (3)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | TABLE ACCESS FULL| TEST34 | 51020 | 249K| 144 (3)| 00:00:02 |

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

統計資訊

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

1 recursive calls

0 db block gets

706 consistent gets

0 physical reads

0 redo size

483 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select (select min(object_id) min_id from test34 a),

(select max(object_id) max_id from test34 b)

from dual;

執行計劃

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

Plan hash value: 1545095403

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

-----------

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

Time |

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

-----------

| 0 | SELECT STATEMENT | | 1 | | 2 (0)|

00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | |

|

| 2 | INDEX FULL SCAN (MIN/MAX)| INDEX_TEST34 | 51020 | 249K| |

|

| 3 | SORT AGGREGATE | | 1 | 5 | |

|

| 4 | INDEX FULL SCAN (MIN/MAX)| INDEX_TEST34 | 51020 | 249K| |

|

| 5 | FAST DUAL | | 1 | | 2 (0)|

00:00:01 |

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

-----------

統計資訊

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

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

533 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

select (select /*+index_asc(test34 index_test34)*/

object_id

from test34

where rownum = 1) min,

(select /*+ index_desc(test34 index_test34) */

object_id

from test34

where rownum = 1) max

from dual;

執行計劃

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

Plan hash value: 1697884143

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

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

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

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

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| TEST34 | 1 | 5 | 2 (0)| 00:00:01 |

|* 3 | COUNT STOPKEY | | | | | |

| 4 | TABLE ACCESS FULL| TEST34 | 1 | 5 | 2 (0)| 00:00:01 |

| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM=1)

3 - filter(ROWNUM=1)

統計資訊

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

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

459 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

利用的是分開掃描時只查詢了leaf 中的第一條記錄或者最後一條記錄！注意COUNT STOPKEY這個是掃描索引到前幾行就結束，當有rownum或者分析函式時出現。

[@more@]