搜尋此網誌

2011年4月29日 星期五

Oracle Table大小查詢

取得每一個table所使用實體空間大小
在執行此query前
1.須先確定db_block_size大小
select value from v$parameter where name='db_block_size'
Oracle有關V$Parameter的說明
2.update所以資料表的統計值

ANALYZE TABLE TABLENAME COMPUTE STATISTICS

Oracle有關Analyze的說明


oracle有關all_tables的說明

select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,BLOCKS*8096 as blockusebyte,AVG_ROW_LEN,AVG_ROW_LEN*NUM_ROWS as rowusebyte
from all_tables
where owner<>'SYS' and owner<>'SYSTEM'
 and blocks is not null
order by blocks desc




2011年4月27日 星期三

查詢tablespace使用空間

可以取得每個table space目前使用空間資訊
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name

Oracle 版本查詢

SELECT BANNER FROM SYS.V_$VERSION

2011年4月21日 星期四

Oracle Hint

研究了很久在SQL中增加/*ordered */
會使查詢效能顯著提升
原來這叫做Oracle Hint


常見Oracle HINT的用法:
1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計資料,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計資料,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;
5. /*+FULL(TABLE)*/
表明對表選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;
6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;
7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;
10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過hash連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.
27. /*+CACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

2011年4月14日 星期四

Oracle Lock機制

Oracle的鎖機制歸納總結

2010-06-09 作者:wallimn 來源:wallimnblog

鎖是防止在兩個事務操作同一個資料源(表或行)時交互破壞資料的一種機制。Oracle採用封鎖技術保證併發操作的可串列性。Oracle的鎖分為兩大類:資料鎖(也稱DML鎖)和字典鎖。字典鎖是Oracle DBMS內部用於對字典表的封鎖。字典鎖包括語法分析鎖和DDL鎖,由DBMS在必要的時候自動加鎖和釋放鎖,用戶無機控制。
Oracle主要提供了5種資料鎖:共用鎖(Share Table Lock,簡稱S鎖)、排它鎖(Exclusive Table Lock,簡稱X鎖)、行級鎖(Row Share Table Lock,簡稱RS鎖)、行級排它鎖(Row Exclusive Table Lock,簡稱RX鎖)和共用行級排它鎖(Share Row Exclusive Table Lock,簡稱SRX鎖)。其封鎖粒度包括行級和表級。
1. 共用鎖(Share Table LockS):
加鎖語法:Lock Table TableName In Share Mode;
允許的操作:一個共用鎖由一個事務控制,僅允許其他事務查詢被鎖定的表。一個有效的共用鎖明確地用Select For update形式鎖定行,或執行Lock Table TableName In Share Mode語法鎖定整個表,不允許被其他事務更新。允許多個事務在同一個表上加共用鎖,這種情況下不允許在該表上加鎖的事務更新表(即使有一個事務控制的是形如Select Row for update這樣行鎖也是不被允許的)。因此,僅有一個事務的一個共用鎖可以更新該表如果其他事務也有相同的事務在該表上的話。
禁止的操作:一個共用鎖由一個事務來控制,防止其他事務更新該表或執行下面的語句:
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
2. 排它鎖(Exclusive Table LockX):
排它鎖是在鎖機制中限制最多的一種鎖類型,允許加排它鎖的事務獨自控制對表的寫許可權。
加鎖語法:Lock Table TableName In Exclusive Mode;
允許的操作:在一個表中只能有一個事務對該表實行排它鎖,排它鎖僅允許其他的事務查詢該表。
禁止的操作:擁有排外鎖的事務禁止其他事務執行其他任何DML類型的語句或在該表上加任何其他類型的鎖。
定義排它鎖的語法:
LOCK TABLE TableName IN EXCLUSIVE MODE;
3. 行級鎖(Row Share Table LockRS):
一個行級鎖(有時稱為Subshare Table Lock,簡稱SS,子共用鎖)需要該事務在被鎖定行的表上用update的形式加鎖。當有下面語句被執行的時候行級鎖自動加在操作的表上。
SELECT . . . FROM TableName. . . FOR UPDATE OF . . . ;
LOCK TABLE TableName IN ROW SHARE MODE;
行級鎖(Row Share Table Lock)在鎖類型中是限制最少的,也是在表的併發程度中使用程度最高的。
允許的操作:行級共用鎖由一個事務控制,允許其他事務查詢、插入、更新、刪除或同時在同一張表上鎖定行。因此其他事務可以同時在同一張表上得到行級鎖、共用行級排它鎖、行級排它鎖、排它鎖。參見數據鎖的相容矩陣圖
禁止的操作:擁有行級鎖的事務不允許其他事務執行排它鎖,即:
Lock Table TableName In Exclusive Mode;
4. 行級排它鎖(Row Exclusive Table LockRX):
行級排它鎖(亦稱為Subexclusive Table Lock,簡稱SX,子排它鎖)通常需要事務擁有的鎖在表上被更新一行或多行。當有下面語句被執行的時候行級排它鎖被加在操作的表上。
INSERT INTO TableName. . . ;
UPDATE TableName. . . ;
DELETE FROM TableName. . . ;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
行級排它鎖比行級鎖稍微多一些限制。
允許的操作:在資料鎖的相容矩陣圖很容易看出行級排它鎖由一個事務擁有允許其他事務執行查詢、修改、插入、刪除或同時在同一張表上鎖定行。執有行級排它鎖的事務允許其他事務在同一張表上同時得到共用鎖和行級排它鎖。
禁止的操作:行級排它鎖由一個事務擁有防止其他事務手動鎖定表來排除其他事務的讀寫權。因此,其他事務不允許在同一張表上使用以下的語句來執行鎖事務。
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE
5. 共用行級排它鎖(Share Row Exclusive Table LockSRX):
共用行級排它鎖有時也稱共用子排它鎖(Share Subexclusive Table LockSSX),它比共用鎖有更多限制。定義共用行級排它鎖的語法為:
Lock Table TableName In Share Row Exclusive Mode;
允許的操作:僅允許一個事務在某一時刻得到行級排它鎖。擁有行級排它鎖事務允許其他事務在被鎖定的表上執行查詢或使用Select From TableName For update…來準確在鎖定行而不能更新行。
禁止的操作:擁有行級排它鎖的事務不允許其他事務有除共用鎖外的其他形式的鎖加在同一張表上或更新該表。即下麵的語句是不被允許的:
LOCK TABLE TableName IN SHARE MODE;
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN EXCLUSIVE MODE;
DML語句獲得的DML自動鎖(Oracle隱含鎖)
DML自動鎖的相容矩陣圖:
X:排它鎖、RS:行級共用鎖、RX:行級排它鎖、S、共用鎖、RSX:行級共用排它鎖
DML語句 有行級鎖 鎖類型
SELECT ... FROM table
INSERT INTO table ... X RX
UPDATE table ... X RX
DELETE FROM table ... X RX
SELECT ... FROM table ... FOR UPDATE OF ... X RS
LOCK TABLE table IN ...
ROW SHARE MODE RS
ROW EXCLUSIVE MODE RX
SHARE MODE S
SHARE EXCLUSIVE MODE SRX
EXCLUSIVE MODE X
資料字典中與鎖有關的表或視圖
V_$LOCKED_OBJECT
V_$LOCKS_WITH_COLLISIONS
V_$LOCK_ACTIVITY
V_$LOCK_ELEMENT
V_$_LOCK
DBMS_LOCK
V$DLM_ALL_LOCKS
V$DLM_LOCKS
V$ENQUEUE_LOCK
V$GLOBAL_BLOCKED_LOCKS
V$LOCK
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$_LOCK
解鎖及Kill Session:
使用下面的語法查出鎖並殺掉Session
SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

2011年4月13日 星期三

Lock 監視

取得目前session狀態,及是否lock優化版
速度快很多
select  /*+ ordered */ <==加上這個會快很多
   c.sid,
        c.serial#,
        b.ctime,
   lock_waiter.waiting_session,
   lock_blocker.holding_session,
   c.program,
   c.osuser,
   c.machine,
   c.process,
   decode(u.name,
      null,'',
      u.name||'.'||o.name
   ) object,
   c.username,
   decode
   (
      b.type,
      'BL', 'Buffer hash table instance lock',
      'CF', 'Control file schema global enqueue lock',
      'CI', 'Cross-instance function invocation instance lock',
      'CU', 'Cursor bind lock',
      'DF', 'Data file instance lock',
      'DL', 'direct loader parallel index create lock',
      'DM', 'Mount/startup db primary/secondary instance lock',
      'DR', 'Distributed recovery process lock',
      'DX', 'Distributed transaction entry lock',
      'FS', 'File set lock',
      'IN', 'Instance number lock',
      'IR', 'Instance recovery serialization global enqueue lock',
      'IS', 'Instance state lock',
      'IV', 'Library cache invalidation instance lock',
      'JQ', 'Job queue lock',
      'KK', 'Thread kick lock',
      'LA','Library cache lock instance lock (A..P=namespace);',
      'LB','Library cache lock instance lock (A..P=namespace);',
      'LC','Library cache lock instance lock (A..P=namespace);',
      'LD','Library cache lock instance lock (A..P=namespace);',
      'LE','Library cache lock instance lock (A..P=namespace);',
      'LF','Library cache lock instance lock (A..P=namespace);',
      'LG','Library cache lock instance lock (A..P=namespace);',
      'LH','Library cache lock instance lock (A..P=namespace);',
      'LI','Library cache lock instance lock (A..P=namespace);',
      'LJ','Library cache lock instance lock (A..P=namespace);',
      'LK','Library cache lock instance lock (A..P=namespace);',
      'LL','Library cache lock instance lock (A..P=namespace);',
      'LM','Library cache lock instance lock (A..P=namespace);',
      'LN','Library cache lock instance lock (A..P=namespace);',
      'LO','Library cache lock instance lock (A..P=namespace);',
      'LP','Library cache lock instance lock (A..P=namespace);',
      'MM', 'Mount definition global enqueue lock',
      'MR', 'Media recovery lock',
      'NA', 'Library cache pin instance lock (A..Z=namespace)',
      'NB', 'Library cache pin instance lock (A..Z=namespace)',
      'NC', 'Library cache pin instance lock (A..Z=namespace)',
      'ND', 'Library cache pin instance lock (A..Z=namespace)',
      'NE', 'Library cache pin instance lock (A..Z=namespace)',
      'NF', 'Library cache pin instance lock (A..Z=namespace)',
      'NG', 'Library cache pin instance lock (A..Z=namespace)',
      'NH', 'Library cache pin instance lock (A..Z=namespace)',
      'NI', 'Library cache pin instance lock (A..Z=namespace)',
      'NJ', 'Library cache pin instance lock (A..Z=namespace)',
      'NK', 'Library cache pin instance lock (A..Z=namespace)',
      'NL', 'Library cache pin instance lock (A..Z=namespace)',
      'NM', 'Library cache pin instance lock (A..Z=namespace)',
      'NN', 'Library cache pin instance lock (A..Z=namespace)',
      'NO', 'Library cache pin instance lock (A..Z=namespace)',
      'NP', 'Library cache pin instance lock (A..Z=namespace)',
      'NQ', 'Library cache pin instance lock (A..Z=namespace)',
      'NR', 'Library cache pin instance lock (A..Z=namespace)',
      'NS', 'Library cache pin instance lock (A..Z=namespace)',
      'NT', 'Library cache pin instance lock (A..Z=namespace)',
      'NU', 'Library cache pin instance lock (A..Z=namespace)',
      'NV', 'Library cache pin instance lock (A..Z=namespace)',
      'NW', 'Library cache pin instance lock (A..Z=namespace)',
      'NX', 'Library cache pin instance lock (A..Z=namespace)',
      'NY', 'Library cache pin instance lock (A..Z=namespace)',
      'NZ', 'Library cache pin instance lock (A..Z=namespace)',
      'PF', 'Password File lock',
      'PI', 'Parallel operation locks',
      'PS', 'Parallel operation locks',
      'PR', 'Process startup lock',
      'QA','Row cache instance lock (A..Z=cache)',
      'QB','Row cache instance lock (A..Z=cache)',
      'QC','Row cache instance lock (A..Z=cache)',
      'QD','Row cache instance lock (A..Z=cache)',
      'QE','Row cache instance lock (A..Z=cache)',
      'QF','Row cache instance lock (A..Z=cache)',
      'QG','Row cache instance lock (A..Z=cache)',
      'QH','Row cache instance lock (A..Z=cache)',
      'QI','Row cache instance lock (A..Z=cache)',
      'QJ','Row cache instance lock (A..Z=cache)',
      'QK','Row cache instance lock (A..Z=cache)',
      'QL','Row cache instance lock (A..Z=cache)',
      'QM','Row cache instance lock (A..Z=cache)',
      'QN','Row cache instance lock (A..Z=cache)',
      'QP','Row cache instance lock (A..Z=cache)',
      'QQ','Row cache instance lock (A..Z=cache)',
      'QR','Row cache instance lock (A..Z=cache)',
      'QS','Row cache instance lock (A..Z=cache)',
      'QT','Row cache instance lock (A..Z=cache)',
      'QU','Row cache instance lock (A..Z=cache)',
      'QV','Row cache instance lock (A..Z=cache)',
      'QW','Row cache instance lock (A..Z=cache)',
      'QX','Row cache instance lock (A..Z=cache)',
      'QY','Row cache instance lock (A..Z=cache)',
      'QZ','Row cache instance lock (A..Z=cache)',
      'RT', 'Redo thread global enqueue lock',
      'SC', 'System commit number instance lock',
      'SM', 'SMON lock',
      'SN', 'Sequence number instance lock',
      'SQ', 'Sequence number enqueue lock',
      'SS', 'Sort segment locks',
      'ST', 'Space transaction enqueue lock',
      'SV', 'Sequence number value lock',
      'TA', 'Generic enqueue lock',
      'TS', 'Temporary segment enqueue lock (ID2=0)',
      'TS', 'New block allocation enqueue lock (ID2=1)',
      'TT', 'Temporary table enqueue lock',
      'UN', 'User name lock',
      'US', 'Undo segment DDL lock',
      'WL', 'Being-written redo log instance lock',
      b.type
   ) lock_type,
   decode
   (
      b.lmode,
      0, 'None',           /* Mon Lock equivalent */
      1, 'Null',           /* N */
      2, 'Row-S (SS)',     /* L */
      3, 'Row-X (SX)',     /* R */
      4, 'Share',          /* S */
      5, 'S/Row-X (SRX)',  /* C */
      6, 'Exclusive',      /* X */
      to_char(b.lmode)
   ) mode_held,
   decode
   (
      b.request,
      0, 'None',           /* Mon Lock equivalent */
      1, 'Null',           /* N */
      2, 'Row-S (SS)',     /* L */
      3, 'Row-X (SX)',     /* R */
      4, 'Share',          /* S */
      5, 'S/Row-X (SSX)',  /* C */
      6, 'Exclusive',      /* X */
      to_char(b.request)
   ) mode_requested
from v$lock b,
     v$session c,
     sys.user$ u,
     sys.obj$ o,
     (select * from sys.dba_waiters) lock_blocker,
     (select * from sys.dba_waiters) lock_waiter
where b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by ctime desc, object, kaddr, lockwait

2011年4月12日 星期二

Oracle效能監視

這個Query可以找出目前在DB中有哪些session
及其目前相關的資訊
select a.status,b.SQL_TEXT,USERNAME,LOCKWAIT,STATUS,OSUSER,MACHINE,TERMINAL,LOGON_TIME
from v$session a
left outer join v$sqlarea b
on a.sql_hash_value=b.hash_value
where a.type='USER'