oracle数据库巡检sql脚本

如何查询sga内各组件信息和pga大小

SQL> conn / as sysdba

SQL> show parameter sga_max –查看sga的大小

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

sga_max_size big integer 1578706860

SQL> show parameter sga_target –查看sga_target大小(只限10g以上版本)

NAME TYPE V ALUE

------------------------------------ ----------- ------------------------------

sga_target big integer 584M

SQL> show parameter db_cache_size –-查看db_cache大小

NAME TYPE V ALUE

------------------------------------ ----------- --------------------

db_cache_size big integer 1048576000

SQL> show parameter shared_pool_size –查看share_pool大小

NAME TYPE V ALUE

------------------------------------ ----------- ---------------------------

shared_pool_size big integer 318767104

SQL> show parameter pga—查看pga_aggregate_target

NAME TYPE V ALUE

------------------------------------ ----------- ---------------

pga_aggregate_target big integer 314572800

查询数据库版本的sql

SQL> select VERSION from v$instance;

VERSION

-----------------

9.2.0.1.0

SQL> select name, log_mode from v$database; --查看数据库名，归档模式

NAME LOG_MODE

--------- ------------

SKYOTA ARCHIVELOG –NOARCHIVELOG为非归档模式

查看数据库字符集

SQL> select value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

V ALUE

-----------------------------------------------------

ZHS16GBK

查看redo log 信息

查看日至组及成员

SELECT group#,member FROM V$LOGFILE

查看日志如成员大小

SQL> select group# ,bytes ,members from v$log;

GROUP# BYTES MEMBERS

---------- ---------- ----------

1 838860800 1

2 838860800 1

3 838860800 1

近一个月内每天日志切换次数

select to_char(first_time,'yyyy-mm-dd') day,count(*) times from

v$log_history where first_time> sysdate-30 group by

to_char(first_time,'yyyy-mm-dd') ;

查看监控当天高峰期日志切换情况–具体高峰时段根据项目特性修改sql

select count(*) times from v$log_history where first_time between

to_date('2009-04-11 10:00:00','yyyy-mm-dd hh24:mi:ss') and

to_date('2009-04-11 16:00:00','yyyy-mm-dd hh24:mi:ss');

在oracle告警日志中（alertsid.log），也可以查看最近日志切换的情况

表空间使用率：

SELECT UPPER (f.tablespace_name) "表空间名",

d.tot_grootte_mb "表空间大小(M)",

d.tot_grootte_mb - f.total_bytes "已使用空间(M)",

TO_CHAR

(ROUND ( (d.tot_grootte_mb - f.total_bytes)

/ d.tot_grootte_mb

* 100,

2

),

'990.99'

) "以使用占当前表空间大小比例",

f.total_bytes "空闲空间(M)", f.max_bytes "最大块(M)",

d.max_autobytes "表空间扩展的最大尺寸(M)",

TO_CHAR (ROUND ( (d.tot_grootte_mb - f.total_bytes)

/ d.max_autobytes

* 100,

2

),

'990.99'

) "以使用占最大扩展比例"

FROM (SELECT tablespace_name,

ROUND (SUM (BYTES) / (1024 * 1024), 2) total_bytes, ROUND (MAX (BYTES) / (1024 * 1024), 2) max_bytes

FROM SYS.dba_free_space

GROUP BY tablespace_name) f,

(SELECT dd.tablespace_name,

ROUND (SUM (dd.BYTES) / (1024 * 1024),

2) tot_grootte_mb,

TRUNC ( SUM (DECODE (dd.maxbytes,

0, dd.BYTES,

dd.maxbytes

)

)

/ (1024 * 1024)

) max_autobytes

FROM SYS.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name;

任务或job的运行情况

select

schema_user,last_date,next_date,total_time,interval,failures,what from dba_jobs where schema_user='SKY' –以实际的业务用户为准

查看系统当前的等待事件

SELECT s.SID, w.event, https://www.360docs.net/doc/5210729220.html,ername, w.p1, w.p2, w.p3, s.logon_time, s.program,

SYSDATE

FROM v$session s, v$session_wait w

WHERE s.SID = w.SID

AND w.event NOT LIKE '%SQL*Net%'

AND w.event NOT LIKE '%rdbms%'

AND w.event NOT LIKE '%timer%'

AND w.event NOT LIKE '%jobq%'

AND w.event NOT LIKE '%wakeup%';

当前等待事件对应的sql

SELECT /*+ RULE*/

s.SID, w.event, https://www.360docs.net/doc/5210729220.html,ername, t.hash_value, t.piece,

t.sql_text, SYSDATE

FROM v$session s, v$session_wait w, v$sqltext t

WHERE s.SID = w.SID

AND s.sql_hash_value = t.hash_value

AND w.event IN

('db file scattered read',

'db file sequential read',

'free buffer waits',

'enqueue',

'latch free',

'log file parallel write',

'log file sync',

'buffer busy waits',

'db file parallel write',

'db file single write',

'direct path read',

'direct path write',

'free buffer inspected',

'free buffer waits',

'library cache load lock',

'library cache lock',

'library cache pin',

'log buffer space',

'log file single write',

'log file switch (checkpoint incomplete)',

'log file switch (archiving needed)',

'log file switch (clearing log file)',

'log file switch completion',

'undo segment extension'

)

ORDER BY s.SID, t.piece;

当前主要全表扫描语句

SELECT b.sql_text, b.executions, b.sorts, b.disk_reads,

b.buffer_gets, a.hash_value, a.object_owner, a.object_name,

SYSDATE

FROM v$sql_plan a, v$sql b

WHERE a.address = b.address

AND a.hash_value = b.hash_value

AND a.child_number = b.child_number

AND a.operation = 'TABLE ACCESS'

AND a.options = 'FULL'

AND a.object_owner NOT IN

('ODM',

'QS',

'QS_CBADM',

'QS_CS',

'QS_ES',

'QS_OS',

'QS_WS',

'SYS',

'SYSTEM'

);

单次执行物理读多前20个语句

SELECT * FROM

(

SELECT

round(disk_reads/decode(EXECUTIONS,0,1,executions),2) bit,

PARSING_USER_ID,

EXECUTIONS,

SORTS,

COMMAND_TYPE,

DISK_READS,

sql_text

FROM v$sqlarea

ORDER BY bit DESC

)

WHERE ROWNUM<21 ;

物理多总数多前10个sql

SELECT * FROM

(

SELECT PARSING_USER_ID,

EXECUTIONS,

SORTS,

COMMAND_TYPE,

DISK_READS,

sql_text

FROM v$sqlarea

ORDER BY disk_reads DESC

)

WHERE ROWNUM<11 ;

单次逻辑读多的前10个sql

SELECT *

FROM (SELECT s.sid,

b.spid,

s.sql_hash_value,

q.sql_text,

q.executions,

q.buffer_gets,

ROUND(q.buffer_gets / q.executions) AS buffer_per_exec,

ROUND(q.elapsed_time / q.executions) AS cpu_time_per_exec,

q.cpu_time,

q.elapsed_time,

q.disk_reads,

q.rows_processed

FROM v$session s, v$process b, v$sql q

WHERE s.sql_hash_value = q.hash_value

AND s.paddr = b.addr

-- AND s.status = 'ACTIVE'

AND s.TYPE = 'USER'

AND q.buffer_gets > 0

AND q.executions > 0

ORDER BY buffer_per_exec DESC)

WHERE ROWNUM <= 10;

逻辑读多的前10个sql

SELECT *

FROM (SELECT s.sid,

b.spid,

s.sql_hash_value,

q.sql_text,

q.executions,

q.buffer_gets,

q.cpu_time,

q.elapsed_time,

q.disk_reads,

q.rows_processed

FROM v$session s, v$process b, v$sql q

WHERE s.sql_hash_value = q.hash_value

AND s.paddr = b.addr

-- AND s.status = 'ACTIVE'

AND s.TYPE = 'USER'

AND q.buffer_gets > 0

AND q.executions > 0

ORDER BY buffer_gets DESC)

WHERE ROWNUM <= 10;

用户对象在非自定义表空间中存储的情况（owner='SKY'以实际业务用户为

准）

select segment_name,segment_type,tablespace_name,bytes from

dba_segments where owner='SKY' AND TABLESPACE_NAME

IN('SYSTEM','USERS','SYSAUX')

系统中最大的二十个表大小及高水位

select segment_name, "SIZE(M)",TABLESPACE_NAME from(select segment_name,bytes/1024/1024"SIZE(M)",TABLESPACE_NAME from user_segments ORDER BY BYTES DESC) where rownum<=20