Oracle性能测试常用SQL语句

作为IT工程师，在进行Oracle数据库性能测试时，以下是一些非常有用的SQL语句：

系统性能监控语句

1. 查看当前会话信息

SELECT sid, serial#, username, status, machine, program, sql_id 
FROM v$session 
WHERE type = 'USER';

2. 查看当前执行的SQL语句

SELECT s.sid, s.serial#, s.username, s.status, 
       q.sql_text, q.sql_id, s.machine, s.program
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.type = 'USER';

3. 查看锁等待情况

SELECT l.session_id, s.serial#, s.username, s.osuser, 
       l.oracle_username, l.locked_mode, o.object_name
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid;

SQL性能分析语句

4. 查看执行计划

EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE your_condition;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

5. 查看SQL执行统计信息

SELECT sql_id, executions, elapsed_time/1000000 total_elapsed_sec,
       elapsed_time/executions/1000000 avg_elapsed_sec,
       buffer_gets, disk_reads
FROM v$sqlarea
WHERE executions > 0
ORDER BY elapsed_time DESC;

6. 查找高消耗SQL

SELECT sql_id, executions, round(elapsed_time/1000000,2) elapsed_sec,
       round(elapsed_time/executions/1000000,4) avg_elapsed_sec,
       buffer_gets, disk_reads
FROM v$sqlarea
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

资源使用情况

7. 查看表空间使用情况

SELECT tablespace_name, 
       round(SUM(bytes)/1024/1024,2) total_mb,
       round(SUM(bytes)/1024/1024 - SUM(NVL(maxbytes,bytes))/1024/1024,2) used_mb,
       round(SUM(NVL(maxbytes,bytes))/1024/1024,2) max_mb,
       round((SUM(bytes)/1024/1024 - SUM(NVL(maxbytes,bytes))/1024/1024) / 
             (SUM(bytes)/1024/1024)*100,2) pct_used
FROM dba_data_files
GROUP BY tablespace_name;

8. 查看内存使用情况

SELECT pool, name, bytes/1024/1024 size_mb
FROM v$sgastat
ORDER BY bytes DESC;

9. 查看I/O性能

SELECT name, phyrds, phywrts, phyblkrd, phyblkwrt,
       phyrds/seconds_in_wait phyrds_per_sec,
       phywrts/seconds_in_wait phywrts_per_sec
FROM v$filestat fs, v$datafile df, v$database d
WHERE fs.file# = df.file#;

索引性能分析

10. 查找未使用的索引

SELECT owner, index_name, table_name, monitoring, used
FROM v$object_usage
WHERE used = 'NO';

11. 索引使用统计

SELECT index_name, table_name, clustering_factor, 
       distinct_keys, num_rows, blevel, leaf_blocks
FROM user_indexes
ORDER BY clustering_factor DESC;

性能优化建议

12. 生成SQL调优报告

-- 需要先获取SQL_ID
SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id => 'your_sql_id',
  type => 'TEXT',
  level => 'TYPICAL') AS report
FROM dual;

13. 查看等待事件

SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;

14. 查看AWR报告关键指标

SELECT snap_id, begin_interval_time, end_interval_time,
       db_time, db_cpu, executions_delta, parse_calls_delta
FROM dba_hist_sysmetric_summary
ORDER BY snap_id DESC;

这些SQL语句可以帮助您全面了解Oracle数据库的性能状况，识别瓶颈，并进行针对性的优化。根据具体的测试场景和需求，您可能需要调整或组合使用这些语句。