oracle常用sql

select name from v$fixed_table
where  name like 'GV%'
order by name
/


select name from v$fixed_table
where  name like 'V$SGA%'
order by name
/


AWR使用多少空间？
col occupant_name for a25
Select occupant_name, occupant_desc, space_usage_kbytes
from v$sysaux_occupants
where occupant_name like '%AWR%'
/
OCCUPANT_NAME   OCCUPANT_DESC                                         SPACE_USAGE_KBYTES
---------------- ------------------------------------------------------------------------
SM/AWR          Server Manageability - Automatic Workload Repository           30080




基本的许可信息
select SESSIONS_MAX,SESSIONS_WARNING,SESSIONS_CURRENT,SESSIONS_HIGHWATER,USERS_MAX from v$license
/
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           0                0              397                454          0


当前会话数量与会话警告级别和会话最大级别一起显示。会话警告级别为0表示没有设置init.ora会话警告参数，
所以系统不会限制会话的数量。


init.ora参数LICENSE_MAX_USERS用于设置数据库中可以创建的已命名的用户数。


数据库中已安装的产品项
select * from v$option
/


计算SGA的实际大小
select ((select  sum(value) from  V$sga)- (select  current_size from  V$sga_dynamic_free_memory)) "SGA_TARGET"
from  dual 
/


SQL> select  *  from  V$SGA ;


NAME                      VALUE
-------------------- ----------
Fixed Size              2100168
Variable Size         218104888
Database Buffers      641728512
Redo Buffers            6287360


Variable Size项中较为突出的部分是共享池


SQL>  select  sum(bytes)/1024/1024 M  from  V$sgastat where  pool='shared pool' ;


         M
----------
 200.00457


SQL>  select  sum(bytes)/1024/1024 M  from  V$sgastat where  pool='large pool' ;     


         M
----------
         4


SQL>  select  sum(bytes)/1024/1024 M  from  V$sgastat where  pool='java pool';


在V$PARAMETER中发现init.ora的设置


select name,value,isdefault,isses_modifiable,issys_modifiable
from v$parameter  where name like 'sga%'
order by name
/


862234


NAME         VALUE      ISDEFAUL ISSES_MODIFIABLE       ISSYS_MODIFIABLE
------------ ---------- -------- ---------------------- ----------------------
sga_max_size 868220928  TRUE     FALSE                  FALSE
sga_target   868220928  FALSE    FALSE                  IMMEDIATE


参数ISSYS _MODIFIABLE =FALSE或DEFERRED，则说明该实例必须关闭并重启，才能使设置生效;




DB_CACHE_SIZE 命中率计算


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
--------------
    .989814754


select  1-(phy.value/(cur.value+con.value)) "db block hitratio"
from  v$sysstat cur, v$sysstat con, v$sysstat phy
where  cur.name = 'db block gets'
and    con.name = 'consistent gets'
and    phy.name = 'physical reads';




测定数据字典的命中率(V$ROWCACHE)


可以使用V$ROWCACHE视图(如程序清单所示)来发现对数据字典的调用是否有效地利用了通过init.ora参数SHARED_POOL_SIZE分配的内存缓存。
如果字典的命中率不高，系统的综合性能将大受影响。


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


select  sql_id, count(*) bind_count
from v$sql_bind_capture
where child_number = 0
group by sql_id
having  count(*) > 20
order by count(*)
/


SQL_ID        BIND_COUNT
------------- ----------
9qgtwh66xg6nz         21
c0agatqzq2jzr         25
19rkm1wsf9axx         29
3qsmy8ybvwt3n         32
b5cr4hhndmbuf         41


求的是每个SQL语句的绑定变量的数目，如果一个SQL语句的绑定变量的数目超过15个，那么性能将会降低下来。
select sql_text,users_executing,executions,users_opening,buffer_gets
from v$sqlarea
where sql_id ='b5cr4hhndmbuf'
order by buffer_gets;


检查用户的当前操作及其使用的资源
将V$SESSION和V$SQLTEXT连接就可以显示目前每一个会话正在执行的SQL语句.


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
/


查找用户正在访问的对象
查找用户正在访问的对象
一旦发现某些用户或者系统中的查询存在问题，查询V$ACCESS可以为您指出有潜在问题的对象(可能缺少索引)。
当想修改一个特殊的对象，或者需要知道在一个给定的时间点上谁在使用该对象时，它也非常有帮助，如下面程序清单所示。


set wrap  off
col OWNER  for a12
col object for a35
select a.sid, a.username, b.owner, b.object, b.type
from v$session a, v$access b
where a.sid = b.sid
and username='GISTAR'
/
用户正在访问的对象。对于已经访问过的对象，不会出现在结果集中。


使用索引
在监控任何索引前，这个视图没有任何记录
SQL>  select  * from  V$object_usage;


no rows selected


开始监控4个索引


alter index HRDT_INDEX1 monitoring usage;
alter index HRDT_INDEX2 monitoring usage;
alter index HRDT_INDEX3 monitoring usage;
alter index HRDT_INDEX4 monitoring usage;


现在视图显示4个索引的启动时间，但也同时指明它们并未被使用：


select index_name, table_name, monitoring, used,start_monitoring, end_monitoring from v$object_usage;




结束了对HRDT_INDEX4的监控，视图现在会显示监控的结束时间：
alter index HRDT_INDEX4 nomonitoring usage;


确定锁定问题


确定锁定问题将有助于定位正在等待其他某些用户或者某些东西的用户。可以使用这个策略来确定当前被锁定在系统中的用户。
这也使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 /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;


正在等待资源，属于被锁住。


Status有两种状态，LOCK表明该进程锁住了某个资源，WAIT表示该进程正在等待某个资源。
Username, Machine分别为ORACLE用户名及机器名
SID，SERIAL#可用于随后的解锁操作
Seconds表示该进程最后一次进行操作至当前的时间(秒)
ID1, 锁标识。某个LOCK状态的ID1与某个WAIT状态的ID1相同，可说明锁的正是另一个进程等待的。
SQL: 锁住资源的SQL语句


查锁语句：查询产生锁的用户锁sql
 select a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltext from v$session a, v$lock b, v$sqltext c
 where b.id1 in (select distinct e.id1　 from v$session d, v$lock e　 where d.lockwait = e.kaddr)　 
 and a.sid = b.sid　 and c.hash_value = a.sql_hash_value　　 and b.request = 0;
 
查询 的是行级排他锁。
select /*+ ordered */ username, v$lock.sid, trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq, lmode, request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid;


select  xid, xidusn, xidslot, xidsqn, status, start_scn
from  v$transaction
order by start_scn
/


SELECT  sid, blocking_session, username, blocking_session_status
FROM  v$session
WHERE username='SCOTT'
ORDER BY blocking_session
/


关闭有问题的会话


一个用户可能已经运行了一些他/她也不想运行的东西，或者可能需要在工作时间终止一个有问题的查询，到晚上再运行。


select username, sid, serial#, program, terminal
from v$session
where username is not null
/


以上程序代码中参数的顺序是SID，然后是SERIAL#。
alter  system  kill session 'SID,SERIAL#'
确定锁定其他用户的用户并终止他们的会话(如果需要)。


我们发现当一个session被kill掉以后，该session的paddr被修改，
如果有多个session被kill，那么多个session 的paddr都被更改为相同的进程地址:


select saddr,sid,serial#,paddr,username,status from v$session where username is not null
/
PADDER值相同


在这种情况下,很多时候，资源是无法释放的,我们需要查询spid，在操作系统级来kill这些进程.
但是由于此时v$session.paddr已经改变，我们无法通过v$session和v$process关联来获得spid
那还可以怎么办呢?


select p.addr from V$process p where pid <> 1 
minus
select s.paddr from  V$session s;




Ok，现在我们获得了进程地址，就可以在v$process中找到spid，然后可以使用Kill或者orakill在系统级来杀掉这些进程.
当在Oracle中kill session以后, Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.
此时v$process和v$session失去关联,进程就此中断.
然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.
如果此时被Kill的process，重新尝试执行任务，那么马上会收到进程中断的提示，process退出，此时Oracle会立即启动PMON 来清除该session.这被作为一次异常中断处理.




set  wrap off
set  line 160
set pagesize 49999




SQL> l
    SELECT DDL.OWNER AS 用户,
           DDL.NAME  AS 对象,
           DDL.type  AS 类型,
           VS.OSUSER AS OS_USER,
           VS.MACHINE,
           VS.STATUS,
           VS.LOGON_TIME AS "LOGIN_TIME",
           VP.SPID,
          'kill -9 ' || VP.SPID AS OS_KILL,
          vs.sid,
          vs.SERIAL#,
          'alter system kill session ''' || vs.sid || ',' || vs.serial# ||
          ''';' "ORACKE_KILL"
     FROM DBA_DDL_LOCKS DDL, V$SESSION VS, V$PROCESS VP
    WHERE DDL.SESSION_ID = VS.SID
    AND VS.PADDR = VP.ADDR


查找使用多会话的用户
select username, count(*)
from v$session
group by  username;


我们可以使用配置文件来限制用户的登录会话数。
select substr(profile,1,10) Profile,
substr(resource_name,1,30) "Resource Name",
substr(limit,1,10) Limit
from dba_profiles
group by substr(profile,1,10), substr(resource_name,1,30),substr(limit,1,10)
/
查看当前的用户配置文件。




查找磁盘I/O问题


视图V$DATAFILE、V$FILESTAT和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#
/


报告获得数据文件I/O的情况
Set TrimSpool   On
Set Line        142
Set Pages        57
Set NewPage       0
Set FeedBack   Off
Set Verify      Off
Set Term         On
TTitle           Off
BTitle          Off
Clear Breaks
Break On Tablespace_Name
Column TableSpace_Name For A12      Head "Tablespace"
Column Name       For A45            Head "File Name"
Column Total      For 999,999,990    Head "Total"
Column Phyrds     For 999,999,990    Head "Physical|Reads "
Column Phywrts   For 999,999,990    Head "Physical| Writes "
Column Phyblkrd  For 999,999,990     Head "Physical |Block Reads"
Column Phyblkwrt For 999,999,990    Head "Physical |Block Writes"
Column Avg_Rd_Time   For 90.9999999 Head "Average |Read Time|Per Block"
Column Avg_Wrt_Time For 90.9999999 Head "Average |Write Time|Per Block"
Column Instance           New_Value _Instance    NoPrint
Column Today               New_Value _Date        NoPrint
Select  Global_Name Instance, To_Char(SysDate, 'FXDay, Month DD, YYYY HH:MI') Today
From Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Data File I/O' Skip 1 -
Center 'Instance Name: ' _Instance Skip 1
select C.TableSpace_Name, B.Name, A.Phyblkrd +A.Phyblkwrt Total, 
A.Phyrds, A.Phywrts,A.Phyblkrd, A.Phyblkwrt
From V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and   B.File# = C.File_Id
order by  TableSpace_Name, A.File#
/
    
    
Column TableSpace_Name For A12      Head "Tablespace"
Column Total  For 9,999,999,990 Head "Total"
Column Phyrds For 9,999,999,990 Head "Physical|Reads "
Column Phywrts For 9,999,999,990 Head "Physical| Writes "
Column Phyblkrd For 9,999,999,990 Head "Physical |Block Reads"
Column Phyblkwrt  For 9,999,999,990 Head "Physical |Block Writes"
Column Avg_Rd_Time For 9,999,990.9999  Head "Average|Read Time  |Per Block"
Column Avg_Wrt_TimeFor 9,999,990.9999  Head "Average |Write Time|Per Block"
Clear Breaks
Break on Disk Skip 1
Compute Sum Of Total On Disk
Compute Sum Of Phyrds On Disk
Compute Sum Of Phywrts On Disk
Compute Sum Of Phyblkrd On Disk
Compute Sum Of Phyblkwrt On Disk
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Disk I/O' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
select SubStr(B.Name, 1, 13) Disk, C.TableSpace_Name,
A.Phyblkrd + A.Phyblkwrt Total, 
A.Phyrds, A.Phywrts,A.Phyblkrd, A.Phyblkwrt, 
((A.ReadTim /Decode(A.Phyrds,0,1,A.Phyblkrd))/100) Avg_Rd_Time,
((A.WriteTim / Decode(A.PhyWrts,0,1,A.PhyblkWrt)) /100) Avg_Wrt_Time
from V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and   B.File# = C.File_Id
order by  Disk,C.Tablespace_Name, A.File#
/


查看回滚段的使用情况，哪个用户正在使用回滚段的资源。
select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username
/


查看回滚段的统计信息
set  line  132
set  wrap  off
col name for a10
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn
/


V$ROLLSTAT中的常用列
USN：回滚段标识
RSSIZE：回滚段默认大小
XACTS：活动事务数
在一段时间内增量用到的列
WRITES：回滚段写入数(单位:bytes)
SHRINKS：回滚段收缩次数
EXTENDS：回滚段扩展次数
WRAPS：回滚段翻转(wrap)次数
GETS：获取回滚段头次数
WAITS：回滚段头等待次数


下面程序清单中的查询显示了整个系统在整体上的等待数。


Set TrimSpool  On
Set NewPage      0
Set Pages       57
Set Line       132
Set FeedBack  Off
Set Verify     Off
Set Term       On
Ttitle         Off
Btitle         Off
Clear  Breaks
Column Event         For A40  Heading "Wait Event"
Column Total_Waits  For 999,999,990  Head "Total Number| Of Waits "
Column Total_Timeouts For 999,999,990 Head "Total Number|Of TimeOuts"
Column Tot_Time     For 999,999,990 Head "Total Time|Waited "
Column Avg_Time     For 99,990.999 Head "Average Time|Per Wait "
Column Instance  New_Value _Instance  NoPrint
Column Today      New_Value _Date       NoPrint
select Global_Name Instance, To_Char(SysDate,'FXDay DD, YYYY HH:MI') Today
from    Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'System Wide Wait Events' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
Select event, total_waits, 
total_timeouts,(time_waited / 100) tot_time, 
(average_wait / 100) Avg_time
from  v$system_event
order by  total_waits desc
/




检查角色和权限设置


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
/


等待事件V$视图


本节包含一些显示等待事件的V$ 脚本。从实用角度来说，应该使用STATSPACK 报表、AWR 报表或企业管理器来查找等待事件。


在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(*)
/