Oracle数据库慢查看数据库

网友投稿 314 2023-11-26

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

Oracle数据库慢查看数据库

   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-/30345407/viewspace-2122532/，如需转载，请注明出处，否则将追究法律责任。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。

标签：数据库 数据 代码 进程 链接
上一篇:怎么掌握MySQL复制架构
下一篇:mysql如何删除not null限制
相关文章

 发表评论

暂时没有评论，来抢沙发吧~