分享
 
 
 

分析数据库性能的SQL

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

--用于查看哪些实例的哪些操作使用了大量的临时段

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,

operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,

trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",

NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE

FROM V$SQL_WORKAREA_ACTIVE

ORDER BY 1,2;

---查询有热块查询的SQL语句

select hash_value

from v$sqltext a,

(select distinct a.owner,a.segment_name,a.segment_type from

dba_extents a,

(select dbarfil,dbablk

from (select dbarfil,dbablk

from x$bh order by tch desc) where rownum < 11) b

where a.RELATIVE_FNO = b.dbarfil

and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks b.dbablk) b

where a.sql_text like '%'b.segment_name'%' and b.segment_type = 'TABLE'

order bya.hash_value,a.address,a.piece;

--全表扫描

select opname,target,b.num_rows,b.tablespace_name,count(target) from v$session_longops a,all_all_tables b

where a.TARGET=b.owner'.'b.table_name

having count(target)10 group by opname,target,b.num_rows,b.tablespace_name

--查看磁盘排序和缓存排序次数

selectto_char(sn.snap_time,'yyyy-mm-dd hh24') time_,

avg(newmen.value - oldmen.value) sorts_memeory,

avg(newdsk.value - olddsk.value) disk_sort

fromstats$sysstat oldmen,

stats$sysstat newmen,

stats$sysstat newdsk,

stats$sysstat olddsk,

stats$snapshot sn

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)'

group byto_char(sn.snap_time,'yyyy-mm-dd hh24')

--执行最慢的前10个SQL???

select * from (

select

to_char(snap_time,'dd Mon HH24:mi:ss') mydate,

executions exec,

loadsloads,

parse_callsparse,

disk_reads reads,

buffer_getsgets,

rows_processed rows_proc,

sortssorts,

sql_text,

hash_value

from

perfstat.stats$sql_summary sql,

perfstat.stats$snapshot sn

where

sql.snap_id

(select min(snap_id) min_snap

from stats$snapshot where snap_time sysdate-$days_back)

and

sql.snap_id = sn.snap_id

order by $sortskey desc)tt where rownum<11;

--SQL缓存池的命中率查询(pinhitratio,gethitratio应该大于90%以上)

select namespace,gethitratio,pinhitratio,reloads,invalidations

from v$librarycache

where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')

--数据库的常规参数我就不说了,除了V$parameter中的常规参数外,Oracle还有大量的隐含参数,下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。

SELECT NAME

,VALUE

,decode(isdefault, 'TRUE','Y','N') as "Default"

,decode(ISEM,'TRUE','Y','N') as SesMod

,decode(ISYM,'IMMEDIATE', 'I',

'DEFERRED', 'D',

'FALSE', 'N') as SysMod

,decode(IMOD,'MODIFIED','U',

'SYS_MODIFIED','S','N') as Modified

,decode(IADJ,'TRUE','Y','N') as Adjusted

,description

FROM ( --GV$SYSTEM_PARAMETER

SELECT x.inst_id as instance

,x.indx+1

,ksppinm as NAME

,ksppity

,ksppstvl as VALUE

,ksppstdf as isdefault

,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM

,decode(bitand(ksppiflg/65536,3),

1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM

,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD

,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ

,ksppdesc as DESCRIPTION

FROM x$ksppi x

,x$ksppsv y

WHERE x.indx = y.indx

AND substr(ksppinm,1,1) = '_'

AND x.inst_id = USERENV('Instance')

)

ORDER BY NAME

--想知道现在哪个用户正在利用临时段吗?这个语句将告诉你哪个用户正在利用临时段。

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,

a.username, a.osuser, a.status,c.sql_text

FROM v$session a,v$sort_usage b, v$sql c

WHERE a.saddr = b.session_addr

AND a.sql_address = c.address(+)

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

--查看磁盘碎片

select tablespace_name,sqrt(max(blocks)/sum(blocks))*

(100/sqrt(sqrt(count(blocks)))) FSFI

from dba_free_space

group by tablespace_name order by 1

1.查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

2.查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

3.查看回滚段名称及大小

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name

15。耗资源的进程(top session)

select s.schemaname schema_name,decode(sign(48 - command), 1,

to_char(command), 'Action Code #' to_char(command) ) action,status

session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,

nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,

s.program program, st.value criteria_valuefrom v$sesstat st, v$session s, v$process p

where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'

or s.status = 'ALL') and p.addr = s.paddr order by st.value desc,p.spid asc, s.username asc, s.osuser asc

16。

查看锁(lock)情况

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,

decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,

'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

lock_mode,o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2

from sys.dba_objects o, ( select s.osuser, s.username, l.type,

l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,

v$lock l where s.sid = l.sid ) lswhere o.object_id = ls.id1 ando.owner

< 'SYS' order by o.owner, o.object_name

--查看低效率的SQL语句

SELECT EXECUTIONS , 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

WHEREEXECUTIONS0

AND BUFFER_GETS 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有