2-調優步驟：  

1 設計調優 

2 應用調優 程式設計師程式碼

3 記憶體

4 連線

5 作業系統

2-已有產品的調優

 1 定位問題

 2 檢視作業系統和oracle統計

 3 考慮通用系統錯誤

 4 假定問題

 5 透過引數修改去調優

 

3調優方法

 1 檢查日誌和trace  file

 2 檢查引數檔案

3 檢查 記憶體 cpu

4 那些sql佔用cpu和io

如果相應時間慢;

 1分析工作實現和相應時間

2 檢查那部分時間長

3 細化問題

 

 

3 檢視alert  log

 1 檢視位置

Show parameter dump

Name 是 backgroup_dump_dest 所對應的目錄是alert log所在的目錄

2 檢查檔案

  1 檢查 錯誤是 ORA 的錯誤和快損壞錯誤

  2 分析這個檔案

  3 定期移動和清空錯誤檔案

     清空檔案: cat  /dev/null  >檔名

 

 

 

改變系統值

 Alter system set 引數名=引數值 scope=  both 或 spfile 

 Both 代表同時改變記憶體和檔案 

 

4 檢視user trace file（使用者操作日誌檔案）

Show parameter dump

Name 是 user_dump_dest 所對應的目錄是user trace file所在的目錄

產生trace檔案的方法：

  1 修改自己的session：alter  session set sql _trace=true

  2 使用sys使用者登入，呼叫儲存過程：將某個使用者的session 產生trace檔案

 

 

  3 sql_trace系統引數確定系統中的所有使用者產生日誌

5-dba的檢視

　dba_tables,dba_tab_colums,dba_indexes,index_stats

 

5 重要的動態檢視

系統相關檢視

V$sysstat 系統統計

V$sgastat SGA統計

V$system_event 系統事件 事件名 v$event_name

 

session 相關的檢視

 

 

v$sesstat session統計資料

v$sesstat_event 

6 重要檢視

 

 

 

 

 

 

資料庫設計

1 不同用途的資料放在不同的tablespace避免資源爭用

2 不同的tablespace放在不同的物理驅動器調高I/O併發能力

3 日誌檔案放在速度快的磁碟上，資料檔案可以放在速度慢的磁碟上

4 日誌檔案的同組中不同成員放在不同的物理磁碟

 

 

7透過物理檔案的讀寫次數來調優

 

Select d.tablespace_name,d.file_name,f.phyrds,f.phywrts from v$filestat f,dba_data_files d where f.file#=d.file_id;

可以透過設定Oracle讀寫塊的大小提高I/O效率

引數如下：

DB_FILE_MULTIBLOCK_READ_COUNT

 

8 striping（均勻分佈資料）

 1 作業系統或檔案系統支援striping

2 建表或更改表時使用 allocate 可以達到striping的目的

8 DB_FILE_MULTIBLOCK_READ_COUNT

可以透過設定Oracle讀寫塊的大小提高I/O效率

 

8 大於6秒的操作查詢

 V$session_longops

Select  name,value from v$sysstat where nane like ‘ %table scan%’

8 監控全表掃描

 

 

3監控大表執行情況

大於6秒的全表掃描記錄在

v$session_longops

檢視全表掃描的記錄條數

 

Show parameter log_ch

 

9 Oracle啟動時間

透過調整 FAST_START_MTTR_TARGET保證Oracle啟動時間，Oracle可以根據這個引數調整其他引數

 

9重做日誌檔案

 如果沒有足夠重做日誌檔案，就會造成系統阻塞，

 

 

解決辦法:

     

1 加大重做日誌檔案

2 增加歸檔程式

    設定引數：LOG_ARCHIVE_MAX_PROCESSES

   在alert log中出現如下錯誤說明日誌檔案已經寫滿

 

透過：

V$log_file

V$log

提高sql命中

11查詢系統hard parse的次數

 

V$sysstat 表中的 parse count （hard）是解析sql條數

11 提高sql相似sql在共享池中的命中率

 

12 使用繫結變數提高共享sql

   使用preparedStatement

 

13 檢視共享池資訊

 

 

13檢視v$sgastat中的free memory決定是否要擴大共享記憶體

13 檢視執行少於5次的sql透過檢視解析次數與執行次數找到效率低的原因

 select sql_text from v$sqlarea where executions<5 order by upper(sql_text)

13 檢視sql檢視解析次數與執行次數找到效率低的原因

 select sql_text, parse_calls,executions from v$sqlarea 

14 儲存過程的呼叫著是過程的屬主，不是呼叫者

14 在sql中新增表的使用者可以減少訪問資料字典的次數

14 在業務高發期使用ddl語句會降低sql的命中，因為sql需要重新解析

14 在業務高發期V$librarycache 中的reloads和invalidations要儘可能小，如果reloads太大說明shared pool太小

14 檢視sql命中率：

Select gethitratio from v$librarycache where namespace=’SQL　AREA’

調整shared pool

14檢視那些sql正在執行：

Select sql_text,users_executing from v$sqlarea

 

15 檢視v$share_pool_advice由Oracle提供共享池最佳化引數

 

16 使用下面的sql 估算共享記憶體

 

將以上兩項值相加基本就是估算的librarycache 的記憶體大小

檢視共享池資訊

Show parameter shared

16 設定保留池的大小，以滿足大物件和臨時物件的使用

Shared_pool_reserved_size

 

   Request_failures持續太大說明保留池太小

   需要加大保留池和共享池

 

16調整共享池大小

  Shared_pool_size

  不能最大值取決於作業系統的記憶體

不能太小

 

17 在共享區，一直保留在記憶體區，不參與記憶體淘汰演算法

 

清除shared pool

Alter system flush shared_pool

18 查詢匿名塊

 

 

 

18 調整Data Dictionary cache

透過v$rowcache

 

 

    調整共享池來調整DD cache

11 large pool

 

Large_pool_size

 Show parameter size

 

 

 

19Buffer cache 調優

 

 1 降低sql讀入buffer資料

2 增大buffer cache

3 

 

 

 

Free buffer inspected ：找到空閒塊之前做的消耗

Free buffer waits 沒有可用的空閒塊

Buffer busy waits 塊被佔用

 

21 查詢等待的事件及等待塊資訊

 

 

21 計算命中率

 

 

 

1 大表多次掃描會降低命中率

2 好的命中率：命中率高，壞事件少

3 將常用的資料放在中介軟體層，減輕資料庫壓力

Cache使用

23增大cache_size

 

增大cache之前

1 沒有3中事件

2 最佳化了sql

3 防止作業系統將sga放到os的交換區

4 命中率低

使用

 增加db_cache_size

 

增加時這個只不能大於sqa_size

 

 

 

23 Oracle 9i 有三中pool

Reclye pool

Keep pool

Default pool

  三個buffer pool大小不一樣，演算法一樣，可以利用這一特性決定吧物件放在那個pool裡

 

使用辦法：

1 熱塊放在 default pool

2 溫塊放在 keep pool

3 冷快放在 recycle pool

 

 

23放物件放在pool中的語法：

 

 

 

24檢視物件使用的塊

 

 

 

 

 

 

24計算命pool中率：

 

 

 

 

 

24建立cache 表

 方法一：建立表時

 

 

方法2：修改普通表到cache 表

方法3：查詢後放在cache表

 

 

25自動管理記憶體

 

25配置多個DBWn

  Alter system db_writer_precesses //作業系統必須支援非同步I/O，多cpu使用

  

 

1 開啟非同步I/O 

2 在不支援非同步I/O的情況下，修改dbwr_io_slaves數量也可以達到增加DBWn的效果

 

 

 

27LGWR是使效能下將

 

Redo Entries 存在的記錄數：

Redo buffer allocation retries: 申請redo buffer 的次數,此值過大說明 redo buffer 太小

 

28當發生下列事件時會引起效能下降

  1 日誌檔案交換

 

   增大redo log files

  2 檢查點未完成

 

 

 

 

解決辦法：

a設定Fast_start_mttr_target越小增加checkPoint的次數

b 增加redo log 組和成員

 

3 歸檔引起

 

 

解決辦法：

1 增加redo log 組和成員

2  archive檔案太多，沒地方寫歸檔檔案，可以刪除檔案

3 增加歸檔程式 log_archive_max_proceses

 

28加速資料插入的時間：

 使用無log方式，批次插入資料

 

 

共享伺服器

 

 

 

 

 

 

 

 

 

 

最佳化排序

 

32最佳化排序-自動管理PGA

設定自動管理PGA：work_size_policy = auto 

在自動管理PAG模式下設定PAG大小：pga_aggregate_target 

 

 

 

 

32設定PGA建議：

 

 

32設定PGA的例子

 

32 oracle的記憶體設定：

Oracle的記憶體由SGA和PGA 之和 決定

 

 

 

 

Over allocation count增大說明 aggregate pga target 太小

 

 

34 查詢建議的PGA記憶體

 

 

Select round(pga_target_for_estimate/1024/1024) as target_mb,round(BYTES_PROCESSED/1024/1024) as process_mb,ESTD_PGA_CACHE_HIT_PERCENTAGE hit_ratio,ESTD_OVERALLOC_COUNT from v$pga_target_advice;

 

34 檢視 sesstat

 

35單個使用者的排序記憶體

 

 

36 統計排序

 

 

 

36避免排序：

 

36當磁碟排序/記憶體排序比值大於5%，需要增大排序記憶體

 

 

 

Sql調優

42 設定sql最佳化模式

 

Choose：9i的預設值

All_rows：最大吞吐率 11的預設值

First_rows_n:最快相應時間,可以設定

First_rows: 10g後不要用

43穩定執行計劃

 Optimizer_features_enable=資料庫版本號

44 獲得sql 的執行計劃

1 建立plan表

 @？/目錄/ultxplan.sql

@？代表oracle home目錄

2 獲得

 

3 檢視執行計劃

 A Select * from table_plan

B select * from table(dbms_xplan.display); 

44 生成trace檔案及檢視

1 開啟trace功能

 

2 使用tkprof格式化檔案

 

 

44 自動trace

1 建立table_plan表

@？/目錄/ultxplan.sql

@？代表oracle home目錄

2建立同義詞並授權

 

3 執行指令碼、授權

 

4 設定自動trace，並得到結果

 

 

統計資訊

47獲得表的統計資訊

1 獲得物件id

 

 

2 獲得表的統計資訊

 

 

 

 

 

52 查詢表塊使用情況

1 分析表

 

2 檢視錶

 

52擴充套件和取消塊

 

 

 

 

 

 

 

 

 

 

OLTP系統使用小塊4k到8k

OLAP系統使用16k到64k

 

索引管理

 

54 監控索引空間

 

Rebuild：會重建索引 ，代價高

Coalesce：修補索引

 

54檢視索引使用情況：

查詢表object_usage

 

 

 

Oracle資料塊架構

55建立叢集表：

將兩張表放在一個segment上

 

58建立索引叢集表：

 

1 建立叢集

Create cluster mycluster (deptno number(2)) size 1024

Deptno 叢集的key，

1024 一個block的大小，決定可以放幾條記錄。

預設是放一條記錄

 

2 建立索引

 

 

 

 

 

 

59分割槽表

時間分割槽表：

 

 

 

 

 

 

 

 

 

Hash分割槽表

 

 

 

 

應用調優

 

60壓縮索引

壓縮索引可以提高索引的查詢效率，節約空間

 

62建立索引表：

   當建立的索引和表的容量一樣時。就直接建立索引表比以往的方案要好。

  索引表沒有rowid

 

 

 

 

Index_stats索引的的資訊

 

 

 

 

建立倒序索引：

 

 

 

Bitmap索引在大表，只讀表，重複欄位多，較好，維護較難

 

 

 

 

給索引表建邏輯rowid使用下列語法：

 

 

66 query rewrite

 

 

 

 

 

 

68 鎖相關檢視

 

 

 

 

 

69 手動鎖表

 

Statspack

安裝schema

1 產看說明文件：

$oracle_home/rdbms/admin/Spdoc.Txt

2 建立表空間

 

3執行指令碼

建立指令碼;

 

 

刪除的指令碼：錯誤可以先刪除

 

 

 

透過sprepcon.Sql修改引數

 

 

 

 

檢視oracle快取的命中率（大於90％） 

select 1 - ((physical.value - direct.value - lobs.value) / logical.value) 

"Buffer Cache Hit Ratio" 

from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical 

where physical.name = 'physical reads' 

and direct.name='physical reads direct' 

and lobs.name='physical reads direct (lob)' 

and logical.name='session logical reads'; 

 

查詢檔案資訊：select * from  dba_data_files

 

 

 

 

 

 

 

oracle redo log管理：

1．  組成員要分散，磁碟IO要快

 

2．  日誌檔案大小分配要合理

 

保證每個組的切換時間應該不小於20分鐘左右

 

切換日誌：

 

Alter system  switch logfile;

 

新增日誌組：

 

alter database add logfile group 4 '/u01/oracle/oradata/orcl/redo04.log' size 50m;

 

下次切換日誌會優先使用此檔案

 

其中group 4 可以省略不寫，系統會自動分配

 

 

 

 

 

 

 

新增有多個成員的組：

 

 

 

alter database add logfile ('/u01/oracle/oradata/orcl/redo06.log','/u01/oracle/oradata/orcl/redo6.log') size 50m;

 

 

 

往已經有的組裡新增成員：

 

alter database add logfile member '/u01/oracle/oradata/orcl/redo4.log' to group 4;

 

大小預設是組內已有成員的大小。

 

alter database add logfile member '<dir>' to group

 

刪除日誌組：

 

日誌組狀態不能使current和active

 

alter database drop logfile group 6,group 5

 

 

 

刪除某個成員：

 

alter database drop logfile member '<dir>';

 

 

 

重做日誌的重新命名：

 

在mount和open階段才能更改，因為是改到控制檔案裡面了。

 

日誌檔案不能是current和active

 

先把檔案複製到另一個名稱

 

Ho cp <old filename> <new name>

 

Alter datebase  rename file ‘<old filename>’ to ‘<new filename>’

 

非歸檔模式可以直接改隨便哪個狀態都可但不能使current

 

 

 

Current 和active丟失後資料庫啟動會成問題

 

 

 

改變日誌組的大小：(先把原來的組刪除，在新建同名的組)

 

Alter database drop logfile group 3;

 

Alter database add logfile group 3 (‘/u01/oracle/oradata/orcl/redo03.log’,’ /u01/oracle/oradata/orcl/redo03.log’);

 

 

 

監控日誌組自動切換的時間間隔：

 

Select  to_char(first_time,’yyyy-mm-dd hh24:mi:ss’’),group# from v$log;

 

 

 

日誌檔案需要監視內容：

 

1.       位置在哪裡，是否存在

 

2.       磁碟空間是否足夠

 

3.       日誌切換間隔時間

 

4.       看日誌組下是否具有多個成員

 

5.       不一致

 

日誌不一致的處理方法：

 

清空日誌：Alter  database clear logfile group n; 清空非current 和active的日誌

 

Alter database clear unarchived logfile group n; 清空非current的日誌；

 

 

 

日誌檔案全部丟失：

 

歸檔模式下不使用隱藏引數：

 

Recover database using backup controlfile;

 

Alter database open resetlogs;

 

使用隱藏引數方法：

 

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

 

Startup  force mount

 

 

 

Alter database open resetlogs

 

 

 

重置隱藏引數（要不容易引起錯誤）

 

Alter  system reset  "_allow_resetlogs_corruption" scope =spfile sid =’*’;

 

 

 

create spfile from pfile='/home/oracle/spfileFLT.ora';

 

 

startup pfile='/home/oracle/spfileFLT.ora';

create spfile='/orac/orahome/10.2.0/dbs/spfileFLT1.ora' from pfile='/orac/orahome/admin/FLT/pfile/init.ora.2302013164056';

 

 

調優

判斷回滾段競爭的SQL語句：（當Ratio大於2時存在回滾段競爭，需要增加更多的回滾段）

 

select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) * 100 ratio

from v$rollstat rs, v$rollname rn

where rs.USN = rn.usn

 

判斷恢復日誌競爭的SQL語句：（immediate_contention或wait_contention的值大於1時存在競爭）

select name,

(t.IMMEDIATE_MISSES /

decode((t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES),

0,

-1,

(t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES))) * 100 immediate_contention,

(t.MISSES / decode((t.GETS + t.MISSES), 0, -1, (t.GETS + t.MISSES))) * 100 wait_contention

from v$latch t

where name in ('redo copy', 'redo allocation')

 

 

判斷表空間碎片：（如果最大空閒空間佔總空間很大比例則可能不存在碎片，如果比例較小，且有許多空閒空間，則可能碎片很多）

select t.tablespace_name,

sum(t.bytes),

max(t.bytes),

count(*),

max(t.bytes) / sum(t.bytes) radio

from dba_free_space t

group by t.tablespace_name

order by t.tablespace_name

確定命中排序域的次數：

select t.NAME, t.VALUE from v$sysstat t where t.NAME like 'sort%'

檢視當前SGA值：

select * from v$sga

確定高速緩衝區命中率：（如果命中率低於70％，則應該加大init.ora引數中的DB_BLOCK_BUFFER的值）

select 1 - sum(decode(name, 'physical reads', value, 0)) /

(sum(decode(name, 'db block gets', value, 0)) +

sum(decode(name, 'consistent gets', value, 0))) hit_ratio

from v$sysstat t

where name in ('physical reads', 'db block gets', 'consistent gets')

確定共享池中的命中率：（如果ratio1大於1時，需要加大共享池，如果ratio2大於10％時，需要加大共享池SHARED_POOL_SIZE）

select sum(pins) pins,

sum(reloads) reloads,

(sum(reloads) / sum(pins)) * 100 ratio1

from v$librarycache

select sum(gets) gets,

sum(getmisses) getmisses,

(sum(getmisses) / sum(gets)) * 100 ratio2

from v$rowcache

查詢INIT.ORA引數：

select * from v$parameter

/////

Oracle效能引數檢視（轉）

0、資料庫引數屬性

col PROPERTY_NAME format a25

col PROPERTY_VALUE format a30

col DESCRIPTION format a100

select * from database_properties;

select * from v$version;

1、求當前會話的SID，SERIAL#

SELECT Sid, Serial# FROM V$session

WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');

2、查詢session的OS程式ID

SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,s.Osuser, s.Machine

FROM V$process p, V$session s, V$bgprocess b

WHERE p.Addr = s.Paddr

AND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1)

UNION ALL

SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,s.Serial#, s.Osuser, s.Machine

FROM V$process p, V$session s

WHERE p.Addr = s.Paddr

And (s.sid=&1 or p.spid=&1)

AND s.Username IS NOT NULL;

3、根據sid檢視對應連線正在執行的sql 

SELECT /*+ PUSH_SUBQ */ Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,

Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,

Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,

Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,

SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status

FROM V$sqlarea WHERE Address = (SELECT Sql_Address

FROM V$session WHERE Sid = &sid );

4、查詢object為哪些程式所用

SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,

a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,

a.OBJECT Object_Name,

Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,

p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,

s.Status Session_Status

FROM V$session s, V$access a, V$process p

WHERE s.Paddr = p.Addr

AND s.TYPE = 'USER'

AND a.Sid = s.Sid

AND a.OBJECT = '&obj'

ORDER BY s.Username, s.Osuser

5、檢視有哪些使用者連線

SELECT s.Osuser Os_User_Name,Decode(Sign(48 - Command),1,To_Char(Command),

'Action Code #' || To_Char(Command)) Action,

p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,

s.Program Program, s.Username User_Name,

s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,

0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num

FROM V$session s, V$process p

WHERE s.Paddr = p.Addr

AND s.TYPE = 'USER'

ORDER BY s.Username, s.Osuser

6、根據v.sid檢視對應連線的資源佔用等情況

SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat v

WHERE v.Sid = &sid

AND v.Statistic# = n.Statistic#

ORDER BY n.CLASS, n.Statistic#

7、查詢耗資源的程式（top session）

SELECT s.Schemaname Schema_Name,Decode(Sign(48 - Command),

1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,

s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value

FROM V$sesstat St, V$session s, V$process p

WHERE St.Sid = s.Sid

AND St.Statistic# = To_Number('38')

AND ('ALL' = 'ALL' OR s.Status = 'ALL')

AND p.Addr = s.Paddr

ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC

8、檢視鎖（lock）情況

SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name,Decode(Ls.TYPE,

'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock','TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,o.Object_Name OBJECT,Decode(Ls.Lmode,1, NULL, 2, 'Row Share', 3, 'Row Exclusive',

4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',NULL) Lock_Mode,o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba_Objects o, 

(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,l.Id2 FROM V$session s, V$lock l

WHERE s.Sid = l.Sid) Ls

WHERE o.Object_Id = Ls.Id1

AND o.Owner <> 'SYS'

ORDER BY o.Owner, o.Object_Name;

9、檢視等待（wait）情況

SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value

FROM V$waitstat Ws, V$sysstat Ss

WHERE Ss.NAME IN ('db block gets', 'consistent gets')

GROUP BY Ws.CLASS, Ws.COUNT;

10、求process/session的狀態

SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#

FROM V$process p, V$session s

WHERE s.Paddr = p.Addr;

11、求誰阻塞了某個session(10g)

SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time

FROM V$session

WHERE State IN ('WAITING')

AND Wait_Class != 'Idle';

12、查會話的阻塞

col user_name format a32

SELECT /*+ rule */ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,

o.Owner, o.Object_Name, 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;

col username format a15

col lock_level format a8

col owner format a18

col object_name format a32

SELECT /*+ rule */ s.Username,Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,

o.Owner, o.Object_Name, s.Sid, s.Serial#

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;

13、求等待的事件及會話資訊/求會話的等待及會話資訊

SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,Se.Average_Wait

FROM V$session s, V$session_Event Se

WHERE s.Username IS NOT NULL

AND Se.Sid = s.Sid

AND s.Status = 'ACTIVE'

AND Se.Event NOT LIKE '%SQL*Net%'

ORDER BY s.Username;

SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,Sw.Seconds_In_Wait

FROM V$session s, V$session_Wait Sw

WHERE s.Username IS NOT NULL

AND Sw.Sid = s.Sid

AND Sw.Event NOT LIKE '%SQL*Net%'

ORDER BY s.Username;

14、求會話等待的file_id/block_id

col event format a24

col p1text format a12

col p2text format a12

col p3text format a12

SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3

FROM V$session_Wait

WHERE Event NOT LIKE '%SQL%'

AND Event NOT LIKE '%rdbms%'

AND Event NOT LIKE '%mon%'

ORDER BY Event;

SELECT NAME, Wait_Time

FROM V$latch l

WHERE EXISTS (SELECT 1

FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3

FROM V$session_Wait

WHERE Event NOT LIKE '%SQL%'

AND Event NOT LIKE '%rdbms%'

AND Event NOT LIKE '%mon%') x

WHERE x.P1 = l.Latch#);

15、求會話等待的物件

col owner format a18

col segment_name format a32

col segment_type format a32

SELECT Owner, Segment_Name, Segment_Type

FROM Dba_Extents

WHERE File_Id = &File_Id

AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1;

16、求出某個程式，並對它進行跟蹤

SELECT s.Sid, s.Serial#

FROM V$session s, V$process p

WHERE s.Paddr = p.Addr

AND p.Spid = &1;

Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);

Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE);

17、求當前session的跟蹤檔案

SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename

FROM V$process p, V$session s, V$parameter P1, V$parameter P2

WHERE P1.NAME = 'user_dump_dest'

AND P2.NAME = 'instance_name'

AND p.Addr = s.Paddr

AND s.Audsid = Userenv('SESSIONID')

AND p.Background IS NULL

AND Instr(p.Program, 'CJQ') = 0;

18、求出鎖定的物件

SELECT Do.Object_Name, Session_Id, Process, Locked_Mode

FROM V$locked_Object Lo, Dba_Objects Do

WHERE Lo.Object_Id = Do.Object_Id;

19、DB_Cache建議

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads

FROM V$DB_CACHE_ADVICE

WHERE name = 'DEFAULT'

AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')

AND advice_status = 'ON';

20、檢視各項SGA相關引數：SGA，SGASTAT

select substr(name,1,10) name,substr(value,1,10) value 

from v$parameter where name = 'log_buffer';

select * from v$sgastat ;

select * from v$sga;

show parameters area_size   #檢視 各項區域記憶體引數， 其中sort_area為排序引數用；

 

各項檢視建議引數值：V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關於PGA

也有相關檢視V$PGA_TARGET_ADVICE 等。

21、記憶體使用鎖定在實體記憶體：

AIX 5L（AIX 4.3.3 以上）

logon aix as root

cd /usr/samples/kernel

./vmtune (資訊如下) v_pingshm已經是1

./vmtune -S 1

然後oracle使用者修改initSID.ora 中 lock_sga = true

重新啟動資料庫

HP UNIX

Root身份登陸

Create the file "/etc/privgroup": vi /etc/privgroup

Add line "dba MLOCK" to file

As root, run the command "/etc/setprivgrp -f /etc/privgroup":

$/etc/setprivgrp -f /etc/privgroup

oracle使用者修改initSID.ora中lock_sga=true

重新啟動資料庫

SOLARIS (solaris2.6以上)

8i版本以上資料庫預設使用隱藏引數 use_ism = true ，自動鎖定SGA於記憶體中,不用設定

lock_sga, 如果設定 lock_sga =true 使用非 root 使用者啟動資料庫將返回錯誤。

WINDOWS （作用不大）

不能設定lock_sga=true,可以透過設定pre_page_sga=true,使得資料庫啟動的時候就把所有內

存頁裝載，這樣可能起到一定的作用。

22、記憶體引數調整

資料緩衝區命中率

select value from v$sysstat where name ='physical reads';

select value from v$sysstat where name ='physical reads direct';

select value from v$sysstat where name ='physical reads direct (lob)';

select value from v$sysstat where name ='consistent gets';

select value from v$sysstat where name = 'db block gets';

這裡命中率的計算應該是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果發現命中率低於90%,則應該調整應用可可以考慮是否增大資料緩衝區；

共享池的命中率

select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

假如共享池的命中率低於95%,就要考慮調整應用（通常是沒使用bind var ）或者增加記憶體；

關於排序部分

select name,value from v$sysstat where name like '%sort%';

假如我們發現sorts (disk)/ (sorts (memory)+ sorts (disk))的比例過高，則通常意味著

sort_area_size 部分記憶體較小，可考慮調整相應的引數。

關於log_buffer

select name,value from v$sysstat

where name in('redo entries','redo buffer allocation retries');

假如 redo buffer allocation retries/ redo entries 的比例超過1%我們就可以考慮增大log_buffer

 

/////

July 28

oracle 常用SQL查詢

 

1、檢視錶空間的名稱及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

 

2、檢視錶空間物理檔案的名稱及大小

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

 

 

3、檢視回滾段名稱及大小

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

 

 

4、檢視控制檔案

select name from v$controlfile;

 

5、檢視日誌檔案

select member from v$logfile;

 

 

6、檢視錶空間的使用情況

select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space

group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

 

7、檢視資料庫庫物件

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

 

8、檢視資料庫的版本　

Select version FROM Product_component_version

Where SUBSTR(PRODUCT,1,6)='Oracle';

 

9、檢視資料庫的建立日期和歸檔方式

Select Created, Log_Mode, Log_Mode From V$Database;

 

10、捕捉執行很久的SQL

column username format a12

column opname format a16

column progress format a8

select username,sid,opname,

round(sofar*100 / totalwork,0) || '%' as progress,

time_remaining,sql_text

from v$session_longops , v$sql

where time_remaining <> 0

and sql_address = address

and sql_hash_value = hash_value

/

 

11。檢視資料表的引數資訊

SELECT partition_name, high_value, high_value_length, tablespace_name,

pct_free, pct_used, ini_trans, max_trans, initial_extent,

next_extent, min_extent, max_extent, pct_increase, FREELISTS,

freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,

empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,

last_analyzed

FROM dba_tab_partitions

--WHERE table_name = :tname AND table_owner = :towner

ORDER BY partition_position

 

12.檢視還沒提交的事務

select * from v$locked_object;

select * from v$transaction;

13。查詢object為哪些程式所用

select

p.spid,

s.sid,

s.serial# serial_num,

s.username user_name,

a.type object_type,

s.osuser os_user_name,

a.owner,

a.object object_name,

decode(sign(48 - command),

1,

to_char(command), 'Action Code #' || to_char(command) ) action,

p.program oracle_process,

s.terminal terminal,

s.program program,

s.status session_status

from v$session s, v$access a, v$process p

where s.paddr = p.addr and

s.type = 'USER' and

a.sid = s.sid and

a.object='SUBSCRIBER_ATTR'

order by s.username, s.osuser

 

 

14。回滾段檢視

select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents

Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,

v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,

sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,

v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and

v$rollstat.usn (+) = v$rollname.usn order by rownum

 

 

15。耗資源的程式（top session）

select s.schemaname schema_name, decode(sign(48 - command), 1,

to_char(command), 'Action Code #' || to_char(command) ) action, status

session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,

nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,

s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p

where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'

or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

 

 

16。檢視鎖（lock）情況

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,

decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,

'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2

from sys.dba_objects o, ( select s.osuser, s.username, l.type,

l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,

v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner

<> 'SYS' order by o.owner, o.object_name

 

 

17。檢視等待（wait）情況

SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value

FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',

'consistent gets') group by v$waitstat.class, v$waitstat.count

 

 

18。檢視sga情況

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

 

 

19。檢視catched object

SELECT owner, name, db_link, namespace,

type, sharable_mem, loads, executions,

locks, pins, kept FROM v$db_object_cache

 

 

20。檢視V$SQLAREA

SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,

VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,

USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,

BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA

 

21。檢視object分類數量

select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,

'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from

sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3

, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select

'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from

 

22。按使用者檢視object種類

select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,

sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))

clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,

NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,

sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))

others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =

o.owner# and u.name <> 'PUBLIC' group by u.name order by

sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$

 

23。有關connection的相關資訊

1）檢視有哪些使用者連線

   select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),

'Action Code #' || to_char(command) ) action, p.program oracle_process,

status session_status, s.terminal terminal, s.program program,

s.username user_name, s.fixed_table_sequence activity_meter, '' query,

0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num

from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'

order by s.username, s.osuser

 2）根據v.sid檢視對應連線的資源佔用等情況

select n.name,

v.value,

n.class,

n.statistic#

from v$statname n,

v$sesstat v

where v.sid = 71 and

v.statistic# = n.statistic#

order by n.class, n.statistic#

 3）根據sid檢視對應連線正在執行的sql

select /*+ PUSH_SUBQ */

command_type,

sql_text,

sharable_mem,

persistent_mem,

runtime_mem,

sorts,

version_count,

loaded_versions,

open_versions,

users_opening,

executions,

users_executing,

loads,

first_load_time,

invalidations,

parse_calls,

disk_reads,

buffer_gets,

rows_processed,

sysdate start_time,

sysdate finish_time,

'>' || address sql_address,

'N' status

from v$sqlarea

where address = (select sql_address from v$session where sid = 71)

 

 

24．查詢表空間使用情況select a.tablespace_name "表空間名稱",

100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "佔用率(%)",

round(a.bytes_alloc/1024/1024,2) "容量(M)",

round(nvl(b.bytes_free,0)/1024/1024,2) "空閒(M)",

round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",

Largest "最大擴充套件段(M)",

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "取樣時間"

from (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

(select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b,

(select round(max(ff.length)*16/1024,2) Largest,

ts.name tablespace_name

from sys.fet$ ff, sys.file$ tf,sys.ts$ ts

where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#

group by ts.name, tf.blocks) c

where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

 

25. 查詢表空間的碎片程度

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

having count(tablespace_name)>10;

alter tablespace name coalesce;

alter table name deallocate unused;

create or replace view ts_blocks_v as

select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space

union all

select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

select * from ts_blocks_v;

select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space

group by tablespace_name;

 

26.檢視有哪些例項在執行：

select * from v$active_instances;

12:15 PM | Add a comment | Permalink | Blog it | Oracle

ORACLE效能調優原則

資料庫的硬體配置：CPU、記憶體、網路條件

1.         CPU：在任何機器中CPU的資料處理能力往往是衡量計算機效能的一個標誌，並且ORACLE是一個提供並行能力的資料庫系統，在CPU方面的要求就更高了，如果執行佇列數目超過了CPU處理的數目，效能就會下降，我們要解決的問題就是要適當增加CPU的數量了，當然我們還可以將需要許多資源的程式KILL掉；

2.         記憶體：衡量機器效能的另外一個指標就是記憶體的多少了，在ORACLE中記憶體和我們在建資料庫中的交換區進行資料的交換，讀資料時，磁碟I/O必須等待物理I/O操作完成，在出現ORACLE的記憶體瓶頸時，我們第一個要考慮的是增加記憶體，由於I/O的響應時間是影響ORACLE效能的主要引數，我將在這方面進行詳細的講解

3.         網路條件：NET*SQL負責資料在網路上的來往，大量的SQL會令網路速度變慢。比如10M的網路卡和100的網路卡就對NET*SQL有非常明顯的影響，還有交換機、集線器等等網路裝置的效能對網路的影響很明顯，建議在任何網路中不要試圖用3個集線器來將網段互聯。

?         OS引數的設定

下表給出了OS的引數設定及說明，DBA可以根據實際需要對這些引數進行設定

核心引數名

說明

bufpages

對buffer空間不按靜態分配，採用動態分配，使bufpages值隨nbuf一起對buffer空間進行動態分配。

create_fastlinks

對HFS檔案系統允許快速符號連結，

dbc_max_pct

加大最大動態buffer空間所佔實體記憶體的百分比，以滿足應用系統的讀寫命中率的需要。

dbc_min_pct

設定最小動態buffer空間所佔實體記憶體的百分比

desfree

提高開始交換操作的最低空閒記憶體下限，保障系統的穩定性，防止出現不可預見的系統崩潰（Crash）。

fs_async

允許進行磁碟非同步操作，提高CPU和磁碟的利用率

lotsfree

提高系統解除換頁操作的空閒記憶體的上限值，保證應用程式有足夠的可用記憶體空間。

maxdsiz

針對系統資料量大的特點，加大最大資料段的大小，保證應用的需要。（32位）

maxdsiz_64bit

maximum process data segment size for 64_bit

Maxssiz

加大最大堆疊段的大小。（32_bit）

maxssiz_64bit

加大最大堆疊段的大小（64_bit）

Maxtsiz

提高最大程式碼段大小，滿足應用要求

maxtsiz_64bit

原值過大，應調小

Minfree

提高停止交換操作的自由記憶體的上限

Shmem

允許進行記憶體共享，以提高記憶體的利用率。

Shmmax

設定最大共享記憶體段的大小，完全滿足目前的需要。

Timeslice

由於系統的瓶頸主要反映在磁碟I/O上，因此　降低時間片的大小，一方面可避免因磁碟I/O不暢造成CPU的等待，從而提高了CPU的綜合利用率。另一方面減少了程式的阻塞量。

unlockable_mem

提高了不可鎖記憶體的大小，使可用於換頁和交換的記憶體空間擴大,用以滿足系統對記憶體管理的要求。

 

摘自：http://www.zdnet.com.cn/developer/database/story/0,3800066906,39276980,00.htm

11:27 AM | Add a comment | Permalink | Blog it | Oracle

UNIX記憶體佔用基本檢查

 

1: 使用top指令.

  top指令是按cpu佔用率排序的,如果想一次獲得所有程式的快照,使用命令

top -n [最大程式數] -f 輸出到檔案,比如top -n 1000 -f topsnapshot.log

  top指令輸出記憶體的統計資訊包括

Memory: 2614368K (2249100K) real, 5838616K (5264696K) virtual, 113028K free  Page# 1/1

其中沒有括號起來的是總數,括號括起來的部分是活動程式使用的記憶體數,free則是真實空閒的實體記憶體數.

程式資訊的列包括

CPU TTY  PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND

和記憶體相關的只有SIZE和RES

SIZE：任務的程式碼加上資料再加上棧空間的大小。 

RES：任務使用的實體記憶體的總數量

要檢查程式是否有記憶體洩露,和實際佔用的記憶體大小,看RES列即可.

2:檢查共享記憶體佔用的記憶體容量

使用ipcs -m -b命令,-m表示檢查共享記憶體,-b表示輸出每個記憶體的位元組數,得到的共享記憶體資訊輸出列包括:

T         ID     KEY        MODE        OWNER     GROUP      SEGSZ

SEGSZ列則是位元組數.把每列相加則是共享記憶體佔用的記憶體總數.

3: 調整核心動態高速緩衝區引數

HP-UX某些型號的伺服器執行的時候需要幾乎1G的記憶體維持系統執行,比如作為裝置緩衝什麼的.

可以用kmtune命令檢查核心配置引數,動態高速緩衝區引數dbc_min_pct 和 dbc_max_pct參數列示一個高速緩衝區允許的可用記憶體的最小和最大百分比,dbc_max_pct的預設值是50,一般設定為10即可.

4:在HP-UX上還可以使用glance

glance是個很強的工具，可惜不是免費的....

11:16 AM | Add a comment | Permalink | Blog it | HPUX

July 27

Oracle的最佳化器(Optimizer)

本文的目的： 

1、說一說Oracle的Optimizer及其相關的一些知識。 

2、回答一下為什麼有時一個表的某個欄位明明有索引,當觀察一些SQL的執行計劃時,發現確不走索引的問題。 

3、如果你對 FIRST_ROWS、 ALL_ROWS這兩種模式有疑惑時也可以看一下這篇文章。

開始吧:

 

Oracle在執行一個SQL之前,首先要分析一下語句的執行計劃,然後再按執行計劃去執行。分析語句的執行計劃的工作是由最佳化器(Optimizer)來完成的。不同的情況,一條SQL可能有多種執行計劃,但在某一時點,一定只有一種執行計劃是最優的,花費時間是最少的。相信你一定會用Pl/sql Developer、Toad等工具去看一個語句的執行計劃,不過你可能對Rule、Choose、First rows、All rows這幾項有疑問,因為我當初也是這樣的,那時我也疑惑為什麼選了以上的不同的項,執行計劃就變了?

1、最佳化器的最佳化方式

Oracle的最佳化器共有兩種的最佳化方式,即基於規則的最佳化方式(Rule-Based Optimization,簡稱為RBO)和基於代價的最佳化方式(Cost-Based Optimization,簡稱為CBO)。 

A、RBO方式：最佳化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的,當一個where子句中的一列有索引時去走索引。 

B、CBO方式：依詞義可知,它是看語句的代價(Cost)了,這裡的代價主要指Cpu和記憶體。最佳化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小 、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的,是你在做analyze後才出現的,很多的時侯過期統計資訊會令最佳化器做出一個錯誤的執行計劃,因些我們應及時更新這些資訊。在Oracle8及以後的版本,Oracle列推薦用CBO的方式。

我們要明瞭,不一定走索引就是優的 ,比如一個表只有兩行資料,一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時對這個表做全表掃描(full table scan)是最好的。

2、最佳化器的最佳化模式(Optermizer Mode)

最佳化模式包括Rule,Choose,First rows,All rows這四種方式,也就是我們以上所提及的。如下我解釋一下：

Rule:不用多說,即走基於規則的方式。

Choolse:這是我們應觀注的,預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊,則走CBO的方式,如果表或索引沒統計資訊,表又不是特別的小,而且相應的列有索引時,那麼就走索引,走RBO的方式。

First Rows:它與Choose方式是類似的,所不同的是當一個表有統計資訊時,它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應時間。

All Rows:也就是我們所說的Cost的方式,當一個表有統計資訊時,它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統計資訊則走基於規則的方式。

3、如何設定選用哪種最佳化模式

a、Instance級別

我們可以透過在init<SID>.ora檔案中設定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去選用3所提的四種方式,如果你沒設定OPTIMIZER_MODE引數則預設用的是Choose這種方式。

B、Sessions級別

透過SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;來設定。

C、語句級別

這些需要用到Hint,比如: 

SQL> SELECT /*+ RULE */ a.userid, 

2 b.name, 

3 b.depart_name 

4 FROM tf_f_yhda a, 

5 tf_f_depart b 

6 WHERE a.userid=b.userid;

4、為什麼有時一個表的某個欄位明明有索引,當觀察一些語的執行計劃確不走索引呢？如何解決呢 ？

A、不走索引大體有以下幾個原因 

♀你在Instance級別所用的是all_rows的方式 

♀你的表的統計資訊(最可能的原因) 

♀你的表很小,上文提到過的,Oracle的最佳化器認為不值得走索引。 

B、解決方法 

♀可以修改init<SID>.ora中的OPTIMIZER_MODE這個引數,把它改為Rule或Choose,重起資料庫。也可以使用4中所提的Hint. 

♀刪除統計資訊 

SQL>analyze table table_name delete statistics; 

♀表小不走索引是對的,不用調的。

5、其它相關

A、如何看一個表或索引是否是統計資訊

SQL>SELECT * FROM user_tables 

2 WHERE table_name=<table_name> 

3 AND num_rows is not null;

SQL>SELECT * FROM user_indexes 

2 WHERE table_name=<table_name> 

3 AND num_rows is not null;

b、如果我們先用CBO的方式,我們應及時去更新表和索引的統計資訊,以免生形不切合實的執行計劃。

SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; 

SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

具體的ANALYZE語句請參照Oracle8i/9i 的refrence文件。

12:42 PM | Add a comment | Permalink | Blog it | Oracle

oracle大資料量的匯入和匯出

在oracle中批次資料的匯出是藉助sqlplus的spool來實現的。批次資料的匯入是透過sqlload來實現的。

大量資料的匯出部分如下：

/***************************

* sql指令碼部分 demo.sql begin

**************************/

/**************************

* @author meconsea

* @date 20050413

* @msn meconsea@hotmail.com

* @Email meconsea@163.com

**************************/

//##--markup html：html格式輸出，預設為off

//##--autocommit：自動提交insert、update、delete帶來的記錄改變，預設為off

//##--define：識別命令中的變數字首符，預設為on，也就是'&'，碰到變數字首符，後面的字串作為變數處理.

set colsep''; //##--域輸出分隔符

set echo off; //##--顯示start啟動的指令碼中的每個sql命令，預設為on

set feedback off; //##--回顯本次sql命令處理的記錄條數，預設為on 

set heading off; //##--輸出域標題，預設為on

set pagesize 0; //##--輸出每頁行數，預設為24,為了避免分頁，可設定為0。

set linesize 80; //##--輸出一行字元個數，預設為80

set numwidth 12; //##--輸出number型別域長度，預設為10

set termout off; //##--顯示指令碼中的命令的執行結果，預設為on

set timing off; //##--顯示每條sql命令的耗時，預設為off

set trimout on; //##--去除標準輸出每行的拖尾空格，預設為off

set trimspool on; //##--去除重定向（spool）輸出每行的拖尾空格，預設為off

spool C:datadmczry.txt; 

select trim(czry_dm),trim(swjg_dm),trim(czry_mc) from dm_czry;

spool off;

EOF

/***********************

* demo.sql end

***********************/

在資料匯入的時候採用sqlload來呼叫，在該部分呼叫的時候用java來呼叫sqlload。

sqlload包括ctl控制檔案。例如：

/*********************

* meconsea ctl

********************/

load data

infile 'C:datadmczry.txt'

replace into table DM_CZRY

fields terminated by X'09'

(CZRY_DM,SWJG_DM,CZRY_MC)

/********************

* end

* 註釋：裡面的replace可以改為append

*******************/

java程式如下：

在java程式用可以根據需求寫成一個bat檔案。 把資料庫的配置和檔案的路徑寫到一個properties

檔案。

/*************************

* ide properties

************************/

Dserver=test/test@SJJZ

sqlldr=D:\oracle\ora92\bin\SQLLDR.EXE

ctldmczry=C:\data\ctl\dmczry.ctl

txtdmczry=C:\data\dmczry.txt

寫個PropertyBean.java來操作properties檔案。(偷懶不寫了！)

用DmCzry.java來把記錄匯入db中。部分程式碼如下：

/****************************

* 程式碼摘要

*

***************************/

..............

sqlldr = pb.getSqlldr();

txt = pb.getTxtdmczry();

ctl = pb.getCtldmczry();

Dserver= pb.getDserver();

 

Process processCmd = Runtime.getRuntime().exec(sqlldr+" "+cmdStr);

.............

12:42 PM | Add a comment | Permalink | Blog it | Oracle

在Oracle中檢視各個表、表空間佔用空間的大小

檢視當前使用者每個表佔用空間的大小：

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

檢視每個表空間佔用空間的大小：

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

12:36 PM | Add a comment | Permalink | Blog it | Oracle

July 22

Oracle 資料型別

Oracle 資料型別

 

資料型別

描述

VARCHAR2(size)

 

 

 

NVARCHAR2(size)

可變長度的字串，其最大長度為 size 個位元組。size 的最大值是 4000，而最小值是 1。您必須指定一個 VARCHAR2 的 size。

 

可變長度的字串，依據所選的國家字符集，其最大長度為 size 個字元或位元組。size 的最大值取決於儲存每個字元所需要的位元組數，其上限為 4000 個位元組。您必須為 NVARCHAR2 指定一個 size。

NUMBER(p,s)

精度為 p 並且數值範圍為 s 的數值。精度 p 的範圍是從 1 到 38。數值範圍 s 的範圍是從 -84 到 127。

LONG

可變長度的字元資料，其最大長度可達 2G 或 231 –1 個位元組。

DATE

有效日期範圍從公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。

RAW(size)

長度為 size 位元組的原始二進位制資料。size 的最大值為 2000 位元組。您必須為 RAW 值指定一個 size。

LONG RAW

可變長度的原始二進位制資料，其最大長度可達 2G 位元組。

CHAR(size)

 

 

NCHAR(size)

 

固定長度的字元資料，其長度為 size 位元組。size 的最大值為 2000 位元組。預設或最小的 size 是一個位元組。

 

固定長度的字元資料，其長度依據國家字符集的選擇為 size 個字元或位元組。size 的最大值取決於儲存每個字元所需要的位元組數，其上限為 2000 個位元組。預設或最小的 size 是一個字元或位元組，這取決於字符集。

CLOB

 

 

 

NCLOB

 

 

一個字元大型物件，可容納單位元組的字元。不支援寬度不等的字符集。最大大小為 4G 位元組。

 

一個字元大型物件，可容納固定寬度的多位元組字元。不支援寬度不等的字符集。最大大小為 4G 位元組。儲存國家字符集資料。

BLOB

一個二進位制大物件。最大大小為 4G 位元組。

BFILE

包含一個大型二進位制檔案的定位器，其儲存在資料庫的外面。使得可以以位元組流 I/O 訪問存在資料庫伺服器上的外部 LOB。最大大小為 4G 位元組。

 

 

 

 

 

3:57 PM | Add a comment | Permalink | Blog it | Oracle

July 19

ORACLE鎖的管理

 ORACLE裡鎖有以下幾種模式:

0：none

1：null      空 

2：Row-S     行共享(RS)：共享表鎖  

3：Row-X     行專用(RX)：用於行的修改

4：Share     共享鎖(S)：阻止其他DML操作

5：S/Row-X   共享行專用(SRX)：阻止其他事務操作

6：exclusive 專用(X)：獨立訪問使用

數字越大鎖級別越高, 影響的操作越多。

一般的查詢語句如select ... from ... ;是小於2的鎖, 有時會在v$locked_object出現。

select ... from ... for update;      是2的鎖。

當對話使用for update子串開啟一個遊標時，

所有返回集中的資料行都將處於行級(Row-X)獨佔式鎖定，

其他物件只能查詢這些資料行，不能進行update、delete或select...for update操作。

insert / update / delete ... ;      是3的鎖。 

沒有commit之前插入同樣的一條記錄會沒有反應, 

因為後一個3的鎖會一直等待上一個3的鎖, 我們必須釋放掉上一個才能繼續工作。

建立索引的時候也會產生3,4級別的鎖。

locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 

但DDL(alter,drop等)操作會提示ora-00054錯誤。

有主外來鍵約束時 update / delete ... ; 可能會產生4,5的鎖。

DDL語句時是6的鎖。

以DBA角色, 檢視當前資料庫裡鎖的情況可以用如下SQL語句：

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time 

from v$locked_object t1,v$session t2 

where t1.session_id=t2.sid order by t2.logon_time;

如果有長期出現的一列，可能是沒有釋放的鎖。

我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#';

如果出現了鎖的問題, 某個DML操作可能等待很久沒有反應。

當你採用的是直接連線資料庫的方式，

也不要用OS系統命令 $kill process_num 或者 $kill -9 process_num來終止使用者連線，

因為一個使用者程式可能產生一個以上的鎖, 殺OS程式並不能徹底清除鎖的問題。

記得在資料庫級別用alter system kill session 'sid,serial#';殺掉不正常的鎖。

5:42 PM | Add a comment | Permalink | Blog it | Oracle

July 18

利用STATSPAGK調整ORACLE效能

一、摘要

　　大部分DBA都利用資料緩衝區命中率，latch free wait time等指標來做資料庫效能調整。ORACLE提供的幾個簡單工具如STATSPACK（或以前版本的BSTAT/ESTAT）中包含了DBA 所需要的主要指標。但如何有效地利用這些資料調整效能？從那裡開始調整？本文使用YAPP效能最佳化方法，結合STATSPACK提供的資料，給出了應該採取的調整步驟。

二、YAPP效能最佳化方法

　　YAPP提供了另一種資料庫效能調整方法，它不使用命中率等指標來衡量資料庫的效能而是透過響應時間來衡量：

　　Response time = service time + wait time

　　即使用者面對的響應時間由服務時間和等待時間組成。服務時間是處理你的請求實際使用的CPU時間，等待時間即等待資源可用所花費的時間。例如如果執行一個需要查詢索引的SQL語句，CPU時間可能包括buffer cache中的索引資料塊的處理時間，掃描該資料塊找到所需行的時間等，此過程中ORACLE可能需要從盤上讀資料，此時可能出現磁碟等待。

　　YAPP方法的主要思路是找出service time，wait time的主要組成部分，然後進行排序並根據順序調整。因此在IO不是引起問題的原因時，你不會做出象‘資料緩衝區命中率太低,最好增加緩衝的結論’,在SQL語句的CPU處理時間為20分鐘時，你也不會做出‘必須將latchwait time減少20秒’的調整決定。另外用YAPP做最佳化時，你可以透過減少整個時間（如用更快的磁碟）或單位時間（如減少訪問磁碟次數）。因此我們稱YAPP為基於時間的調優方法，基本步驟如下：

　　（1）、得到服務時間和等待時間及其組成部分 

　　（2）、將所有組成部分排序 

　　（3）、依次最佳化每個部分 

　　（4）、對錶中的每一項,減少每次執行的代價或執行次數

　　STATSPACK或bstat/estat中的資料完全能滿足基於時間而不是基於命中率的最佳化方法。然而實際上要找出所有耗時的部分有些困難，在分析細節時service和wait本身並不精確，例如當你等待磁碟IO時，實際是從OS的緩衝中讀寫，實際上它是service （即CPU）時間,因此響應時間更好的表達為：

　　Response time = time compnent1+….+time componentn

　　使用者感知的響應時間由一系列時間成分組成，所謂效能最佳化就是最佳化最耗時的成分，依次類推。從ORACLE instance的角度，請求一般含三個部分：client （如SQLPLUS，TUXEDO），前臺程式（如LOCAL = NO的服務程式），後臺程式（如DBWR）。

三、ORACLE中的時間記錄

　　所有的ORACLE程式（前臺和後臺）都會將所使用的CPU時間（service time）和各種等待事件所費時間記錄下來，這些資訊記錄在SGA，使用者可透過V$▁檢視訪問這些資料。這種資料分為session級和system級，使用者可訪問V$SYSTEM▁EVENT和V$SYSSTAT等檢視來獲取這些資訊。ORACLE的STATSPACK工具（老版本中的BSTAT/ESTAT）就是查詢這些檢視來收集，計算和生成效能資料。要在ORACLE中產生時間記錄，DBA必須在INIT.ORA中將TIMED▁STATISTICS設定為TRUE或透過ALTERSYSTEM將其定義為TRUE。8i以前的版本中時間單位為1/100秒。9i以後時間單位為1/1,000,000秒(微秒)。本文主要面向system級的資料，但使用的方法適用於session級的資料。

　　在基於時間的最佳化方法中，最重要的檢視是V$SYSTEM▁EVENT，V$SYSSTAT，V$LATCH，V$SQLAREA。V$SYSSTAT記錄使用的CPU時間，V$SYSTEM▁EVENT記錄程式等待時間花費的間，V$SQLAREA能用於找出最耗資源的SQL語句，而V$LATCH則可用於各種LATCH的等待資訊。這些檢視的詳細結構和含義見Sewer Reference Mannual

四、利用STATSPACK最佳化效能

　　前一節所說的V$▁ 中記錄的資料都是系統啟動後的累加值，從某一個時間點看這些累加值沒有實際意義。只有每隔一段時間對這些累加值取樣，計算出抽樣之間的差別對最佳化才有價值。ORACLE的STATSPACK就是完成定期取樣的工作，一般可用ORACLE的JOB來自動完成定期取樣。資料收集完成後，DBA可以執行STATSPACK帶的SPREPORT生成某兩個取樣點之間的差別。STATSPACK生成的報告中含有各種資料，包括上述四個檢視中的資料。

　　1、從STATSPACK報告中找出晌應時間組成部分

　　基於時間的最佳化方法YAPP就是要找出最值得最佳化（最耗時）的成分。我們需找出前臺程式使用的sevice time及等待事件花費的時間，service time資訊可以從V$SYSSTAT中得到而事件等待花費的時間可從V$SYSTEM▁EVENT中得到。在STATSPACK報告中它們分別在 '''' Instance Activity Stats for DB '''' 和 '''' Wait Eventsfor DB '''' 一節中。尤其要注意三個時間成分：

　　CPU used by this session Total CPU time spent. 

　　Recursive cpu usage 

　　Time spent doing recursive work in the foreground .   

　　This includes data dictionary lookup and any PL /SQL work, including time spent 

by SQL inside 

　　PL/SQL parse time cpu 

　　CPU time spent parsing SQL statements

　　Recursive cpu usage和parse time cpu CPU是CPU used by this session的組成部分，除此之外的CPU時間我們一律定義為other CPU

　　下一步需找出wait time的組成部分，最簡單的方法就是找出 '''' Top5Wait Events '''' 下的5個等待事件，另一種方法即在 '''' Wait events for DB '''' 一節中找出最主要的事件。

　　下面是根據STATSPACK報告的資料，用基於YAPP方法的最佳化步驟：

　　（1）、找出parse time cpu所花費的時間 

　　（2）、找出CPU used by this session的值，減去parse time cpu，得出other CPU 

　　（3）、找出最耗時的等待事件 

　　（4）、將1—3的成分倒序排序，從第一項開始最佳化 

　　（5）、如果最耗時的等待事件不是latch free，見Tuning possibilities for wait events 

　　（6）、如果最耗時的等待事件是latch free，見Tuning possibilities for  latches 

　　（7）、如果最耗時的成分是與CPU有關的成分,見Tuning possibilities for CPU

　　2、Tuning possibilities for CPU

　　recursive cpu usage  如果處理大量的PLSQL此成分可能很高，本文不深入討論此問題產生的原因，但你需要找出你所有的PLSQL，包括儲存過程。找出CPU開銷最大的PLSQL過程並對其最佳化。如果PLSQL中的主要工作是完成過程處理而非執行SQL，高開銷的recursive cpu usage可能是需要最佳化的成分。

　　parse time cpu  分析（parsing）SQL是一個開銷很大的，它可以透過SQL語句的重用來避免。在預編譯程式中，可透過增加MAXOPENCURSORS引數減少這部分開銷。V$SQL的PARSE▁CALLS和EXECUTIONS可用來找出經常parse的語句。

　　Other cpu  其它CPU主要用於處理緩衝區中的緩衝。一般而言，SQL語句花費的CPU時間與訪問的緩衝區個數成比例，因此可以從V$SQL中的buffer gets得到SQL所防問的緩衝區個數，在STATSPACK中，可以檢視 '''' SQL ordered by Gets for DB ''''。應對清單中的SQL語句最佳化。在bstat /estat報告中沒有SQL語句，需定期查詢V$SQLAREA，找出buffer gets增加最快的語句。9i中V$SQL中含有CPU▁T|ME欄位,記錄語句所花費的時間。

　　3、Tuning possibilities for wait events

　　db file scattered read  當ORACLE全表掃描時,一次需讀多個資料塊，此時使用這一等待事件。i n i t .o r a中的db▁ file▁mutiblock▁read▁count定義了多資料塊讀取時，一次能讀取的最大塊數。一般此引數定義為4—16，與資料庫大小無關。但值越大DB▁BLOCK▁SlZE應越小。如果db file scattered read所佔比例較大,必須減少IO的代價（如使用更快的磁碟,均衡IO分佈），或減少全表掃描的次數（最佳化SQL語句）。參見下面IO最佳化。

　　db file sequential read  表示ORACLE順序讀資料塊，一般出現在讀索引。如果db file sequential read等待很長，必須減少IO的代價（如使用更快的磁碟，均衡IO分佈），或增加緩衝區。參見下面IO最佳化。

　　buffer busy waits  多個程式訪問（修改）緩衝區中同一資料塊時出現此等待事件。當表沒有free lists而對錶並行插入時，或回滾段個數太少時，會出現此事件，V$WA|TSTAT及 

STATSPACK報告可輔助找出原因。

　　latch free  見下節。

　　Enqueue  一般為應用程式使用的鎖，例如SEELECT ... FOR UPDATE。如果此部分佔用的時間較大，需分析應用系統，尤其是長時間佔有鎖資源的程式碼。要分析每個鎖的等待時間不太可能，雖然V$LOCK記錄了每種所等待的次數。

　　log file sync  任何時候一個事物提交時，它將通知LGWR將LOG▁BUFFER寫入日誌檔案，如果此部分佔用時間較長，應減少COMMIT的次數，此外應使用效能更好的IO系統，另一個相關的事件是'''' log buffer parallel write '''' ，也與IO系統或CPU資源太少有關。

　　free buffer wait   當一個SESSION需要空閒緩衝區但不能獲取時，出現此等待事件。它將通知DBWR將髒的緩衝區寫入資料檔案。需要確定是否需要最佳化IO系統或者增加DBWR的個數，如果此事件不是由於IO系統效能引起的，可考慮增加緩衝區。

　　rdbms ipc message  這些事件為空閒事件，一般應占主要的時間。''''

　　pmon timer

　　smon timer

　　SQL*Net message 

　　from client

　　Tuning possibilities for latches

　　shared pool   在parsing，尤其是hard parsing時。如果應用程式使用常量而不是BIND變數，可能會對此latch大量競爭，8.1.6以後可在init.ora中設定cursor▁sharing為force來減少hard parsing和對此latch的競爭，應用程式應保證只分析一次，執行多次。

　　library cache  在soft parsing和hard parsing時都會大量使用此latch，如有可能應修改應用程式，減少競爭。在init.ora中設定cursor▁sharing為force可減少soft parsing和hard parsing需要的library cache。此外定義session▁cached▁cursors也能減少同一session中soft parsing對library cache的競爭。此外還可以定義cursor▁space▁for▁time=true。

　　mw cache  row cache保護字典資訊，如表和列的資訊。hard parsing需要row cache 。在init.ora中設定cursor▁sharing為force可減少競爭。

　　cache buffer chain   保護緩衝區的hash chain，用於對緩衝區的每次訪問。一般可透過減少訪問緩衝區次數來減少對l|atch的競爭。利用X$BH可以找出某些hash chain是否有許多的緩衝區，經常會有熱塊（如root index block）可能引起競爭。

　　cache buffer lru chain  資料緩衝一組LRU塊組成的鏈。每一個由一個cache buffer lru  chain 保護透過增加db▁ block▁lru▁latches可減少競爭。

　　4、Tuning possibilities for I/O 

如果db file scattered/sequential read等直接的事件或file write（DBWR/LGWR）等非直接事件佔用的時間比例較大，需要檢查IO的效率。STATSPACK報告中有一節為 ''''Tablespace IO Summary for DB'''' 其中列出了表空間名稱和它們的lO rate。另一節'''' file IO Statistics for DB''''列出了每個資料檔案和它們的IO rate。首先應檢查IO rate是否在期望的範圍，其次應檢查IO分佈。如果IO rate 在可接受的範圍（帶cache的檔案2—10ms或裸裝置的每次IO 5—20ms）而且所有的資料檔案IO  rate相似，那麼可以肯定IO系統的效能符合要求。這種情況下減少每次IO代價沒有必要，應該減少IO的次數（增加緩衝區或最佳化SQL）。然而如果IO rate大大超出合理範圍或分佈不合理，你需要重組IO子系統，如使用更多的磁碟驅動器，修改結構（如不使用RAID5），或IO重新分佈。

五、值得注意的地方

　　雖然ORACLE的統計和等待事件可以為你找出系統瓶頸提供了很好的資料，但有些情況這些資料可能會誤導使用者：

　　·ORACLE 8i以前的版本時間單位是1/100秒，但在某些特別|的系統中其精度不夠。因而某些發生過的事件可能沒有記錄，而某些發生時間並不很長的事件記錄的時間要比實際的時間長。這個問題在9i中不會出現（計量單位是1/1,000,000秒）。

　　·ORACLE前臺程式花費的CPU時間記錄比較粗糙，CPU used by this session遠遠大於實際使用配的時間，唯一能做的估計是所用CPU時間與所訪問的緩衝區個數成比例，但在執行大的PLSQL，複雜的表示式，表連線時這種估計是不精確的。一般而言，這類估計在OLTP型別的應用系統是有效的，對DSS系統這種估計是不精確的。

　　·V$SYSSTAT檢視包含前臺和後臺程式時間的總和，然而CPU時間成分中只有前臺程式所用的時間值得注意，某些後臺程式（尤其是DBWR，LGWR）使用了大量的CPU，導致前臺程式統計配的不精確。

　　·某些時間沒有計算。如SQL*NET的時間，但它影響響應時間。

　　·YAPP中只考慮了ORACLE前臺程式使用的時間，如果ORACLE所用的時間只佔響應時間的很小部分，最佳化ORACLE不會帶來任何效能改進。

六、IBSTAT/ESTAT的使用

　　STATSPACK只有8.1.6以後的版本才有，如果使用的老的版本只有BSTAT/ESTAT，兩者的主要區別是：

　　·BSTAT/ESAT由DBA直接手工執行而不透過ORACLE JOB自動執行，每執行一次只收集一個時間間隔的資料。

　　·BSTAT/ESAT沒有SQL語句的資訊，如果OTHER CPU是開銷最大的成分，需要查詢V$SQL找出最耗資源的SQL。

　　·沒有TOP5 WAIT EVENT,需查詢檢視找出最耗時的事件。

[關閉視窗]

1:41 PM | Add a comment | Permalink | Blog it | Oracle

Oracle效能調優實踐中的幾點心得

很多的時侯，做Oracle DBA的我們，當應用管理員向我們通告現在應用很慢、資料庫很慢的時侯，我們到資料庫時做幾個示例的Select也發現同樣的問題時，有些時侯我們會無從下手，因為我們認為資料庫的各種命種率都是滿足Oracle文件的建議。實際上如今的最佳化己經向最佳化等待(waits)轉型了，實際中效能最佳化最根本的出現點也都集中在IO，這是影響效能最主要的方面，由系統中的等待去發現Oracle庫中的不足、作業系統某些資源利用的不合理是一個比較好的辦法，下面把我的一點實踐經驗與大家分享一下，本文測重於Unix環境。

一、透過作業系統的一些工具檢查系統的狀態，比如CPU、記憶體、交換、磁碟的利用率，根據經驗或與系統正常時的狀態相比對，有時系統表面上看起來看空閒這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外我們還應觀注那些佔用系統資源(cpu、記憶體)的程式。

1、如何檢查作業系統是否存在IO的問題？使用的工具有sar,這是一個比較通用的工具。

  Rp1#Sar -u 2 10

  即每隔2秒檢察一次，共執行20次，當然這些都由你決定了。

  示例返回：

  HP-UX hpn2 B.11.00 U 9000/800    08/05/03

  18:26:32    %usr    %sys    %wio   %idle

  18:26:34      80       9      12       0

  18:26:36      78      11      11       0

  18:26:38      78       9      13       1

  18:26:40      81      10       9       1

  18:26:42      75      10      14       0

  18:26:44      76       8      15       0

  18:26:46      80       9      10       1

  18:26:48      78      11      11       0

  18:26:50      79      10      10       0

  18:26:52      81      10       9       0

  Average       79      10      11       0

    其中的%usr指的是使用者程式使用的cpu資源的百分比，%sys指的是系統資源使用cpu資源的百分比，%wio指的是等待io完成的百分比，這是值得我們觀注的一項，%idle即空閒的百分比。如果wio列的值很大，如在35%以上，說明你的系統的IO存在瓶頸，你的CPU花費了很大的時間去等待IO的完成。Idle很小說明系統CPU很忙。像我的這個示例，可以看到wio平均值為11說明io沒什麼特別的問題，而我的idle值為零，說明我的cpu已經滿負荷執行了。

當你的系統存在IO的問題，可以從以下幾個方面解決

  ♀聯絡相應的作業系統的技術支援對這方面進行最佳化，比如hp-ux在劃定卷組時的條帶化等方面。

  ♀查詢Oracle中不合理的sql語句，對其進行最佳化

  ♀對Oracle中訪問量頻繁的表除合理建索引外，再就是把這些表分表空間存放以免訪問上產生熱點，再有就是對錶合理分割槽。

2、關注一下記憶體。

    常用的工具便是vmstat，對於hp-unix來說可以用glance,Aix來說可以用topas,當你發現vmstat中pi列非零，memory中的free列的值很小，glance,topas中記憶體的利用率多於80%時，這時說明你的記憶體方面應該調節一下了，方法大體有以下幾項。

  ♀劃給Oracle使用的記憶體不要超過系統記憶體的1/2,一般保在系統記憶體的40%為益。

  ♀為系統增加記憶體

  ♀如果你的連線特別多，可以使用MTS的方式

  ♀打全補丁，防止記憶體漏洞。

3、如何找到點用系用資源特別大的Oracle的session及其執行的語句。

Hp-unix可以用glance,top

IBM AIX可以用topas

些外可以使用ps的命令。

透過這些程式我們可以找到點用系統資源特別大的這些程式的程式號，我們就可以透過以下的sql語句發現這個pid正在執行哪個sql，這個sql最好在pl/sql developer,toad等軟體中執行, 把<>中的spid換成你的spid就可以了。

SELECT a.username,

       a.machine,

       a.program,

       a.sid,

       a.serial#,

       a.status,

       c.piece,

       c.sql_text

  FROM v$session a,

       v$process b,

       v$sqltext c

 WHERE b.spid=<spid>  

   AND b.addr=a.paddr

   AND a.sql_address=c.address(+)

 ORDER BY c.piece    

   我們就可以把得到的這個sql分析一下，看一下它的執行計劃是否走索引，對其最佳化避免全表掃描，以減少IO等待，從而加快語句的執行速度。

提示：我在做最佳化sql時，經常碰到使用in的語句，這時我們一定要用exists把它給換掉，因為Oracle在處理In時是按Or的方式做的，即使使用了索引也會很慢。

比如：

SELECT  col1,col2,col3 FROM table1 a 

 WHERE a.col1 not in (SELECT  col1 FROM table2)

       可以換成：

SELECT  col1,col2,col3 FROM table1 a 

 WHERE not exists

 (SELECT  'x'  FROM table2 b

WHERE  a.col1=b.col1)

4、另一個有用的指令碼：查詢前十條效能差的sql.

 SELECT * FROM 

  (

   SELECT PARSING_USER_ID

          EXECUTIONS,

          SORTS,

          COMMAND_TYPE,

          DISK_READS,

          sql_text

      FROM  v$sqlarea

     ORDER BY disk_reads DESC 

   )  

  WHERE ROWNUM<10 ;

 

二、迅速發現Oracle Server的效能問題的成因，我們可以求助於v$session_wait這個檢視，看系統的這些session在等什麼，使用了多少的IO。以下是我提供的參考指令碼：

指令碼說明：檢視佔io較大的正在執行的session

 SELECT se.sid,

       se.serial#,

       pr.SPID,

       se.username,

       se.status,

       se.terminal,

       se.program,

       se.MODULE,

       se.sql_address,

       st.event,

       st.p1text,

       si.physical_reads,

       si.block_changes 

  FROM v$session se,

       v$session_wait st,

       v$sess_io si,

       v$process pr

 WHERE st.sid=se.sid 

   AND st.sid=si.sid

   AND se.PADDR=pr.ADDR

   AND se.sid>6

   AND st.wait_time=0 

   AND st.event NOT LIKE '%SQL%'

 ORDER BY physical_reads DESC

對檢索出的結果的幾點說明：

1、我是按每個正在等待的session已經發生的物理讀排的序，因為它與實際的IO相關。

2、你可以看一下這些等待的程式都在忙什麼，語句是否合理？

  Select sql_address from v$session where sid=<sid>;

  Select * from v$sqltext where address=<sql_address>;

執行以上兩個語句便可以得到這個session的語句。

你也以用alter system kill session 'sid,serial#';把這個session殺掉。

3、應觀注一下event這列，這是我們調優的關鍵一列，下面對常出現的event做以簡要的說明：

a、buffer busy waits，free buffer waits這兩個引數所標識是dbwr是否夠用的問題，與IO很大相關的，當v$session_wait中的free buffer wait的條目很小或沒有的時侯，說明你的系統的dbwr程式決對夠用，不用調整；free buffer wait的條目很多，你的系統感覺起來一定很慢，這時說明你的dbwr已經不夠用了，它產生的wio已經成為你的資料庫效能的瓶頸，這時的解決辦法如下：

a.1增加寫程式，同時要調整db_block_lru_latches引數

示例：修改或新增如下兩個引數

  db_writer_processes=4

  db_block_lru_latches=8

a.2開非同步IO，IBM這方面簡單得多，hp則麻煩一些，可以與Hp工程師聯絡。

b、db file sequential read，指的是順序讀，即全表掃描，這也是我們應該儘量減少的部分，解決方法就是使用索引、sql調優，同時可以增大db_file_multiblock_read_count這個引數。

c、db file scattered read,這個引數指的是透過索引來讀取，同樣可以透過增加db_file_multiblock_read_count這個引數來提高效能。

d、latch free,與栓相關的了，需要專門調節。

e、其他引數可以不特別觀注。

 

 

SMON: Parallel transaction recovery tried 引發的問題

 

 SMON: Parallel transaction recovery tried 這個一般是在具有在跑大資料量的 transaction的時候kill掉了程式而導致 smon 去清理 回滾段時導致的。

這個在業務高峰期的時候，如果發現這個，有可能導致 SMON 佔用了 100% cpu 而導致 系統 hang 在那邊。

即使你shutdown immediate ，oracle 也會等待 smon 清理完畢才能關機，而這個等待過程也許是漫長的。

如果你 shutdown abort，那麼oracle會馬上shutdown ，但是，當你startup的時候，有可能就會很慢，因為 smon 會接著清理 undo，這個等待過程也許是很漫長的：

— — — —————————————————————————————————— 

Completed: ALTER DATABASE   MOUNT

Thu Aug 26 22:43:57 2010

ALTER DATABASE OPEN

Thu Aug 26 22:43:57 2010 

Beginning crash recovery of 1 threads

Thu Aug 26 22:43:57 2010 

Started first pass scan

Thu Aug 26 22:43:57 2010

Completed first pass scan

 402218 redo blocks read, 126103 data blocks need recovery

Thu Aug 26 22:45:05 2010

Restarting dead background process QMN0

QMN0 started with pid=16

Thu Aug 26 22:45:19 2010

Started recovery at

 Thread 1: logseq 13392, block 381202, scn 0.0

Recovery of Online Redo Log: Thread 1 Group 3 Seq 13392 Reading mem 0

  Mem# 0 errs 0: /zxindata/oracle/redolog/redo03.dbf

Recovery of Online Redo Log: Thread 1 Group 1 Seq 13393 Reading mem 0

  Mem# 0 errs 0: /zxindata/oracle/redolog/redo01.dbf

Thu Aug 26 22:45:21 2010

Completed redo application 

Thu Aug 26 22:48:35 2010

Ended recovery at

 Thread 1: logseq 13393, block 271434, scn 2623.1377219707

 126103 data blocks read, 115641 data blocks written, 402218 redo blocks read

Crash recovery completed successfully 

________________________________________________

看紅色標註的那個，等待了 3 分鐘才做完 recovery。

那如何才能讓它快呢，metalink（238507.1 ） 有給出一些做法：

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

1. Find SMON's Oracle PID:

Example:

SQL> select pid, program from v$process where program like '%SMON%';

       PID PROGRAM

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

         6 oracle@stsun7 (SMON) 

2. Disable SMON transaction cleanup:

SVRMGR> oradebug setorapid <SMON's Oracle PID>

SVRMGR> oradebug event 10513 trace name context forever, level 2 

3. Kill the PQ slaves that are doing parallel transaction recovery. 

You can check V$FAST_START_SERVERS to find these.

4. Turn off fast_start_parallel_rollback:

alter system set fast_start_parallel_rollback=false; 

If SMON is recovering, this command might hang, if it does just control-C out of it.  You may need to try this many times to get this to complete (between SMON cycles).

5. Re-enable SMON txn recovery:

SVRMGR> oradebug setorapid <SMON's Oracle PID>

SVRMGR> oradebug event 10513 trace name context off 

——————————————————————————————————

以上的思路主要是要把 SMON 並行 recovery 的功能給改成非並行，主要

是 fast_start_parallel_rollback 這個引數的作用。

There are cases where parallel transaction recovery is not as fast as serial transaction recovery, because the pq slaves are interfering with each other. This depends mainly on the  type of changes that need to be made during rollback and usually may happen when rolling back INDEX Updates in parallel. 

 

參考至：%CC%D8%B0%AE%C0%B6%C1%AB%BB%A8/blog/item/9af29302b820fab22eddd439.html

如有錯誤，歡迎指正

 

Kill session

 alter system kill session 'sid,serial#' ;

被kill掉的session,狀態會被標記為killed,Oracle會在該使用者下一次touch時清除該程式.

我們發現當一個session被kill掉以後，該session的paddr被修改，如果有多個session被kill，那麼多個session

的paddr都被更改為相同的程式地址

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR           SID    SERIAL# PADDR    USERNAME                       STATUS

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

542E0E6C         11        314 542B70E8 EYGLE                          INACTIVE

542E5044         18        662 542B6D38 SYS                            ACTIVE

 

 

SQL> alter system kill session '11,314';

 

System altered.

 

增加表空間大小的四種方法

Meathod1：給表空間增加資料檔案

ALTER TABLESPACE app_data ADD DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

 

Meathod2：新增資料檔案，並且允許資料檔案自動增長

ALTER TABLESPACE app_data ADD DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

 

Meathod3：允許已存在的資料檔案自動增長

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

 

Meathod4：手工改變已存在資料檔案的大小

ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'

RESIZE 100M;

自動新增資料檔案

set serveroutput on;

declare

Tablespacename Varchar(500);

  temp_file_name Varchar(500);

 file_name Varchar(500);

 free           Number(10,2);

 rate           Number(10,2);

 total           Number(10,2);

 used           Number(10,2);

 Vs_Sql         Varchar2(500);

 Data_File_No   Number(4);

 cursor cur_t  is

  select a.tablespace_name tablespace_name,a.total as total,a.total-a.free_space as used,(a.total-a.free_space)/a.total as rate, b.file_name file_name from (

  select f.tablespace_name tablespace_name,sum(f.bytes)/(1024*1024) as free_space,sum(d.bytes)/(1024*1024) as total

from dba_free_space f,dba_data_files  d

 where f.tablespace_name=d.tablespace_name and f.tablespace_name like '%GPS%' group by f.tablespace_name) a,(select max(file_name) file_name,tablespace_name from dba_data_files group by tablespace_name ) b where a.tablespace_name=b.tablespace_name;

 

 tablespace_info cur_t%rowtype;

 

Begin

  for tablespace_info in cur_t  loop

           rate:=tablespace_info.rate;

           total:=tablespace_info.total;

           used:=tablespace_info.used;

           Tablespacename:=tablespace_info.tablespace_name;

           file_name:=tablespace_info.file_name;

         

            If rate >=80 Then

            

               dbms_output.put_line(Tablespacename||' '||file_name||' '||rate);

            

            temp_file_name:=file_name;

            temp_file_name:=substr(file_name,0,INSTR (file_name, '.')-1);

            temp_file_name:=temp_file_name||'_'||to_char(sysdate,'yyyymmddhh24miss')||'.dbf';

             

        Vs_Sql := 'alter tablespace '||Tablespacename||' add datafile '''||temp_file_name||''' size 200M autoextend on next 100m maxsize UNLIMITED ';

         dbms_output.put_line(Vs_Sql);

        --alter中的路徑需要根據本地資料庫的安裝路徑修改 

        Execute Immediate Vs_Sql;

       End If;

  end loop;

End ;

 

修改索引表空間

  select 'alter index ' ||index_name|| ' rebuild tablespace FLTIDX;' index_name from  dba_indexes where owner like '%FLT%' and table_owner='FLT' and tablespace_name='FLT' and index_name  like '%PK%';

  

  select index_name,table_owner,tablespace_name from dba_indexes where owner like '%FLT%' and table_owner='FLT' and tablespace_name='FLTIDX' ;

  

   select index_name,table_owner,tablespace_name from dba_indexes where owner like '%FLT%' and table_owner='FLT' and tablespace_name='FLT' and index_name not like '%PK%';

  

  

  select * from  dba_indexes where owner like '%FLT%' and table_owner='FLT';

  alter index ANSWER_PK rebuild tablespace FLTIDX;

刪除重複資料

delete from 表名 a 

　　where a.rowid != 

　　( 

　　select max(b.rowid) from 表名 b 

　　where a.欄位1 = b.欄位1 and 

　　a.欄位2 = b.欄位2 

)

 

 

 

查詢session的psid

select a.sid, a.SERIAL#, b.spid, a.status, a.PROGRAM

from v$session a, V$PROCESS b

where a.sid in (29, 30, 32, 77, 120, 144, 151)

and a.paddr=b.ADDR

order by a.sid;

清除歸檔日誌

rman target/ 

或rman target/@orcl 

在命令視窗裡面執行 

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; 

魏健康 09:41:17

ALTER DATABASE ARCHIVELOG;

魏健康 09:41:31

ALTER DATABASE NOARCHIVELOG;

魏健康 09:41:37

 ARCHIVE  LOG  LIST

 

RAC 啟動和停止

[oracle@node1 ~]$ crs_stat 

[oracle@node1 ~]$ crs_start -all 

[oracle@node1 ~]$ crs_stop -all 

[oracle@node1 ~]$ crs_stop "ora.fyweb.db" 

[oracle@node1 ~]$ crs_start "ora.fyweb.db" 

刪除表空間，表分割槽

alter table ALARM_REALTIME drop partition flt_data_part_201301;

drop tablespace test_data including contents and datafiles;

 

查詢oracle程式現在執行的sql

 

SELECT   /*+ ORDERED */

         sql_text

    FROM v$sqltext a

   WHERE (a.hash_value, a.address) IN (

            SELECT DECODE (sql_hash_value,

                           0, prev_hash_value,

                           sql_hash_value

                          ),

                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

              FROM v$session b

             WHERE b.paddr = (SELECT addr

                                FROM v$process c

                               WHERE c.spid = 5396))

ORDER BY piece ASC;