oralc 监控

Temp表空间上进程的查询

?

1

2

3

4

5

selecta.tablespace, b.sid, b.serial#, a.blocks,c.sql_text

  fromv$sort_usage a,v$session b,v$sqltext c

 wherea.session_addr = b.saddr

   andb.sql_address = c.address

 orderby a.tablespace,b.sid,b.serial#,c.address, c.piece;

点击打开链接

查看表锁

?

1

select* fromsys.v_$sqlarea wheredisk_reads>100;

监控事例的等待

?

1

2

3

4

selectevent,sum(decode(wait_Time,0,0,1))"Prev",

sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"

fromv$session_Wait

groupby event orderby 4 ;

回滚段的争用情况

?

1

2

3

selectname, waits, gets, waits/gets "Ratio"

fromv$rollstat a, v$rollname b

wherea.usn = b.usn;

查看前台正在发出的SQL语句

?

1

2

3

4

5

selectuser_name,sql_text

　　fromv$open_cursor

　　wheresid in(selectsid from(selectsid,serial#,username,program

　　fromv$session

　　wherestatus='ACTIVE'));

数据表占用空间大小情况

?

1

2

3

4

selectsegment_name,tablespace_name,bytes,blocks

fromuser_segments

wheresegment_type='TABLE'

ORDERBY bytes DESC,blocks DESC;

查看表空间碎片大小

?

1

2

3

4

selecttablespace_name,round(sqrt(max(blocks)/sum(blocks))*

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

   fromdba_free_space

   groupby tablespace_name orderby 1;

查看表空间占用磁盘情况

?

1

2

3

4

5

6

7

8

9

10

11

select

         b.file_id                                 文件ID号,

         b.tablespace_name                         表空间名,

         b.bytes                                 字节数,

         (b.bytes-sum(nvl(a.bytes,0)))                 已使用,

         sum(nvl(a.bytes,0))                         剩余空间,

         sum(nvl(a.bytes,0))/(b.bytes)*100         剩余百分比

         fromdba_free_space a,dba_data_files b

         wherea.file_id=b.file_id

         groupby b.tablespace_name,b.file_id,b.bytes

         orderby b.file_id;

查看Oracle 表空间使用率

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

SELECTD.TABLESPACE_NAME, 

       SPACE|| 'M'"SUM_SPACE(M)", 

       SPACE- NVL (FREE_SPACE, 0) || 'M'"USED_SPACE(M)", 

       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 

          "USED_RATE(%)", 

       FREE_SPACE || 'M'"FREE_SPACE(M)" 

  FROM(  SELECTTABLESPACE_NAME, 

                 ROUND (SUM(BYTES) / (1024 * 1024), 2) SPACE, 

                 SUM(BLOCKS) BLOCKS 

            FROMDBA_DATA_FILES 

        GROUPBY TABLESPACE_NAME) D, 

       ( SELECTTABLESPACE_NAME, 

                 ROUND (SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE 

            FROMDBA_FREE_SPACE 

        GROUPBY TABLESPACE_NAME) F 

 WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

UNIONALL                                                           --如果有临时表空间 

SELECTD.TABLESPACE_NAME, 

       SPACE|| 'M'"SUM_SPACE(M)", 

       USED_SPACE || 'M'"USED_SPACE(M)", 

       ROUND (NVL (USED_SPACE, 0) / SPACE* 100, 2) || '%'"USED_RATE(%)", 

       NVL (FREE_SPACE, 0) || 'M'"FREE_SPACE(M)" 

  FROM(  SELECTTABLESPACE_NAME, 

                 ROUND (SUM(BYTES) / (1024 * 1024), 2) SPACE, 

                 SUM(BLOCKS) BLOCKS 

            FROMDBA_TEMP_FILES 

        GROUPBY TABLESPACE_NAME) D, 

       ( SELECTTABLESPACE_NAME, 

                 ROUND (SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 

                 ROUND (SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 

            FROMV$TEMP_SPACE_HEADER 

        GROUPBY TABLESPACE_NAME) F 

 WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

ORDERBY 1;

查看Temp 表空间实际使用磁盘大小

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Selectf.tablespace_name,

       d.file_name"Tempfile name",

       round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",

       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,

             2)"Free MB",

       round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",

       round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /

             round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,

             2)as"Used_Rate(%)"

  fromSYS.V_$TEMP_SPACE_HEADER f,

       DBA_TEMP_FILES           d,

       SYS.V_$TEMP_EXTENT_POOL  p

 wheref.tablespace_name(+) = d.tablespace_name

   andf.file_id(+) = d.file_id

   andp.file_id(+) = d.file_id;

查看session使用回滚段

?

1

2

3

4

5

6

7

8

9

10

11

12

13

SELECT r.name回滚段名,

        s.sid,

        s.serial#,

        s.username 用户名,

        t.status,

        t.cr_get,

        t.phy_io,

        t.used_ublk,

        t.noundo,

        substr(s.program, 1, 78) 操作程序

FROM  sys.v_$session s,sys.v_$transactiont,sys.v_$rollname r

WHERE t.addr = s.taddr andt.xidusn = r.usn

ORDER BY t.cr_get,t.phy_io;

查看SGA区剩余可用内存

?

1

2

3

4

5

6

selectname,

　　　　　　sgasize/1024/1024　　　　　　　　"Allocated(M)",

　　　　　　bytes/1024　　　　　　　　　　　"**空间(K)",

　　　　　　round(bytes/sgasize*100, 2)　　　"**空间百分比(%)"

　　from  (selectsum(bytes) sgasize fromsys.v_$sgastat) s, sys.v_$sgastat f

　　where f.name= 'free memory';

–监控表空间I/O比例
select df.tablespace_name name,df.file_name “file”,f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

监控SGA命中率

?

1

2

3

4

5

6

7

selecta.value + b.value "logical_reads",

       c.value"phys_reads",

       round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"

fromv$sysstat a, v$sysstat b, v$sysstat c

wherea.statistic# = 38 and

      b.statistic# = 39 and

      c.statistic# = 40 ;

监控 SGA 中字典缓冲区的命中率

?

1

2

3

4

5

selectparameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100"Hit ratio"

fromv$rowcache

wheregets+getmisses <>0

groupby parameter, gets, getmisses ;

监控 SGA **享缓存区的命中率，应该小于1%

?

1

2

3

selectsum(pins)"Total Pins",sum(reloads)"Total Reloads",

sum(reloads)/sum(pins) *100 libcache

fromv$librarycache;

监控 SGA 中重做日志缓存区的命中率，应该小于1%

?

1

2

3

4

5

SELECTname, gets, misses, immediate_gets, immediate_misses,

Decode(gets,0,0,misses/gets*100) ratio1,

Decode(immediate_gets+immediate_misses,0,0,

immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

FROMv$latch WHEREname IN ('redo allocation','redo copy');

监控内存和硬盘的排序比率，最好使它小于 .10

?

1

2

3

SELECTname, value

FROMv$sysstat

WHEREname IN ('sorts (memory)','sorts (disk)') ;

监控字典缓冲区

?

1

2

SELECTSUM(GETS)"DICTIONARY GETS",SUM(GETMISSES)"DICTIONARY CACHE GET MISSES"

FROMV$ROWCACHE ;

<h1 id="非系统用户建在system表空间中的表">非系统用户建在SYSTEM表空间中的表

?

1

2

3

4

SELECTowner,table_name

FROMDBA_TABLES

WHEREtablespace_name in('SYSTEM','USER_DATA')AND

      ownerNOTIN('SYSTEM','SYS','OUTLN','ORDSYS','MDSYS','SCOTT','HOSTEAC');

性能最差的SQL

?

1

2

3

4

SELECT* FROM( SELECTPARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text

                FROMv$sqlarea

                ORDERBY disk_reads DESC)

WHEREROWNUM<100;

读磁盘数超100次的sql

?

1

select* fromsys.v_$sqlarea wheredisk_reads>100;

最频繁执行的sql

?

1

select* fromsys.v_$sqlarea whereexecutions>100;

查询使用CPU多的用户session

?

1

2

3

4

5

6

selecta.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value

fromv$session a,v$process b,v$sesstat c

wherec.statistic#=12 and

      c.sid=a.sidand

      a.paddr=b.addr

orderby value desc;

当前每个会话使用的对象数

?

1

2

3

4

5

SELECTa.sid,s.terminal,s.program,count(a.sid)

FROMV$ACCESS a,V$SESSION s

WHEREa.owner <> 'SYS'ANDs.sid = a.sid

GROUPBY a.sid,s.terminal,s.program

ORDERBY count(a.sid) ;