性能测试常用SQL

查询表空间使用情况

SELECT A.TABLESPACE_NAME TABLENAME,
    ROUND(A.BYTES/1024/1024/1024,2) "TOTAL(G)",
    ROUND(B.BYTES/1024/1024/1024,2) "USED(G)",
    ROUND(C.BYTES/1024/1024/1024,2) "FREE(G)"
FROM
    SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
AND A.TABLESPACE_NAME NOT IN('USED','SYSTEM', 'SYSAUX','UNDOTBS1')

数据表占用空间大小情况

select segment_name, tablespace_name, bytes, blocks
from user_segments
where segment_type = 'TABLE'
ORDER BY bytes DESC, blocks DESC;

select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))),2) FSFI
from dba_free_space
group by tablespace_name order by 1;

Automatic Workload Report报告打印过程

1.sqlplus / as sysdba
2.exec dbms_workload_repository.create_snapshot();
3.exec dbms_workload_repository.create_snapshot();
4.@?/rdbms/admin/awrrpt

用户解锁、修改密码、监听、开机、关机、数据库连接

1.sqlplus / as sysdba --登录SYS用户模式
2.alter user scott account unlock; --解锁用户
3.alter user scott identified by tiger;  --修改密码
4.shutdown immediate;    --关机
5.startup;         --开机
查看状态命令：lsnrctl status
启动监听：lsnrctl start
关闭监听:lsnrctl stop
sqlplus / as sysdba
sqlplus 用户名/密码
sqlplus username/password@//ip:port/SID_name

测试Oracle磁盘IOPS性能

DECLARE 
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
dbms_output.put_line ('max_mbps = ' || mbps);
END;

查询redo日志切换频率

SELECT b.SEQUENCE#,  b.FIRST_TIME,  a.SEQUENCE#, a.FIRST_TIME,
    ROUND(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2) 切换时间
FROM v$log_history a, v$log_history b
WHERE a.SEQUENCE#=b.SEQUENCE#+1 AND b.THREAD#=1
ORDER BY  a.SEQUENCE# DESC;

查询数据库锁情况

SELECT object_name AS 对象名称,  s.sid,  s.serial#,  p.spid AS 系统进程号
FROM  v$locked_object l , dba_objects o ,  v$session s , v$process p
WHERE l.object_id=o.object_id AND l.session_id=s.sid AND s.paddr=p.addr;
清除锁对象:
alter system kill session 'sid,serial#';

多表数据统计显示查询语句

SELECT 'STUDENT' TABLE_NAME,  COUNT(*)  TABLE_COUNT 
FROM STUDENT
WHERE JYRQ IN (TRUNC(SYSDATE))
UNION ALL
SELECT 'TEACHER' TABLE_NAME,  COUNT(*)  TABLE_COUNT 
FROM TEACHER
WHERE JYRQ IN (TRUNC(SYSDATE))