Oracle数据库慢查看数据库

ゝ一纸荒年。 2022-10-09 06:17 263阅读 0赞

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：

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语句

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、查找数据库当前连接数

select count(*) from v$session;
    select count(*) from v$session where status='ACTIVE'

3、查找执行次数最多的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;