Oracle:有关监控的一些SQL(一)
本文初步陈列一些与数据库状态监控有关的SQL,有的是自己写的,有的是摘自读过的文档或书籍,… 不管怎样,能解决问题就是好的。
下面开始。
1. 数据库的用户连接与断开的历史(次数)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> col userhost for a12 SQL> col os_username for a12 SQL> col username for a12 SQL> select userhost,os_username,username,count(*) from dba_audit_session group by userhost,os_username,username order by userhost; USERHOST OS_USERNAME USERNAME COUNT(*) ------------ ------------ ------------ ---------- ora11g oracle BI 2 ora11g oracle DBSNMP 813 ora11g oracle HR 2 ora11g oracle OE 12 ora11g oracle SH 4 ora11g oracle SYSMAN 59 6 rows selected. SQL> |
2. 查看某个用户的登陆与登出时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> select distinct os_username from dba_audit_session where timestamp is not null; OS_USERNAME ------------ oracle SQL> select userhost,os_username,username,to_char(timestamp,'yyyy-mon-dd hh24:mi:ss') as "Login",to_char(logoff_time,'yyyy-mon-dd hh24:mi:ss') as "Logout" from dba_audit_session where timestamp is not null and rownum<10; USERHOST OS_USERNAME USERNAME Login Logout ------------ ------------ ------------ -------------------- -------------------- ora11g oracle HR 2014-oct-09 04:13:04 ora11g oracle HR 2014-oct-09 04:13:05 2014-oct-09 04:13:05 ora11g oracle OE 2014-oct-09 04:13:05 ora11g oracle OE 2014-oct-09 04:13:05 2014-oct-09 04:13:05 ora11g oracle OE 2014-oct-09 04:13:16 ora11g oracle OE 2014-oct-09 04:13:16 2014-oct-09 04:13:16 ora11g oracle OE 2014-oct-09 04:13:16 ora11g oracle OE 2014-oct-09 04:13:16 2014-oct-09 04:13:16 ora11g oracle OE 2014-oct-09 04:13:16 9 rows selected. SQL> |
3. 数据库的表空间大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select tablespace_name,sum(bytes)/1048576 as MB from dba_data_files group by tablespace_name order by tablespace_name; TABLESPACE_NAME MB ------------------------------ ---------- ADAMHUAN 3000 EXAMPLE 313.125 SYSAUX 560 SYSTEM 710 UNDOTBS1 70 USERS 5 6 rows selected. SQL> |
4. TOP10:空间占用最大的表
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 |
SQL> col segment_name for a23 SQL> col owner for a12 SQL> run 1 with ranked_information as 2 ( 3 select owner,segment_name,bytes/1048576 as MB,rank() over (order by bytes desc) as RANK from dba_segments where segment_type='TABLE' 4 ) 5 select rank,owner,segment_name,mb from ranked_information where rank<=10 6* RANK OWNER SEGMENT_NAME MB ---------- ------------ ----------------------- ---------- 1 SYS IDL_UB1$ 248 2 SYS SOURCE$ 72 3 SYS IDL_UB2$ 31 4 SYS ARGUMENT$ 12 4 SH CUSTOMERS 12 4 SYS JAVA$MC$ 12 7 SYS IDL_CHAR$ 10 8 SYS OBJ$ 8 8 XDB XDB$RESOURCE 8 10 SYS VIEW$ 7 10 MDSYS SDO_CS_SRS 7 10 SYS DEPENDENCY$ 7 12 rows selected. SQL> |
5. 列出每秒的逻辑读与逻辑写
代码:
with
pivoted_data as
(
select * from ( select snap_id,name,value from stats$sysstat )
pivot(
sum(value) for name in (‘physical reads’ as physical_reads,’session logical reads’ as logical_reads)
)
),
deltas as
(
select
snap_id,snap_time,
snap_time – lag(snap_time) over (partition by startup_time order by snap_id) as duration,
physical_reads – lag(physical_reads) over (partition by startup_time order by snap_id) as physical_reads,
logical_reads – lag(logical_reads) over (partition by startup_time order by snap_id) as logical_reads
from pivoted_data natural join stats$snapshot
)
select
snap_id,
to_char(snap_time,’yyyy-mon-dd hh24:mi:ss’) as snap_time,
physical_reads / duration / (24*60*60) as physical_reads_per_second,
logical_reads / duration / (24*60*60) as logical_reads_per_second
from deltas
order by snap_id
;
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 |
SQL> set linesize 300 SQL> SQL> with 2 pivoted_data as 3 ( 4 select * from ( select snap_id,name,value from stats$sysstat ) 5 pivot( 6 sum(value) for name in ('physical reads' as physical_reads,'session logical reads' as logical_reads) 7 ) 8 ), 9 deltas as 10 ( 11 select 12 snap_id,snap_time, 13 snap_time - lag(snap_time) over (partition by startup_time order by snap_id) as duration, 14 physical_reads - lag(physical_reads) over (partition by startup_time order by snap_id) as physical_reads, 15 logical_reads - lag(logical_reads) over (partition by startup_time order by snap_id) as logical_reads 16 from pivoted_data natural join stats$snapshot 17 ) 18 select 19 snap_id, 20 to_char(snap_time,'yyyy-mon-dd hh24:mi:ss') as snap_time, 21 physical_reads / duration / (24*60*60) as physical_reads_per_second, 22 logical_reads / duration / (24*60*60) as logical_reads_per_second 23 from deltas 24 order by snap_id 25 ; SNAP_ID SNAP_TIME PHYSICAL_READS_PER_SECOND LOGICAL_READS_PER_SECOND ---------- -------------------- ------------------------- ------------------------ 1 2014-oct-09 21:00:03 2 2014-oct-09 21:02:07 3.2016129 567.193548 SQL> |
6. 计算CPU的利用率
代码:
with
pivoted_data as
(
select * from (select snap_id,osstat_id,value from stats$osstat) pivot(sum(value) for osstat_id in (1 as idle_time,2 as busy_time))
),
deltas as
(
select
snap_id,snap_time,
idle_time – lag(idle_time) over (partition by startup_time order by snap_id) as idle_time,
busy_time – lag(busy_time) over (partition by startup_time order by snap_id) as busy_time
from pivoted_data natural join stats$snapshot
)
select
snap_id,
to_char(snap_time,’yyyy-mon-dd hh24:mi:ss’) as snap_time,
busy_time / (idle_time+busy_time) as cpu_utilization_percent
from deltas order by snap_id
;
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 |
SQL> with 2 pivoted_data as 3 ( 4 select * from (select snap_id,osstat_id,value from stats$osstat) pivot(sum(value) for osstat_id in (1 as idle_time,2 as busy_time)) 5 ), 6 deltas as 7 ( 8 select 9 snap_id,snap_time, 10 idle_time - lag(idle_time) over (partition by startup_time order by snap_id) as idle_time, 11 busy_time - lag(busy_time) over (partition by startup_time order by snap_id) as busy_time 12 from pivoted_data natural join stats$snapshot 13 ) 14 select 15 snap_id, 16 to_char(snap_time,'yyyy-mon-dd hh24:mi:ss') as snap_time, 17 busy_time / (idle_time+busy_time) as cpu_utilization_percent 18 from deltas order by snap_id 19 ; SNAP_ID SNAP_TIME CPU_UTILIZATION_PERCENT ---------- -------------------- ----------------------- 1 2014-oct-09 21:00:03 2 2014-oct-09 21:02:07 .029366701 SQL> |
7. 数据库大小以及可用空间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> col file_name for a50 SQL> run 1 select tablespace_name,file_name,file_id,bytes/1024/1024 as MB,autoextensible,increment_by * 8192/1024/1024 as next_mb, 2* maxbytes / 1024/1024 as max_mb from dba_data_files order by tablespace_name,file_id TABLESPACE_NAME FILE_NAME FILE_ID MB AUT NEXT_MB MAX_MB --------------- -------------------------------------------------- ---------- ---------- --- ---------- ---------- ADAMHUAN /u01/app/oracle/oradata/orcl/adamhuan01.dbf 6 3000 NO 0 0 EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 5 345.625 YES .625 32767.9844 STATSPACK_ME /u01/app/oracle/oradata/orcl/statspack_me01.dbf 7 100 YES 10 2048 STATSPACK_ME /u01/app/oracle/oradata/orcl/statspack_me02.dbf 8 20 YES 10 2048 SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 2 560 YES 10 32767.9844 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 1 720 YES 10 32767.9844 UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 3 70 YES 5 32767.9844 USERS /u01/app/oracle/oradata/orcl/users01.dbf 4 5 YES 1.25 32767.9844 8 rows selected. SQL> |
————————————————————
Ending。