io tuning.txt

Oracle培训专题3-数据库配置.ppt Oracle培训专题4-备份和恢复.ppt Oracle培训专题5-浅谈调优.ppt

 --确定热点文件

  SELECT phyrds,phywrts,d.name,readtim,writetim
FROM v$datafile d, v$filestat f
 WHERE d.file#=f.file# order by d.name


select d.tablespace_name TABLESPACE, d.file_name, f.phyrds, f.phyblkrd,
f.readtim, f.phywrts, f.phyblkwrt, f.writetim
 from v$filestat f, dba_data_files d
 where f.file# = d.file_id
 order by tablespace_name, file_name;

--数据条带
ALTER TABLE tablename
ALLOCATE EXTENT (DATAFILE ’filename’ SIZE 10 M);\


--全表扫描
select * from v$session_longops;

--执行进度
SELECT sid, serial#, opname,
 TO_CHAR(start_time,‘HH24:MI:SS’)AS START,
 (sofar/totalwork)*100 AS PERCENT_COMPLETE
 FROM v$session_longops;

dbms_application_info.set_session_longops(rindex, slno,
"Operation X", obj, 0, sofar, totalwork, "table",
"tables");

SELECT name, value FROM v$sysstat
 WHERE name LIKE '%table scan%';
select disk.value "Disk", mem.value "Mem",
 (mem.value/(disk.value+mem.value))*100 "Ratio"
 from v$sysstat mem, v$sysstat disk
 where mem.name = 'sorts (memory)'
 and disk.name = 'sorts (disk)';

SQL> SELECT username, tablespace, contents, extents, blocks
2> FROM v$sort_usage;

SQL> select tablespace_name, current_users, total_extents,
2 used_extents, extent_hits, max_used_blocks,
3 max_sort_blocks
4 from v$sort_segment;


--回滚段使用命中率
SELECT sum(waits)* 100 /sum(gets) "Ratio",
 sum(waits) "Waits", sum(gets) "Gets"
 FROM v$rollstat; --小于1% 

--回滚块大小统计
select begin_time, end_time, undoblks,
 txncount, maxquerylen
 from v$undostat;
 
SELECT (RD * (UPS * OVERHEAD) + OVERHEAD)/1024/1024 AS "M Bytes"
  FROM (SELECT value AS RD FROM v$parameter WHERE name = 'undo_retention'),
       (SELECT (SUM(undoblks) / SUM(((end_time - begin_time) * 86400))) AS UPS
          FROM v$undostat),
       (SELECT value AS Overhead
          FROM v$parameter
         WHERE name = 'db_block_size');
 
 --回滚段冲突诊断
 Select Class,Count From V$WAITSTAT 
 where class like '%undo header%' 
 
 SELECT event, total_waits, total_timeouts
 FROM v$system_event
 WHERE event LIKE 'undo segment tx slot';
 
 SELECT sum(waits)* 100 /sum(gets) "Ratio",
 sum(waits) "Waits", sum(gets) "Gets"
 FROM v$rollstat;
 
 select * from v$rollname
 select * from v$rollstat

 select * from v$sysstat where name like '%roll%' or name like '%undo%'

--会话运行的事务
select s.SID,s.username,t.NAME, t.used_ublk,t.start_time,sa.SQL_TEXT
from v$transaction t,v$session s,v$sqlarea sa 
where s.TADDR=t.ADDR
and s.SQL_HASH_VALUE=sa.HASH_VALUE
and s.SQL_ADDRESS = sa.ADDRESS

select * from v$rollstat
Select * from dba_rollback_segs


select * from dba_temp_files