查询处于锁表中的表
SELECT l.session_id SID, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time, s.serial# FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s. SID ORDER BY SID, s.serial#;
删除掉系统锁定的此记录
ALTER SYSTEM KILL SESSION 'SID,serial#';
查询最慢的sql
SELECT * FROM ( SELECT parsing_user_id, executions, sorts command_type, disk_reads, sql_text FROM v$sqlarea ORDER BY disk_reads DESC ) WHERE ROWNUM < 10
消耗磁盘读取最多的sql top5
SELECT disk_reads, sql_text FROM ( SELECT sql_text, disk_reads, DENSE_RANK () OVER (ORDER BY disk_reads DESC) disk_reads_rank FROM v$sql ) WHERE disk_reads_rank <= 5;
ORACLE分页查询
SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( SELECT * FROM TABLE_NAME ) ROW_ ) WHERE ROWNUM_ > 0 AND ROWNUM_ <= 5
ORACLE查询一行数据
SELECT * FROM ( SELECT * FROM TABLE_NAME ) A WHERE ROWNUM = 1
查询IO大于10000的SQL
SELECT b.username username, a.disk_reads READS, a.executions exec, a.disk_reads / decode( a.executions, 0, 1, a.executions ) rds_exec_ratio, a.sql_text statement FROM v$sqlarea a, dba_users b WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 100000 ORDER BY a.DISK_READS DESC;
解析时间大于执行时间
SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND( (BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2 ) Hit_radio, ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0
性能最差SQL
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls, sql_text FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC;
查看表空间
SELECT Upper(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES, Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。