财务管控模块应用监控统计查询脚本(网省使用).doc

一、 准备阶段1.1. 创建视图-创建审计日志视图，只显示登录成功日志记录CREATE OR REPLACE VIEW VIEW_XTAUDITLOGEVT_CWGK ASSELECT YHDM,YHMC,YHXSMC,SJ,DWDM,DWMC,HOSTNAME,HOSTIP,LCONTENT,YES AS ISLOGON FROM XTAUDITLOGEVT WHERE LCONTENT=登陆成功-创建用户视图，不包括停用的用户，各单位可以根据实际情况追加条件把实施用户可以剔除掉CREATE OR REPLACE VIEW VIEW_XTYHXX_CWGK ASSELECT A.YHDM,A.DWDM,B.MC,A.YHMC,A.XSMC FROM XTYHXX A,XTDW B WHERE A.DWDM=B.DH AND A.STOPED=0 ORDER BY A.DWDM-创建2009、2010年凭证主表记录 DECLARE V_SQL VARCHAR2(32767);BEGIN V_SQL:=CREATE OR REPLACE VIEW VIEW_XTBILL_ALL AS|CHR(13); FOR R IN(SELECT USERID FROM XTDW WHERE DHMAIN AND XJ=1 ORDER BY DH) LOOP V_SQL:=V_SQL| SELECT BILLID,TTIME,LASTCHANGETIME,COMPID FROM |R.USERID|.XTBILL2009 UNION| SELECT BILLID,TTIME,LASTCHANGETIME,COMPID FROM |R.USERID|.XTBILL2010 UNION|CHR(13); END LOOP; V_SQL:=SUBSTRING(V_SQL,1,LEN(V_SQL)-6); - DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL;END;-创建2009、2010年凭证明细表记录DECLARE V_SQL VARCHAR2(32767);BEGIN V_SQL:=CREATE OR REPLACE VIEW VIEW_XTBILLMX_ALL AS|CHR(13); FOR R IN(SELECT USERID FROM XTDW WHERE DHMAIN AND XJ=1 ORDER BY DH) LOOP V_SQL:=V_SQL|SELECT BILLID,BILLID1,TTIMEF FROM |R.USERID|.XTBILLMX2009 UNION| SELECT BILLID,BILLID1,TTIMEF FROM |R.USERID|.XTBILLMX2010 UNION|CHR(13); END LOOP; V_SQL:=SUBSTRING(V_SQL,1,LEN(V_SQL)-6); - DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL;END;-创建2010年凭证主表记录 DECLARE V_SQL VARCHAR2(32767);BEGIN V_SQL:=CREATE OR REPLACE VIEW VIEW_XTBILL_2010 AS|CHR(13); FOR R IN(SELECT USERID FROM XTDW WHERE DHMAIN AND XJ=1 ORDER BY DH) LOOP V_SQL:=V_SQL| SELECT BILLID,TTIME,LASTCHANGETIME,COMPID FROM |R.USERID|.XTBILL2010 UNION|CHR(13); END LOOP; V_SQL:=SUBSTRING(V_SQL,1,LEN(V_SQL)-6); - DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL;END;-创建2010年凭证明细表记录DECLARE V_SQL VARCHAR2(32767);BEGIN V_SQL:=CREATE OR REPLACE VIEW VIEW_XTBILLMX_2010 AS|CHR(13); FOR R IN(SELECT USERID FROM XTDW WHERE DHMAIN AND XJ=1 ORDER BY DH) LOOP V_SQL:=V_SQL| SELECT BILLID,BILLID1,TTIMEF FROM |R.USERID|.XTBILLMX2010 UNION|CHR(13); END LOOP; V_SQL:=SUBSTRING(V_SQL,1,LEN(V_SQL)-6); - DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL;END;二、 网省财务管控模块应用监控2.1. 数据库运行情况监控2.1.1. 查询数据库脚本版本号-脚本功能：查询数据库脚本版本号，检查各单位是否及时执行财务管控脚本包。共享库执行。-查询结果说明：要求全国网范围内财务管控数据库脚本大版本号（前三位版本号）一致，最后一位小版本号可存在一定差异。SELECT 数据库脚本版本,CSZ FROM XTCS WHERE CSMC =SCRIPT_VERSION;2.1.2. 查询数据库所有表空间总大小、平均使用率-脚本功能：查询表空间总大小（G）表空间实际使用总大小，表空间平均使用率。-查询结果说明：通过了解表空间总的使用情况，结合数据服务器实际存出大小，提出数据库存储扩容申请。SELECT ROUND(SUM(AA.TOT_GROOTTE_MB) / 1024, 2) 表空间总大小(M), ROUND(SUM(AA.TOTAL_BYTES) / 1024, 2) 已使用空间(M), ROUND(ROUND(SUM(AA.TOTAL_BYTES) / 1024, 2) / ROUND(SUM(AA.TOT_GROOTTE_MB) / 1024, 2), 2) 表空间平均使用率FROM (SELECT UPPER(F.TABLESPACE_NAME) 表空间名, D.TOT_GROOTTE_MB AS TOT_GROOTTE_MB,D.TOT_GROOTTE_MB - F.TOTAL_BYTES TOTAL_BYTES,ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2) 使用比, F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M) FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME) AA;2.1.3. 查询数据库表空间使用情况-脚本功能：查询所有表空间的使用情况。-查询结果说明：通过了解各表空间使用情况，当表空间使用比率过高时，可考虑手工或自动调整表空间大小。SELECT UPPER(F.TABLESPACE_NAME) 表空间名, D.TOT_GROOTTE_MB AS 表空间大小,(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) As 表空间实际使用大小,ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2) 使用比 FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME Order By F.TABLESPACE_NAME查询结果类似如下：表空间名表空间大小（单位：M）表空间实际使用大小（单位：M）使用比FMIS_00011080917.1984.93FMIS_000290088498.22FMIS_0003860842.3797.95FMIS_LOB2000127.316.37FMIS_SHARE75806588.6286.92FMIS_TOP70775.9770033.3498.95NBI137204188.3730.53SYSAUX24502405.598.18SYSTEM46604657.1999.94UNDOTBS16980733.1910.5UNDOTBS23665330.259.01USERS51.1923.8YG_BANK2005.692.852.1.4. 查询数据库执行命中率-脚本功能：查询数据库缓冲命中率、共享池命中率、字典命中率-查询结果说明：如果查询命中率过低，低于75%，建议可进行相应的性能优化Select 1, 数据库缓冲命中率, Round(100 - (c.Value - a.Value - b.Value) / (d.Value + d.Value - a.Value - b.Value) * 100), 2) hit radioFrom (Select Value From V$sysstat Where Name = physical reads direct) a, (Select Value From V$sysstat Where Name = physical reads direct (lob) b, (Select Value From V$sysstat Where Name = physical reads) c, (Select Value From V$sysstat Where Name = consistent gets) d, (Select Value From V$sysstat Where Name = db block gets) eUnionSelect 2, 共享池命中率, Round(Sum(Pinhits - Reloads) / Sum(Pins) * 100, 2) hit radioFrom V$librarycacheUnionSelect 3, 字典命中率, Round(1 - (Sum(Getmisses) / Sum(Gets) * 100, 2) Hit Ratio From V$rowcache;2.2. 财务管控登录情况统计2.3.1. 全省范围内财务管控登录情况统计-脚本功能：查询注册用户总数（不包括下发用户，各单位可以根据实际情况追加条件剔除实施用户）、本日登录人数、本周登录人数、本月登录人数、本日登录人次、本周登录人次、本月登录人次。共享库执行-注意：需要根据实际情况修改红色字体部分日期，填写本周星期一的日期SELECT 1,注册用户总数,COUNT(*) FROM XTYHXX WHERE COMPID 9999 AND TRIM(COMPID) NOT LIKE 00_ AND XTDXID IS NOT NULL AND STOPED=0 -注册用户总数UNIONSELECT 2,本日登录人数,COUNT(DISTINCT YHDM) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND TRUNC(SJ) = TRUNC(SYSDATE) UNIONSELECT 3,本周登录人数,COUNT(DISTINCT YHDM) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND SJ = TO_DATE(2010-01-11,YYYY-MM-DD)UNIONSELECT 4,本月登录人数,COUNT(DISTINCT YHDM) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND TRUNC(SJ,MONTH) = TRUNC(SYSDATE,MONTH)UNIONSELECT 8,本日登录人次,COUNT(*) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND TRUNC(SJ) = TRUNC(SYSDATE)UNIONSELECT 9,本周登录人次,COUNT(*) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND SJ = TO_DATE(2010-01-11,YYYY-MM-DD)UNIONSELECT 10,本月登录人次,COUNT(*) FROM XTAUDITLOGEVT WHERE LCONTENT = 登陆成功 AND TRUNC(SJ,MONTH) = TRUNC(SYSDATE,MONTH)查询结果类似如下：1注册用户总数COUNT(*)1注册用户总数2342本日登录人数73本周登录人数194本月登录人数128本日登录人次139本周登录人次10410本月登录人次472.3.2. 按单位统计各单位用户登录情况-1、脚本功能：查看期间范围内各单位用户登录情况。共享库执行-需要根据情况修改查询时间范围，修改红色字体部分SELECT DWDM AS 单位代码,DWMC AS 单位名称,YHXSMC AS 用户名称,COUNT(*) AS 登录次数 FROM VIEW_XTAUDITLOGEVT_CWGK WHERE SJ=TO_DATE(2010-01-01 00:00:00,YYYY-MM-DD HH24:MI:SS) AND SJ=TO_DATE(2010-01-01 00:00:00,YYYY-MM-DD HH24:MI:SS) AND SJ= TO_DATE(2010-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS) AND B.SJ = TO_DATE(2010-02-22,YYYY-MM-DD)UNIONSELECT 4,本周成功数,COUNT(*) FROM XTTRANSBILL WHERE TRANSTIME= TO_DATE(2010-02-22,YYYY-MM-DD) AND RSPCODE=0UNIONSELECT 5,本月传入数,COUNT(*) FROM XTTRANSBILL WHERE TRUNC(TRANSTIME,MONTH) = TRUNC(SYSDATE,MONTH)UNIONSELECT 6,本月成功数,COUNT(*) FROM XTTRANSBILL WHERE TRUNC(TRANSTIME,MONTH) = TRUNC(SYSDATE,MONTH) AND RSPCODE=0UNIONSELECT 7,累计传入数,COUNT(*) FROM XTTRANSBILL UNIONSELECT 8,累计成功数,COUNT(*) FROM XTTRANSBILL WHERE RSPCODE=0查询结果类似如下：1本日传入数COUNT(*)1本日传入数912本日成功数653本周传入数1204本周成功数935本月传入数23436本月成功数21437累计传入数466688累计成功数444362.3.2. 全网省系统凭证情况-脚本功能：查询凭证情况。共享库执行。-需要根据实际情况修改红色字体部分日期，填写本周星期一的日期SELECT 1,累计凭证总数, COUNT(*) FROM VIEW_XTBILL_ALLUNIONSELECT 2,本日发生凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE TRUNC(TTIME) = TRUNC(SYSDATE)UNIONSELECT 3,本周发生凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE TTIME = TO_DATE(2010-03-01,YYYY-MM-DD)UNIONSELECT 4,本月发生凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE TRUNC(TTIME,MONTH) = TRUNC(SYSDATE,MONTH)UNIONSELECT 5,本日新增或修改凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE TRUNC(LASTCHANGETIME) = TRUNC(SYSDATE)UNIONSELECT 6,本周新增或修改凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE LASTCHANGETIME = TO_DATE(2010-03-01,YYYY-MM-DD)UNIONSELECT 7,本月新增或修改凭证数, COUNT(*) FROM VIEW_XTBILL_ALL WHERE TRUNC(LASTCHANGETIME,MONTH) = TRUNC(SYSDATE,MONTH)查询结果类似如下：1累计凭证总数COUNT(*)1累计凭证总数578482本日发生凭证数03本周发生凭证数04本月发生凭证数05本日新增或修改凭证数656本周新增或修改凭证数937本月新增或修改凭证数932.3.3. 按单位统计时间范围内凭证情况-脚本功能：查询各单位时间范围内凭证情况SELECT C.COMPID, C.MC,COUNT(*) AS 凭证数 FROM(SELECT A.*,B.MCFROM VIEW_XTBILL_ALL A,XTDW BWHERE TTIME = TO_DATE(2010-01-01, YYYY-MM-DD) ANDTTIME = TO_DATE(2010-01-01, YYYY-MM-DD) AND B.TTIME = TO_DATE(2010-03-01,YYYY-MM-DD)UNIONSELECT 4,本月发生单据数, COUNT(*) FROM XTYWBILL WHERE TRUNC(TTIME,MONTH) = TRUNC(SYSDATE,MONTH)查询结果类似如下：1累计单据总数COUNT(*)1累计单据总数8182本日发生单据数03本周发生单据数04本月发生单据数02.3.5. 查询管理对象总数-查询管理对象总数。共享库执行SELECT 1,累计管理对象数量总数, COUNT(*) FROM XTGLDXIDMC2.3.6. 科目汇总数据上报情况-脚本功能：查询是否生成