oracle优化中的常用语句

来源：互联网发布：淘宝违规不知道为什么编辑：程序博客网时间：2024/06/06 13:17

查看session使用数？（或者称建立的连接数）

select count(*) from v$session;

1.2 查看oracle锁

SELECT substr(v$lock.sid,1,4) "SID",

substr(username,1,12) "UserName",osuser,machine,

substr(object_name,1,25) "ObjectName",

v$lock.type "LockType",

decode(rtrim(substr(lmode,1,4)),

'2','Row-S (SS)','3','Row-X (SX)',

'4','Share', '5','S/Row-X (SSX)',

'6','Exclusive', 'Other' ) "LockMode",

substr(v$session.program,1,25) "ProgramName"

FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION

WHERE (OBJECT_ID = v$lock.id1

AND v$lock.sid = v$session.sid

AND username IS NOT NULL

AND username NOT IN ('SYS','SYSTEM')

AND SERIAL# != 1);

1.3 数据库分析

语句 execute DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE',10000,NULL,'');

1.4 查看表统计数据

select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed from all_tables where owner='RMS'

分析单表 analyze table rms.user_port compute statistics

1.5 查看索引统计数据

select index_name,blevel,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,last_analyzed from all_indexes where owner='RMS';

1.6 生成单表分析语句

select 'analyze table rms.'||segment_name||' compute statistics ;'segment_name,sum(bytes)/1024/1024 MB from user_segments,all_tables

where all_tables.owner='RMS' and all_tables.table_name= segment_name GROUP BY segment_name

1.7 发现表链接行

语句 select table_name,chain_cnt from user_tables order by chain_cnt desc

select table_name,chain_cnt from all_tables where owner='RMS' order by chain_cnt desc

1.8 计算高速缓存命中率

语句 "select 1-(phy.value/(cur.value+con.value)) from v$sysstat cur,v$sysstat con,v$sysstat phy

where cur.name='db block gets' and con.name='consistent gets'

and phy.name='physical reads'"

db block gets 4709479237

consistent gets 22146942

physical reads 8931785

命中率 0.998112322 1-physical reads/(db block gets+consistent gets)

建议达到0.90或以上认为是好的

1.9 找出大量消耗Buffer Cache的对象

" column c0 heading 'Owner' format a15

column c1 heading 'Object|Name' format a30

column c2 heading 'Number|of|Buffers' format 999,999

column c3 heading 'Percentage|ofData|Buffer' format 999,999,999

select

owner c0,

object_name c1,

count(1) c2,

(count(1)/(select count(*) from v$bh)) *100 c3

from

dba_objects o,

v$bh bh

where

o.object_id = bh.objd

and

o.owner not in ('SYS','SYSTEM')

group by

owner,

object_name

order by

count(1) desc

1.10计算库缓存命中率

语句 SELECT SUM(pins-reloads)/SUM(pins) from v$librarycache

建议低于0.99需要试着改善它，可以增加共享池的规模来改善它，如果有很多可用空间那就得从改善我们的程序

查看当前未使用的共享池 "select to_number(v$parameter.value) value, v$sgastat.BYTES,

(v$sgastat.bytes/v$parameter.value)*100 ""percent free""

from v$sgastat, v$parameter

where v$sgastat.name= 'free memory'

and v$parameter.name = 'shared_pool_size'

and v$sgastat.pool='shared pool'"

1.11手工方式清空共享池

ALTER SYSTEM FLUSH SHARED_POOL;

1.12计算字典缓存命中率

语句 select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache

建议达到0.85或以上认为是可以接受的

1.13检查回滚段争用

语句 select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat

建议大于1%需要处理，可以通过更多回滚段来降低争用

查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。

select s.username, u.name from v$transaction t,v$rollstat r,

v$rollname u,v$session s where s.taddr=t.addr and

t.xidusn=r.usn and r.usn=u.usn order by s.username;

"SELECT a.name , b.xacts ""活动事务个数"", b.writes ""写入的字节数"", b.extents as ""区个数""

FROM v$rollname a, v$rollstat b

WHERE a.usn=b.usn;"

1.14检查回滚段收缩

语句 select name,shrinks from v$rollstat,v$rollname where V$rollstat.usn=v$rollname.usn

1.15redo日志缓存器

select name,value from v$sysstat where name ='redo buffer allocation retries'

1.16生成删除约束脚本

select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';' from all_constraints where owner='RMS' AND CONSTRAINT_TYPE<>'P'

发现表链接行

select table_name,chain_cnt from user_tables order by chain_cnt desc;

1.17查看表空间情况

"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"

1.18合并表空间

alter tablespace trmsdata coalesce;

1.19查看表占用空间数

"select segment_name,sum(bytes)/1024/1024 MB from user_segments,all_tables

where all_tables.owner='RMS' and all_tables.table_name= segment_name GROUP BY segment_name"

1.20查看索引占用空间数

"select segment_name,sum(bytes)/1024/1024 MB from user_segments, all_indexes

where all_indexes.owner='RMS' and all_indexes.index_name= segment_name GROUP BY segment_name

1.21查看系统内存使用

swapinfo -atm HP UNIX内核参数 bufpages 61992 缓冲页 dbc_max_pct 10 动态缓存占内存最大百分比 dbc_min_pct 10 动态缓存占内存最小百分比

1.22查看session使用的内存

select c.sid,c.OSUSER,c.MACHINE,name,value from v$statname n,v$sesstat s,v$session c

1.23找最耗资源的session

"SELECT ses.sid

, DECODE(ses.action,NULL,'online','batch') ""User""

, MAX(DECODE(sta.statistic#,9,sta.value,0))

/greatest(3600*24*(sysdate-ses.logon_time),1) ""Log IO/s""

, MAX(DECODE(sta.statistic#,40,sta.value,0))

/greatest(3600*24*(sysdate-ses.logon_time),1) ""Phy IO/s""

, 60*24*(sysdate-ses.logon_time) ""Minutes""

FROM V$SESSION ses

, V$SESSTAT sta

WHERE ses.status = 'ACTIVE'

AND sta.sid = ses.sid

AND sta.statistic# IN (9,40)

GROUP BY ses.sid, ses.action, ses.logon_time

ORDER BY

SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )

/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;"

1.24查找前十条性能差的sql

"SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,

COMMAND_TYPE,DISK_READS,sql_text.address FROM v$sqlarea

order BY disk_reads DESC )where ROWNUM<10 ;"

1.25查出SQL的查询计划

"select lpad(' ', 2*(level-1))||operation ""Operation"",

options ""Options"",

decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) ""Object Name"",

substr(optimizer, 1, 6) ""Optimizer""

from v$sql_plan a

start with address = 'C0000000FCCDEDA0'

and id = 0

connect by prior id = a.parent_id

and prior a.address = a.address

and prior a.hash_value = a.hash_value;"

1.26查看oracle系统进程

select * from V$PROCESS spid为系统进程ID

1.27查看进程正在执行的SQL

"SELECT a.username,a.machine,a.program,a.sid,a.serial#,

a.status,c.piece,c.sql_text from v$session a,v$process b,

v$sqltext c WHERE b.spid='ORCL' AND b.addr=a.paddr AND

a.sql_address=c.address(+)order BY c.piece"

1.28查看占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>8 AND st.

wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC"

1.29查看session正在执行的SQL

select sql_text,piece

from v$sqltext

where (address,hash_value) in ( select decode(sql_hash_value,0,prev_sql_addr,sql_address) ,

decode(sql_hash_value,0,prev_hash_value,sql_hash_value)

from v$session

where sid =&sid)

order by piece

1.30脏缓冲回写到磁盘去

alter system set events = 'immediate trace name flush_cache';