oracle常用性能分析语句执行情况等语句总结

oralce查询当前执行耗资源的语句；查询Oracle正在执行的sql语句及执行该语句的用户；数据库等待时间查询；数据库锁表关系、源头等一系列锁表问题查询及解决；查看oracle数据库最近执行了哪些sql语句；查询oracle效率底下的语句；oracle查询最占用资源的查询；oracle从磁盘、使用频率、缓冲等分析性能语句； Oracle数据库是世界上最广泛使用的数据库管理系统之一，特别是在企业级应用中，其性能优化和问题排查显得尤为重要。本篇文章将总结一些常用的Oracle性能分析和查询语句，帮助管理员了解系统运行状况，定位并解决性能瓶颈。 要查询当前执行耗资源的SQL语句，可以使用以下查询： ```sql SELECT t.*, s.sid, s.serial#, s.machine, s.program, s.osuser FROM ( SELECT c.USERNAME, a.event, to_char(a.cnt) as seconds, a.sql_id, dbms_lob.substr(b.sql_fulltext,3500,1) sqltext FROM ( SELECT rownum rn, t.* FROM ( SELECT decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu') Event, s.sql_id, s.user_id, count(*) CNT FROM v$active_session_history s WHERE sample_time > sysdate - 30 / 1440 GROUP BY s.user_id, decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu'), s.sql_id ORDER BY CNT desc ) t WHERE rownum < 20 ) a, v$sqlarea b, dba_users c WHERE a.sql_id = b.sql_id AND a.user_id = c.user_id ORDER BY CNT desc ) t, v$session s WHERE t.sql_id = s.sql_id(+); ``` 这个查询返回了过去30分钟内最消耗资源的SQL语句及其执行的用户、会话ID、机器名和程序信息。 要查找Oracle正在执行的SQL语句以及执行这些语句的用户，可以执行： ```sql SELECT b.sid oracleID, b.username 登录Oracle用户名, b.serial#, spid 操作系统ID, paddr, sql_text 正在执行的SQL, b.machine 计算机名 FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value; ``` 此外，可以查看正在执行SQL的发起者信息，包括其电脑登录身份、发起请求的程序、登录系统的用户名、模式名称、CPU时间、状态和执行的SQL： ```sql SELECT OSUSER 电脑登录身份, PROGRAM 发起请求的程序, USERNAME 登录系统的用户名, SCHEMANAME, B.Cpu_Time 花费cpu的时间, STATUS, B.SQL_TEXT 执行的sql FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE ORDER BY b.cpu_time DESC; ``` 针对数据库等待时间的查询，可以通过`v$session_wait`视图来获取，它提供了当前会话的等待事件详情。 对于锁表问题，可以查询`v$lock`和`dba_objects`来找出被锁定的对象： ```sql SELECT l.session_id sid, s.serial#, o.object_name, l.type FROM v$lock l, dba_objects o, v$session s WHERE l.id1 = o.object_id AND l.type IN ('TX', 'TM') AND s.sid = l.sid; ``` 查看Oracle数据库最近执行的SQL语句，可以利用`v$sql`视图： ```sql SELECT * FROM v$sql ORDER BY last_executed DESC; ``` 分析性能语句时，还可以关注SQL的缓冲、磁盘I/O等指标。例如，使用`db_file_multiblock_read_count`统计多块读取的次数，或者通过`v$sysmetric`和`v$session_longops`来观察数据库的长期操作。 通过上述查询和分析，数据库管理员可以有效地监控Oracle数据库的性能，找出低效的SQL语句，定位资源争抢和锁问题，从而进行性能调优，确保系统的稳定和高效运行。在实际操作中，还需要结合数据库设计、索引优化、SQL改写等多种手段，全面提升Oracle数据库的性能。