Oracle常用的性能诊断语句

--2.查找前10条性能差的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
SELECT sql_text,hash_value,executions,buffer_gets,disk_reads,parse_calls
 FROM V$SQLAREA
WHERE buffer_gets > 10000000 OR disk_reads > 100000
ORDER BY buffer_gets + 100 * disk_reads DESC;


--附
SELECT EXECUTIONS,BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS) / BUFFER_GETS,2) Hit_radio,TRUNC(DISK_READS / EXECUTIONS) Reads_per_run,SQL_TEXT
FROM  V$SQLAREA 
WHERE EXECUTIONS > 0 AND DISK_READS > 0 and BUFFER_GETS > 0
    AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS < 0.8 
ORDER BY 5 DESC;



-- 查看某条SQL语句的资源消耗　
SELECT HASH_VALUE,PARSE_CALLS
  FROM  V$SQLAREA
 WHERE HASH_VALUE = 228801498
   AND ADDRESS = HEXTORAW('CBD8E4B0');


-- 运行时间很长的SQL（session_longops视图显示运行超过6秒的操作。包括备份，恢复，统计信息收集，查询等等）
select b.USERNAME,b.SID,b.SERIAL#,b.START_TIME,b.LAST_UPDATE_TIME,round(sofar * 100 / totalwork,0) || '%' as progress,a.sql_text 
from v$sqlarea a
     join v$session_longops b on a.SQL_ID = b.SQL_ID
--where b.TIME_REMAINING != 0        -- TIME_REMAINING：预计完成操作的剩余时间(秒) 
--  and b.USERNAME = 'sccot'


-- 最近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;


-- 补充1，根据时间找出响应时间较长的SQL：

   select t.SQL_TEXT,t.SQL_FULLTEXT,t.ELAPSED_TIME,t.LAST_LOAD_TIME,t.* 
   from v$sql t 


   where t.LAST_LOAD_TIME > to_char(sysdate - 10/(24*60),'yyyy-mm-dd/hh24:mi:ss') --and t.sql_text like '%LSHSXM%' 
   order by t.ELAPSED_TIME desc;

-- 补充2，查找硬解析严重的SQL：
select max(sql_id),substr(sql_text,0,100),count(1)
from v$sql 
where executions < 10 
group by substr(sql_text,100) 
having count(1) > 1000
order by count(1) desc;



--3.统计行数或空间占用较大的表
select   t.table_name,t.num_rows,t.last_analyzed,t.temporary,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,trunc(B.BYTES / 1024 / 1024) lobSize,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


补充，查询该表实际使用的blocks：
   SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) || DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) AS Used_Blocks 
   FROM table_name;



--4.查看Oracle内存参数配置  
select *
from   v$parameter t
where  t.NAME in (
              'memory_max_target','memory_target','sga_max_size','sga_target','pga_aggregate_target','cpu_count','db_cache_size','shared_pool_size','large_pool_size','java_pool_size','streams_pool_size','log_buffer','db_2k_cache_size','db_4k_cache_size','db_8k_cache_size','db_16k_cache_size','db_32k_cache_size'
       );


-- 查询SGA区的分配细节及使用情况
select t1.pool,t1.reserved_size_mb,t2.used_size_mb,t1.reserved_size_mb - t2.used_size_mb AS unused_size_mb
from (
       select t.POOL,trunc(sum(t.BYTES)/1024/1024) reserved_size_mb 
       from v$sgastat t 
       where t.POOL is not null 
       group by t.POOL
    ) t1
    left join (
      select t.POOL,trunc(sum(t.BYTES)/1024/1024) used_size_mb  
      from v$sgastat t 
      where t.pool is not null and t.name != 'free memory' 
      group by t.POOL
    ) t2 on t1.pool = t2.pool
union all
select t.name,trunc(sum(t.BYTES)/1024/1024) reserved_size_mb,null,null  
from v$sgastat t 
where t.pool is null 
group by t.NAME
order by  2 desc;


-- 查看系统的缓存命中率与软解析率         
SELECT 'Cache hit ratio' as name,(1-(physical_reads/(db_block_gets+consistent_gets)))*100 value 
FROM v$buffer_pool_statistics
union all  
select 'Soft parse ratio',100-100*(a.value/b.value) 
from v$sysstat a,v$sysstat b  
Where  a.name='parse count (hard)' and b.name='parse count (total)'; 





--5.查询锁表的语句
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] 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,NVL(currentSql.SQL_TEXT,prevSql.SQL_TEXT) as sql_text
from  V$LOCKED_OBJECT VL
      join DBA_OBJECTS OB on VL.OBJECT_ID = OB.OBJECT_ID
      join V$SESSION VS on VL.SESSION_ID = VS.SID 
      join v$process PS on PS.ADDR = VS.PADDR
      --left join v$sql currentSql on vs.SQL_HASH_VALUE = currentSql.HASH_VALUE and vs.SQL_ADDRESS = currentSql.ADDRESS
      --left join v$sql prevSql on vs.PREV_HASH_VALUE = prevSql.HASH_VALUE and vs.PREV_SQL_ADDR = prevSql.ADDRESS
      left join v$sql currentSql on vs.SQL_ID = currentSql.SQL_ID and vs.SQL_Child_Number = currentSql.Child_Number 
      left join v$sql prevSql on vs.Prev_SQL_ID = prevSql.Sql_Id and vs.PREV_Child_Number = prevSql.Child_Number
order by vs.logon_time;



--6.产生kill会话的语句 
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;





--7.查看IO情况
select 
    df.name 文件名,fs.phyrds 读次数,fs.phywrts 写次数,(fs.readtim/decode(fs.phyrds,-1,fs.phyrds)) 读时间,(fs.writetim/decode(fs.phywrts,fs.phywrts)) 写时间
from  v$datafile df,v$filestat fs
where df.file#=fs.file#
order by df.name;





--8.查看表空间情况
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;





--9.根据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%';





-- 数据库及实例的状态信息
select name,log_mode,open_mode,flashback_on,supplemental_log_data_min,platform_name from v$database;
select instance_number,instance_name,host_name,version,startup_time,status from v$instance;



--清空缓存(共享池和数据缓存)，Command window执行：
alter system flush shared_pool;
alter system flush buffer_cache;

--索引信息查看： 
select t.table_owner,t.index_name,t.blevel,t.leaf_blocks,t.distinct_keys,t.sample_size
from user_indexes t where t.table_name = upper('workitem');

--analyze table 更新统计信息：
analyze table my_table compute statistics; 
analyze index idx_name compute statistics;
--OR
EXEC DBMS_STATS.gather_table_stats(ownname => 'socct',tabname =>'WORKITEM',estimate_percent =>100,cascade =>true,method_opt => 'for all columns size auto');
/*
参数说明：
一、 estimate_percent ：抽样统计百分比
二、 cascade ：为 false 时,只对表进行统计分析；为 true 时,同时对表和索引进行统计分析 .
三、 method_opt ：
　 method_opt: 决定 histograms 信息是怎样被统计的 .method_opt 的取值如下 :
　　 for all columns: 统计所有列的 histograms.
　　 for all indexed columns: 统计所有 indexed 列的 histograms.
　　 for all hidden columns: 统计你看不到列的 histograms
　　 for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY
　　　　  SIZE ：统计指定列的 histograms.N 的取值范围 [1,254];
  　　　　REPEAT 上次统计过的 histograms;
　　　　  AUTO 由 oracle 决定 N 的大小 ;  
四、 degree ：并发性，可调值为， 8/16/24/32...
*/   


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

or
alter table table_name move; -- 执行此操作后，需要重建索引




select t.NUM_ROWS,t.TEMPORARY,t.* from user_tables t 
where t.TABLE_NAME = upper('gspauresult_tkk0107');


select t.num_rows,t.sample_size,t.* 
from user_indexes t 
where t.table_name = upper('tkk007');


select t.* from user_ind_columns t 
where t.TABLE_NAME = upper('gspauresult_tkk0107')
order by t.INDEX_NAME,t.COLUMN_POSITION;


select dbms_metadata.get_ddl('TABLE',upper('gspauresult_tkk0107')) from dual;
select dbms_metadata.get_ddl('INDEX',upper('idx_gspauresult_tkk0107')) from dual;
select dbms_metadata.get_ddl('CONSTRAINT',upper('tkk0107')) from dual;
select dbms_metadata.get_ddl('REF_CONSTRAINT',upper('tkk0107')) from dual;

select * from user_tables t order by dbms_random.random;



获取指定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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!