数据库日常检查

1、HUAWEI TECHNOLOGIES CO., LTDHuawei Confidential Security Level: 2022-6-12CRMCRM数据库日常检查数据库日常检查HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 2查看数据库情况l-检查检查sga使用情况使用情况lselect * from v$sga;l-数据库字符集数据库字符集lselect * from v$nls_parameters;l-oracle版本号版本号lSelect version FROM Product_component_version

2、Where SUBSTR(PRODUCT,1,6)=Oracle;l-数据字典缓冲区数据字典缓冲区lselect * from v$librarycache;HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 3查看表空间状态lselect tablespace_name,extent_management,segment_space_management from dba_tablespaces;lselect table_name,freelists,freelist_groups from user_tables;HUAWEI TE

3、CHNOLOGIES CO., LTD.Huawei Confidential Page 4察看表空间碎片情况l select tablespace_name,round(sqrt(max(blocks)/sum(blocks)*l (100/sqrt(sqrt(count(blocks),2) FSFIl from dba_free_spacel group by tablespace_name order by 1HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 5表空间剩余百分比l select a.tablespace_name

4、, 100* a.bytes/b.bytes balance from l(select tablespace_name, sum(nvl(bytes,0) bytes from dba_free_space group by tablespace_name) a,l(select tablespace_name, sum(nvl(bytes,0) bytes from dba_data_files group by tablespace_name) blwhere a.tablespace_name=b.tablespace_name and 100* a.bytes/b.bytes 50H

5、UAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 6性能最差的SQLl-性能最差的性能最差的SQLlSELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text l FROM v$sqlarea l ORDER BY disk_reads DESC) lWHERE ROWNUM 200;HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 8根据pid获取SQLlsele

6、ct sql_text from v$sqlarea where address in (select sql_address from v$session where paddr in (select addr from v$process where spid = 344318); HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 9查看数据库等待事件l Select count(*), eventl from v$session_waitl where event not in (smon timer, pmon timer, r

7、dbms ipc message,l SQL*Net message from client)l group by eventl order by 1 descHUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 10检查是否有超过200个extent的对象lselect segment_name,l tablespace_name,l extentsl from dba_segmentslwhere owner not in (SYS,SYSTEM)land extents 200;HUAWEI TECHNOLOGIES CO., LTD

8、.Huawei Confidential Page 11检查无效的对象lselect object_name, object_type, owner, statusl from dba_objectsl where status !=VALIDl and owner not in (SYS,SYSTEM) and object_type in (TRIGGER,VIEW,PROCEDURE,FUNCTION);HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 12求cache中缓存超过其5%的对象lselect o.owner,o.ob

9、ject_type,o.object_name,count(b.objd)lfrom v$bh b,dba_objects olwhere b.objd = o.object_idlgroup by o.owner,o.object_type,o.object_namelhaving count(b.objd) (select to_number(value)*0.05 from v$parameter where name = db_block_buffers);HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 13求session的

10、OS进程IDlcol program format a54lselect p.spid OS Thread, Name-User, gramlfrom v$process p, v$session s, v$bgprocess blwhere p.addr = s.paddr and p.addr = b.paddrlUNION ALLlselect p.spid OS Thread, s.username Name-User, gramlfrom v$process p, v$session s where p.addr = s.paddr and s.us

11、ername is not null;HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 14查看一下谁在使用那个可以得回滚段l查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它将它commit，再不行就看能否，再不行就看能否kill它它,查看当前正在使用的回滚段的用户信查看当前正在使用的回滚段的用户信息和回滚段信息息和回滚段信息l

12、set linesize 121lSELECT ROLLBACK SEGMENT NAME ,l.sid ORACLE PID,p.spid SYSTEM PID ,s.username ORACLE USERNAMElFROM v$lock l, v$process p, v$rollname r, v$session slWHERE l.sid = p.pid(+) AND s.sid=l.sid AND TRUNC(l.id1(+)/65536) = r.usn AND l.type(+) = TX AND l.lmode(+) = 6 ORDER BY ;HU

13、AWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 15求回滚段正在处理的事务lselect ,b.xacts,c.sid,c.serial#,d.sql_textlfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction elwhere a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addrland c.sql_address=d.address and c.sql_hash_value=d.hash_va

14、lue order by ,c.sid,d.piece;HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 16查看用户的回滚段的信息lselect s.username, from v$session s, v$transaction t, v$rollstat r, v$rollname rnlwhere s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usnHUAWEI TECHNOLOGIES CO., LTD.Huawei Confid

15、ential Page 17查看内存使用情况lselect least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024) shared_pool_used,lmax(b.value)/(1024*1024) shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)-(sum(a.bytes)/(1024*1024) shared_pool_avail,(sum(a.bytes)/(1024*1024)/(max(b.value)/(1024*1024)

16、*100 avail_pool_pctlfrom v$sgastat a, v$parameter b where (a.pool=shared pool and not in (free memory) and =shared_pool_size;HUAWEI TECHNOLOGIES CO., LTD.Huawei Confidential Page 18计算共享池内存使用率lSELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2) * 100 | % l FROM V$SGASTAT l WHERE NAME = free memory AND