扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
这种思路不好，实现不了。
创新互联建站的客户来自各行各业，为了共同目标，我们在工作上密切配合，从创业型小企业到企事业单位，感谢他们对我们的要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。专业领域包括成都网站制作、成都网站设计、电商网站开发、微信营销、系统平台开发。
需要换一种想法。
例如：
1.C#写一个实时监控程序，Oracle写包和触发器。
2.监控程序的作用是实时的调用包，然后返回相关数据。
3.触发器的作用是，当某个表有数据插入或者删除，或者某个字段被修改了会直接触发。将想要写的数据，写进指定表或视图。
整体思路是：
当某个表有变动，触发器就触发并写入数据到指定表或视图。
一段时间后，C#监控程序调用Oracle的包，包从指定表或视图获取数据，然后将数据返回到监控端，监控端再触发特定的C#代码。
在数据库管理与维护中，我们总会遇到一个问题：我们创建的索引是否会被某些SQL语句使用呢？换个通俗表达方式：我创建的索引是否是未使用的索引（unused Indexes），是否有价值呢？如果创建的某个索引是Unused Indexes，尤其是没有合理规划索引的系统或那些管理控制不规范的系统。有可能建立了N个索引，其实有些索引都是没有任何SQL会使用，那么此时这些多余的索引其实会带来两个问题：1：浪费存储空间，尤其是大表的索引，浪费的存储空间尤其可观； 2：加重DML操作（UPDATE、INSERT、DELETE）的开销。
ORACLE其实提供了监控索引使用情况的功能。ALTER INDEX index_name MONITORING USAGE; 我们下面来测试验证一下吧。
创建一个表TEST作为实验测试验证的样例
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
execute dbms_stats.gather_table_stats(ownname = 'ETL', tabname ='TEST', estimate_percent =DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt = 'FOR ALL COLUMNS SIZE AUTO');启用对索引IDX_TEST_ID的监控
ALTER INDEX IDX_TEST_ID MONITORING USAGE;此时观察V$OBJECT_USAGE表数据的变化，如下所示，MONITORIN字段值变为YES，表示索引IDX_TEST_ID已经被置于监控状态。USED字段为NO表示暂时没有SQL使用该索引SQL COL INDEX_NAME FOR A20
SQL COL TABLE_NAME FOR A10
SQL COL MONITORING FOR A10
SQL COL USED FOR A10
SQL COL START_MONITORING FOR A20
SQL COL END_MONITORING FOR A20
SQL SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------ ---------- ---------- ---- -------------------- ----------------IDX_TEST_ID TEST YES NO 11/28/2015 14:57:41此时我们执行下面SQL，因为此时使用全表扫描，那么索引IDX_TEST_ID依然没有被使用，此时可以查看V$OBJECT_USAGE进行验证。
SQL SET AUTOTRACE ON;
SQL SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------1001 Kerry
Execution Plan
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 2 || 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 2 |----------------------------------------------------------Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
clip_image001
如下所示，此时索引IDX_TEST_ID依然没有被使用。
clip_image002
我们使用索引提示强制下面SQL使用索引IDX_TEST_IDSELECT /*+ INDEX(TEST IDX_TEST_ID) */* FROM TEST WHERE ID =1001;此时你就会发现USED的值变为了YES了。
clip_image003
ALTER INDEX IDX_TEST_ID NOMONITORING USAGE;执行上面命令后，在V$OBJECT_USAGE表中，就会更新表TEST记录的END_MONITORING、MONITORING的值。
clip_image004
如果你又启用监控索引使用情况，那么系统会更新START_MONITORING、END_MONITORING字段的值（END_MONITORING的值更新为NULL）。如果删除表TEST，此时你会发现V$OBJECT_USAGE对象中关于表TEST的记录也不见了。
注意：SELECT * FROM V$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息。
在测试过程中有个小疑问，就是在准备测试环境时，如果不对表收集统计信息的话，那么即使SQL走全表扫描，你依然发现V$OBJECT_USAGE中索引被标记使用了。如下所示DROP TABLE TEST PURGE;
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
ALTER INDEX IDX_TEST_ID MONITORING USAGE;SQL SET AUTOTRACE ON;
SQL SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------1001 Kerry
Execution Plan
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 31 | 2 || 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 2 |----------------------------------------------------------Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------7 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------ ---------- ---------- ------ ----------- -------------------IDX_TEST_ID TEST YES YES 11/28/2015 15:11:46那么为什么呢？ 猜测是在解析生成执行计划时，用到了索引的一些信息，导致V$OBJECT_USAGE表中的字段USED被标记为YES。
如果我们想在系统中监控所有的索引，那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。原因有下面两个：
1：LOB indexes不能修改，否则会报ORA-22864错误（ORA-22864: cannot ALTER or DROP LOB indexes）。
2：ORA-00701: object necessary for warmstarting database cannot be alteredORA-00701: object necessary for warmstarting database cannot be altered00701. 00000 - "object necessary for warmstarting database cannot be altered"*Cause: Attempt to alter or drop a database object (table, cluster, orindex) which are needed for warmstarting the database.
*Action: None.
SET PAGES 999;
SET HEADING OFF;
SPOOL run_monitor.sql
SELECT
'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'
FROM
DBA_INDEXES
WHERE
INDEX_TYPE != 'LOB' AND OWNER NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');
SPOOL OFF;
@run_monitor.sql
此时使用下面脚本就能查出那些索引是未使用索引，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。
SELECT I.TABLE_OWNER,
T.TABLE_NAME,
I.INDEX_NAME,
U.USED,
U.START_MONITORING,
U.END_MONITORING
FROM USER_TABLES T
INNER JOIN USER_INDEXES I
ON T.TABLE_NAME = I.TABLE_NAME
INNER JOIN V$OBJECT_USAGE U
ON U.TABLE_NAME = I.TABLE_NAME
AND I.INDEX_NAME = U.INDEX_NAME
WHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')另外，博客Oracle - Find unused Indexes中介绍了一个查找没有使用索引的SQL语句。如下所示statspack_unused_indexes.sqlcol owner heading "Index Owner" format a30col index_name heading "Index Name" format a30set linesize 95 trimspool on pagesize 80
select *
from
(select
owner,
index_name
from
dba_indexes di
where
di.index_type != 'LOB'
and
owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
index_owner owner,
index_name
from
dba_constraints dc
where
index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
p.object_owner owner,
p.object_name index_name
from
stats$snapshot sn,
stats$sql_plan p,
stats$sql_summary st,
stats$sql_plan_usage spu
where
st.sql_id = spu.sql_id
and
spu.plan_hash_value = p.plan_hash_value
and
st.hash_value = p.plan_hash_value
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
sn.snap_id = spu.snap_id
and
sn.dbid = spu.snap_id
and
sn.instance_number = spu.instance_number
and
sn.snap_id between begin_snap and end_snapand
p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')order by 1, 2
/
这里是另一个脚本用来跟踪未使用的索引并展示给所有索引的调用计数。最重要的是，这个脚本显示了多列索引引用的列（这个脚本执行时间较长，资源开销较大。）col c1 heading 'Begin|Interval|time' format a20col c2 heading 'Search Columns' format 999col c3 heading 'Invocation|Count' format 99,999,999break on c1 skip 2
accept idxname char prompt 'Enter Index Name: '
ttitle 'Invocation Counts for index|idxname'
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,p.search_columns c2,count(*) c3from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = 'idxname'
group by
begin_interval_time,search_columns;
分别从当前状态、最近状态和启动至今三个时间段去看：
v$session/v$session+v$session_wait[当前状态]
v$session_wait_history[最近状态]
v$session_event[启动至今]
从10g开始v$session包含了v$session_wait的所有字段，所以对于当前状态，10g以后可以直接查看v$session，而10g之前可通过v$session和v$session_wait关联达到同样的效果。
v$session_wait_history记录了每个active session的最近10次的等待事件。如果查询某个session的等待事件频繁发生，则反映在v$session_wait_history的最近10条记录上则几乎是同一个等待事件。
v$session_event则记录了实例启动至今所有等待事件的等待次数、等待时间等的统计信息。可以根据等待次数或者等待时间进行排序，类似AWR report或者Statspack中的TOP 5 Waits，就可以大致反映出常态下数据库主要在等待哪些操作的完成，从而给数据库的进一步优化提供方向。
监控数据备份恢复完成进度（EXPDP/IMPDP/RMAN）
一、查看EXPDP/IMPDP的进度
1 两个视图
当你当如导出的时候，如果数据量比较大，中途有些人会着急，不免想看看进度如何，利用两个视图就可以看:
DBA_DATAPUBMP_JOBS和DBA_DATAPUMP_SESSIONS视图
col owner_name for a10
col job_name for a20
col operation for a10
col job_mode for a10
col state for a20
col degree for a10
col ATTACHED_SESSIONS for a30
col DATAPUMP_SESSIONS for a30
set linesize 200
格式化只是为了好看，也可以不用，直接用PL/SQL DEVELOPER图形工具。
可以使用DBA_DATAPUBMP_JOBS和DBA_DATAPUMP_SESSIONS视图来显示数据泵取作业的信息。
select * from DBA_DATAPUBMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;
select sid,serial# from v$session s,dba_datapump_sessions d where s.saddr=d.saddr;
补充一下，前面的sql命令行格式化有点问题，number型的字段应该用9999xxx（多个9） 而不是axx，否则会出现一串串的#。
2 attach参数
1）查看任务进度
当你使用crontab后台任务运行导入导出任务的时候，想查看任务进度，该参数很有用，可以让你再次连接到已经断开的会话中，再次接管导出或导入的任务，当然你得先用前面2个视图查到jobname。
2）中途想停止crontab后台任务
此时，该参数非常好用。因为你不这样做，就得杀进程，而那么多进程，通常都会出错，如此暴利的杀进程方式，强烈滴不推荐。
举例：
假如之前后台任务的脚本中，有类似命令：
expdp system/xxx DIRECTORY=DATA_PUMP_DIR2 parallel=32 DUMPFILE=xxxx-%U.dmp
ATTACH参数解释：将你的数据泵取客户机会话加入到一个运行的作业中，并使你进行交互方式。此参数只能与用户名/密码组合一起使用。
此时你可以使用如下命令重新连接任务,并达到提前终止任务的目的：
expdp system/xxx attach=lurou.exp
连接进去之后再执行help命令可以查到停止任务的命令，这里就不列出来了
3 longops视图
另也可以通过v$session_longops视图来监控长期运行的会话。
4.通过语句查看impdp进度SELECT a.tablespace_name,
ROUND (a.total_size) "total_size(MB)",
ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",
ROUND (b.free_size, 3) "free_size(MB)",
ROUND (b.free_size / total_size * 100, 2) || '%' free_rate
FROM
(
SELECT
tablespace_name, SUM (bytes) / 1024 / 1024 total_size
FROM dba_data_files
GROUP BY
tablespace_name) a,
(
SELECT
tablespace_name, SUM (bytes) / 1024 / 1024 free_size
FROM dba_free_space
GROUP BY
tablespace_name) b
WHERE
a.tablespace_name = b.tablespace_name(+);
二、查看RMAN备份进度
另外，查看rman的备份进度，可以用如下语句，记录备忘。
SELECT SID,OPNAME, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'AND OPNAME NOT LIKE '%aggregate%'AND TOTALWORK != 0 AND SOFAR TOTALWORK
order by "%_COMPLETE" desc
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网