Oracle常用的诊断语句

1.数据池命中率 --db_pool命中率(要求：95%左右)： select 100 - 100 * ((select value from v$sysstat where name = ‘physical reads‘)) / ((select value from v$sysstat where name = ‘consistent gets‘) + (select value from v$sysstat where name = ‘db block gets‘)) from dual; select * from V$PARAMETER where name in (‘sga_max_size‘, ‘db_cache_size‘, ‘shared_pool_size‘, ‘pga_aggregate_target‘); --2.查找前十条性能差的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; SELECT sql_text, hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100 * disk_reads DESC; --3.根据unix上Top命令看到的PID,查找对应的SQl SELECT P.pid pid, S.sid sid, P.spid spid, S.username username, S.osuser osname, P.serial# S_#, P.terminal, P.program program, P.background, S.status, RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL FROM v$process P, v$session S, v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address(+) AND P.spid LIKE ‘%CPU最高的进程对应的PID%‘; --4.察看IO情况： select df.name 文件名, fs.phyrds 读次数, fs.phywrts 写次数, (fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 读时间, (fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 写时间 from v$datafile df, v$filestat fs where df.file#=fs.file# order by df.name; --5.表空间察看 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 4 DESC; -- 数据表总行数排序 select t.table_name, t.num_rows, t.* from user_tables t where t.num_rows > 0 order by t.num_rows desc -- 普通表数据大小排序 select segment_name, trunc(bytes/1024/1024) from user_segments where segment_type = ‘TABLE‘ order by bytes desc; -- 含LOB类型表的数据大小 SELECT A.TABLE_NAME, A.COLUMN_NAME, trunc(B.BYTES / 1024 / 1024), B.SEGMENT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME, B.BLOCKS, B.EXTENTS FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ORDER BY B.BYTES DESC; -- 数据表总的数据大小占用 select rowSize + nvl(lobSize, 0) dataSize, t1.*, t2.* from ( select segment_name, trunc(bytes/1024/1024) rowSize from user_segments where segment_type = ‘TABLE‘ ) t1 left join ( SELECT A.TABLE_NAME, A.COLUMN_NAME, trunc(B.BYTES / 1024 / 1024) lobSize, B.SEGMENT_NAME, B.SEGMENT_TYPE, B.TABLESPACE_NAME, B.BLOCKS, B.EXTENTS FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ) t2 on t1.segment_name = t2.table_name order by rowSize + nvl(lobSize, 0) desc --6.运行时间很长的SQL select username, sid, opname, round(sofar * 100 / totalwork, 0) || ‘%‘ as progress, time_remaining, sql_text from v$session_longops, v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value; --7.锁相关的查询 select ‘kill -9 ‘||PS.SPID, ‘alter system kill session ‘‘‘||vs.sid||‘,‘||vs.serial#||‘‘‘;‘, /* DECODE(V$LOCK.TYPE, ‘TM‘, ‘TABLE LOCK‘, ‘TX‘, ‘ROW LOCK‘, NULL) LOCK_LEVEL,*/ Decode(VL.LOCKED_MODE,0,‘[0] none‘, 1,‘[1] null 空‘, 2,‘[2] Row-S 行共用(RS)：共用表鎖，sub share ‘, 3,‘[3] Row-X 行獨佔(RX)：用於行的修改，sub exclusive ‘, 4,‘[4] Share 共用鎖(S)：阻止其他DML操作，share‘, 5,‘[5] S/Row-X 共用行獨佔(SRX)：阻止其他事務操作，share/sub exclusive ‘, 6,‘[6] exclusive 獨佔(X)：獨立訪問使用，exclusive ‘, ‘[‘||VL.LOCKED_MODE||‘] Other Lock‘) LockMode, PS.SPID,OS_USER_NAME,VS.PROGRAM,VS.MACHINE,ORACLE_USERNAME,OBJECT_NAME,vs.LOGON_TIME , Vs.status,vs.MODULE from V$LOCKED_OBJECT VL ,DBA_OBJECTS OB ,V$SESSION VS ,v$process PS WHERE VL.OBJECT_ID = OB.OBJECT_ID AND VL.SESSION_ID = VS.SID AND Ps.ADDR = Vs.PADDR select ‘kill -9 ‘||PS.SPID, ‘alter system kill session ‘‘‘||vs.sid||‘,‘||vs.serial#||‘‘‘;‘, /* DECODE(V$LOCK.TYPE, ‘TM‘, ‘TABLE LOCK‘, ‘TX‘, ‘ROW LOCK‘, NULL) LOCK_LEVEL,*/ Decode(VL.LOCKED_MODE,0,‘[0] none‘, 1,‘[1] null 空‘, 2,‘[2] Row-S 行共用(RS)：共用表鎖，sub share ‘, 3,‘[3] Row-X 行獨佔(RX)：用於行的修改，sub exclusive ‘, 4,‘[4] Share 共用鎖(S)：阻止其他DML操作，share‘, 5,‘[5] S/Row-X 共用行獨佔(SRX)：阻止其他事務操作，share/sub exclusive ‘, 6,‘[6] exclusive 獨佔(X)：獨立訪問使用，exclusive ‘, ‘[‘||VL.LOCKED_MODE||‘] Other Lock‘) LockMode, PS.SPID,OS_USER_NAME,VS.PROGRAM,VS.MACHINE,ORACLE_USERNAME,OBJECT_NAME,vs.LOGON_TIME , Vs.status,vs.MODULE, vs.SQL_ID, st.SQL_TEXT from V$LOCKED_OBJECT VL ,DBA_OBJECTS OB ,V$SESSION VS ,v$process PS, v$sqlarea st WHERE VL.OBJECT_ID = OB.OBJECT_ID AND VL.SESSION_ID = VS.SID AND Ps.ADDR = Vs.PADDR and vs.SQL_ID = st.SQL_ID AND VS.USERNAME = ‘LC019999‘ select st.SQL_FULLTEXT, vs.* from v$session vs join v$sql st on vs.SQL_ID = st.SQL_ID where vs.USERNAME = ‘scott‘ and st.SQL_TEXT like ‘%%‘ --8.产生kill会话的Sql语句 select A.SID, B.SPID, A.SERIAL#, a.lockwait, A.USERNAME, A.OSUSER, a.logon_time, a.last_call_et / 3600 LAST_HOUR, A.STATUS, ‘orakill ‘ || sid || ‘ ‘ || spid HOST_COMMAND, ‘alter system kill session ‘‘‘ || A.sid || ‘,‘ || A.SERIAL# || ‘‘‘‘ SQL_COMMAND from v$session A, V$PROCESS B where A.PADDR = B.ADDR AND SID > 6; -- 最近10分钟最消耗CPU的SQL语句： select sql_text from ( select sql_id,count(*) as cn from v$active_session_history where sample_time > sysdate - 10/24/60 and session_type <> ‘BACKGROUND‘ and SESSION_STATE = ‘ON CPU‘ group by sql_id order by cn desc ) ash, v$sql s where ash.sql_id=s.sql_id; -- 最近10分钟最消耗IO的SQL语句： select sql_text from ( select sql_id,count(*) as cn from v$active_session_history where sample_time > sysdate - 10/24/60 and session_type <> ‘BACKGROUND‘ and WAIT_CLASS=‘User I/O‘ group by sql_id order by cn desc ) ash, v$sql s where ash.sql_id=s.sql_id;

  --清空缓存，Command window执行：
  Alter system flush shared_pool;

  --analyze table 更新统计信息：
  analyze table my_table compute statistics;

  --删除数据后，释放数据空间
  alter table my_table enable row movement;
  alter table my_table shrink space cascade;
  alter table my_table disable row movement;

获取指定SQL在内存中的执行计划：
select sql_id,child_number,sql_text from v$sql where sql_text like ‘select count(1) from emp a where a.dept_no =%‘;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id‘,0)); 

1、PL/SQL Developer 中使用F5
2、explain plan for select count(1) from emp a where a.dept_no=5;
     select * from table(dbms_xplan.display());
3、sqlplus中使用 set autotrace traceonly exp;

Oracle常用的诊断语句

标签：