SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
2 FROM v$sysstat cur, v$sysstat con, v$sysstat phy
3 WHERE cur.name = 'db block gets'
4 AND con.name = 'consistent gets'
5 AND phy.name = 'physical reads'
6 /
高成本(10g-R2)sql：
select OPTIMIZER_COST,EXECUTIONS,sql_text from v$sqlarea
where OPTIMIZER_COST>
(select max(OPTIMIZER_COST)/5 from v$sqlarea)
order by 1;
高IO SQL:
select round(DISK_READS/EXECUTIONS) ,DISK_READS,EXECUTIONS,sql_text
from v$sqlarea
where round(DISK_READS/EXECUTIONS)>
(select max(round((DISK_READS/EXECUTIONS)/5)) from v$sqlarea
where EXECUTIONS>0)
and EXECUTIONS>0 and DISK_READS>100
order by 1;
参看频繁调度最多的5条sql语句（内存里）
select sql_text,executions from
(select sql_text,executions,rank() over (order by executions desc) exec_rank from v$sql) where exec_rank <=5;
查看表空间使用情况
set col colsep ' '
select dbf.tablespace_name,
dbf.totalspace as "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace as "剩余总量(M)",
dfs.freeblocks as "剩余块数",
round((dfs.freespace / dbf.totalspace) * 100,2)||'%' as "空闲比例%",
round((1-(dfs.freespace / dbf.totalspace)) * 100,2)||'%' as "已使用空间比例%"
from (select t.tablespace_name,
round(sum(t.bytes) / 1024 / 1024,2) totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
round(sum(tt.bytes) / 1024 / 1024,2) freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
;
set colsep ' ';
select dbf.tablespace_name,
dbf.totalspace as "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace as "剩余总量(M)",
dfs.freeblocks as "剩余块数",
concat(round((dfs.freespace / dbf.totalspace) * 100,2),'%') as "空闲比例%"
from (select t.tablespace_name,
round(sum(t.bytes) / 1024 / 1024,2) totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
round(sum(tt.bytes) / 1024 / 1024,2) freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
;
concat（字符串1，字符串2，....,字符串n）：此函数是字符串连接函数，用户连接字符串，其作用与连接符（||）完全相同。
迅速发现oracle server的性能问题的成因，我们可以求助于v$session_wait这个视图，看系统的这些session在等什么，使用了多少的io。以下是参考脚本：
脚本说明：查看占io较大的正在运行的session
select se.sid,
se.serial#,
pr.spid,
se.username,
se.status, se.terminal,
se.program,
se.module,
se.sql_address,
st.event,
st.p1text,
si.physical_reads,
si.block_changes
from v$session se,
v$session_wait st,
v$sess_io si,
v$process pr
where st.sid=se.sid
and st.sid=si.sid
and se.paddr=pr.addr
and se.sid>6
and st.wait_time=0
and st.event not like '%sql%'
order by physical_reads desc;
确定最耗CPU用户、语句
SET line 800
SET pages 100
col sid format 9990
col SERIAL# format 999990
col top_io_machine format a8
col top_io_program format a10
col top_io_sql format a50
SET colsep |
SELECT /*+ rule */
aa.sid AS "sid",
aa.serial# AS "SERIAL#",
aa.VALUE AS "CPU",
bb.VALUE AS "MEN-PGA",
cc.VALUE AS "I/O-Phy-read",
cc.machine AS top_io_machine,
cc.program AS top_io_program,
cc.sql_text AS top_io_sql
FROM (SELECT d.sql_text,
c.machine,
c.program,
c.sid,
c.serial#,
a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'CPU used by this session'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) aa,
(SELECT d.sql_text,
c.machine,
c.program,
c.sid,
c.serial#,
a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'session pga memory'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) bb,
(SELECT d.sql_text,
c.machine,
c.program,
c.sid,
c.serial#,
a.NAME,
b.VALUE
FROM v$statname a, v$sesstat b, v$session c, v$sqlarea d
WHERE NAME = 'physical reads'
AND a.statistic# = b.statistic#
AND c.sid = b.sid
AND c.status = 'ACTIVE'
AND d.address = c.sql_address) cc
WHERE aa.sid = bb.sid
AND aa.sid = cc.sid
AND aa.serial# = bb.serial#
AND aa.serial# = cc.serial#
ORDER BY 5 ASC, 4 ASC, 3 ASC;
下面给出一个综合的sql语句，它可以查询出浪费空间的表(浪费超过25%)，而且还计算出其它信息(使用时根据具体情况修改where子句中的blocks，owner限制条件)：
SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,
CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER and
SEGMENT_NAME = TABLE_NAME and
SEGMENT_TYPE = 'TABLE' AND
B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.TS$ C,
SYS.DBA_TABLES D
WHERE A.OWNER = B.TABLE_OWNER and
SEGMENT_NAME = B.TABLE_NAME and
SEGMENT_TYPE = 'TABLE PARTITION' AND
B.TABLESPACE_NAME = C.NAME AND
D.OWNER = B.TABLE_OWNER AND
D.TABLE_NAME = B.TABLE_NAME AND
A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)
MAX_FREE_SPACE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND WNER = '??' AND BLOCKS > 128
ORDER BY 10 DESC, 1 ASC, 2 ASC;
查看所有表空间的碎片程度（值在30以下表示碎片很多）
select tablespace_name,sum(bytes),sum(free),round(sum(free)*100/sum(bytes),2) from (select
b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id) group by tablespace_name order by sum(free)*100/sum(bytes);
对showsql.sql脚本的改进
很久没有更新博客了,主要是最近被博客上的垃圾评论整得心烦,眼不见心不烦,今天对showsql.sql脚本做了点更新,相信大家用起来更顺手,所以就发出来给大家做参考,本次改进减少了一个sql的调用,输出的结果就更准确了...
$ORACLE_HOME/bin/sqlplus -S /nolog <connect / as sysdba;
set serveroutput on size 1000000
set lines 200
set pages 1000
set feedback off
column username format a20
column sql_text format a98
declare
type tab_varchar2 is table of varchar2(128);
v_list tab_varchar2 := tab_varchar2();
procedure p (p_str in varchar2)
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line(substr(l_str, 1, 250));
l_str := substr(l_str, 251);
end loop;
end;
begin
for x in (select a.username||'('||a.sid||','||a.serial#||') spid='||b.spid||
' hash_value='||to_char(a.sql_hash_value)||' execs='||to_char(s.executions)||
' els_time='||to_char(trunc(elapsed_time/1000000/decode(executions,0,null,executions),2)) username,
' program='||a.program program,a.sid,a.serial#,
' disk_reads='||to_char(trunc(disk_reads/decode(executions,0,null,executions),2)) disk_reads,
' buffer_gets='||to_char(trunc(buffer_gets/decode(executions,0,null,executions),2)) buffer_gets,sql_address,sql_hash_value
from v$session a,v$process b,v$sqlarea s
where a.status = 'ACTIVE' and s.hash_value=a.sql_hash_value
and a.paddr = b.addr and rawtohex(sql_address) <> '00' and a.username is not null
and sid <> (select sid from v$mystat where rownum = 1) order by last_call_et)
loop
dbms_output.put_line( '--------------------------------------------------------------------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.program || ' ' ||x.disk_reads || ' '|| x.buffer_gets);
v_list.extend;
v_list(v_list.count) := 'alter system kill session '''||to_char(x.sid)||','||to_char(x.serial#)||''';';
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p(y.sql_text);
end loop;
--output sql execution plan
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in (select rpad('|'||substr(lpad(' ',1 * (depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----',
substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,
object_name)||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',decode(sign(cardinality-1000),
-1, cardinality||' ',decode(sign(cardinality-1000000), -1,
trunc(cardinality/1000)||'K',decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',decode(sign(bytes-1073741824),
-1, trunc(bytes/1048576)||'M',trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as Explain_plan
from v$sql_plan
where hash_value = x.sql_hash_value
and child_number = (select max(child_number) from v$sql_plan where hash_value = x.sql_hash_value))
loop
dbms_output.put_line(i.explain_plan);
end loop;
end loop;
--output kill session script
dbms_output.put_line( '----------------------------alter system kill session---------------------------' );
dbms_output.put_line( '--------------------------------------------------------------------------------' );
for i in 1..v_list.count loop
dbms_output.put_line(v_list(i));
end loop;
end;
/
exit
EOF
显示碎片show_space
Declare
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_lastusedextfileid number;
l_lastusedextblockid number;
l_last_used_block number;
begin
dbms_space.unused_space(segment_owner =>'&segment_owner',
segment_name =>'&segment_name',
segment_type =>'TABLE',
total_blocks =>l_total_blocks,
total_bytes =>l_total_bytes,
unused_blocks =>l_unused_blocks,
unused_bytes =>l_unused_bytes,
last_used_extent_file_id =>l_lastusedextfileid,
last_used_extent_block_id =>l_lastusedextblockid,
last_used_block =>l_last_used_block);
dbms_output.put_line(rpad('Total Blocks',40,'.')||l_total_blocks);
dbms_output.put_line(rpad('Total Bytes',40,'.')||l_total_bytes);
dbms_output.put_line(rpad('Unused Blocks',40,'.')||l_unused_blocks);
dbms_output.put_line(rpad('Unused Bytes',40,'.')||l_unused_bytes);
dbms_output.put_line(rpad('Last Used Ext FileId',40,'.')||l_lastusedextfileid);
dbms_output.put_line(rpad('Last Used Ext BlockId',40,'.')||l_lastusedextblockid);
dbms_output.put_line(rpad('Last Used Block',40,'.')||l_last_used_block);
end;
/
023课程中的一个实验，我的owner, segment_name, segment_type 都是大写，仍然出现下列错误：
DECLARE
v_owner VARCHAR2(30) := 'TT';
v_segment_name VARCHAR2(30) := 'TEST1';
v_segment_type VARCHAR2(30) := 'TABLE';
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
BEGIN
dbms_space.unused_space(v_owner,
v_segment_name,
v_segment_type,
v_total_blocks,
v_total_bytes,
v_unused_blocks,
v_unused_bytes,
v_last_used_extent_file_id,
v_last_used_extent_block_id,
v_last_used_block
);
dbms_output.put_line(INITCAP(v_segment_type)||':'||v_owner||'.'||v_segment_name);
dbms_output.put_line('Total Blocks :'||TO_CHAR(v_total_blocks));
dbms_output.put_line('Blocks above HWM :'||TO_CHAR(v_unused_blocks));
END;
/
-- logical_reads, physical_reads, physical_writes by segment (tablespace, table, object_type).
select n.owner , n.tablespace_name,
case when n.object_type = 'TABLE' then n.object_name
when n.object_type = 'INDEX' then (SELECT table_name from dba_indexes WHERE index_name = n.object_name AND wner = n.owner)
when n.object_type = 'LOB' then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name AND wner = n.owner)
else 'N/A' end as Table_name,
n.object_name, n.object_type, r.logical_reads,
round(r.logical_reads_ratio * 100, 2) logical_reads_ratio,
r.physical_reads,
round(r.physical_reads_ratio * 100, 2) physical_reads_ratio,
r.physical_writes,
round(r.physical_writes_ratio * 100, 2) physical_writes_ratio
from dba_hist_seg_stat_obj n,
(select *
from (select e.dataobj#, e.obj#, e.dbid,
sum(e.logical_reads_delta) logical_reads,
ratio_to_report(sum(e.logical_reads_delta)) over () logical_reads_ratio,
sum(e.physical_reads_delta) physical_reads,
ratio_to_report(sum(e.physical_reads_delta)) over () physical_reads_ratio,
sum(e.physical_writes_delta) physical_writes,
ratio_to_report(sum(e.physical_writes_delta)) over () physical_writes_ratio
from dba_hist_seg_stat e
where e.snap_id > 209 and
e.snap_id <= 210
group by e.dataobj#,
e.obj#,
e.dbid
having sum(e.logical_reads_delta) > 0 or
sum(e.physical_reads_delta) > 0 or
sum(e.physical_writes_delta) > 0
order by logical_reads desc) d
) r
where n.dataobj# = r.dataobj# and
n.obj# = r.obj# and
n.dbid = r.dbid and
n.owner = 'TT'
order by logical_reads desc;
select n.owner , n.tablespace_name,
case when n.object_type = 'TABLE' then n.object_name
when n.object_type = 'INDEX' then (SELECT table_name from dba_indexes WHERE index_name = n.object_name AND wner = n.owner)
when n.object_type = 'LOB' then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name AND wner = n.owner)
else 'N/A' end as Table_name,
n.object_name, n.object_type, r.logical_reads,
round(r.logical_reads_ratio * 100, 2) logical_reads_ratio,
r.physical_reads,
round(r.physical_reads_ratio * 100, 2) physical_reads_ratio,
r.physical_writes,
round(r.physical_writes_ratio * 100, 2) physical_writes_ratio
from dba_hist_seg_stat_obj n,
(select *
from (select e.dataobj#, e.obj#, e.dbid,
sum(e.logical_reads_delta) logical_reads,
ratio_to_report(sum(e.logical_reads_delta)) over () logical_reads_ratio,
sum(e.physical_reads_delta) physical_reads,
ratio_to_report(sum(e.physical_reads_delta)) over () physical_reads_ratio,
sum(e.physical_writes_delta) physical_writes,
ratio_to_report(sum(e.physical_writes_delta)) over () physical_writes_ratio
from dba_hist_seg_stat e
where e.snap_id > 209 and
e.snap_id <= 210
group by e.dataobj#,
e.obj#,
e.dbid
having sum(e.logical_reads_delta) > 0 or
sum(e.physical_reads_delta) > 0 or
sum(e.physical_writes_delta) > 0
order by logical_reads desc) d
) r
where n.dataobj# = r.dataobj# and
n.obj# = r.obj# and
n.dbid = r.dbid and
n.owner = 'MKTG'
order by logical_reads desc
创建一个表语句
create table test13
(id int,fname varchar2(18))
storage(initial 10k next 20k
pctincrease 0
minextents 1 maxextents 3)
pctfree 10 pctused 40 tablespace test;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9561680/viewspace-521314/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9561680/viewspace-521314/
148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
