Oracle学习

http://www.fwvv.net/info/2008/07/01/20080701-63249.shtml

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 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 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、查会话的阻塞

 

SELECT 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;

SELECT 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

 

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、求会话等待的对象

 

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、查看游标状态

 

select  o.sid,
        osuser,
        machine,
        count(*) num_curs  
from v$open_cursor o,v$session s 
where  osuser='liuke'and o.sid=s.sid 
group by o.sid,osuser,machine 
order by num_curs desc;

 

20、查看内存

 

select  a.sid,
        spid,
        status,
        substr(a.program,1,40) prog, 
        a.terminal,osuser,
        value/60/100 value 
from  v$session a,v$process b,v$sesstat c 
where  c.statistic#=12 and 
       c.sid=a.sid and 
       a.paddr=b.addr 
order by value desc;
      

 

21.查看当前用户

 

select sys_context('userenv','ip_address') from dual;

22. 从V$SQLAREA中查询最占用资源的查询,

用buffer_gets列来替换disk_reads列可以得到占用最多内存的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.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;

23.列出使用频率最高的5个查询：

select sql_text,executions
from (select sql_text,
             executions,
             rank() over
     (order by executions desc) exec_rank
     from v$sql)
where exec_rank <=5;

24. 消耗磁盘读取最多的sql top5：

select disk_reads,
       sql_text
from (select sql_text,
             disk_reads,
             dense_rank() over
     (order by disk_reads desc) disk_reads_rank
     from v$sql)
where disk_reads_rank <=5;

 

25.找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,
       sql_text
from (select sql_text,
             buffer_gets,
             dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
     from v$sql)
where buffer_gets_rank<=5;

25,查看某张表实际物理大小 

SELECT TABLESPACE_NAME,
       TO_CHAR(SUM(BYTES) / (1024 * 1024), '999G999D999') CNT_MB
  FROM DBA_EXTENTS
 WHERE OWNER = '&OWNER'
   AND SEGMENT_NAME = '&TABLE_NAME'
   AND SEGMENT_TYPE LIKE 'TABLE%'
 GROUP BY TABLESPACE_NAME;

26.查询当前会话数

  SELECT sid,program,prev_exec_start,t.LOGON_TIME FROM V$SESSION t where username='QTEST' order by logon_time desc

--优化分析
ANALYZE TABLE QTEST.STOCK_TEMP_DAY_HK COMPUTE STATISTICS;
ANALYZE INDEX QTEST.PK_STOCK_TEMP_DAY_SZ ESTIMATE STATISTICS; 
--测试连接数
show parameter processes;
alter system set processes=1000 scope=spfile;
create pfile from spfile;
reboot
select COUNT(DISTINCT  SID) from v$open_cursor where user_name='QTEST' ORDER BY SID;
SELECT * FROM V$SESSION