Oracle数据库慢查看数据库-CSDN博客

1、查看数据库正在运行的sql

select a.program, b.spid, c.sql_text,c.SQL_ID

from v$session a, v$process b, v$sqlarea c

where a.paddr = b.addr

and a.sql_hash_value = c.hash_value

and a.username is not null;

#查看锁
select * from v$lock where type in ('TM','TX','UL');

select * from v$session where sid=1717;

2、Oracle数据库查看一个进程是如何执行相关的实际SQL语句

SELECT b.sql_text, sid, serial#, osuser, machine
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address;
3、查询前台发出的SQL语句.

select user_name,sql_text
from v$open_cursor
where sid in
(select sid from (select sid,serial#,username,program from v$session where status='ACTIVE'));

4、根据SPID查询session

SELECT * FROM v$session WHERE paddr IN
(SELECT addr FROM v$process WHERE spid=&spid);

5、根据SID查询process

SELECT * FROM v$process WHERE addr IN
(SELECT paddr FROM v$session WHERE sid=&sid);

6、DBA如何查询其他用户所进行的操作

SELECT sql_text
FROM v$sql t1, v$session t2
WHERE t1.address = t2.sql_address
AND t2.sid = &sid;

7、根据process查询sql语句

SELECT 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 = '$processID'))
ORDER BY piece ASC;

8、关于v$session

在查询 v$session 视图的时候，我们根据command字段内部表示解码每一个字段，当我们需要快速找出他们的 Oracle 系统的内部情况时非常有用。
select
substr(s.username,1,18) username,substr(s.program,1,15) program,p.spid,s.process,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
from
v$session s,
v$process p,
v$transaction t,
v$rollstat r,
v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by username

（3）几个相关的SQL

--查看系统进程对应的信息
select se.saddr,se.sid,se.serial#,p.pid,se.paddr,s.sql_id,s.sql_text
from v$session se ,v$process p, v$sqlarea s
where se.paddr=p.addr and se.sql_address=s.address and se.process='&1'
and se.username is not null

--查看所有的会话
select se.username,se.saddr,se.sid,se.serial#,se.process,s.sql_id
from v$session se,v$sqlarea s
where se.sql_address=s.address

--查看会话对应的sql内容
select se.username,se.process,s.sql_text
from v$session se,v$sqlarea s
where se.sql_address=s.address and s.sql_id='&1'

1.查看消耗资源最多的SQL：

      Sql代码   
      
    
 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls   
 FROM V$SQLAREA   
 WHERE buffer_gets > 10000000 OR disk_reads > 1000000   
 ORDER BY buffer_gets + 100 * disk_reads DESC;   

查找前10条性能差的sql语句

      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 ;   

1、查看数据库当前有无死锁
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object);

2、2，查找数据库当前连接数
select count(*) from v$session;
select count(*) from v$session where status='ACTIVE'

3查找数据库当前会话

6\查找执行次数最多的SQL
select sql_text,executions
from (
select sql_text,executions,rank() over(order by executions desc) exec_rank
from v$sql
)
where exec_rank <=20;

1.查看总消耗时间最多的前20条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 20;

2.查看CPU消耗时间最多的前20条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 20;

3.查看消耗磁盘读取最多的前20条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 20;

逻辑读 读前10的SQL：

SELECT *
FROM (
    SELECT sql_fulltext AS sql, SQL_ID,buffer_gets / executions AS "Gets/Exec",buffer_gets, executions
    FROM V$SQLAREA
    WHERE buffer_gets > 10000
    ORDER BY "Gets/Exec" DESC
)
WHERE rownum <= 10;

物理读 读前10的SQL：

SELECT *
FROM (
    SELECT sql_fulltext AS sql, SQL_ID,disk_reads / executions AS "Reads/Exec",disk_reads, executions
    FROM V$SQLAREA
    WHERE disk_reads > 1000
    ORDER BY "Reads/Exec" DESC
)
WHERE rownum <= 10;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30345407/viewspace-2122532/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30345407/viewspace-2122532/