Oracle工具sql
发布时间:2020-12-12 16:23:14 所属栏目:百科 来源:网络整理
导读:查询处于锁表中的表 SELECTl.session_idSID,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,s.terminal,o.object_name,s.logon_time,s.serial#FROMv$locked_objectl,all_objectso,v$sessionsWHEREl.object_id=o.object_idANDl.session_id=s.SIDO
查询处于锁表中的表 SELECT l.session_idSID,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_objectl,all_objectso,v$sessions WHERE l.object_id=o.object_id ANDl.session_id=s.SID ORDERBY SID,s.serial#; 删除掉系统锁定的此记录 ALTERSYSTEMKILLSESSION'SID,serial#'; 查询最慢的sql SELECT * FROM ( SELECT parsing_user_id,executions,sortscommand_type,disk_reads,sql_text FROM v$sqlarea ORDERBY disk_readsDESC ) WHERE ROWNUM<10 消耗磁盘读取最多的sqltop5 SELECT disk_reads,sql_text FROM ( SELECT sql_text,DENSE_RANK()OVER(ORDERBYdisk_readsDESC)disk_reads_rank FROM v$sql ) WHERE disk_reads_rank<=5; ORACLE分页查询 SELECT * FROM ( SELECT ROW_.*,ROWNUMROWNUM_ FROM ( SELECT*FROMTABLE_NAME )ROW_ ) WHERE ROWNUM_>0 ANDROWNUM_<=5 ORACLE查询一行数据 SELECT * FROM ( SELECT*FROMTABLE_NAME )A WHERE ROWNUM=1 查询IO大于10000的SQL SELECT b.usernameusername,a.disk_readsREADS,a.executionsexec,a.disk_reads/decode( a.executions,1,a.executions )rds_exec_ratio,a.sql_textstatement FROM v$sqlareaa,dba_usersb WHERE a.parsing_user_id=b.user_id ANDa.disk_reads>100000 ORDERBY a.DISK_READSDESC; 解析时间大于执行时间 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 ANDBUFFER_GETS>0 AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0 性能最差SQL SELECT hash_value,buffer_gets,parse_calls,sql_text FROM V$SQLAREA WHERE buffer_gets>10000000 ORdisk_reads>1000000 ORDERBY buffer_gets+100*disk_readsDESC; 查看表空间 SELECTUpper(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(SELECTTABLESPACE_NAME,Round(Sum(BYTES)/(1024*1024),2)TOTAL_BYTES,Round(Max(BYTES)/(1024*1024),2)MAX_BYTES FROMSYS.DBA_FREE_SPACE GROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,Round(Sum(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB FROMSYS.DBA_DATA_FILESDD GROUPBYDD.TABLESPACE_NAME)D WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME ORDERBY1; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |