办公系统Oracle数据库监控、统计常用SQL语句

      在维护生产环境数据库时，如果需要人工修改数据，禁止使用工具锁表式修改数据（例如：Select * from XXX ... forUpdate），最好使用数据处理脚本，例如在命令行中，一个事务处理完毕（直接提交Commit）。

      如下是常用的监控、分析数据的SQL语句。

1、统计表空间语句
select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1,sum(nvl(bytes,0)) a2 from dba_free_spacegroup by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files groupby tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 fromdba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;

2、监控关键表
select count(*) from cordys.task_list_wait;
select count(*) from cordys.task_list;
select count(*) from cordys.workflow_instance_translog;

3、按月统计业务量（每月启动主流程数量）
select ny,sum(decode(mo,'公文模块',co))公文模块,sum(decode(mo,'业务流程模块',co)) 业务流程模块,sum(co) 合计 from
(select ny,mo,count(*) co from
(select to_char(t.start_datetime,'yyyymm') ny,ta.workflow_module mofrom cordys.workflow_instance t,cordys.workflow ta
where t.parent_guid is null and t.workflow_id =ta.workflow_id)
group by ny,mo)
group by ny
order by ny

4、查询锁表的方法
SELECT S.MACHINE,S.TERMINAL,S.OSUSER,S.SID SESSION_ID,S.USERNAME,
            DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive',TO_CHAR(LMODE)) MODE_HELD,
            DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive',TO_CHAR(REQUEST)) MODE_REQUESTED,
            O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', S.TYPELOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2
FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S
WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID and S.USERNAME IS NOTNULL;

5、按日统计处理业务量（文件-含业务流程）
select rq,sum(decode(lb,'公文模块',sl,0)) as公文,sum(decode(lb,'业务流程模块',sl,0)) as 业务流程,
sum(decode(lb,'发文传阅',sl,0)) as 发文传阅,sum(decode(lb,'公告',sl,0)) as公告,
sum(decode(lb,'通用办公模块',sl,0)) as 通用办公模块,sum(sl) as 合计 from
(
select to_char(a.create_date, 'yyyy-mm-dd') as rq,'发文传阅' aslb,count(*) as sl
  from doc_packing_issue_adv  a
  where a.states in ('1','3','5')
  group byto_char(a.create_date,'yyyy-mm-dd'),'发文传阅'
union
  select to_char(t.advice_time, 'yyyy-mm-dd') asrq,'公告'as lb, count(*) as sl
  from ofc_advice t,ofc_advice_incept a
  where
  t.PK_ADVICE_ID=a.PK_ADVICE_ID
  group by to_char(t.advice_time,'yyyy-mm-dd'),'公告'
union
select to_char(t.end_datetime,'yyyy-mm-dd') as rq,t.workflow_moduleas lb,count(*) as sl
from cordys.task_list t
group byto_char(t.end_datetime,'yyyy-mm-dd'),t.workflow_module
union
select to_char(t.end_datetime,'yyyy-mm-dd') as rq,t.workflow_moduleas lb,count(*) as sl
from cordys.task_list_finish t
group byto_char(t.end_datetime,'yyyy-mm-dd'),t.workflow_module
)
where rq>'2011-09-30'
group by rq order by rq

6、查询SQL语句执行计划
    通过此语句获取执行效率低的SQL执行计划。
select substr(s.SQL_TEXT, 0,700) ,count(*) as aa from v$sql s groupby substr(s.SQL_TEXT, 0,700)
order by aa desc;

selectt.SQL_TEXT,t.SQL_FULLTEXT,t.SHARABLE_MEM,t.PERSISTENT_MEM,t.RUNTIME_MEM
from v$sql t
where t.PARSING_SCHEMA_NAME = 'OA_OA'
and to_char(t.LAST_ACTIVE_TIME,'yyyy-mm-dd')='2013-03-01' order byt.CPU_TIME desc

7、数据占用内存情况
select p.spid,s.sid,pga_max_mem from v$process p ,v$session s wheres.paddr=p.addr and s.username='CORDYS' order by pga_max_mem;

selects.USERNAME,s.MACHINE,s.LOGON_TIME,s.SCHEMANAME,s.sid,pga_max_mem/1024/1024,p.PGA_USED_MEM/1024/1024from v$process p ,v$session s where s.paddr=p.addr order bypga_max_mem;