Shulou(Shulou.com)06/01 Report--

Query the table in the locked table

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_idAND l.session_id = s. SIDORDER BY SID, s.serial#

Delete this record locked by the system

ALTER SYSTEM KILL SESSION 'SID,serial#'

Sql with the slowest query

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_textFROM ( SELECT sql_text, disk_reads, DENSE_RANK () OVER (ORDER BY disk_reads DESC) disk_reads_rank FROM v$sql )WHERE disk_reads_rank 0AND ROWNUM_ 100000ORDER 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_TEXTFROM V$SQLAREAWHERE EXECUTIONS >

0AND BUFFER_GETS > 0AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS

< 0 性能最差SQL SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls, sql_textFROM V$SQLAREAWHERE buffer_gets >

10000000OR disk_reads > 1000000ORDER BY buffer_gets + 100 * disk_reads DESC

View tablespace

SELECT Upper (F.TABLESPACE_NAME) "tablespace name", D.TOT_GROOTTE_MB "tablespace size (M)", D.TOT_GROOTTE_MB-F.TOTAL_BYTES "used space (M)", To_char (Round ((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2) '990.99') | |'%'"usage ratio", F.TOTAL_BYTES "Free Space (M)", F.MAX_BYTES "maximum Block (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) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.