Oracle dba常用脚本
Oracle dba常用脚本
以下为Oracle dba常用的脚本
1、检查数据缓冲区和共享区命中率
- declare
- reads_value number(15);
- direct_value number(15);
- lob_value number(15);
- consical_value number(15);
- db_value number(15);
- x number;
- y number;
- z number;
- begin
- select value into reads_value from v$sysstat where name ='physical reads';
- select value into direct_value from v$sysstat where name ='physical reads direct';
- select value into lob_value from v$sysstat where name ='physical reads direct (lob)';
- select value into consical_value from v$sysstat where name ='consistent gets';
- select value into db_value from v$sysstat where name = 'db block gets';
- x:=direct_value+lob_value;
- y:=1-(reads_value-x)/(consical_value+db_value-x);
- dbms_output.put_line('数据缓冲区命中率是:'||y);
- if(y>=0.9) then
- dbms_output.put_line('命中率大于0.9数据缓存大小比较合适');
- else
- dbms_output.put_line('数据缓存命中率小于0.9,请调试db_cache_size大小');
- end if;
- select sum(pinhits-reloads)/sum(pins) into z from v$librarycache;
- dbms_output.put_line('共享区命中率是:'||z);
- if(z>=0.9) then
- dbms_output.put_line('共享区命中率大于0.9大小比较合适');
- else
- dbms_output.put_line('共享区命中率小于0.9,请调试db_cache_size大小');
- end if;
- end;
2、检查表空间的是使用情况
- select a.tablespace_name,total,free,(total-free)/total used from
- (select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a,
- (select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b
- where a.tablespace_name=b.tablespace_name;
3、检查和表空间生成语句
- --表空间生成语句,请在sql窗体中执行
- select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
- --表生成语句
- select dbms_metadata.get_ddl('TABLE',upper('&TABLE_NAME'),upper('&USER_NAME')) from dual;
4、找出系统中cpu消耗和io比较大的语句
- select SQL_TEXT,SQL_FULLTEXT from v$sql a where a.HASH_VALUE
- in (select hash_value from v$sqlarea where buffer_gets>1000000
- or disk_reads>1000000/*order by buffer_gets+disk_reads desc*/);
5、找出当前系统中比较慢的sql
①aix中通过topas命令找出cpu系统比较大的pid
②通过sql语句方法找出具体语句
- --spid为进程号
- select SQL_TEXT,SQL_FULLTEXT,FIRST_LOAD_TIME from v$sql where ADDRESS=(select SQL_ADDRESS from v$session where
- PADDR=(select ADDR from v$process where spid=&&spid));
6、找出比较多的表空间碎片
- select tablespace_name,
- count(*) chunks ,
- max(bytes/1024/1024) max_chunk
- from dba_free_space
- group by tablespace_name;
说明:得分越高,表空间的碎片越大
评论暂时关闭