ORACLE學習筆記--效能最佳化二

10.Oracle什麼時候會使用跳躍式索引掃描[@more@]

這是9i的一個新特性跳躍式索引掃描(Index Skip Scan).

例如表有索引index(a,b,c)，當查詢條件為where b=?的時候，可能會使用到索引index(a,b,c)，如，執行計劃中出現如下計劃：

INDEX (SKIP SCAN) OF “TEST_IDX“ (NON-UNIQUE)

Oracle的最佳化器(這裡指的是CBO)能對查詢應用Index Skip Scans至少要有幾個條件:

<1> 最佳化器認為是合適的。

<2> 索引中的前導列的唯一值的數量能滿足一定的條件（如重複值很多）。

<3> 最佳化器要知道前導列的值分佈(透過分析/統計表得到)。

<4> 合適的SQL語句

等。

11.怎麼樣建立使用虛擬索引

可以使用nosegment選項，如

create index virtual_index_name on table_name(col_name) nosegment;

如果在哪個session需要測試虛擬索引，可以利用隱含引數來處理

alter session set "_use_nosegment_indexes" = true;

就可以利用explain plan for select ……來看虛擬索引的效果，利用@$ORACLE_HOME/rdbms/admin/utlxpls檢視執行計劃，最後，根據需要，我們可以刪除虛擬索引，如普通索引一樣

drop index virtual_index_name;

注意：虛擬索引並不是物理存在的，所以虛擬索引並不等同於物理索引，不要用自動跟蹤去測試虛擬索引，因為那是實際執行的效果，是用不到虛擬索引的。

12.怎樣監控無用的索引

Oracle 9i以上，可以監控索引的使用情況，如果一段時間內沒有使用的索引，一般就是無用的索引

語法為：

開始監控：alter index index_name monitoring usage;

檢查使用狀態：select * from v$object_usage;

停止監控：alter index index_name nomonitoring usage;

當然，如果想監控整個使用者下的索引，可以採用如下的指令碼：

set heading off

set echo off

set feedback off

set pages 10000

spool start_index_monitor.sql

SELECT “alter index “||owner||“.“||index_name||“ monitoring usage;“

FROM dba_indexes

WHERE owner = USER;

spool off

set heading on

set echo on

set feedback on

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

set heading off

set echo off

set feedback off

set pages 10000

spool stop_index_monitor.sql

SELECT “alter index “||owner||“.“||index_name||“ nomonitoring usage;“

FROM dba_indexes

WHERE owner = USER;

spool off

set heading on

set echo on

set feedback on

13.怎麼樣能固定我的執行計劃

可以使用OUTLINE來固定SQL語句的執行計劃，用如下語句可以建立一個OUTLINE

Create oe replace outline OutLn_Name on

Select Col1,Col2 from Table

where .......

如果要刪除Outline，可以採用

Drop Outline OutLn_Name;

對於已經建立了的OutLine，存放在OUTLN使用者的OL$HINTS表下面，對於有些語句，你可以使用update outln.ol$hints來更新outline，如

update outln.ol$hints(ol_name,“TEST1“,“TEST2“,“TEST2“,“TEST1)

where ol_name in (“TEST1“,“TEST2“);

這樣，你就把Test1 OUTLINE與Test2 OUTLINE互換了，如果想利用已經存在的OUTLINE，需要設定以下引數

Alter system/session set Query_rewrite_enabled = true

Alter system/session set use_stored_outlines = true

14.v$sysstat中的class分別代表什麼

統計類別

1 代表事例活動

2 代表Redo buffer活動

4 代表鎖

8 代表資料緩衝活動

16 代表OS活動

32 代表並行活動

64 代表表訪問

128 代表除錯資訊

15.怎麼殺掉特定的資料庫會話

Alter system kill session “sid,serial#“;

或者

alter system disconnect session “sid,serial#“ immediate;

在win上，還可以採用oracle提供的orakill殺掉一個執行緒（其實就是一個Oracle程式）

在Linux/Unix上，可以直接利用kill殺掉資料庫程式對應的OS程式

16.怎麼快速查詢鎖與鎖等待

資料庫的鎖是比較耗費資源的，特別是發生鎖等待的時候，我們必須找到發生等待的鎖，有可能的話，殺掉該程式。

這個語句將查詢到資料庫中所有的DML語句產生的鎖，還可以發現，任何DML語句其實產生了兩個鎖，一個是表鎖，一個是行鎖。

可以透過alter system kill session ‘sid,serial#’來殺掉會話

SELECT /*+ rule */ s.username,

decode(l.type,“TM“,“TABLE LOCK“,“TX“,“ROW LOCK“,NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,

s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser

FROM v$session s,v$lock l,dba_objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id(+)

AND s.username is NOT NULL

如果發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待，以下的語句可以查詢到誰鎖了表，而誰在等待。

SELECT /*+ rule */ lpad(“ “,decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

o.owner,o.object_name,o.object_type,s.sid,s.serial#

FROM v$locked_object l,dba_objects o,v$session s

WHERE l.object_id=o.object_id

AND l.session_id=s.sid

ORDER BY o.object_id,xidusn DESC

以上查詢結果是一個樹狀結構，如果有子節點，則表示有等待發生。如果想知道鎖用了哪個回滾段，還可以關聯到V$rollname，其中xidusn就是回滾段的USN

[Q] 如何有效的刪除一個大表(extent數很多的表)

[A] 一個有很多(100k)extent的表，如果只是簡單地用drop table的話，會很大量消耗CPU（Oracle要對fet$、uet$資料字典進行操作），可能會用上幾天的時間，較好的方法是分多次刪除extent，以減輕這種消耗：

1. truncate table big-table reuse storage;

2. alter table big-table deallocate unused keep 2000m ( 原來大小的n-1/n);

3. alter table big-table deallocate unused keep 1500m ;

....

4. drop table big-table;

17.如何收縮臨時資料檔案的大小

9i以下版本採用

ALTER DATABASE DATAFILE “file name“ RESIZE 100M類似的語句

9i以上版本採用

ALTER DATABASE TEMPFILE “file name“ RESIZE 100M

注意，臨時資料檔案在使用時，一般不能收縮，除非關閉資料庫或斷開所有會話，停止對臨時資料檔案的使用。