常用SQL（1）

 

/*当前时间段使用频率较高的sql*/
SELECT sql_text, executions, SYSDATE
  FROM (SELECT sql_text,
               executions,
               rank() over(ORDER BY executions DESC) exec_rank
          FROM v$sql)
 WHERE exec_rank <= 10;

/**实例**/
--查看当前实例
SELECT host_name, instance_name, version FROM v$instance;

/*SGA PAG*/
--SGA 信息
SELECT t.pool, t.NAME, t.bytes / 1024 / 1204 FROM v$sgastat t;
--PGA 信息
SELECT t.NAME,
       decode(t.unit, 'bytes', t.VALUE / 1024 / 1024, t.VALUE) val,
       decode(t.unit, 'bytes', 'MB', t.unit) unit
  FROM v$pgastat t;

/**连接数**/
show parameter processes;
--总连接数
SELECT COUNT(1) FROM v$session WHERE username IS NOT NULL;
--不同用户的连接数
SELECT username, COUNT(username)
  FROM v$session
 WHERE username IS NOT NULL
 GROUP BY username;
--查看并发连接数
SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';
--不同程序的连接数
SELECT t.program, COUNT(1)
  FROM v$session t
 WHERE username IS NOT NULL
 GROUP BY program
 ORDER BY COUNT(1) DESC;