性能相关查询
查询用户 CPU 的使用率
这个语句是用来显示每个用户的 CPU 使用率,有助于用户理解数据库负载情况
1 SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds 2 FROM v$session ss, v$sesstat se, v$statname sn 3 WHERE se.STATISTIC# = sn.STATISTIC# 4 AND NAME LIKE '%CPU used by this session%' 5 AND se.SID = ss.SID 6 AND ss.status = 'ACTIVE' 7 AND ss.username IS NOT NULL 8 ORDER BY VALUE DESC;
查询数据库长查询进展情况
显示运行中的长查询的进展情况
01 SELECT a.sid, 02 a.serial#, 03 b.username, 04 opname OPERATION, 05 target OBJECT, 06 TRUNC (elapsed_seconds, 5) "ET (s)", 07 TO_CHAR (start_time, 'HH24:MI:SS') start_time, 08 ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)" 09 FROM v$session_longops a, v$session b 10 WHERE a.sid = b.sid 11 AND b.username NOT IN ('SYS', 'SYSTEM') 12 AND totalwork > 0 13 ORDER BY elapsed_seconds;
获取当前会话 ID,进程 ID,客户端 ID 等
这个专门提供给想使用进程 ID 和 会话 ID 做些 voodoo magic 的用户。
1 SELECT b.sid, 2 b.serial#, 3 a.spid processid, 4 b.process clientpid 5 FROM v$process a, v$session b 6 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');
V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID
V$PROCESS.SPID 是数据库服务器后台进程 ID
V$SESSION.PROCESS 是客户端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.
查询特定的模式或者表中执行的最后一个 SQL 语句
1 SELECT CREATED, TIMESTAMP, last_ddl_time 2 FROM all_objects 3 WHERE OWNER = 'MYSCHEMA' 4 AND OBJECT_TYPE = 'TABLE' 5 AND OBJECT_NAME = 'EMPLOYEE_TABLE';
查询每个执行读取的前十个 SQL
01 SELECT * 02 FROM ( SELECT ROWNUM, 03 SUBSTR (a.sql_text, 1, 200) sql_text, 04 TRUNC ( 05 a.disk_reads / DECODE (a.executions, 0, 1, a.executions)) 06 reads_per_execution, 07 a.buffer_gets, 08 a.disk_reads, 09 a.executions, 10 a.sorts, 11 a.address 12 FROM v$sqlarea a 13 ORDER BY 3 DESC) 14 WHERE ROWNUM < 10;
在视图中查询并显示实际的 Oracle 连接
1 SELECT osuser, 2 username, 3 machine, 4 program 5 FROM v$session 6 ORDER BY osuser;
查询并显示通过打开连接程序打开连接的组
1 SELECT program application, COUNT (program) Numero_Sesiones 2 FROM v$session 3 GROUP BY program 4 ORDER BY Numero_Sesiones DESC;
查询并显示连接 Oracle 的用户和用户的会话数量
1 SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones 2 FROM v$session 3 GROUP BY username 4 ORDER BY Numero_Sesiones DESC;
获取拥有者的对象数量
1 SELECT owner, COUNT (owner) number_of_objects 2 FROM dba_objects 3 GROUP BY owner 4 ORDER BY number_of_objects DESC;