oracle性能sql未验证.docx
- 文档编号:23299059
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:6
- 大小:17.11KB
oracle性能sql未验证.docx
《oracle性能sql未验证.docx》由会员分享,可在线阅读,更多相关《oracle性能sql未验证.docx(6页珍藏版)》请在冰豆网上搜索。
oracle性能sql未验证
性能分析SQL
zhouwf0726|15八月,200610:
03
--用于查看哪些实例的哪些操作使用了大量的临时段
SELECTto_number(decode(SID,65535,NULL,SID))sid,
operation_typeOPERATION,trunc(EXPECTED_SIZE/1024)ESIZE,
trunc(ACTUAL_MEM_USED/1024)MEM,trunc(MAX_MEM_USED/1024)"MAXMEM",
NUMBER_PASSESPASS,trunc(TEMPSEG_SIZE/1024)TSIZE
FROMV$SQL_WORKAREA_ACTIVE
ORDERBY1,2;
---查询有热块查询的SQL语句
selecthash_value
fromv$sqltexta,
(selectdistincta.owner,a.segment_name,a.segment_typefrom
dba_extentsa,
(selectdbarfil,dbablk
from(selectdbarfil,dbablk
fromx$bhorderbytchdesc)whererownum<11)b
wherea.RELATIVE_FNO=b.dbarfil
anda.BLOCK_ID<=b.dbablkanda.block_id+a.blocks>b.dbablk)b
wherea.sql_textlike'%'||b.segment_name||'%'andb.segment_type='TABLE'
orderby a.hash_value,a.address,a.piece;
--全表扫描
selectopname,target,b.num_rows,b.tablespace_name,count(target)fromv$session_longopsa,all_all_tablesb
wherea.TARGET=b.owner||'.'||b.table_name
havingcount(target)>10groupby opname,target,b.num_rows,b.tablespace_name
--查看磁盘排序和缓存排序次数
select to_char(sn.snap_time,'yyyy-mm-ddhh24')time_,
avg(newmen.value-oldmen.value)sorts_memeory,
avg(newdsk.value-olddsk.value)disk_sort
from stats$sysstatoldmen,
stats$sysstatnewmen,
stats$sysstatnewdsk,
stats$sysstatolddsk,
stats$snapshotsn
where newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmen.snap_id=sn.snap_id
and newdsk.snap_id=sn.snap_id-1
and oldmen.name='sorts(memory)'
and newmen.name='sorts(memory)'
and olddsk.name='sorts(disk)'
and newdsk.name='sorts(disk)'
groupby to_char(sn.snap_time,'yyyy-mm-ddhh24')
--执行最慢的前10个SQL?
?
?
select*from(
select
to_char(snap_time,'ddMonHH24:
mi:
ss')mydate,
executions exec,
loads loads,
parse_calls parse,
disk_reads reads,
buffer_gets gets,
rows_processed rows_proc,
sorts sorts,
sql_text,
hash_value
from
perfstat.stats$sql_summarysql,
perfstat.stats$snapshot sn
where
sql.snap_id>
(selectmin(snap_id)min_snap
fromstats$snapshotwheresnap_time>sysdate-$days_back)
and
sql.snap_id=sn.snap_id
orderby$sortskeydesc) ttwhererownum<11;
--SQL缓存池的命中率查询(pinhitratio,gethitratio应该大于90%以上)
selectnamespace,gethitratio,pinhitratio,reloads,invalidations
fromv$librarycache
wherenamespacein('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER')
--数据库的常规参数我就不说了,除了V$parameter中的常规参数外,ORACLE还有大量的隐含参数,下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。
SELECTNAME
,VALUE
,decode(isdefault,'TRUE','Y','N')as"Default"
,decode(ISEM,'TRUE','Y','N')asSesMod
,decode(ISYM,'IMMEDIATE','I',
'DEFERRED','D',
'FALSE','N')asSysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N')asModified
,decode(IADJ,'TRUE','Y','N')asAdjusted
,description
FROM(--GV$SYSTEM_PARAMETER
SELECTx.inst_idasinstance
,x.indx+1
,ksppinmasNAME
,ksppity
,ksppstvlasVALUE
,ksppstdfasisdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')asISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE')asISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE')asIMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE')asIADJ
,ksppdescasDESCRIPTION
FROMx$ksppix
,x$ksppsvy
WHEREx.indx=y.indx
ANDsubstr(ksppinm,1,1)='_'
ANDx.inst_id=USERENV('Instance')
)
ORDERBYNAME
--想知道现在哪个用户正在利用临时段吗?
这个语句将告诉你哪个用户正在利用临时段。
SELECTb.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser,a.status,c.sql_text
FROMv$sessiona,v$sort_usageb,v$sqlc
WHEREa.saddr=b.session_addr
ANDa.sql_address=c.address(+)
ORDERBYb.tablespace,b.segfile#,b.segblk#,b.blocks;
--查看磁盘碎片
selecttablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks))))FSFI
fromdba_free_space
groupbytablespace_nameorderby1
1.查看表空间的名称及大小
selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size
fromdba_tablespacest,dba_data_filesd
wheret.tablespace_name=d.tablespace_name
groupbyt.tablespace_name;
2.查看表空间物理文件的名称及大小
selecttablespace_name,file_id,file_name,
round(bytes/(1024*1024),0)total_space
fromdba_data_files
orderbytablespace_name;
3.查看回滚段名称及大小
selectsegment_name,tablespace_name,r.status,
(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,
max_extents,v.curextCurExtent
Fromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn(+)
orderbysegment_name
15。
耗资源的进程(topsession)
selects.schemanameschema_name, decode(sign(48-command),1,
to_char(command),'ActionCode#'||to_char(command))action, status
session_status, s.osuseros_user_name, s.sid, p.spid, s.serial#serial_num,
nvl(s.username,'[Oracleprocess]')user_name, s.terminalterminal,
s.programprogram, st.valuecriteria_value fromv$sesstatst, v$sessions ,v$processp
wherest.sid=s.sidand st.statistic#=to_number('38')and ('ALL'='ALL'
ors.status='ALL')andp.addr=s.paddrorderbyst.valuedesc, p.spidasc,s.usernameasc,s.osuserasc
16。
查看锁(lock)情况
select/*+RULE*/ls.osuseros_user_name, ls.usernameuser_name,
decode(ls.type,'RW','Rowwaitenqueuelock','TM','DMLenqueuelock','TX',
'Transactionenqueuelock','UL','Usersuppliedlock')lock_type,
o.object_nameobject, decode(ls.lmode,1,null,2,'RowShare',3,
'RowExclusive',4,'Share',5,'ShareRowExclusive',6,'Exclusive',null)
lock_mode, o.owner, ls.sid, ls.serial#serial_num, ls.id1, ls.id2
fromsys.dba_objectso,( selects.osuser, s.username, l.type,
l.lmode, s.sid, s.serial#, l.id1, l.id2 fromv$sessions,
v$lockl wheres.sid=l.sid)ls whereo.object_id=ls.id1and o.owner
<>'SYS' orderbyo.owner,o.object_name
--查看低效率的SQL语句
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 性能 sql 验证