1.无对应合理索引

2.位图索引

新版本才有此参数控制是否创建位图索引.
select * from v$dm_ini where para_name='ENABLE_CREATE_BM_INDEX_FLAG';
 SELECT * FROM SYS.DBA_INDEXES a where A.INDEX_TYPE='BITMAP';

3.事务阻塞

SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,'被阻塞的信息' WT,
       S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
       S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
       '引起阻塞的信息' FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
       S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
 FROM V$SESSIONS S1,V$SESSIONS S2,V$TRXWAIT W
 WHERE S1.TRX_ID=W.ID
   AND S2.TRX_ID=W.WAIT_FOR_ID;

以及
-- 006.追踪事务等待会话信息+历史SQL（受限V$SQL_HISTORY记录量）,可以临时快速 抓出源头执行的SQL历史
WITH XLCK AS (
	SELECT 
	      K.LTYPE || ':' || K.LMODE LCK_OP -- 锁类型和模式
	    , (SELECT O.SUBTYPE$ || ':' || SF_GET_SCHEMA_NAME_BY_ID(O.SCHID) || '.' || O.NAME FROM SYSOBJECTS O WHERE O.ID = K.TABLE_ID) OBJ$  -- 锁对象类型：对象名称
	    , K.ROW_IDX  -- 锁头位置
		, X.ID REQ_TID  -- 发起请求事务号
		, K.TID  BLK_TID -- 锁端阻塞事务号
--	    , TO_CHAR(SF_GET_SESSION_SQL(X.SESS_ID)) REQ_SQL  -- 等待者：请求
	    , SF_GET_SESSION_SQL(X.SESS_ID) REQ_SQL  -- 等待者：请求SQL大文本不适合普通字符类型 
	    , X.THRD_ID REQ_THRD#   -- 请求线程号：被阻塞线程号
	FROM V$TRX X, V$LOCK K
	WHERE X.START_LSN > 0
	AND X.WAITING > 0
	AND X.WAITING = K.ADDR
)
SELECT /*+ SORT_FLAG(0)*/
     CONCAT('/**', S2.SESS_ID ,'**/SP_CANCEL_SESSION_OPERATION(' || S2.SESS_ID || ');', CHR(10), 'SP_CLOSE_SESSION(' || S2.SESS_ID || ');') AS TERM_SESS
    , K1.*
    , DECODE(K1.REQ_TID, K1.BLK_TID, 'PURGING', 'NORMAL') IS_PURGE -- 判断事务等待另一种可能性：purge后台内部事务在动作
    , FLOOR((NOW() - S1.LAST_RECV_TIME) * 86400) WTNG_SS -- 当前已等待多长时间（秒）
	, S2.SQL_TEXT BLK_CUR_SQL  -- 阻塞源头
    , (SELECT LISTAGG(TOP_SQL_TEXT, CHR(10)) WITHIN GROUP(ORDER BY START_TIME) 
          FROM V$SQL_HISTORY H
          WHERE (NOT REGEXP_LIKE(H.TOP_SQL_TEXT, '\*DMJDBC\*|V\$|SYS(OBJECTS|INDEX|COLUMN)', 'i') ) -- 排除管理工具产生无效的SQL
          AND S2.TRX_ID = H.TRX_ID AND S2.THRD_ID = H.THREAD_ID
      ) BLK_HIS_SQL   -- 锁源者：执行历史SQL
	, REGEXP_REPLACE(S1.USER_NAME || '☆' || S1.CURR_SCH  || '@' || S1.CLNT_IP || '/' || S1.APPNAME, '[:f]*|:\d+\>|/$') REQ_CLNT -- 请求者客户端环境信息
	, REGEXP_REPLACE(S2.USER_NAME || '☆' || S2.CURR_SCH  || '@' || S2.CLNT_IP || '/' || S2.APPNAME, '[:f]*|:\d+\>|/$') BLK_CLNT -- 锁源客户端环境信息
FROM XLCK K1, V$SESSIONS S1, V$SESSIONS S2
WHERE K1.REQ_TID = S1.TRX_ID
AND K1.BLK_TID = S2.TRX_ID(+)
ORDER BY WTNG_SS DESC;

4.外键关系

表现为：v$sql_stat中的UNDO_PAGE_CHANGES_CNT和DATA_PAGE_CHANGES_CNT其实在变化增长。

SELECT A.OWNER,
       A.TABLE_NAME,
       A.CONSTRAINT_NAME,
       A.CONSTRAINT_TYPE,
       A.STATUS,
       B.OWNER,
       B.TABLE_NAME      AS 父表,
       B.COLUMN_NAME     AS 父表字段,
       B.CONSTRAINT_NAME AS R_CONSTRAINT_NAME
  FROM DBA_CONSTRAINTS      A,
       DBA_CONS_COLUMNS     B
 WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
   AND B.TABLE_NAME in ('T_STUDENT1','T_TEACHER1','T_BOOK1');
-- 关闭外键约束检查
ALTER TABLE T_STUDENT1 DISABLE CONSTRAINT "FK_JNXX_FILE1";
-- 开启外键约束检查
ALTER TABLE T_STUDENT1 ENABLE CONSTRAINT "FK_JNXX_FILE1";

--8.1.4.80 pack29
--子表创建外键时，如带了WITH INDEX，则可以用上，主表delete数据时，子表可以搜走约束所在的索引
ALTER TABLE "DZDA"."LOAN_MAIN_FILE" MODIFY CONSTRAINT "FK_JNXX_FILE" TO FOREIGN KEY("AJ_CODE", "JNXX_NO")
REFERENCES "DZDA"."LOAN_MAIN_JNXX"("AJ_CODE", "JNXX_NO") WITH INDEX;
--如果子表创建外键时没有WITH INDEX定义，哪怕后面像下面的sql这样去子表补充索引，那也用不上；这样会导致主表delete数据时，子表还是全表扫描
CREATE   INDEX "DZDA"."IND_TEMP1" ON "DZDA"."LOAN_MAIN_FILE"("AJ_CODE" ASC,"JNXX_NO" ASC)  ;


--速查外键无索引的表
select 
  sf_get_schema_name_by_id(t.schid) as owner,
  t.name as table_name,
  sc.name as constraint_name,
  sc.valid as enabled,
  rtrim(
     replace(
          replace(dbms_metadata.get_ddl('CONSTRAINT', sc.name, sf_get_schema_name_by_id(SC.schid)), 
                    'ADD CONS', 'MODIFY CONS'), 
         'FOREIGN KEY', 'TO FOREIGN KEY')
      , ';' ) 
      || ' WITH INDEX;' as foreign_index_ddl
from syscons c -- constraint detail
join sysobjects sc on c.id = sc.id and c.type$='F' and sc.subtype$='CONS' -- constraint outline
join sysobjects t on c.tableid = t.id -- retrieve table
join sysindexes i on c.indexid = i.id -- index compare
where (i.xtype & 0x00000001) = 1  
and (i.flag & 0x00000003) = 0x00000003     --  虚索引bit(1) + PK bit(2)
  AND T.NAME IN ('T_STUDENT1','T_TEACHER1','T_BOOK1');
Select    
        O_TAB_OBJ.table_owner  -- 表模式
      , O_TAB_OBJ.table_name   -- 子表名（引用表）
      , O_TAB_OBJ.constraint_name -- 子表约束名
      , O_TAB_OBJ.column_name   -- 子表字段名
      , R_TAB_OBJ.r_table_owner -- 父表模式
      , R_TAB_OBJ.r_table_name  --父表名（被引用表）
      , R_TAB_OBJ.r_constraint_name -- 父表约束名
      , R_TAB_OBJ.r_column_name   -- 父表字段名
from
(       select
 		sf_get_schema_name_by_id(o_tab.schid) table_owner
	      , o_tab.name table_name
	      , o_cons.name constraint_name
	      , s_col.name column_name
              , s_cons.findexid -- 外键虚拟索引
              , SF_GET_INDEX_KEY_SEQ(o_idx.KEYNUM, o_idx.KEYINFO, s_col.COLID) kpos
 	from syscons s_cons, sysobjects o_cons, sysobjects o_tab ,sysindexes o_idx, syscolumns s_col
	where 1 = 1
	and s_cons.type$ = 'F' -- 外键
	and o_cons.subtype$ = 'CONS'
	and o_cons.id = s_cons.id
	and o_tab.subtype$='UTAB'
	and o_tab.id = s_cons.tableid
	and s_col.id = o_tab.id
	and o_idx.id = s_cons.indexid
	AND SF_COL_IS_IDX_KEY(o_idx.KEYNUM, o_idx.KEYINFO, s_col.COLID) = 1
	-- and o_tab.name = 'ZCL_CESHI'  -- 调试
) O_TAB_OBJ,
(
	select 
	       sf_get_schema_name_by_id(r_o_tab.schid) r_table_owner
	      ,r_o_tab.name r_table_name
	      ,r_s_col.name r_column_name
              ,r_o_cons.name r_constraint_name
	      ,r_idx.id r_indexid
              ,r_o_idx.name r_index_name
              ,SF_GET_INDEX_KEY_SEQ(r_idx.KEYNUM, r_idx.KEYINFO, r_s_col.COLID) rpos
	from sysobjects r_o_idx, sysobjects r_o_tab
             ,sysindexes r_idx, syscolumns r_s_col 
             ,sysobjects r_o_cons, syscons r_cons
	where 1 = 1
	and r_o_idx.subtype$ = 'INDEX'
        and r_o_tab.subtype$ = 'UTAB'
        and r_idx.ISUNIQUE= 'Y' -- 唯一索引
	and r_o_tab.id = r_o_idx.pid
	and r_o_idx.id = r_idx.id
        and r_s_col.id = r_o_tab.id  -- 遗漏 ： 列的上级对象
	and SF_COL_IS_IDX_KEY(r_idx.KEYNUM, r_idx.KEYINFO, r_s_col.COLID) = 1
        -- and r_idx.id = 33555943   -- 调试入口
        and r_o_cons.subtype$(+) = 'CONS'
        and r_idx.id = r_cons.indexid(+) 
        and r_cons.id = r_o_cons.id(+)
) R_TAB_OBJ 
where 1 = 1
--and O_TAB_OBJ.TABLE_NAME = 'TBL_CONS_01'    -- 可变入口：传入参数条件，子表名称
--R
and O_TAB_OBJ.findexid = R_TAB_OBJ.r_indexid
and O_TAB_OBJ.kpos = R_TAB_OBJ.rpos   -- 去重唯一作用
order by table_owner, table_name, column_name -- 保证组合唯一键的组合字段顺序

5.触发器影响

表现为：v$sql_stat中的UNDO_PAGE_CHANGES_CNT和DATA_PAGE_CHANGES_CNT其实在变化增长。
select a.UNDO_PAGE_CHANGES_CNT,
       a.DATA_PAGE_CHANGES_CNT,
       a.STMT_BF_TRG_CNT,--观察是否增长
       a.STMT_AF_TRG_CNT,--观察是否增长
       a.ROW_BF_TRG_CNT,--观察是否增长
       a.ROW_AF_TRG_CNT,--观察是否增长
       a.INSTEAD_OF_TRG_CNT,--观察是否增长
       a.*
  from v$sql_stat a
 where a.SQL_TXT like '%DOCFILEINFO%'
   and a.SQL_TXT not like '%sql_stat%';
--样例
drop table if exists DOCFILEINFO;
CREATE TABLE "DOCFILEINFO"
(
"ID" VARCHAR(400) NOT NULL,
"FILE_PATH" VARCHAR(4000),
"FILE_SIZE" VARCHAR(256) NOT NULL,
"CONVERT_PATH" VARCHAR(4000),
"PID" VARCHAR(256) DEFAULT '0' NOT NULL,
"ISCONVERT" INT DEFAULT 0 NOT NULL,
NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
drop  TRIGGER if exists "TRG_ADD_SLASH_PREFIX";
CREATE OR REPLACE   TRIGGER "TRG_ADD_SLASH_PREFIX"
 BEFORE  INSERT OR  UPDATE 
 ON "DOCFILEINFO" 
 REFERENCING OLD ROW AS "OLD" NEW ROW AS "NEW"
 FOR EACH ROW
BEGIN
    IF :NEW.FILE_PATH IS NOT NULL AND :NEW.FILE_PATH <> '' AND NOT (:NEW.FILE_PATH LIKE '/%') THEN
        :NEW.FILE_PATH := '/' || :NEW.FILE_PATH;
    END IF;
    IF :NEW.CONVERT_PATH IS NOT NULL AND :NEW.CONVERT_PATH <> '' AND NOT (:NEW.CONVERT_PATH LIKE '/%') THEN
        :NEW.CONVERT_PATH := '/' || :NEW.CONVERT_PATH;
    END IF;
END;

6.物化视图影响

--这样可以查全库的物化视图数量
SELECT * FROM SYS.USER_MVIEWS;

查询阻塞时，发现引起阻塞的sql是这种语句（算是阻塞的子类）。
{
{
try{
	DATETIME DT;
	DT = NOW(6);
	SF_LOCK_DICT(150995955, 4, 0);
	SF_LOCK_DICT_EX2(16778448, 3);
	UPDATE SYS.SYSOBJECTS SET INFO5 = SF_SET_MV_PROPERTY(3, NULL, INFO5)                  WHERE SF_GET_MV_PROPERTY(INFO5,1) = 'FRESH' AND ID IN (16778448) AND TYPE$ = 'SCHOBJ' AND SUBTYPE$ = 'VIEW' AND SF_GET_MV_PROPERTY(INFO5) < DT;
	SF_REMOVE_DICT_OBJ(150995955, 16778448, 'SCHOBJ', 'VIEW', 1);
   }
catch (exception ex){
	COMMIT;
	}
	COMMIT;
}
{
try{
   }
catch (exception ex){
	COMMIT;
	}
	COMMIT;
}
}