【管理】Oracle 常用的V$ 檢視指令碼

版本資訊：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

資料庫資訊：
SQL> select name, created, log_mode from v$database;

NAME CREATED LOG_MODE
--------- -------------- ------------
TEST 13-9月 -09 ARCHIVELOG

2. 自動工作量倉庫(AWR) 的基本資訊

自動工作量倉庫(AWR)在預設情況下，倉庫用小時填充，保留期是7天。

AWR使用多少空間

SQL>Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';

OCCUPANT_N OCCUPANT_DESC SPACE_USAGE_KBYTES
---------- -------------------------------------------------- ------------------
SM/AWR Server Manageability - Automatic Workload Repository 51200

系統上最原始的AWR資訊是什麼？
SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
21-8月 -09 09.18.15.359000000 上午 +08:00

什麼是AWR資訊的保留期？
SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31

將AWR資訊的保留期更改為15天？
SQL> EXEC dbms_stats.alter_stats_history_retention(15);

PL/SQL 過程已成功完成。

3. 基本的許可資訊

V$LICENSE檢視允許DBA監控系統內任何時候有關資料庫數量的所有系統活動的數量。會話警告級別為0表示沒有設定init.ora會話警告引數，所以系統不會顯示警告資訊。會話最大級別為0表示沒有設定init.ora會話最大引數，所以系統不會限制會話的數量。查詢V$LICENSE檢視，以檢視所允許的最大會話數。也可以在接近最大數時設定警告。

應該定期執行指令碼，以向DBA提供系統一天中實際的會話數量，從而保證正確的許可授權。設定init.ora引數LICENSE_MAX_SESSIONS = 110，將會話數限制為110。設定init.ora引數LICENSE_SESSIONS_WARNING = 100，系統將向每位在第100個會話之後的使用者顯示警告資訊，這樣他們就會通知DBA，系統因遇到問題而關閉(希望能如此)。init.ora引數LICENSE_MAX_USERS用於設定資料庫中可以建立的已命名的使用者數。在以下程式清單中，該值為0，所以沒有限制。

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 9 18 0

4. 資料庫中已安裝的產品項

查詢V$OPTION檢視，可以獲取您已安裝的Oracle產品項。V$VERSION檢視將給出已安裝的基本產品項的版本。

SQL> select * from v$option;

PARAMETER VALUE
---------------------------------------------------------------- -----
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
Flashback Database TRUE
Data Mining Scoring Engine FALSE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
... ...

5. 記憶體分配摘要(V$SGA)

V$SGA檢視給出了系統的系統全域性區(System Global Area，SGA)記憶體結構的摘要資訊。Data Buffers是在記憶體中分配給資料的位元組數量。它根據init.ora的引數DB_CACHE_SIZE得到。Redo Buffers主要是依據init.ora引數LOG_BUFFER計算得到，每當COMMIT命令提交資料時，它被用於快取已改變的記錄並將它們儲存到重做日誌中。訪問V$SGA檢視可以得到系統的實體記憶體分配的基本概念，包括在Oracle中為資料、共享池、large池、java池以及日誌緩衝區分配的記憶體。

SQL> COLUMN value FORMAT 999,999,999,999
SQL> select * from v$sga;

NAME VALUE
-------------------- ----------------
Fixed Size 1,248,576
Variable Size 100,664,000
Database Buffers 180,355,072
Redo Buffers 7,139,328

如果使用SGA_TARGET-- 內部動態調整大小：
SQL> select ((select sum(value) from v$sga) -(select current_size from v$sga_dynamic_free_memory)) "
SGA_TARGET" from dual;

SGA_TARGET
----------
289406976

6.記憶體分配的細節(V$SGASTAT)

在V$檢視中，可以查詢V$SGASTAT檢視來提供有關SGA更詳細的記憶體分配資訊。這個檢視提供了SGA和記憶體資源的動態資訊(訪問資料庫時會出現相應變化)。這個語句非常詳細地描述了SGA的尺寸。在V$SGA和V$SGASTAT檢視中均包含記錄FIXED_SGA、BUFFER_CACHE和LOG_BUFFER.V$SGASTAT檢視可獲取Oracle SGA詳細的分類列表以及共享池分配中各儲存容器的詳細資訊。

SQL> select * from v$sgastat;

POOL NAME BYTES
------------ -------------------------- ----------
fixed_sga 1248576
buffer_cache 180355072
log_buffer 7139328
shared pool dpslut_kfdsg 256
shared pool hot latch diagnostics 80
shared pool ENQUEUE STATS 8360
shared pool transaction 264528
shared pool KCB buffer wait statistic 3352
shared pool invalid low rba queue 320
shared pool KQF optimizer stats table 2396
... ...

7. 在V$PARAMETER顯示init.ora資訊

程式清單中的指令碼顯示了系統中的init.ora引數。它還提供了有關引數的資訊，確定每一個引數的當前值是否就是預設值(ISDEFAULT=TRUE)。查詢V$PARAMETER檢視，將得到init.ora引數的當前值。它還顯示了哪些init.ora引數已經改動了原始的預設值：ISDEFAULT = FALSE。它還顯示了對於一個給定的會話，只能修改哪些引數(當ISSES_MODIFIABLE = TRUE時)。最後，它顯示了在不用關閉和重啟資料庫可以修改哪些引數(當ISSYS_MODIFIABLE = IMMEDIATE時)；而ISSYS_MODIFIABLE = DEFERRED說明該引數對所有新登入的，但當前未登入會話的使用者有效。如果引數ISSYS _MODIFIABLE =FALSE，則說明該例項必須關閉並重啟，才能使設定生效。

SQL>select name, value, isdefault, isses_modifiable,issys_modifiable from v$parameter order by name;

NAME VALUE ISDEFAULT ISSES ISSYS_MOD
--------------- ----------------------------------- --------- ----- ---------
active_instance TRUE FALSE FALSE
asm_diskgroups TRUE FALSE IMMEDIATE
audit_file_dest D:/ORACLE/ADMIN/TEST/ADUMP FALSE FALSE DEFERRED
audit_sys_opera FALSE TRUE FALSE FALSE
background_dump D:/ORACLE/ADMIN/TEST/BDUMP FALSE FALSE IMMEDIATE
backup_tape_io_ FALSE TRUE FALSE DEFERRED
... ...

8.測定資料的命中率(V$SYSSTAT)

查詢V$SYSSTAT檢視(如下程式清單所示)可以檢視從記憶體中讀取資料的頻率。它提供了資料庫中設定的資料塊快取區的命中率。這個資訊可以幫助您判斷系統何時需要更多的資料快取(DB_CACHE_SIZE)，或者系統的狀態何時調整得不佳(二者均將導致較低的命中率)。通常情況下，您應當確保讀資料的命中率保持在95%以上。將系統的命中率從98%提高到99%，可能意味著效能提高了100%(取決於引起磁碟讀操作的語句)。
SELECT 1
- ( SUM (DECODE (NAME, 'physical reads', VALUE, 0))
/ ( SUM (DECODE (NAME, 'db block gets', VALUE, 0))
+ (SUM (DECODE (NAME, 'consistent gets', VALUE, 0)))
)
) "Read Hit Ratio"
FROM v$sysstat;

Read Hit Ratio
--------------
.993067726

在Oracle 10g中，也可以直接獲得V$SYSMETRIC中的 AWR 資訊:
SQL> select metric_name,value from v$sysmetric where metric_name='Buffer Cache Hit Ratio';

METRIC_NAME VALUE
---------------------------------------------------------------- ----------
Buffer Cache Hit Ratio 100
Buffer Cache Hit Ratio 100

9.測定資料字典的命中率(V$ROWCACHE)

可以使用V$ROWCACHE檢視(如程式清單所示)來發現對資料字典的呼叫是否有效地利用了透過init.ora引數SHARED_POOL_SIZE分配的記憶體快取.如果字典的命中率不高，系統的綜合效能將大受影響。推薦的命中率是95%或者更高。如果命中率低於這個百分比，說明可能需要增加init.ora引數SHARED_POOL_SIZE。但要記住，在V$SGASTAT檢視中看到的共享池包括多個部分，而這裡僅僅就是其中之一。注意：在大幅度使用公共同名的環境中，字典命中率可能難以超過75%，即使共享池的尺寸很大。這是因為Oracle必須經常檢查不存在的物件是否依舊存在。

SQL>select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)+ sum(getmisses)))) * 100 HitRate from v$rowcache;

SUM(GETS) SUM(GETMISSES) HITRATE
---------- -------------- ----------
370854 11068 97.1020261

在Oracle 10g中，也可以直接獲得V$SYSMETRIC中的AWR資訊:
select metric_name, value from v$sysmetric where metric_name ='Library Cache Hit Ratio';
METRIC_NAME VALUE
---------------------------------------------------------------- ----------------
Library Cache Hit Ratio 98.0281690140845
Library Cache Hit Ratio 98.0281690140845

10.測定共享SQL和PL/SQL的命中率(V$LIBRARYCACHE)
訪問V$LIBRARYCACHE檢視可以顯示實際使用的語句(SQL和PL/SQL)訪問記憶體的情況。如果init.ora的引數SHARED_POOL_SIZE設定得太小，記憶體中就沒有足夠的空間來儲存所有的語句。固定命中率通常應該是95%或更高，而過載的次數不應該超過1%。查詢V$SQL_BIND_CAPTURE檢視，看看每個SQL繫結是否太高，是否需要CURSOR_SHARING。

select sum(pins) "Executions", sum(pinhits) "Hits",((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",sum(reloads) "Misses", ((sum(pins) / (sum(pins)+ sum(reloads))) * 100) "RelHitRatio" from v$librarycache;

Executions Hits PinHitRatio Misses RelHitRatio
---------- ---------- ----------- ---------- -----------
417954 403489 96.5390928 4092 99.0304374

查詢 v$sql_bind_capture，看看 average binds 是否大於15 (issue):
select sql_id, count(*) bind_count from v$sql_bind_capture where child_number = 0 group by sql_id having count(*) > 20order by count(*);

SQL_ID BIND_COUNT
------------- ----------
9qgtwh66xg6nz 21

11.確定需要固定的PL/SQL物件
碎片化現象造成共享池中的可用空間均成為許多零散的片段，而沒有足夠大的連續空間，這是共享池中的普遍現象。消除共享池錯誤(參閱第4章和第13章以瞭解更多資訊)的關鍵是理解哪些物件會引起問題。一旦知道了會引起潛在問題的PL/SQL物件，就可以在資料庫啟動時固定這個程式碼(這時共享池是完全連續的)。

SQL>select name, sharable_mem from v$db_object_cache where sharable_mem > 100000 and type in ('PACKAGE', 'PACKAGE BODY','FUNCTION', 'PROCEDURE') and kept = 'NO';
NAME SHARABLE_MEM
----------------- ------------
DBMS_BACKUP_RESTO 258495
DBMS_STATS 131422

12.透過V$SQLAREA查詢有問題的查詢
V$SQLAREA檢視提供了一種識別有潛在問題或者需要最佳化的SQL語句的方法，從而可透過減少磁碟的訪問來最佳化資料庫的綜合效能。
select b.username username, a.disk_reads reads,a.executions exec, a.disk_reads /decode(a.executions, 0, 1,a.executions) rds_exec_ratio,a.command_type, a.sql_text Statement from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;

13.檢查使用者的當前操作及其使用的資源
將V$SESSION和V$SQLTEXT連線就可以顯示目前每一個會話正在執行的SQL語句，如下面的程式清單所示。這在有些時候是極為有用的，例如DBA希望檢視某一個給定的時間點上系統究竟執行了哪些操作。
select a.sid, a.username, s.sql_text from v$session a, v$sqltext s where a.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece;

select a.username, b.block_gets, b.consistent_gets,b.physical_reads, b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username;

USERNAME BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
------------------------------ ---------- --------------- --------------
DBSNMP 27 118917 246
DBSNMP 4383 111119 268
SYSMAN 45617 123434 660
SYSMAN 9416 94902 157
SYSMAN 380 37019 47
SYSTEM 11 256588 147

14.查詢使用者正在訪問的物件
透過查詢V$ACCESS檢視可檢視在給定的時間點上使用者所訪問的所有物件。這有助於查明有問題的物件，在想修改一個特定的物件時也很有用(查詢誰在訪問它)。然而，當系統有一個很大的共享池和數百個使用者時，這個操作的開銷將很大。
select a.sid, a.username, b.owner, b.object, b.type from v$session a, v$access b where a.sid = b.sid;

15.使用索引
Oracle 9i提供了監控索引使用的功能。這個新的檢視表示索引是否被引用，但不能反映索引使用的頻率。要監控的索引需要單獨開啟和關閉。可以使用alter index命令來初始化監控工作，然後透過對檢視V$OBJECT_USAGE的查詢來實現索引的跟蹤。

select * from v$object_usage;
開始監控索引：
alter index HRDT_INDEX1 monitoring usage;
select index_name, table_name, monitoring, used,start_monitoring, end_monitoring from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
----------- ---------- --- --- ------------------- ------------------
HRDT_INDEX1 HRS_DETAIL YES NO 10/13/2002 03:11:34

16.確定鎖定問題
確定鎖定問題將有助於定位正在等待其他某些使用者或者某些東西的使用者。可以使用這個策略來確定當前被鎖定在系統中的使用者。這也使DBA們可以確認一個相關的Oracle程式是否真地被鎖定了，還是僅僅執行得比較慢。您還能夠識別當前的語句是否正在執行鎖定使用者的操作。
select /*+ ordered */ b.username, b.serial#, d.id1, a.sql_text from v$lock d, v$session b, v$sqltext a where b.lockwait = d.kaddr and a.address = b.sql_address and a.hash_value = b.sql_hash_value;

檢視系統中是哪個使用者造成了前一個使用者被鎖定的問題
select /*+ ordered */ a.serial#, a.sid, a.username, b.id1, c.sql_text from v$lock b, v$session a, v$sqltext c where b.id1 in(select /*+ ordered */ distinct e.id1 from v$lock e, v$session d where d.lockwait= e.kaddr) and a.sid = b.sid and c.hash_value = a.sql_hash_value and b.request = 0;

17. 關閉有問題的會話
select username, sid, serial#, program, terminal from v$session;
alter system kill session '11,18';

You can't kill your own session though:
alter system kill session '10,4';
*ERROR at line 1:ORA-00027: cannot kill current session

18.查詢使用多會話的使用者
有些時候，使用者喜歡使用多會話來一次完成多個任務，但這會引起問題。開發人員也會有同樣的問題，如果他開發了一個建立了會派生大量程式的糟糕的應用程式。所有這些都可能降低系統的綜合效能。使用者名稱NULL是後臺程式。
SQL> select username, count(*) from v$session group by username;

USERNAME COUNT(*)
------------------------------ ----------
17
SYSTEM 4
SYSMAN 3
DBSNMP 2

19.查詢磁碟I/O問題
檢視V$DATAFILE、V$FILESTAT和V$DBA_DATA_FILES提供了資料庫中所有資料檔案和磁碟的檔案I/O活動資訊。理想情況下，物理的讀和寫應當平均分佈。如果沒有合理的配置系統，其綜合效能就會受到影響。
select a.file#, a.name, a.status, a.bytes,b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;

FILE# NAME STATUS BYTES PHYRDS PHYWRTS
---------- ----------------- ------- ---------- ---------- ----------
1 D:/ORACLE/ORADATA SYSTEM 534773760 7732 1506
2 D:/ORACLE/ORADATA ONLINE 31457280 54 2778
3 D:/ORACLE/ORADATA ONLINE 356515840 2680 7905
4 D:/ORACLE/ORADATA ONLINE 5242880 25 3

20.查詢回滾段的內容
這個有幫助的查詢顯示了一個回滾段的實際等待數。可以顯示回滾資訊(包括自動撤消)。還可以從程式清單顯示的檢視中查詢Shrink和 wrap資訊。查詢V$ROLLNAME、V$ROLLSTAT和V$TRANSACTION檢視可以提供使用者如何使用回滾段和撤消表空間的資訊。通常情況下，在一個時間點上不應讓多個使用者訪問同一個回滾段(儘管這是被允許的)。

select a.name, b.extents, b.rssize, b.xacts,b.waits, b.gets, optsize, status from v$rollname a, v$rollstat b where a.usn = b.usn;
NAME EXTENTS RSSIZE XACTS WAITS GETS STATUS
----------------- ---------- ---------- ---------- ---------- ---------- -------
SYSTEM 6 385024 0 0 215 ONLINE
_SYSSMU1$ 3 1171456 0 0 3191 ONLINE

21.檢查空閒列表是否充足

如果使用多程式完成大量的插入操作，空閒列表(空閒的資料庫資料塊的列表)的預設值1可能是不夠的。如果沒有使用自動空間段管理(Automatic Space Segment Management，簡稱ASSM)，您可能需要增加空閒列表，或者空閒列表組。在使用多程式完成大量的插入操作時，應確保有足夠的空閒列表和空閒列表組。空閒列表的預設儲存值是1。如果您使用了ASSM，Oracle將為您管理這些引數，但是一個有大量資料交換的事務環境中，在應用ASSM前應經過仔細的測試。雖然如此，但通常最好使用ASSM。

select ((A.Count/(B.Value + C.Value))*100)Pct from V$WaitStat A, V$SysStat B, V$SysStat C where A.Class = 'free list' and B.Statistic# = ( select Statistic# from V$StatName where Name = 'db block gets') and C.Statistic# = (select Statistic# from V$StatName where Name = 'consistent gets')；

22 檢查角色和許可權設定

根據使用者名稱進行授權的物件級特權
select b.owner || '.' || b.table_name obj,b.privilege what_granted, b.grantable,a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1,2,3;

根據被授權人進行授權的物件級特權
Select owner || '.' || table_name obj,privilege what_granted, grantable, grantee from sys.dba_tab_privs where not exists(select 'x'from sys.dba_users where username = grantee)order by 1,2,3;

根據使用者名稱進行授予的系統級特權
select b.privilege what_granted,b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1,2;

根據被授權人進行授予的系統級特權
select privilege what_granted,admin_option, grantee from sys.dba_sys_privs where not exists ( select 'x' from sys.dba_users where username = grantee ) order by 1,2;

根據使用者名稱授予的角色
select b.granted_role ||decode(admin_option, 'YES',' (With Admin Option)',null) what_granted, a.username from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;

根據被授權人授予的角色
select granted_role ||decode(admin_option, 'YES',' (With Admin Option)', null) what_granted,grantee from sys.dba_role_privs where not exists(select 'x'from sys.dba_users where username = grantee ) order by 1;

使用者名稱及已被授予的相應許可權
select a.username,b.granted_role || decode(admin_option,'YES',' (With Admin Option)',null) what_granted from
sys.dba_users a,sys.dba_role_privs b where a.username = b.grantee
UNION
select a.username,b.privilege || decode(admin_option,'YES',' (With Admin Option)', null) what_granted from
sys.dba_users a,sys.dba_sys_privs b where a.username = b.grantee
UNION
select a.username,b.table_name ||'-' || b.privilege|| decode(grantable,'YES',' (With Grant Option)',null)
what_granted from sys.dba_users a, sys.dba_tab_privs b where a.username = b.granteeorder by 1;

查詢使用者名稱及相應的配置檔案、預設的表空間和臨時表空間
Select username, profile, default_tablespace,temporary_tablespace, created from sys.dba_users order by username;

23.等待事件V$檢視
在Oracle 10g中V$SESSION_WAIT中的所有等待事件列現在都在V$SESSION中。因此，確保查詢等待資訊的 V$SESSION，因為它是一個更快的檢視。V$ACTIVE_SESSION_HISTORY (ASH)將許多重要統計資料合併為一個檢視或一個報表(ASH報表)。

馬上該誰等待--查詢V$SESSION_WAIT / V$SESSION
select event, sum(decode(wait_time,0,1,0)) "Waiting Now",sum(decode(wait_time,0,0,1)) "Previous Waits",count(*) "Total" from v$session_wait group by event order by count(*);

馬上該誰等待；SPECIFIC Waits--查詢V$SESSION_WAIT
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

誰在等待 - 最後10 個等待數--查詢V$SESSION_WAIT_HISTORY
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id+de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event,sid;

查詢P1, P2, P3代表什麼--查詢 V$EVENT_NAME
select event#,name,parameter1 p1,parameter2 p2,parameter3 p3 from v$event_name where name in ('buffer busy waits', 'write complete waits');

會話開始後的所有等待數--查詢 V$SESSION_EVENT
select sid, event, total_waits, time_waited, event_id from v$session_event where time_waited > 0 order by time_waited;

類的所有會話等待數--查詢V$SESSION_WAIT_CLASS
select sid, wait_class, total_waits from v$session_wait_class;

系統啟動後的所有等待數--查詢V$SYSTEM_EVENT
select event, total_waits, time_waited, event_id from v$system_event where time_waited > 0 order by time_waited;

類的系統等待數--查詢V$SYSTEM_WAIT_CLASS
select wait_class, total_waits from v$system_wait_class order by total_waits desc;

類的系統等待數--查詢V$ACTIVE_SESSION_HISTORY
--In the query below, the highest count session is leader in non-idle wait events.
select session_id,count(1) from v$active_session_history group by session_id order by 2;
--In the query below, find the SQL for the leader in non-idle wait events.
select c.sql_id, a.sql_text from v$sql a, (select sql_id,count(1) from v$active_session_history b where sql_id is not null group by sql_idorder by 2 desc ) c where rownum <= 5 order by rownum;